Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 434
  • Last Modified:

Detail check on the update of statistics

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
marrowyung
Asked:
marrowyung
  • 22
  • 17
1 Solution
 
mimran18Commented:
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
 
marrowyungAuthor Commented:
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
 
mimran18Commented:
I gave you 3 scripts.Which one script is not working ?
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
marrowyungAuthor Commented:
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
 
marrowyungAuthor Commented:
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
 
mimran18Commented:
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
 
marrowyungAuthor Commented:
AFter i run this, and run "Select * from IndexUsageStatistics", I see no result.
0
 
mimran18Commented:
Hi,
    Execute it like this.
exec GatherIndexUsageStatistics 'ALL',1

Because I am getting the records.
0
 
marrowyungAuthor Commented:
it say:

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


0
 
mimran18Commented:
which one sql server version are you using ?
Are you using it as an administraor ?

0
 
mimran18Commented:
also execute this query.

SELECT * FROM master.dbo.sysdatabases

0
 
marrowyungAuthor Commented:
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
 
mimran18Commented:
Hi ,
  Try this with your database name
exec GatherIndexUsageStatistics 'DBName',1

also check your compatibilty level.It should be 90 and above.
0
 
marrowyungAuthor Commented:
this time, it said:

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


DBA100.
0
 
marrowyungAuthor Commented:
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
 
mimran18Commented:
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
 
marrowyungAuthor Commented:
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
 
mimran18Commented:
you need to drop the table and create again.
0
 
marrowyungAuthor Commented:
I found out that I have to create the SP in master DB.  But will the table created keep cleaning each time ?

DBA100.
0
 
marrowyungAuthor Commented:
mimran18:

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

DBA100.
0
 
mimran18Commented:
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
 
marrowyungAuthor Commented:
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
 
marrowyungAuthor Commented:
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
 
mimran18Commented:
Now you need to browse "IndexUsageStatistics " table and check that these columns are there or not
0
 
marrowyungAuthor Commented:
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
 
marrowyungAuthor Commented:
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
 
mimran18Commented:
because there will be no data in "IndexUsageStatistics"
And all of them are inner join.
0
 
marrowyungAuthor Commented:
no there are data in this table
0
 
mimran18Commented:
Now you need to check in "sys.index_columns"
And  "sys.columns" Table
Any link  is missing.
0
 
marrowyungAuthor Commented:
both  "sys.index_columns"
And  "sys.columns" Table has data.

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

DBA100.
0
 
mimran18Commented:
Hi,
   I ran it and it gave the data.
  Please check that do you have indexes in your table ?
 
0
 
marrowyungAuthor Commented:
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
 
mimran18Commented:
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
 
marrowyungAuthor Commented:
The last one work and I change the DMV characters.

Any way to veify this script?

0
 
mimran18Commented:
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
 
marrowyungAuthor Commented:
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
 
mimran18Commented:
I did not use it.
0
 
marrowyungAuthor Commented:
is there anyway to make the final script show what field of the table the index cover?
0
 
marrowyungAuthor Commented:
Very Good.
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 22
  • 17
Tackle projects and never again get stuck behind a technical roadblock.
Join Now