sql-server-2008

Causes of recompilations

- The schema of regular tables, temporary tables, or views referred to in the stored procedure statement have changed. Schema changes include changes to the metadata of the table or the indexes on the table.

- Bindings (such as defaults) to the columns of regular or temporary tables have changed.

- Statistics on the table indexes or columns have changed past a certain threshold.

- An object did not exist when the stored procedure was compiled, but it was created during execution. This is called deferred object resolution.

- SET options have changed. (SET NOCOUNT doesn’t cause stored procedure recompilation)

- The execution plan was aged and deallocated.

- An explicit call of sp_recompile on a table, view, trigger or stored procedure

- There was an explicit use of the RECOMPILE hint:
CREATE PROCEDURE <spName> WITH RECOMPILE => prevents the caching of the stored procedure plan
EXEC <spname> WITH RECOMPILE; => a new plan is generated temporarily, without being cached of affecting any other existing cached plan
OPTION (RECOMPILE)</sql> => causes statement recompilation

Find locks in SQL Server

dm_tran_locks contains info about all current locks, granted or pending.

select *
from sys.dm_tran_locks

Finding queries that are using table scans

The T-SQL script used to discover queries that are using table scans by searching within query plan for TableScan tag:

SELECT TOP 20
        DB_NAME(ST.dbid)AS database_name
        , ST.text as sql_Statement
        , CP.usecounts
        , QP.query_plan
FROM
        sys.dm_exec_cached_plans CP
        CROSS APPLY sys.dm_exec_sql_text(CP.plan_handle) ST
        CROSS APPLY sys.dm_exec_query_plan(CP.plan_handle) QP
WHERE
        ST.dbid = DB_ID()
        AND CAST(QP.query_plan AS NVARCHAR(MAX)) LIKE '%<TableScan%'
ORDER BY CP.usecounts DESC

Determining when a query was last run

Running the T-SQL script given will identify the top 20 times a given query was run, as ordered by last_execution_time. You can identify the relevant query by supplying part of it as a filter via the WHERE clause.

For example, determine when a specific stored procedure was last time changed:

SELECT DISTINCT TOP 20
        DB_NAME(QT.dbid) AS DatabaseName
        , QT.text as sql_statement
        , QS.last_execution_time
FROM
        sys.dm_exec_query_stats QS
        CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as QT
WHERE
        QT.dbid = DB_ID()               -- limit search on current database
        AND QT.text LIKE '%CREATE PROCEDURE%GetEmployees%'
ORDER BY QS.last_execution_time DESC;

Finding the queries that use the most I/O

Running the SQL script given in the following listing will identify the top 20 queries that use the most I/O.

SELECT TOP 20
        DB_NAME(QT.dbid) as database_name
        , QS.total_logical_reads + QS.total_logical_writes as total_io
        , QS.execution_count   
        , SUBSTRING(QT.text, (qs.statement_start_offset/2)+1,
                ((      CASE qs.statement_end_offset
                                WHEN -1 THEN DATALENGTH(QT.text)
                                ELSE qs.statement_end_offset
                        END - QS.statement_start_offset)/2) + 1) as statement_text
        , QT.text as parent_statement_text     
        , O.name
        , O.type_desc
        , QP.query_plan
FROM
        sys.dm_exec_query_stats QS
        CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) QT
        CROSS APPLY sys.dm_exec_query_plan(QS.plan_handle) QP
        LEFT JOIN sys.objects O ON O.object_id = QT.objectid
WHERE QT.dbid = DB_ID()
ORDER BY total_io DESC

Find missing indexes using DMVs

The following query determines which are the missing indexes and displays their column details based on dm_db_missing_index_group_stats DMV.

USE <DatabaseName>;

SELECT 
        REPLACE(ID.[Statement], '[' + DB_NAME(ID.database_id) + '].', '') as table_name,
        CAST(S.avg_total_user_cost * S.avg_user_impact * S.user_seeks as INT) as [score],
        ID.equality_columns, ID.inequality_columns, ID.included_columns,               
        'CREATE INDEX '
        + 'IX_' + OBJECT_NAME(ID.object_id) + '_' + CAST(ID.index_handle as VARCHAR)
        + ' ON ' + REPLACE(ID.[Statement], '[' + DB_NAME(ID.database_id) + '].', '')
        + ' (' + ISNULL (ID.equality_columns,'')
        + CASE
                WHEN ID.equality_columns IS NOT NULL AND ID.inequality_columns IS NOT NULL
                THEN ', ' + ID.inequality_columns
                ELSE ''
          END
        + ')'
        + ISNULL (' INCLUDE (' + ID.included_columns + ')', '') AS create_index_statement
FROM
        sys.dm_db_missing_index_details ID
        INNER JOIN sys.dm_db_missing_index_groups G
                ON ID.index_handle = G.index_handle
        INNER JOIN sys.dm_db_missing_index_group_stats S
                ON G.index_group_handle = S.group_handle
WHERE
        ID.database_id = DB_ID()
        AND OBJECTPROPERTY(ID.[object_id], 'IsMsShipped') = 0  
ORDER BY score DESC;

Get database restore history

Returns last 10 restores performed on your server:

SELECT TOP 10
        RH.destination_database_name AS [Database],
        RH.user_name AS [Restored By],
        RH.restore_date AS [Restore Started],
        BMF.physical_device_name AS [Restored From],
        RF.destination_phys_name AS [Restored To],
        RH.*
FROM
        msdb.dbo.restorehistory RH
        INNER JOIN msdb.dbo.backupset BS ON RH.backup_set_id = BS.backup_set_id
        INNER JOIN msdb.dbo.restorefile RF ON RH.restore_history_id = RF.restore_history_id
        INNER JOIN msdb.dbo.backupmediafamily BMF ON BMF.media_set_id = BS.media_set_id
--WHERE destination_database_name = '<DatabaseName>'
ORDER BY RH.restore_history_id DESC
GO

Compute the last day of the year

You bring the last day of a year (let's say December 31th 1900) and then you add it the number of years passed:

DECLARE @dt AS DATETIME = SYSDATETIME();
SELECT DATEADD(year, DATEDIFF(year,'19001231', @dt), '19001231');
GO

Improve performance using filtered indexes

A filtered index is a non-clustered index that contains only a subset of the number of rows contained in a table. You add a WHERE clause to reduce the number of rows that are stored at the leaf level.

CREATE NONCLUSTERED INDEX <Index_Name>
ON <Table Name>(<Column(s)>)
WHERE <Filter Condition>

A good real example it is use is on Status column, as you might have a large number of rows with Closed status that are not part of your current searches (the business wants to query for Open, Processing, Invoicing, etc)

CREATE NONCLUSTERED INDEX [IX_Orders_IdStatus_Filterered]
ON [dbo].[Orders] ([IdStatus])
WHERE ([IdStatus] IN ('O', 'P', 'I'))

Another well-suited scenarios is where you frequently have to filter out NULL values.

Pros:

  • By having fewer rows in an index, less I/O is done when that index is used.
  • Index size is dramatically reduced.
  • The statistics on these filtered indexes are filtered as well, which typically results in them being more accurate.
  • Cons:

  • Use certain expressions, such as BETWEEN, NOT IN, or a CASE statement.
  • Parameterized queries doesn't take advantage of filtered indexes.
  • How to revalidate/refresh all stored procedures and functions

    After a stored procedure is created, you might make changes to the underlying objects that could break the stored procedure. The script presented will retrieve the list of stored procedures and functions and then will re-validate them, based on sp_refreshsqlmodule system stored procedure.

    Here is the script that performs this.

    SET NOCOUNT ON;

    DECLARE
            @RowIndex AS SMALLINT = 1
            , @ObjectName AS NVARCHAR(128)
            , @Sql AS NVARCHAR(MAX)
           

    -- Get list of objects (stored procedures, functions) to be refreshed
    DECLARE @ObjectsTable AS Table (RowIndex SMALLINT IDENTITY(1,1), ObjectType nvarchar(60), ObjectName NVARCHAR(128), IsRefreshed BIT, Error NVARCHAR(MAX))
    INSERT INTO @ObjectsTable (ObjectType, ObjectName, IsRefreshed)
    SELECT O.type_desc, SCHEMA_NAME(O.schema_id) + '.' + O.name, 1
    FROM
            sys.sql_modules M
            INNER JOIN sys.objects O ON O.object_id = M.object_id
    WHERE
            O.is_ms_shipped = 0
            AND O.type_desc NOT IN ('VIEW')
    ORDER BY O.type_desc, O.name

    -- For each function/sp...
    WHILE @RowIndex <= (SELECT COUNT(*) FROM @ObjectsTable)
    BEGIN  
            SELECT @ObjectName = ObjectName        
            FROM @ObjectsTable
            WHERE RowIndex = @RowIndex
           
            SET @Sql = 'EXEC sp_refreshsqlmodule ''' + @ObjectName + ''''
            BEGIN TRY
                    EXEC sp_executesql @Sql
            END TRY
            BEGIN CATCH
                    UPDATE @ObjectsTable
                    SET
                            IsRefreshed = 0,
                            Error = ERROR_MESSAGE()
                    WHERE RowIndex = @RowIndex
                   
                    IF @@TRANCOUNT > 0
                            ROLLBACK TRAN
            END CATCH
                                                                   
            -- Process next object
            SET @RowIndex = @RowIndex + 1
    END


    -- List all objects not refreshed
    SELECT ObjectType, ObjectName, IsRefreshed, Error
    FROM @ObjectsTable
    WHERE IsRefreshed = 0
    GO

    Pages

    Subscribe to RSS - sql-server-2008