Solved

Detail check on the update of statistics

Posted on 2011-09-11
39
415 Views
Last Modified: 2012-06-27
Dear all,

Is there any script to check out the detail on the use of index of a given DB, I need to know the respective table name of the given DB, the index it is using and how frequent it has been used daily.

DBA100.
0
Comment
Question by:marrowyung
  • 22
  • 17
39 Comments
 
LVL 9

Expert Comment

by:mimran18
Comment Utility
Hi
  Try this

For Getting Index name :
   sp_helpindex 'Table Name'

For Index space :
exec Sp_Spaceused 'Table name'

For All tables index details.

SELECT  CAST(SO.[name] AS CHAR(20)) AS TableName
      , CAST(SI.[name] AS CHAR(30)) AS IndexName
      , CAST(SC.[name] AS CHAR(15)) AS ColName
      , CAST(ST.[name] AS CHAR(10)) AS TypeVal
      , CASE WHEN (SI.status & 16)<>0 THEN 'Yes' ELSE 'No' END AS ClusteredIndex
FROM
      SYSOBJECTS SO INNER JOIN SYSINDEXES SI INNER JOIN SYSINDEXKEYS SIK
      ON SIK.[id] = SI.[id]
      AND SIK.indid = SI.indid INNER JOIN SYSCOLUMNS SC INNER JOIN SYSTYPES ST
    ON SC.xtype = ST.xtype ON SIK.[id] = SC.[id]
      AND SIK.colid = SC.colid
      ON SO.[id] = SI.[id]
WHERE SO.xtype = 'u'
AND SI.indid > 0
AND      SI.indid < 255
AND      (SI.status & 64)=0
ORDER BY
      TableName
      , IndexName
      , SIK.keyno
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
mimran18:,

Thanks for that.

when I run the script, it says,

Msg 208, Level 16, State 1, Line 2
Invalid object name 'SYSOBJECTS'.

Any reasons for this ?

DBA100.
0
 
LVL 9

Expert Comment

by:mimran18
Comment Utility
I gave you 3 scripts.Which one script is not working ?
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
mimran18:

The last one. I have some script on the same usage of script 1 and scipt 2.

I am looking for script that shows the index name, table name related, the usage of it.

I have a script to return the index name that hasn't been used and we can consider to drop that:

WITH UnusedIndex (Object_ID, ObjectName, IndexName, Index_ID, Reads, Writes, Rows,last_user_seek ,
last_user_scan ,last_user_lookup , last_user_update) AS
(
SELECT s.object_id,
objectname=OBJECT_NAME(s.OBJECT_ID)
, indexname=i.name
, i.index_id
, reads=user_seeks + user_scans + user_lookups
, writes = user_updates
, p.rows,
s.last_user_seek ,s.last_user_scan ,s.last_user_lookup , s.last_user_update
FROM sys.dm_db_index_usage_stats s JOIN sys.indexes i
ON i.index_id = s.index_id AND s.OBJECT_ID = i.OBJECT_ID
JOIN sys.partitions p ON p.index_id = s.index_id AND s.OBJECT_ID = p.OBJECT_ID
WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1
AND s.database_id = DB_ID()  
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
AND p.rows > 10000
)
,
IndexSizes (schemaname,tablename,object_id,indexname,index_id,indextype,indexsize_kb,indexsize_mb,indexsize_gb) AS
(
SELECT sys_schemas.name AS SchemaName
,sys_objects.name AS TableName
,sys_objects.[object_id] AS object_id
,sys_indexes.name AS IndexName
,sys_indexes.index_id as index_id
,sys_indexes.type_desc AS IndexType
,partition_stats.used_page_count * 8 AS IndexSizeKB
,CAST(partition_stats.used_page_count * 8 / 1024.00 AS Decimal(10,3))AS IndexSizeMB
,CAST(partition_stats.used_page_count * 8 / 1048576.00 AS Decimal(10,3)) AS IndexSizeGB
FROM sys.dm_db_partition_stats partition_stats
INNER JOIN sys.indexes sys_indexes
ON partition_stats.[object_id] = sys_indexes.[object_id]
AND partition_stats.index_id = sys_indexes.index_id
AND sys_indexes.type_desc <> 'HEAP'
INNER JOIN sys.objects sys_objects
ON sys_objects.[object_id] = partition_stats.[object_id]
INNER JOIN sys.schemas sys_schemas
ON sys_objects.[schema_id] = sys_schemas.[schema_id]
AND sys_schemas.name <> 'SYS'
)

select IndexSizes.*,UnusedIndex.*
from UnusedIndex
inner join IndexSizes
on UnusedIndex.Object_ID = IndexSizes.object_id
and UnusedIndex.Index_ID = IndexSizes.index_id


Can we change this one to make it find out the statistic of all index of all table of a given database?

DBA100.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
I found this one from internet:

SELECT o.name 'table name', -- Object_Name table_name,
       SCHEMA_NAME(o.schema_id) Schema_name,
       i.name Index_name,
       i.Type_Desc,
       s.user_seeks,
       s.user_scans,
       s.user_lookups,
       s.user_updates  
 FROM sys.objects AS o
     JOIN sys.indexes AS i
 ON o.object_id = i.object_id
     JOIN
  sys.dm_db_index_usage_stats AS s    
 ON i.object_id = s.object_id  
  AND i.index_id = s.index_id
 WHERE  o.type = 'u'
 -- Clustered and Non-Clustered indexes
  AND i.type IN (1, 2 )
 -- Indexes that have been updated by not used
  AND(s.user_seeks > 0 or s.user_scans > 0 or s.user_lookups > 0 )
  ORDER by o.name


However, it doesn't shows all field covered by the index, it doens't show up all table name, any way can fix it?

DBA100.
0
 
LVL 9

Expert Comment

by:mimran18
Comment Utility
Hi
 Try this
 
DROP TABLE dbo.IndexUsageStatistics
Go
DROP TABLE dbo.IndexUsageStatisticsHistory
Go
CREATE TABLE dbo.IndexUsageStatistics(
	ServerName nvarchar(128),
	DBName nvarchar(128),
	SchemaName nvarchar(128),
	TableName nvarchar(128),
	IndexName nvarchar(128),
	IsUsed bit,
	IsExpensive bit,
	TypeDescription nvarchar(60),
	UserReads bigint,
	UserWrites bigint,
	Reads bigint,
	LeafWrites bigint,
	LeafPageSplits bigint,
	NonleafWrites bigint,
	NonleafPageSplits bigint,
	UserSeeks bigint,
	UserScans bigint,
	UserLookups bigint,
	UserUpdates bigint,
	LastUserSeek datetime,
	LastUserScan datetime,
	LastUserLookup datetime,
	LastUserUpdate datetime,
	RecordCount bigint,
	TotalPageCount bigint,
	IndexSizeInMegabytes float,
	AverageRecordSizeInBytes float,
	IndexDepth int,
	RecordedDateTime datetime
) 

CREATE TABLE dbo.IndexUsageStatisticsHistory(
	ServerName nvarchar(128),
	DBName nvarchar(128),
	SchemaName nvarchar(128),	
	TableName nvarchar(128),
	IndexName nvarchar(128),
	IsUsed bit,
	IsExpensive bit,
	TypeDescription nvarchar(60),
	UserReads bigint,
	UserWrites bigint,
	Reads bigint,
	LeafWrites bigint,
	LeafPageSplits bigint,
	NonleafWrites bigint,
	NonleafPageSplits bigint,
	UserSeeks bigint,
	UserScans bigint,
	UserLookups bigint,
	UserUpdates bigint,
	LastUserSeek datetime,
	LastUserScan datetime,
	LastUserLookup datetime,
	LastUserUpdate datetime,
	RecordCount bigint,
	TotalPageCount bigint,
	IndexSizeInMegabytes float,
	AverageRecordSizeInBytes float,
	IndexDepth int,
	RecordedDateTime datetime
) 


IF EXISTS (
  SELECT 1
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME = N'GatherIndexUsageStatistics' 
)
DROP PROCEDURE GatherIndexUsageStatistics
GO
/*
exec GatherIndexUsageStatistics 'ALL',1
select * from IndexUsageStatistics
*/
CREATE PROCEDURE GatherIndexUsageStatistics
				@DatabaseList           VARCHAR(MAX),
				@ExcludeSystemDatabases tinyint = 1
AS
  SET NOCOUNT ON
/*****************************************
* Truncate holding tables
*****************************************/
  IF EXISTS (SELECT 1
             FROM   dbo.IndexUsageStatistics)
    BEGIN
        INSERT INTO dbo.IndexUsageStatisticsHistory
        SELECT *
        FROM   dbo.IndexUsageStatistics;

        TRUNCATE TABLE dbo.IndexUsageStatistics;
    END

  DECLARE @cmd VARCHAR(8000),
		@servername VARCHAR(256),
		@dbname     VARCHAR(256),
		@recordeddatetime datetime

	CREATE TABLE #dbnames
	(
		name NVARCHAR(128)
	)
	
  SET @recordeddatetime = GETDATE()
  SET @servername = CAST(Serverproperty('servername') AS VARCHAR(256))

    IF Upper(@DatabaseList) = 'ALL'
    BEGIN
        IF @ExcludeSystemDatabases = 1
          BEGIN
              SET @DatabaseList = '';

              SELECT @DatabaseList = @DatabaseList + '''' + name + ''','
              FROM   MASTER.dbo.sysdatabases
              WHERE  name NOT IN ( 'master', 'msdb', 'model', 'pubs',
                                   'northwind', 'tempdb' );
          END
        ELSE
          BEGIN
              SELECT @DatabaseList = @DatabaseList + '''' + name + ''','
              FROM   MASTER.dbo.sysdatabases;
          END

        SET @DatabaseList = LEFT(@DatabaseList, Len(@DatabaseList) - 2) + ''''
        
		INSERT INTO #dbnames
		EXEC('select name from master.dbo.sysdatabases where name in ('+@DatabaseList+')')
        --Print   @DatabaseList
    END
    --found at http://mangalpardeshi.blogspot.com/2009/03/how-to-split-comma-delimited-string.html
		;WITH Cte AS
		(
			select CAST('<M>' + REPLACE( @DatabaseList,  ',' , '</M><M>') + '</M>' AS XML) AS DatabaseNames
		)
		
		insert into #dbnames
		SELECT 	Split.a.value('.', 'VARCHAR(100)') AS DatabaseNames
		FROM Cte
		CROSS APPLY DatabaseNames.nodes('/M') Split(a)

    IF not exists(select 1 from #dbnames)
    BEGIN
		insert into #dbnames select @DatabaseList
    END

  DECLARE db CURSOR FAST_FORWARD FOR
    SELECT name
    FROM   #dbnames

  OPEN db

  FETCH NEXT FROM db INTO @dbname

  WHILE ( @@FETCH_STATUS <> -1 )
    BEGIN
        IF ( @@FETCH_STATUS <> -2 )
          BEGIN
              IF (SELECT CONVERT(SYSNAME, Databasepropertyex(@dbname, 'status'))
                 )
                 =
                 'ONLINE'
                BEGIN  
                
                set @cmd ='
					INSERT INTO dbo.IndexUsageStatistics
					SELECT   *
							,'''+convert(varchar,@recordeddatetime, 121)+'''
					FROM     (SELECT '''+@servername+''' AS ''ServerName'',
									 '''+@dbname+''' AS ''DBName'',
									Object_schema_name(so.object_id,DB_ID('''+@dbname+''')) as ''SchemaName'',
									OBJECT_NAME(so.object_id,db_id('''+@dbname+''')) as ''TableName'',
									 i.name AS ''IndexName'',
									 Convert(BIT,CASE 
															 WHEN u.object_id IS NULL
															 THEN 0
															 ELSE 1
														   END) as ''IsUsed'',
									 Convert(BIT,CASE 
																  WHEN (i.type_desc <> ''HEAP''
																		AND (leaf_insert_count
																			   + leaf_update_count
																			   + leaf_delete_count) > (range_scan_count
																										 + singleton_lookup_count))
																  THEN 1
																  ELSE 0
																END) as ''IsExpensive'',
									 i.type_desc,
									 u.user_seeks
													+ u.user_scans
													+ u.user_lookups as ''UserReads'',
									 u.user_updates as ''UserWrites'',
									 Reads = range_scan_count
											   + singleton_lookup_count,
									 leaf_insert_count
													   + leaf_update_count
													   + leaf_delete_count as''LeafWrites'',
									 leaf_allocation_count as ''LeafPageSplits'',
									 nonleaf_insert_count
									 + nonleaf_update_count
									 + nonleaf_delete_count as ''NonleafWrites'',
									  nonleaf_allocation_count as ''NonleafPageSplits'',
									 u.user_seeks,
									 u.user_scans,
									 u.user_lookups,
									 u.user_updates,
									 u.last_user_seek,
									 u.last_user_scan,
									 u.last_user_lookup,
									 u.last_user_update,
									 f.record_count,
									 f.page_count,
									 f.IndexSizeInMegabytes,
									 f.AverageRecordSizeInBytes,
									 f.IndexDepth
							  FROM   ['+@dbname+'].sys.indexes i
									 INNER JOIN ['+@dbname+'].sys.objects so
									   ON so.object_id = i.object_id
									 INNER JOIN (SELECT   object_id,
														  index_id,
														  Sum(record_count) AS record_count,
														  Sum(page_count) AS page_count,
														  Convert(FLOAT,Sum(page_count))
															* 8192
															/ 1024
															/ 1024 AS ''IndexSizeInMegabytes'',
														  Avg(avg_record_size_in_bytes) AS ''AverageRecordSizeInBytes'',
														  Sum(index_depth) AS ''IndexDepth''
												 FROM     sys.Dm_db_index_physical_stats(DB_ID('''+@dbname+'''),NULL,NULL,NULL,''SAMPLED'') f
												 GROUP BY object_id,
														  index_id) f
									   ON i.object_id = f.object_id
										  AND i.index_id = f.index_id
									 LEFT JOIN ['+@dbname+'].sys.dm_db_index_usage_stats u
									   ON f.object_id = u.object_id
										  AND f.index_id = u.index_id
										  AND u.database_id = DB_ID('''+@dbname+''')
									 INNER JOIN sys.Dm_db_index_operational_stats(DB_ID('''+@dbname+'''),NULL,NULL,NULL) s
									   ON i.object_id = s.object_id
										  AND i.index_id = s.index_id
							  WHERE  so.TYPE = ''U''
							  ) indexes
							ORDER BY 
								TableName,
								IndexName'
					 --Print @cmd
					 EXEC(@cmd)
					  
				END
			END
		FETCH NEXT FROM db INTO @dbname
	END

	CLOSE db
	DEALLOCATE db
	
	Select * from IndexUsageStatistics
	
	DROP TABLE #dbnames
	SET nocount OFF

Open in new window


Reference : https://github.com/SQLServerIO/SQLDIY/blob/master/Management.GatherIndexUsageStatistics.sql
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
AFter i run this, and run "Select * from IndexUsageStatistics", I see no result.
0
 
LVL 9

Expert Comment

by:mimran18
Comment Utility
Hi,
    Execute it like this.
exec GatherIndexUsageStatistics 'ALL',1

Because I am getting the records.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
it say:

Msg 208, Level 16, State 1, Procedure GatherIndexUsageStatistics, Line 42
Invalid object name 'MASTER.dbo.sysdatabases'.


0
 
LVL 9

Expert Comment

by:mimran18
Comment Utility
which one sql server version are you using ?
Are you using it as an administraor ?

0
 
LVL 9

Expert Comment

by:mimran18
Comment Utility
also execute this query.

SELECT * FROM master.dbo.sysdatabases

0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
mimran18:

"which one sql server version are you using ?
Are you using it as an administraor ?
"
SQL server 2008 with SP2. Yes, running on adminstrator account.

"SELECT * FROM master.dbo.sysdatabases " this one works.
0
 
LVL 9

Expert Comment

by:mimran18
Comment Utility
Hi ,
  Try this with your database name
exec GatherIndexUsageStatistics 'DBName',1

also check your compatibilty level.It should be 90 and above.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
this time, it said:

Msg 208, Level 16, State 1, Line 2
Invalid object name 'sys.Dm_db_index_physical_stats'.


DBA100.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
I found just because a lot of DMV print in this way:  sys.Dm_db_index_physical_stats.

So I change all of them to sys.dm_db_index_physical_stats.

But one thing, is there any way to show out what field of the table the index cover?

from the result of the SP, only the firled with last user update will have usage/reference?

DBA100.
0
 
LVL 9

Expert Comment

by:mimran18
Comment Utility
Hi ,
   I have altered this procedure.Now you can get the field name as well.
 
DROP TABLE dbo.IndexUsageStatistics
Go
DROP TABLE dbo.IndexUsageStatisticsHistory
Go
CREATE TABLE dbo.IndexUsageStatistics(
	ServerName nvarchar(128),
	DBName nvarchar(128),
	SchemaName nvarchar(128),
	TableName nvarchar(128),
	IndexName nvarchar(128),
	IndexID int,
	ObjectID int, 
	IsUsed bit,
	IsExpensive bit,
	TypeDescription nvarchar(60),
	UserReads bigint,
	UserWrites bigint,
	Reads bigint,
	LeafWrites bigint,
	LeafPageSplits bigint,
	NonleafWrites bigint,
	NonleafPageSplits bigint,
	UserSeeks bigint,
	UserScans bigint,
	UserLookups bigint,
	UserUpdates bigint,
	LastUserSeek datetime,
	LastUserScan datetime,
	LastUserLookup datetime,
	LastUserUpdate datetime,
	RecordCount bigint,
	TotalPageCount bigint,
	IndexSizeInMegabytes float,
	AverageRecordSizeInBytes float,
	IndexDepth int,
	RecordedDateTime datetime
) 

CREATE TABLE dbo.IndexUsageStatisticsHistory(
	ServerName nvarchar(128),
	DBName nvarchar(128),
	SchemaName nvarchar(128),	
	TableName nvarchar(128),
	IndexName nvarchar(128),
	IndexID int,
	ObjectID int, 
	IsUsed bit,
	IsExpensive bit,
	TypeDescription nvarchar(60),
	UserReads bigint,
	UserWrites bigint,
	Reads bigint,
	LeafWrites bigint,
	LeafPageSplits bigint,
	NonleafWrites bigint,
	NonleafPageSplits bigint,
	UserSeeks bigint,
	UserScans bigint,
	UserLookups bigint,
	UserUpdates bigint,
	LastUserSeek datetime,
	LastUserScan datetime,
	LastUserLookup datetime,
	LastUserUpdate datetime,
	RecordCount bigint,
	TotalPageCount bigint,
	IndexSizeInMegabytes float,
	AverageRecordSizeInBytes float,
	IndexDepth int,
	RecordedDateTime datetime
) 


IF EXISTS (
  SELECT 1
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME = N'GatherIndexUsageStatistics' 
)
DROP PROCEDURE GatherIndexUsageStatistics
GO
/*
exec GatherIndexUsageStatistics 'ALL',1
select * from IndexUsageStatistics
*/
CREATE PROCEDURE GatherIndexUsageStatistics
				@DatabaseList           VARCHAR(MAX),
				@ExcludeSystemDatabases tinyint = 1
AS
  SET NOCOUNT ON
/*****************************************
* Truncate holding tables
*****************************************/
  IF EXISTS (SELECT 1
             FROM   dbo.IndexUsageStatistics)
    BEGIN
        INSERT INTO dbo.IndexUsageStatisticsHistory
        SELECT *
        FROM   dbo.IndexUsageStatistics;

        TRUNCATE TABLE dbo.IndexUsageStatistics;
    END

  DECLARE @cmd VARCHAR(8000),
		@servername VARCHAR(256),
		@dbname     VARCHAR(256),
		@recordeddatetime datetime

	CREATE TABLE #dbnames
	(
		name NVARCHAR(128)
	)
	
  SET @recordeddatetime = GETDATE()
  SET @servername = CAST(Serverproperty('servername') AS VARCHAR(256))

    IF Upper(@DatabaseList) = 'ALL'
    BEGIN
        IF @ExcludeSystemDatabases = 1
          BEGIN
              SET @DatabaseList = '';

              SELECT @DatabaseList = @DatabaseList + '''' + name + ''','
              FROM   MASTER.dbo.sysdatabases
              WHERE  name NOT IN ( 'master', 'msdb', 'model', 'pubs',
                                   'northwind', 'tempdb' );
          END
        ELSE
          BEGIN
              SELECT @DatabaseList = @DatabaseList + '''' + name + ''','
              FROM   MASTER.dbo.sysdatabases;
          END

        SET @DatabaseList = LEFT(@DatabaseList, Len(@DatabaseList) - 2) + ''''
        
		INSERT INTO #dbnames
		EXEC('select name from master.dbo.sysdatabases where name in ('+@DatabaseList+')')
        --Print   @DatabaseList
    END
    --found at http://mangalpardeshi.blogspot.com/2009/03/how-to-split-comma-delimited-string.html
		;WITH Cte AS
		(
			select CAST('<M>' + REPLACE( @DatabaseList,  ',' , '</M><M>') + '</M>' AS XML) AS DatabaseNames
		)
		
		insert into #dbnames
		SELECT 	Split.a.value('.', 'VARCHAR(100)') AS DatabaseNames
		FROM Cte
		CROSS APPLY DatabaseNames.nodes('/M') Split(a)

    IF not exists(select 1 from #dbnames)
    BEGIN
		insert into #dbnames select @DatabaseList
    END

  DECLARE db CURSOR FAST_FORWARD FOR
    SELECT name
    FROM   #dbnames

  OPEN db

  FETCH NEXT FROM db INTO @dbname

  WHILE ( @@FETCH_STATUS <> -1 )
    BEGIN
        IF ( @@FETCH_STATUS <> -2 )
          BEGIN
              IF (SELECT CONVERT(SYSNAME, Databasepropertyex(@dbname, 'status'))
                 )
                 =
                 'ONLINE'
                BEGIN  
                
                set @cmd ='
					INSERT INTO dbo.IndexUsageStatistics
					SELECT   *
							,'''+convert(varchar,@recordeddatetime, 121)+'''
					FROM     (SELECT '''+@servername+''' AS ''ServerName'',
									 '''+@dbname+''' AS ''DBName'',
									Object_schema_name(so.object_id,DB_ID('''+@dbname+''')) as ''SchemaName'',
									OBJECT_NAME(so.object_id,db_id('''+@dbname+''')) as ''TableName'',
									 i.name AS ''IndexName'',i.index_id as ''IndexID'',so.object_id as ''ObjectID'',
									 Convert(BIT,CASE 
															 WHEN u.object_id IS NULL
															 THEN 0
															 ELSE 1
														   END) as ''IsUsed'',
									 Convert(BIT,CASE 
																  WHEN (i.type_desc <> ''HEAP''
																		AND (leaf_insert_count
																			   + leaf_update_count
																			   + leaf_delete_count) > (range_scan_count
																										 + singleton_lookup_count))
																  THEN 1
																  ELSE 0
																END) as ''IsExpensive'',
									 i.type_desc,
									 u.user_seeks
													+ u.user_scans
													+ u.user_lookups as ''UserReads'',
									 u.user_updates as ''UserWrites'',
									 Reads = range_scan_count
											   + singleton_lookup_count,
									 leaf_insert_count
													   + leaf_update_count
													   + leaf_delete_count as''LeafWrites'',
									 leaf_allocation_count as ''LeafPageSplits'',
									 nonleaf_insert_count
									 + nonleaf_update_count
									 + nonleaf_delete_count as ''NonleafWrites'',
									  nonleaf_allocation_count as ''NonleafPageSplits'',
									 u.user_seeks,
									 u.user_scans,
									 u.user_lookups,
									 u.user_updates,
									 u.last_user_seek,
									 u.last_user_scan,
									 u.last_user_lookup,
									 u.last_user_update,
									 f.record_count,
									 f.page_count,
									 f.IndexSizeInMegabytes,
									 f.AverageRecordSizeInBytes,
									 f.IndexDepth
							  FROM   ['+@dbname+'].sys.indexes i
									 INNER JOIN ['+@dbname+'].sys.objects so
									   ON so.object_id = i.object_id
									 INNER JOIN (SELECT   object_id,
														  index_id,
														  Sum(record_count) AS record_count,
														  Sum(page_count) AS page_count,
														  Convert(FLOAT,Sum(page_count))
															* 8192
															/ 1024
															/ 1024 AS ''IndexSizeInMegabytes'',
														  Avg(avg_record_size_in_bytes) AS ''AverageRecordSizeInBytes'',
														  Sum(index_depth) AS ''IndexDepth''
												 FROM     sys.Dm_db_index_physical_stats(DB_ID('''+@dbname+'''),NULL,NULL,NULL,''SAMPLED'') f
												 GROUP BY object_id,
														  index_id) f
									   ON i.object_id = f.object_id
										  AND i.index_id = f.index_id
									 LEFT JOIN ['+@dbname+'].sys.dm_db_index_usage_stats u
									   ON f.object_id = u.object_id
										  AND f.index_id = u.index_id
										  AND u.database_id = DB_ID('''+@dbname+''')
									 INNER JOIN sys.Dm_db_index_operational_stats(DB_ID('''+@dbname+'''),NULL,NULL,NULL) s
									   ON i.object_id = s.object_id
										  AND i.index_id = s.index_id
							  WHERE  so.TYPE = ''U''
							  ) indexes
							ORDER BY 
								TableName,
								IndexName'
					 --Print @cmd
					 EXEC(@cmd)
					  
				END
			END
		FETCH NEXT FROM db INTO @dbname
	END

	CLOSE db
	DEALLOCATE db
	
	Select ind.*, col.name as [Column Name] from IndexUsageStatistics ind
	inner join      
	sys.index_columns ic on        
	ind.objectid = ic.object_id 
	and ind.indexid = ic.index_id 
	inner join     sys.columns col on       
	ic.object_id = col.object_id 
	and ic.column_id = col.column_id  
	
	DROP TABLE #dbnames
	SET nocount OFF

Open in new window


Cheers
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
right now it said:

Msg 207, Level 16, State 1, Procedure GatherIndexUsageStatistics, Line 188
Invalid column name 'objectid'.
Msg 207, Level 16, State 1, Procedure GatherIndexUsageStatistics, Line 189
Invalid column name 'indexid'.


DBA100.
0
 
LVL 9

Expert Comment

by:mimran18
Comment Utility
you need to drop the table and create again.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
I found out that I have to create the SP in master DB.  But will the table created keep cleaning each time ?

DBA100.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 1

Author Comment

by:marrowyung
Comment Utility
mimran18:

I did drop it all the time, my usual practice. But this is another problem, the message is different this time.

DBA100.
0
 
LVL 9

Expert Comment

by:mimran18
Comment Utility
In this query the system cannot find these 2 columns

Select ind.*, col.name as [Column Name] from IndexUsageStatistics ind
      inner join      
      sys.index_columns ic on        
      ind.objectid = ic.object_id
      and ind.indexid = ic.index_id
      inner join     sys.columns col on      
      ic.object_id = col.object_id
      and ic.column_id = col.column_id  

so you can execute it seperatly and check.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
This one show this:

Msg 207, Level 16, State 1, Line 5
Invalid column name 'objectid'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'indexid'.


0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
Yeah, is this one causing problem:

      Select ind.*, col.name as [Column Name] from IndexUsageStatistics ind
      inner join      
      sys.index_columns ic on        
      ind.objectid = ic.object_id
      and ind.indexid = ic.index_id
      inner join     sys.columns col on      
      ic.object_id = col.object_id
      and ic.column_id = col.column_id  
      
0
 
LVL 9

Expert Comment

by:mimran18
Comment Utility
Now you need to browse "IndexUsageStatistics " table and check that these columns are there or not
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
The problem of this:

      Select ind.*, col.name as [Column Name] from IndexUsageStatistics ind
      inner join      
      sys.index_columns ic on        
      ind.objectid = ic.object_id
      and ind.indexid = ic.index_id
      inner join     sys.columns col on      
      ic.object_id = col.object_id
      and ic.column_id = col.column_id  

is the objectid should be Objectid and indexid has to be Indexid.

and I fix that problem:

Msg 208, Level 16, State 1, Line 2
Invalid object name 'sys.Dm_db_index_physical_stats'.

However, the result shows nothing..

0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
I found just because this one:

       Select ind.*, col.name as [Column Name] from IndexUsageStatistics ind
      inner join      
      sys.index_columns ic on        
      ind.ObjectID = ic.object_id
      and ind.IndexID = ic.index_id
      inner join     sys.columns col on      
      ic.object_id = col.object_id
      and ic.column_id = col.column_id

return nothing, why is it ?
0
 
LVL 9

Expert Comment

by:mimran18
Comment Utility
because there will be no data in "IndexUsageStatistics"
And all of them are inner join.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
no there are data in this table
0
 
LVL 9

Expert Comment

by:mimran18
Comment Utility
Now you need to check in "sys.index_columns"
And  "sys.columns" Table
Any link  is missing.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
both  "sys.index_columns"
And  "sys.columns" Table has data.

Can't see why?  you rans this script and it return something ?

DBA100.
0
 
LVL 9

Expert Comment

by:mimran18
Comment Utility
Hi,
   I ran it and it gave the data.
  Please check that do you have indexes in your table ?
 
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
mimran18:

can you generate your script using SQL SSMS and post here again, I guess I copy something wrong in this case.

The first script has something return however.

DBA100.
0
 
LVL 9

Accepted Solution

by:
mimran18 earned 500 total points
Comment Utility
New one :

 
DROP TABLE dbo.IndexUsageStatistics
Go
DROP TABLE dbo.IndexUsageStatisticsHistory
Go
CREATE TABLE dbo.IndexUsageStatistics(
	ServerName nvarchar(128),
	DBName nvarchar(128),
	SchemaName nvarchar(128),
	TableName nvarchar(128),
	IndexName nvarchar(128),
	IndexID int,
	ObjectID int, 
	IsUsed bit,
	IsExpensive bit,
	TypeDescription nvarchar(60),
	UserReads bigint,
	UserWrites bigint,
	Reads bigint,
	LeafWrites bigint,
	LeafPageSplits bigint,
	NonleafWrites bigint,
	NonleafPageSplits bigint,
	UserSeeks bigint,
	UserScans bigint,
	UserLookups bigint,
	UserUpdates bigint,
	LastUserSeek datetime,
	LastUserScan datetime,
	LastUserLookup datetime,
	LastUserUpdate datetime,
	RecordCount bigint,
	TotalPageCount bigint,
	IndexSizeInMegabytes float,
	AverageRecordSizeInBytes float,
	IndexDepth int,
	RecordedDateTime datetime
) 

CREATE TABLE dbo.IndexUsageStatisticsHistory(
	ServerName nvarchar(128),
	DBName nvarchar(128),
	SchemaName nvarchar(128),	
	TableName nvarchar(128),
	IndexName nvarchar(128),
	IndexID int,
	ObjectID int, 
	IsUsed bit,
	IsExpensive bit,
	TypeDescription nvarchar(60),
	UserReads bigint,
	UserWrites bigint,
	Reads bigint,
	LeafWrites bigint,
	LeafPageSplits bigint,
	NonleafWrites bigint,
	NonleafPageSplits bigint,
	UserSeeks bigint,
	UserScans bigint,
	UserLookups bigint,
	UserUpdates bigint,
	LastUserSeek datetime,
	LastUserScan datetime,
	LastUserLookup datetime,
	LastUserUpdate datetime,
	RecordCount bigint,
	TotalPageCount bigint,
	IndexSizeInMegabytes float,
	AverageRecordSizeInBytes float,
	IndexDepth int,
	RecordedDateTime datetime
) 


IF EXISTS (
  SELECT 1
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME = N'GatherIndexUsageStatistics' 
)
DROP PROCEDURE GatherIndexUsageStatistics
GO
/*
exec GatherIndexUsageStatistics 'ALL',1
select * from IndexUsageStatistics
*/
CREATE PROCEDURE GatherIndexUsageStatistics
				@DatabaseList           VARCHAR(MAX),
				@ExcludeSystemDatabases tinyint = 1
AS
  SET NOCOUNT ON
/*****************************************
* Truncate holding tables
*****************************************/
  IF EXISTS (SELECT 1
             FROM   dbo.IndexUsageStatistics)
    BEGIN
        INSERT INTO dbo.IndexUsageStatisticsHistory
        SELECT *
        FROM   dbo.IndexUsageStatistics;

        TRUNCATE TABLE dbo.IndexUsageStatistics;
    END

  DECLARE @cmd VARCHAR(8000),
		@servername VARCHAR(256),
		@dbname     VARCHAR(256),
		@recordeddatetime datetime

	CREATE TABLE #dbnames
	(
		name NVARCHAR(128)
	)
	
  SET @recordeddatetime = GETDATE()
  SET @servername = CAST(Serverproperty('servername') AS VARCHAR(256))

    IF Upper(@DatabaseList) = 'ALL'
    BEGIN
        IF @ExcludeSystemDatabases = 1
          BEGIN
              SET @DatabaseList = '';

              SELECT @DatabaseList = @DatabaseList + '''' + name + ''','
              FROM   MASTER.dbo.sysdatabases
              WHERE  name NOT IN ( 'master', 'msdb', 'model', 'pubs',
                                   'northwind', 'tempdb' );
          END
        ELSE
          BEGIN
              SELECT @DatabaseList = @DatabaseList + '''' + name + ''','
              FROM   MASTER.dbo.sysdatabases;
          END

        SET @DatabaseList = LEFT(@DatabaseList, Len(@DatabaseList) - 2) + ''''
        
		INSERT INTO #dbnames
		EXEC('select name from master.dbo.sysdatabases where name in ('+@DatabaseList+')')
        --Print   @DatabaseList
    END
    --found at http://mangalpardeshi.blogspot.com/2009/03/how-to-split-comma-delimited-string.html
		;WITH Cte AS
		(
			select CAST('<M>' + REPLACE( @DatabaseList,  ',' , '</M><M>') + '</M>' AS XML) AS DatabaseNames
		)
		
		insert into #dbnames
		SELECT 	Split.a.value('.', 'VARCHAR(100)') AS DatabaseNames
		FROM Cte
		CROSS APPLY DatabaseNames.nodes('/M') Split(a)

    IF not exists(select 1 from #dbnames)
    BEGIN
		insert into #dbnames select @DatabaseList
    END

  DECLARE db CURSOR FAST_FORWARD FOR
    SELECT name
    FROM   #dbnames

  OPEN db

  FETCH NEXT FROM db INTO @dbname

  WHILE ( @@FETCH_STATUS <> -1 )
    BEGIN
        IF ( @@FETCH_STATUS <> -2 )
          BEGIN
              IF (SELECT CONVERT(SYSNAME, Databasepropertyex(@dbname, 'status'))
                 )
                 =
                 'ONLINE'
                BEGIN  
                
                set @cmd ='
					INSERT INTO dbo.IndexUsageStatistics
					SELECT   *
							,'''+convert(varchar,@recordeddatetime, 121)+'''
					FROM     (SELECT '''+@servername+''' AS ''ServerName'',
									 '''+@dbname+''' AS ''DBName'',
									Object_schema_name(so.object_id,DB_ID('''+@dbname+''')) as ''SchemaName'',
									OBJECT_NAME(so.object_id,db_id('''+@dbname+''')) as ''TableName'',
									 i.name AS ''IndexName'',i.index_id as ''IndexID'',so.object_id as ''ObjectID'',
									 Convert(BIT,CASE 
															 WHEN u.object_id IS NULL
															 THEN 0
															 ELSE 1
														   END) as ''IsUsed'',
									 Convert(BIT,CASE 
																  WHEN (i.type_desc <> ''HEAP''
																		AND (leaf_insert_count
																			   + leaf_update_count
																			   + leaf_delete_count) > (range_scan_count
																										 + singleton_lookup_count))
																  THEN 1
																  ELSE 0
																END) as ''IsExpensive'',
									 i.type_desc,
									 u.user_seeks
													+ u.user_scans
													+ u.user_lookups as ''UserReads'',
									 u.user_updates as ''UserWrites'',
									 Reads = range_scan_count
											   + singleton_lookup_count,
									 leaf_insert_count
													   + leaf_update_count
													   + leaf_delete_count as''LeafWrites'',
									 leaf_allocation_count as ''LeafPageSplits'',
									 nonleaf_insert_count
									 + nonleaf_update_count
									 + nonleaf_delete_count as ''NonleafWrites'',
									  nonleaf_allocation_count as ''NonleafPageSplits'',
									 u.user_seeks,
									 u.user_scans,
									 u.user_lookups,
									 u.user_updates,
									 u.last_user_seek,
									 u.last_user_scan,
									 u.last_user_lookup,
									 u.last_user_update,
									 f.record_count,
									 f.page_count,
									 f.IndexSizeInMegabytes,
									 f.AverageRecordSizeInBytes,
									 f.IndexDepth
							  FROM   ['+@dbname+'].sys.indexes i
									 INNER JOIN ['+@dbname+'].sys.objects so
									   ON so.object_id = i.object_id
									 INNER JOIN (SELECT   object_id,
														  index_id,
														  Sum(record_count) AS record_count,
														  Sum(page_count) AS page_count,
														  Convert(FLOAT,Sum(page_count))
															* 8192
															/ 1024
															/ 1024 AS ''IndexSizeInMegabytes'',
														  Avg(avg_record_size_in_bytes) AS ''AverageRecordSizeInBytes'',
														  Sum(index_depth) AS ''IndexDepth''
												 FROM     sys.Dm_db_index_physical_stats(DB_ID('''+@dbname+'''),NULL,NULL,NULL,''SAMPLED'') f
												 GROUP BY object_id,
														  index_id) f
									   ON i.object_id = f.object_id
										  AND i.index_id = f.index_id
									 LEFT JOIN ['+@dbname+'].sys.dm_db_index_usage_stats u
									   ON f.object_id = u.object_id
										  AND f.index_id = u.index_id
										  AND u.database_id = DB_ID('''+@dbname+''')
									 INNER JOIN sys.Dm_db_index_operational_stats(DB_ID('''+@dbname+'''),NULL,NULL,NULL) s
									   ON i.object_id = s.object_id
										  AND i.index_id = s.index_id
							  WHERE  so.TYPE = ''U''
							  ) indexes
							ORDER BY 
								TableName,
								IndexName'
					 --Print @cmd
					 EXEC(@cmd)
					  
				END
			END
		FETCH NEXT FROM db INTO @dbname
	END

	CLOSE db
	DEALLOCATE db
	
	Select ind.*, col.name as [Column Name] from IndexUsageStatistics ind
	inner join      
	sys.index_columns ic on        
	ind.objectid = ic.object_id 
	and ind.indexid = ic.index_id 
	inner join     sys.columns col on       
	ic.object_id = col.object_id 
	and ic.column_id = col.column_id  
	
	DROP TABLE #dbnames
	SET nocount OFF

Open in new window


Old One :

 
DROP TABLE dbo.IndexUsageStatistics
Go
DROP TABLE dbo.IndexUsageStatisticsHistory
Go
CREATE TABLE dbo.IndexUsageStatistics(
	ServerName nvarchar(128),
	DBName nvarchar(128),
	SchemaName nvarchar(128),
	TableName nvarchar(128),
	IndexName nvarchar(128),
	IndexID int,
	ObjectID int, 
	IsUsed bit,
	IsExpensive bit,
	TypeDescription nvarchar(60),
	UserReads bigint,
	UserWrites bigint,
	Reads bigint,
	LeafWrites bigint,
	LeafPageSplits bigint,
	NonleafWrites bigint,
	NonleafPageSplits bigint,
	UserSeeks bigint,
	UserScans bigint,
	UserLookups bigint,
	UserUpdates bigint,
	LastUserSeek datetime,
	LastUserScan datetime,
	LastUserLookup datetime,
	LastUserUpdate datetime,
	RecordCount bigint,
	TotalPageCount bigint,
	IndexSizeInMegabytes float,
	AverageRecordSizeInBytes float,
	IndexDepth int,
	RecordedDateTime datetime
) 

CREATE TABLE dbo.IndexUsageStatisticsHistory(
	ServerName nvarchar(128),
	DBName nvarchar(128),
	SchemaName nvarchar(128),	
	TableName nvarchar(128),
	IndexName nvarchar(128),
	IndexID int,
	ObjectID int, 
	IsUsed bit,
	IsExpensive bit,
	TypeDescription nvarchar(60),
	UserReads bigint,
	UserWrites bigint,
	Reads bigint,
	LeafWrites bigint,
	LeafPageSplits bigint,
	NonleafWrites bigint,
	NonleafPageSplits bigint,
	UserSeeks bigint,
	UserScans bigint,
	UserLookups bigint,
	UserUpdates bigint,
	LastUserSeek datetime,
	LastUserScan datetime,
	LastUserLookup datetime,
	LastUserUpdate datetime,
	RecordCount bigint,
	TotalPageCount bigint,
	IndexSizeInMegabytes float,
	AverageRecordSizeInBytes float,
	IndexDepth int,
	RecordedDateTime datetime
) 


IF EXISTS (
  SELECT 1
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME = N'GatherIndexUsageStatistics' 
)
DROP PROCEDURE GatherIndexUsageStatistics
GO
/*
exec GatherIndexUsageStatistics 'ALL',1
select * from IndexUsageStatistics
*/
CREATE PROCEDURE GatherIndexUsageStatistics
				@DatabaseList           VARCHAR(MAX),
				@ExcludeSystemDatabases tinyint = 1
AS
  SET NOCOUNT ON
/*****************************************
* Truncate holding tables
*****************************************/
  IF EXISTS (SELECT 1
             FROM   dbo.IndexUsageStatistics)
    BEGIN
        INSERT INTO dbo.IndexUsageStatisticsHistory
        SELECT *
        FROM   dbo.IndexUsageStatistics;

        TRUNCATE TABLE dbo.IndexUsageStatistics;
    END

  DECLARE @cmd VARCHAR(8000),
		@servername VARCHAR(256),
		@dbname     VARCHAR(256),
		@recordeddatetime datetime

	CREATE TABLE #dbnames
	(
		name NVARCHAR(128)
	)
	
  SET @recordeddatetime = GETDATE()
  SET @servername = CAST(Serverproperty('servername') AS VARCHAR(256))

    IF Upper(@DatabaseList) = 'ALL'
    BEGIN
        IF @ExcludeSystemDatabases = 1
          BEGIN
              SET @DatabaseList = '';

              SELECT @DatabaseList = @DatabaseList + '''' + name + ''','
              FROM   MASTER.dbo.sysdatabases
              WHERE  name NOT IN ( 'master', 'msdb', 'model', 'pubs',
                                   'northwind', 'tempdb' );
          END
        ELSE
          BEGIN
              SELECT @DatabaseList = @DatabaseList + '''' + name + ''','
              FROM   MASTER.dbo.sysdatabases;
          END

        SET @DatabaseList = LEFT(@DatabaseList, Len(@DatabaseList) - 2) + ''''
        
		INSERT INTO #dbnames
		EXEC('select name from master.dbo.sysdatabases where name in ('+@DatabaseList+')')
        --Print   @DatabaseList
    END
    --found at http://mangalpardeshi.blogspot.com/2009/03/how-to-split-comma-delimited-string.html
		;WITH Cte AS
		(
			select CAST('<M>' + REPLACE( @DatabaseList,  ',' , '</M><M>') + '</M>' AS XML) AS DatabaseNames
		)
		
		insert into #dbnames
		SELECT 	Split.a.value('.', 'VARCHAR(100)') AS DatabaseNames
		FROM Cte
		CROSS APPLY DatabaseNames.nodes('/M') Split(a)

    IF not exists(select 1 from #dbnames)
    BEGIN
		insert into #dbnames select @DatabaseList
    END

  DECLARE db CURSOR FAST_FORWARD FOR
    SELECT name
    FROM   #dbnames

  OPEN db

  FETCH NEXT FROM db INTO @dbname

  WHILE ( @@FETCH_STATUS <> -1 )
    BEGIN
        IF ( @@FETCH_STATUS <> -2 )
          BEGIN
              IF (SELECT CONVERT(SYSNAME, Databasepropertyex(@dbname, 'status'))
                 )
                 =
                 'ONLINE'
                BEGIN  
                
                set @cmd ='
					INSERT INTO dbo.IndexUsageStatistics
					SELECT   *
							,'''+convert(varchar,@recordeddatetime, 121)+'''
					FROM     (SELECT '''+@servername+''' AS ''ServerName'',
									 '''+@dbname+''' AS ''DBName'',
									Object_schema_name(so.object_id,DB_ID('''+@dbname+''')) as ''SchemaName'',
									OBJECT_NAME(so.object_id,db_id('''+@dbname+''')) as ''TableName'',
									 i.name AS ''IndexName'',i.index_id as ''IndexID'',so.object_id as ''ObjectID'',
									 Convert(BIT,CASE 
															 WHEN u.object_id IS NULL
															 THEN 0
															 ELSE 1
														   END) as ''IsUsed'',
									 Convert(BIT,CASE 
																  WHEN (i.type_desc <> ''HEAP''
																		AND (leaf_insert_count
																			   + leaf_update_count
																			   + leaf_delete_count) > (range_scan_count
																										 + singleton_lookup_count))
																  THEN 1
																  ELSE 0
																END) as ''IsExpensive'',
									 i.type_desc,
									 u.user_seeks
													+ u.user_scans
													+ u.user_lookups as ''UserReads'',
									 u.user_updates as ''UserWrites'',
									 Reads = range_scan_count
											   + singleton_lookup_count,
									 leaf_insert_count
													   + leaf_update_count
													   + leaf_delete_count as''LeafWrites'',
									 leaf_allocation_count as ''LeafPageSplits'',
									 nonleaf_insert_count
									 + nonleaf_update_count
									 + nonleaf_delete_count as ''NonleafWrites'',
									  nonleaf_allocation_count as ''NonleafPageSplits'',
									 u.user_seeks,
									 u.user_scans,
									 u.user_lookups,
									 u.user_updates,
									 u.last_user_seek,
									 u.last_user_scan,
									 u.last_user_lookup,
									 u.last_user_update,
									 f.record_count,
									 f.page_count,
									 f.IndexSizeInMegabytes,
									 f.AverageRecordSizeInBytes,
									 f.IndexDepth
							  FROM   ['+@dbname+'].sys.indexes i
									 INNER JOIN ['+@dbname+'].sys.objects so
									   ON so.object_id = i.object_id
									 INNER JOIN (SELECT   object_id,
														  index_id,
														  Sum(record_count) AS record_count,
														  Sum(page_count) AS page_count,
														  Convert(FLOAT,Sum(page_count))
															* 8192
															/ 1024
															/ 1024 AS ''IndexSizeInMegabytes'',
														  Avg(avg_record_size_in_bytes) AS ''AverageRecordSizeInBytes'',
														  Sum(index_depth) AS ''IndexDepth''
												 FROM     sys.Dm_db_index_physical_stats(DB_ID('''+@dbname+'''),NULL,NULL,NULL,''SAMPLED'') f
												 GROUP BY object_id,
														  index_id) f
									   ON i.object_id = f.object_id
										  AND i.index_id = f.index_id
									 LEFT JOIN ['+@dbname+'].sys.dm_db_index_usage_stats u
									   ON f.object_id = u.object_id
										  AND f.index_id = u.index_id
										  AND u.database_id = DB_ID('''+@dbname+''')
									 INNER JOIN sys.Dm_db_index_operational_stats(DB_ID('''+@dbname+'''),NULL,NULL,NULL) s
									   ON i.object_id = s.object_id
										  AND i.index_id = s.index_id
							  WHERE  so.TYPE = ''U''
							  ) indexes
							ORDER BY 
								TableName,
								IndexName'
					 --Print @cmd
					 EXEC(@cmd)
					  
				END
			END
		FETCH NEXT FROM db INTO @dbname
	END

	CLOSE db
	DEALLOCATE db
	
	Select ind.*, col.name as [Column Name] from IndexUsageStatistics ind
	inner join      
	sys.index_columns ic on        
	ind.objectid = ic.object_id 
	and ind.indexid = ic.index_id 
	inner join     sys.columns col on       
	ic.object_id = col.object_id 
	and ic.column_id = col.column_id  
	
	DROP TABLE #dbnames
	SET nocount OFF

Open in new window


Your database is case sensitive , so you need to modify it in few places.
DROP TABLE dbo.IndexUsageStatistics
Go
DROP TABLE dbo.IndexUsageStatisticsHistory
Go
CREATE TABLE dbo.IndexUsageStatistics(
	ServerName nvarchar(128),
	DBName nvarchar(128),
	SchemaName nvarchar(128),
	TableName nvarchar(128),
	IndexName nvarchar(128),
	--IndexID int,
	--ObjectID int, 
	IsUsed bit,
	IsExpensive bit,
	TypeDescription nvarchar(60),
	UserReads bigint,
	UserWrites bigint,
	Reads bigint,
	LeafWrites bigint,
	LeafPageSplits bigint,
	NonleafWrites bigint,
	NonleafPageSplits bigint,
	UserSeeks bigint,
	UserScans bigint,
	UserLookups bigint,
	UserUpdates bigint,
	LastUserSeek datetime,
	LastUserScan datetime,
	LastUserLookup datetime,
	LastUserUpdate datetime,
	RecordCount bigint,
	TotalPageCount bigint,
	IndexSizeInMegabytes float,
	AverageRecordSizeInBytes float,
	IndexDepth int,
	RecordedDateTime datetime
) 

CREATE TABLE dbo.IndexUsageStatisticsHistory(
	ServerName nvarchar(128),
	DBName nvarchar(128),
	SchemaName nvarchar(128),	
	TableName nvarchar(128),
	IndexName nvarchar(128),
	--IndexID int,
	--ObjectID int, 
	IsUsed bit,
	IsExpensive bit,
	TypeDescription nvarchar(60),
	UserReads bigint,
	UserWrites bigint,
	Reads bigint,
	LeafWrites bigint,
	LeafPageSplits bigint,
	NonleafWrites bigint,
	NonleafPageSplits bigint,
	UserSeeks bigint,
	UserScans bigint,
	UserLookups bigint,
	UserUpdates bigint,
	LastUserSeek datetime,
	LastUserScan datetime,
	LastUserLookup datetime,
	LastUserUpdate datetime,
	RecordCount bigint,
	TotalPageCount bigint,
	IndexSizeInMegabytes float,
	AverageRecordSizeInBytes float,
	IndexDepth int,
	RecordedDateTime datetime
) 


IF EXISTS (
  SELECT 1
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME = N'GatherIndexUsageStatistics' 
)
DROP PROCEDURE GatherIndexUsageStatistics
GO
/*
exec GatherIndexUsageStatistics 'ALL',1
select * from IndexUsageStatistics
*/
CREATE PROCEDURE GatherIndexUsageStatistics
				@DatabaseList           VARCHAR(MAX),
				@ExcludeSystemDatabases tinyint = 1
AS
  SET NOCOUNT ON
/*****************************************
* Truncate holding tables
*****************************************/
  IF EXISTS (SELECT 1
             FROM   dbo.IndexUsageStatistics)
    BEGIN
        INSERT INTO dbo.IndexUsageStatisticsHistory
        SELECT *
        FROM   dbo.IndexUsageStatistics;

        TRUNCATE TABLE dbo.IndexUsageStatistics;
    END

  DECLARE @cmd VARCHAR(8000),
		@servername VARCHAR(256),
		@dbname     VARCHAR(256),
		@recordeddatetime datetime

	CREATE TABLE #dbnames
	(
		name NVARCHAR(128)
	)
	
  SET @recordeddatetime = GETDATE()
  SET @servername = CAST(Serverproperty('servername') AS VARCHAR(256))

    IF Upper(@DatabaseList) = 'ALL'
    BEGIN
        IF @ExcludeSystemDatabases = 1
          BEGIN
              SET @DatabaseList = '';

              SELECT @DatabaseList = @DatabaseList + '''' + name + ''','
              FROM   MASTER.dbo.sysdatabases
              WHERE  name NOT IN ( 'master', 'msdb', 'model', 'pubs',
                                   'northwind', 'tempdb' );
          END
        ELSE
          BEGIN
              SELECT @DatabaseList = @DatabaseList + '''' + name + ''','
              FROM   MASTER.dbo.sysdatabases;
          END

        SET @DatabaseList = LEFT(@DatabaseList, Len(@DatabaseList) - 2) + ''''
        
		INSERT INTO #dbnames
		EXEC('select name from master.dbo.sysdatabases where name in ('+@DatabaseList+')')
        --Print   @DatabaseList
    END
    --found at http://mangalpardeshi.blogspot.com/2009/03/how-to-split-comma-delimited-string.html
		;WITH Cte AS
		(
			select CAST('<M>' + REPLACE( @DatabaseList,  ',' , '</M><M>') + '</M>' AS XML) AS DatabaseNames
		)
		
		insert into #dbnames
		SELECT 	Split.a.value('.', 'VARCHAR(100)') AS DatabaseNames
		FROM Cte
		CROSS APPLY DatabaseNames.nodes('/M') Split(a)

    IF not exists(select 1 from #dbnames)
    BEGIN
		insert into #dbnames select @DatabaseList
    END

  DECLARE db CURSOR FAST_FORWARD FOR
    SELECT name
    FROM   #dbnames

  OPEN db

  FETCH NEXT FROM db INTO @dbname

  WHILE ( @@FETCH_STATUS <> -1 )
    BEGIN
        IF ( @@FETCH_STATUS <> -2 )
          BEGIN
              IF (SELECT CONVERT(SYSNAME, Databasepropertyex(@dbname, 'status'))
                 )
                 =
                 'ONLINE'
                BEGIN  
                
                set @cmd ='
					INSERT INTO dbo.IndexUsageStatistics
					SELECT   *
							,'''+convert(varchar,@recordeddatetime, 121)+'''
					FROM     (SELECT '''+@servername+''' AS ''ServerName'',
									 '''+@dbname+''' AS ''DBName'',
									Object_schema_name(so.object_id,DB_ID('''+@dbname+''')) as ''SchemaName'',
									OBJECT_NAME(so.object_id,db_id('''+@dbname+''')) as ''TableName'',
									 i.name AS ''IndexName'',
									 Convert(BIT,CASE 
															 WHEN u.object_id IS NULL
															 THEN 0
															 ELSE 1
														   END) as ''IsUsed'',
									 Convert(BIT,CASE 
																  WHEN (i.type_desc <> ''HEAP''
																		AND (leaf_insert_count
																			   + leaf_update_count
																			   + leaf_delete_count) > (range_scan_count
																										 + singleton_lookup_count))
																  THEN 1
																  ELSE 0
																END) as ''IsExpensive'',
									 i.type_desc,
									 u.user_seeks
													+ u.user_scans
													+ u.user_lookups as ''UserReads'',
									 u.user_updates as ''UserWrites'',
									 Reads = range_scan_count
											   + singleton_lookup_count,
									 leaf_insert_count
													   + leaf_update_count
													   + leaf_delete_count as''LeafWrites'',
									 leaf_allocation_count as ''LeafPageSplits'',
									 nonleaf_insert_count
									 + nonleaf_update_count
									 + nonleaf_delete_count as ''NonleafWrites'',
									  nonleaf_allocation_count as ''NonleafPageSplits'',
									 u.user_seeks,
									 u.user_scans,
									 u.user_lookups,
									 u.user_updates,
									 u.last_user_seek,
									 u.last_user_scan,
									 u.last_user_lookup,
									 u.last_user_update,
									 f.record_count,
									 f.page_count,
									 f.IndexSizeInMegabytes,
									 f.AverageRecordSizeInBytes,
									 f.IndexDepth
							  FROM   ['+@dbname+'].sys.indexes i
									 INNER JOIN ['+@dbname+'].sys.objects so
									   ON so.object_id = i.object_id
									 INNER JOIN (SELECT   object_id,
														  index_id,
														  Sum(record_count) AS record_count,
														  Sum(page_count) AS page_count,
														  Convert(FLOAT,Sum(page_count))
															* 8192
															/ 1024
															/ 1024 AS ''IndexSizeInMegabytes'',
														  Avg(avg_record_size_in_bytes) AS ''AverageRecordSizeInBytes'',
														  Sum(index_depth) AS ''IndexDepth''
												 FROM     sys.Dm_db_index_physical_stats(DB_ID('''+@dbname+'''),NULL,NULL,NULL,''SAMPLED'') f
												 GROUP BY object_id,
														  index_id) f
									   ON i.object_id = f.object_id
										  AND i.index_id = f.index_id
									 LEFT JOIN ['+@dbname+'].sys.dm_db_index_usage_stats u
									   ON f.object_id = u.object_id
										  AND f.index_id = u.index_id
										  AND u.database_id = DB_ID('''+@dbname+''')
									 INNER JOIN sys.Dm_db_index_operational_stats(DB_ID('''+@dbname+'''),NULL,NULL,NULL) s
									   ON i.object_id = s.object_id
										  AND i.index_id = s.index_id
							  WHERE  so.TYPE = ''U''
							  ) indexes
							ORDER BY 
								TableName,
								IndexName'
					 --Print @cmd
					 EXEC(@cmd)
					  
				END
			END
		FETCH NEXT FROM db INTO @dbname
	END

	CLOSE db
	DEALLOCATE db
	
	Select * --ind.*, col.name as [Column Name] 
	from IndexUsageStatistics --ind
	--inner join      
	--sys.index_columns ic on        
	--ind.objectid = ic.object_id 
	--and ind.indexid = ic.index_id 
	--inner join     sys.columns col on       
	--ic.object_id = col.object_id 
	--and ic.column_id = col.column_id  
	
	DROP TABLE #dbnames
	SET nocount OFF

Open in new window

0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
The last one work and I change the DMV characters.

Any way to veify this script?

0
 
LVL 9

Expert Comment

by:mimran18
Comment Utility
I think you need to use the old one it was verified on your machine.
But I posted the new one and old one both are tested and verified.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
ok, both script are verified, right?

But my point is we can use the bulid in report feature of SLQ server2008, it has a index statistic report, right?

DBA100.
0
 
LVL 9

Expert Comment

by:mimran18
Comment Utility
I did not use it.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
is there anyway to make the final script show what field of the table the index cover?
0
 
LVL 1

Author Closing Comment

by:marrowyung
Comment Utility
Very Good.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now