Jan 262012
 

Knowing when and how often your indexes are used can really come in handy. Indexes that are never utilized aren’t worth keeping around, and knowing their usage patterns can be a big help when making decisions regarding things such as filegroup placement and compression settings. SQL Server 2005 brought some great advancements in the form of the sys.dm_db_index_usage_stats DMV, which returns statistics on index usage that are automatically being kept by the server. While it’s a step forward, I feel it still leaves a few things to be desired:

  • The values are cumulative since the server was last restarted
  • Once the server is shut down or restarted, there’s no way to get the previous values back

CollectableWith this in mind, I came up with my own system for archiving non-cumulative index usage stats on a daily basis. Microsoft introduced Management Data Warehouse in SQL Server 2008 which provides a way to keep track of these statistics over time (along with many others), but I’ve been using my own method longer than that. This will also work in SQL Server Express Edition, which doesn’t support MDW. I’ll give descriptions of each part below, but if you just want the code right now download it here.

Tables

IndexUsageStats_LastCumulative
As I mentioned before, the output from sys.dm_db_index_usage_stats is cumulative since the last time the server was restarted, and I was looking for a way to find daily values that weren’t cumulative. Restarting the server immediately after collecting the values would have accomplished this, but users probably wouldn’t be very happy :). Instead I just keep track of the previous cumulative values and subtract them from the current ones. If the output shows an index was used 7 times on Tuesday at Midnight, and the same number was 12 on Wednesday at Midnight, then it must have been used 5 times in that 24 hour period. IndexUsageStats_LastCumulative holds the previous cumulative values for all indexes to make this calculation possible.

Names
The only true way to identify an object in SQL Server is by its name, but DMVs typically return object IDs instead of names. It’s easy to translate between names and IDs, but over time names and IDs can change:
– If an object is dropped and re-created with the same name, chances of it having the same ID are virtually zero.
– If an object is renamed, its ID will not change, but of course its name will.
Using either IDs or Names exclusively can make historical values become useless over time – you might have an ID that’s no longer valid or a name that changed a while back. To give myself more options for tracking changes over time, I store both. Since storing each object’s name in a character data type would consume a lot of space and be repetitive, I prefer to map object names to numeric values, and this table facilitates that.

IndexUsageStats
This table does the heavy lifting storing for our system – it contains all the historical index usage statistics by both object ID and name. I’ve found it to be a great candidate for compression if you’re running enterprise edition, but it’s still pretty compact even when uncompressed thanks to the Names table.

Stored Procedures

CollectIndexUsageStats
The stored procedure that drives statistics collection. You’ll want to set up a job to run this – mine runs at midnight. Basically it iterates through each online database in the instance collecting usage stats and then compares them to the previous cumulative values and writes the differences to the IndexUsageStats table. You’ll find comments in-line.

Views

vw_IndexUsageStats
Since the data contained in the IndexUsageStats table is all numeric, it’s not the easiest to browse. This view makes some joins back to the Names table so things are a little more user-friendly. Most of the queries I write are against this view. Object IDs aren’t returned here as they’re typically not needed. When they are necessary, I’ll write more detailed queries against IndexUsageStats.

A few notes before the code:

  • As always, this code is offered as-is with no warranty whatsoever. Do not deploy this in a production environment before you have tested it and understand exactly what it does.
  • I recommend putting all these objects in their own database. If you already have a DBA database for management information that should work fine too. My database is called “iDBA” because I inherited it with that name.
  • This was written and tested on SQL Server 2008 and 2008R2. It can probably be tweaked to run in 2005 by removing the datetime2 datatype and writing substitute queries for the MERGE statement.
-----------------------------------------------------------
-- Name: CollectIndexUsageStats.sql
--
-- Description: Collects index usage statistics over time.
--
-- Author: Bob Pusateri, https://www.bobpusateri.com
--
-- THIS SCRIPT IS PROVIDED "AS-IS" WITHOUT ANY WARRANTY.
-- DO NOT RUN THIS ON A PRODUCTION SYSTEM UNTIL YOU HAVE
--   COMPLETE UNDERSTANDING OF THE TASKS IT PERFORMS AND
--   HAVE TESTED IT ON A DEVELOPMENT SYSTEM.
-----------------------------------------------------------

-- I recommend putting these objects in a separate database
-- (or your existing DBA database if you have one)
--
-- If your DBA database is not named 'iDBA', you'll want to replace it with your DB name
--     It is referenced several times in the script below
USE [iDBA];
GO

-- stores cumulative data from sys.dm_db_index_usage_stats DMV
CREATE TABLE [dbo].[IndexUsageStats_LastCumulative] (
	[ServerNameID] [int] NOT NULL,
	[DatabaseID] [smallint] NOT NULL,
	[ObjectID] [int] NOT NULL,
	[IndexID] [int] NOT NULL,
	[LoadTime] [datetime2](0) NOT NULL,
	[User_Seeks] [bigint] NOT NULL,
	[User_Scans] [bigint] NOT NULL,
	[User_Lookups] [bigint] NOT NULL,
	[User_Updates] [bigint] NOT NULL,
	[System_Seeks] [bigint] NOT NULL,
	[System_Scans] [bigint] NOT NULL,
	[System_Lookups] [bigint] NOT NULL,
	[System_Updates] [bigint] NOT NULL,
	CONSTRAINT [PK_IUS_C] PRIMARY KEY CLUSTERED ([ServerNameID],[DatabaseID],[ObjectID],[IndexID])
);
GO

-- used for Server/DB/Schema/Table/Index name mapping
CREATE TABLE [dbo].[Names] (
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Value] [nvarchar](260) NOT NULL,
	CONSTRAINT [PK_Names] PRIMARY KEY CLUSTERED ([ID])
);
GO

-- stores historical usage statistics
CREATE TABLE [dbo].[IndexUsageStats] (
	[StatsDate] [datetime2](0) NOT NULL,
	[ServerNameID] [int] NOT NULL,
	[DatabaseID] [smallint] NOT NULL,
	[ObjectID] [int] NOT NULL,
	[IndexID] [int] NOT NULL,
	[DatabaseNameID] [int] NOT NULL,
	[SchemaNameID] [int] NOT NULL,
	[TableNameID] [int] NOT NULL,
	[IndexNameID] [int] NULL,
	[User_Seeks] [bigint] NOT NULL,
	[User_Scans] [bigint] NOT NULL,
	[User_Lookups] [bigint] NOT NULL,
	[User_Updates] [bigint] NOT NULL,
	[System_Seeks] [bigint] NOT NULL,
	[System_Scans] [bigint] NOT NULL,
	[System_Lookups] [bigint] NOT NULL,
	[System_Updates] [bigint] NOT NULL,
	CONSTRAINT [PK_IUS] PRIMARY KEY CLUSTERED ([StatsDate],[ServerNameID],[DatabaseID],[ObjectID],[IndexID]),
	CONSTRAINT [FK_IUS_Names_DB] FOREIGN KEY([DatabaseNameID]) REFERENCES [dbo].[Names] ([ID]),
	CONSTRAINT [FK_IUS_Names_Index] FOREIGN KEY([IndexNameID]) REFERENCES [dbo].[Names] ([ID]),
	CONSTRAINT [FK_IUS_Names_Schema] FOREIGN KEY([SchemaNameID]) REFERENCES [dbo].[Names] ([ID]),
	CONSTRAINT [FK_IUS_Names_Table] FOREIGN KEY([TableNameID]) REFERENCES [dbo].[Names] ([ID]),
	CONSTRAINT [CK_IUS_PositiveValues] CHECK ([User_Seeks]>=(0) AND [User_Scans]>=(0) AND [user_Lookups]>=(0)
		AND [user_updates]>=(0) AND [system_seeks]>=(0) AND [system_scans]>=(0) AND [system_lookups]>=(0)
		AND [system_updates]>=(0))
);
GO

-- collects usage statistics
-- I run this once daily (can be run more often if you like)
CREATE PROCEDURE [dbo].[CollectIndexUsageStats]
AS
BEGIN
	BEGIN TRY
		SET NOCOUNT ON;

		-- get current stats for all online databases

		SELECT database_id, name
		INTO #dblist
		FROM sys.databases
		WHERE [state] = 0
			AND database_id != 2; -- skip TempDB

		CREATE TABLE #t (
			StatsDate DATETIME2(0),
			ServerName SYSNAME,
			DatabaseID SMALLINT,
			ObjectID INT,
			IndexID INT,
			DatabaseName SYSNAME,
			SchemaName SYSNAME,
			TableName SYSNAME,
			IndexName SYSNAME NULL,
			User_Seeks BIGINT,
			User_Scans BIGINT,
			User_Lookups BIGINT,
			User_Updates BIGINT,
			System_Seeks BIGINT,
			System_Scans BIGINT,
			System_Lookups BIGINT,
			System_Updates BIGINT
		);

		DECLARE @DBID INT;
		DECLARE @DBNAME SYSNAME;
		DECLARE @Qry NVARCHAR(2000);

		-- iterate through each DB, generate & run query
		WHILE (SELECT COUNT(*) FROM #dblist) > 0
		BEGIN
			SELECT TOP (1) @DBID=database_id, @DBNAME=[name]
			FROM #dblist ORDER BY database_id;

			SET @Qry = '
				INSERT INTO #t
				SELECT
					SYSDATETIME() AS StatsDate,
					@@SERVERNAME AS ServerName,
					s.database_id AS DatabaseID,
					s.object_id AS ObjectID,
					s.index_id AS IndexID,
					''' + @DBNAME + ''' AS DatabaseName,
					c.name AS SchemaName,
					o.name AS TableName,
					i.name AS IndexName,
					s.user_seeks,
					s.user_scans,
					s.user_lookups,
					s.user_updates,
					s.system_seeks,
					s.system_scans,
					s.system_lookups,
					s.system_updates
				FROM sys.dm_db_index_usage_stats s
				INNER JOIN ' + @DBNAME + '.sys.objects o ON s.object_id = o.object_id
				INNER JOIN ' + @DBNAME + '.sys.schemas c ON o.schema_id = c.schema_id
				INNER JOIN ' + @DBNAME + '.sys.indexes i ON s.object_id = i.object_id and s.index_id = i.index_id
				WHERE s.database_id = ' + CONVERT(NVARCHAR,@DBID) + ';
				';

			EXEC sp_executesql @Qry;

			DELETE FROM #dblist WHERE database_id = @DBID;
		END -- db while loop

		DROP TABLE #DBList;

		BEGIN TRAN;

		-- create ids for Server Name by inserting new ones into dbo.Names
		INSERT INTO iDBA.dbo.Names (Value)
		SELECT DISTINCT RTRIM(LTRIM(t.ServerName)) AS ServerName
		FROM #t t
		LEFT JOIN iDBA.dbo.Names n ON t.ServerName = n.Value
		WHERE n.ID IS NULL AND t.ServerName IS NOT NULL
		ORDER BY RTRIM(LTRIM(t.ServerName));

		-- same as above for DatabaseName
		INSERT INTO iDBA.dbo.Names (Value)
		SELECT DISTINCT RTRIM(LTRIM(t.DatabaseName)) AS DatabaseName
		FROM #t t
		LEFT JOIN iDBA.dbo.Names n ON t.DatabaseName = n.Value
		WHERE n.ID IS NULL AND t.DatabaseName IS NOT NULL
		ORDER BY RTRIM(LTRIM(t.DatabaseName));

		-- SchemaName
		INSERT INTO iDBA.dbo.Names (Value)
		SELECT DISTINCT RTRIM(LTRIM(t.SchemaName)) AS SchemaName
		FROM #t t
		LEFT JOIN iDBA.dbo.Names n ON t.SchemaName = n.Value
		WHERE n.ID IS NULL AND t.SchemaName IS NOT NULL
		ORDER BY RTRIM(LTRIM(t.SchemaName));

		-- TableName
		INSERT INTO iDBA.dbo.Names (Value)
		SELECT DISTINCT RTRIM(LTRIM(t.TableName)) AS TableName
		FROM #t t
		LEFT JOIN iDBA.dbo.Names n ON t.TableName = n.Value
		WHERE n.ID IS NULL AND t.TableName IS NOT NULL
		ORDER BY RTRIM(LTRIM(t.TableName));

		-- IndexName
		INSERT INTO iDBA.dbo.Names (Value)
		SELECT DISTINCT RTRIM(LTRIM(t.IndexName)) AS IndexName
		FROM #t t
		LEFT JOIN iDBA.dbo.Names n ON t.IndexName = n.Value
		WHERE n.ID IS NULL AND t.IndexName IS NOT NULL
		ORDER BY RTRIM(LTRIM(t.IndexName));

		-- Calculate Deltas
		INSERT INTO iDBA.dbo.IndexUsageStats (StatsDate, ServerNameID, DatabaseID, ObjectID,
			IndexID, DatabaseNameID, SchemaNameID, TableNameID, IndexNameID, User_Seeks, User_Scans,
			User_Lookups, User_Updates, System_Seeks, System_Scans, System_Lookups, System_Updates)
		SELECT
			t.StatsDate,
			s.ID AS ServerNameID,
			t.DatabaseID,
			t.ObjectID,
			t.IndexID,
			d.ID AS DatabaseNameID,
			c.ID AS SchemaNameID,
			b.ID AS TableNameID,
			i.ID AS IndexNameID,
			CASE
				-- if the previous cumulative value is greater than the current one, the server has been reset
				-- just use the current value
				WHEN t.User_Seeks - ISNULL(lc.User_Seeks,0) < 0 THEN t.User_Seeks
				-- if the prev value is less than the current one, then subtract to get the delta
				ELSE t.User_Seeks - ISNULL(lc.User_Seeks,0)
			END AS User_Seeks,
			CASE
				WHEN t.User_Scans - ISNULL(lc.User_Scans,0) < 0 THEN t.User_Scans
				ELSE t.User_Scans - ISNULL(lc.User_Scans,0)
			END AS User_Scans,
			CASE
				WHEN t.User_Lookups - ISNULL(lc.User_Lookups,0) < 0 THEN t.User_Lookups
				ELSE t.User_Lookups - ISNULL(lc.User_Lookups,0)
			END AS User_Lookups,
			CASE
				WHEN t.User_Updates - ISNULL(lc.User_Updates,0) < 0 THEN t.User_Updates
				ELSE t.User_Updates - ISNULL(lc.User_Updates,0)
			END AS User_Updates,
			CASE
				WHEN t.System_Seeks - ISNULL(lc.System_Seeks,0) < 0 THEN t.System_Seeks
				ELSE t.System_Seeks - ISNULL(lc.System_Seeks,0)
			END AS System_Seeks,
			CASE
				WHEN t.System_Scans - ISNULL(lc.System_Scans,0) < 0 THEN t.System_Scans
				ELSE t.System_Scans - ISNULL(lc.System_Scans,0)
			END AS System_Scans,
			CASE
				WHEN t.System_Lookups - ISNULL(lc.System_Lookups,0) < 0 THEN t.System_Lookups
				ELSE t.System_Lookups - ISNULL(lc.System_Lookups,0)
			END AS System_Lookups,
			CASE
				WHEN t.System_Updates - ISNULL(lc.System_Updates,0) < 0 THEN t.System_Updates
				ELSE t.System_Updates - ISNULL(lc.System_Updates,0)
			END AS System_Updates
		FROM #t t
		INNER JOIN iDBA.dbo.Names s ON t.ServerName = s.Value
		INNER JOIN iDBA.dbo.Names d ON t.DatabaseName = d.Value
		INNER JOIN iDBA.dbo.Names c ON t.SchemaName = c.Value
		INNER JOIN iDBA.dbo.Names b ON t.TableName = b.Value
		LEFT JOIN iDBA.dbo.Names i ON t.IndexName = i.Value
		LEFT JOIN iDBA.dbo.IndexUsageStats_LastCumulative lc
			ON s.ID = lc.ServerNameID
			AND t.DatabaseID = lc.DatabaseID
			AND t.ObjectID = lc.ObjectID
			AND t.IndexID = lc.IndexID
		ORDER BY StatsDate, ServerName, DatabaseID, ObjectID, IndexID;

		-- Update last cumulative values with the current ones
		MERGE INTO iDBA.dbo.IndexUsageStats_LastCumulative lc
		USING #t t
		INNER JOIN iDBA.dbo.Names s ON t.ServerName = s.Value
		ON s.ID = lc.ServerNameID
			AND t.DatabaseID = lc.DatabaseID
			AND t.ObjectID = lc.ObjectID
			AND t.IndexID = lc.IndexID
		WHEN MATCHED THEN
			UPDATE SET
				lc.LoadTime = t.StatsDate,
				lc.User_Seeks = t.User_Seeks,
				lc.User_Scans = t.User_Scans,
				lc.User_Lookups = t.User_Lookups,
				lc.User_Updates = t.User_Updates,
				lc.System_Seeks = t.System_Seeks,
				lc.System_Scans = t.System_Scans,
				lc.System_Lookups = t.System_Lookups,
				lc.System_Updates = t.System_Updates
		WHEN NOT MATCHED BY TARGET THEN
			INSERT (ServerNameID, DatabaseID, ObjectID, IndexID, LoadTime, User_Seeks, User_Scans,
				User_Lookups, User_Updates, System_Seeks, System_Scans,
				System_Lookups, System_Updates)
			VALUES (s.ID, t.DatabaseID, t.ObjectID, t.IndexID, t.StatsDate, t.User_Seeks, t.User_Scans,
				t.User_Lookups, t.User_Updates, t.System_Seeks, t.System_Scans,
				t.System_Lookups, t.System_Updates)
		WHEN NOT MATCHED BY SOURCE
			THEN DELETE;

		COMMIT TRAN;

	END TRY
	BEGIN CATCH

		IF @@TRANCOUNT > 0
			ROLLBACK TRAN;

		DECLARE @ErrorNumber INT;
		DECLARE @ErrorSeverity INT;
		DECLARE @ErrorState INT;
		DECLARE @ErrorProcedure NVARCHAR(126);
		DECLARE @ErrorLine INT;
		DECLARE @ErrorMessage NVARCHAR(2048);

		SELECT @ErrorNumber = ERROR_NUMBER(),
			   @ErrorSeverity = ERROR_SEVERITY(),
			   @ErrorState = ERROR_STATE(),
			   @ErrorProcedure = ERROR_PROCEDURE(),
			   @ErrorLine = ERROR_LINE(),
			   @ErrorMessage = ERROR_MESSAGE();

		RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

	END CATCH
END
GO

-- displays usage statistics
CREATE VIEW [dbo].[vw_IndexUsageStats]
AS
SELECT
	s.StatsDate,
	vn.Value AS ServerName,
	dbn.Value AS DatabaseName,
	sn.Value AS SchemaName,
	tn.Value AS TableName,
	dn.Value AS IndexName,
	s.IndexID,
	s.User_Seeks,
	s.User_Scans,
	s.User_Lookups,
	s.User_Updates,
	s.System_Seeks,
	s.System_Scans,
	s.System_Lookups,
	s.System_Updates
FROM dbo.IndexUsageStats s
INNER JOIN dbo.Names vn ON s.ServerNameID = vn.ID
INNER JOIN dbo.Names dbn ON s.DatabaseNameID = dbn.ID
INNER JOIN dbo.Names sn ON s.SchemaNameID = sn.ID
INNER JOIN dbo.Names tn ON s.TableNameID = tn.ID
LEFT JOIN dbo.Names dn ON s.IndexNameID = dn.ID;
GO
Sep 142010
 

This post is my contribution to T-SQL Tuesday #10, which is being hosted by Michael J. Swart (blog | twitter)

T-SQL TuesdayEach year my Boy Scout troop has a rummage sale as a fundraiser.  All the scouts and their families gather up their old clothes, books, toys, electronics, and any other treasures they no longer need and donate them to the troop, which sells them.  It’s basically a giant garage sale, except it’s done in the gym of the school we meet at.  Since everything sold comes from someone associated with the troop, many years ago it was decided to create an award for the person that donates the weirdest piece of crap most unique item each year.  This award comes in the form of a travelling trophy which started out as a child’s potty chair that was painted gold.  Each year, the winner is instructed to embellish the trophy with something new.  Here’s how it looks now!

As you can see, it’s grown from a simple potty chair mounted on a board into a lamp with a whole bunch of accessories, including a barbecue grill. I’m surprised nobody’s added a TV or fridge yet – then there’d really be no reason to leave!

So what does this have to do with indexes?  The bowels of this post contain something that I have embellished myself.  Last month Tom LaRock (blog | twitter) posted a script for helping to detect duplicate indexes.  He couldn’t claim credit for it as he said it was passed on to him a while ago and he’s unsure of the original source.  Similarly, I won’t claim credit for this other than to say I made a few changes I thought would be handy.  If you feel this script is lacking functionality that you think would be helpful, I invite you to change it and please share with the community when you’re done!

Why Duplicate Indexes Are Bad

Aside from serving no purpose, duplicate indexes can cause problems to occur which could require your attention, such as:

  • Wasted disk space – just like any other type of data, storing something more than once means it will take up space more than once as well.
  • Slower backups, restores, and integrity checks – more data means more time to read/write/check it all
  • Slower data modification operations – inserting, updating, and deleting rows will take longer when there’s more indexes that need updating
  • Increased chances of deadlock and blocking – More objects requiring locks for an operation means there’s a greater chance that another process has already acquired at least some of said locks
  • Increased transaction log growth and/or usage – The more writing/locking that’s happening due to an increased number of objects means that more will be written to the log

And I’m sure there’s plenty more potential issues than those I just listed. The simplest way to avoid this is to maintain only as many indexes as necessary. This script can help you find if any duplicates exist.

What’s New In This Version

  • I removed the columns that returned individual component columns of the indexes.  Since there was already a column that showed them all concatenated together, I couldn’t think of any reason for listing them individually other than it might help when programmatically dropping indexes, which shouldn’t be done anyway.  Indexes should only be dropped after careful review.
  • Added a column for displaying the size of the index.  Duplicate indexes are bad no matter what their size is, however this information may be helpful.
  • Added columns showing whether the index is filtered or has included columns.

Disclaimer

I have tested this script on SQL Server 2005 and later, and it will not run on earlier versions.  You should not trust any script you find on the internet (including this one!)  Make sure you completely understand what a script is doing before running it.  Always test on a testing or development server before using it in any production environment.

The Script

What you’ve really been waiting for.  You can see the entire script below, or just download the .zip of it here.  Enjoy!

-- This script will generate 3 reports that give an overall or high level
-- view of the indexes in a particular database. The sections are as follows:
-- 1.  Lists ALL indexes and constraints along with the key details of each
-- 2.  Lists any tables with potential Redundant indexes
-- 3.  Lists any tables with potential Reverse indexes

--  Create a table variable to hold the core index info
CREATE TABLE #AllIndexes (
   TableID INT NOT NULL,
   SchemaName SYSNAME NOT NULL,
   TableName SYSNAME NOT NULL,
   IndexID INT NULL,
   IndexName NVARCHAR(128) NULL,
   IndexType VARCHAR(12) NOT NULL,
   ConstraintType VARCHAR(11) NOT NULL,
   ObjectType VARCHAR(10) NOT NULL,
   AllColName NVARCHAR(2078) NULL,
   ColName1 NVARCHAR(128) NULL,
   ColName2 NVARCHAR(128) NULL,
   IndexSizeKB BIGINT NULL,
   HasFilter BIT NOT NULL,
   HasIncludedColumn BIT NOT NULL
);

DECLARE @ProductVersion NVARCHAR(128);
DECLARE @MajorVersion TINYINT;
DECLARE @loadIndexSQL NVARCHAR(4000);

SET @ProductVersion = CONVERT(NVARCHAR(128), SERVERPROPERTY('ProductVersion'));
SET @MajorVersion = CONVERT(TINYINT, LEFT(@ProductVersion, CHARINDEX('.', @ProductVersion) - 1));

SET @loadIndexSQL = N'
	INSERT INTO #AllIndexes (TableID, SchemaName, TableName, IndexID, IndexName, IndexType, ConstraintType,
		ObjectType, AllColName, ColName1, ColName2, IndexSizeKB, HasFilter, HasIncludedColumn)
	SELECT o.object_id, -- TableID
		u.[name], -- SchemaName
		o.[name], -- TableName
		i.index_id, -- IndexID
		i.[name], -- IndexName
		CASE i.[type]
			WHEN 0 THEN ''HEAP''
			WHEN 1 THEN ''CL''
			WHEN 2 THEN ''NC''
			WHEN 3 THEN ''XML''
			ELSE ''UNKNOWN''
		END, -- IndexType
		CASE
			WHEN (i.is_primary_key) = 1 THEN ''PK''
			WHEN (i.is_unique) = 1 THEN ''UNQ''
			ELSE ''''
		END, -- ConstraintType
		CASE
			WHEN (i.is_unique_constraint) = 1 OR i.is_primary_key = 1 THEN ''CONSTRAINT''
			WHEN i.type = 0 THEN ''HEAP''
			WHEN i.type = 3 THEN ''XML INDEX''
			ELSE ''INDEX''
		END, -- ObjectType
		(SELECT COALESCE(c1.[name], '''')
			FROM sys.columns AS c1
			INNER JOIN sys.index_columns AS ic1 ON c1.object_id = ic1.object_id
				AND c1.column_id = ic1.column_id
				AND ic1.key_ordinal = 1
			WHERE ic1.object_id = i.object_id
				AND ic1.index_id = i.index_id) +
			CASE
				WHEN INDEX_COL(''['' + u.[name] + ''].['' + o.[name] + '']'', i.index_id, 2) IS NULL THEN ''''
				ELSE '', '' + INDEX_COL(''['' + u.[name] + ''].['' + o.[name] + '']'', i.index_id, 2)
			END +
			CASE
				WHEN INDEX_COL(''['' + u.[name] + ''].['' + o.[name] + '']'', i.index_id, 3) IS NULL THEN ''''
				ELSE '', '' + INDEX_COL(''['' + u.[name] + ''].['' + o.[name] + '']'', i.index_id, 3)
			END +
			CASE
				WHEN INDEX_COL(''['' + u.[name] + ''].['' + o.[name] + '']'', i.index_id, 4) IS NULL THEN ''''
				ELSE '', '' + INDEX_COL(''['' + u.[name] + ''].['' + o.[name] + '']'', i.index_id, 4)
			END +
			CASE
				WHEN INDEX_COL(''['' + u.[name] + ''].['' + o.[name] + '']'', i.index_id, 5) IS NULL THEN ''''
				ELSE '', '' + INDEX_COL(''['' + u.[name] + ''].['' + o.[name] + '']'', i.index_id, 5)
			END +
			CASE
				WHEN INDEX_COL(''['' + u.[name] + ''].['' + o.[name] + '']'', i.index_id, 6) IS NULL THEN ''''
				ELSE '', '' + INDEX_COL(''['' + u.[name] + ''].['' + o.[name] + '']'', i.index_id, 6)
			END +
			CASE
				WHEN INDEX_COL(''['' + u.[name] + ''].['' + o.[name] + '']'', i.index_id, 7) IS NULL THEN ''''
				ELSE '', '' + INDEX_COL(''['' + u.[name] + ''].['' + o.[name] + '']'', i.index_id, 7)
			END +
			CASE
				WHEN INDEX_COL(''['' + u.[name] + ''].['' + o.[name] + '']'', i.index_id, 8) IS NULL THEN ''''
				ELSE '', '' + INDEX_COL(''['' + u.[name] + ''].['' + o.[name] + '']'', i.index_id, 8)
			END, -- AllColName
		(SELECT COALESCE(c1.[name], '''')
			FROM sys.columns AS c1
			INNER JOIN sys.index_columns AS ic1 ON c1.[object_id] = ic1.[object_id]
				AND c1.[column_id] = ic1.[column_id]
				AND ic1.[key_ordinal] = 1
			WHERE ic1.[object_id] = i.[object_id]
				AND ic1.[index_id] = i.[index_id]), -- ColName1
			CASE
				WHEN INDEX_COL(''['' + u.name + ''].[''+ o.name + '']'', i.index_id, 2) IS NULL THEN ''''
				ELSE INDEX_COL(''['' + u.[name] + ''].['' + o.[name] + '']'', i.[index_id],2)
			END, -- ColName2
			ps.used_page_count * 8, -- IndexSizeKB' + CHAR(13);

			IF @MajorVersion >= 10
				SET @loadIndexSQL = @loadIndexSQL + 'i.has_filter';
			ELSE
				SET @loadIndexSQL = @loadIndexSQL + '0';

			SET @loadIndexSQL = @loadIndexSQL + ', -- HasFilter' + CHAR(13);

			IF @MajorVersion >= 9
				SET @loadIndexSQL = @loadIndexSQL + 'CASE WHEN (SELECT COUNT(*) FROM sys.index_columns ic WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1) >= 1 THEN 1 ELSE 0 END';
			ELSE
				SET @loadIndexSQL = @loadIndexSQL + '0';

			SET @loadIndexSQL = @loadIndexSQL + ' -- HasIncludedColumn
	FROM sys.objects o WITH (NOLOCK)
		INNER JOIN sys.schemas u WITH (NOLOCK) ON o.schema_id = u.schema_id
		LEFT OUTER JOIN sys.indexes i WITH (NOLOCK) ON o.object_id = i.object_id
		LEFT OUTER JOIN sys.dm_db_partition_stats ps WITH (NOLOCK) ON ps.[object_id] = i.[object_id] AND ps.[index_id] = i.[index_id]
	WHERE o.[type] = ''U''
		AND o.[name] NOT IN (''dtproperties'')
		AND i.[name] NOT LIKE ''_WA_Sys_%'';
';

EXEC sp_executesql @loadIndexSQL;

-----------

SELECT 'Listing All Indexes' AS [Comments];

SELECT TableID, SchemaName, TableName, IndexID, IndexName, IndexType, ConstraintType, ObjectType, AllColName, IndexSizeKB, HasFilter, HasIncludedColumn
   FROM #AllIndexes
   ORDER BY TableName;

-----------
SELECT 'Listing Possible Redundant Index keys' AS [Comments];

SELECT DISTINCT i.TableName, i.IndexName,i.IndexType, i.ConstraintType, i.AllColName, i.IndexSizeKB, i.HasFilter, i.HasIncludedColumn
   FROM #AllIndexes AS i
   JOIN #AllIndexes AS i2 ON i.TableID = i2.TableID
      AND i.ColName1 = i2.ColName1
      AND i.IndexName <> i2.IndexName
      AND i.IndexType <> 'XML'
   ORDER BY i.TableName, i.AllColName;

----------
SELECT 'Listing Possible Reverse Index keys' AS [Comments];

SELECT DISTINCT I.TableName, I.IndexName, I.IndexType, I.ConstraintType, I.AllColName, I.IndexSizeKB, I.HasFilter, I.HasIncludedColumn
   FROM #AllIndexes AS I
   JOIN #AllIndexes AS I2 ON I.TableID = I2.TableID
      AND I.ColName1 = I2.ColName2
      AND I.ColName2 = I2.ColName1
      AND I.IndexName <> I2.IndexName
      AND I.IndexType <> 'XML';

DROP TABLE #AllIndexes;
Jun 072010
 

T-SQL Tuesday ImageThis post is my contribution to T-SQL Tuesday #007, hosted this month by the SQLChicken himself, Jorge Segarra (Blog | Twitter).

Since I’m a bad DBA and have yet to get my hands on a copy of R2, I’ll make use of the rule that we can discuss any feature from 2008.  My favorite is filtered indexes, as they were the solution to an issue I had been trying to fix for 5 years.

The Background

In a former life, I spent several years working as a building inspector for one of Chicago’s western suburbs.  It quickly became evident that they were in need of a system for keeping track of permits, inspections, and other day-to-day activities, as their gigantic spreadsheet clearly wasn’t cutting it.  Wanting to learn about databases and see if I could solve said problem, I picked up a book on Access 2000 and my solution was born.  It’s grown up a lot in the past 7 years, and is now used by 2 other municipalities as well.

The Problem

In my system, building permits can have one or two unique identifiers depending on their stage in the permit lifecycle.  That probably doesn’t make sense, so I’ll elaborate.  When a resident applies for a building permit, a record is created in the permit table with a primary key known as the PermitId.  This permit has a status of pending, as it has only been applied for and not issued.  Many permits never make it to the issued state, as they may be denied or their application withdrawn.  Permits that make it through the approval process and are issued receive a Permit Number, another unique identifier.  Enforcing uniqueness on the PermitNumber column was desired, but impossible as all pending permits had a NULL value for PermitNumber.  The best I could do for this (given my knowledge at the time) was to create a non-unique index on PermitNumber and hope that duplicate values didn’t end up occurring.  While that may be good enough for some, it always irked me.

Some may ask why PermitId and PermitNumber can’t just be combined.  The answer is that it’s a business requirement.  All three of the cities I do business with issue PermitNumbers in sequential order based on the permit issue date, so there’s no way the PermitNumber can be determined ahead of time.  Also the PermitId is an internal value and is only known to the database for use in joins.  PermitNumber, on the other hand, is known to the public and is commonly searched on, so indexing it is desirable.

The Solution

The Filtered Index feature allowed me to create a unique index on PermitNumber where the value is not null.  I was able to enforce uniqueness with permit numbers, and all was right in the world once again.  The syntax to do this was extremely simple:

CREATE UNIQUE INDEX UQ_Permits_PermitNumber
ON Permits(PermitNumber)
WHERE PermitNumber IS NOT NULL; -- The WHERE statement makes it a filtered index

And there you have it, a simple solution using elegant syntax.  ‘Tis a shame that this feature didn’t appear until 2008, but I’m sure glad it’s there now!