CPU usage

I want to know what is the %CPU used by a process in SQL Server. In other
words, I got the CPUTime from the sysprocesses table and the number is say
10,000.
How can I relate this CPU time with NT processor percentage? A user called
me before running some stored procedure and wanted to know what is the
percentage of Total NT CPU this stored procedure is using?
Any idea?
Thanks in advanceHi David
Sysprocesses/CPU is a cumulative CPU usage and therefore can not be taken
against the current perfmon value. The amount of CPU any given stored
procedure takes will probably be slightly different each time it runs,
depending on what else is running and other factors such as how up-to-date
are the statistics or if it encountered blocking etc...
John
"David" wrote:

> I want to know what is the %CPU used by a process in SQL Server. In other
> words, I got the CPUTime from the sysprocesses table and the number is say
> 10,000.
> How can I relate this CPU time with NT processor percentage? A user called
> me before running some stored procedure and wanted to know what is the
> percentage of Total NT CPU this stored procedure is using?
> Any idea?
> Thanks in advance

CPU usage

I want to know what is the %CPU used by a process in SQL Server. In other
words, I got the CPUTime from the sysprocesses table and the number is say
10,000.
How can I relate this CPU time with NT processor percentage? A user called
me before running some stored procedure and wanted to know what is the
percentage of Total NT CPU this stored procedure is using?
Any idea?
Thanks in advanceHi David
Sysprocesses/CPU is a cumulative CPU usage and therefore can not be taken
against the current perfmon value. The amount of CPU any given stored
procedure takes will probably be slightly different each time it runs,
depending on what else is running and other factors such as how up-to-date
are the statistics or if it encountered blocking etc...
John
"David" wrote:
> I want to know what is the %CPU used by a process in SQL Server. In other
> words, I got the CPUTime from the sysprocesses table and the number is say
> 10,000.
> How can I relate this CPU time with NT processor percentage? A user called
> me before running some stored procedure and wanted to know what is the
> percentage of Total NT CPU this stored procedure is using?
> Any idea?
> Thanks in advance

CPU Usage

Im having rather high CPU usage by my sqlservr process and i was wondering
how i would go about analyzing that.
I was peeking in the activity monitor but i wasnt entirely sure on how to
read it all.
any links or advice would be greatly appreciated.
Thanks
JustinJustin
http://www.sql-server-performance.com/tips/performance_monitor_cpu_counter_p1.aspx
"Justin Rich" <jrich523@.yahoo.spam.com> wrote in message
news:%23DDRn%23CBIHA.1208@.TK2MSFTNGP03.phx.gbl...
> Im having rather high CPU usage by my sqlservr process and i was wondering
> how i would go about analyzing that.
> I was peeking in the activity monitor but i wasnt entirely sure on how to
> read it all.
> any links or advice would be greatly appreciated.
> Thanks
> Justin
>

cpu up with SQL2005 - higher FullScans/sec, lwr IndexSearches/sec

We are seeing a 29% increase in the cpu of the sql process in SQL2005 over a
5 hour perfmorance test. The application code is the same and the actions
performed during the test are the same. The increase is compared to SQL2000.
The db server is the same and was upgraded from SQL2000.
Disk Configuration: SCSI RAID 5, 3 Partitions
RAM: 3.75 GB of RAM
CPU: Quad Processor, 2.7Ghz
OS: Windows2003 SP1 Enterprise
* Perfmon shows disk read/sec and disk write/sec fairly close between
SQL2000 and SQL2005
Specifically, perfmon shows increased FullScans/sec and decreased
IndexSearches/sec.
From perfmon
FullScan/sec (avg):
2000: 6.292
2005: 54.338
IndexSearches/sec (avg):
2000: 3455.942
2005: 1441.712
Any comments or suggestions would be greatly appreciated. Thanks.Did you update the stats after you brought over the db?
Andrew J. Kelly SQL MVP
"George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
> We are seeing a 29% increase in the cpu of the sql process in SQL2005 over
> a
> 5 hour perfmorance test. The application code is the same and the actions
> performed during the test are the same. The increase is compared to
> SQL2000.
> The db server is the same and was upgraded from SQL2000.
> Disk Configuration: SCSI RAID 5, 3 Partitions
> RAM: 3.75 GB of RAM
> CPU: Quad Processor, 2.7Ghz
> OS: Windows2003 SP1 Enterprise
> * Perfmon shows disk read/sec and disk write/sec fairly close between
> SQL2000 and SQL2005
> Specifically, perfmon shows increased FullScans/sec and decreased
> IndexSearches/sec.
> From perfmon
> FullScan/sec (avg):
> 2000: 6.292
> 2005: 54.338
> IndexSearches/sec (avg):
> 2000: 3455.942
> 2005: 1441.712
> Any comments or suggestions would be greatly appreciated. Thanks.|||We saw even worse behavior. But I fixed it with these 3 things and it
resolved most of the slowness (not sure if you need one or both)
-- to update all stats
exec sp_updatestats
--rebuild all indexes
dbcc dbreindex('tablename') for each table
--set all sps to recompile on next execute
exec sp_recompile 'sp_name' for every stored procedure in your database
I identified a few other queries that just had to be re-written. They
seemed to always involve subqueries using "where not in (select ? from...)
"George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
> We are seeing a 29% increase in the cpu of the sql process in SQL2005 over
> a
> 5 hour perfmorance test. The application code is the same and the actions
> performed during the test are the same. The increase is compared to
> SQL2000.
> The db server is the same and was upgraded from SQL2000.
> Disk Configuration: SCSI RAID 5, 3 Partitions
> RAM: 3.75 GB of RAM
> CPU: Quad Processor, 2.7Ghz
> OS: Windows2003 SP1 Enterprise
> * Perfmon shows disk read/sec and disk write/sec fairly close between
> SQL2000 and SQL2005
> Specifically, perfmon shows increased FullScans/sec and decreased
> IndexSearches/sec.
> From perfmon
> FullScan/sec (avg):
> 2000: 6.292
> 2005: 54.338
> IndexSearches/sec (avg):
> 2000: 3455.942
> 2005: 1441.712
> Any comments or suggestions would be greatly appreciated. Thanks.|||I did not manually execute such a step. And if this wasnt done automatically
with the upgrade to SQL2005 then it was not done.
Is this the link for this?
http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx
or are there more specifc instructions you can point me to?
Thanks
"Andrew J. Kelly" wrote:
> Did you update the stats after you brought over the db?
>
> --
> Andrew J. Kelly SQL MVP
>
> "George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
> news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
> > We are seeing a 29% increase in the cpu of the sql process in SQL2005 over
> > a
> > 5 hour perfmorance test. The application code is the same and the actions
> > performed during the test are the same. The increase is compared to
> > SQL2000.
> >
> > The db server is the same and was upgraded from SQL2000.
> > Disk Configuration: SCSI RAID 5, 3 Partitions
> > RAM: 3.75 GB of RAM
> > CPU: Quad Processor, 2.7Ghz
> > OS: Windows2003 SP1 Enterprise
> >
> > * Perfmon shows disk read/sec and disk write/sec fairly close between
> > SQL2000 and SQL2005
> >
> > Specifically, perfmon shows increased FullScans/sec and decreased
> > IndexSearches/sec.
> >
> > From perfmon
> > FullScan/sec (avg):
> > 2000: 6.292
> > 2005: 54.338
> >
> > IndexSearches/sec (avg):
> > 2000: 3455.942
> > 2005: 1441.712
> >
> > Any comments or suggestions would be greatly appreciated. Thanks.
>
>|||No this is not done by the upgrade process. The link tells you what stats
do. To update them either run sp_updatestats to update all stats or if you
haven't run DBCC DBREINDEX in a while try running that on all tables.
--
Andrew J. Kelly SQL MVP
"George Koulis" <GeorgeKoulis@.discussions.microsoft.com> wrote in message
news:071092A9-6057-47BB-A0A1-3EC8061D088C@.microsoft.com...
>I did not manually execute such a step. And if this wasnt done
>automatically
> with the upgrade to SQL2005 then it was not done.
> Is this the link for this?
> http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx
> or are there more specifc instructions you can point me to?
> Thanks
>
> "Andrew J. Kelly" wrote:
>> Did you update the stats after you brought over the db?
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "George Koulis" <George Koulis@.discussions.microsoft.com> wrote in
>> message
>> news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
>> > We are seeing a 29% increase in the cpu of the sql process in SQL2005
>> > over
>> > a
>> > 5 hour perfmorance test. The application code is the same and the
>> > actions
>> > performed during the test are the same. The increase is compared to
>> > SQL2000.
>> >
>> > The db server is the same and was upgraded from SQL2000.
>> > Disk Configuration: SCSI RAID 5, 3 Partitions
>> > RAM: 3.75 GB of RAM
>> > CPU: Quad Processor, 2.7Ghz
>> > OS: Windows2003 SP1 Enterprise
>> >
>> > * Perfmon shows disk read/sec and disk write/sec fairly close between
>> > SQL2000 and SQL2005
>> >
>> > Specifically, perfmon shows increased FullScans/sec and decreased
>> > IndexSearches/sec.
>> >
>> > From perfmon
>> > FullScan/sec (avg):
>> > 2000: 6.292
>> > 2005: 54.338
>> >
>> > IndexSearches/sec (avg):
>> > 2000: 3455.942
>> > 2005: 1441.712
>> >
>> > Any comments or suggestions would be greatly appreciated. Thanks.
>>|||Just FYI. There is no need to do all three steps. When you run DBCC
DBREINDEX on a table it will not only rebuild the index but update the stats
as well. Each time the stats are updated any query plans that are associated
with these stats will automatically be marked for recompile the next time
they get run. So in a sense you are tripling your efforts by doing all three
manually.
--
Andrew J. Kelly SQL MVP
"JL Morrison" <sqlserverdba@.tampabay.rr.com> wrote in message
news:eOQAKlgHGHA.3896@.TK2MSFTNGP15.phx.gbl...
> We saw even worse behavior. But I fixed it with these 3 things and it
> resolved most of the slowness (not sure if you need one or both)
> -- to update all stats
> exec sp_updatestats
> --rebuild all indexes
> dbcc dbreindex('tablename') for each table
> --set all sps to recompile on next execute
> exec sp_recompile 'sp_name' for every stored procedure in your database
> I identified a few other queries that just had to be re-written. They
> seemed to always involve subqueries using "where not in (select ? from...)
>
> "George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
> news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
>> We are seeing a 29% increase in the cpu of the sql process in SQL2005
>> over a
>> 5 hour perfmorance test. The application code is the same and the actions
>> performed during the test are the same. The increase is compared to
>> SQL2000.
>> The db server is the same and was upgraded from SQL2000.
>> Disk Configuration: SCSI RAID 5, 3 Partitions
>> RAM: 3.75 GB of RAM
>> CPU: Quad Processor, 2.7Ghz
>> OS: Windows2003 SP1 Enterprise
>> * Perfmon shows disk read/sec and disk write/sec fairly close between
>> SQL2000 and SQL2005
>> Specifically, perfmon shows increased FullScans/sec and decreased
>> IndexSearches/sec.
>> From perfmon
>> FullScan/sec (avg):
>> 2000: 6.292
>> 2005: 54.338
>> IndexSearches/sec (avg):
>> 2000: 3455.942
>> 2005: 1441.712
>> Any comments or suggestions would be greatly appreciated. Thanks.
>|||Whenever you upgrade SQL, whether it is just a patch or a Service Pack, and
sqlservr.exe is updated, and especially a version update you should try and
rebuild indexes and update stats. If sqlservr.exe changes then the optimizer
most probably has changed and will not work exactly the same as it did
before the upgrade.
Chris
"JL Morrison" <sqlserverdba@.tampabay.rr.com> wrote in message
news:eOQAKlgHGHA.3896@.TK2MSFTNGP15.phx.gbl...
> We saw even worse behavior. But I fixed it with these 3 things and it
> resolved most of the slowness (not sure if you need one or both)
> -- to update all stats
> exec sp_updatestats
> --rebuild all indexes
> dbcc dbreindex('tablename') for each table
> --set all sps to recompile on next execute
> exec sp_recompile 'sp_name' for every stored procedure in your database
> I identified a few other queries that just had to be re-written. They
> seemed to always involve subqueries using "where not in (select ? from...)
>
> "George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
> news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
>> We are seeing a 29% increase in the cpu of the sql process in SQL2005
>> over a
>> 5 hour perfmorance test. The application code is the same and the actions
>> performed during the test are the same. The increase is compared to
>> SQL2000.
>> The db server is the same and was upgraded from SQL2000.
>> Disk Configuration: SCSI RAID 5, 3 Partitions
>> RAM: 3.75 GB of RAM
>> CPU: Quad Processor, 2.7Ghz
>> OS: Windows2003 SP1 Enterprise
>> * Perfmon shows disk read/sec and disk write/sec fairly close between
>> SQL2000 and SQL2005
>> Specifically, perfmon shows increased FullScans/sec and decreased
>> IndexSearches/sec.
>> From perfmon
>> FullScan/sec (avg):
>> 2000: 6.292
>> 2005: 54.338
>> IndexSearches/sec (avg):
>> 2000: 3455.942
>> 2005: 1441.712
>> Any comments or suggestions would be greatly appreciated. Thanks.
>

cpu up with SQL2005 - higher FullScans/sec, lwr IndexSearches/sec

We are seeing a 29% increase in the cpu of the sql process in SQL2005 over a
5 hour perfmorance test. The application code is the same and the actions
performed during the test are the same. The increase is compared to SQL2000.
The db server is the same and was upgraded from SQL2000.
Disk Configuration: SCSI RAID 5, 3 Partitions
RAM: 3.75 GB of RAM
CPU: Quad Processor, 2.7Ghz
OS: Windows2003 SP1 Enterprise
* Perfmon shows disk read/sec and disk write/sec fairly close between
SQL2000 and SQL2005
Specifically, perfmon shows increased FullScans/sec and decreased
IndexSearches/sec.
From perfmon
FullScan/sec (avg):
2000: 6.292
2005: 54.338
IndexSearches/sec (avg):
2000: 3455.942
2005: 1441.712
Any comments or suggestions would be greatly appreciated. Thanks.
Did you update the stats after you brought over the db?
Andrew J. Kelly SQL MVP
"George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
> We are seeing a 29% increase in the cpu of the sql process in SQL2005 over
> a
> 5 hour perfmorance test. The application code is the same and the actions
> performed during the test are the same. The increase is compared to
> SQL2000.
> The db server is the same and was upgraded from SQL2000.
> Disk Configuration: SCSI RAID 5, 3 Partitions
> RAM: 3.75 GB of RAM
> CPU: Quad Processor, 2.7Ghz
> OS: Windows2003 SP1 Enterprise
> * Perfmon shows disk read/sec and disk write/sec fairly close between
> SQL2000 and SQL2005
> Specifically, perfmon shows increased FullScans/sec and decreased
> IndexSearches/sec.
> From perfmon
> FullScan/sec (avg):
> 2000: 6.292
> 2005: 54.338
> IndexSearches/sec (avg):
> 2000: 3455.942
> 2005: 1441.712
> Any comments or suggestions would be greatly appreciated. Thanks.
|||We saw even worse behavior. But I fixed it with these 3 things and it
resolved most of the slowness (not sure if you need one or both)
-- to update all stats
exec sp_updatestats
--rebuild all indexes
dbcc dbreindex('tablename') for each table
--set all sps to recompile on next execute
exec sp_recompile 'sp_name' for every stored procedure in your database
I identified a few other queries that just had to be re-written. They
seemed to always involve subqueries using "where not in (select ? from...)
"George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
> We are seeing a 29% increase in the cpu of the sql process in SQL2005 over
> a
> 5 hour perfmorance test. The application code is the same and the actions
> performed during the test are the same. The increase is compared to
> SQL2000.
> The db server is the same and was upgraded from SQL2000.
> Disk Configuration: SCSI RAID 5, 3 Partitions
> RAM: 3.75 GB of RAM
> CPU: Quad Processor, 2.7Ghz
> OS: Windows2003 SP1 Enterprise
> * Perfmon shows disk read/sec and disk write/sec fairly close between
> SQL2000 and SQL2005
> Specifically, perfmon shows increased FullScans/sec and decreased
> IndexSearches/sec.
> From perfmon
> FullScan/sec (avg):
> 2000: 6.292
> 2005: 54.338
> IndexSearches/sec (avg):
> 2000: 3455.942
> 2005: 1441.712
> Any comments or suggestions would be greatly appreciated. Thanks.
|||Just FYI. There is no need to do all three steps. When you run DBCC
DBREINDEX on a table it will not only rebuild the index but update the stats
as well. Each time the stats are updated any query plans that are associated
with these stats will automatically be marked for recompile the next time
they get run. So in a sense you are tripling your efforts by doing all three
manually.
Andrew J. Kelly SQL MVP
"JL Morrison" <sqlserverdba@.tampabay.rr.com> wrote in message
news:eOQAKlgHGHA.3896@.TK2MSFTNGP15.phx.gbl...
> We saw even worse behavior. But I fixed it with these 3 things and it
> resolved most of the slowness (not sure if you need one or both)
> -- to update all stats
> exec sp_updatestats
> --rebuild all indexes
> dbcc dbreindex('tablename') for each table
> --set all sps to recompile on next execute
> exec sp_recompile 'sp_name' for every stored procedure in your database
> I identified a few other queries that just had to be re-written. They
> seemed to always involve subqueries using "where not in (select ? from...)
>
> "George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
> news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
>
|||Whenever you upgrade SQL, whether it is just a patch or a Service Pack, and
sqlservr.exe is updated, and especially a version update you should try and
rebuild indexes and update stats. If sqlservr.exe changes then the optimizer
most probably has changed and will not work exactly the same as it did
before the upgrade.
Chris
"JL Morrison" <sqlserverdba@.tampabay.rr.com> wrote in message
news:eOQAKlgHGHA.3896@.TK2MSFTNGP15.phx.gbl...
> We saw even worse behavior. But I fixed it with these 3 things and it
> resolved most of the slowness (not sure if you need one or both)
> -- to update all stats
> exec sp_updatestats
> --rebuild all indexes
> dbcc dbreindex('tablename') for each table
> --set all sps to recompile on next execute
> exec sp_recompile 'sp_name' for every stored procedure in your database
> I identified a few other queries that just had to be re-written. They
> seemed to always involve subqueries using "where not in (select ? from...)
>
> "George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
> news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
>

cpu up with SQL2005 - higher FullScans/sec, lwr IndexSearches/sec

We are seeing a 29% increase in the cpu of the sql process in SQL2005 over a
5 hour perfmorance test. The application code is the same and the actions
performed during the test are the same. The increase is compared to SQL2000
.
The db server is the same and was upgraded from SQL2000.
Disk Configuration: SCSI RAID 5, 3 Partitions
RAM: 3.75 GB of RAM
CPU: Quad Processor, 2.7Ghz
OS: Windows2003 SP1 Enterprise
* Perfmon shows disk read/sec and disk write/sec fairly close between
SQL2000 and SQL2005
Specifically, perfmon shows increased FullScans/sec and decreased
IndexSearches/sec.
From perfmon
FullScan/sec (avg):
2000: 6.292
2005: 54.338
IndexSearches/sec (avg):
2000: 3455.942
2005: 1441.712
Any comments or suggestions would be greatly appreciated. Thanks.Did you update the stats after you brought over the db?
Andrew J. Kelly SQL MVP
"George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
> We are seeing a 29% increase in the cpu of the sql process in SQL2005 over
> a
> 5 hour perfmorance test. The application code is the same and the actions
> performed during the test are the same. The increase is compared to
> SQL2000.
> The db server is the same and was upgraded from SQL2000.
> Disk Configuration: SCSI RAID 5, 3 Partitions
> RAM: 3.75 GB of RAM
> CPU: Quad Processor, 2.7Ghz
> OS: Windows2003 SP1 Enterprise
> * Perfmon shows disk read/sec and disk write/sec fairly close between
> SQL2000 and SQL2005
> Specifically, perfmon shows increased FullScans/sec and decreased
> IndexSearches/sec.
> From perfmon
> FullScan/sec (avg):
> 2000: 6.292
> 2005: 54.338
> IndexSearches/sec (avg):
> 2000: 3455.942
> 2005: 1441.712
> Any comments or suggestions would be greatly appreciated. Thanks.|||We saw even worse behavior. But I fixed it with these 3 things and it
resolved most of the slowness (not sure if you need one or both)
-- to update all stats
exec sp_updatestats
--rebuild all indexes
dbcc dbreindex('tablename') for each table
--set all sps to recompile on next execute
exec sp_recompile 'sp_name' for every stored procedure in your database
I identified a few other queries that just had to be re-written. They
seemed to always involve subqueries using "where not in (select ? from...)
"George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
> We are seeing a 29% increase in the cpu of the sql process in SQL2005 over
> a
> 5 hour perfmorance test. The application code is the same and the actions
> performed during the test are the same. The increase is compared to
> SQL2000.
> The db server is the same and was upgraded from SQL2000.
> Disk Configuration: SCSI RAID 5, 3 Partitions
> RAM: 3.75 GB of RAM
> CPU: Quad Processor, 2.7Ghz
> OS: Windows2003 SP1 Enterprise
> * Perfmon shows disk read/sec and disk write/sec fairly close between
> SQL2000 and SQL2005
> Specifically, perfmon shows increased FullScans/sec and decreased
> IndexSearches/sec.
> From perfmon
> FullScan/sec (avg):
> 2000: 6.292
> 2005: 54.338
> IndexSearches/sec (avg):
> 2000: 3455.942
> 2005: 1441.712
> Any comments or suggestions would be greatly appreciated. Thanks.|||Just FYI. There is no need to do all three steps. When you run DBCC
DBREINDEX on a table it will not only rebuild the index but update the stats
as well. Each time the stats are updated any query plans that are associated
with these stats will automatically be marked for recompile the next time
they get run. So in a sense you are tripling your efforts by doing all three
manually.
Andrew J. Kelly SQL MVP
"JL Morrison" <sqlserverdba@.tampabay.rr.com> wrote in message
news:eOQAKlgHGHA.3896@.TK2MSFTNGP15.phx.gbl...
> We saw even worse behavior. But I fixed it with these 3 things and it
> resolved most of the slowness (not sure if you need one or both)
> -- to update all stats
> exec sp_updatestats
> --rebuild all indexes
> dbcc dbreindex('tablename') for each table
> --set all sps to recompile on next execute
> exec sp_recompile 'sp_name' for every stored procedure in your database
> I identified a few other queries that just had to be re-written. They
> seemed to always involve subqueries using "where not in (select ? from...)
>
> "George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
> news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
>|||Whenever you upgrade SQL, whether it is just a patch or a Service Pack, and
sqlservr.exe is updated, and especially a version update you should try and
rebuild indexes and update stats. If sqlservr.exe changes then the optimizer
most probably has changed and will not work exactly the same as it did
before the upgrade.
Chris
"JL Morrison" <sqlserverdba@.tampabay.rr.com> wrote in message
news:eOQAKlgHGHA.3896@.TK2MSFTNGP15.phx.gbl...
> We saw even worse behavior. But I fixed it with these 3 things and it
> resolved most of the slowness (not sure if you need one or both)
> -- to update all stats
> exec sp_updatestats
> --rebuild all indexes
> dbcc dbreindex('tablename') for each table
> --set all sps to recompile on next execute
> exec sp_recompile 'sp_name' for every stored procedure in your database
> I identified a few other queries that just had to be re-written. They
> seemed to always involve subqueries using "where not in (select ? from...)
>
> "George Koulis" <George Koulis@.discussions.microsoft.com> wrote in message
> news:247F2966-FBD4-41F8-8628-6166F02885D8@.microsoft.com...
>

CPU Time

I have a SQL server 2000 standard version installed on a window 2000 server.
It has been working well. Recently, SQL server process sqlservr.exe consume
50%-99% of the CPU time even I didn't issue any SQL server related
operation. There is not any backup or maintain job was set up in the server.
The memory usage doesn't change. I once waited for more than one hour to
complete the process, however it didn't.
Did anyone know what's happened? My computer become really slow and hardly
do anything.
Thanks
YingHi
Is SQL Server 2000 Service Pack 3 or 4 applied. If not, it sounds like the
Slammer Virus that came out 3 years ago.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Ying" <Ying@.discussions.microsoft.com> wrote in message
news:D712A471-053F-448E-A8CA-4A85CCB1EB1C@.microsoft.com...
>I have a SQL server 2000 standard version installed on a window 2000
>server.
> It has been working well. Recently, SQL server process sqlservr.exe
> consume
> 50%-99% of the CPU time even I didn't issue any SQL server related
> operation. There is not any backup or maintain job was set up in the
> server.
> The memory usage doesn't change. I once waited for more than one hour to
> complete the process, however it didn't.
> Did anyone know what's happened? My computer become really slow and hardly
> do anything.
> Thanks
> Ying

CPU Time

I have a SQL server 2000 standard version installed on a window 2000 server.
It has been working well. Recently, SQL server process sqlservr.exe consume
50%-99% of the CPU time even I didn't issue any SQL server related
operation. There is not any backup or maintain job was set up in the server.
The memory usage doesn't change. I once waited for more than one hour to
complete the process, however it didn't.
Did anyone know what's happened? My computer become really slow and hardly
do anything.
Thanks
Ying
Hi
Is SQL Server 2000 Service Pack 3 or 4 applied. If not, it sounds like the
Slammer Virus that came out 3 years ago.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Ying" <Ying@.discussions.microsoft.com> wrote in message
news:D712A471-053F-448E-A8CA-4A85CCB1EB1C@.microsoft.com...
>I have a SQL server 2000 standard version installed on a window 2000
>server.
> It has been working well. Recently, SQL server process sqlservr.exe
> consume
> 50%-99% of the CPU time even I didn't issue any SQL server related
> operation. There is not any backup or maintain job was set up in the
> server.
> The memory usage doesn't change. I once waited for more than one hour to
> complete the process, however it didn't.
> Did anyone know what's happened? My computer become really slow and hardly
> do anything.
> Thanks
> Ying

CPU Time

I have a SQL server 2000 standard version installed on a window 2000 server.
It has been working well. Recently, SQL server process sqlservr.exe consume
50%-99% of the CPU time even I didn't issue any SQL server related
operation. There is not any backup or maintain job was set up in the server.
The memory usage doesn't change. I once waited for more than one hour to
complete the process, however it didn't.
Did anyone know what's happened? My computer become really slow and hardly
do anything.
Thanks
YingHi
Is SQL Server 2000 Service Pack 3 or 4 applied. If not, it sounds like the
Slammer Virus that came out 3 years ago.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Ying" <Ying@.discussions.microsoft.com> wrote in message
news:D712A471-053F-448E-A8CA-4A85CCB1EB1C@.microsoft.com...
>I have a SQL server 2000 standard version installed on a window 2000
>server.
> It has been working well. Recently, SQL server process sqlservr.exe
> consume
> 50%-99% of the CPU time even I didn't issue any SQL server related
> operation. There is not any backup or maintain job was set up in the
> server.
> The memory usage doesn't change. I once waited for more than one hour to
> complete the process, however it didn't.
> Did anyone know what's happened? My computer become really slow and hardly
> do anything.
> Thanks
> Ying

CPU SPIKES

Folks,
Since Friday i am seeing High CPU Spikes on our app / db server. the Sql ser
ver process is currently consuming about 60+ % cpu. There is not much going
on at the database level ? I am not sure what is causing the spikes.
This is really abnormal behaviour on the this server.
Please any help would be really appreciated
Thanks
GirishGirish wrote:
> Folks,
> Since Friday i am seeing High CPU Spikes on our app / db server. the
> Sql server process is currently consuming about 60+ % cpu. There is
> not much going on at the database level ? I am not sure what is
> causing the spikes.
Occassional spikes are pretty normal in SQL Server. Or is it a constant 60+%
CPU usage?
See the "How to Do SQL Server Performance Trend Analysis" articles series
for performance monitoring
http://www.sql-server-performance.c...tutor_part1.asp
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Have you tried running sp_who or sp_who2 to see what processes are running
stuff in sql? Run this system proc and check io and cpu cycles.
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 programming by Example
"Girish" <anonymous@.discussions.microsoft.com> wrote in message
news:CDF77CFE-DD12-4E3F-97E2-6D9FE33B15D0@.microsoft.com...
> Folks,
> Since Friday i am seeing High CPU Spikes on our app / db server. the Sql
server process is currently consuming about 60+ % cpu. There is not much
going on at the database level ? I am not sure what is causing the spikes.
> This is really abnormal behaviour on the this server.
> Please any help would be really appreciated
> Thanks
> Girish

CPU SPIKES

Folks
Since Friday i am seeing High CPU Spikes on our app / db server. the Sql server process is currently consuming about 60+ % cpu. There is not much going on at the database level ? I am not sure what is causing the spikes
This is really abnormal behaviour on the this server.
Please any help would be really appreciate
Thank
GirishGirish wrote:
> Folks,
> Since Friday i am seeing High CPU Spikes on our app / db server. the
> Sql server process is currently consuming about 60+ % cpu. There is
> not much going on at the database level ? I am not sure what is
> causing the spikes.
Occassional spikes are pretty normal in SQL Server. Or is it a constant 60+%
CPU usage?
See the "How to Do SQL Server Performance Trend Analysis" articles series
for performance monitoring
http://www.sql-server-performance.com/performance_monitoring_tutor_part1.asp
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Have you tried running sp_who or sp_who2 to see what processes are running
stuff in sql? Run this system proc and check io and cpu cycles.
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Girish" <anonymous@.discussions.microsoft.com> wrote in message
news:CDF77CFE-DD12-4E3F-97E2-6D9FE33B15D0@.microsoft.com...
> Folks,
> Since Friday i am seeing High CPU Spikes on our app / db server. the Sql
server process is currently consuming about 60+ % cpu. There is not much
going on at the database level ? I am not sure what is causing the spikes.
> This is really abnormal behaviour on the this server.
> Please any help would be really appreciated
> Thanks
> Girish

CPU 100%

Hello
We have running our server-client application on line. The server Win2000
is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
the server.
What do you suggest me to solve or to mitigate this matter?
THXSHello Harvey,
Are you still able to connect to SQL Server? If you can, connect to SQL
Server using Query Analzyer as a sysadmin. Execute the command 'sp_who2
active'. This will show you all the active connections in SQL Server.
That will give you an idea which connection(s) is consuming all the CPU
time.
I also recommend reviewing the following article on using Profiler and
Performance monitor to troubleshoot performance issue.
HOW TO: Troubleshoot Application Performance with SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;224587
Hope this helps.
Yih-Yoon Lee
Harvey Triana wrote:
> Hello
> We have running our server-client application on line. The server Win2000
> is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
> sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
> the server.
> What do you suggest me to solve or to mitigate this matter?
> THXS
>|||Thanks Yih-
Saludos.
"Yih-Yoon Lee" <yihyoon.online@.gmail.com> escribió en el mensaje
news:%23dfb302%23EHA.2112@.TK2MSFTNGP14.phx.gbl...
> Hello Harvey,
> Are you still able to connect to SQL Server? If you can, connect to SQL
> Server using Query Analzyer as a sysadmin. Execute the command 'sp_who2
> active'. This will show you all the active connections in SQL Server.
> That will give you an idea which connection(s) is consuming all the CPU
> time.
> I also recommend reviewing the following article on using Profiler and
> Performance monitor to troubleshoot performance issue.
> HOW TO: Troubleshoot Application Performance with SQL Server
> http://support.microsoft.com/default.aspx?scid=kb;en-us;224587
> Hope this helps.
> Yih-Yoon Lee
> Harvey Triana wrote:
>> Hello
>> We have running our server-client application on line. The server
>> Win2000 is showing (Tasks Administrator) use of CPU 100%. SQL Server
>> (process sqlservr.exe) is the great consumer of memory. I am afraid a
>> collapse in the server.
>> What do you suggest me to solve or to mitigate this matter?
>> THXS|||"Harvey Triana" <harveytriana@.hotmail.com> wrote in
news:uauwev1#EHA.1564@.TK2MSFTNGP09.phx.gbl:
> Hello
> We have running our server-client application on line. The server
> Win2000 is showing (Tasks Administrator) use of CPU 100%. SQL Server
> (process sqlservr.exe) is the great consumer of memory. I am afraid a
> collapse in the server.
Run a trace to find out what SQL Server operations are running. Do you have
the likes of autoshrink on? Are you indexes correctly defined? Are
statistics upto date?|||Also make sure you are on SP3 or better... If the proc is 100% consistently
( even with no users), that is an indicator of the slammer virus...
Otherwise, look for big queries as the others have suggested.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Harvey Triana" <harveytriana@.hotmail.com> wrote in message
news:uauwev1%23EHA.1564@.TK2MSFTNGP09.phx.gbl...
> Hello
> We have running our server-client application on line. The server Win2000
> is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
> sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
> the server.
> What do you suggest me to solve or to mitigate this matter?
> THXS
>|||ok Wayne
...
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> escribió en el mensaje
news:ewix%23f9%23EHA.208@.TK2MSFTNGP12.phx.gbl...
> Also make sure you are on SP3 or better... If the proc is 100%
> consistently
> ( even with no users), that is an indicator of the slammer virus...
> Otherwise, look for big queries as the others have suggested.
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Harvey Triana" <harveytriana@.hotmail.com> wrote in message
> news:uauwev1%23EHA.1564@.TK2MSFTNGP09.phx.gbl...
>> Hello
>> We have running our server-client application on line. The server
>> Win2000
>> is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
>> sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
>> the server.
>> What do you suggest me to solve or to mitigate this matter?
>> THXS
>>
>

CPU %usage restriction for a process

Is there a way to restrict a certain process from using 100% of the CPU time.

Is it possible to control how much % CPU time a process can use?

For e.g. there is a process that generates OLAP Cubes and is using 100% of the CPU time. This just completely stops me from doing anything else on the server or the database. I would like to allocate a designated percentage of CPU time to this process (or all processes), or even make it run in the background as a lower priority process, so it is possible to work on the database. Is it possible to achieve this?You could try finding the process in the Windows Task Manager on the Server.

Once you have located the process right click on it and set the priority to BelowNormal or Low. I've never tried this but it sounds reasonable.

Also, if you have a multi-processor system you could change the Servers properties in EM by telling the system to use only a specified number of processors as opposed to all of them which is the default.

As with anything plan for the worst - create backups and test it first before trying it on the live server.|||Thanks for your reply.

Windows task manager does not show me the processes running inside the database. It shows only one SQL server process running. Now only if Enterprise Manager will let me do that......

I like your idea about the processors. But the change would be a global change for all the processes. Is there a way to do it only for a particular process? i.e. the rest of the processes can use all the CPU while a chosed process can use only one CPU?|||Enterprise Manager is a snap-in to the Microsoft Management Console and you should be able to find it in task manager as mmc.exe. Just be aware that other snap-ins may be running as well as the one for EM. To be sure you might want to close any other instances of mmc so you can select the correct one.

Your right about the processor change - it's global. I am not aware that you can set a particular process to use just a single CPU but hey who knows. Anyone out there have an answer?

Wish I had a better solution.

Best Regards|||Resource management for a processes is deal on MS SQL Server engine. ... and Windows also. You can't change priority for one process only.

Coverting Int to DateTime Recommendation?

Friends,

I have a database that I am in the process of converting to a new application. All the dates are currently stored as an numeric YYYYMMDD format. I am hoping someone can suggest the best way of dealing with these in SQL Server. Should I convert them to DateTime formats for example, and what is the best way to do this?

Any input would be greatly appreciated.

J.H.

yes you should do it.

the best way?

this is how I would do it -

create function YYYMMDDToDate(@.date int)
returns datetime
as
begin
declare @.datestring char(8)
set @.datestring = cast(@.date as char(8))
return convert(datetime, substring(@.datestring,1,4) +'-' +
substring(@.datestring,5,2) +'-' +
substring(@.datestring,7,2), 127)
end
go
alter table affectedtable add newDateColumn datetime;
go
update affectedtable
set newDateColumn = dbo.YYYMMDDToDate(oldDateColumn);
alter table affectedtable drop column oldDateColumn;
exec sp_rename 'dbo.affectedtable.newDateColumn', 'oldDateColumn', 'COLUMN';

|||

The trick is that MSSQL recognizes dates in YYYYMMDD format unambigously. So may just convert it twice as:

select cast(cast(20060104 as varchar) as datetime)