Solved

Detail check on the update of statistics

Posted on 2011-09-11
39
426 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 22
  • 17
39 Comments
 
LVL 9

Expert Comment

by:mimran18
ID: 36520849
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
ID: 36520948
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
ID: 36520952
I gave you 3 scripts.Which one script is not working ?
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 1

Author Comment

by:marrowyung
ID: 36529363
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
ID: 36533636
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
ID: 36534058
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
ID: 36534537
AFter i run this, and run "Select * from IndexUsageStatistics", I see no result.
0
 
LVL 9

Expert Comment

by:mimran18
ID: 36534596
Hi,
    Execute it like this.
exec GatherIndexUsageStatistics 'ALL',1

Because I am getting the records.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 36535043
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
ID: 36535068
which one sql server version are you using ?
Are you using it as an administraor ?

0
 
LVL 9

Expert Comment

by:mimran18
ID: 36535070
also execute this query.

SELECT * FROM master.dbo.sysdatabases

0
 
LVL 1

Author Comment

by:marrowyung
ID: 36535142
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
ID: 36535173
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
ID: 36540583
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
ID: 36540610
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
ID: 36540863
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
ID: 36541000
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
ID: 36541068
you need to drop the table and create again.
0
 
LVL 1

Author Comment

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

DBA100.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 36541096
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
ID: 36541178
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
ID: 36541196
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
ID: 36541217
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
ID: 36541224
Now you need to browse "IndexUsageStatistics " table and check that these columns are there or not
0
 
LVL 1

Author Comment

by:marrowyung
ID: 36541234
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
ID: 36541239
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
ID: 36541280
because there will be no data in "IndexUsageStatistics"
And all of them are inner join.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 36541289
no there are data in this table
0
 
LVL 9

Expert Comment

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

Author Comment

by:marrowyung
ID: 36541363
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
ID: 36541379
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
ID: 36541389
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
ID: 36541563
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
ID: 36541609
The last one work and I change the DMV characters.

Any way to veify this script?

0
 
LVL 9

Expert Comment

by:mimran18
ID: 36541711
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
ID: 36541757
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
ID: 36542014
I did not use it.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 36546971
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
ID: 36556006
Very Good.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

617 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