April 8, 2008 at 12:25 pm
I just came off a phone call with Microsoft.
We had a severe performance issue with our SQL 2000 database. After running "UPDATE STATISTICS tblName WITH FULLSCAN" on a large table (10,000,000 rows), the problem was fixed!
The (new) thing I learned today is that, although our database stats was up-to-date, the sampling for the update was inadequate. Running the following command on the indexes of the large table, gave a sampling percentage of only 1% of all rows:
DBCC SHOW_STATISTICS (tblName, idxName);
output:
-------
rows: 10,000,000
rows sampled: 100,000
Given what I now know, I would like to implement a procedure of updating the stats of all tables in the database WITH FULLSCAN on a rotational basis.
Anyone have any scripts/advice on doing this?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 8, 2008 at 7:49 pm
How about this one,
sp_msforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'
April 9, 2008 at 10:40 am
Thanks, that's a great way of doing this, but I'm wondering, is it useful to apply this to all tables or just the larger ones? Any hints as to what the size of a table should be above which 'FULL SCAN' stats updates are beneficial?
We do update stats on a nightly basis, but not WITH FULLSCAN. I learned yesterday that not using FULLSCAN with update-stats on large tables can create severe performance problems! That had never occurred to me before...
(...although it makes perfect sense now after the fact)
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 9, 2008 at 3:01 pm
Try this, if you do not like FULLSCAN,
EXEC sp_msforeachtable 'UPDATE STATISTICS ? WITH SAMPLE 20 PERCENT'
October 11, 2008 at 1:45 pm
This is what I did on SQL 2000 to create a script that I divided up so that I did not run it all at once:
SELECT 'UPDATE STATISTICS ' + name + ' WITH FULLSCAN'
FROM sysobjects
WHERE objectproperty( id, N'IsMSShipped' ) = 0
AND objectproperty( id, N'IsTable' ) = 1
ORDER BY name
October 11, 2008 at 2:05 pm
Marios Philippopoulos (4/9/2008)
Thanks, that's a great way of doing this, but I'm wondering, is it useful to apply this to all tables or just the larger ones? Any hints as to what the size of a table should be above which 'FULL SCAN' stats updates are beneficial?
Generally it's applicable to large tables, but not all large tables. Normally the sample % adjusts for the size of the table. Sometimes that sampling can miss certain (important) values.
Certainly large tables that have wide variations in the frequency of important values (say where 70% of one column is one value and the other 30% are 100 different values)
Usually I just leave the auto-update to do it's job and only have troublesome tables updated daily. Troublesome would be ones where the 20% threshold for auto update is too large, or where I've picked up that the sample rate isn't enough.
If you've got query performance monitoring, you can usually pick up from that queries that perform erratically. The main symptom of a stats problem is that the query's actual exec plan shows (for certain operators) a large discrepancy between estimated and actual rows. (taking into account that estimated is per execution of the operator and actual is overall)
You can also check the stat's histogram and compare that with the actual distribution of data. It has to be done table-by-table and column-by-column, but it will show you where there are problems.
One other thing - an index rebuild will update that index's stats with fullscan.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 15, 2009 at 11:49 am
Hi All,
I am vijay , SQL DBA.. I am facing one problem with update statistics.. can any one help me out of this..
Below is the error i am getting...
Failed:(-1073548784) Executing the query "UPDATE STATISTICS [dbo].[PDE_RFEQP_MVMNT_EVENT_DN]
WITH FULLSCAN
" failed with the following error: "A severe error occurred on the current command. The results, if any, should be discarded.
A severe error occurred on the current command. The results, if any, should be discarded.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
September 15, 2009 at 11:53 am
vijay_shanthi23400 (9/15/2009)
Hi All,I am vijay , SQL DBA.. I am facing one problem with update statistics.. can any one help me out of this..
Below is the error i am getting...
Failed:(-1073548784) Executing the query "UPDATE STATISTICS [dbo].[PDE_RFEQP_MVMNT_EVENT_DN]
WITH FULLSCAN
" failed with the following error: "A severe error occurred on the current command. The results, if any, should be discarded.
A severe error occurred on the current command. The results, if any, should be discarded.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
You need to run this on the database:
UPDATE STATISTICS dbName WITH FULLSCAN;
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 15, 2009 at 12:13 pm
On one of the larger dbs (around 150 GB), I had the need to do reindexing/defrag and update stats on a daily basis. I used Michelle's script[/url] (one of the best I have seen) and modified it to suit my needs of logging and maxdop etc...
then i had to get to the stage of update stats. One thing to note here is that stats are updated for reindexed columns, but not for the ones that got defragged. then there are other stats that are created by the system (and also we had created some by recommendations from DTA) . These extra stats and the indexes that were picked up for a defrag had to be updated with a full scan.
I created the script on these lines
1. From the logging tables, find out index ID, table Id etc that got reindexed.
2. Find defraged indexes and pick their index ids.
3. Find StatNames defined for a particular table. You could get that from sys.stats
4. figure out a pool of candidates for your Update stats adventure.
5. run the update stats with full scan for each of those.
I can post the script if anybody is interested in having a look.
Hope this helps
--- OOPS WRONG GROUP ..... ASSUMED IT TO BE SQL 2005 .... If you could find a systable in SQL 2000 which has the stats info, then something like i Outlined might be achievable
-------------------------------------------------
-Amit
Give a man a fish and he'll ask for a lemon. Teach a man to fish and he wont get paged on weekends !! :w00t: - desparately trying to fish [/size]
September 16, 2009 at 8:24 am
If you have a sufficiently long maintenance window I see no reason not to do the update stats with fullscan every time. I don't think it can make things WORSE! 😀
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 10, 2011 at 1:07 am
Hi,
The things you mentioned looks good. Does it mean that Update Stats with Full Scan is not needed for the Indexes that were rebuilt?
Can you pl provide the script that you are using (The Michelle's script)?
Thanks
August 10, 2011 at 8:18 am
dheeraj gupta (8/10/2011)
Hi,The things you mentioned looks good. Does it mean that Update Stats with Full Scan is not needed for the Indexes that were rebuilt?
Can you pl provide the script that you are using (The Michelle's script)?
Thanks
Statistics are automatically updated when an index is rebuilt, so, yes, no need to run "Update Stats with Full Scan" on those indexes.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 10, 2011 at 8:30 am
dheeraj gupta (8/10/2011)
Hi,The things you mentioned looks good. Does it mean that Update Stats with Full Scan is not needed for the Indexes that were rebuilt?
Can you pl provide the script that you are using (The Michelle's script)?
Thanks
Please not 2 years old thread.
http://sqlfool.com/2011/06/index-defrag-script-v4-1/
That version doesn't update the rest of the stats (was a bug earlier, not fixed yet).
August 10, 2011 at 8:33 am
Marios Philippopoulos (8/10/2011)
dheeraj gupta (8/10/2011)
Hi,The things you mentioned looks good. Does it mean that Update Stats with Full Scan is not needed for the Indexes that were rebuilt?
Can you pl provide the script that you are using (The Michelle's script)?
Thanks
Statistics are automatically updated when an index is rebuilt, so, yes, no need to run "Update Stats with Full Scan" on those indexes.
There's still a need to update stats for the stats outside the indexes. I also vote that if you have the window for it, just update all with fullscan. Then if you see it hurts 1 plan, take that one out.
Keep in mind that Gail's experience has been on banking 1+ tb system while mine has been on 100 GB and less with 10 hours update windows + week-ends.
August 11, 2011 at 6:31 pm
Thanks for quick response.
Then how to leave specifically for which statistics has been updated while rebuild the table indexes and update the stats for rest of the things?
Need it for not to waste maintainance time in updating the stats for which it has already been updated when it rebuilt.
Thanks.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply