June 2018 – Welcome to our SQL World!

Recent Full Backups

This query returns information on the recent full backups taken for the current database. This query helps you to find out the backup sizes, backup timings, backup location etc.

SQL Query

SELECT TOP (30) bs.machine_name, bs.server_name, bs.database_name AS [Database Name], bs.recovery_model,
CONVERT (BIGINT, bs.backup_size / 1048576 ) AS [Uncompressed Backup Size (MB)],
CONVERT (BIGINT, bs.compressed_backup_size / 1048576 ) AS [Compressed Backup Size (MB)],
CONVERT (NUMERIC (20,2), (CONVERT (FLOAT, bs.backup_size) /
CONVERT (FLOAT, bs.compressed_backup_size))) AS [Compression Ratio], bs.has_backup_checksums, bs.is_copy_only, bs.encryptor_type,
DATEDIFF (SECOND, bs.backup_start_date, bs.backup_finish_date) AS [Backup Elapsed Time (sec)],
bs.backup_finish_date AS [Backup Finish Date], bmf.physical_device_name AS [Backup Location], bmf.physical_block_size
FROM msdb.dbo.backupset AS bs WITH (NOLOCK)
INNER JOIN msdb.dbo.backupmediafamily AS bmf WITH (NOLOCK)
ON bs.media_set_id = bmf.media_set_id 
WHERE bs.database_name = DB_NAME(DB_ID())
AND bs.[type] = 'D' -- Change to L if you want Log backups
ORDER BY bs.backup_finish_date DESC OPTION (RECOMPILE);

Output

The following query returns the Last Backup Date Time for each database on your Instance.

SQL Query

SELECT
    db.Name AS DatabaseName,
    COALESCE(CONVERT(VARCHAR(19), MAX(bs.backup_finish_date), 120),'None') AS LastBackUpDateTime
FROM
    sys.sysdatabases db
    LEFT OUTER JOIN msdb.dbo.backupset bs
    ON bs.database_name = db.name
GROUP BY
    db.Name;

Output

The following query returns the databases that have never had a backup, or the current backup is over 24 hours old.

SQL Query

SELECT
    db.Name AS DatabaseName,
    COALESCE(CONVERT(VARCHAR(19), MAX(bs.backup_finish_date), 120),'None') AS LastBackUpTime
FROM
    sys.sysdatabases db
    LEFT OUTER JOIN msdb.dbo.backupset bs
    ON bs.database_name = db.name
GROUP BY
    db.Name
HAVING
    MAX(bs.backup_finish_date) < DATEADD(dd,-1,GETDATE())
    OR MAX(bs.backup_finish_date) IS NULL;

Output

By reviewing the output from these scripts, you will be able to identify the last backup for the databases on your instance.

References

https://technet.microsoft.com/en-us/library/ms186299(v=sql.110).aspx

https://technet.microsoft.com/en-us/library/ms190284(v=sql.110).aspx

Helpful Queries for Administration

Capture the Most Expensive Queries Using Query Store

Generally we generate queries based on the dynamic management view sys.dm_exec_query_stats to capture the most resource hungry queries across a SQL instance. That approach has some drawbacks though. First, it is cleared out every time an instance restarts, and second it only keeps figures for currently cached plans, so when a query recompiles, data is lost.

Alternatively we can use SQL Server Query Store option for this purpose. One of the great features of it is that performance stats are stored in the database, so they aren’t lost in either of the above scenarios.

This query will produce a ranked list of your most expensive queries for the current database. The query requires that Query Store is enabled for the database.

SQL Query

SELECT
                DB_NAME() AS DatabaseName,
                s.name AS SchemaName,
                o.name AS ObjectName,
                SUBSTRING(t.query_sql_text,1,1000) AS QueryText,
                SUM(rs.count_executions) AS TotalExecutions,
                SUM(rs.avg_duration * rs.count_executions) AS TotalDuration,
                SUM(rs.avg_cpu_time * rs.count_executions) AS TotalCPU,
                SUM(rs.avg_logical_io_reads * rs.count_executions) AS TotalLogicalReads
          FROM sys.query_store_query q
          INNER JOIN sys.query_store_query_text t
                ON q.query_text_id = t.query_text_id
          INNER JOIN sys.query_store_plan p
                ON q.query_id = p.query_id
          INNER JOIN sys.query_store_runtime_stats rs
                ON p.plan_id = rs.plan_id
          INNER JOIN sys.query_store_runtime_stats_interval rsi
                ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
          LEFT JOIN sys.objects o
                ON q.OBJECT_ID = o.OBJECT_ID
          LEFT JOIN sys.schemas s
                ON o.schema_id = s.schema_id    
          WHERE rsi.start_time >= DATEADD(hour, -1, GETUTCDATE())
          GROUP BY s.name, o.name, SUBSTRING(t.query_sql_text,1,1000)
          OPTION(RECOMPILE)

Output

References

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-stats-transact-sql

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-query-store-query-text-transact-sql

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-query-store-query-transact-sql

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-query-store-plan-transact-sql

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-query-store-runtime-stats-transact-sql

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-query-store-runtime-stats-interval-transact-sql

Helpful Queries - General

Query Store Options

This query returns Query Store Options for the current database.

SQL Query

SELECT actual_state_desc, desired_state_desc,
       current_storage_size_mb, [max_storage_size_mb],
          query_capture_mode_desc, size_based_cleanup_mode_desc
FROM sys.database_query_store_options WITH (NOLOCK) OPTION (RECOMPILE);

Output

References

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-query-store-options-transact-sql

Helpful Queries for Troubleshooting

Statistics Update Information

This query provides details on when were the Statistics last updated on all indexes for the current database. This query helps discover possible problems with out-of-date statistics and also gives you an idea which indexes are the most active.

SQL Query

SELECT SCHEMA_NAME(o.Schema_ID) + N'.' + o.[NAME] AS [Object Name], o.[type_desc] AS [Object Type],
      i.[name] AS [Index Name], STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date],
      s.auto_created, s.no_recompute, s.user_created, s.is_incremental, s.is_temporary,
         st.row_count, st.used_page_count
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON o.[object_id] = i.[object_id]
INNER JOIN sys.stats AS s WITH (NOLOCK)
ON i.[object_id] = s.[object_id]
AND i.index_id = s.stats_id
INNER JOIN sys.dm_db_partition_stats AS st WITH (NOLOCK)
ON o.[object_id] = st.[object_id]
AND i.[index_id] = st.[index_id]
WHERE o.[type] IN ('U', 'V')
AND st.row_count > 0
ORDER BY STATS_DATE(i.[object_id], i.index_id) DESC OPTION (RECOMPILE);