DBAs gained the ability to disable indexes in SQL Server 2005. This is a handy little option when you are tuning indexes or consolidating indexes. When you disable an index, you tell SQL Server to basically pretend the index doesn’t exist. Data changes made to the underlying tables are not reflected in the disabled index. As a result, you can sometimes improve performance by disabling unused indexes.
Disabling an index is not the same as dropping an index. When you drop an index, the index and its definition are deleted from the database. When you disable an index, the index definition is retained in the system catalog and you can still see the index in Management Studio. This may lead you to believe the index data remains as well and that it is just no longer updated with data changes. In actuality, when you disable an index, although the index definition remains in the database, the actual index data is deleted from the database. Indeed, the BOL entry for the ALTER INDEX statement, which is what you use to disable an index, states: “Any index can be disabled. The index definition of a disabled index remains in the system catalog with no underlying index data.” This is actually handy if you are unsure if any code is referencing the index. Just dropping the index would cause such references to fail. Disabling the index will also cause them to fail, but because the index definition is retained, it’s an easy matter to recreate the index.
Let’s verify this by running a test. First, we’ll create a test database and make a table with a clustered index.
USE [master] GO CREATE DATABASE [Demo] GO ALTER DATABASE [Demo] SET RECOVERY SIMPLE GO USE Demo CREATE TABLE [dbo].[BigTable]( [PK] [int] IDENTITY(1,1) NOT NULL, [ColA] [varchar](100) NULL, [ColB] [varchar](100) NULL, [ColC] [varchar](100) NULL, [ColD] [varchar](100) NULL ) ON [PRIMARY] GO CREATE CLUSTERED INDEX [ClusteredIndex] ON [dbo].[BigTable] ( [PK] ASC ) ON [PRIMARY] GO
Now, let’s add 10,000 rows of data:
INSERT INTO BigTable (ColA, ColB, ColC, ColD) VALUES (newid(), newid(), newid(), newid()) GO 10000
Let’s look at how much space this table takes up.
Now, we’ll add a large index – an index on ColA and including ColB, ColC, and ColD. (Note this is not a good indexing strategy – adding every column in a table to an index. I’m doing it here simply to make a large index.)
CREATE NONCLUSTERED INDEX [NonClusteredIndex] ON [dbo].[BigTable] ( [ColA] ASC ) INCLUDE ( [ColB], [ColC], [ColD]) GO
Once again, let’s check the size of the table and indexes:
We can see that, compared to the sizes before we added the non-clustered index, our index_size increased by 1704 KB.
Now, let’s disable the index and see what happens to our sizes:
ALTER INDEX [NonClusteredIndex] ON [dbo].[BigTable] DISABLE GO
As you can see, the index_size dropped back down to what it was before we added the non-clustered index. When we disabled the index, SQL deleted the underlying index data. (If you use the sp_spaceused command without the table name, you will see results for the entire database and you will can verify that the data has indeed been removed from the database.)
When you think about what is going on, you can understand why there is no ENABLE INDEX command. To re-enable a disabled index, you issue an index rebuild command. There is no index data present to re-enable – SQL must recreate it.
The exception to this behavior is when you disable a clustered index. Because the clustered index actually is the table data, SQL cannot delete it. Instead, when you disable the clustered index on the table, the table becomes unavailable.
ALTER INDEX [ClusteredIndex] ON [dbo].[BigTable] DISABLE GO
You can see that, even though the clustered index has been disabled, the data is still present:
However, attempting to access the table will generate an error:
So why is this information useful?
I ran into a problem the other day with one of my production databases. This database had been a constant size for months. Suddenly, one day I got a warning that the drive the database was on was running out of disk space. I did some investigating and narrowed down the cause of the file growth to a particular table in the database. The table was a log table and records were being written to it and deleted every day.
My first thought was perhaps we had an increase in activity and more records were being inserted. Luckily, the records included a datestamp, so I was able to look at the number of rows being inserted per day. That number was relatively constant over the past couple months, so that was not the cause of the growth.
Two of the columns in the table were XML data types, so my next thought was perhaps the XML data that was being logged had grown. I checked with our developers and they did not make any changes to the XML data being logged. I also used the DATALENGTH function and verified the average data size of the two fields had remained the same over the past several months.
So what was going on? Why was my table growing? I needed to get an answer quickly. This table had 26 million rows in it and it was suddenly growing by about 300 MB per day where it had been a constant size previously.
I started running sp_spaceused on a regular basis and writing the output to a table. From here, I was able to see the index space used was very close to the data space used and was growing at about the same rate as the data. Hmmm.
That led me to take a closer look at the indexes. I found a newly added index! This index was on two columns, but had all the other columns of the table included in the index as INCLUDED columns. So this index basically doubled the size of the table. I had found my culprit! It just so happened that while I was digging into this, a developer was online rebuilding this index, so I was able to find out the person responsible for the index. After a brief visit, I found out he was attempting to resolve some performance issues. I gave some advice (including to test on a development system, not production), and I disabled the index. (I didn’t just drop the index because we were worried other processes might be referencing it and we wanted to make sure nothing broke before we got rid of it altogether.) Once the index was disabled, the index data was removed from the database, and my file growth stopped.
My next step was to investigate how a developer got DDL access to a production database.