sql-server-2012

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

    Get list of replicated tables

    T-SQL script for getting list of replicated tables and associated subscriptions / publications:

    USE master;

    SELECT
            P.publication,
            S.publisher_db, S.subscriber_db,
            A.source_owner, A.source_object, A.destination_owner, A.destination_object
    FROM
            distribution.dbo.MSsubscriptions S
            INNER JOIN distribution.dbo.MSpublications P ON P.publication_id = S.publication_id
            INNER JOIN distribution.dbo.MSarticles A
                    ON A.publication_id = P.publication_id AND A.article_id = S.article_id
    ORDER BY
            P.publication, S.subscriber_db, A.source_object;

    How to find all the dependencies of a table - Object dependencies

    In SQL Server 2008 there are two new Dynamic Management Functions introduced to keep track of Object Dependencies: sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities.

    Returning the entities that refer to a given entity:

    SELECT
            referencing_schema_name, referencing_entity_name,
            referencing_class_desc, is_caller_dependent
    FROM sys.dm_sql_referencing_entities ('<TableName>', 'OBJECT')

    Returning entities that are referenced by an object:

    SELECT
            referenced_schema_name, referenced_entity_name, referenced_minor_name,
            referenced_class_desc, is_caller_dependent, is_ambiguous
    FROM sys.dm_sql_referenced_entities ('<StoredProcedureName>', 'OBJECT');

    Another option is to use a pretty useful tool called SQL Dependency Tracker from Red Gate.

    Get the space used for each database

    SELECT
            DB.name,
            MF.physical_name,
            MF.type_desc AS FileType,
            MF.size * 8 / 1024 AS FileSizeMB,
            fileproperty(MF.name, 'SpaceUsed') * 8/ 1024 AS UsedSpaceMB
    FROM
            sys.master_files MF
            JOIN sys.databases DB ON DB.database_id = MF.database_id
    ORDER BY
            DB.name,
            FileSizeMB DESC

    EXEC sp_msforeachdb @command1='use [?] exec sp_spaceused'

    USE <DatabaseName>
    EXEC sp_spaceused

    Using DBCC SQLPERF to check log free space for a SQL Server database:

    DBCC SQLPERF(logspace)

    T-SQL script for copying a database

    USE master;

    DECLARE
            @SourceDatabaseName AS SYSNAME = '<SourceDatabaseName>',
            @TargetDatabaseName AS SYSNAME = '<NewDatabaseName>'


           
    -- ============================================
    -- Define path where backup will be saved
    -- ============================================
    IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = @SourceDatabaseName)
            RAISERROR ('Variable @SourceDatabaseName is not set correctly !', 20, 1) WITH LOG      
           
    DECLARE @SourceBackupFilePath varchar(2000)
    SELECT @SourceBackupFilePath = BMF.physical_device_name
    FROM
            msdb.dbo.backupset B
            JOIN msdb.dbo.backupmediafamily BMF ON B.media_set_id = BMF.media_set_id
    WHERE B.database_name = @SourceDatabaseName
    ORDER BY B.backup_finish_date DESC

    SET @SourceBackupFilePath = REPLACE(@SourceBackupFilePath, '.bak', '_clone.bak')



    -- ============================================
    -- Backup source database
    -- ============================================
    DECLARE @Sql NVARCHAR(MAX)
    SET @Sql = 'BACKUP DATABASE @SourceDatabaseName TO DISK = ''@SourceBackupFilePath'''
    SET @Sql = REPLACE(@Sql, '@SourceDatabaseName', @SourceDatabaseName)
    SET @Sql = REPLACE(@Sql, '@SourceBackupFilePath', @SourceBackupFilePath)
    SELECT 'Performing backup...', @Sql as ExecutedSql
    EXEC (@Sql)



    -- ============================================
    -- Automatically compose database files (.mdf and .ldf) paths
    -- ============================================
    DECLARE
            @LogicalDataFileName as NVARCHAR(MAX),
            @LogicalLogFileName as NVARCHAR(MAX),
            @TargetDataFilePath as NVARCHAR(MAX),
            @TargetLogFilePath as NVARCHAR(MAX)
                   
    SELECT
            @LogicalDataFileName = name,
            @TargetDataFilePath = SUBSTRING(physical_name,1,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))) + '\' + @TargetDatabaseName + '.mdf'
    FROM sys.master_files
    WHERE
            database_id = DB_ID(@SourceDatabaseName)       
            AND type = 0            -- datafile file

    SELECT
            @LogicalLogFileName = name,
            @TargetLogFilePath = SUBSTRING(physical_name,1,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))) + '\' + @TargetDatabaseName + '.ldf'
    FROM sys.master_files
    WHERE
            database_id = DB_ID(@SourceDatabaseName)       
            AND type = 1            -- log file    



    -- ============================================
    -- Restore target database
    -- ============================================
    IF EXISTS (SELECT 1 FROM sys.databases WHERE name = @TargetDatabaseName)
            RAISERROR ('Variable @SourceDatabaseName is not set correctly as a database with the same name already exists!', 20, 1) WITH LOG       
           
    SET @Sql = 'RESTORE DATABASE @TargetDatabaseName
    FROM DISK = '
    '@SourceBackupFilePath''
    WITH MOVE '
    '@LogicalDataFileName'' TO ''@TargetDataFilePath'',
    MOVE '
    '@LogicalLogFileName'' TO ''@TargetLogFilePath'''
    SET @Sql = REPLACE(@Sql, '@TargetDatabaseName', @TargetDatabaseName)
    SET @Sql = REPLACE(@Sql, '@SourceBackupFilePath', @SourceBackupFilePath)
    SET @Sql = REPLACE(@Sql, '@LogicalDataFileName', @LogicalDataFileName)
    SET @Sql = REPLACE(@Sql, '@TargetDataFilePath', @TargetDataFilePath)
    SET @Sql = REPLACE(@Sql, '@LogicalLogFileName', @LogicalLogFileName)
    SET @Sql = REPLACE(@Sql, '@TargetLogFilePath', @TargetLogFilePath)
    SELECT 'Restoring...', @Sql as ExecutedSql
    EXEC (@Sql)

    Get database backup history

    In case you need to see the last backups performed on a specific database:

    SELECT
            BMF.physical_device_name as BackupFileName     
            , B.backup_finish_date as BackupDate
            , B.backup_size/1024.0/1024.0 AS BackupSizeMB
    FROM
            msdb.dbo.backupset B
            JOIN msdb.dbo.backupmediafamily BMF ON B.media_set_id = BMF.media_set_id
    WHERE database_name = '<YourDatabaseName>'
    ORDER BY B.backup_finish_date DESC

    Get data and log file sizes for all databases

    SELECT
            DB.name,
            SUM(CASE WHEN type = 0 THEN MF.size * 8 / 1024 ELSE 0 END) AS DataFileSizeMB,
            SUM(CASE WHEN type = 1 THEN MF.size * 8 / 1024 ELSE 0 END) AS LogFileSizeMB
    FROM
            sys.master_files MF
            JOIN sys.databases DB ON DB.database_id = MF.database_id
    GROUP BY DB.name
    ORDER BY DataFileSizeMB DESC

    Get size of all tables in a database

    SELECT
            T.NAME AS TableName,
            S.Name AS SchemaName,
            P.rows AS RowCounts,
            SUM(AU.total_pages) * 8 / 1024 AS TotalSpaceMB,
            SUM(AU.used_pages) * 8 / 1024 AS UsedSpaceMB
    FROM
            syS.tables T
            JOIN sys.indexes I ON T.object_id = I.object_id
            JOIN sys.partitions P ON I.object_id = P.object_id AND I.index_id = P.index_id
            JOIN sys.allocation_units AU ON P.partition_id = AU.container_id
            JOIN sys.schemas S ON T.schema_id = S.schema_id
    GROUP BY
            T.Name, S.Name, P.Rows
    ORDER BY
            TotalSpaceMB DESC

    Find out the disk size of an index

    SELECT
            OBJECT_NAME(I.OBJECT_ID) AS table_name,
            I.name AS index_name,  
            I.type_desc,
            AU.used_pages,
        8 * SUM(AU.used_pages) AS 'index_size_KB',         
        CAST(8 * SUM(AU.used_pages) / 1024.0 AS DECIMAL(18,2)) AS 'Index size (MB)'
    FROM
        sys.indexes I
            JOIN sys.partitions P ON P.OBJECT_ID = I.OBJECT_ID AND P.index_id = I.index_id
            JOIN sys.allocation_units AU ON AU.container_id = P.partition_id
    --WHERE
    --      OBJECT_NAME(I.OBJECT_ID) = '<tableName>'        
    GROUP BY
        I.object_id, I.name, I.type_desc, AU.used_pages
    ORDER BY
        table_name;

    Pages

    Subscribe to RSS - sql-server-2012