With my recent move to into a software development team I find myself paying more attention to software engineering techniques and tactics, one of them being source control. In the past I’ve had a very manual…archaic…system for maintaining indexes.
- Everyone should get SQL Source Control. It’s such a low price that I can’t imagine any employer saying no to a tool that helps you source control your databases. I really think you owe it to yourself to at least try the demo. With that said, if you don’t have SQL Source Control then I’m sure you can still accomplish the end goal, but you’ll need to experiment yourself.
- I am using Git because it’s seriously easy to use, it installs seriously fast and it’s seriously easy to use (did I already mention that it’s seriously easy to use?). I installed Git on an Ubuntu Server using Azure.
- I am using Atlassian SourceTree and Stash, mostly because I use JIRA, but also because I like having a graphical interface. It also has a terminal you can pop open when needed, but in my case I’ve found it much easier to teach other people about Git when I can show them using a GUI.
One thing that I commonly do when performing index analysis is to identify indexes that are not being helpful. How I do that is not in the scope of this post, but I do it mostly with thunder. After a Critical Care session with Jes Schultz Borland
, I was convinced that disabling indexes
was the way to go. Taking her advice, I started using a workflow that went a little something like this:
- Track possible uses in the plan cache
- Script the index creation and save in folders identified by date
- Disable the index
- Chill or be Chilled (being Chilled is like being Iced)
Seems pretty standard right? Item 3 was a painful area for me. Scripting the index and filing it away was a manual process, tedious, boring, excruciatingly monotonous (you get the idea…), and I also had to make sure it was backed up and available.
After setting up Git and Stash on an Azure VM for my team, I installed SQL Source Control [SSC] and linked our first database. This was cool, I got to see all the important stuff go into source control and it was win. So the time came for me to start testing how indexes worked with SSC and Git.
Creating an index is perfect, SSC detects the change, and adds in the tsql to the schema.tablename.sql file. Dropping the index was just the same, the tsql was removed, and the commit messages saved. Next I figured that disabling an index would be just as smooth, so I disabled my test index, and clicked the commit changes tab in SSC. …damn. Ok, so nothing happened but silence, so I thought to myself, this won’t do at all. I was on the hook with my homey Josh and I mentioned the slight frustration and he said “why don’t you rename the index?”. Simply genius…that is, if renaming the index will allow usage stats to remain intact (this is why disabling is preferred over dropping).
Now like any good software engineer would, I decided to perform an experiment to see how the usage stats would react. I cracked open Red Gate SQL Data Generator
, inserted a milly, and then wrote a while loop that would run a select statement that used the index, and ran it some random amount of clicks. I then ran the following tsql excerpt
and recorded the usage stats and executed the following tsql:
EXEC sp_rename N'testingGit.dbo.testingGitTable1.IX_testingGitTable1col1', N'IX_Iwasrenamed', N'INDEX';
Reran this guy
and to my delight, usage stats remained the same.
Now at this point I needed a more organized method of running the process for renaming and disabling an index. I wrote the following stored procedure to handle this task, it takes in the database name, schema name, table name and index name, then handles it for you. All indexes disabled in this fashion will have the postfix _wasDisabled_ + MMDDYYYY. Example: IX_RenameMe gets disabled and becomes IX_RenameMe_wasDisabled_01012014.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
— Author: Tim M. Hidalgo
— Create date: 01-16-2014
— Description: Input variables Database Name, Schema Name, Table Name and Index Name to be renamed/disabled
IF EXISTS (SELECT OBJECT_ID(‘dba_DisableAndRename’,‘p’))
DROP PROCEDURE [dbo].[dba_DisableAndRename]
CREATE PROCEDURE dba_DisableAndRename
— Add the parameters for the stored procedure here
@databaseName NVARCHAR(MAX), @schemaName NVARCHAR(MAX), @tableName NVARCHAR(MAX), @indexName NVARCHAR(MAX)AS
DECLARE @newName NVARCHAR(MAX), @alter NVARCHAR(MAX)
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
SET @newName = @indexName + ‘_wasDisabled_’ + REPLACE(CONVERT(CHAR(10), GETDATE(), 103), ‘/’, ”)
–Print statements for debugging
EXEC (‘sp_rename N”’ + @databaseName + ‘.’ + @schemaName + ‘.’ + @tableName + ‘.’ + @indexName + ”’,N”’ + @newName + ”’,N”INDEX”’)
–PRINT(‘sp_rename N”’ + @databaseName + ‘.’ + @schemaName + ‘.’ + @tableName + ‘.’ + @indexName + ”’,N”’ + @newName + ”’,N”INDEX”’)
EXEC (‘ALTER INDEX ‘ + @newName + ‘ ON ‘ + @databaseName + ‘.’ + @schemaName + ‘.’ +@tableName + ‘ DISABLE’)
–PRINT(‘ALTER INDEX ‘ + @newName + ‘ ON ‘ + @databaseName + ‘.’ + @schemaName + ‘.’ [email protected] + ‘ DISABLE’)
After the rename SSC will detect the change, then you can save your changes, commit to Git and win. One problem with this code is that I have to create the proc in each database, I wasn’t able to get it to run from the master without the following error:
Either the parameter @objname is ambiguous or the claimed @objtype (INDEX)
If anyone has ideas on how to improve this procedure, please let me know.