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.
>