Tag Archives: Performance Tuning

Script to get the last update statistics date

Below script will execute against the sys.Objects,sys.indexes and sys.stats. Script will return last statistics update date and meta data of  statistics. Script will return the result for table and view level statistics.

In this script I have used the function STATS_DATE which will return last updated statistics date.

Syntax
STATS_DATE ( table_id , index_id )

SELECT sysobj.name AS objectname,
sysindex.name AS indexname,
Stats_date(sysindex.[object_id], sysindex.index_id) AS
[Statistics Update Date],
CASE sysstats.auto_created
WHEN 0 THEN ‘No’
WHEN 1 THEN ‘Yes’
END AS
isstatscreatedbyqueryprocessor,
CASE sysstats.user_created
WHEN 0 THEN ‘No’
WHEN 1 THEN ‘Yes’
END AS
isstatscreatedbyuser,
CASE sysstats.no_recompute
WHEN 0 THEN ‘No’
WHEN 1 THEN ‘Yes’
END AS
isstatscreatedwithnorecomputeoption
FROM sys.objects AS sysobj WITH (nolock)
INNER JOIN sys.indexes AS sysindex WITH (nolock)
ON sysobj.[object_id] = sysindex.[object_id]
INNER JOIN sys.stats AS sysstats WITH (nolock)
ON sysindex.[object_id] = sysstats.[object_id]
AND sysindex.index_id = sysstats.stats_id
WHERE sysobj.[type] IN ( ‘U’, ‘V’ )
ORDER BY Stats_date(sysindex.[object_id], sysindex.index_id);

DBCC PAGE

 DBCC PAGE 

We can use the undocumented DBCC PAGE command to view the page header, data rows, and row offset table for any data page in a database. You have to turn on traceflag 3604 before running this command.  You can also use this command when you find the IO bottleneck and query is waiting Page Latches.

Permission: System Admin rights

Syntax

dbcc page ( {‘dbname’ | dbid}, file number, page number [, print opt={0|1|2|3} ])

 You can pass the below print option parameter as per the requirement

  • 0 – print just the page header
  • 1 – page header plus per-row hex dumps and a dump of the page slot array (unless it’s a page that doesn’t have one, like allocation bitmaps)
  • 2 – page header plus whole page hex dump
  • 3 – page header plus detailed per-row interpretation

Trace Flag – 3604 Trace flag 3604 is the most commonly used trace flag. It sends the output of a trace to the client. For example, before you can run DBCC PAGE, which views data page information, you must run this trace flag. 

DBCC traceon(3604)

DBCC PAGE ( {dbid | dbname}, filenum, pagenum [, printopt] [, cache] ) 

The output from DBCC PAGE is divided into four main sections: Buffer, Page Header, Data, and Offset Table (really the Offset Array).