Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a SQL Server 2005 that has become unpredictable of late, and I'm scratching my head as to why. Queries that executed in seconds are changing plans and taking minutes (taking the time in full table scan or index spool). Now the first and most obvious thing is, the statistics are obsolete causing the optimizer to get confused but I am convinced this is not the case - firstly because the underlying data isn't significantly changing (e.g. adding one day's data ontop of a year's data already in a table) and secondly because Auto Create Statistics and Auto Update Statistics are both true. However the optimizer is getting confused; running the SQL in the Tuning Advisor gives me lots of multi-column CREATE STATISTICS statements which do seem to fix it (until the next bit of SQL misbehaves).

Any ideas of a strategy I can use to approach root-causing this? Any why the "normal" statistics aren't sufficient?

share|improve this question
add comment

3 Answers 3

up vote 7 down vote accepted

If your top wait is SOS_SCHEDULER_YIELD, then it would appear you have some pressure on CPU. But this could be a result of something else, such as your design no longer being sufficient for your queries. I know you said that you are only adding one day's worth of data, but you could have hit a tipping point.

How are your queries being issued? Is it dynamic SQL? Are you using stored procedures? Are you using sp_executesql? Is it possible that you have a case of parameter sniffing? What does your db design look like? What are the PK and FK relationships?

Do you have an example of a good plan? If you are able to determine a good plan, you could use plan guides to force the query to execute in a specific way.

Can you give an example of a good plan gone bad?

Lastly, go grab a copy of sp_whoIsActive ( http://sqlblog.com/blogs/adam_machanic/archive/2010/10/21/who-is-active-v10-00-dmv-monitoring-made-easy.aspx ) from Adam Machanic and use that to determine more about the queries that are running. And if you want to be able to capture the output from sp_whoIsActive, go here http://www.littlekendra.com/2011/02/01/whoisactive/

share|improve this answer
    
It's a third party application, I have no control over its schema or SQL, which is pretty horrible, lots of parameterized queries (e.g. where col=(cast @var...)) and @var might be '%'. I just inherited it a week or two ago, and need to keep it basically working until it's replaced. Thanks for the link, I'll give it a whirl. –  Gaius Mar 13 '11 at 10:25
    
Next biggest wait after SOS_SCHEDULER_YIELD was CXPACKET and sp_configure "max degree of parallelism", 1 appears to have - for now - knocked both problems on the head. Thanks! –  Gaius Mar 16 '11 at 11:46
    
+1 for the link to sp_whoIsActive –  Jeff Mar 24 '11 at 14:13
add comment

From MSDN:

"Insert Operations Occur on Ascending or Descending Key Columns Statistics on ascending or descending key columns, such as IDENTITY or real-time timestamp columns, might require more frequent statistics updates than the query optimizer performs. Insert operations append new values to ascending or descending columns. The number of rows added might be too small to trigger a statistics update. If statistics are not up-to-date and queries select from the most recently added rows, the current statistics will not have cardinality estimates for these new values. This can result in inaccurate cardinality estimates and slow query performance.

For example, a query that selects from the most recent sales order dates will have inaccurate cardinality estimates if the statistics are not updated to include cardinality estimates for the most recent sales order dates.

After Maintenance Operations Consider updating statistics after performing maintenance procedures that change the distribution of data, such as truncating a table or performing a bulk insert of a large percentage of the rows. This can avoid future delays in query processing while queries wait for automatic statistics updates."

You might use "EXEC sp_updatestats" from time to time on your system (scheduled some time) or use the function STATS_DATE on all the objects and see when their statistics were actually updated last time and if there was too much time since then, use UPDATE STATISTICS for that particular object. In my experience, even with Automatic statistics enabled we're still forced to update stats from time to time, because of insert operations that didn't trigger automatic update.

To add my personal code (used in a weekly job that builds dynamic statements for stats update):

select distinct
        'update statistics [' + stats.SchemaName + '].[' + stats.TableName + ']'
            + case when stats.RowCnt > 50000 then ' with sample 30 percent;'
            else 
                ';' end
        as UpdateStatement
    from (
        select
            ss.name SchemaName,
            so.name TableName,
            so.id ObjectId,
            st.name AS StatsName, 
            STATS_DATE(st.object_id, st.stats_id) AS LastStatisticsUpdateDate
            , si.RowModCtr
            , (select case si2.RowCnt when 0 then 1 else si2.RowCnt end from sysindexes si2 where si2.id = si.id and si2.indid in (0,1)) RowCnt
        from sys.stats st
            join sysindexes si on st.object_id = si.id and st.stats_id = si.indid
            join sysobjects so on so.id = si.id and so.xtype = 'U' --user table
            join sys.schemas ss on ss.schema_id = so.uid
    ) stats
    where cast(stats.RowModCtr as float)/cast(stats.RowCnt as FLOAT)*100 >= 10 --more than 10% of the rows have changed
    or ( --update statistics that were not updated for more than 3 months (and rows no > 0)
        datediff(month, stats.LastStatisticsUpdateDate, getdate()) >= 3
        and stats.RowCnt > 0
    )

Here I get all objects where didn't have stats updated for more then 3 months or since the last stats update it had more than 10% of the rows changed.

share|improve this answer
    
Hmm, my top wait event is SOS_SCHEDULER_YIELD but I can't tell right now if that's due to the bad plans, or that this (6-year-old, 2-processor, 4G RAM) box is really just overloaded now and I've gone over some tipping point. –  Gaius Mar 12 '11 at 13:23
    
rather than just running that query to make the UPDATE statements and running them manually, you could use a cursor based on that select statement to loop round the results running them using calls to sp_executesql - that way you can run it automatically (for instance as part of an overnight (or other quiet period) maintenance plan). –  David Spillett Mar 12 '11 at 15:35
    
@David: this is what I'm doing in the weekly job :). I've just formatted it differently for Gaius to see the output I'm using. The initial script was way too ugly and long. Thanks for the help with formatting! Can you send me to a formatting tutorial..because I don't really know how to make the code look nice here. Thanks! –  Marian Mar 12 '11 at 15:56
    
there is a "formatting help" link on the "edit answer" screen, and as an icon to the right above the initial answer box on the main question page, that lists the markdown syntax supported by these sites. –  David Spillett Mar 12 '11 at 21:15
3  
Auto update statistics actually triggers at 20%+500 rows, not 10%. –  mrdenny Mar 12 '11 at 23:34
show 1 more comment

My guess is that one or more of your tables are getting large enough that they are not hitting the 20% of changes needed to help mark that current statistics as stale so that the Auto Update Statistics will kick in and yet there's enough updates (or inserts) that having updated statistics would help out a lot. I found the same thing recently in a particular environment after upgrading from SQL 2000 to SQL 2008.

In addition to the other sites mentioned in the answers above, I would suggest checking out the following online resources.

1) Red-Gate has a number of free ebooks available for download including "SQL Server Statistics" by Holger Schmeling, where you'll find the following quote:

http://www.red-gate.com/our-company/about/book-store/

"tables with more than 500 rows at least 20% of a column’s data had to be changed in order to invalidate any linked statistics"

2) SQL Sentry has a free Plan Explorer tool that helps track down issues within a SQL plan, such as an estimate of too many or too few rows compared with the actual number of rows for a given table in a query. Just save the actual execution plan from SSMS and then walk through the different parts of the plan using Plan Explorer. It's not that the information isn't available in SSMS using the graphic execution plan, but the tool from SQL Sentry does make it much easier to see.

http://www.sqlsentry.com/plan-explorer/sql-server-query-view.asp

3) Check out the stats update date yourself for tables in the queries you are most interested in using STATS_DATE(), you can find a quick query to get the oldest stats using a query found in the following discussion.

http://blog.sqlauthority.com/2010/01/25/sql-server-find-statistics-update-date-update-statistics/

I hope this helps!

I think you'll especially enjoy the book from Red-Gate!

-Jeff

share|improve this answer
    
Thanks, I'll work my way through those. I am mainly an Oracle DBA who has inherited this system (tho' I am not prejudiced against SQL Server at all, from what I see since 2005 it's a very capable platform, I just don't know it as well as I know Oracle). –  Gaius Mar 13 '11 at 10:27
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.