The SQL Dude!

On-Disk & In-Memory Pages that teach you the SQL way of life!

Archive for the ‘DB Maintenance’ Category

Scan, Find & Understand Missing Indexes in your SQL Database

Posted by Sudarshan Narasimhan on June 17, 2016


This post is Part 4 of my series of posts on Index Maintenance for a DBA. So here goes.

Enough has been said & talked about regarding missing indexes in SQL Server. Before you read any further, it is very important to understand that “missing indexes” that your colleagues & friends talk about are really missing index “recommendations“. The keyword is the recommendations part. Take the information in this post as a whole and don’t go crazy creating every missing index recommended when you run the query given below. Refer to my earlier posts in the indexing series (duplicate, unused indexes) to know why it is a bad idea to go “index-crazy”. You need to carefully evaluate the benefits of having an index vs the cost of maintaining it.

Some Background

SQL Server provides exposes index recommendations via DMV’s (sys.dm_db_missing_index_details, sys.dm_db_missing_index_groups and sys.dm_db_missing_index_group_stats). These dynamic views contain information collected by the SQL Server Query optimizer engine as your DB workloads and queries execute on the database engine. Since this is “dynamic” information, it is only as relevant as your last SQL Service restart. So this is something that needs to get into your blood as a DBA to check for periodically and is NOT a one-off activity.

 

Things to Consider & Understand before implementing Missing Index Recommendations

Your boss asks you if this index thingy will improve the performance of the database. The favourite DBA response “It Depends”  😀

MrBeanItDepends

 

 

 

 

 

 

 

 

 

 

Well, it really does! I’ll explain why… Like any other recommendations you need to take into account additional information along with this data to come to a conclusion. Consider for example the following:

  1. Has your DB workload changed since the time you collected the missing index recommendations? (i.e. Has there been an application release or DB schema upgrade since)
  2. Are there similar existing indexes on the table where it’s recommending you create another index? (Maybe you just need to add a few additional columns to the existing index as included columns to “cover” it…)
  3. Is the query batch or workload very frequently run, enough that the impact is very high if you create this index? (Refer to the last_user_seek and last_user_scan columns to find out when a particular query last used these columns for seek/scan operations)
  4. Do you have enough space in the DB/disk to afford yet another index? Especially important, if it’s a large table. (DBA 101 question, I know!)
  5. If you create this index and it’s rarely used, you are increase the maintenance overhead and also slowly down WRITE operations on the table.

 

IT DEPENDS, really it does!

 

Missing Index – Improvement Measure Calculation Logic

To help answer some of these questions, I calculate an arbitrary number to determine the “usefulness” of a new index, called improvement measure. The higher this number, the more useful it is to create it. The improvement measure is calculated based on 4 factors

  1. Average cost of the user queries that could be reduced by the new index. This is the current cost of the query.
  2. Average impact on the query if this index were created. i.e. The query cost would on average drop by this percentage value.
  3. Number of Seek operations that this index could have been used by user queries.
  4. Number of Scan operations that this index could have been used by user queries.

 

Now that you understand what missing indexes are, factors to consider and the logic behind the recommendations, go ahead and run the below query on your databases. The output provides you the “CREATE INDEX” T-SQL statement that you can use to create the index, if you see fit after considering all factors mentioned so far. This saves you time having to write the T-SQL yourself. The CREATE INDEX statement will include the index key columns in order, suggest included columns where applicable.

 


Script – Find Missing Indexes Recommendations in SQL Server

USE [msdb]  /*Replace with your Database Name */
GO
SELECT TOP 50 
    GETDATE() AS [RunTime],
    DB_NAME(mid.database_id) AS [DBNAME], 
    OBJECT_NAME(mid.[object_id]) AS [ObjectName], mid.[object_id] AS [ObjectID],
    CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS [Improvement_Measure],
    'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle) 
    + ' ON ' + mid.statement 
    + ' (' + ISNULL (mid.equality_columns,'') 
    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL (mid.inequality_columns, '')
    + ')' 
    + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS [CREATE_INDEX_Statement],
    migs.user_seeks, migs.user_scans, migs.last_user_seek, migs.last_user_scan, migs.avg_total_user_cost, migs.avg_user_impact, migs.avg_system_impact,
    mig.index_group_handle, mid.index_handle
FROM sys.dm_db_missing_index_groups mig
    INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
    INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
    AND mid.database_id = DB_ID()
ORDER BY [Improvement_Measure] DESC
GO

 

Script Result
Here is a sample output from my test system showing all missing indexes recommended on the MSDB database.

MissingIndexes

 

 

 

Test, Test, Test!

Did I mention that you must test these index recommendations on your Test system? If you know your application workload/query, you must test to gauge the impact of having this index.

Keep in mind, your testing must not be to run just a single batch/query, as 95% of the time this will yield a positive result. But, you must include other sample workloads along with your specific query. Why? Because, indexes are structures on the table not specific to just your query. Other queries can use it too and SQL has to keep the index up to date when data changes happen to the table. Always test any index changes with as close to a real world production like workload as possible. You don’t want any surprises on Production now, do you?  🙂

Well SQL folks, this concludes my indexing series of posts for DBA’s. Leave your comments down below if you find this useful and would like more material on these lines.

 

-Sudarshan (TheSQLDude)

Posted in DB Maintenance, Indexing, Performance, T-SQL | Tagged: , , , , , , | 1 Comment »

Seek & Evaluate Unused Indexes in your SQL database

Posted by Sudarshan Narasimhan on June 16, 2016


This post is Part 3 of my series of posts on Index Maintenance for a DBA. So here goes.

As a regular part of your DBA work, it is very important to keep evaluating the need for indexes in your database environment. Any DBA worth his salt, will always look to “maintain” the indexes by ensuring the fragmentation is kept low. Most of you probably have maintenance tasks/scripts to take care of this. An often under-looked aspect in index maintenance is to also consider whether some indexes are an over-kill a.k.a unnecessary and potentially drop them if it’s not really utilized. This is especially important when dealing with 3rd party vendor databases, where the DBA does not have full control on what comes in and what goes out in terms of index changes.

I like to think of the database as my “house” and take care of it the same way one would do to your own home. You always consider who/what comes in to your house. The same care needs to be applied to the databases you maintain. As gatekeepers to the database, even if you don’t have complete control on what comes in, it is good to be aware & have the stats handy so that you can work with your application teams or vendors, to keep the house tidy.

If you link this data back to my previous posts Part 1 – Seek & Destroy Duplicate Indexes in your SQL Database and Part 2 – Seek & Understand Top Indexes by Size in your SQL database , you will be on your way to hopefully free up some space and realize space cost and performance gains.

Here is a handy script that I use to:-

1) Lists the indexes on each table in the database, that are completely unused.

This scripts lists those indexes where not even a single Read operation has used these indexes. Keep in mind, write operations still need to update this index as data is being updated/inserted contributing to overall time of the Write operations. When no-one is using the index for Read it becomes a system overhead.

2) Lists the indexes on each table in the database, that are having more Writes compared to Reads

The maintenance cost of having these indexes from insert, update, or delete operations on the underlying table are more compared to the benefits for Read operations. These indexes needs to be investigated to see if they are still required. A cost benefit analysis on these indexes needs to be performed.
Note: I’m not advocating that you remove these indexes, but just that you carefully consider the benefits vs the cost, before making any decision.


Script #1 – TOTALLY UNUSED INDEXES

USE [DBNAME] /* Replace with your Database Name */
GO
--TOTALLY UN-USED INDEXES
SELECT DB_NAME(s.database_id) as [DB Name], OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id,
    i.is_disabled, i.is_hypothetical, i.has_filter, i.fill_factor, i.is_unique,
    s.user_updates AS [Total Writes],
    (s.user_seeks + s.user_scans + s.user_lookups) AS [Total Reads],
    s.user_updates - (s.user_seeks + s.user_scans + s.user_lookups) AS [Difference],
    (partstats.used_page_count / 128.0) AS [IndexSizeinMB]
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
    ON s.[object_id] = i.[object_id]
    AND s.index_id = i.index_id
    AND s.database_id = DB_ID()
INNER JOIN sys.dm_db_partition_stats AS partstats
    ON i.object_id = partstats.object_id AND i.index_id = partstats.index_id
WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
    AND user_updates > (user_seeks + user_scans + user_lookups)
    AND (s.user_lookups=0 AND s.user_scans=0 AND s.user_seeks=0)
    AND i.index_id > 1
ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC OPTION (RECOMPILE);
GO

Script #1 Result
Here is a sample output from my test system showing all unused indexes on the MSDB database.

UnusedIndexes

 

 

 

 

 

 


Script #2 – INDEXES WITH MORE WRITES THAN READS

USE [DBNAME] /* Replace with your Database Name */
GO
--INDEXES WITH WRITES > READS
SELECT DB_NAME(s.database_id) as [DB Name], OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id,
    i.is_disabled, i.is_hypothetical, i.has_filter, i.fill_factor, i.is_unique,
    s.user_updates AS [Total Writes],
    (s.user_seeks + s.user_scans + s.user_lookups) AS [Total Reads],
    s.user_updates - (s.user_seeks + s.user_scans + s.user_lookups) AS [Difference],
    (partstats.used_page_count / 128.0) AS [IndexSizeinMB]
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
    ON s.[object_id] = i.[object_id]
    AND s.index_id = i.index_id
    AND s.database_id = DB_ID()
INNER JOIN sys.dm_db_partition_stats AS partstats
    ON i.object_id = partstats.object_id AND i.index_id = partstats.index_id
WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
    AND (s.user_lookups<>0 OR s.user_scans<>0 OR s.user_seeks<>0)
    AND s.user_updates > (s.user_seeks + s.user_scans + s.user_lookups)
    AND i.index_id > 1
ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC OPTION (RECOMPILE);
GO

Script #2 Result
Here is a sample output from my test system showing all indexes having more Writes compared to Reads on the MSDB database.

WritesMoreThanReadsIndexes

 

 

 

 

 

Well, that’s all folks.

-Sudarshan (TheSQLDude)

Posted in DB Maintenance, Indexing, T-SQL | Tagged: , , , , , , | 1 Comment »

Seek & Understand Top Indexes by Size in your SQL database

Posted by Sudarshan Narasimhan on June 3, 2016


This post is Part 2 of my series of posts on Index Maintenance for a DBA. So here goes.

Often, when you are performing capacity planning or upgrades or working with 3rd party vendor on database schema upgrades, you need to look into the existing indexes on the database. One of the key things you need to consider is disk space. Aside from the actual “data” in the tables, indexes also occupy space depending on the type of index. Here is a handy script that I use to quickly understand how many indexes are present for each table and the size of each index, in any given SQL database.

If you link this data back to my post “Seek & Destroy Duplicate Indexes in your SQL Database“, you will be on your way to hopefully free up some space and realize space cost savings.

This query only reports on user objects (object_id>99) and filters out HEAP tables. There are some exceptions to the filter on user objects (like service broker tables, CDC tables etc) which will still be considered as “user objects” though it supports a system functionality.

USE [msdb]  /*Replace with your Database Name */
GO
SELECT 
    DB_NAME() DatabaseName,
    OBJECT_SCHEMA_NAME(i.OBJECT_ID) SchemaName,
    OBJECT_NAME(i.OBJECT_ID ) TableName,
    i.name IndexName,
    i.type_desc as [IndexType],
    i.is_primary_key as [PrimaryKeyIndex],
    i.is_unique as [UniqueIndex],
    i.is_disabled as [Disabled],
    SUM(s.used_page_count) / 128.0 IndexSizeinMB,
    CAST((SUM(s.used_page_count)/128.0)/1024.0 as decimal(10,6))  IndexSizeinGB
FROM sys.indexes AS i
INNER JOIN sys.dm_db_partition_stats AS S
    ON i.OBJECT_ID = S.OBJECT_ID AND I.index_id = S.index_id
WHERE i.object_id>99 and i.type_desc  'HEAP'
GROUP BY i.OBJECT_ID, i.name, s.used_page_count, i.type_desc, i.is_primary_key, i.is_unique, i.is_disabled
ORDER BY s.used_page_count DESC
GO

 

Script Result
Here is a sample output from my test system showing all the indexes on MSDB database.

Index Size

 

Stay tuned for more index related scripts in this series.

-Sudarshan (TheSQLDude)

Posted in DB Maintenance, Indexing, T-SQL | Tagged: , , , | 1 Comment »

Seek & Destroy Duplicate Indexes in your SQL Database

Posted by Sudarshan Narasimhan on May 20, 2016


This post is Part 1 of my series of upcomingposts on Index Maintenance for a DBA. So here goes.

There are all sorts of indexing tuning & optimizations that can be done on a SQL database. One of the no-brainer things a DBA must do is “seek & destroy” (pun intended) duplicate indexes. Duplicate indexes are indexes exactly similar in structure, including key columns and their base tables. These indexes serve no useful purpose and in fact contribute to slowness for WRITE database operations as the WRITER (INSERT/UPDATE/DELETE) must do more work to update the index as well as this directly contributes to the slowness of a write operation. If AlwaysON or other HA, then it has to do this on all the replicas as well, adding additional unnecessary network overhead.

You’d be surprised at the results. Given this script a run on any of your databases especially Vendor application databases. If you do see any duplicates, time to talk to your vendor or DB developer.

Here is query that I use to identify duplicate indexes. This query considers 2 or more indexes to be Exact Duplicates when all the key columns & included columns (if any) match another index on the same table.

USE [tempdb]  /* Change to your DB Name */
GO
DECLARE @i int=1, @count int=0, @tblname nvarchar(250), @indxname nvarchar(250), @cmd nvarchar(4000)
DECLARE @DuplicateIndexes TABLE(id int IDENTITY(1,1), table_name nvarchar(250), index_name nvarchar(250), dup_index_name nvarchar(250))
DECLARE @IndexInfo TABLE(id int IDENTITY(1,1), table_name nvarchar(250), index_name nvarchar(250), index_description nvarchar(250),index_keys nvarchar(250), included_columns nvarchar(250) null)

-- exact duplicates
;WITH indexcols AS
(
    select object_id as id, index_id as indid, name,
    (select case keyno when 0 then NULL else colid end as [data()]
    from sys.sysindexkeys as k
    where k.id = i.object_id
    and k.indid = i.index_id
    order by keyno, colid
    for xml path('')) as cols,
    (select case keyno when 0 then colid else NULL end as [data()]
    from sys.sysindexkeys as k
    where k.id = i.object_id
    and k.indid = i.index_id
    order by colid
    for xml path('')) as inc
    from sys.indexes as i
),
DuplicateIndexes AS
(
    SELECT
    object_schema_name(c1.id) + '.' + object_name(c1.id) as [TableName],
    c1.name as [IndexName],
    c2.name as [DuplicateIndexName]
    FROM indexcols as c1
    join indexcols as c2
    ON c1.id = c2.id
    and c1.indid < c2.indid
    and c1.cols = c2.cols
    and c1.inc = c2.inc
)
INSERT INTO @DuplicateIndexes
SELECT * FROM DuplicateIndexes

SELECT @count=count(*) FROM @DuplicateIndexes
WHILE (@i <= @count)
BEGIN
    SELECT @tblname = [table_name], @indxname = [index_name] FROM @DuplicateIndexes WHERE [id] = @i
    DECLARE @tmptbl TABLE (index_name nvarchar(250), index_description nvarchar(250),index_keys nvarchar(250))
    DELETE FROM @tmptbl
    SET @cmd = 'EXEC sp_helpindex ''' +@tblname+ ''''

    INSERT INTO @tmptbl (index_name, index_description, index_keys)
        EXEC (@cmd)

    INSERT INTO @IndexInfo (table_name, index_name, index_description, index_keys)
    SELECT @tblname, index_name, index_description, index_keys FROM @tmptbl

    DECLARE @incld_cols sysname
    SET @cmd = '
    SELECT @tmpstr=b.IncludedColumns
    FROM (
    SELECT LEFT(a.indx_cols_included, LEN(a.indx_cols_included)-1) as [IncludedColumns]
    FROM (
    SELECT STUFF (( SELECT C.Name + '','' AS [text()] 
    FROM sys.indexes I 
     INNER JOIN sys.index_columns IC 
      ON  I.object_id = IC.object_id AND I.index_id = IC.index_id 
     INNER JOIN sys.columns C 
      ON IC.object_id = C.object_id and IC.column_id = C.column_id 
    WHERE IC.object_id=object_id('''+@tblname+''') and I.name = '''+@indxname+''' and IC.is_included_column=1
    FOR XML PATH('''')
    ), 1, 0, '''') AS indx_cols_included) as a) as b'
    
    EXEC sp_executesql @cmd, N'@tmpstr sysname OUTPUT', @incld_cols OUTPUT
    UPDATE @IndexInfo SET included_columns = @incld_cols WHERE [id]=@i

SET @i = @i + 1
END

--DUPLICATE INDEX INFORMATION SUMMARY
SELECT DB_NAME() AS [DB Name], d.table_name as [TableName], d.index_name as [IndexName], d.dup_index_name as [DuplicateIndexName], i.index_keys as [IndexKeyColumns], i.included_columns as [IncludedColumns]
FROM @DuplicateIndexes d
JOIN @IndexInfo i
    ON d.table_name = i.table_name
    AND d.index_name = i.index_name
GO

 

Script Result
Here is a sample output from my test system where I’ve created 2 indexes having the same structure
DuplicateIndexes

 

 

 

Note: The original source script was from HERE. I’ve modified & added to this to include index key column information as well as included columns to present a more clear output on the duplicate indexes.

 

Stay tuned for more index related scripts in this series.

-Sudarshan (TheSQLDude)

Posted in DB Maintenance, Indexing, Query Optimization, T-SQL | 4 Comments »

T-SQL Script to find the size of every index in the database

Posted by Sudarshan Narasimhan on December 15, 2012


I wrote this query to find the size of all indexes in the database for one of my customers. Thought I’d share this script with everyone. Cheers.

CREATE PROCEDURE uspIndexSizeinDB
AS 
-- Author: Sudarshan (TheSQLDude.com)
-- Data Created: Dec 15, 2012
-- Description: Calculate the SQL Server index size for all indexes in the current database
  BEGIN 
    DECLARE  @IndexSize BIGINT, 
             @IndexID   INT,
             @IndexName nvarchar(200),
             @IndexType nvarchar(50),
             @ObjectID  INT
     
    SET @IndexSize = 0
    
    create table #tmpresults (ObjName nvarchar(100), IndexID int, IndexName nvarchar(200),[IndexSize(MB)] int, IndexType nvarchar(50))
    
    DECLARE curIndex CURSOR  FOR 
    SELECT sysind.object_id, sysind.index_id, sysind.name, sysind.type_desc 
    FROM sys.indexes sysind 
    join sys.sysobjects sysobj
    on sysind.object_id = sysobj.id
    where sysind.type>0 and sysobj.type not in ('S','IT')
    order by sysobj.id asc
   
    OPEN curIndex 
     
    FETCH NEXT FROM curIndex 
    INTO @ObjectID, @IndexID, @IndexName, @IndexType
     
    WHILE (@@FETCH_STATUS = 0) 
      BEGIN 
        SELECT @IndexSize = sum(avg_record_size_in_bytes * record_count) 
        FROM   sys.dm_db_index_physical_stats(DB_ID(),@ObjectID, @IndexID, NULL, 'detailED') 
         
        insert into #tmpresults (ObjName, IndexID, IndexName, [IndexSize(MB)], IndexType)
        SELECT TableName = OBJECT_NAME(@ObjectID), 
               IndexID = @IndexID, 
               IndexName = @IndexName,
               [IndexSize(MB)] = CONVERT(DECIMAL(16,1),(@IndexSize / (1024.0 * 1024))), IndexType = @IndexType
         
        FETCH NEXT FROM curIndex 
        INTO @ObjectID, @IndexID, @IndexName, @IndexType
      END 
    CLOSE curIndex 
    DEALLOCATE curIndex 
    
    select UPPER(ObjName) as ObjectName, IndexID, IndexName, IndexType, [IndexSize(MB)]  from #tmpresults order by [IndexSize(MB)] desc
  END 
GO

Posted in DB Maintenance, T-SQL | Tagged: , , , , , , | 2 Comments »

T-SQL Script for SQL Server Statistics Last Updated Time & Percent Rows modified

Posted by Sudarshan Narasimhan on August 4, 2011


I wrote a post on the same topic @ Amit’s Blog. I made some changes to the script to make it easier for DBA’s to read the output. I am re-posting this here for your reference. This can be quite handy if you are managing multiple SQL instances and you have lot of tables or have large databases where you only want to update statistics on some tables based on row modifications (data changes e.g. OLTP). You can always use DBCC SHOW_STATISTICS or SP_AUTOSTATS to find out the same information for a specific table, index or column stats.

select

schemas.name as table_schema,

tbls.name as Object_name,

i.id as Object_id,

i.name as index_name,

i.indid as index_id,

i.rowmodctr as modifiedRows,

(select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2) as rowcnt,

convert(DECIMAL(18,8), convert(DECIMAL(18,8),i.rowmodctr) / convert(DECIMAL(18,8),(select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2))) as ModifiedPercent,

stats_date( i.id, i.indid ) as lastStatsUpdateTime

from sysindexes i

inner join sysobjects tbls on i.id = tbls.id

inner join sysusers schemas on tbls.uid = schemas.uid

inner join information_schema.tables tl

on tbls.name = tl.table_name

and schemas.name = tl.table_schema

and tl.table_type=‘BASE TABLE’

where 0 < i.indid and i.indid < 255

and table_schema <> ‘sys’

and i.rowmodctr <> 0

and i.status not in (8388704,8388672)

and (select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2) > 0

order by modifiedRows desc

Keep in mind that Rowmodctr values displayed in above output are not entirely reliable starting with SQL 2005 onwards. But, you can rely on last updated time to make a decision if the statistics need to be updated. Read here for more info on this.

 

 

You can use this script to generate the UPDATE STATISTICS command for each table in your table. I am using FULL SCAN by default, which you can of course modify to use specific SAMPLE. You have to un-comment the comments lines, to actually perform the update.

 

select identity(int,1,1) as rownum,table_name into table_count from information_schema.tables where table_type=‘base table’

declare @count int,@stmt varchar (255),@maxcount int,@tblname varchar(50)

set @count=1

select @maxcount=count(*) from table_count

while @count < @maxcount+1

begin

      select @tblname=table_name from table_count where rownum=@count

      set @stmt = ‘UPDATE STATISTICS ‘+ ‘[‘ +@tblname+ ‘]’ + ‘ WITH FULLSCAN’

      print @stmt

      –PRINT (‘Updating statistics for table :’+@tblname)

      –EXEC(@stmt)

      –PRINT (‘Finished Updating statistics for table :’+@tblname)

      print ”

      set @count=@count+1

      set @stmt=”

End

drop table table_count

Have fun with your database maintenanceSmile. As always stay tuned for more cool SQL stuff.

Posted in DB Maintenance | Tagged: , , , , , | 4 Comments »