Wednesday, March 20, 2013

Get Missing Identity Value Information

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

DECLARE @Store_TABLE_Info TABLE  (    Ident int IDENTITY (1,1)
                                    , Table_Name varchar(256)
                                    , Column_Name varchar(130)
                                    , Seed_Value sql_variant
                                    , increment_value sql_variant
                                    , Last_value sql_variant
                                 )

DECLARE @Table_Name varchar(261)
        ,@Column_Name varchar(130)
        ,@Seed_value sql_variant
        ,@Increment_Value sql_variant
        ,@last_Value sql_variant
        ,@count int
        ,@Sqlcmd1 nvarchar(4000)
        ,@Sqlcmd2 nvarchar(4000)
        ,@Sqlcmd3 nvarchar(4000)
        ,@count_Records  varchar(50)

/*
 List out all table names thats has identity column.
    * Get Complete Table Name (Including Schema Name).
    * Get Column Name on which Identity property is enabled
    * Get Seed Value (Starting Value of Identity)
    * Get Increment Value (Incremental value for Identity Property)
    * Get Current Seed value/ Last Value in column. (Current Value)
*/

INSERT INTO        @Store_TABLE_Info
                SELECT    '['+SCHEMA_NAME([Schema_id])+'].'+ '['+OBJECT_NAME(ST.[object_id])+']'  [Table_Name]
                        ,'['+SIC.Name+']' [Column_Name]
                        ,Seed_Value
                        ,Increment_Value
                        ,ISNULL(last_Value,0)last_Value
                FROM    Sys.Tables ST
                        JOIN sys.Identity_columns SIC ON ST.[object_id] = SIC.[object_id]

CREATE TABLE    #Temp_Final_Mismatch    (    TABLE_Name varchar(261)
                                            , Column_Name varchar(130)
                                            , Missing_Identity_Values varchar (MAX)
                                        )

SET @count = 1
WHILE @count < =  ( SELECT COUNT(* ) FROM @Store_TABLE_Info)
    BEGIN
        SELECT    @TABLE_Name = TABLE_Name
                ,@Column_Name = Column_Name
                ,@Seed_value = Seed_value
                ,@Increment_Value = Increment_Value
                ,@last_Value = last_Value
        FROM    @Store_TABLE_Info
        WHERE    Ident = @Count

        /*
        Considering One table at a time (Making Use of While Loop):

        Using Above information:
        1. we First create a Temporary table with same column name, with the same Seed Value and Same Increment value.
        2. We have Seed Value, Increment Value and Current Value, This information is enough to have complete set of Identity values in our Temporary table.
            * This is done through another while loop.
        3. Now we will compare Temporary Table created in Step 1 with User Defined table
            * Trying to find all missing Identity values that are present in Temporary table but not present in user defined table. (One Table at a time)
        4. Result from above step, we store into another temporary table.
        */   

        --        Based on Original Seed Value, Increment Value and Current Value, We can calculate how many records suppose to be present in User Defined Table.       

        SET @count_Records = CONVERT(varchar ,((CONVERT(int,@last_Value) - (1* CONVERT(int,@Seed_value)) )/CONVERT(int,@Increment_Value)  )+    1)

        SET @Sqlcmd1 = '
            CREATE TABLE #Temp_'+CONVERT(varchar , @Count) +' ( '+@Column_Name+' int IDENTITY ('+CONVERT(varchar ,@Seed_value) +','+CONVERT( varchar,@Increment_Value)+'), Bit_Value bit default (1))
           
            '+/*           
                Inserting Estimated No of Records into Temporary table.
            */+'
            DECLARE @Count1 int
            SET @Count1 = 1
            While @Count1 < = '+@count_Records+' -- To Get Actual Number of records that should be present in a TABLE.
                BEGIN
                    INSERT INTO #Temp_'+CONVERT(varchar , @Count) +' VALUES (default)       
                    SET @Count1 = @Count1 + 1
                END

            '+/*           
                Comparing Temporary Table with User Defined Table, To get Missing Identity Values information.
            */+'
            DECLARE @temp_store_id TABLE ( Ident int IDENTITY (1,1), Missing_Ident varchar (50))

            INSERT INTO @temp_store_id (Missing_Ident)
                                        SELECT '+@Column_Name+'
                                        FROM
                                            (
                                                SELECT    '+@Column_Name+' 
                                                FROM    #Temp_'+CONVERT(varchar , @Count) +'
                                                    EXCEPT
                                                SELECT    '+@Column_Name +'
                                                FROM    '+@TABLE_Name+'
                                            ) X

            '+/*           
                Now that we have all Missing value into @temp_store_id Table. Lets make it a string, So that we can easily read it.
            */+'
            DECLARE @String varchar (max)
            SET @String = '' ''
            SELECT @String = @String + CASE WHEN @string = '' '' THEN '''' ELSE '','' END + CONVERT( varchar(50),Missing_Ident)
            FROM @temp_store_id

            '+/*           
                Store String Values in another temporary table, Which will be displayed at the end
            */+'
            If @String != '' ''
                INSERT INTO #Temp_Final_Mismatch VALUES ('''+@TABLE_Name+''', '''+@Column_Name +''' , @String )

            DROP TABLE #Temp_'+CONVERT(varchar , @Count)

            --print @Sqlcmd1
             EXEC Sp_EXECutesql @Sqlcmd1
        SET @count = @count + 1
END

-- Displaying Final Result.
SELECT    *
FROM    #Temp_Final_Mismatch

DROP TABLE #Temp_Final_Mismatch
SET NOCOUNT ON
SET ANSI_WARNINGS ON

Top 10 Unused Indexes

SELECT TOP 10
o.name AS ObjectName
, i.name AS IndexName
, i.index_id AS IndexID 
, dm_ius.user_seeks AS UserSeek
, dm_ius.user_scans AS UserScans
, dm_ius.user_lookups AS UserLookups
, dm_ius.user_updates AS UserUpdates
, p.TableRows
, 'DROP INDEX ' + QUOTENAME(i.name)
+ ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(OBJECT_NAME(dm_ius.object_id)) as 'drop statement'
FROM sys.dm_db_index_usage_stats dm_ius 
INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id AND dm_ius.object_id = i.object_id  
INNER JOIN sys.objects o on dm_ius.object_id = o.object_id
INNER JOIN sys.schemas s on o.schema_id = s.schema_id
INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.object_id
                FROM sys.partitions p GROUP BY p.index_id, p.object_id) p
        ON p.index_id = dm_ius.index_id AND dm_ius.object_id = p.object_id
WHERE OBJECTPROPERTY(dm_ius.object_id,'IsUserTable') = 1
AND dm_ius.database_id = DB_ID()  
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC

Top 10 Missing Indexes

SELECT TOP 10
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
object_name(dm_mid.object_id,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + object_name(dm_mid.object_id,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +
CASE
    WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'
    ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC

Find Duplicate Indexes

WITH MyDuplicate AS (SELECT
    Sch.[name] AS SchemaName,
    Obj.[name] AS TableName,
    Idx.[name] AS IndexName,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 1) AS Col1,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 2) AS Col2,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 3) AS Col3,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 4) AS Col4,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 5) AS Col5,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 6) AS Col6,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 7) AS Col7,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 8) AS Col8,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 9) AS Col9,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 10) AS Col10,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 11) AS Col11,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 12) AS Col12,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 13) AS Col13,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 14) AS Col14,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 15) AS Col15,
    INDEX_Col(Sch.[name] + '.' + Obj.[name], Idx.index_id, 16) AS Col16
FROM sys.indexes Idx
INNER JOIN sys.objects Obj ON Idx.[object_id] = Obj.[object_id]
INNER JOIN sys.schemas Sch ON Sch.[schema_id] = Obj.[schema_id]
WHERE index_id > 0)
SELECT    MD1.SchemaName, MD1.TableName, MD1.IndexName,
        MD2.IndexName AS OverLappingIndex,
        MD1.Col1, MD1.Col2, MD1.Col3, MD1.Col4,
        MD1.Col5, MD1.Col6, MD1.Col7, MD1.Col8,
        MD1.Col9, MD1.Col10, MD1.Col11, MD1.Col12,
        MD1.Col13, MD1.Col14, MD1.Col15, MD1.Col16
FROM MyDuplicate MD1
INNER JOIN MyDuplicate MD2 ON MD1.tablename = MD2.tablename
    AND MD1.indexname <> MD2.indexname
    AND MD1.Col1 = MD2.Col1
    AND (MD1.Col2 IS NULL OR MD2.Col2 IS NULL OR MD1.Col2 = MD2.Col2)
    AND (MD1.Col3 IS NULL OR MD2.Col3 IS NULL OR MD1.Col3 = MD2.Col3)
    AND (MD1.Col4 IS NULL OR MD2.Col4 IS NULL OR MD1.Col4 = MD2.Col4)
    AND (MD1.Col5 IS NULL OR MD2.Col5 IS NULL OR MD1.Col5 = MD2.Col5)
    AND (MD1.Col6 IS NULL OR MD2.Col6 IS NULL OR MD1.Col6 = MD2.Col6)
    AND (MD1.Col7 IS NULL OR MD2.Col7 IS NULL OR MD1.Col7 = MD2.Col7)
    AND (MD1.Col8 IS NULL OR MD2.Col8 IS NULL OR MD1.Col8 = MD2.Col8)
    AND (MD1.Col9 IS NULL OR MD2.Col9 IS NULL OR MD1.Col9 = MD2.Col9)
    AND (MD1.Col10 IS NULL OR MD2.Col10 IS NULL OR MD1.Col10 = MD2.Col10)
    AND (MD1.Col11 IS NULL OR MD2.Col11 IS NULL OR MD1.Col11 = MD2.Col11)
    AND (MD1.Col12 IS NULL OR MD2.Col12 IS NULL OR MD1.Col12 = MD2.Col12)
    AND (MD1.Col13 IS NULL OR MD2.Col13 IS NULL OR MD1.Col13 = MD2.Col13)
    AND (MD1.Col14 IS NULL OR MD2.Col14 IS NULL OR MD1.Col14 = MD2.Col14)
    AND (MD1.Col15 IS NULL OR MD2.Col15 IS NULL OR MD1.Col15 = MD2.Col15)
    AND (MD1.Col16 IS NULL OR MD2.Col16 IS NULL OR MD1.Col16 = MD2.Col16)
ORDER BY
    MD1.SchemaName,MD1.TableName,MD1.IndexName

Top 10 Expensive Queries

SELECT DISTINCT TOP 10
d.Name,t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
JOIN sys.databases d ON t.dbid = d.database_id
ORDER BY s.max_elapsed_time DESC, ExecutionCount DESC