---------------------------------------------
-- 1 - SQL Server Process Percentage Complete
---------------------------------------------

-- Window 1

USE AdventureWorks
GO 
DBCC CHECKDB
GO

-- Window 2
USE AdventureWorks
GO

SELECT * 
FROM sys.dm_exec_sessions 
WHERE session_id = <spid>
GO

SELECT * 
FROM sys.dm_exec_requests 
WHERE session_id = <spid>
GO

SELECT session_id, start_time, status, command, 
DB_NAME(database_id), SUSER_SNAME(user_id), 
percent_complete, total_elapsed_time
FROM sys.dm_exec_requests 
WHERE session_id = <spid>
GO


-----------------------------
-- 2 - SQL Server Index Usage
-----------------------------

USE AdventureWorks
GO
-- In this first query we are just using 
-- sys.dm_db_index_usage_stats and sys.objects 
-- to get a list of the indexes that have been 
-- used and how they are being used.

SELECT DB_NAME(DATABASE_ID) AS DATABASENAME, 
       OBJECT_NAME(B.OBJECT_ID) AS TABLENAME, 
       INDEX_NAME = (SELECT NAME 
                     FROM   SYS.INDEXES A 
                     WHERE  A.OBJECT_ID = B.OBJECT_ID 
                            AND A.INDEX_ID = B.INDEX_ID), 
       USER_SEEKS, 
       USER_SCANS, 
       USER_LOOKUPS, 
       USER_UPDATES 
FROM   SYS.DM_DB_INDEX_USAGE_STATS B 
       INNER JOIN SYS.OBJECTS C 
         ON B.OBJECT_ID = C.OBJECT_ID 
WHERE  DATABASE_ID = DB_ID(DB_NAME()) 
       AND C.TYPE <> 'S' 
GO

-- In this query we are listing each user table and 
-- all of the tables indexes that have not been used by using 
-- a NOT EXISTS against sys.dm_db_index_usage_stats.

SELECT   DB_NAME() AS DATABASENAME, 
         OBJECT_NAME(B.OBJECT_ID) AS TABLENAME, 
         B.NAME AS INDEXNAME, 
         B.INDEX_ID 
FROM     SYS.OBJECTS A 
         INNER JOIN SYS.INDEXES B 
           ON A.OBJECT_ID = B.OBJECT_ID 
WHERE    NOT EXISTS (SELECT * 
                     FROM   SYS.DM_DB_INDEX_USAGE_STATS C 
                     WHERE  B.OBJECT_ID = C.OBJECT_ID 
                            AND B.INDEX_ID = C.INDEX_ID) 
         AND A.TYPE <> 'S' 
ORDER BY 1, 2, 3 
GO

-- In this query we tie in our PIVOT query above with 
-- sys.dm_db_index_usage_stats so we can look at only 
-- the indexes that have been used since the last 
-- time the stats were reset.

SELECT   PVT.TABLENAME, PVT.INDEXNAME, [1] AS COL1, [2] AS COL2, [3] AS COL3, 
         [4] AS COL4, [5] AS COL5, [6] AS COL6, [7] AS COL7, B.USER_SEEKS, 
         B.USER_SCANS, B.USER_LOOKUPS 
FROM     (SELECT A.NAME AS TABLENAME, 
                 A.OBJECT_ID, 
                 B.NAME AS INDEXNAME, 
                 B.INDEX_ID, 
                 D.NAME AS COLUMNNAME, 
                 C.KEY_ORDINAL 
          FROM   SYS.OBJECTS A 
                 INNER JOIN SYS.INDEXES B 
                   ON A.OBJECT_ID = B.OBJECT_ID 
                 INNER JOIN SYS.INDEX_COLUMNS C 
                   ON B.OBJECT_ID = C.OBJECT_ID 
                      AND B.INDEX_ID = C.INDEX_ID 
                 INNER JOIN SYS.COLUMNS D 
                   ON C.OBJECT_ID = D.OBJECT_ID 
                      AND C.COLUMN_ID = D.COLUMN_ID 
          WHERE  A.TYPE <> 'S') P 
         PIVOT 
         (MIN(COLUMNNAME) 
          FOR KEY_ORDINAL IN ( [1],[2],[3],[4],[5],[6],[7] ) ) AS PVT 
         INNER JOIN SYS.DM_DB_INDEX_USAGE_STATS B 
           ON PVT.OBJECT_ID = B.OBJECT_ID 
              AND PVT.INDEX_ID = B.INDEX_ID 
              AND B.DATABASE_ID = DB_ID() 
ORDER BY TABLENAME, INDEXNAME; 
GO


-- This view gives you information about insert, 
-- update and delete operations that occur on a 
-- particular index.  In addition, this view also 
-- offers data about locking, latching and access 
-- methods.  There are several columns that are 
-- returned from this view, but these are some of the 
-- more interesting columns:

-- leaf_insert_count - total count of leaf level inserts 
-- leaf_delete_count - total count of leaf level inserts 
-- leaf_update_count  - total count of leaf level updates

SELECT OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME], 
       I.[NAME] AS [INDEX NAME], 
       A.LEAF_INSERT_COUNT, 
       A.LEAF_UPDATE_COUNT, 
       A.LEAF_DELETE_COUNT 
FROM   SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A 
       INNER JOIN SYS.INDEXES AS I 
         ON I.[OBJECT_ID] = A.[OBJECT_ID] 
            AND I.INDEX_ID = A.INDEX_ID 
WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1
GO


----------------------
-- 3 - Missing indexes
----------------------

-- sys.dm_db_missing_index_details
-- Equality example
USE AdventureWorks;
GO
SELECT City, StateProvinceID, PostalCode
FROM Person.Address
WHERE StateProvinceID = 9;
GO

-- Inequality example
USE AdventureWorks;
GO
SELECT City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode <> '20854';
GO

-- Simple Example
SELECT	Index_Handle, 
		DB_NAME(Database_ID) AS 'Database', 
		OBJECT_NAME(Object_ID) AS 'Object',
		Equality_Columns,
		Inequality_Columns,
		Included_Columns,
		Statement
FROM sys.dm_db_missing_index_details;
GO

-- Basic correlation
SELECT	D.Index_Handle, 
		DB_NAME(D.Database_ID) AS 'Database', 
		OBJECT_NAME(D.Object_ID) AS 'Object',
		D.Equality_Columns,
		D.Inequality_Columns,
		D.Included_Columns,
		D.Statement,
		S.unique_compiles,
		S.user_seeks,
		S.user_scans,
		S.avg_total_user_cost,
		S.avg_user_impact
FROM sys.dm_db_missing_index_details D
INNER JOIN sys.dm_db_missing_index_groups G
	ON D.index_handle = G.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats S
	ON S.group_handle = G.index_group_handle;
GO
