Today, I worked on a service request that our customer reported that the statistics update maintenance plan is taking too much time. This is causing that all these operations (rebuild and update statistics) are taking more time that the maintenace windows that they have. I would like to share with you my findings how to reduce this time.
I found we're updating too many statistics and we worked in two main operations:
We have the following table:
CREATE TABLE MyData (ID INT IDENTITY(1,1) PRIMARY KEY, [Name] VARCHAR(80))
INSERT INTO myData ([name]) valueS(replicate('Y',70));
GO 1000
INSERT INTO myData ([name]) valueS(replicate('M',70));
GO 1000
INSERT INTO myData ([name]) valueS(replicate('C',70));
GO 1000
INSERT INTO myData ([name]) valueS(replicate('A',70));
GO 1000
if our application runs any query using the column name, for example, SELECT * FROM MyData where [name]='YMCA', we are going to see that an automatic statistics has been created for this column called _WA_Sys_00000002_2116E6DF
Futher, if based on performance needs you need to create an index in this table based on this column, you are going to have the following situation:
CREATE INDEX MyData_ix1
ON MyData
(
Name
)
2 statistics: One related with user/automatic statistic and other one related with index.
So, dropping the unnecessary automatic statistics, our customer reduces time for updating the statistics. In this case, my lesson learned here, is check if you have an existing user/automatic statistics to avoid this scenario and check your script to avoid this situation.
Enjoy!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.