SQL DBA Script All In One

Ultimate SQL Database Administration Toolkit

SQL Server Performance Tuning Scripts

There are various scripts that can be used for performance tuning in SQL Server. However, please keep in mind that executing scripts on a production database can have serious consequences and should be done carefully and preferably under the supervision of a DBA (Database Administrator). Below are some general scripts that are commonly used in performance tuning. Remember to thoroughly understand and test them in a development environment before using them in production.

Find Long Running Queries:

				
					SELECT
    TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2) + 1,
    ((CASE statement_end_offset
        WHEN -1 THEN DATALENGTH(qt.TEXT)
        ELSE qs.statement_end_offset
        END - qs.statement_start_offset)/2) + 1),
    qs.execution_count,
    qs.total_logical_reads, qs.last_logical_reads,
    qs.total_logical_writes, qs.last_logical_writes,
    qs.total_worker_time,
    qs.last_worker_time,
    qs.total_elapsed_time/1000000 total_elapsed_time_in_sec,
    qs.last_elapsed_time/1000000 last_elapsed_time_in_sec,
    qs.last_execution_time,
    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
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

Check Index Fragmentation:

				
					SELECT
    dbschemas.[name] as 'Schema',
    dbtables.[name] as 'Table',
    dbindexes.[name] as 'Index',
    indexstats.avg_fragmentation_in_percent,
    indexstats.page_count
FROM
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
    INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
    INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
    INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
    AND indexstats.index_id = dbindexes.index_id
WHERE
    indexstats.database_id = DB_ID()
ORDER BY
    indexstats.avg_fragmentation_in_percent DESC

Check for Blocked Processes:

				
					SELECT
    blocking_session_id AS BlockingSessionID,
    session_id AS VictimSessionID,
    (SELECT text FROM sys.dm_exec_sql_text(sql_handle)) AS SQLQuery
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0

SQL Server Wait Statistics:

				
					WITH Waits AS
(SELECT
    wait_type,
    wait_time_ms / 1000. AS wait_time_s,
    100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
    ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
 FROM sys.dm_os_wait_stats
 WHERE wait_type NOT IN (
    'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
    'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',
    'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',
    'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',
    'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
)
SELECT
    W1.wait_type,
    CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
    CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
    CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
    INNER JOIN Waits AS W2 ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold

Find Missing Indexes:

				
					SELECT
    dm_mid.database_id,
    dm_mid.[object_id],
    dm_migs.avg_total_user_cost * (dm_migs.avg_user_impact / 100.0) * (dm_migs.user_seeks + dm_migs.user_scans) AS improvement_measure,
    'CREATE INDEX missing_index_' + CONVERT (varchar, dm_mid.index_group_handle) + '_' + CONVERT (varchar, dm_mid.index_handle) + ' ON ' + dm_mid.statement + ' (' + ISNULL (dm_mic.column_store, '') + ')' + ISNULL (dm_mic.equality_columns,'')
    + CASE WHEN dm_mic.equality_columns IS NOT NULL AND dm_mic.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL (dm_mic.inequality_columns, '') + ')' + ISNULL (' INCLUDE (' + dm_mic.included_columns + ')', '') AS create_index_statement,
    dm_migs.*,
    dm_mid.database_id,
    dm_mid.[object_id]
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
    INNER JOIN (
        SELECT
            index_handle,
            STRING_AGG(column_name, ', ') WITHIN GROUP (ORDER BY column_name) AS column_store
        FROM sys.dm_db_missing_index_columns (NULL)
        WHERE column_usage = 'STORE'
        GROUP BY index_handle
    ) AS dm_mic_store ON dm_mid.index_handle = dm_mic_store.index_handle
    LEFT JOIN (
        SELECT
            index_handle,
            STRING_AGG(column_name, ', ') WITHIN GROUP (ORDER BY column_name) AS equality_columns,
            STRING_AGG(column_name, ', ') WITHIN GROUP (ORDER BY column_name) AS inequality_columns,
            STRING_AGG(column_name, ', ') WITHIN GROUP (ORDER BY column_name) AS included_columns
        FROM sys.dm_db_missing_index_columns (NULL)
        WHERE column_usage = 'EQUALITY' OR column_usage = 'INEQUALITY' OR column_usage = 'INCLUDE'
        GROUP BY index_handle
    ) AS dm_mic ON dm_mid.index_handle = dm_mic.index_handle
WHERE
    dm_mid.database_id = DB_ID()
ORDER BY
    improvement_measure DESC

**These scripts provide insights into various aspects of SQL Server performance, such as identifying long-running queries, indexing opportunities, blocking sessions, wait statistics, and more. However, these scripts should be used with caution, especially in production environments, as poorly executed queries or changes can negatively impact performance or cause data loss. Always make sure you have backups and understand what the scripts are doing before executing them. Additionally, note that theremight be some syntax differences if the SQL Server version you’re using is different than when these scripts were written. Keep your SQL Server version in mind and make adjustments as necessary. Moreover, performance tuning is not just about executing scripts; it often involves an understanding of the underlying data and workload. Consider involving an experienced Database Administrator (DBA) for critical systems.

Monitor CPU Utilization:

				
					SELECT
    SQLProcessUtilization AS [SQL Server Process CPU Utilization],
    SystemIdle AS [System Idle Process],
    100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization]
FROM
    (SELECT
         record_id,
         dateadd(ms, -1 * ((sys.ms_ticks / 1000) - [timestamp]), GetDate()) as [Event Time],
         SQLProcessUtilization,
         SystemIdle
     FROM
         (SELECT
              record_id,
              sys.ms_ticks,
              [timestamp],
              convert(xml, record) as [record]
          FROM sys.dm_os_ring_buffers
          CROSS JOIN sys.dm_os_sys_info sys
          WHERE ring_buffer_id = 1
          AND record_id > (SELECT MAX(record_id) FROM sys.dm_os_ring_buffers WHERE ring_buffer_id = 1) - 60
         ) AS x
     ) AS y
ORDER BY record_id DESC;

Buffer Cache Hit Ratio:

This script calculates the buffer cache hit ratio, which can help determine if your SQL Server instance has enough memory.

				
					SELECT
    CAST((COUNT(*) * 100) AS DECIMAL(5,2)) AS BufferCacheHitRatio
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Buffer Manager'
    AND counter_name = 'Buffer cache hit ratio'

Page Life Expectancy:

This script shows you how long, on average, pages are staying in the buffer pool. A lower value might indicate that you don’t have enough memory.

				
					SELECT
    [object_name],
    counter_name,
    cntr_value
FROM
    sys.dm_os_performance_counters
WHERE
    [object_name] LIKE '%Manager%'
    AND counter_name = 'Page life expectancy'

Database Size and Free Space:

It’s also good to monitor your database size and the space used.

				
					EXEC sp_MSforeachdb N'USE [?];
    SELECT
        DB_NAME() AS [DatabaseName],
        file_id,
        type_desc AS [FileType],
        name AS [LogicalName],
        Physical_Name AS [PhysicalName],
        (size * 8.0 / 1024) AS [SizeMB],
        (FILEPROPERTY(name, ''SpaceUsed'') * 8.0 / 1024) AS [SpaceUsedMB],
        ((size - FILEPROPERTY(name, ''SpaceUsed'')) * 8.0 / 1024) AS [FreeSpaceMB]
    FROM sys.master_files
    WHERE
        DB_NAME(database_id) = DB_NAME()
    ORDER BY
        type, file_id;
    '

**Please remember to use these scripts with caution. Analyzing and tuning a database’s performance can be complex and requires a good understanding of the specific environment and workload. Always make sure you have backups, and understand what the scripts are doing before executing them in a production environment. If necessary, consult a database administrator.

Identifying Expensive Queries:

This script helps identify the most expensive queries which consume the most I/O.

				
					SELECT
    TOP 5 total_logical_reads,
    total_logical_writes,
    total_physical_reads,
    execution_count,
    total_logical_reads + total_logical_writes AS [Aggregated I/O],
    total_elapsed_time,
    statement_start_offset,
    statement_end_offset,
    plan_handle,
    sql_handle
FROM
    sys.dm_exec_requests
ORDER BY
    [Aggregated I/O] DESC;

Finding Deadlocks:

This script will help you find deadlocks if any have occurred.

				
					SELECT
    XEvent.query('(data/value/deadlock)[1]') AS DeadlockGraph
FROM
    (SELECT
         XEvent.query('.') AS XEvent
     FROM
         (SELECT
              CAST(target_data AS XML) AS TargetData
          FROM
              sys.dm_xe_session_targets st
          JOIN
              sys.dm_xe_sessions s ON s.address = st.event_session_address
          WHERE
              name = 'system_health') AS Data
     CROSS APPLY
         TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
     ) AS src
WHERE
    XEvent.value('(@name)[1]', 'varchar(4000)') = 'xml_deadlock_report';

Disk Usage by Table:

This script returns the disk space used by each table in the current database.

				
					EXEC sp_MSforeachtable N'EXEC sp_spaceused [?]'

Detect SQL Server Performance Condition with Query Wait Statistics:

This script helps to analyze the type of waits affecting your SQL Server Performance.

				
					SELECT
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms
FROM
    sys.dm_os_wait_stats
WHERE
    wait_time_ms > 0
    AND waiting_tasks_count > 0
ORDER BY
    wait_time_ms DESC

Find Last Modified Stored Procedures:

This script helps you to find the last modified stored procedures which can be useful in identifying recent changes that might affect performance.

				
					SELECT
    name AS [Stored Procedure],
    modify_date AS [Last Modified Date]
FROM
    sys.objects
WHERE
    type = 'P'
    AND DATEDIFF(D, modify_date, GETDATE()) < 30 -- Change 30 to the number of days you want to go back
ORDER BY
    modify_date DESC;

**Always remember to use these scripts cautiously, especially on production environments. Understanding the database workload, configuration, and underlying hardware is essential for performance tuning. Additionally, it’s always a good practice to have recent backups before making any changes to the database system. If you are unsure about the effects of these scripts, consulting a database administrator is recommended.

Tempdb Utilization:

This script helps you monitor the utilization of tempdb, which can be useful in performance tuning.

				
					SELECT
    SUM(user_object_reserved_page_count) * 8 as [User Object Space Utilization (KB)],
    SUM(internal_object_reserved_page_count) * 8 as [Internal Object Space Utilization (KB)],
    SUM(version_store_reserved_page_count) * 8 as [Version Store Space Utilization (KB)],
    SUM(unallocated_extent_page_count) * 8 as [Free Space (KB)],
    SUM(mixed_extent_page_count) * 8 as [Mixed Extent Space Utilization (KB)]
FROM
    tempdb.sys.dm_db_file_space_usage;

Check Active Transactions:

This script lists all active transactions in the database. Long-running transactions can sometimes be a cause for performance issues.

				
					SELECT
    transaction_id,
    name,
    transaction_begin_time,
    transaction_type,
    transaction_state
FROM
    sys.dm_tran_active_transactions;

Query Execution Plan Cache:

This script helps in examining the execution plan cache to identify queries that might benefit from optimization.

				
					SELECT
    TOP 10
    qs.plan_generation_num as recompiles,
    qs.execution_count,
    qs.total_elapsed_time/1000000 as total_duration_secs,
    qs.total_worker_time/1000000 as total_cpu_secs,
    qs.total_logical_reads,
    qs.total_logical_writes,
    t.text AS [SQL_Text],
    p.query_plan
FROM
    sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) p
ORDER BY
    qs.total_logical_reads DESC;

Statistics Update Date:

Stale statistics can lead to suboptimal execution plans. This script lists the last updated date for statistics on all tables.

				
					SELECT
    name AS index_name,
    STATS_DATE(object_id, index_id) AS statistics_update_date
FROM
    sys.indexes
WHERE
    object_id = OBJECT_ID('YourTableName');

Check SQL Server Agent Jobs:

Check the status of SQL Server Agent jobs.

				
					EXEC msdb.dbo.sp_help_job;

List All User-Defined Functions and Their Usage:

				
					SELECT
    o.name,
    o.type_desc,
    (SELECT
         COUNT(*)
     FROM
         sys.sql_expression_dependencies sed
     WHERE
         sed.referencing_id = o.object_id) as dependent_object_count
FROM
    sys.objects o
WHERE
    o.type IN ('TF', 'IF', 'FN', 'FS', 'FT')
ORDER BY
    dependent_object_count DESC,
    o.name;

**Please keep in mind that executing these scripts, especially on production databases, should be done with caution. It’s important to understand the implications of the scripts and their potential impact on performance. Always make sure that there are recent backups and, if possible, test these scripts in a development environment before running them in production. Consulting a database administrator is also a good practice if you are unsure about the effects of these scripts.

Identify Blocking Queries:

This script helps you identify which queries are causing blocks.

				
					SELECT
    blocking_session_id AS BlockingSessionID,
    session_id AS VictimSessionID,
    (SELECT text FROM sys.dm_exec_sql_text(sql_handle)) AS VictimQuery
FROM
    sys.dm_exec_requests
WHERE
    blocking_session_id <> 0;

View Lock Information:

This script provides you with details about locks that are currently held by transactions.

				
					SELECT
    resource_type,
    resource_database_id,
    resource_associated_entity_id,
    request_mode,
    request_type,
    request_status,
    request_session_id
FROM
    sys.dm_tran_locks
WHERE
    resource_type <> 'DATABASE';

Find Deadlocks:

This script will help you find deadlocks if any have occurred.

				
					SELECT
    XEvent.query('(data/value/deadlock)[1]') AS DeadlockGraph
FROM
    (SELECT
         XEvent.query('.') AS XEvent
     FROM
         (SELECT
              CAST(target_data AS XML) AS TargetData
          FROM
              sys.dm_xe_session_targets st
          JOIN
              sys.dm_xe_sessions s ON s.address = st.event_session_address
          WHERE
              name = 'system_health') AS Data
     CROSS APPLY
         TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
     ) AS src
WHERE
    XEvent.value('(@name)[1]', 'varchar(4000)') = 'xml_deadlock_report';

Get Details on Blocked Processes:

This script provides details on currently blocked processes which can help in identifying and resolving contention.

				
					SELECT
    blocked_session_id,
    blocking_session_id,
    wait_type,
    wait_duration_ms,
    resource_description,
    (SELECT text FROM sys.dm_exec_sql_text(sql_handle)) AS sql_text
FROM
    sys.dm_os_waiting_tasks
WHERE
    blocking_session_id IS NOT NULL;

Active Transactions and Locks:

This script shows active transactions with their lock information.

				
					SELECT
    at.transaction_id,
    at.name AS transaction_name,
    at.transaction_begin_time,
    at.transaction_state,
    at.transaction_status,
    tl.request_session_id,
    tl.resource_type,
    tl.resource_database_id,
    tl.resource_description,
    tl.request_mode,
    tl.request_status
FROM
    sys.dm_tran_active_transactions at
JOIN
    sys.dm_tran_locks tl ON at.transaction_id = tl.request_owner_id;

Check the Most Recent Backup for Each Database:

This script shows you the most recent backup for each database.

				
					SELECT
    database_name AS [Database],
    MAX(backup_start_date) AS [Last Backup Time],
    DATEDIFF(hour, MAX(backup_start_date), GETDATE()) AS [Hours Since Last Backup],
    backup_size/1024/1024 AS [Backup Size (MB)]
FROM
    msdb.dbo.backupset
GROUP BY
    database_name,
    backup_size
HAVING 
    MAX(backup_start_date) IS NOT NULL
ORDER BY
    MAX(backup_start_date) DESC;

Check the Status of All Backups for a Specific Database:

This script gives you detailed information about all the backups for a specific database.

				
					SELECT
    database_name AS [Database],
    backup_start_date AS [Backup Start],
    backup_finish_date AS [Backup Finish],
    DATEDIFF(second, backup_start_date, backup_finish_date) AS [Duration (s)],
    backup_size/1024/1024 AS [Size (MB)],
    [type] AS [Backup Type]
FROM
    msdb.dbo.backupset
WHERE
    database_name = 'YourDatabaseName' -- Specify your database name here
ORDER BY
    backup_start_date DESC;

Check for Databases with No Recent Backups:

This script helps you identify databases that haven’t been backed up recently.

				
					WITH LastBackup AS (
    SELECT
        database_name,
        MAX(backup_finish_date) AS last_backup
    FROM
        msdb.dbo.backupset
    GROUP BY
        database_name
)
SELECT
    name AS [Database]
FROM
    sys.databases db
LEFT JOIN
    LastBackup lb ON db.name = lb.database_name
WHERE
    db.state = 0 -- Only consider online databases
    AND (
        lb.last_backup IS NULL
        OR DATEDIFF(hour, lb.last_backup, GETDATE()) > 48 -- Consider as not recent if more than 48 hours old
    )
ORDER BY
    db.name;

**Remember that it’s not just important to create backups, but also to ensure that they are restorable and valid. Regularly practice restoring your backups to a test environment to make sure they’re good

Maintaining indexes is an important aspect of database performance tuning. Below are some scripts that can be used for index maintenance in SQL Server:

Identify Fragmented Indexes:

This script helps you identify fragmented indexes that might need maintenance.

				
					SELECT
    dbschemas.[name] as 'Schema',
    dbtables.[name] as 'Table',
    dbindexes.[name] as 'Index',
    indexstats.avg_fragmentation_in_percent,
    indexstats.page_count
FROM
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN
    sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN
    sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN
    sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
    AND indexstats.index_id = dbindexes.index_id
WHERE
    indexstats.database_id = DB_ID()
ORDER BY
    indexstats.avg_fragmentation_in_percent desc;

Rebuild an Index:

This script rebuilds an index. You should do this during periods of low database activity as it can be resource-intensive.

				
					ALTER INDEX [YourIndexName] ON [YourSchema].[YourTableName]
REBUILD;

Reorganize an Index:

This script reorganizes a mildly fragmented index. This is usually less resource-intensive compared to rebuilding an index.

				
					ALTER INDEX [YourIndexName] ON [YourSchema].[YourTableName]
REORGANIZE;

Update Statistics:

This script updates the statistics of an index.

				
					UPDATE STATISTICS [YourSchema].[YourTableName] [YourIndexName]

Rebuild All Indexes in a Database:

This script rebuilds all indexes in a database. Be cautious when using this on a production database as it can be very resource-intensive.

				
					EXEC sp_MSForEachTable 'ALTER INDEX ALL ON ? REBUILD'

Conditional Rebuild or Reorganize Based on Fragmentation Levels:

This script is for more advanced maintenance, where you might want to rebuild, reorganize, or ignore indexes based on their fragmentation levels.

				
					DECLARE @TableName NVARCHAR(255)
DECLARE @SchemaName NVARCHAR(255)
DECLARE @IndexName NVARCHAR(255)
DECLARE @FragPercent DECIMAL

DECLARE IndexCursor CURSOR FOR
    SELECT
        OBJECT_SCHEMA_NAME(ips.[object_id]) as SchemaName,
        OBJECT_NAME(ips.[object_id]) as TableName,
        si.name as IndexName,
        ips.avg_fragmentation_in_percent
    FROM
        sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
    JOIN
        sys.indexes si ON ips.[object_id] = si.[object_id]
        AND ips.index_id = si.index_id
    WHERE
        ips.avg_fragmentation_in_percent > 10 -- Consider indexes with more than 10% fragmentation
    ORDER BY
        ips.avg_fragmentation_in_percent DESC

OPEN IndexCursor

FETCH NEXT FROM IndexCursor INTO @SchemaName, @TableName, @IndexName, @FragPercent

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @FragPercent > 30
    BEGIN
        EXEC('ALTER INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + '] REBUILD')
    END
    ELSE
    BEGIN
        EXEC('ALTER INDEX [' + @IndexName + '] ON[' + @SchemaName + '].[' + @TableName + '] REORGANIZE')
    END

    FETCH NEXT FROM IndexCursor INTO @SchemaName, @TableName, @IndexName, @FragPercent
END

CLOSE IndexCursor
DEALLOCATE IndexCursor

Disable and Rebuild a Non-clustered Index:

This script shows how to disable and then rebuild a non-clustered index.

				
					-- Disable the index
ALTER INDEX [YourIndexName] ON [YourSchema].[YourTableName] DISABLE

-- Rebuild the index
ALTER INDEX [YourIndexName] ON [YourSchema].[YourTableName] REBUILD

List Partition Functions:

This script lists all the partition functions in the current database.

				
					SELECT
    name AS PartitionFunction,
    type_desc,
    fanout,
    create_date,
    modify_date
FROM
    sys.partition_functions;

List Partition Schemes:

This script lists all the partition schemes in the current database.

				
					SELECT
    name AS PartitionScheme,
    type_desc,
    fanout,
    create_date,
    modify_date
FROM
    sys.partition_schemes;

Check Partitioned Tables and Indexes:

This script checks the partitioned tables and their indexes in the current database.

				
					SELECT
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    p.partition_number,
    fg.name AS FileGroupName,
    p.rows,
    au.total_pages AS TotalPages
FROM
    sys.partitions p
JOIN
    sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
JOIN
    sys.allocation_units au ON p.hobt_id = au.container_id
JOIN
    sys.filegroups fg ON au.data_space_id = fg.data_space_id
WHERE
    p.data_compression > 0
ORDER BY
    OBJECT_NAME(i.object_id),
    p.partition_number;

Check Partition Ranges and Rows:

This script shows the ranges of each partition and the number of rows in each.

				
					SELECT
    OBJECT_NAME(p.object_id) AS TableName,
    i.name AS IndexName,
    p.partition_number,
    pr.value AS BoundaryValue,
    p.rows AS [RowCount]
FROM
    sys.partitions p
JOIN
    sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
LEFT JOIN
    sys.partition_range_values pr ON p.partition_number = pr.boundary_id AND pr.function_id = i.data_space_id
WHERE
    OBJECT_NAME(p.object_id) = 'YourTableName' -- Specify the table name here
ORDER BY
    p.partition_number;

**Remember to be cautious when working with partitions especially in a production environment. Consult a database administrator if you’re unsure about the effects of your actions. It’s always advisable to perform operations on a development environment first and have recent backups available.