March 3, 2009 at 2:35 pm
If i run the statement below the seeks/scans report.
I know the seeks are better than scans...question is (the counts is based on last time sql shutdown or can you get this on a daily basic/monthly basic).
Lastly if the there was a lot of scans rather than seeks - how would you capture the entire sql statements is this possible to rewrite or add indexes accordingly.
SELECT
[Database] = d.name,
[Schema]= s.name,
[Table]= o.name,
[Index]= x.name,
[Scans] = user_scans,
[Seeks] = user_seeks,
[Lookups] = user_lookups,
[Last Scan] = last_user_scan,
[System Scans] = system_scans
FROM sys.dm_db_index_usage_stats u
INNER JOIN sys.sysdatabases d on u.database_id = d.dbid
INNER JOIN sys.sysindexes x on u.object_id = x.id and u.index_id = x.indid
INNER JOIN sys.objects o on u.object_id = o.object_id
INNER JOIN sys.schemas s on s.schema_id = o.schema_id
WHERE d.name = 'mydatabase'
and o.type = 'u'
ORDER BY [Scans] desc, [Seeks] desc
March 3, 2009 at 3:01 pm
TRACEY (3/3/2009)
If i run the statement below the seeks/scans report.I know the seeks are better than scans...question is (the counts is based on last time sql shutdown or can you get this on a daily basic/monthly basic).
Lastly if the there was a lot of scans rather than seeks - how would you capture the entire sql statements is this possible to rewrite or add indexes accordingly.
SELECT
[Database] = d.name,
[Schema]= s.name,
[Table]= o.name,
[Index]= x.name,
[Scans] = user_scans,
[Seeks] = user_seeks,
[Lookups] = user_lookups,
[Last Scan] = last_user_scan,
[System Scans] = system_scans
FROM sys.dm_db_index_usage_stats u
INNER JOIN sys.sysdatabases d on u.database_id = d.dbid
INNER JOIN sys.sysindexes x on u.object_id = x.id and u.index_id = x.indid
INNER JOIN sys.objects o on u.object_id = o.object_id
INNER JOIN sys.schemas s on s.schema_id = o.schema_id
WHERE d.name = 'mydatabase'
and o.type = 'u'
ORDER BY [Scans] desc, [Seeks] desc
You should really not say that seeks are better than scans ... the real answer is "it depends".
When you are retrieving large amount of data or the table is small enough scans are superior to seeks 😉
To get historic information you should take snapshots of this data at intervals and the diffs will provide you with the answer.
Of course a server reboot can mess up your data but it should not happen often, right? 😀
* Noel
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply