Index usage report

SQL Server 2005/2008 : Index usage report

The new missing index report makes it easy to identify what new indexes will improve the performance. But if you do not investigate all the recommended indexes before creating them then you might end up will unnecessary indexes and thereby increasing the write times on the tables.

So I run the following query once in a while to identify which indexes are not being used and then drop them. Take a look at the user_updates and if it is significantly higher than (user_seeks+user_scans) then you might want to drop them,

SELECT   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],

         I.[NAME] AS [INDEX NAME],

         USER_SEEKS,

         USER_SCANS,

         USER_LOOKUPS,

         USER_UPDATES, last_user_seek, last_user_scan, last_user_lookup

FROM     SYS.DM_DB_INDEX_USAGE_STATS AS S

         INNER JOIN SYS.INDEXES AS I

           ON I.[OBJECT_ID] = S.[OBJECT_ID]

              AND I.INDEX_ID = S.INDEX_ID

WHERE    I.[NAME] not like 'PK%' and s.database_id = DB_ID() and

OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1   order by user_updates desc

Cannot create or update statistics on view because both FULLSCAN and NORECOMPUTE options are required

SQL Server 2005/2008


Failed:(-1073548784) Executing the query "UPDATE STATISTICS [dbo].[v_errorlog]
WITH SAMPLE 50 PERCENT,NORECOMPUTE

Cannot create or update statistics on view because both FULLSCAN and NORECOMPUTE options are required

Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The problem is due to the fact that the maintenance plan was trying to update the stats on views too. And on a view we can not update the stats with anything other than full scan.
The maintenance plan was configured to run update stats on all the databases. But when you do that, you do not get the choice to pick if you want to exclude or include tables/views or specific tables/views.

It pretty much runs the plan against all DBs, all tables and all views. You can exclude the views only if you select one database at a time. If you have limited number of databases in the instance then create a maintenance plan like the following.

1)       Create two ‘Update statistics’ tasks for each database.
2)       The first task for each database will update the statistics only on tables. You can choose the  
           sampling percentage in this step.
          
3)       The second task  for each database will update the statistics only on views. But make sure you 
           pick full scan option in this step.
4)       Repeat step 2 and 3 for each database.

If it is not possible to create multiple tasks due to the large of databases in the instance, then you will have to write a script to this. If you need help then let us know.

Find missing indexes

SQL Server 2005/2008 : Find missing indexes

Missing index report
Starting from SQL Server 2005 there were some DMVs introduced that stored information regarding what indexes would enhance the performance of the queries. Following query will give you a list of indexes that SQL Server recommends. Use this report wisely and do not create all the indexes listed in the report.


SELECT
  migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
 [Table] = [statement],
  'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
  + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
  + ' ON ' + mid.statement
  + ' (' + ISNULL (mid.equality_columns,'')
  + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
  + ISNULL (mid.inequality_columns, '')
  + ')'
  + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
  migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

SQL Server : Get Index usage report

For some time now I have been using the following query to find the index usage statistics for all indexes in a database.

SELECT   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
         I.[NAME] AS [INDEX NAME],
         USER_SEEKS,
         USER_SCANS,
         USER_LOOKUPS,
         USER_UPDATES, last_user_seek, last_user_scan, last_user_lookup
FROM     SYS.DM_DB_INDEX_USAGE_STATS AS S
         INNER JOIN SYS.INDEXES AS I
         ON I.[OBJECT_ID] = S.[OBJECT_ID]
         AND I.INDEX_ID = S.INDEX_ID
WHERE    I.[NAME] not like 'PK%'
OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1   order by user_updates desc

But yesterday one of me colleagues asked me if it is possible for two objects in two different databases to have the same object_id. The DMV sys.dm_db_index_usage_stats had a column for database_id and my query would never check the DB_ID of the object_id. S

Upon further investigation I found that this was indeed true. It is possible the the OBJECT_ID for two objects in two different databases could be the same. Closer inspection revealed that the query was actually giving a cumulative value for the index stats for all the objects with the same object_id, irrespective of whether the objects were in the same database or not.
So the above mentioned query would not give the exact index stats for a given database. So I made a slight modification to the query and now I check the database_id for each record in the DMV. This gives me more accurate results.
So I learned two things today.
1) Object_id is only unique within a database but not within the instance. Two objects in two different databases can have the same object_id.
2) The DMV sys.dm_db_index_usage_stats contains info for all the databases, so it is important to query that DMV for a specific DB_ID.

The correct query to find index utilization statistics is
SELECT   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
         I.[NAME] AS [INDEX NAME],
         USER_SEEKS,
         USER_SCANS,
         USER_LOOKUPS,
         USER_UPDATES, last_user_seek, last_user_scan, last_user_lookup
FROM     SYS.DM_DB_INDEX_USAGE_STATS AS S
         INNER JOIN SYS.INDEXES AS I
           ON I.[OBJECT_ID] = S.[OBJECT_ID]
              AND I.INDEX_ID = S.INDEX_ID
WHERE    I.[NAME] not like 'PK%' and s.database_id = DB_ID() and
OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1   order by user_updates desc