December 31, 2011 at 5:59 am
Hi Everyone,
Hope all is well and eagerly waiting for the new year. I wish everyone a very Happy New Year.
I have an issue with my update statistics job. I have scheduled it to run daily @ 5:00 AM and it takes 4 hours to complete. I want to know why is it taking so long to complete and is it advisable to run update stats on a production environment daily? I am using sql server 2008 r2 standard edition on x64 bit machine.
Thanks Much.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
December 31, 2011 at 7:15 am
In your job are you using "UPDATE STATISTICS " or sp_updatestats ?
Are you defragmenting your indexes in that job as well ?
December 31, 2011 at 8:47 am
The time it takes depends on the options specified for update statistics (fullscan/sample), the speed of your IO subsystem, the amount of data to be read. 4 hours is indeed a lot, but let's have a mathematical approach to it:
4 hrs = 4*60*60 sec = 14400 sec. If your IO subsystem can handle 3200 IOps of 64kB, you have 14400*3200*64kB ~ 3TB of data read, which is not very unrealistic if you have a few large tables which has to be scanned. For virtual servers, you often see performance that is way, way worse than this, perhaps even as low as 200 IOps.
My best shot: Check if you can do with a sample of the data. I often find that even as low as 1 percent can do.
December 31, 2011 at 1:36 pm
Taking into account okbangas post above:
You might wish to minimize those tables whose statistics you are going to update by running the following. Then examining the results, and then selecting the tables to have their statistics updated where the percentage updated exceeds a value you determine by trail and error after observing the effect on response at your different selections.
CREATE TABLE #S (TableName VARCHAR(100),Rows DECIMAL(10,2), RowsAltered DECIMAL(10,2))
INSERT INTO #S
SELECT object_name(id)AS 'Table name',SUM(rowcnt) AS 'Rows in table'
,SUM(rowmodctr ) AS 'Rows added/deleted/updated'
FROM sys.sysindexes WHERE id > 100 AND rowmodctr > 0 GROUP BY id
--SELECT * FROM #S --for testing only
--The following lists all tables where rows have been altered/added/deleted and the percentage of rows effected.
SELECT TableName,[Rows],RowsAltered, CAST(RowsAltered/[Rows] AS DECIMAL(10,4))*100 AS 'Percent Altered'
FROM #S WHERE [Rows] > 0 AND RowsAltered > 0
--Using the Northwind DB
Results: (List shortened for this posting)
TableNameRowsRowsAlteredPercent Altered
Orders 1660.001660.00 100.0000
Products462.00539.00 116.6700
Region 4.004.00 100.0000
Territories53.0068.00 128.3000
Now beware that rowmodctr has changed from earlier versions of SQL Server, but should be accurate enough for your purpose.
January 2, 2012 at 7:29 am
Hi Ron,
I used the maintenance plan wizard to create this update statistics and I have selected only the required user databases. I am not rebuilding or reorganizing any indexes along with this.
Thanks
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
January 2, 2012 at 7:40 am
Hi okbangas,
I did not get an option of selecting the option of fullscan or sample for the update stats as it is part of a maintenance plan and since I am using a maintenance I am assuming this is does a fullscan. The user databases that I selected are hardly 150GB together. I have a delete job running just before the update stats job everyday on one of the user databases which is 110GB to purge data older than 30days. I am not sure if that has an impact on the job.
Thanks
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
January 2, 2012 at 7:57 am
Hi Ron,
Thanks for the script. That really helps a lot. The sum(rowmodctr) column is returning records more than 45000 for some tables. But is there a way to know which operation has been performed on a specific table? I meant if there was a delete or an update or an insert happened on that specific table? Also this column being a delta value, how much old data is this column returning?
Thanks much
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
January 2, 2012 at 10:25 am
Sapen (1/2/2012)
Hi Ron,Thanks for the script. That really helps a lot. The sum(rowmodctr) column is returning records more than 45000 for some tables. But is there a way to know which operation has been performed on a specific table? I meant if there was a delete or an update or an insert happened on that specific table? Also this column being a delta value, how much old data is this column returning?
Thanks much
In answer to your question. BOL states:
Counts the total number of inserted, deleted, or updated rows since the last time statistics were updated for the table
To test if the above is correct, please select one table, rebuild its statistics and then re-run the script. Should give you proof one way or the other as to the accuracy of the quoted statement.
January 2, 2012 at 10:41 am
ok...this is a daily running job and I also have the auto update stats enabled...is it a good practice to have it enabled while i have this job running daily??
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply