October 27, 2014 at 2:39 pm
Comments posted to this topic are about the item Complete Index Detail
November 10, 2014 at 8:34 am
Script produces bugs:
Msg 15009, Level 16, State 1, Procedure sp_spaceused, Line 62
The object 't_DispCSV' does not exist in database 'EPCentral' or is invalid for this operation.
Even though script has only 58 lines, it shows an error at line 62. Tried to comment it but no luck.
Tried to comment just EXEC sp_spaceused @strTableName, but received two empty result sets.
Is this I am doing something wrong or this script was not tested for all circumstances?
November 10, 2014 at 8:38 am
hi
in the line
SET @strTableName = 't_DispCSV'
you should put the name of the table you want to analyze
SET @strTableName = 'TableNameToAnalyze'
November 18, 2014 at 7:58 am
Thank you for the script. It's a really quick way to look at a table. Sometime, unfortunately, I am tuning "on the fly" and this will help.
Thanks again.
November 18, 2014 at 8:07 am
You are wellcome
I have a few others that works in the tuning department
November 19, 2014 at 9:06 am
This is a really good script for a quick overview of the desired table and its indexes. One addition that needs to be made though is to account for the schema. In my case, I have my staging tables and business model tables in the same database. The table names are the same, but the schema controls which tables are used for the ETL processes in SSIS. Because of the schemas not being accounted for, your original version of the script brings up both schemas and the associated data for a given table name. I have modified the script to account for schema name and offer it back to you for review and correction of any mistakes I may have made. It works in my testing so far:
DECLARE @strtablename AS VARCHAR (50);
DECLARE @strschemaname AS VARCHAR (50);
SET @strtablename = 'tablename'; -- insert your desired table name here
SET @strschemaname = 'dbo'; -- change the schema name to whatever is appropriate if other than dbo
EXEC sp_spaceused @strtablename;
WITH CTE AS
(
SELECT ic.[index_id] + ic.[object_id] AS [IndexId]
, t.[name] AS [TableName]
, s.[name] AS [SchemaName]
, i.[name] AS [IndexName]
, c.[name] AS [ColumnName]
, i.[type_desc]
, i.[is_primary_key]
, i.[is_unique]
, ic.is_included_column
, ic.[index_id]
, ic.[object_id]
FROM [sys].[indexes] AS i
INNER JOIN [sys].[index_columns] AS ic ON i.[index_id] = ic.[index_id]
AND i.[object_id] = ic.[object_id]
INNER JOIN [sys].[columns] AS c ON ic.[column_id] = c.[column_id]
AND i.[object_id] = c.[object_id]
INNER JOIN [sys].[tables] AS t ON i.[object_id] = t.[object_id]
INNER JOIN [sys].[schemas] AS s ON s.[schema_id] = t.[schema_id]
)
SELECT c.[TableName]
, c.[IndexName]
, c.[type_desc]
, c.[is_primary_key]
, c.[is_unique]
, STUFF ( ( SELECT ',' + a.[ColumnName]
FROM CTE AS a
WHERE a.is_included_column = 0
AND c.[IndexId] = a.[IndexId]
FOR XML PATH ('') )
, 1
, 1
, '' ) AS [Columns]
, STUFF ( ( SELECT ',' + a.[ColumnName]
FROM CTE AS a
WHERE a.is_included_column = 1
AND c.[IndexId] = a.[IndexId]
FOR XML PATH ('') )
, 1
, 1
, '' ) AS [IncludedColumns]
, 'drop index [' + c.[IndexName] + '] on ' + c.[TableName] AS DropStatment
FROM CTE AS c
WHERE c.[TableName] = @strtablename
AND c.[SchemaName] = @strschemaname
GROUP BY c.[IndexId]
, c.[TableName]
, c.[IndexName]
, c.[type_desc]
, c.[is_primary_key]
, c.[is_unique]
ORDER BY c.[TableName]
, c.[IndexName];
SELECT OBJECT_NAME ( ind.OBJECT_ID ) AS TableName
, ind.NAME AS IndexName
, STATS_DATE ( ind.OBJECT_ID
, istats.index_id ) AS statistics_update_date
, user_seeks
, user_scans
, user_lookups
, user_updates
, (istats.user_seeks + istats.user_scans + istats.user_lookups) * 100.00 / (user_updates + 1)AS UseVsUpdate
, COALESCE ( CAST ( 100 * (istats.user_seeks + istats.user_scans + istats.user_lookups) / (NULLIF (
SUM ( istats.user_seeks +
istats.user_scans +
istats.user_lookups )
OVER (
PARTITION BY ind.object_id)
, 0 ) * 1.)AS DECIMAL ( 6, 2 ))
, 0 ) AS user_total_pct
, CAST ( reserved_page_count * CAST ( 8 AS FLOAT ) / 1024 AS DECIMAL ( 12, 2 )) AS size_in_mb
, row_count
, 'drop index [' + ind.NAME + '] on ' + OBJECT_NAME (ind.OBJECT_ID) AS DropStatment
, ind.OBJECT_ID
, ind.index_id
, ind.*
FROM sys.indexes AS ind
INNER JOIN sys.tables AS t ON t.object_id = ind.object_id
INNER JOIN sys.schemas AS s ON s.schema_id = t.schema_id
INNER JOIN sys.dm_db_index_usage_stats AS istats ON ind.index_id = istats.index_id
AND ind.OBJECT_ID = istats.OBJECT_ID
AND istats.database_id = DB_ID ( )
INNER JOIN sys.dm_db_partition_stats AS part ON ind.index_id = part.index_id
AND ind.OBJECT_ID = part.OBJECT_ID
WHERE t.name = @strtablename
AND s.name = @strschemaname
ORDER BY OBJECT_NAME ( ind.OBJECT_ID )
, ind.NAME;
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply