Manage Database Statistics

STATS_DATE is a useful function in order to check when stats were last updated on an Index.

-- When were Statistics last updated on all indexes?
SELECT o.name, i.name AS [Index Name],
       STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date],
       s.auto_created, s.no_recompute, s.user_created
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON o.[object_id] = i.[object_id]
INNER JOIN sys.stats AS s WITH (NOLOCK)
ON i.[object_id] = s.[object_id]
AND i.index_id = s.stats_id
WHERE o.[type] = 'U'
ORDER BY STATS_DATE(i.[object_id], i.index_id) ASC;

If NO_RECOMPUTE has been turned on for some of the indexes in the database (including most of the more important tables), then it can lead to performance degradation as it no_recompute is basically turning off automatic statistics management.

In an OLTP workload, with very volatile tables, this is bad news.  That index-level setting overrides the database level Auto Update Statistics setting for each individual index. This is generally a bad thing to do in most cases.  An exception would be if you had an extremely volatile index that was changing so rapidly that auto update statistics could not keep up with the changes, and you decided to manually update statistics very frequently from an Agent job.

-- Find indexes with no_recompute turned on
SELECT o.name, i.name AS [Index Name],
       STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date],
       s.auto_created, s.no_recompute, s.user_created
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON o.[object_id] = i.[object_id]
INNER JOIN sys.stats AS s WITH (NOLOCK)
ON i.[object_id] = s.[object_id]
AND i.index_id = s.stats_id
WHERE o.[type] = 'U'
AND no_recompute = 1
ORDER BY STATS_DATE(i.[object_id], i.index_id) ASC;

The next step was to rebuild each of those indexes, setting STATISTICS_NORECOMPUTE to OFF. A sample of how to do this is shown below. Notice that the ONLINE option is turned on, which only works in Enterprise Edition.

-- Rebuild an Index with STATISTICS_NORECOMPUTE  = OFF
ALTER INDEX [IX_UserAccount_Active_UserID_UserName]
ON [dbo].[UserAccount] REBUILD
WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON,
ONLINE = ON, SORT_IN_TEMPDB = OFF);
GO

One alternative would have been to just change the property setting for that index, like you see in the example below. Rebuilding the index with the proper statistics no_recompute setting will also do an UPDATE STATISTICS WITH FULLSCAN, so I thought that was a better long-term solution.

-- Set the STATISTICS_NORECOMPUTE option Off
ALTER INDEX PK_FeedInfo
ON dbo.FeedInfo
SET (STATISTICS_NORECOMPUTE  = OFF);

These queries and commands work the same way in SQL Server 2005, 2008 and 2008 R2.

Source: Glen Berry

Advertisements

Text File Comparison Tool – Winmerge

WinMerge is an Open Source differencing and merging tool for Windows. WinMerge can compare both folders and files, presenting differences in a visual text format that is easy to understand and handle.

http://winmerge.org/