How to find out which index is missing

To find out the missing index, we have to analyze the information in the following system views:

• sys.dm_db_missing_index_groups
• sys.dm_db_missing_index_group_stats
• sys.dm_db_missing_index_details

You may download the script that combine all the necessary information from the following URL:

   https://gist.github.com/alexsorokoletov/a079629f9e1435c7f81f

And here is the SQL script:

SELECT
    CONVERT (varchar, getdate(), 126) AS runtime,
    mig.index_group_handle, mid.index_handle,
    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.*, mid.database_id, mid.[object_id]

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 database_id =  DB_ID('my_database')

ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC


How to use it?

• I'm relying on improvement_measure value and I review the top 5 missing index information and then decide if I should create an index. 
• We should not create all the indexes returned by this query. Because some of the missing indexes can be merge into one index.
• We should review the existing indexes and compare against what is missing and then decide the new index. This might involves deleting the existing index before creating a new one.

Life is tough with naming convention especially we want to know how many times that we have reviewed a particular index. My naming rule works this way:

• IX_my_table_1 - this is the first index.
• IX_my_table_2 - this is another index.
• IX_my_table_2_1 - this is the newer version of index where IX_my_table_2 has been dropped and merge with the new missing columns.

Fixing the database state

Sometimes after rebooting the server, the database state might stick in "recovery". Waiting and waiting and rebooting might not change to the normal state.

In this case, we need to fix this issue manually.

To view the current database state:

   SELECT name, state_desc from sys.databases

To fix the problematic database:


   ALTER DATABASE test SET EMERGENCY;
   GO

   ALTER DATABASE test SET SINGLE_USER
   GO

   DBCC CHECKDB (test, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
   GO

   ALTER DATABASE test SET MULTI_USER
   GO

Before running the above commands, please make sure you have done sufficient research on the Internet before executing it!

Running the same stored procedure sequentially

Use the following system stored procedure to block the same stored procedure from running concurrently.

   sp_getapplock

Finally, you must call the following so that next process is allowed to run the same stored procedure.

   sp_releaseapplock

Note:

• The above system stored procedures must be running within a database transaction.
• If @@lock_timeout is -1, then, sp_getapplock will be block until it has been released and commit/rollback must be called. 
• If @@lock_timeout is not -1, then, the result will be less than zero (failed). But, sometimes it returns "> 0" (i.e., successfully get he app lock) and on the other hand it might fail to lock the "record in the table". In this case, error #1222 will be raised by SQL server.

Getting the deadlock details

In SQL 2012, you may query the deadlock details with the following SQL statement:

SELECT XEvent.query('(event/data/value/deadlock)[1]') AS DeadlockGraph
FROM ( SELECT XEvent.query('.') AS XEvent
       FROM ( SELECT CAST(target_data AS XML) AS TargetData
              FROM sys.dm_xe_session_targets st
                   JOIN sys.dm_xe_sessions s
                   ON s.address = st.event_session_address
              WHERE s.name = 'system_health'
                    AND st.target_name = 'ring_buffer'
              ) AS Data
              CROSS APPLY
                 TargetData.nodes
                    ('RingBufferTarget/event[@name="xml_deadlock_report"]')
              AS XEventData ( XEvent )
      ) AS src;


The following query is for SQL2008:

SELECT  CAST(event_data.value('(event/data/value)[1]',
                               'varchar(max)') AS XML) AS DeadlockGraph
FROM    ( SELECT    XEvent.query('.') AS event_data
          FROM      (    -- Cast the target_data to XML
                      SELECT    CAST(target_data AS XML) AS TargetData
                      FROM      sys.dm_xe_session_targets st
                                JOIN sys.dm_xe_sessions s
                                 ON s.address = st.event_session_address
                      WHERE     name = 'system_health'
                                AND target_name = 'ring_buffer'
                    ) AS Data -- Split out the Event Nodes
                    CROSS APPLY TargetData.nodes('RingBufferTarget/
                                     event[@name="xml_deadlock_report"]')
                    AS XEventData ( XEvent )
        ) AS tab ( event_data )


For more details about deadlock and resolution, please read the following article:

https://www.red-gate.com/simple-talk/sql/database-administration/handling-deadlocks-in-sql-server/
https://www.red-gate.com/products/dba/sql-monitor/resources/articles/monitor-sql-deadlock

Something about deadlock

This is a nice article that talks about how to find more details about deadlock.

Finding and Extracting deadlock information using Extended Events

http://social.technet.microsoft.com/wiki/contents/articles/31280.finding-and-extracting-deadlock-information-using-extended-events.aspx

Using TRY/CATCH to handle the deadlock

https://technet.microsoft.com/en-us/library/aa175791(v=sql.80).aspx

Wait for a while..

Here is the code that you need to put your process into sleep mode:

declare @wait_time nvarchar(255)

print 'start working on it.. => current ms: ' + cast(datepart(millisecond, getdate()) as nvarchar)

set @wait_time = '00:00:00.' + left(cast(cast(rand() * 1000 as int) as nvarchar), 3)
waitfor delay @wait_time

print 'im done and waited for ' + @wait_time
    + ' => current ms: '
    + cast(datepart(millisecond, getdate()) as nvarchar)

Concatenating the records and use appropriate separator

If we have 4 records, we want to concatenate all 4 records and the last separator should be "&" symbol. We can achieve this by using row_number() function and then decide whether the separator is "," or "&".

For example:

declare @s nvarchar(max)

create table #tb1 (
    email nvarchar(255)
)
insert into #tb1 values ('a@a.com');
insert into #tb1 values ('b@a.com');
insert into #tb1 values ('c@a.com');
insert into #tb1 values ('d@a.com');

select
    @s = coalesce(@s
        + case when rowidx < cnt then ',' else ' & ' end
        + email, email)
from (
    select
        rowidx=row_number() over( order by email )
        , email
        , cnt = (select count(*) from tb1)
    from #tb1
) as a

select @s

drop table #tb1