SQL Slow after use UPDATE STATISTICS ..Why??
-
One week before we move the SQL Server 2000 from one server to another server.
Following are the SQL version of New server
Sql : Microsoft SQL Server Ent. 2000 - 8.00.2040
OS new : Windows 2003 R2,
OS Old : Windows 2003,
Installation : Cluster server installation.
After DB move, all sql statement are responding very Slowly in new server
We tried DBCC DBREINDEX ,sp_updatestats,Peformance Monitor…. to trace and I shows that full scan is more..
At last , we reach the following consolation.
1.If we re- create the Index sql is working fine. But if we run UPDATE STATISTICS , system respond very slow ( Around 20 times slower)
Following are my question:
Why SQL is slow if we update the statistics of the table?
Can I delete all the table statistic created by the system ?
Can I delete all views crated by Index Tunning Wizard?
Pls : This db running last 8 years all statistics and indexed views exists in the system.
-
Would advice to check the hardware related counter to determine bottleneck as listed below if you have not checked.
How good is DISK IO ? Check the counters DISK sec/read and DISK sec/write in perfmon tool
Is the Avilable Memory < Total Commited Memory ?
If the 32 bit is it paging a lot in new server ?
How many hops to new server from application ?
Cheer Satish 🙂
-
If updating the statistics slows down the performance of the query, it's likely because a new execution plan was created based on the updated statistics. You'll need to look at the plans before & after the statistics update to understand exactly why. Usually, in most circumstances, the reverse is true, out of date statistics leads to poor performance. But sometimes this does happen. The exact solution depends on what's causing the problem within your query and based on what's been posted so far, I can't be sure. It might simply be bad parameter sniffing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
When you re-create indexes, STATISTICS will have 100% accurate data, but when you run just UPDATE STATISTICS without specifying FULLSCAN or SAMPLE (percent|row), sample percentage may be less than 100% depending on how much data space table has occupied. Lower the data space size, higher the sampling percentage.
In your case, if you run DBCC SHOW_STATISTICS for a specific index, will show you total number of rows and sampled rows. Sampling reduces overhead of data scans during UPDATE STATS operations and sometimes reduces accuracy of data as well.
Just try UPDATE STATS with FULLSCAN (or SAMPLE 100%) and should give you performance as similar to re-index operation
-
Satish Nagaraja (6/4/2012)
Would advice to check the hardware related counter to determine bottleneck as listed below if you have not checked.How good is DISK IO ? Check the counters DISK sec/read and DISK sec/write in perfmon tool
Disk Read/Sec for data disk = average : 5-50 , Maximum 1605
Disk Read/Sec for Temp DB disk = average : 30- 40, Maximum 1605
if sort of hevy load
Disk Read/Sec for Temp DB disk = average :500-1000, Maximum 4000
Is the Avilable Memory < Total Commited Memory ?
avilable memmory bytes : 7200000000
Total commited memmroy :1.569
If the 32 bit is it paging a lot in new server ? Page- Fault /sec : 500-1000 maximum : 10000
page/sec : 129, max : 8912
How many hops to new server from application ?
Mathew
-
I have to agree with the above post. If your response times are good after a rebuild of the indexes and then falls off when you do an UPDATE STATISTICS, it comes down to sampling. An index rebuild does a full scan when rebuilding statistics. If you don't specify FULL SCAN when you do an UPDATE STATISTICS, SQL Server sets a sampling rate which may not provide you with the detailed information needed to provide an appropriate execution plan for some of your queries.
-
Dear
please see the following picture, execution plan ..I am not sure, how to upload full execution plan to this forum.
-
Dear Daxesh Patel,
You are correct. If i run "update statistics table " and check DBCC SHOW_STATISTICS, Row sampled is only 20% of actual rows.
If database "auto update statiscs" enabled, again it go back to slow performance.
Can we disable "auto update statistics" of database.?
If so, how the system will get the new statists? Is it good to schedule update statistics as job and run every day?
Thanking you
Regards
Mathew
-
Mathew M.Varghese (6/4/2012)
Dear Daxesh Patel,You are correct. If i run "update statistics table " and check DBCC SHOW_STATISTICS, Row sampled is only 20% of actual rows.
If database "auto update statiscs" enabled, again it go back to slow performance.
Can we disable "auto update statistics" of database.?
If so, how the system will get the new statists? Is it good to schedule update statistics as job and run every day?
Thanking you
Regards
Mathew
In general, the auto update statistics works fine. For those few tables it doesn't, you may want to schedule at least a nightly job to update those statistics using a full scan.
-
Mathew M.Varghese (6/4/2012)
Dear Daxesh Patel,You are correct. If i run "update statistics table " and check DBCC SHOW_STATISTICS, Row sampled is only 20% of actual rows.
If database "auto update statiscs" enabled, again it go back to slow performance.
Can we disable "auto update statistics" of database.?
If so, how the system will get the new statists? Is it good to schedule update statistics as job and run every day?
Thanking you
Regards
Mathew
You can have a maintenance job that updates stastics of tables to have better control on it.
For problem tables/indexes you can specify higher percent in SAMPLE option. e.g. try with 30% then 40%.... and see if you acheive performance or not.
I would not recommend FULLSCAN since it will slow down you UPDATA STATISCTICS operation.
And yes, you can disable "auto update statistics" but you don't have to if you are running maintenance job at required frequncy.
-
Yes I agree with Lynn Pettis, usually update statistics works fine. For specific issues you can use sql job.
-
Mathew M.Varghese (6/4/2012)
Dearplease see the following picture, execution plan ..I am not sure, how to upload full execution plan to this forum.
Right click the plan, choose "Save As" and create a *.sqlplan file. That's what we need.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Hi,
I tryed to save as "execution plan", but it is not working in sql server 2000 version.
Regards
mathew
-
Dear All,
Thanks you for all your support and help. I schedule the update statistics with Full scan and now system is working fine.
DB- Auot update the statistics was disabled. Now i enable it ...
I hope this will not create any major issues
Once again thanks for all the support.
Regards
Mathew
-
Mathew M.Varghese (6/5/2012)
Hi,I tryed to save as "execution plan", but it is not working in sql server 2000 version.
Regards
mathew
Oh. You're posting in the SQL Server 2008 forum, so answers might be oriented towards 2008. You're right, you can't do that in 2000.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply