Home > Performence Tuning > Database statistics update issue along with a resolution

Database statistics update issue along with a resolution


In our environment we execute the sp_updatestats job on a daily basis at 5 am, but we have faced severe performance issue due to inaccuracy of statistics and as a result query is unable to use the optimal execution plan.

Finding the state of your statistics

 

Up-to-date statistics help ensure that the most appropriate index is chosen to obtain the underlying data. They also help ensure that the correct index access mechanism is chosen, for example, seek or lookup. Running the SQL script given in the following listing will identify the current state of your statistics.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT

ss.name AS SchemaName

, st.name AS TableName

, s.name AS IndexName

, STATS_DATE(s.id,s.indid) AS ‘Statistics Last Updated’

, s.rowcnt AS ‘Row Count’

, s.rowmodctr AS ‘Number Of Changes’

, CAST((CAST(s.rowmodctr AS DECIMAL(28,8))/CAST(s.rowcnt AS

DECIMAL(28,2)) * 100.0)

AS DECIMAL(28,2)) AS ‘% Rows Changed’

FROM sys.sysindexes s

INNER JOIN sys.tables st ON st.[object_id] = s.[id]

INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id]

WHERE s.id > 100

AND s.indid > 0

AND s.rowcnt >= 500

ORDER BY SchemaName, TableName, IndexName

Reason :

The system stored procedure sp_updatestats does not provide the ability to update your statistics with the FULLSCAN option. When we are faced with an urgent performance issue updating statistics with the “WITH FULLSCAN” option can be exactly what SQL Server needs to find the optimal execution plan and update the statistics

Workaround:

We can use rhe EXEC sp_MSforeachtable ‘UPDATE STATISTICS ? WITH FULLSCAN’ at specific database level to update the statistics correctly or we can schedule the Rebuild job daily basis if it is permitted by business owner or did not conflict with any other job.

Advertisements
Categories: Performence Tuning
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: