Problem Table Query

[rsai]

A query which we run daily for the last two months and which took 3-4 minutes has over the last week jumped to a 33 minute run time. When the query was run the system was not working particularly hard and most of the rest of the system was performing OK. I pinned it down to a particular table used in the query by running other simple queries against it which likewise performed poorly. The table has 33 million rows but grows at a steady rate. I recreated a couple of the indexes which were being used in the query but to no effect. As a last resort I copied the table to a new table, recreated all the indexes, renamed the old table and renamed the new table as the old table. This did the trick and the query is back running at 3 minutes.

Any ideas as to what could have gone wrong with the original table over the last week to cause such a drop in performance? I did notice when I was getting the details of the indexes to recreate on the new table that the check box - Do not recompute statistics (not recomended)- had been ticked, could this have been the culprit. I'd like to prevent this happening again so any thoughts will be gratefully received.

 

[SQLSister]

Yes, not maintaining the statistics will make the the execution plan become inefficient according to Books online. Since this is such a large table, auto-update of the statistics was probably turned off becasue of the time associated with maintaining the statistics. More than likely an insert or update was timing out and somebody found that it wouldn't with the option of recomputing statistics turned off and didn't think through how that might affect processing down the line. BOL also says:
"If you instruct SQL Server not to maintain statistics automatically, you must manually update the statistical information."

As I see it you can choose to ignore the problem and it will eventually return, you can turn on auto update of statistics realizing that something else may (and probably will) break down, you can schedule a job to manually update statistics during the off peak hours, or you can turn on the statistics with the use of sampling rather than full scan in creating the statistics.

I don't know what your query is doing but even the normal 2-3 minutes is a long time for a query to run, it may also need optimizing. Or your hardware configuration may need looking into to optimze the accessing of data. You have an awful lot of records, hopefully they are partitioned across multiple drives with the indexes and transaction logs on separte drives with separte drive controllers. If not you should look into this. Also, you might think if you really need to to keep that many records in your active table. Maybe inactive records could be moved to an archive table or a data mart to improve performance. Not knowing what kind of data your are storing or what you need to do with the data, it is hard to say if this is what you need to do, but you should at least consider it.

 

[tlbroadbent]

Indexes do become fragmented over time. Statistics can get out of date. Auto updating statistics is a good idea. Rebuilding indexes if the table has a lot of insert activity is a good idea. You may want to consider deframenting the indexes rather than rebuilding on a large table.

If you rebuild indexes, the statistics will be updated unless you specify that stats should not be updated. Rebuild the clustered index if it exists and all non-clustered indexes will be rebuilt. If there is no clustered index make sure you rebuild all indexes that are fragmented.

I created a procedure to reindex fragmented indexes. You can get the code at...

http://tlbroadbent.home.attbi.com/sql/reindexlowdensity.txt

If you use a stored procedure in the query, you may want to recompile it. Query plans can become outdated and inefficient as data distribution changes. Though this does not appear to be the case with your query. If you want to get the best answer for your question read faq183-874 and thread183-468158.


Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[rsai]

Thanks for the feedback, I will let the system auto update the statistics for the time being and see how it gets on. I don't think this will cause a problem elsewhere as I have 10-12 other tables of the same size (this table is one of a number produced by splitting a very wide table down into tall thin tables to improve query time, so they all have the same key fields) which are updated at the same time and are updating statistics, which don't have a problem during updates. This was the only table with the "Do not recompute statistics (not recomended)" flag set on the indexes. The thing that puzzles me is that this must have been set this way for at least 3-4 months, being the last time anybody but myself would have made changes to the database and yet the problem only manifested itself last week. Also we have a maintenance job which rebuilds all the indexes each Sunday, last Sundays job seemed to run OK without any errors.
The database is a reporting database and for the amount of data we are returning 2-3 minutes is accepatble, we have spent a long time optimising both the data structure and the spreading of the tables & indexes over the disks.We have looked at splitting these large tables by time ( dividing by year or month into smaller tables) but this does not suite the way the users wish to access the data and our reporting strategy, I think were stuck with a high row count.

Thanks again