Muller Jannie

About myself and everyone else.

Auto Create Statistics

So I had a fun experience today with SQL.

I’ve known off a single bad performing query which is due to the fact that the query runs on a table and filter on a column in the where clause which happens not to be indexed. THe reason for this is that this is and old legacy DB that comes from SQL 2000 believe it or not. So I’ve been puzzled what to do for performance in this regard.

Usually I like to quickly grab an overview of the object by doing a highlight + alt F1 which reveals the structure of the table which is handy to see column data types and lengths. You can use it to see the indexes on the table and you can also use it to have a look at the FK constraints on the table. I’ve had an interesting experience with that aswell but that is another post.

This time I happened to use the old sysindexes compatibility view which is replace by sys.indexes and sys.stats respectively.

And there it was.

I found the somehow SQL server had manage to create 243 auto created stats (this is a db option) on the table. Besides the obviously high number the thing that bugged me is that the table on had 87 columns. This is not a super wide table by any standard but to have two stats on the same column doesn’t make sense.

To give you some background on the topic is to note that the server does have an update stats job which soldiers on in the evenings, there is also an index rebuilt job for indexes over 50%. Both of these jobs run as they are expected to.

After dropping the stats , all 243 of them the queried performed better, interesting enough around 5 minutes later seven of the columns had a new set of stats created. According to BOL the reason for the stats is when it’s a requirement to fulfill a predicate in a query. This makes sense but again why two on the same column. THe other assumption knowing the workload on the server is that there is a good amount of poor parameterization, using exec instead of sp_executesql and dynamic sql instead of params. There are some pretty good reasons why not to use exec alone.

In the meantime I have disabled auto create of the stats. I’ve also created a script in order to remove stats. You can perhaps look at the ratios on your box for interest. The poor performance was obviously due to out of date stats. Why this wasn’t picked up by the sp_updatestats I’m not sure (yet).

select
‘DROP STATISTICS ‘ +  object_name(a.object_id) + ‘.’ + name ,
‘CREATE STATISTICS ‘ + NAME +  ‘ ON ‘ + object_name(a.object_id) + ‘ ( ‘ + col_name(a.object_id,column_id) + ‘)’, * from sys.stats_columns a
inner join   sys.stats  b
on a.object_id = b.object_id
and a.stats_id = b.stats_id
where auto_created = 1

You can run this to give you info on your database.

What I expected to find was a correlation between missing indexes and the stats. However the dmv for the missing indexes are pretty slim and they don’t compare. At this stage I can assume that it may be due to the parameterization and ad hoc code (not a stored proc). Most of the code is build on the fly and use oledb cursors to query the data.

The quick win for now, remove the stats and disable auto create. Remember that if you are on the latest SP , SQL knows best and I’m disabling this in order to diagnose if I can add missing indexes and whether the performance is slowly degrading etc. In order to draw a conclusion.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Information

This entry was posted on September 13, 2011 by in SQL Server and tagged , .
%d bloggers like this: