sys.dm_db_index_usage_stats [Maintenance Cost] = [Retrieval Usage]

Question

sys.dm_db_index_usage_stats [Maintenance Cost] = [Retrieval Usage]

sqlTobi

Right there with Babe

Points: 739
More actions
September 27, 2010 at 4:07 am

#228748

Hi guys,
I'm checking sys.dm_db_index_usage_stats with the script below to find unused and rarley used indexes.
I've many indexes on Indexed Views where [Maintenance Cost] = [Retrieval Usage].
How do you interpret this fact?
Are those Indexed Views never used for reading data?
thanks
Tobias
SELECT
(user_updates + system_updates) [Maintenance Cost]
,(user_seeks + user_scans + user_lookups) [Retrieval Usage]
,OBJECT_NAME(s.[object_id]) [Table Name]
,i.name [Index Name]
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i
ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
WHERE
s.database_id = DB_ID()
AND i.name IS NOT NULL
AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
AND (user_updates + system_updates) > 0
ORDER BY [Maintenance Cost] DESC

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

raistlinx Ten Centuries Points: 1345 More actions · Answer 1

Tobias Ortmann (9/27/2010)
Hi guys,
I'm checking sys.dm_db_index_usage_stats with the script below to find unused and rarley used indexes.
I've many indexes on Indexed Views where [Maintenance Cost] = [Retrieval Usage].
How do you interpret this fact?
Are those Indexed Views never used for reading data?

Is your system restarted often? dm_db_index_usage_stats is reset each time the server restarts so I would guess it's just how the data is used. i.e. everytime the index updated it is also used to get data.

If you manually do a couple of SELECTs from one of the views are they still equal?

sqlTobi Right there with Babe Points: 739 More actions · Answer 2

Thanks for your feedback.

Is your system restarted often? dm_db_index_usage_stats is reset each time the server restarts so I would guess it's just how the data is used. i.e. everytime the index updated it is also used to get data.

No, the system will only be restarted for updates or other maintenance reasons but not on a regular basis.

There are Indexed Views with 1.000.000 updates (and seeks/scans).

If you manually do a couple of SELECTs from one of the views are they still equal?

I have to check this.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

if you dont get data in this DMV then trace flag 2330 could be enable at this sql server. flip it to OFF and then your DMV will capture the data.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

paul.jones SSC Eights! Points: 854 More actions · Answer 4

If you are using Enterprise edition, the optimiser will automatically consider using the indexes on an indexed view, but on other editions you need a WITH (NOEXPAND) hint. Also there's a list of session options that matter - see http://msdn.microsoft.com/en-us/library/ms181151.aspx for details.

I got round this by creating a second view along the lines of

CREATE VIEW new_view AS

SELECT column_list FROM original_view WITH (NOEXPAND)

which saved me needing to remember the hint every time.

sqlTobi Right there with Babe Points: 739 More actions · Answer 5

If you are using Enterprise edition, the optimiser will automatically consider using the indexes on an indexed view, but on other editions you need a WITH (NOEXPAND) hint.

I use standard edition, but the NOEXPAND hint will always be used. The Indexed Views as well as SQL statements for querying them will be autogenerated by client app, wich is MS Dynamics NAV.

I got round this by creating a second view along the lines of
CREATE VIEW new_view AS
SELECT column_list FROM original_view WITH (NOEXPAND)
which saved me needing to remember the hint every time.

Nice trick.