Display Stale Index Statistics

This query displays all the index statistics in the current database that are more than 'n' days old.

-- When were Statistics last updated on all indexes more than 7 days ago?  
  select o.name                                as 'Table Name'
       , i.name                                as 'Index Name'
       , stats_date(i.object_id, i.index_id)   as 'Statistics Date'
       , s.auto_created
       , s.no_recompute
       , s.user_created
    from sys.objects   o
    join sys.indexes   i
      on o.object_id = i.object_id
    join sys.stats     s
      on i.object_id = s.object_id
     and i.index_id    = s.stats_id
   where o.type = 'U'
     and datediff(Day, stats_date(i.object_id, i.index_id), getdate()) > 7
order by [Statistics Date] desc

Find all Occurrences of Text in all Stored Procedures in all Databases

Sometimes you'd like to find all occurrences of a piece of text in every stored procedure, trigger, etc., possibly to replace it with a new value. The script below will do just that; it returns the database name, the object name, and the object type. Note that it works for both SQL Server 2000 and post-SQL Server 2000 versions.

One thing to note is that the @TextToFind uses the LIKE operator with its ESCAPE argument set to the dollar sign symbol. I added the ESCAPE argument just to make it easier to search for text such containing the underscore character, such as 'My_HardToFind_Thing". Of course, if you're searching for text with a dollar sign symbol in it, you'll want to change the ESCAPE argument to something else.

(The script below can also be run in CMS, which will allow you to also return results across server groups.)

-- Simply set the text you want to look for here. 
declare @TextToFind nvarchar(4000)
set @TextToFind = 'THIS IS MY TEXT TO FIND'

declare @Sql nvarchar(4000)

set @Sql =
    'use [?]

     declare @DatabaseName sysname
     declare @ProcName     sysname
     declare @ProcType     nvarchar(60)

     if left(convert(nvarchar, serverproperty(''productversion'')), 1) <> ''8''
     begin
         declare cur cursor local fast_forward for
            select distinct ''?''          as ''Database Name''
                          , o.name         as ''Object Name''
                          , o.type_desc    as ''Object Type''
              from sys.sql_modules   m
              join sys.objects       o
                on m.object_id = o.object_id
             where m.definition like ''%' + @TextToFind + '%'' escape ''$''

        open cur
        fetch next from cur into @DatabaseName, @ProcName, @ProcType

        while @@fetch_status = 0
        begin
            print @DatabaseName + '' '' + @ProcName + '' '' + @ProcType
            fetch next from cur into @DatabaseName, @ProcName, @ProcType
        end

        close cur
        deallocate cur
    end
    else
    begin
         declare cur cursor local fast_forward for
            select distinct ''?''               as ''Database Name''
                          , object_name(c.id)   as ''Object Name''
                          , o.xtype             as ''Object Type''
              from syscomments   c
              join sysobjects    o
                on c.id = o.id
             where c.[text] like ''%' + @TextToFind + '%'' escape ''$''
               and objectproperty(c.id, ''isprocedure'') = 1

        open cur
        fetch next from cur into @DatabaseName, @ProcName, @ProcType

        while @@fetch_status = 0
        begin
            print @DatabaseName + '' '' + @ProcName + '' '' + @ProcType
            fetch next from cur into @DatabaseName, @ProcName, @ProcType
        end

        close cur
        deallocate cur
    end
    
    '

exec sp_msforeachdb @Sql

Quick Query to Display Database by Dataspace, Table, and Index

This query just breaks down the current database by dataspace, table, and index, giving you some useful information such as size in MB, pathname of the dataspace file, etc.

-- Shows size of each object in each dataspace in the current database. 
  select db_name()                              as DatabaseName
       , ds.name                                as DataSpaceName
       , ds.type_desc                           as DataSpaceTypeDesc
       , o.name                                 as TableName
       , i.name                                 as IndexName
       , i.type_desc                            as IndexDesc
       , (sum(ps.used_page_count) * 8) / 1024   as IndexSizeMB
       , df.physical_name                       as PathName
    from sys.objects                 o
    join sys.indexes                 i 
      on o.object_id = i.object_id 
    join sys.data_spaces             ds 
      on ds.data_space_id = i.data_space_id
    join sys.database_files          df
      on ds.data_space_id = df.data_space_id
    join sys.dm_db_partition_stats   ps
      on i.object_id = ps.object_id
group by ds.name
       , o.name
       , i.name
       , ds.type_desc
       , i.type_desc
       , df.physical_name
order by ds.name
       , o.name
       , i.name

Cleaning User Stuff from the master Database

This is an upgrade to a script I wrote a long time ago. It's handy when you find a system whose master database is somehow full of user objects. (I know that none of us ever make that mistake...)

The biggest improvement is that this script now only generates DROP statement, it doesn't execute them. You have to do that yourself, and you should be sure to back up your master database (you do that on a regular basis anyway, right?), and be darn sure to read all the comments, and of course examine the generated T-SQL code, before executing it. Use at your own risk!

-------------------------------------------------------------------------------
-- Drops all user objects that have been accidentally created in 'master'.     
-- This script erases everything in the master database that is not Microsoft- 
-- shipped.  HOWEVER, be careful: some user objects BELONG in the master       
-- database (database triggers, security objects, for example). Backing up the 
-- master database first would be a prudent step.  Use at your own risk.       
-- NOTE: This script only generates the T-SQL code required.  You have to copy 
-- it and do the actual execution yourself.  You may have to run this script   
-- several times due to dependencies; you may also get error messages about    
-- objects that cannot be found, also due to dependencies.                     
-------------------------------------------------------------------------------

USE master

DECLARE @Sql            NVARCHAR(MAX)       SET @Sql            = ''
DECLARE @ObjectName     sysname             SET @ObjectName     = ''
DECLARE @ConstraintName sysname             SET @ConstraintName = ''
DECLARE @TableName      sysname             SET @TableName      = ''
DECLARE @TypeDesc       NVARCHAR(4000)      SET @TypeDesc       = ''
DECLARE @Type           NVARCHAR(1024)      SET @Type           = ''
DECLARE @Score          INT                 SET @Score          = 0
DECLARE @ObjectID       INT                 SET @ObjectID       = 0

DECLARE @ENumber        INT                 
DECLARE @ESeverity      INT                 
DECLARE @EState         INT                 
DECLARE @EProcedure     NVARCHAR(126)       
DECLARE @ELine          INT                 
DECLARE @EMessageRecv   NVARCHAR(2048)      
DECLARE @EMessageSent   NVARCHAR(440)       

RAISERROR('-- Execute this code only *AFTER* you have examined it throughly!', 10, 1) WITH NOWAIT
RAISERROR('USE master', 10, 1) WITH NOWAIT


-------------------------------------------------------------------------------
-- Most objects follow this pattern.                                           
-------------------------------------------------------------------------------

DECLARE cur CURSOR DYNAMIC FOR
    SELECT name,
           type_desc
      FROM sys.objects
     WHERE is_ms_shipped = 0
     ORDER BY type_desc, name

OPEN cur
FETCH FIRST FROM cur INTO @ObjectName, @TypeDesc

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        IF @TypeDesc = 'VIEW'
        BEGIN
            SET @Sql = 'DROP VIEW ' + QUOTENAME(@ObjectName)
            RAISERROR('%s', 10, 1, @Sql) WITH NOWAIT    
        END
        ELSE IF @TypeDesc NOT LIKE '%_CONSTRAINT' ESCAPE '$'
        BEGIN
            SET @Type  = REVERSE(@TypeDesc)
            SET @Score = CHARINDEX('_', @Type)
            SET @Type  = SUBSTRING(@Type, 0, @Score)
            SET @Type  = REVERSE(@Type)
            SET @Sql   = 'DROP ' + @Type + ' ' + QUOTENAME(@ObjectName)
            RAISERROR('%s', 10, 1, @Sql) WITH NOWAIT    
        END
    END TRY
    BEGIN CATCH
        SET @ENumber      = ISNULL(ERROR_NUMBER(),          -1)
        SET @ESeverity    = ISNULL(ERROR_SEVERITY(),        -1)
        SET @EState       = ISNULL(ERROR_STATE(),            0)    IF @EState = 0 SET @EState = 42
        SET @EProcedure   = ISNULL(ERROR_PROCEDURE(), N'{N/A}')
        SET @ELine        = ISNULL(ERROR_LINE(),            -1)
        SET @EMessageRecv = ISNULL(ERROR_MESSAGE(),        N'')
        SET @EMessageSent = N''

        IF ERROR_PROCEDURE() IS NOT NULL   SET @EMessageSent = N'Error %d, Level %d, State %d, Procedure %s, Line %d, Message: '
        SET @EMessageSent = @EMessageSent + ERROR_MESSAGE()
        RAISERROR(@EMessageSent, 10 /* @ESeverity */ , @EState, @ENumber, @ESeverity, @EState, @EProcedure, @ELine) WITH LOG
    END CATCH

    FETCH NEXT FROM cur INTO @ObjectName, @TypeDesc
END

CLOSE cur
DEALLOCATE cur


-------------------------------------------------------------------------------
-- Constraints are the only exception to the pattern above.                    
-------------------------------------------------------------------------------

DECLARE cur CURSOR DYNAMIC FOR
    SELECT name,
           type_desc,
           OBJECT_NAME(parent_object_id)
      FROM sys.objects
     WHERE is_ms_shipped = 0
       AND type_desc LIKE '%_CONSTRAINT' ESCAPE '$'
  ORDER BY type_desc, name

OPEN cur    
FETCH FIRST FROM cur INTO @ConstraintName, @TypeDesc, @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        SET @Sql = 'ALTER TABLE ' + QUOTENAME(@TableName) + ' DROP CONSTRAINT ' + QUOTENAME(@ConstraintName)
        RAISERROR('%s', 10, 1, @Sql) WITH NOWAIT    
    END TRY
    BEGIN CATCH
        SET @ENumber      = ISNULL(ERROR_NUMBER(),          -1)
        SET @ESeverity    = ISNULL(ERROR_SEVERITY(),        -1)
        SET @EState       = ISNULL(ERROR_STATE(),            0)    IF @EState = 0 SET @EState = 42
        SET @EProcedure   = ISNULL(ERROR_PROCEDURE(), N'{N/A}')
        SET @ELine        = ISNULL(ERROR_LINE(),            -1)
        SET @EMessageRecv = ISNULL(ERROR_MESSAGE(),        N'')
        SET @EMessageSent = N''

        IF ERROR_PROCEDURE() IS NOT NULL   SET @EMessageSent = N'Error %d, Level %d, State %d, Procedure %s, Line %d, Message: '
        SET @EMessageSent = @EMessageSent + ERROR_MESSAGE()
        RAISERROR(@EMessageSent, 10 /* @ESeverity */ , @EState, @ENumber, @ESeverity, @EState, @EProcedure, @ELine) WITH LOG
    END CATCH

    FETCH NEXT FROM cur INTO @ConstraintName, @TypeDesc, @TableName
END

CLOSE cur
DEALLOCATE cur


-------------------------------------------------------------------------------
-- Done.                                                                       
-------------------------------------------------------------------------------

RAISERROR('-- Execute this code only *AFTER* you have examined it throughly!', 10, 1) WITH NOWAIT

What About "Under-Used" Indexes?

We've all spent time looking for unused indexes (right?), but today my boss asked about under-used indexes. Now, I wrote a script a while ago to find completely unused indexes, but finding indexes that were only being used a little was just different enough to require a new script.

The script below displays all indexes, with the least-used (including unused) ones first, and totals for user and system reads and updates. I figured having the ratio between reads and updates might be useful, since if it takes more updates to maintain the index than it gets used, you might want to investigate its cost-benefit.

Some caveats:

• If your server just rebooted, these numbers probably won't mean a lot; wait for at least a full day to allow time for the indexes to be read or written to.
• Also, don't forget about periodic events like end-of-month processing. You don't want a phone call at 12:15 am on the first of the month when it turns out that index was needed after all.
• Just because it costs more to maintain an index doesn't mean it's not still worth having. The cost of maintaining the index is probably spread out over a long period of time; the benefit to a waiting user of a query that takes two seconds instead of five minutes to run might outweigh that cost. It depends on your particular environment.
• Note that indexes that support primary key constraints are excluded, as I'm pretty sure I'm not going to ever want to drop them!

Finally, consider disabling any under-used indexes you find, rather than just dropping them. If it turns out the index was important, you'll still have to rebuild the index, but at least the DDL code will be there already.

-- Display unused and under-used indexes. 

DECLARE @dbid INT = DB_ID(DB_NAME())

; WITH cteUser AS
(
    SELECT object_id,
           index_id,
           user_seeks + user_scans + user_lookups   AS 'User Reads',
           user_updates                             AS 'User Updates'
      FROM sys.dm_db_index_usage_stats   
     WHERE database_id = @dbid
)
,
cteSystem AS
(
    SELECT object_id,
           index_id,
           system_seeks + system_scans + system_lookups   AS 'System Reads',
           system_updates                                 AS 'System Updates'
      FROM sys.dm_db_index_usage_stats   
     WHERE database_id = @dbid
)
,
cteTotal AS
(
    SELECT u.object_id,
           u.index_id,
           [User Reads]   + [System Reads]     AS 'Total Reads',
           [User Updates] + [System Updates]   AS 'Total Updates'
      FROM cteUser     u
      JOIN cteSystem   s
        ON u.object_id = s.object_id
       AND u.index_id  = s.index_id
)
,
cteReadToUpdateRatio AS
(
    SELECT object_id,
           index_id,
           CONVERT(NVARCHAR,
                  CONVERT(MONEY, ISNULL(
                                        CAST([Total Reads] AS REAL)
                                        /
                                        NULLIF([Total Updates], 0.0)
                                        , [Total Reads]
                                        )
                               , 1
                         )
                  )   AS 'Read-to-Update Ratio'
                  
      FROM cteTotal
)
SELECT OBJECT_NAME(i.object_id)               AS 'Table Name',
       i.name                                 AS 'Index Name',
       u.[User Reads],
       u.[User Updates],
       s.[System Reads],
       s.[System Updates],
       t.[Total Reads],
       t.[Total Updates],
       r.[Read-to-Update Ratio],
       t.[Total Reads] + t.[Total Updates]    AS 'TOTAL READS + UPDATES'
  FROM cteUser                        u
  JOIN cteSystem                      s
    ON u.object_id = s.object_id
   AND u.index_id  = s.index_id
  JOIN cteTotal                       t
    ON u.object_id = t.object_id
   AND u.index_id  = t.index_id
  JOIN cteReadToUpdateRatio           r
    ON u.object_id = r.object_id
   AND u.index_id  = r.index_id
  JOIN sys.indexes                    i
    ON u.object_id = i.object_id
   AND u.index_id  = i.index_id
  JOIN sys.objects                    o
    ON u.object_id = o.object_id
 WHERE OBJECTPROPERTY(o.object_id, 'IsUserTable') = 1
   AND i.is_primary_key = 0
   --AND (t.[Total Reads] = 0                                  -- These definitely need to be dropped. 
   -- OR CAST(r.[Read-to-Update Ratio] AS REAL) < 1.0)         -- These may need to be dropped. 
 ORDER BY CAST(r.[Read-to-Update Ratio] AS REAL)

Deleting User Objects in Master Database

This script erases everything in the master database that is not Microsoft-shipped. Be careful: some user objects belong in the master database (database triggers, security objects, for example). Backing up the master database first would be a prudent step.

-- Drops all user objects that have been accidentally created in the master database.
USE master

DECLARE @sName     sysname
DECLARE @sTypeDesc NVARCHAR(1024)
DECLARE @sType     NVARCHAR(1024)
DECLARE @nScore    INT
DECLARE @sSql      NVARCHAR(1024)

DECLARE TheCursor CURSOR FOR
 SELECT name, type_desc
   FROM sys.objects
  WHERE is_ms_shipped = 0

OPEN TheCursor

FETCH NEXT FROM TheCursor
 INTO @sName, @sTypeDesc

WHILE @@FETCH_STATUS = 0
BEGIN
 SET @sType  = REVERSE(@sTypeDesc)
 SET @nScore = CHARINDEX('_', @sType)
 SET @sType  = SUBSTRING(@sType, 0, @nScore)
 SET @sType  = REVERSE(@sType)

 SET @sSql = 'DROP ' + @sType + ' ' + @sName
 RAISERROR('%s', 10, 1, @sSql) WITH NOWAIT 
 EXEC sp_sqlexec @sSql
 
 FETCH NEXT FROM TheCursor
  INTO @sName, @sTypeDesc
END

CLOSE TheCursor
DEALLOCATE TheCursor

PRINT 'Done.'