Do you have rarely used indexes that are hurting performance of writes?

We know that indexes are necessary for query performance but comes with overhead during DML operations. Usually, the overhead is worth the performance gain we get out of them.

What I needed was to find and consider for removal indexes that incur heavy maintenance overhead, but are only lightly used for queries or enforcing constraints.

Here is an a version of query to find unused or lesser used indexes. It looks for non-unique indexes with large number of DMLs with ratio of writes vs reads is relatively high (I am using a factor of 10 but at this point its an arbitrary starting point).

---

WITH cte
     AS (SELECT Db_name(iu.database_id)                   db_name,
                Object_name(iu.object_id, iu.database_id) object_name,
                i.NAME                                    index_name,
                i.type_desc                               index_type,
                Sum(iu.user_seeks) + Sum(iu.user_scans)
                + Sum(iu.user_lookups)                    total_user_reads,
                Sum(iu.user_updates)                      total_user_writes
         FROM   sys.dm_db_index_usage_stats iu
                INNER JOIN sys.indexes i ON i.object_id = iu.object_id
                           AND i.index_id = iu.index_id
         WHERE  iu.database_id = Db_id()
                AND i.index_id > 0 
                AND i.is_unique = 0
         GROUP  BY iu.database_id,
                   iu.object_id,
                   i.NAME,
                   i.type_desc)
SELECT *,
       total_user_writes / total_user_reads write_to_read_ratio
FROM   cte
WHERE  1 = 1
       AND total_user_writes > 1000000 
       AND total_user_reads  < 1000
       AND ( total_user_writes / NULLIF(total_user_reads,0) > 10
              OR total_user_writes / total_user_reads IS NULL )
ORDER  BY write_to_read_ratio DESC 

---

Please make sure to test before actually deleting any index.

Using Extended Events To Capture Backup and Restore Progress

If you are running a DATABASE BACKUP or RESTORE command manually, SQL Server will show you the progress at a specified % completion interval. For the BACKUP, the default is every approximately 10%. You can change that frequency interval by specifying STATS [ = percentage ] option.


BACKUP DATABASE [AdminDBA]
TO  DISK = N'O:\MSSQL13.SQL2016AG01\MSSQL\Backup\AdminDBA.bak' WITH
NOFORMAT, NOINIT, 
NAME = N'AdminDBA-Full Database Backup',
SKIP,
NOREWIND,
NOUNLOAD, 
STATS = 10
GO

But what if the backup/restore was started from a different session that you don't have access (another DBA, scheduled job etc.) or you need more information to troubleshoot issues?

Here I should first mention that there are already couple options to track the progress.

You could review or query the sql server error logs (unless trace flag 3226 is enabled). By default its disabled. You can if that trace flag is enabled using:

DBCC TRACESTATUS(3226);

If trace flag 3226 is enabled, the successful backup messages are suppressed in the error log.

Or you could use one of the popular DMVs:

SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time 
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a 
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')


Starting with SQL Server 2016, you can turn on the backup_restore_progress_trace extended event to trace both Backup and Restore progress. It comes with rich collection and diagnostic capabilities of extended events that will give you great insight into backup/restore operations and help you troubleshoot any issues better.

Here is how to setup the trace using SSMS:

Connect to the SQL Server then expand all way up to the Extended Events -> Sessions, right click and select New Session Wizard:

Accept the welcome page and on the next page give the session a name. If you choose to you can check box against the Start the event session at Server Startp. I will leave it unchecked though.



There is no built in trace template so leave the Do not use a template selected and click next.

On the next screen, type in "backup" to search in the EventLibrary. select the "backup_restore_progress_trace" then click > to add it to the Selected Events box.



FYI: Here are the data fields that are specific to this event and are automatically captured.

On the next screen it will display list of Global fields if you would like to capture.  For this example, I am not selecting any of them.

On the following screen you can add any filters you would like.

Just for the hack of it here, I have added  a filter to exclude system databases from the trace.

On the next screen, configure the data storage options. Generally I prefer to store the trace data in file.

Click next and it will bring you to the Summary page.

You can click on Script to generate the script to create event.

Click on Finish to create the event.



You could choose option to start the trace immediately.

If not, right click on the newly created event and select Start Session to start the trace.

Once the trace is started, you can view the Live trace by right clicking the trace and select Watch Live Data.

Or expand the event, select the file, right click and click View Target Data.

Here is sample trace data.

Sample TSQL code to create the extended event trace:

CREATE EVENT SESSION [Monitor Backup Progress] ON SERVER
ADD EVENT sqlserver.backup_restore_progress_trace
ADD TARGET package0.event_file(SET filename=N'Monitor Backup Progress',max_file_size=(10))
WITH
(
   MAX_MEMORY=4096 KB,
   EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
   MAX_DISPATCH_LATENCY=30 SECONDS,
   MAX_EVENT_SIZE=0 KB,
   MEMORY_PARTITION_MODE=NONE,
   TRACK_CAUSALITY=OFF,
   STARTUP_STATE=OFF)
GO

Find identical duplicate indexes

I realized yesterday I posted this quickly after testing only against SQL 2016 version. So I decided to also test against SQL 2014 and 2012 versions to make sure its compatible.  I made one tweak (removed the compression_delay field from the output) to make it possible. 

So please give it a try and let me know your results, perspectives and feedback!

As I had mentioned in my original post, I was looking to find identical duplicate non-clustered indexes i.e. indexes on same table with same columns in same order. The query below uses two variables to make it little bit more flexible.

If you have a need or just curious to find indexes having same columns but in any order, set value for the variable @disregard_column_order = 1 in the code.

The query excludes identical indexes where one is clustered and the other one is non-clustered index.  But you can again toggle that by setting value for variable @include_clustered_indexes = 1.

---

/* @include_clustered_indexes
Whether to include identical indexes where one is clustered 

and  the other one is non-clustered index

*/

DECLARE @include_clustered_indexes bit = 0

/* @disregard_column_order
Whether to find duplicate indexes where although all 

columns are same, they may not be in same order
*/

DECLARE @disregard_column_order bit = 0

;WITH cte

AS

       (SELECT

                        o.schema_id,

                        o.type_desc,

                        o.object_id,

                        i.index_id,

                        i.name index_name,

                        index_columns =
                                                             COALESCE((STUFF((SELECT CAST(',' +
                                                             COL_NAME(object_id, column_id) AS varchar(max))

                               FROM sys.index_columns

                               WHERE
                          (
                                 object_id = i.object_id AND
                                         index_id = i.index_id
                          )

                               ORDER BY object_id, index_id,

                               CASE WHEN @disregard_column_order = 1
                                then column_id 
                                else key_ordinal end

                               FOR xml PATH ('')), 1, 1, '')), ''),

                        i.type_desc index_type,

                        i.is_unique,

                        i.data_space_id,

                        i.ignore_dup_key,

                        i.is_primary_key,

                        i.is_unique_constraint,

                       i.fill_factor,

                        i.is_padded,

                        i.is_disabled,

                        i.is_hypothetical,

                        i.allow_row_locks,

                        i.allow_page_locks,

                        i.has_filter,

                        i.filter_definition

       FROM sys.indexes i

       INNER JOIN sys.objects o ON o.object_id = i.object_id

       WHERE OBJECTPROPERTY(o.object_id, 'ismsshipped') = 0 AND index_id != 0

       AND i.index_id > CASE WHEN @include_clustered_indexes = 1 THEN 0 ELSE 1 END

)

SELECT

          SCHEMA_NAME(i1.schema_id) schema_name,

          i1.type_desc,

          OBJECT_NAME(i1.object_id) object_name,

          i1.index_name,

          i1.*

FROM cte i1

INNER JOIN (SELECT schema_id, type_desc, object_id, index_columns

            FROM cte

            GROUP BY schema_id, type_desc, object_id, index_columns

            HAVING COUNT(*) > 1) i2

                      ON i1.schema_id = i2.schema_id

           AND i1.type_desc = i2.type_desc

           AND i1.object_id = i2.object_id

           AND i1.index_columns = i2.index_columns

ORDER BY schema_name, i1.type_desc, object_name, i1.index_name

---

Now among other things, the query does not take into account the ASC or DESC clause of the index. So you may have two identical indexes but one is sorted ASC and the other one is DESC and you may have a very good reason for that.  Nor does this query consider if one or both indexes are filtered indexes. I would like to hear your feedbacks before putting more efforts to cover every other possible options.

Download the SQL Script from GitHub:

Find-Duplicate-SQL-Indexes

Find identical duplicate indexes

I realized yesterday I posted this quickly after testing only against SQL 2016 version. So I decided to also test against SQL 2014 and 2012 versions to make sure its compatible.  I made one tweak (removed the compression_delay field from the output) to make it possible. 

So please give it a try and let me know your results, perspectives and feedback!

As I had mentioned in my original post, I was looking to find identical duplicate non-clustered indexes i.e. indexes on same table with same columns in same order. The query below uses two variables to make it little bit more flexible.

If you have a need or just curious to find indexes having same columns but in any order, set value for the variable @disregard_column_order = 1 in the code.

The query excludes identical indexes where one is clustered and the other one is non-clustered index.  But you can again toggle that by setting value for variable @include_clustered_indexes = 1.

---

/*
whether to include identical indexes where one is clustered and 
the other one is non-clustered index
*/

DECLARE @include_clustered_indexes bit = 0

/*
whether to find duplicate indexes where although all columns are same, they may not be in same order
*/

DECLARE @disregard_column_order bit = 0

;WITH cte

AS

       (SELECT

                        o.schema_id,

                        o.type_desc,

                        o.object_id,

                        i.index_id,

                        i.name index_name,

                        index_columns =
                                                             COALESCE((STUFF((SELECT CAST(',' +
                                                             COL_NAME(object_id, column_id) AS varchar(max))

                               FROM sys.index_columns

                               WHERE
                          (
                                 object_id = i.object_id AND
                                         index_id = i.index_id
                          )

                               ORDER BY object_id, index_id,

                               CASE WHEN @disregard_column_order = 1
                                then column_id 
                                else key_ordinal end

                               FOR xml PATH ('')), 1, 1, '')), ''),

                        i.type_desc index_type,

                        i.is_unique,

                        i.data_space_id,

                        i.ignore_dup_key,

                        i.is_primary_key,

                        i.is_unique_constraint,

                       i.fill_factor,

                        i.is_padded,

                        i.is_disabled,

                        i.is_hypothetical,

                        i.allow_row_locks,

                        i.allow_page_locks,

                        i.has_filter,

                        i.filter_definition

       FROM sys.indexes i

       INNER JOIN sys.objects o ON o.object_id = i.object_id

       WHERE OBJECTPROPERTY(o.object_id, 'ismsshipped') = 0 AND index_id != 0

       AND i.index_id > CASE WHEN @include_clustered_indexes = 1 THEN 0 ELSE 1 END

)

SELECT

          SCHEMA_NAME(i1.schema_id) schema_name,

          i1.type_desc,

          OBJECT_NAME(i1.object_id) object_name,

          i1.index_name,

          i1.*

FROM cte i1

INNER JOIN (SELECT schema_id, type_desc, object_id, index_columns

            FROM cte

            GROUP BY schema_id, type_desc, object_id, index_columns

            HAVING COUNT(*) > 1) i2

                      ON i1.schema_id = i2.schema_id

           AND i1.type_desc = i2.type_desc

           AND i1.object_id = i2.object_id

           AND i1.index_columns = i2.index_columns

ORDER BY schema_name, i1.type_desc, object_name, i1.index_name

---

Now among other things, the query does not take into account the ASC or DESC clause of the index. So you may have two identical indexes but one is sorted ASC and the other one is DESC and you may have a very good reason for that.  Nor does this query consider if one or both indexes are filtered indexes. I would like to hear your feedbacks before putting more efforts to cover every other possible options.


I have tested this against SQL versions 2012 till 2016.

---