Find index usage against a particular table, & find all unused indexes in the database

The environment: SQL Server 2005 or SQL Server 2008

Maybe you have a large database, and you're looking for ways to save space.  Maybe you're seeing a large, heavily-used table with many indexes, and you're wondering if you could improve write performance by getting rid of some of the indexes.

Before you start dropping indexes left and right, you should start by checking to see if, and how often, you'd obviously like to check which indexes are actually being used.  Starting in SQL Server 2005, there are DMV's (Dynamic Management Views) that make this possible - namely, sys.dm_db_index_usage_stats.  This DMV holds a row for each index which has been used since the last time the SQL Service starts up.  This information does not persist across service restarts, so I generally like to wait at least a month before I start looking at this DMV to get an idea of which indexes are being used, and how.

For more information on the SQL DMV's, check out Books Online:

SQL Server 2005 Dynamic Management Views and Functions

SQL Server 2008 Dynamic Management Views and Functions

Personal note: I've found that a month's worth of data is reasonably representative of your actual index usage - for example, the table in question may have a large index which is only used once a month - but it happens to be used by a query that is critical to your monthly billing system.

1. Check all of the indexes on a given table:

    select t.[name],
        i.[name],
        s.*
    from sys.tables t
        left outer join sys.indexes i on t.object_id = i.object_id
        left outer join sys.dm_db_index_usage_stats s on i.index_id = s.index_id
                                    and i.object_id = s.object_id
                                    and s.database_id = db_id()
    where i.is_hypothetical = 0    -- discard stats from consideration
        and t.[name] = 'TableName'                                   
    order by t.[name], i.[name]   

       

2. Check all tables in the database for small numbers of total seeks, scans and updates against any of their indexes:

    select t.[name],
        i.index_id,
        i.[name],
        s.*
    from sys.tables t
        left outer join sys.indexes i on t.object_id = i.object_id
        left outer join sys.dm_db_index_usage_stats s on i.index_id = s.index_id
                                    and i.object_id = s.object_id
                                    and s.database_id = db_id()
    where i.is_hypothetical = 0        --    not stats
        and
        (      --    has no index activity
            s.index_id is null              

            --    or has very little index activity       
            or ( s.user_seeks + s.user_scans + s.user_lookups ) < 100  
        )
        and i.[name] is not null       --    has indexes               
        and i.index_id <> 1            --    not a primary key           
    order by t.[name], i.[name]   

Migrating Legacy DTS packages

The environment: SQL Server 2000, SQL Server 2005 and SQL Server 2008

Consider an environment in which you're asked to migrate a large number of DTS packages from one of your servers onto another server.  You could manually move each package, but if the following conditions are met, a simple insert statement into msdb..sysdtspackages can make your life a lot easier:

• you don't need to convert your DTS packages to SSIS packages
• all of your packages are stored on the server (rather than as a file)
  

use [msdb]

go

insert into sysdtspackages

    select [name],
        id,
        versionid,
        description,
        categoryid,
        createdate,
        owner,
        packagedata,
        owner_sid,
        packagetype
    from [linkedserver].msdb.dbo.sysdtspackages

 

Find the last full backup date for all databases on a server

Consider a situation in which you're taking over a number of SQL Servers, and you find yourself wondering about the backup situation - maybe you need to pull a full backup history for each database on each server, or maybe you just need to verify that your databases are being backed up.  This is a quick script that pulls the last full backup date for each database on the server - I'm including a SQL Server 2000-compatible version of the script:

SQL Server 2000

select d.[name],
    x.backup_date
from sysdatabases d
    left outer join
    (
        select database_name,
            convert(varchar(50),max(backup_finish_date),110) as backup_date
        from msdb..backupset
        where [type] = 'D'
        group by database_name
    ) x on x.database_name = d.[name]
order by d.[name]

SQL Server 2008 (should also work for SQL Server 2005)

;with x as
(
    select database_name,
        backup_size                                 as backup_size_bytes,
        backup_size / 1024                          as backup_size_kbytes,
        backup_size / (1024*1024)                   as backup_size_mbytes,
        compressed_backup_size,
        convert(varchar(50),backup_finish_date,110) as backup_date,
        [type],
        row_number() over ( partition by database_name order by backup_finish_date desc ) as rnk
    from msdb..backupset
    where [type] = 'D'       
)

select d.[name],
    d.recovery_model_desc,
    x.*
from sys.databases d
    left outer join x on x.database_name = d.[name]
                        and x.rnk = 1
order by d.[name]

Note that this is only the first step in your due diligence - you still have to determine where your databases are being backed up to, your retention window (locally and on any off-site tapes or servers), and (of course) run periodic fire drills to make sure your backup solution is adequate to the task - more on that in a later post.

Find disk space usage for all tables and their indexes

The environment: SQL Server 2008 (this should work on SQL Server 2005 as well)

Consider a scenario in which you inherit a large database, or in which you revisit a database which has been sitting quietly in production for a while, just accumulating data.  This script compiles a full list of all of the tables in the database, along with their total reserved size in KB, their total index size in KB, and their total data size in KB.

create table #tmpsizes
(
    ID            int    identity(1,1) primary key,
    [name]        nvarchar(128),
    [rows]        char(11),
    reserved      varchar(18),
    data          varchar(18),
    index_size    varchar(18),
    unused        varchar(18),
)

create table #tmpsizes2
(   
    ID                int    identity(1,1) primary key,
    [name]            nvarchar(128),
    [rows]            int,
    reserved_kb       int,
    data_kb           int,
    index_size_kb     int,
    unused_kb         int
)

declare @objname nvarchar(128)
declare sizeCur cursor forward_only for
    select '[' + s.[name] + '].' + t.[name]
    from sys.tables t
        inner join sys.schemas s on t.schema_id = s.schema_id
open sizeCur
fetch next from sizeCur into @objname
while @@fetch_status = 0
begin
    insert into #tmpsizes
        exec sp_spaceused @objname

    fetch next from sizeCur into @objname
end
close sizeCur
deallocate sizeCur

insert into #tmpsizes2
    select [name],
        [rows],
        left(reserved, len(reserved)-3),
        left(data, len(data)-3),
        left(index_size, len(index_size)-3),
        left(unused, len(unused)-3)
    from #tmpsizes
 
-- show tables with the most rows at the top of the list      
select *
from #tmpsizes2
order by [rows] desc

-- show tables with highest ratios of index size to table size 

-- at the top of the list
select *,
    cast(index_size_kb as decimal(18,4))/cast(reserved_kb as decimal(18,4)) as index_percentage
from #tmpsizes2
where reserved_kb > 0
order by cast(index_size_kb as decimal(18,4))/cast(reserved_kb as decimal(18,4)) desc

drop table #tmpsizes
drop table #tmpsizes2