Tuesday, September 09, 2008

SQL Server Statistics

Today I found a problem with indexed view performance getting worse over time. This is another post I am creating mostly for myself, but this may be useful for others since it was pretty hard to find all the details on the web. One of the most important factors to SQL Server performance is up to date statistics. Using automatic statistics (enabled by default) does a good job, but we found the need to run sp_updatestats during periods of low activity. We use a Prestonia based server (waiting for Nehalem) for a 5 GB database with 100+ active users, so every bit of performance helps. Automatic statistics and sp_updatestats handles everything well except indexed views.

sp_updatestats ignores indexed views. Microsoft claims this is because statistics on indexed views are ignored except when using the WITH (NOEXPAND) hint. You need Enterprise Edition to use indexed views without the (NOEXPAND) hint, so the only explanation I can think of for this behavior is somehow Microsoft thinks screwed up statistics will sell more Enterprise licenses. If they sell more of anything, it will be Oracle licenses. I have never seen automatic updates work on indexed views either, although I haven't seen this documented.

The result of this is that indexed view that sped everything up when you created it is getting further out of sync with its statistics. Eventually this leads to horrible execution plans that just keep getting worse. The solution, first you should run:

SELECT so.name,
si.rowmodctr,
si.rowcnt,
CASE WHEN si.rowcnt > 0 THEN 100.0 * si.rowmodctr / si.rowcnt ELSE 0 END PercentError
FROM sys.objects so
INNER JOIN sys.sysindexes si ON si.id = so.object_id
WHERE is_ms_shipped = 0 AND indid = 1
ORDER BY 4 DESC

Running this periodically will show you the state of all clustered index statistics, including indexed views and regular tables, and is a good thing to pay attention to regardless of if you have Enterprise edition or use indexed views.

You will need to update stats for each indexed view similar to 'UPDATE STATISTICS vAcBalanceBill', I have not found another way to do this. Setting this up in SQL Server Agent mostly takes care of the problem.

0 Comments:

Post a Comment

<< Home