Deleting unused indexes from SQL server to free up space and improve performance

In SQL Server, indexes can be a double-edged sword. Sure, they can make queries run faster, but at the same time, their maintenance can have a negative impact. You can improve your server’s overall performance by only maintaining useful indexes – but finding the ones you don’t need can be quite a manual process.

If you see indexes where there are no seeks, scans or lookups, but there are updates this means that SQL Server has not used the index to satisfy a query but still needs to maintain the index.

Remember that the data from these DMVs is reset when SQL Server is restarted, so make sure you have collected data for a long enough period of time to determine which indexes may be good candidates to be dropped.

Run this in SQL Server:

       I.[NAME] AS [INDEX NAME], 
       AND S.database_id = DB_ID()

Here we can see seeks, scans, lookups and updates.

The seeks refer to how many times an index seek occurred for that index. A seek is the fastest way to access the data, so this is good.
The scans refers to how many times an index scan occurred for that index. A scan is when multiple rows of data had to be searched to find the data. Scans are something you want to try to avoid.
The lookups refer to how many times the query required data to be pulled from the clustered index or the heap (does not have a clustered index). Lookups are also something you want to try to avoid.
The updates refers to how many times the index was updated due to data changes which should correspond to the first query above.

To find the ones that can be safely removed, run this:

       I.[NAME] AS [INDEX NAME], 
AND OBJECT_NAME(S.[OBJECT_ID]) = '[your table name]'
       AND S.database_id = DB_ID() AND (USER_SEEKS = 0 AND USER_SCANS =0 AND USER_LOOKUPS=0)

You can then delete the unused indexes.

How to determine Index fragmentation and then defragment a database

Index fragmentation is always an issue with big data. I have written some articles before:
How to rebuild all indexes
How to script all indexes
How to examine index fragmentation in SQL Server 2012

The easy way:

, SI.NAME AS IndexName
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) DPS --N'LIMITED') DPS
INNER JOIN sysindexes SI

As a rule, all indexes with a fragmentation state over 30% should be defragmented:

— Index neu erstellen
EXEC sp_MSforeachtable @command1=”print ‘Erstelle Index für ?’ ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = 90)”

— Index reorganisieren
EXEC sp_MSforeachtable @command1=”print ‘Reorgansiere Index für ?’ ALTER INDEX ALL ON ? REORGANIZE”

How to reduce index size and free up space in a SQL database.

First, run this script to figure out the worst offenders:

create table #t
  name nvarchar(128),
  rows varchar(50),
  reserved varchar(50),
  data varchar(50),
  index_size varchar(50),
  unused varchar(50)

declare @id nvarchar(128)
declare c cursor for
select name from sysobjects where xtype='U'

open c
fetch c into @id

while @@fetch_status = 0 begin

  insert into #t
  exec sp_spaceused @id

  fetch c into @id

close c
deallocate c

select * from #t
order by convert(int, substring(data, 1, len(data)-3)) desc

drop table #t

Continue reading “How to reduce index size and free up space in a SQL database.”

How to examine index fragmentation in SQL Server 2012

In this exercise, execute the following T-SQL code, examine the fragmentation reported, and defragment the indexes that show 100 percent fragmentation.

SELECT SCHEMA_NAME(so.schema_id) AS [SchemaName],
OBJECT_NAME(idx.OBJECT_ID) AS [TableName], AS [IndexName],
idxstats.index_type_desc AS [Index_Type_Desc],
CAST(idxstats.avg_fragmentation_in_percent AS decimal(5,2)) AS [Frag_Pct],
FROM sys.dm_db_index_physical_stats
(DB_ID(), NULL, NULL, NULL, ‘DETAILED’) idxstats
sys.indexes idx
ON idx.OBJECT_ID = idxstats.OBJECT_ID
AND idx.index_id = idxstats.index_id

INNER JOIN sys.objects so
ON so.object_id = idx.object_id

WHERE idxstats.avg_fragmentation_in_percent > 20

ORDER BY idxstats.avg_fragmentation_in_percent DESC

Index fragmentation
Index fragmentation

How to create a filtered index

This is the quick-and-dirty method of creating a filtered index:

USE [AdventureWorks2012]
CREATE NONCLUSTERED INDEX [NC_Person_Address_City_spi_pc] ON [Person].[Address] ([City] ASC) INCLUDE ([StateProvinceID], [PostalCode])
WHERE [City] = ‘Seattle’ ON [PRIMARY] GO

You can find more about filtered indexes on Introduction to SQL Server Filtered Indexes by Seth Delconte

Find missing indexes for SQL Server 2012

The following example generates a listing of missing indexes that could be beneficial for T-SQL batches that have been submitted to the query optimizer since the last time the SQL Server service was restarted:


SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS [Index_Useful] ,
igs.last_user_seek ,
id.statement AS [Statement] ,
id.equality_columns ,
id.inequality_columns ,
id.included_columns ,
igs.unique_compiles ,
igs.user_seeks ,
igs.avg_total_user_cost ,
FROM sys.dm_db_missing_index_group_stats AS igs
INNER JOIN sys.dm_db_missing_index_groups AS ig ON igs.group_handle = ig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS id ON ig.index_handle = id.index_handle
ORDER BY [Index_Useful] DESC;

Query Execution Results
Query Execution Results
Missing Index Details - Full listing showing the desirability status, columns to be used and user seeks
Missing Index Details – Full listing showing the desirability status, columns to be used and user seeks

As you can see, the most required index missing in the database above is one on the status table.
We can create the missing index using the following command:

CREATE INDEX IX_ERPStatus ON tblERPStatus (SystemID, ERPDocumentNumber)

How to search a website with Microsoft Indexing services

Providing search capabilities requires two steps:

  • First, you must create an index of the site’s contents. An index of a website is synonymous to the index in a book. If you want to read about a particular topic in the book you can quickly find the page(s) through the index, as opposed to having to read through the book’s entire contents.
  • Once an index has been created you need to be able to search through the index. Essentially, a user will enter a search string and you’ll need to find the matching, relevant results in the index, displaying them to the user.

Unfortunately, building a search engine for your site is not as straightforward and simple as we’d like. Writing your own indexer, indexing the content, and building code to search the index is definitely possible, but requires a good deal of work. Fortunately there exist a number of indexers out there that you can leverage for your site. Some indexers include commercial products like EasySearchASP.NET and Building31.Search, which are products designed to specifically search an ASP.NET website. Additionally, Microsoft provides its own indexer, Microsoft Index Services.

This article examines using Microsoft Index Services for your site’s search functionality.
With Index Services you can specify a specific group of documents or HTML pages to be indexed, and then create an ASP.NET page that can query this index.

We’ll build a simple, fast, and extensible search tool using .NET and Microsoft Indexing Services along with the Adobe IFilter plug-in, which allows MS Indexing Services to index PDF documents and display them in your search results.

Configuring Microsoft Indexing Services

The first step in creating an index for your search application is to configure Indexing Services on the IIS server that your Web application will be running. To do this you need access to the Web server itself. Open the Microsoft management console by clicking Start, then Run; type mmc and click Ok. Next, to open the Indexing Services snap-in, you must:

  • Click file,
  • Click Add/Remove Snap-In,
  • Click Add,
  • Select the Indexing Service Snap-In,
  • Click Add,
  • Click Finish,
  • Close the dialog

After following these steps you should see something akin to the screenshot below.

To create a new catalog – which is the vernacular Microsoft uses for an index – right-click on the Indexing Service node, click New and then Catalog. You then need to choose a location to store the catalog file. Once you’ve done that, expand the catalog that you just created and click on the directories icon. Right-click on the directories folder, click new directory, and add the directory or directories that contain the content that you want to search. These directories can reside anywhere that the host computer can access, virtual directories and even UNC paths (Servershare) may be used. However, each directory that is indexed must either reside physically, or be included as a virtual directory, in the root of the website that you are indexing. If a directory is specified that is not in the web root via a physical folder or virtual directory, the results will be displayed in your search, but they will return broken links.

Indexing Services will effectively index HTML, Word, and, once properly configured, PDF documents. To ensure that your required directories will be indexed you should verify that the index flag is properly set on the files and folders. You can verify this setting by right clicking on any folder or file and selecting properties. Click the “Advanced button” and make sure that the “For fast searching, allow indexing services to index this folder” checkbox is checked, as shown in the screenshot to the right.

Next, you want to set the properties of this catalog so that the HTML paths can be used, and so that Indexing Services will generate abstracts for the documents as they are indexed. To do this right-click on the catalog you just created and select Properties. On the tracking tab, you’ll need to make sure that the “WWW Server:” field is set to the website that your application will be running from. This ensures that the html paths work as they should when you get to building the front-end for the search. If you want to display a short bit of each article along with your search results, then go to the Generation tab, uncheck “inherit above settings from service,” then check generate abstracts and set the number of characters you wish to have displayed in each abstract.

If you want your search to include PDF documents, then you must install the Adobe IFilter extension. You can download this free of charge from Adobe:
This plug-in is a standard windows installer and requires no additional configuration. After the plug-in has been installed, PDF documents will automatically be included in the search results as they are indexed without any user intervention or configuration required.

When you navigate to the Directories folder in the catalog that you’ve created, you may notice that there one or more directories appear in addition to the ones you added in the previous step. These are website shares added automatically by Indexing Services, they and need to be excluded from indexing if you don’t want your search to include them. To exclude these directories, you must find them in the file system via windows explorer. Next, right click the folder and choose Properties. From the dialog that appears click advanced and uncheck the box that says “For fast searching, allow index services to index this folder.” (See the screenshot above) This will exclude the folder from your search results. The configuration of indexing services is now complete.

As you can see, an index may include as little as one folder of documents or as much as an entire website or group of websites. It’s up to you to determine the breadth of the index. However, since Index Services does not crawl links like a spider, it will only catalog file system objects. Thus, the results from this search will include static files such as HTML pages, Word documents, and PDF documents, but not any dynamically generated pages. Changes made to these static documents will be picked up by Indexing Services and will very quickly be reflected in your search results.

Searching the Index

Once the index has been created, the next step is to build a search page that allows the website visitor to search through the index. To do this, you need, at minimum, a TextBox Web control for the end user to enter search terms, a Button Web control to initiate the search, and a Repeater control to display the results. The following markup shows the bare minimum markup for a simple search page:

Enter your search query:
<asp:TextBox id="txtSearch" runat="server"/>



<asp:Repeater id="searchResults" runat="server">

        <%# DataBinder.Eval(Container.DataItem, "File") %> <br>
        <%# DataBinder.Eval(Container.DataItem, "FileAbstract") %> <br>



This results page will display a list of results with a line for the document title followed by the abstract, which is generated by indexing services. Let’s take a look at the code-behind class.

In the code-behind page, an OleDbConnection is attached to the Indexing Services catalog that we set up earlier. Once connected, the catalog can be searched using a variety of query languages, including SQL syntax. You can read about each of the language options here: Query Languages for Indexing Services. For this example, I’m going to use the IS Query Language to perform a freetext search which allows for natural language search capabilities, but you can modify your search to use Boolean, phrase, or any of the query types that indexing services support.

To set up the connection to the indexing services catalog you need to set up a OleDB connection as follows: 

// create a connection object and command object, to connect the Index Server

System.Data.OleDb.OleDbConnection odbSearch = new System.Data.OleDb.OleDbConnection( "Provider="MSIDXS";Data Source="docSearch";");

System.Data.OleDb.OleDbCommand cmdSearch = new System.Data.OleDb.OleDbCommand();

// assign connection to command object cmdSearch
cmdSearch.Connection = odbSearch;
// Execute the query using freetext searching and sort by relevance ranking
//Query to search a free text string in the contents of the indexed documents in the catalog
string searchText = txtSearch.Text.Replace(“‘”,”””);

cmdSearch.CommandText = “select doctitle, filename, vpath, rank, characterization from scope() where FREETEXT(Contents, ‘”+ searchText +”‘) order by rank desc “;

The fields returned from querying the index include:

  • Doctitle: The title of document, which is the text between the <title> tags in an HTML document or the text in the title field of a word or PDF document.
  • Filename: The physical name of the file that the result was returned from.
  • Vpath: The virtual path of the file where the result was returned from. This is the field you use to specify an HTML link to the file.
  • Rank: The relevance of the returned result.
  • Characterization: The abstract for the document, usually the first 320 characters.

Tying it All Together

While there are a number of ways in which you can display the results from your search, a Repeater is likely the most efficient and gives you the greatest control on how your results are formatted. The sample application that is attached demonstrates how to bind the results of your search to a Repeater control. It also adds paging functionality to the results that will make the results easier to use, as shown in the screenshot below. The results can easily be modified to show paths to documents or display the rankings of each result.


This search tool is small, fast, and simple enough to deploy for searching individual folders of specific content in your intranet or Internet site. However, it can easily be used to search entire sites composed of thousands of documents. Due to the power of Microsoft Indexing Services, all that you will need to do is alter the scope of the Indexing Services catalog to include any folders you want indexed. Adding new documents to these folders or modifying the existing documents will automatically be picked up by Indexing Services. 

For more information about Indexing Services, be sure to read the following resources: