Statistics Details in SQL Server

Posted: November 8, 2011 by Virendra Yaduvanshi in SQL General

SQL Server tries to use statistics to react intelligently in its query optimization. Knowing number of records, density of pages, histogram, or available indexes help the SQL Server optimizer guess more accurately how it can best retrieve data. A common misnomer is that if you have indexes, SQL Server will use those indexes to retrieve records in your query.

Below is the query using DMVs to find out statistics details about current database’s tables.

SELECT
OBJECT_NAME(A.OBJECT_ID)
AS Object_Name,

A.name AS index_name, a.type_desc AS Index_Type,STATS_DATE(A.OBJECT_ID, index_id)
AS StatsUpdated ,DATEDIFF(d,STATS_DATE(A.OBJECT_ID, index_id),getdate()) DaysOld

FROM
sys.indexes A

INNER
JOIN
sys.tables B ON A.object_id = B.object_id

WHERE A.name IS
NOT
NULL

ORDER
BY
DATEDIFF(d,STATS_DATE(A.OBJECT_ID, index_id),getdate())
DESC


 

Advertisements

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