Execute a SSIS package using T-SQL

DECLARE @execution_id BIGINT;
DECLARE @use32bitruntime BIT = CAST(0 AS BIT);

-- A new execution operation is created.
EXEC catalog.create_execution
@folder_name = N'<FolderName>',
@project_name = N'<ProjectName>',
@package_name = N'<PackageName.dtsx>',
@use32bitruntime = @use32bitruntime,
@reference_id = NULL,
@execution_id = @execution_id OUTPUT;

-- Set execution properties
EXEC catalog.set_execution_parameter_value
@object_type = 50,
@parameter_name = N'LOGGING_LEVEL',
@parameter_value = 1;

-- Execution is started asynchronous
EXEC catalog.start_execution @execution_id;

Support for simplified pagination using OFFSET-FETCH filter

Starting with SQL Server 2012.

SELECT orderId, orderDate, customerId, empId
FROM sales.orders
ORDER BY orderDate

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.

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)

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

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


  • 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.
  • Index design recommendation - how to choose optimal indexes

    Indexes should be considered on all columns that are frequently accessed by the WHERE, ORDER BY, GROUP BY, TOP, and DISTINCT clauses.

    Clustered index recommendations
    - It is highly recommended that every table should have at least a clustered index. However, there is also a very interesting topic to be taken into account on use-the-index-luke.com.
    - Consider having a clustered indexes when the data retrieval needs to be sorted.
    - Avoid creating a clustered index on columns that are highly updatable as row locator of all the non clustered indexes will be updated accordingly.
    - Create the clustered index first. In case the clustered index is created later => all non clustered indexes need to be rebuilt.
    - Keep clustered indexes narrow as this directly impacts index size on disk. This is very important as all non clustered indexes store the clustered keys as their row locator, or simpler said:
    clustered index row width = non clustered index column width + clustered index column width

    Nonclustered Index Recommendations
    - A nonclustered index is most useful when all you want to do is retrieve a small number of rows and columns from a large table.
    - Consider using INCLUDE clause in order to create covering indexes.
    - To improve the performance of a query, SQL Server can use multiple indexes on a table. Therefore, instead of creating wide index keys, consider creating multiple narrow indexes.
    - Foreign keys columns are good index candidates.

    Disk consideration
    - Place the table and index on separate disks.
    - Take into account index compression i.e. fewer pages and fewer index levels are needed to store the index.

    Column order matters in a composite index
    Using the most selective column first will help filter the index rows more efficiently.

    Avoid indexes on columns with lower selectivity
    If selectivity ratio > 0.85 => a table scan will be preferred.

    Take into account column uniqueness
    Creating an index on columns with a very low range of possible unique values (such as gender) will not benefit performance, because the query optimizer will not be able to use the index to effectively narrow down the rows to be returned.

    The data type of an index matters
    Unless they are absolutely necessary, minimize the use of wide data type columns with large sizes in an index. A large index key size increases the number of index pages, thereby increasing the amount of memory and disk activities required for the index.

    Indexed or materialized views
    - A database view can be materialized on the disk by creating a unique clustered index on the view. After a unique clustered index is created on the view, the view’s result set is materialized immediately and persisted in physical storage in the database, saving the overhead of performing costly operations during query execution.
    - Aggregations can be precomputed and persisted in the indexed view to minimize expensive computations during query execution.
    - Available only in Enterprise Edition.

    Other aspects to be taken into account
    - Considering using the Database Engine Tuning Advisor tool provided by SQL Server that helps you determine the correct indexes in a database for a given SQL workload ( a trace file or a table or, new with SQL Server 2012, you can use the queries that exist in the plan cache).
    - ColumnStore indexes. Used to index information by columns rather than by rows.
    - Spatial indexes.
    - XML indexes.
    - Index computed columns.
    - Consider using filtered indexes.
    - Resolve Key or RID lookups by using a clustered index or by using a covering index or by using a index join (i.e. an index intersection between two or more indexes to fully cover a query).

    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.


            @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
            sys.sql_modules M
            INNER JOIN sys.objects O ON O.object_id = M.object_id
            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)
            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
                            IsRefreshed = 0,
                            Error = ERROR_MESSAGE()
                    WHERE RowIndex = @RowIndex
                    IF @@TRANCOUNT > 0
                            ROLLBACK TRAN
            END CATCH
            -- Process next object
            SET @RowIndex = @RowIndex + 1

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

    Get list of replicated tables

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

    USE master;

            S.publisher_db, S.subscriber_db,
            A.source_owner, A.source_object, A.destination_owner, A.destination_object
            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
            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:

            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:

            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

            MF.type_desc AS FileType,
            MF.size * 8 / 1024 AS FileSizeMB,
            fileproperty(MF.name, 'SpaceUsed') * 8/ 1024 AS UsedSpaceMB
            sys.master_files MF
            JOIN sys.databases DB ON DB.database_id = MF.database_id
            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;

            @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
            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
    -- ============================================
    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
    -- ============================================
            @LogicalDataFileName as NVARCHAR(MAX),
            @LogicalLogFileName as NVARCHAR(MAX),
            @TargetDataFilePath as NVARCHAR(MAX),
            @TargetLogFilePath as NVARCHAR(MAX)
            @LogicalDataFileName = name,
            @TargetDataFilePath = SUBSTRING(physical_name,1,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))) + '\' + @TargetDatabaseName + '.mdf'
    FROM sys.master_files
            database_id = DB_ID(@SourceDatabaseName)       
            AND type = 0            -- datafile file

            @LogicalLogFileName = name,
            @TargetLogFilePath = SUBSTRING(physical_name,1,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))) + '\' + @TargetDatabaseName + '.ldf'
    FROM sys.master_files
            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 = '
    '@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:

            BMF.physical_device_name as BackupFileName     
            , B.backup_finish_date as BackupDate
            , B.backup_size/1024.0/1024.0 AS BackupSizeMB
            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


    Subscribe to SQLhint RSS