DMVs most used

alerts.sql

-- Get SQL Server Agent Alert Information (Query 5) (SQL Server Agent Alerts)
SELECT      name
          , event_source
          , message_id
          , severity
          , [enabled]
          , has_notification
          , delay_between_responses
          , occurrence_count
          , last_occurrence_date
          , last_occurrence_time
 FROM       msdb.dbo.sysalerts WITH (NOLOCK)
 ORDER BY   name
OPTION (RECOMPILE);

-- Gives you some basic information about your SQL Server Agent Alerts (which are different from SQL Server Agent jobs)
-- Read more about Agent Alerts here: http://www.sqlskills.com/blogs/glenn/creating-sql-server-agent-alerts-for-critical-errors/

audit.sql

--  Get logins that are connected and how many sessions they have (Query 34) (Connection Counts)
SELECT      login_name
          , [program_name]
          , COUNT(session_id) AS [session_count]
 FROM       sys.dm_exec_sessions WITH (NOLOCK)
 GROUP BY   login_name
          , [program_name]
 ORDER BY   COUNT(session_id) DESC
OPTION (RECOMPILE);

-- This can help characterize your workload and
-- determine whether you are seeing a normal level of activity


-- Get a count of SQL connections by IP address (Query 35) (Connection Counts by IP Address)
SELECT      ec.client_net_address
          , es.[program_name]
          , es.[host_name]
          , es.login_name
          , COUNT(ec.session_id) AS [connection count]
 FROM       sys.dm_exec_sessions AS es WITH (NOLOCK)
 INNER JOIN sys.dm_exec_connections AS ec WITH (NOLOCK) ON es.session_id = ec.session_id
 GROUP BY   ec.client_net_address
          , es.[program_name]
          , es.[host_name]
          , es.login_name
 ORDER BY   ec.client_net_address
          , es.[program_name]
OPTION (RECOMPILE);





-- Find single-use, ad-hoc and prepared queries that are bloating the plan cache  (Query 43) (Ad hoc Queries)
SELECT TOP (50)
            [text] AS [QueryText]
          , cp.cacheobjtype
          , cp.objtype
          , cp.size_in_bytes
 FROM       sys.dm_exec_cached_plans AS cp WITH (NOLOCK)
 CROSS APPLY sys.dm_exec_sql_text(plan_handle)
 WHERE      cp.cacheobjtype = N'Compiled Plan' AND  cp.objtype IN (N'Adhoc', N'Prepared') AND   cp.usecounts = 1
 ORDER BY   cp.size_in_bytes DESC
OPTION (RECOMPILE);

-- Gives you the text, type and size of single-use ad-hoc and prepared queries that waste space in the plan cache
-- Enabling 'optimize for ad hoc workloads' for the instance can help (SQL Server 2008 and above only)
-- Running DBCC FREESYSTEMCACHE ('SQL Plans') periodically may be required to better control this.
-- Enabling forced parameterization for the database can help, but test first!

cluster.sql

-- Get information about your OS cluster (if your database server is in a cluster)  (Query 14) (Cluster Properties)
SELECT  VerboseLogging
      , SqlDumperDumpFlags
      , SqlDumperDumpPath
      , SqlDumperDumpTimeOut
      , FailureConditionLevel
      , HealthCheckTimeout
 FROM   sys.dm_os_cluster_properties WITH (NOLOCK)
OPTION (RECOMPILE);

-- You will see no results if your instance is not clustered


-- Get information about your cluster nodes and their status  (Query 15) (Cluster Node Properties)
-- (if your database server is in a failover cluster)
SELECT  NodeName, status_description, is_current_owner FROM sys.dm_os_cluster_nodes WITH (NOLOCK)
OPTION (RECOMPILE);

-- Knowing which node owns the cluster resources is critical
-- Especially when you are installing Windows or SQL Server updates
-- You will see no results if your instance is not clustered


-- Get information about any AlwaysOn AG cluster this instance is a part of (Query 16) (AlwaysOn AG Cluster)
SELECT  cluster_name, quorum_type_desc, quorum_state_desc FROM  sys.dm_hadr_cluster WITH (NOLOCK)
OPTION (RECOMPILE);

-- You will see no results if your instance is not using AlwaysOn AGs

cpu.sql

-- Get CPU utilization by database (Query 29) (CPU Usage by Database)
WITH DB_CPU_Stats
AS (SELECT      DatabaseID
              , DB_NAME(DatabaseID) AS [Database Name]
              , SUM(total_worker_time) AS [CPU_Time_Ms]
     FROM       sys.dm_exec_query_stats AS qs
     CROSS APPLY
         (SELECT    CONVERT(INT, value) AS [DatabaseID]
           FROM     sys.dm_exec_plan_attributes(qs.plan_handle)
           WHERE    attribute = N'dbid') AS F_DB
     GROUP BY   DatabaseID)
 SELECT     ROW_NUMBER() OVER (ORDER BY [CPU_Time_Ms] DESC) AS [CPU Rank]
          , [Database Name]
          , [CPU_Time_Ms] AS [CPU Time (ms)]
          , CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER () * 100.0 AS DECIMAL(5, 2)) AS [CPU Percent]
  FROM      DB_CPU_Stats
  WHERE     DatabaseID <> 32767 -- ResourceDB
  ORDER BY  [CPU Rank]
OPTION (RECOMPILE);

-- Helps determine which database is using the most CPU resources on the instance


-- Get CPU Utilization History for last 256 minutes (in one minute intervals)  (Query 37) (CPU Utilization History)
-- This version works with SQL Server 2014
DECLARE @ts_now BIGINT = (SELECT    cpu_ticks / (cpu_ticks / ms_ticks) FROM sys.dm_os_sys_info WITH (NOLOCK));

SELECT TOP (256)
            SQLProcessUtilization AS [SQL Server Process CPU Utilization]
          , SystemIdle AS [System Idle Process]
          , 100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization]
          , DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
 FROM
            (SELECT record.value('(./Record/@id)[1]', 'int') AS record_id
                  , record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle]
                  , record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcessUtilization]
                  , [timestamp]
              FROM
                    (SELECT [timestamp]
                          , CONVERT(XML, record) AS [record]
                      FROM  sys.dm_os_ring_buffers WITH (NOLOCK)
                      WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' AND record LIKE N'%<SystemHealth>%') AS x ) AS y
 ORDER BY   record_id DESC
OPTION (RECOMPILE);

-- Look at the trend over the entire period 
-- Also look at high sustained Other Process CPU Utilization values


-- Get Average Task Counts (run multiple times)  (Query 36) (Avg Task Counts)
SELECT  AVG(current_tasks_count) AS [Avg Task Count]
      , AVG(runnable_tasks_count) AS [Avg Runnable Task Count]
      , AVG(pending_disk_io_count) AS [Avg Pending DiskIO Count]
 FROM   sys.dm_os_schedulers WITH (NOLOCK)
 WHERE  scheduler_id < 255
OPTION (RECOMPILE);

-- Sustained values above 10 suggest further investigation in that area
-- High Avg Task Counts are often caused by blocking or other resource contention

-- Sustained values above 1 suggest further investigation in that area
-- High Avg Runnable Task Counts are a good sign of CPU pressure
-- High Avg Pending DiskIO Counts are a sign of disk pressure

index.sql

-- Missing Indexes for all databases by Index Advantage  (Query 27) (Missing Indexes All Databases)
SELECT      CONVERT(DECIMAL(18, 2), user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)) AS [index_advantage]
          , migs.last_user_seek
          , mid.[statement] AS [Database.Schema.Table]
          , mid.equality_columns
          , mid.inequality_columns
          , mid.included_columns
          , migs.unique_compiles
          , migs.user_seeks
          , migs.avg_total_user_cost
          , migs.avg_user_impact
 FROM       sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
 INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK) ON migs.group_handle = mig.index_group_handle
 INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK) ON mig.index_handle = mid.index_handle
 ORDER BY   index_advantage DESC
OPTION (RECOMPILE);

-- Getting missing index information for all of the databases on the instance is very useful
-- Look at last user seek time, number of user seeks to help determine source and importance
-- Also look at avg_user_impact and avg_total_user_cost to help determine importance
-- SQL Server is overly eager to add included columns, so beware
-- Do not just blindly add indexes that show up from this query!!!


-- Possible Bad NC Indexes (writes > reads)  (Query 55) (Bad NC Indexes)
SELECT      OBJECT_NAME(s.[object_id]) AS [Table Name]
          , i.name AS [Index Name]
          , i.index_id
          , i.is_disabled
          , i.is_hypothetical
          , i.has_filter
          , i.fill_factor
          , user_updates AS [Total Writes]
          , user_seeks + user_scans + user_lookups AS [Total Reads]
          , user_updates - (user_seeks + user_scans + user_lookups) AS [Difference]
 FROM       sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
 INNER JOIN sys.indexes AS i WITH (NOLOCK) ON s.[object_id] = i.[object_id] AND i.index_id = s.index_id
 WHERE      OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1 AND s.database_id = DB_ID() AND user_updates > (user_seeks + user_scans + user_lookups)
            AND i.index_id > 1
 ORDER BY   [Difference] DESC
          , [Total Writes] DESC
          , [Total Reads] ASC
OPTION (RECOMPILE);

-- Look for indexes with high numbers of writes and zero or very low numbers of reads
-- Consider your complete workload, and how long your instance has been running
-- Investigate further before dropping an index!


-- Missing Indexes for current database by Index Advantage  (Query 56) (Missing Indexes)
SELECT DISTINCT
            CONVERT(DECIMAL(18, 2), user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)) AS [index_advantage]
          , migs.last_user_seek
          , mid.[statement] AS [Database.Schema.Table]
          , mid.equality_columns
          , mid.inequality_columns
          , mid.included_columns
          , migs.unique_compiles
          , migs.user_seeks
          , migs.avg_total_user_cost
          , migs.avg_user_impact
          , OBJECT_NAME(mid.[object_id]) AS [Table Name]
          , p.rows AS [Table Rows]
 FROM       sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
 INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK) ON migs.group_handle = mig.index_group_handle
 INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK) ON mig.index_handle = mid.index_handle
 INNER JOIN sys.partitions AS p WITH (NOLOCK) ON p.[object_id] = mid.[object_id]
 WHERE      mid.database_id = DB_ID()
 ORDER BY   index_advantage DESC
OPTION (RECOMPILE);

-- Look at last user seek time, number of user seeks to help determine source and importance
-- SQL Server is overly eager to add included columns, so beware
-- Do not just blindly add indexes that show up from this query!!!


-- Find missing index warnings for cached plans in the current database  (Query 57) (Missing Index Warnings)
-- Note: This query could take some time on a busy instance
SELECT TOP (25)
            OBJECT_NAME(objectid) AS [ObjectName]
          , query_plan
          , cp.objtype
          , cp.usecounts
 FROM       sys.dm_exec_cached_plans AS cp WITH (NOLOCK)
 CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
 WHERE      CAST(query_plan AS NVARCHAR(MAX)) LIKE N'%MissingIndex%' AND dbid = DB_ID()
 ORDER BY   cp.usecounts DESC
OPTION (RECOMPILE);

-- Helps you connect missing indexes to specific stored procedures or queries
-- This can help you decide whether to add them or not


-- Get fragmentation info for all indexes above a certain size in the current database  (Query 62) (Index Fragmentation)
-- Note: This could take some time on a very large database
SELECT      DB_NAME(ps.database_id) AS [Database Name]
          , OBJECT_NAME(ps.object_id) AS [Object Name]
          , i.name AS [Index Name]
          , ps.index_id
          , ps.index_type_desc
          , ps.avg_fragmentation_in_percent
          , ps.fragment_count
          , ps.page_count
          , i.fill_factor
          , i.has_filter
          , i.filter_definition
 FROM       sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, N'LIMITED') AS ps
 INNER JOIN sys.indexes AS i WITH (NOLOCK) ON ps.[object_id] = i.[object_id] AND ps.index_id = i.index_id
 WHERE      ps.database_id = DB_ID() AND ps.page_count > 2500
 ORDER BY   ps.avg_fragmentation_in_percent DESC
OPTION (RECOMPILE);

-- Helps determine whether you have framentation in your relational indexes
-- and how effective your index maintenance strategy is


--- Index Read/Write stats (all tables in current DB) ordered by Reads  (Query 63) (Overall Index Usage - Reads)
SELECT      OBJECT_NAME(s.[object_id]) AS [ObjectName]
          , i.index_id
          , i.name AS [IndexName]
          , user_seeks + user_scans + user_lookups AS [Reads]
          , s.user_updates AS [Writes]
          , i.type_desc AS [IndexType]
          , i.fill_factor AS [FillFactor]
          , i.has_filter
          , i.filter_definition
          , s.last_user_scan
          , s.last_user_lookup
          , s.last_user_seek
 FROM       sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
 INNER JOIN sys.indexes AS i WITH (NOLOCK) ON s.[object_id] = i.[object_id]
 WHERE      OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1 AND i.index_id = s.index_id AND s.database_id = DB_ID()
 ORDER BY   user_seeks + user_scans + user_lookups DESC
OPTION (RECOMPILE); -- Order by reads


SELECT      t.name AS TableName
          , s.name AS SchemaName
          , p.rows AS RowCounts
          , SUM(a.total_pages) * 8 AS TotalSpaceKB
          , CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB
          , SUM(a.used_pages) * 8 AS UsedSpaceKB
          , CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB
          , (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
          , CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
          , f.name
 FROM       sys.tables t
 INNER JOIN sys.indexes i ON t.object_id = i.object_id
 INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id
 INNER JOIN sys.partitions p ON i.object_id = p.object_id AND   i.index_id = p.index_id
 INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
 LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
 WHERE      t.name NOT LIKE 'dt%' AND   t.is_ms_shipped = 0 AND i.object_id > 255 AND   t.name LIKE '%_HX'
            AND  t.name NOT IN (SELECT      t.name FROM sys.tables AS t WHERE   t.is_replicated = 1)
 GROUP BY   t.name
          , s.name
          , p.rows
          , f.name
 ORDER BY   t.name;

SELECT      SCHEMA_NAME(t.schema_id) [schema_name]
          , t.name
          , ix.name
          , CASE
                WHEN ix.is_unique = 1
                    THEN 'UNIQUE '
                ELSE ''
            END
          , ix.type_desc
          , CASE
                WHEN ix.is_padded = 1
                    THEN 'PAD_INDEX = ON, '
                ELSE 'PAD_INDEX = OFF, '
            END + CASE
                      WHEN ix.allow_page_locks = 1
                          THEN 'ALLOW_PAGE_LOCKS = ON, '
                      ELSE 'ALLOW_PAGE_LOCKS = OFF, '
                  END + CASE
                            WHEN ix.allow_row_locks = 1
                                THEN 'ALLOW_ROW_LOCKS = ON, '
                            ELSE 'ALLOW_ROW_LOCKS = OFF, '
                        END + CASE
                                  WHEN INDEXPROPERTY(t.object_id, ix.name, 'IsStatistics') = 1
                                      THEN 'STATISTICS_NORECOMPUTE = ON, '
                                  ELSE 'STATISTICS_NORECOMPUTE = OFF, '
                              END + CASE
                                        WHEN ix.ignore_dup_key = 1
                                            THEN 'IGNORE_DUP_KEY = ON, '
                                        ELSE 'IGNORE_DUP_KEY = OFF, '
                                    END + 'SORT_IN_TEMPDB = OFF, FILLFACTOR =' + CAST(ix.fill_factor AS VARCHAR(3)) AS IndexOptions
          , ix.is_disabled
          , FILEGROUP_NAME(ix.data_space_id) FileGroupName
 FROM       sys.tables t
 INNER JOIN sys.indexes ix ON t.object_id = ix.object_id
 WHERE      ix.type > 0 AND ix.is_primary_key = 0 AND   ix.is_unique_constraint = 0 --and schema_name(tb.schema_id)= @SchemaName and tb.name=@TableName
            AND t.is_ms_shipped = 0 AND t.name <> 'sysdiagrams'
			AND t.is_replicated = 1
 ORDER BY   SCHEMA_NAME(t.schema_id)
          , t.name
          , ix.name;

io.sql

-- Volume info for all LUNS that have database files on the current instance (Query 23) (Volume Info)
SELECT DISTINCT
        vs.volume_mount_point
      , vs.file_system_type
      , vs.logical_volume_name
      , CONVERT(DECIMAL(18, 2), vs.total_bytes / 1073741824.0) AS [Total Size (GB)]
      , CONVERT(DECIMAL(18, 2), vs.available_bytes / 1073741824.0) AS [Available Size (GB)]
      , CAST(CAST(vs.available_bytes AS FLOAT) / CAST(vs.total_bytes AS FLOAT) AS DECIMAL(18, 2)) * 100 AS [Space Free %]
 FROM   sys.master_files AS f WITH (NOLOCK)
 CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs
OPTION (RECOMPILE);
--Shows you the total and free space on the LUNs where you have database files

-- Drive level latency information (Query 24) (Drive Level Latency)
-- Based on code from Jimmy May
SELECT      [Drive]
          , CASE
                WHEN num_of_reads = 0
                    THEN 0
                ELSE (io_stall_read_ms / num_of_reads)
            END AS [Read Latency]
          , CASE
                WHEN io_stall_write_ms = 0
                    THEN 0
                ELSE (io_stall_write_ms / num_of_writes)
            END AS [Write Latency]
          , CASE
                WHEN (num_of_reads = 0 AND  num_of_writes = 0)
                    THEN 0
                ELSE (io_stall / (num_of_reads + num_of_writes))
            END AS [Overall Latency]
          , CASE
                WHEN num_of_reads = 0
                    THEN 0
                ELSE (num_of_bytes_read / num_of_reads)
            END AS [Avg Bytes/Read]
          , CASE
                WHEN io_stall_write_ms = 0
                    THEN 0
                ELSE (num_of_bytes_written / num_of_writes)
            END AS [Avg Bytes/Write]
          , CASE
                WHEN (num_of_reads = 0 AND  num_of_writes = 0)
                    THEN 0
                ELSE ((num_of_bytes_read + num_of_bytes_written) / (num_of_reads + num_of_writes))
            END AS [Avg Bytes/Transfer]
 FROM
            (SELECT     LEFT(mf.physical_name, 2) AS Drive
                      , SUM(num_of_reads) AS num_of_reads
                      , SUM(io_stall_read_ms) AS io_stall_read_ms
                      , SUM(num_of_writes) AS num_of_writes
                      , SUM(io_stall_write_ms) AS io_stall_write_ms
                      , SUM(num_of_bytes_read) AS num_of_bytes_read
                      , SUM(num_of_bytes_written) AS num_of_bytes_written
                      , SUM(io_stall) AS io_stall
              FROM      sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
              INNER JOIN sys.master_files AS mf WITH (NOLOCK) ON vfs.database_id = mf.database_id AND  vfs.file_id = mf.file_id
              GROUP BY  LEFT(mf.physical_name, 2)) AS tab
 ORDER BY   [Overall Latency]
OPTION (RECOMPILE);

-- Shows you the drive-level latency for reads and writes, in milliseconds
-- Latency above 20-25ms is usually a problem


-- Calculates average stalls per read, per write, and per total input/output for each database file  (Query 25) (IO Stalls by File)
SELECT      DB_NAME(fs.database_id) AS [Database Name]
          , CAST(fs.io_stall_read_ms / (1.0 + fs.num_of_reads) AS NUMERIC(10, 1)) AS [avg_read_stall_ms]
          , CAST(fs.io_stall_write_ms / (1.0 + fs.num_of_writes) AS NUMERIC(10, 1)) AS [avg_write_stall_ms]
          , CAST((fs.io_stall_read_ms + fs.io_stall_write_ms) / (1.0 + fs.num_of_reads + fs.num_of_writes) AS NUMERIC(10, 1)) AS [avg_io_stall_ms]
          , CONVERT(DECIMAL(18, 2), mf.size / 128.0) AS [File Size (MB)]
          , mf.physical_name
          , mf.type_desc
          , fs.io_stall_read_ms
          , fs.num_of_reads
          , fs.io_stall_write_ms
          , fs.num_of_writes
          , fs.io_stall_read_ms + fs.io_stall_write_ms AS [io_stalls]
          , fs.num_of_reads + fs.num_of_writes AS [total_io]
 FROM       sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
 INNER JOIN sys.master_files AS mf WITH (NOLOCK) ON fs.database_id = mf.database_id AND fs.[file_id] = mf.[file_id]
 ORDER BY   avg_io_stall_ms DESC
OPTION (RECOMPILE);

-- Helps determine which database files on the entire instance have the most I/O bottlenecks
-- This can help you decide whether certain LUNs are overloaded and whether you might
-- want to move some files to a different location or perhaps improve your I/O performance

-- Recovery model, log reuse wait description, log file size, log usage size  (Query 26) (Database Properties)
-- and compatibility level for all databases on instance
SELECT  db.[name] AS [Database Name]
      , db.recovery_model_desc AS [Recovery Model]
      , db.state_desc
      , db.log_reuse_wait_desc AS [Log Reuse Wait Description]
      , CONVERT(DECIMAL(18, 2), ls.cntr_value / 1024.0) AS [Log Size (MB)]
      , CONVERT(DECIMAL(18, 2), lu.cntr_value / 1024.0) AS [Log Used (MB)]
      , CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT) AS DECIMAL(18, 2)) * 100 AS [Log Used %]
      , db.[compatibility_level] AS [DB Compatibility Level]
      , db.page_verify_option_desc AS [Page Verify Option]
      , db.is_auto_create_stats_on
      , db.is_auto_update_stats_on
      , db.is_auto_update_stats_async_on
      , db.is_parameterization_forced
      , db.snapshot_isolation_state_desc
      , db.is_read_committed_snapshot_on
      , db.is_auto_close_on
      , db.is_auto_shrink_on
      , db.target_recovery_time_in_seconds
      , db.is_cdc_enabled
      , db.is_memory_optimized_elevate_to_snapshot_on
      , db.delayed_durability_desc
      , db.is_auto_create_stats_incremental_on
 FROM   sys.databases AS db WITH (NOLOCK)
 INNER JOIN sys.dm_os_performance_counters AS lu WITH (NOLOCK) ON db.name = lu.instance_name
 INNER JOIN sys.dm_os_performance_counters AS ls WITH (NOLOCK) ON db.name = ls.instance_name
 WHERE  lu.counter_name LIKE N'Log File(s) Used Size (KB)%' AND ls.counter_name LIKE N'Log File(s) Size (KB)%' AND  ls.cntr_value > 0
OPTION (RECOMPILE);

-- Things to look at:
-- How many databases are on the instance?
-- What recovery models are they using?
-- What is the log reuse wait description?
-- How full are the transaction logs ?
-- What compatibility level are the databases on? 
-- What is the Page Verify Option? (should be CHECKSUM)
-- Is Auto Update Statistics Asynchronously enabled?
-- Make sure auto_shrink and auto_close are not enabled!


-- Get I/O utilization by database (Query 30) (IO Usage By Database)
WITH Aggregate_IO_Statistics
AS (SELECT      DB_NAME(database_id) AS [Database Name]
              , CAST(SUM(num_of_bytes_read + num_of_bytes_written) / 1048576 AS DECIMAL(12, 2)) AS io_in_mb
     FROM       sys.dm_io_virtual_file_stats(NULL, NULL) AS [DM_IO_STATS]
     GROUP BY   database_id)
 SELECT     ROW_NUMBER() OVER (ORDER BY io_in_mb DESC) AS [I/O Rank]
          , [Database Name]
          , io_in_mb AS [Total I/O (MB)]
          , CAST(io_in_mb / SUM(io_in_mb) OVER () * 100.0 AS DECIMAL(5, 2)) AS [I/O Percent]
  FROM      Aggregate_IO_Statistics
  ORDER BY  [I/O Rank]
OPTION (RECOMPILE);

-- Helps determine which database is using the most I/O resources on the instance



-- I/O Statistics by file for the current database  (Query 45) (IO Stats By File)
SELECT  DB_NAME(DB_ID()) AS [Database Name]
      , df.name AS [Logical Name]
      , vfs.[file_id]
      , df.physical_name AS [Physical Name]
      , vfs.num_of_reads
      , vfs.num_of_writes
      , vfs.io_stall_read_ms
      , vfs.io_stall_write_ms
      , CAST(100. * vfs.io_stall_read_ms / (vfs.io_stall_read_ms + vfs.io_stall_write_ms) AS DECIMAL(10, 1)) AS [IO Stall Reads Pct]
      , CAST(100. * vfs.io_stall_write_ms / (vfs.io_stall_write_ms + vfs.io_stall_read_ms) AS DECIMAL(10, 1)) AS [IO Stall Writes Pct]
      , (vfs.num_of_reads + vfs.num_of_writes) AS [Writes + Reads]
      , CAST(vfs.num_of_bytes_read / 1048576.0 AS DECIMAL(10, 2)) AS [MB Read]
      , CAST(vfs.num_of_bytes_written / 1048576.0 AS DECIMAL(10, 2)) AS [MB Written]
      , CAST(100. * vfs.num_of_reads / (vfs.num_of_reads + vfs.num_of_writes) AS DECIMAL(10, 1)) AS [# Reads Pct]
      , CAST(100. * vfs.num_of_writes / (vfs.num_of_reads + vfs.num_of_writes) AS DECIMAL(10, 1)) AS [# Write Pct]
      , CAST(100. * vfs.num_of_bytes_read / (vfs.num_of_bytes_read + vfs.num_of_bytes_written) AS DECIMAL(10, 1)) AS [Read Bytes Pct]
      , CAST(100. * vfs.num_of_bytes_written / (vfs.num_of_bytes_read + vfs.num_of_bytes_written) AS DECIMAL(10, 1)) AS [Written Bytes Pct]
 FROM   sys.dm_io_virtual_file_stats(DB_ID(), NULL) AS vfs
 INNER JOIN sys.database_files AS df WITH (NOLOCK) ON vfs.[file_id] = df.[file_id]
OPTION (RECOMPILE);

-- This helps you characterize your workload better from an I/O perspective for this database
-- It helps you determine whether you has an OLTP or DW/DSS type of workload

job_info.sql

-- Get SQL Server Agent jobs and Category information (Query 4) (SQL Server Agent Jobs)
SELECT      sj.name AS [JobName]
          , sj.[description] AS [JobDescription]
          , SUSER_SNAME(sj.owner_sid) AS [JobOwner]
          , sj.date_created
          , sj.[enabled]
          , sj.notify_email_operator_id
          , sc.name AS [CategoryName]
 FROM       msdb.dbo.sysjobs AS sj WITH (NOLOCK)
 INNER JOIN msdb.dbo.syscategories AS sc WITH (NOLOCK) ON sj.category_id = sc.category_id
 ORDER BY   sj.name
OPTION (RECOMPILE);

-- Gives you some basic information about your SQL Server Agent jobs, who owns them and how they are configured
-- Look for Agent jobs that are not owned by sa

log.sql

-- Get VLF Counts for all databases on the instance (Query 28) (VLF Counts)
-- (adapted from Michelle Ufford) 
CREATE TABLE #VLFInfo (RecoveryUnitID INT
                     , FileID         INT
                     , FileSize       BIGINT
                     , StartOffset    BIGINT
                     , FSeqNo         BIGINT
                     , [Status]       BIGINT
                     , Parity         BIGINT
                     , CreateLSN      NUMERIC(38));

CREATE TABLE #VLFCountResults (DatabaseName sysname
                             , VLFCount     INT);

EXEC sp_MSforeachdb N'Use [?]; 

				INSERT INTO #VLFInfo 
				EXEC sp_executesql N''DBCC LOGINFO([?])''; 
	 
				INSERT INTO #VLFCountResults 
				SELECT DB_NAME(), COUNT(*) 
				FROM #VLFInfo; 

				TRUNCATE TABLE #VLFInfo;';

SELECT  DatabaseName, VLFCount FROM #VLFCountResults ORDER BY   VLFCount DESC;

DROP TABLE #VLFInfo;
DROP TABLE #VLFCountResults;

-- High VLF counts can affect write performance 
-- and they can make database restores and recovery take much longer
-- Try to keep your VLF counts under 200 in most cases

memory.sql

-- See if buffer pool extension (BPE) is enabled (Query 18) (BPE Enabled)
SELECT  [path]
      , state_description
      , current_size_in_kb
      , CAST(current_size_in_kb / 1048576.0 AS DECIMAL(10, 2)) AS [Size (GB)]
 FROM   sys.dm_os_buffer_pool_extension_configuration WITH (NOLOCK)
OPTION (RECOMPILE);

-- BPE is available in both Standard Edition and Enterprise Edition

-- Look at buffer descriptors to see BPE usage by database (Query 19) (BPE Usage) 
SELECT      DB_NAME(database_id) AS [Database Name]
          , COUNT(page_id) AS [Page Count]
          , CAST(COUNT(*) / 128.0 AS DECIMAL(10, 2)) AS [Buffer size(MB)]
          , AVG(read_microsec) AS [Avg Read Time (microseconds)]
 FROM       sys.dm_os_buffer_descriptors WITH (NOLOCK)
 WHERE      database_id <> 32767 AND is_in_bpool_extension = 1
 GROUP BY   DB_NAME(database_id)
 ORDER BY   [Buffer size(MB)] DESC
OPTION (RECOMPILE);

-- You will see no results if BPE is not enabled or if there is no BPE usage

-- Get information on location, time and size of any memory dumps from SQL Server  (Query 21) (Memory Dump Info)
SELECT      [filename]
          , creation_time
          , size_in_bytes / 1048576.0 AS [Size (MB)]
 FROM       sys.dm_server_memory_dumps WITH (NOLOCK)
 ORDER BY   creation_time DESC
OPTION (RECOMPILE);

-- This will not return any rows if you have 
-- not had any memory dumps (which is a good thing)


-- Get total buffer usage by database for current instance  (Query 31) (Total Buffer Usage by Database)
WITH AggregateBufferPoolUsage
AS (SELECT      DB_NAME(database_id) AS [Database Name]
              , CAST(COUNT(*) * 8 / 1024.0 AS DECIMAL(10, 2)) AS [CachedSize]
     FROM       sys.dm_os_buffer_descriptors WITH (NOLOCK)
     WHERE      database_id > 4 -- system databases
                AND database_id <> 32767 -- ResourceDB
     GROUP BY   DB_NAME(database_id))
 SELECT     ROW_NUMBER() OVER (ORDER BY CachedSize DESC) AS [Buffer Pool Rank]
          , [Database Name]
          , CachedSize AS [Cached Size (MB)]
          , CAST(CachedSize / SUM(CachedSize) OVER () * 100.0 AS DECIMAL(5, 2)) AS [Buffer Pool Percent]
  FROM      AggregateBufferPoolUsage
  ORDER BY  [Buffer Pool Rank]
OPTION (RECOMPILE);

-- Tells you how much memory (in the buffer pool) 
-- is being used by each database on the instance


-- Good basic information about OS memory amounts and state  (Query 38) (System Memory)
SELECT  total_physical_memory_kb / 1024 AS [Physical Memory (MB)]
      , available_physical_memory_kb / 1024 AS [Available Memory (MB)]
      , total_page_file_kb / 1024 AS [Total Page File (MB)]
      , available_page_file_kb / 1024 AS [Available Page File (MB)]
      , system_cache_kb / 1024 AS [System Cache (MB)]
      , system_memory_state_desc AS [System Memory State]
 FROM   sys.dm_os_sys_memory WITH (NOLOCK)
OPTION (RECOMPILE);

-- You want to see "Available physical memory is high"
-- This indicates that you are not under external memory pressure


-- SQL Server Process Address space info  (Query 39) (Process Memory)
-- (shows whether locked pages is enabled, among other things)
SELECT  physical_memory_in_use_kb / 1024 AS [SQL Server Memory Usage (MB)]
      , large_page_allocations_kb
      , locked_page_allocations_kb
      , page_fault_count
      , memory_utilization_percentage
      , available_commit_limit_kb
      , process_physical_memory_low
      , process_virtual_memory_low
 FROM   sys.dm_os_process_memory WITH (NOLOCK)
OPTION (RECOMPILE);

-- You want to see 0 for process_physical_memory_low
-- You want to see 0 for process_virtual_memory_low
-- This indicates that you are not under internal memory pressure


-- Page Life Expectancy (PLE) value for each NUMA node in current instance  (Query 40) (PLE by NUMA Node)
SELECT  @@servername AS [Server Name]
      , [object_name]
      , instance_name
      , cntr_value / 60 AS [Page Life Expectancy]
 FROM   sys.dm_os_performance_counters WITH (NOLOCK)
 WHERE  [object_name] LIKE N'%Buffer Node%' -- Handles named instances
        AND counter_name = N'Page life expectancy'
OPTION (RECOMPILE);

-- PLE is a good measurement of memory pressure.
-- Higher PLE is better. Watch the trend over time, not the absolute value.
-- This will only return one row for non-NUMA systems.


-- Memory Grants Pending value for current instance  (Query 41) (Memory Grants Pending)
SELECT  @@servername AS [Server Name]
      , [object_name]
      , cntr_value AS [Memory Grants Pending]
 FROM   sys.dm_os_performance_counters WITH (NOLOCK)
 WHERE  [object_name] LIKE N'%Memory Manager%' -- Handles named instances
        AND counter_name = N'Memory Grants Pending'
OPTION (RECOMPILE);

-- Memory Grants Pending above zero for a sustained period is a very strong indicator of memory pressure


-- Memory Clerk Usage for instance  (Query 42) (Memory Clerk Usage)
-- Look for high value for CACHESTORE_SQLCP (Ad-hoc query plans)
SELECT TOP (10)
            [type] AS [Memory Clerk Type]
          , SUM(pages_kb) / 1024 AS [Memory Usage (MB)]
 FROM       sys.dm_os_memory_clerks WITH (NOLOCK)
 GROUP BY   [type]
 ORDER BY   SUM(pages_kb) DESC
OPTION (RECOMPILE);

-- MEMORYCLERK_SQLBUFFERPOOL wass new for SQL Server 2012. It should be your highest consumer of memory


-- CACHESTORE_SQLCP  SQL Plans         
-- These are cached SQL statements or batches that aren't in stored procedures, functions and triggers
-- Watch out for high values for CACHESTORE_SQLCP

-- CACHESTORE_OBJCP  Object Plans      
-- These are compiled plans for stored procedures, functions and triggers


-- Breaks down buffers used by current database by object (table, index) in the buffer cache  (Query 58) (Buffer Usage)
-- This query can take some time on a large database
SELECT      OBJECT_NAME(p.[object_id]) AS [Object Name]
          , p.index_id
          , CAST(COUNT(*) / 128.0 AS DECIMAL(10, 2)) AS [Buffer size(MB)]
          , COUNT(*) AS [BufferCount]
          , p.rows AS [Row Count]
          , p.data_compression_desc AS [Compression Type]
 FROM       sys.allocation_units AS a WITH (NOLOCK)
 INNER JOIN sys.dm_os_buffer_descriptors AS b WITH (NOLOCK) ON a.allocation_unit_id = b.allocation_unit_id
 INNER JOIN sys.partitions AS p WITH (NOLOCK) ON a.container_id = p.hobt_id
 WHERE      b.database_id = CONVERT(INT, DB_ID()) AND   p.[object_id] > 100
 GROUP BY   p.[object_id]
          , p.index_id
          , p.data_compression_desc
          , p.[rows]
 ORDER BY   [BufferCount] DESC
OPTION (RECOMPILE);

-- Tells you what tables and indexes are using the most memory in the buffer cache
-- It can help identify possible candidates for data compression

numa.sql

-- SQL Server NUMA Node information  (Query 9) (SQL Server NUMA Info)
SELECT  node_id
      , node_state_desc
      , memory_node_id
      , processor_group
      , online_scheduler_count
      , active_worker_count
      , avg_load_balance
      , resource_monitor_state
 FROM   sys.dm_os_nodes WITH (NOLOCK)
 WHERE  node_state_desc <> N'ONLINE DAC'
OPTION (RECOMPILE);

-- Gives you some useful information about the composition 
-- and relative load on your NUMA nodes

plan.sql

SELECT
	[qs].[last_execution_time],
	[qs].[execution_count],
	[qs].[total_logical_reads]/[qs].[execution_count] [AvgLogicalReads],
	[qs].[min_logical_reads],
	[qs].[max_logical_reads],
	[t].[text],
	[p].[query_plan],
	[qs].[plan_handle]
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]
WHERE [t].[text] LIKE '%usp_CustomerTransactionInfo%';
GO

-- Find missing index warnings for cached plans in the current database  (Query 57) (Missing Index Warnings)
-- Note: This query could take some time on a busy instance
SELECT TOP (25)
            OBJECT_NAME(objectid) AS [ObjectName]
          , query_plan
          , cp.objtype
          , cp.usecounts
 FROM       sys.dm_exec_cached_plans AS cp WITH (NOLOCK)
 CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
 WHERE      CAST(query_plan AS NVARCHAR(MAX)) LIKE N'%MissingIndex%' AND dbid = DB_ID()
 ORDER BY   cp.usecounts DESC
OPTION (RECOMPILE);

-- Helps you connect missing indexes to specific stored procedures or queries
-- This can help you decide whether to add them or not

server_info.sql

-- Check the major product version to see if it is SQL Server 2014 CTP2 or greater
IF NOT EXISTS (SELECT   * WHERE CONVERT(VARCHAR(128), SERVERPROPERTY('ProductVersion')) LIKE '12%')
BEGIN
    DECLARE @ProductVersion VARCHAR(128) = CONVERT(VARCHAR(128), SERVERPROPERTY('ProductVersion'));
    RAISERROR('Script does not match the ProductVersion [%s] of this instance. Many of these queries may not work on this version.', 18, 16, @ProductVersion);
END;
ELSE
    PRINT N'You have the correct major version of SQL Server for this diagnostic information script';


-- Instance level queries *******************************

-- SQL and OS Version information for current instance  (Query 1) (Version Info)
SELECT  @@servername AS [Server Name], @@version AS [SQL Server and OS Version Info];

-- SQL Server 2014 RTM Branch Builds						
-- Build			Description			Release Date		
-- 11.0.9120        CTP1				6/2/2013
-- 12.0.1524		CTP2				10/15/2013
-- 12.0.2000        RTM					4/1/2014
-- 12.0.2342        CU1                 4/21/2014

-- SQL Server 2014 build versions
-- http://support.microsoft.com/kb/2936603


-- When was SQL Server installed  (Query 2) (SQL Server Install Date)  
SELECT  @@servername AS [Server Name]
      , create_date AS [SQL Server Install Date]
 FROM   sys.server_principals WITH (NOLOCK)
 WHERE  name = N'NT AUTHORITY\SYSTEM' OR name = N'NT AUTHORITY\NETWORK SERVICE'
OPTION (RECOMPILE);

-- Tells you the date and time that SQL Server was installed
-- It is a good idea to know how old your instance is


-- Get selected server properties (SQL Server 2014)  (Query 3) (Server Properties)
SELECT  SERVERPROPERTY('MachineName') AS [MachineName]
      , SERVERPROPERTY('ServerName') AS [ServerName]
      , SERVERPROPERTY('InstanceName') AS [Instance]
      , SERVERPROPERTY('IsClustered') AS [IsClustered]
      , SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS]
      , SERVERPROPERTY('Edition') AS [Edition]
      , SERVERPROPERTY('ProductLevel') AS [ProductLevel]
      , SERVERPROPERTY('ProductVersion') AS [ProductVersion]
      , SERVERPROPERTY('ProcessID') AS [ProcessID]
      , SERVERPROPERTY('Collation') AS [Collation]
      , SERVERPROPERTY('IsFullTextInstalled') AS [IsFullTextInstalled]
      , SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsIntegratedSecurityOnly]
      , SERVERPROPERTY('IsHadrEnabled') AS [IsHadrEnabled]
      , SERVERPROPERTY('HadrManagerStatus') AS [HadrManagerStatus]
      , SERVERPROPERTY('IsXTPSupported') AS [IsXTPSupported];

-- This gives you a lot of useful information about your instance of SQL Server,
-- such as the ProcessID for SQL Server and your collation
-- The last column is new for SQL Server 2014

-- Returns a list of all global trace flags that are enabled (Query 6) (Global Trace Flags)
DBCC TRACESTATUS(-1);

-- If no global trace flags are enabled, no results will be returned.
-- It is very useful to know what global trace flags are currently enabled as part of the diagnostic process.

-- Common trace flags that should be enabled in most cases
-- TF 3226 - Supresses logging of successful database backup messages to the SQL Server Error Log
-- TF 1118 - Helps alleviate allocation contention in tempdb (more important with older versions of SQL Server)


-- Windows information (SQL Server 2014)  (Query 7) (Windows Info)
SELECT  windows_release
      , windows_service_pack_level
      , windows_sku
      , os_language_version
 FROM   sys.dm_os_windows_info WITH (NOLOCK)
OPTION (RECOMPILE);

-- Gives you major OS version, Service Pack, Edition, and language info for the operating system
-- 6.3 is either Windows 8.1 or Windows Server 2012 R2 
-- 6.2 is either Windows 8 or Windows Server 2012
-- 6.1 is either Windows 7 or Windows Server 2008 R2
-- 6.0 is either Windows Vista or Windows Server 2008

-- Windows SKU codes
-- 4  is Enterprise Edition
-- 48 is Professional Edition

-- 1033 for os_language_version is US-English

-- Using SQL Server in Windows 8, Windows 8.1, Windows Server 2012 and Windows Server 2012 R2 environments
-- http://support.microsoft.com/kb/2681562



-- SQL Server Services information (SQL Server 2014) (Query 8) (SQL Server Services Info)
SELECT  servicename
      , process_id
      , startup_type_desc
      , status_desc
      , last_startup_time
      , service_account
      , is_clustered
      , cluster_nodename
      , [filename]
 FROM   sys.dm_server_services WITH (NOLOCK)
OPTION (RECOMPILE);

-- Tells you the account being used for the SQL Server Service and the SQL Agent Service
-- Shows the processid, when they were last started, and their current status
-- Shows whether you are running on a failover cluster instance

-- Hardware information from SQL Server 2014  (Query 10) (Hardware Info)
-- (Cannot distinguish between HT and multi-core)
SELECT  cpu_count AS [Logical CPU Count]
      , scheduler_count
      , hyperthread_ratio AS [Hyperthread Ratio]
      , cpu_count / hyperthread_ratio AS [Physical CPU Count]
      , physical_memory_kb / 1024 AS [Physical Memory (MB)]
      , committed_kb / 1024 AS [Committed Memory (MB)]
      , committed_target_kb / 1024 AS [Committed Target Memory (MB)]
      , max_workers_count AS [Max Workers Count]
      , affinity_type_desc AS [Affinity Type]
      , sqlserver_start_time AS [SQL Server Start Time]
      , virtual_machine_type_desc AS [Virtual Machine Type]
 FROM   sys.dm_os_sys_info WITH (NOLOCK)
OPTION (RECOMPILE);

-- Gives you some good basic hardware information about your database server
-- Note: virtual_machine_type_desc of HYPERVISOR does not automatically mean you are running SQL Server inside of a VM
-- It merely indicates that you have a hypervisor running on your host


-- Get System Manufacturer and model number from  (Query 11) (System Manufacturer)
-- SQL Server Error log. This query might take a few seconds 
-- if you have not recycled your error log recently
EXEC xp_readerrorlog 0, 1, "Manufacturer";

-- This can help you determine the capabilities
-- and capacities of your database server


-- Get processor description from Windows Registry  (Query 12) (Processor Description)
EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE'
                       , N'HARDWARE\DESCRIPTION\System\CentralProcessor\0'
                       , N'ProcessorNameString';

-- Gives you the model number and rated clock speed of your processor(s)
-- Your processors may be running at less than the rated clock speed due
-- to the Windows Power Plan or hardware power management



-- Shows you where the SQL Server failover cluster diagnostic log is located and how it is configured  (Query 13) (SQL Server Error Log)
SELECT  is_enabled
      , [path]
      , max_size
      , max_files
 FROM   sys.dm_os_server_diagnostics_log_configurations WITH (NOLOCK)
OPTION (RECOMPILE);

-- Knowing this information is important for troubleshooting purposes
-- Also shows you the location of other error and diagnostic log files


-- Get configuration values for instance  (Query 17) (Configuration Values)
SELECT      name
          , value
          , value_in_use
          , minimum
          , maximum
          , [description]
          , is_dynamic
          , is_advanced
 FROM       sys.configurations WITH (NOLOCK)
 ORDER BY   name
OPTION (RECOMPILE);

-- Focus on
-- backup compression default (should be 1 in most cases)
-- cost threshold for parallelism
-- clr enabled (only enable if it is needed)
-- lightweight pooling (should be zero)
-- max degree of parallelism 
-- max server memory (MB) (set to an appropriate value)
-- optimize for ad hoc workloads (should be 1)
-- priority boost (should be zero)

-- Get information about TCP Listener for SQL Server  (Query 20) (TCP Listener States)
SELECT      listener_id
          , ip_address
          , is_ipv4
          , port
          , type_desc
          , state_desc
          , start_time
 FROM       sys.dm_tcp_listener_states WITH (NOLOCK)
 ORDER BY   listener_id
OPTION (RECOMPILE);

-- Helpful for network and connectivity troubleshooting

-- File names and paths for TempDB and all user databases in instance  (Query 22) (Database Filenames and Paths)
SELECT      DB_NAME([database_id]) AS [Database Name]
          , [file_id]
          , name
          , physical_name
          , type_desc
          , state_desc
          , is_percent_growth
          , growth
          , CONVERT(BIGINT, growth / 128.0) AS [Growth in MB]
          , CONVERT(BIGINT, size / 128.0) AS [Total Size in MB]
 FROM       sys.master_files WITH (NOLOCK)
 WHERE      [database_id] > 4 AND   [database_id] <> 32767 OR   [database_id] = 2
 ORDER BY   DB_NAME([database_id])
OPTION (RECOMPILE);

-- Things to look at:
-- Are data files and log files on different drives?
-- Is everything on the C: drive?
-- Is TempDB on dedicated drives?
-- Is there only one TempDB data file?
-- Are all of the TempDB data files the same size?
-- Are there multiple data files for user databases?
-- Is percent growth enabled for any files (which is bad)?


-- Database specific queries *****************************************************************

-- **** Switch to a user database *****
USE YourDatabaseName;
GO

-- Individual File Sizes and space available for current database  (Query 44) (File Sizes and Space)
SELECT  f.name AS [File Name]
      , f.physical_name AS [Physical Name]
      , CAST((f.size / 128.0) AS DECIMAL(15, 2)) AS [Total Size in MB]
      , CAST(f.size / 128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS INT) / 128.0 AS DECIMAL(15, 2)) AS [Available Space In MB]
      , [file_id]
      , fg.name AS [Filegroup Name]
 FROM   sys.database_files AS f WITH (NOLOCK)
 LEFT OUTER JOIN sys.data_spaces AS fg WITH (NOLOCK) ON f.data_space_id = fg.data_space_id
OPTION (RECOMPILE);

-- Look at how large and how full the files are and where they are located
-- Make sure the transaction log is not full!!


-- Get Table names, row counts, and compression status for clustered index or heap  (Query 59) (Table Sizes)
SELECT      OBJECT_NAME(object_id) AS [ObjectName]
          , SUM(rows) AS [RowCount]
          , data_compression_desc AS [CompressionType]
 FROM       sys.partitions WITH (NOLOCK)
 WHERE      index_id < 2 --ignore the partitions from the non-clustered index if any
            AND OBJECT_NAME(object_id) NOT LIKE N'sys%' AND OBJECT_NAME(object_id) NOT LIKE N'queue_%'
            AND  OBJECT_NAME(object_id) NOT LIKE N'filestream_tombstone%' AND OBJECT_NAME(object_id) NOT LIKE N'fulltext%'
            AND   OBJECT_NAME(object_id) NOT LIKE N'ifts_comp_fragment%' AND  OBJECT_NAME(object_id) NOT LIKE N'filetable_updates%'
            AND   OBJECT_NAME(object_id) NOT LIKE N'xml_index_nodes%'
 GROUP BY   object_id
          , data_compression_desc
 ORDER BY   SUM(rows) DESC
OPTION (RECOMPILE);

-- Gives you an idea of table sizes, and possible data compression opportunities



-- Get some key table properties (Query 60) (Table Properties)
SELECT      [name]
          , create_date
          , lock_on_bulk_load
          , is_replicated
          , has_replication_filter
          , is_tracked_by_cdc
          , lock_escalation_desc
          , is_memory_optimized
          , durability_desc
 FROM       sys.tables WITH (NOLOCK)
 ORDER BY   [name]
OPTION (RECOMPILE);

-- Gives you some good information about your tables
-- Is Memory optimized and durability description are Hekaton-related properties that are new in SQL Server 2014


-- Show which indexes in the current database are most active for Reads
SELECT  * FROM  sys.dm_os_sys_info AS dosi;

--- Index Read/Write stats (all tables in current DB) ordered by Writes  (Query 64) (Overall Index Usage - Writes)
SELECT      OBJECT_NAME(s.[object_id]) AS [ObjectName]
          , i.index_id
          , i.name AS [IndexName]
          , s.user_updates AS [Writes]
          , user_seeks + user_scans + user_lookups AS [Reads]
          , i.type_desc AS [IndexType]
          , i.fill_factor AS [FillFactor]
          , i.has_filter
          , i.filter_definition
          , s.last_system_update
          , s.last_user_update
 FROM       sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
 INNER JOIN sys.indexes AS i WITH (NOLOCK) ON s.[object_id] = i.[object_id]
 WHERE      OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1 AND i.index_id = s.index_id AND s.database_id = DB_ID()
 ORDER BY   s.object_id DESC
OPTION (RECOMPILE); -- Order by writes

-- Show which indexes in the current database are most active for Writes


-- Look at recent Full backups for the current database (Query 65) (Recent Full Backups)
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]
          , DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) AS [Backup Elapsed Time (sec)]
          , bs.backup_finish_date AS [Backup Finish Date]
 FROM       msdb.dbo.backupset AS bs WITH (NOLOCK)
 WHERE      DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) > 0 AND   bs.backup_size > 0 AND  bs.type = 'D' -- Change to L if you want Log backups
            AND database_name = DB_NAME(DB_ID())
 ORDER BY   bs.backup_finish_date DESC
OPTION (RECOMPILE);

-- Are your backup sizes and times changing over time?


-- Get the average full backup size by month for the current database (SQL 2012) (Query 66) (Database Size History)
-- This helps you understand your database growth over time
-- Adapted from Erin Stellato
SELECT      [database_name] AS [Database]
          , DATEPART(MONTH, [backup_start_date]) AS [Month]
          , CAST(AVG([backup_size] / 1024 / 1024) AS DECIMAL(15, 2)) AS [Backup Size (MB)]
          , CAST(AVG([compressed_backup_size] / 1024 / 1024) AS DECIMAL(15, 2)) AS [Compressed Backup Size (MB)]
          , CAST(AVG([backup_size] / [compressed_backup_size]) AS DECIMAL(15, 2)) AS [Compression Ratio]
 FROM       msdb.dbo.backupset WITH (NOLOCK)
 WHERE      [database_name] = DB_NAME(DB_ID()) AND  [type] = 'D' AND backup_start_date >= DATEADD(MONTH, -12, GETDATE())
 GROUP BY   [database_name]
          , DATEPART(mm, [backup_start_date])
OPTION (RECOMPILE);

-- The Backup Size (MB) (without backup compression) shows the true size of your database over time
-- This helps you track and plan your data size growth
-- It is possible that your data files may be larger on disk due to empty space within those files

stats.sql

-- When were Statistics last updated on all indexes?  (Query 61) (Statistics Update)
SELECT      o.name
          , 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
          , st.row_count
          , s.is_incremental
          , 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] = 'U'
 ORDER BY   STATS_DATE(i.[object_id], i.index_id) ASC
OPTION (RECOMPILE);

-- Helps discover possible problems with out-of-date statistics
-- Also gives you an idea which indexes are most active


-- Top cached queries by Execution Count (SQL Server 2014)  (Query 46) (Query Execution Counts)
SELECT TOP (100)
            qs.execution_count
          , qs.total_rows
          , qs.last_rows
          , qs.min_rows
          , qs.max_rows
          , qs.last_elapsed_time
          , qs.min_elapsed_time
          , qs.max_elapsed_time
          , total_worker_time
          , total_logical_reads
          , SUBSTRING(qt.text
                    , qs.statement_start_offset / 2 + 1
                    , (CASE
                           WHEN qs.statement_end_offset = -1
                               THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
                           ELSE qs.statement_end_offset
                       END - qs.statement_start_offset) / 2) AS query_text
 FROM       sys.dm_exec_query_stats AS qs WITH (NOLOCK)
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
 ORDER BY   qs.execution_count DESC
OPTION (RECOMPILE);

-- Uses several new rows returned columns to help troubleshoot performance problems


-- Top Cached SPs By Execution Count (SQL Server 2014)  (Query 47) (SP Execution Counts)
SELECT TOP (100)
            p.name AS [SP Name]
          , qs.execution_count
          , ISNULL(qs.execution_count / DATEDIFF(MINUTE, qs.cached_time, GETDATE()), 0) AS [Calls/Minute]
          , qs.total_worker_time / qs.execution_count AS [AvgWorkerTime]
          , qs.total_worker_time AS [TotalWorkerTime]
          , qs.total_elapsed_time
          , qs.total_elapsed_time / qs.execution_count AS [avg_elapsed_time]
          , qs.cached_time
 FROM       sys.procedures AS p WITH (NOLOCK)
 INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id]
 WHERE      qs.database_id = DB_ID()
 ORDER BY   qs.execution_count DESC
OPTION (RECOMPILE);

-- Tells you which cached stored procedures are called the most often
-- This helps you characterize and baseline your workload




-- Top Cached SPs By Avg Elapsed Time (SQL Server 2014)  (Query 48) (SP Avg Elapsed Time)
SELECT TOP (25)
            p.name AS [SP Name]
          , qs.total_elapsed_time / qs.execution_count AS [avg_elapsed_time]
          , qs.total_elapsed_time
          , qs.execution_count
          , ISNULL(qs.execution_count / DATEDIFF(MINUTE, qs.cached_time, GETDATE()), 0) AS [Calls/Minute]
          , qs.total_worker_time / qs.execution_count AS [AvgWorkerTime]
          , qs.total_worker_time AS [TotalWorkerTime]
          , qs.cached_time
 FROM       sys.procedures AS p WITH (NOLOCK)
 INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id]
 WHERE      qs.database_id = DB_ID()
 ORDER BY   avg_elapsed_time DESC
OPTION (RECOMPILE);

-- This helps you find long-running cached stored procedures that
-- may be easy to optimize with standard query tuning techniques


-- Top Cached SPs By Avg Elapsed Time with execution time variability (SQL Server 2014)  (Query 49) (SP Avg Elapsed Variable Time)
SELECT TOP (25)
            p.name AS [SP Name]
          , qs.execution_count
          , qs.min_elapsed_time
          , qs.total_elapsed_time / qs.execution_count AS [avg_elapsed_time]
          , qs.max_elapsed_time
          , qs.last_elapsed_time
          , qs.cached_time
 FROM       sys.procedures AS p WITH (NOLOCK)
 INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id]
 WHERE      qs.database_id = DB_ID()
 ORDER BY   avg_elapsed_time DESC
OPTION (RECOMPILE);

-- This gives you some interesting information about the variability in the
-- execution time of your cached stored procedures, which is useful for tuning


-- Top Cached SPs By Total Worker time (SQL Server 2014). Worker time relates to CPU cost  (Query 50) (SP Worker Time)
SELECT TOP (25)
            p.name AS [SP Name]
          , qs.total_worker_time AS [TotalWorkerTime]
          , qs.total_worker_time / qs.execution_count AS [AvgWorkerTime]
          , qs.execution_count
          , ISNULL(qs.execution_count / DATEDIFF(MINUTE, qs.cached_time, GETDATE()), 0) AS [Calls/Minute]
          , qs.total_elapsed_time
          , qs.total_elapsed_time / qs.execution_count AS [avg_elapsed_time]
          , qs.cached_time
 FROM       sys.procedures AS p WITH (NOLOCK)
 INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id]
 WHERE      qs.database_id = DB_ID()
 ORDER BY   qs.total_worker_time DESC
OPTION (RECOMPILE);

-- This helps you find the most expensive cached stored procedures from a CPU perspective
-- You should look at this if you see signs of CPU pressure


-- Top Cached SPs By Total Logical Reads (SQL Server 2014). Logical reads relate to memory pressure  (Query 51) (SP Logical Reads)
SELECT TOP (25)
            p.name AS [SP Name]
          , qs.total_logical_reads AS [TotalLogicalReads]
          , qs.total_logical_reads / qs.execution_count AS [AvgLogicalReads]
          , qs.execution_count
          , ISNULL(qs.execution_count / DATEDIFF(MINUTE, qs.cached_time, GETDATE()), 0) AS [Calls/Minute]
          , qs.total_elapsed_time
          , qs.total_elapsed_time / qs.execution_count AS [avg_elapsed_time]
          , qs.cached_time
 FROM       sys.procedures AS p WITH (NOLOCK)
 INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id]
 WHERE      qs.database_id = DB_ID()
 ORDER BY   qs.total_logical_reads DESC
OPTION (RECOMPILE);

-- This helps you find the most expensive cached stored procedures from a memory perspective
-- You should look at this if you see signs of memory pressure


-- Top Cached SPs By Total Physical Reads (SQL Server 2014). Physical reads relate to disk I/O pressure  (Query 52) (SP Physical Reads)
SELECT TOP (25)
            p.name AS [SP Name]
          , qs.total_physical_reads AS [TotalPhysicalReads]
          , qs.total_physical_reads / qs.execution_count AS [AvgPhysicalReads]
          , qs.execution_count
          , qs.total_logical_reads
          , qs.total_elapsed_time
          , qs.total_elapsed_time / qs.execution_count AS [avg_elapsed_time]
          , qs.cached_time
 FROM       sys.procedures AS p WITH (NOLOCK)
 INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id]
 WHERE      qs.database_id = DB_ID() AND qs.total_physical_reads > 0
 ORDER BY   qs.total_physical_reads DESC
          , qs.total_logical_reads DESC
OPTION (RECOMPILE);

-- This helps you find the most expensive cached stored procedures from a read I/O perspective
-- You should look at this if you see signs of I/O pressure or of memory pressure

-- Top Cached SPs By Total Logical Writes (SQL Server 2014)  (Query 53) (SP Logical Writes)
-- Logical writes relate to both memory and disk I/O pressure 
SELECT TOP (25)
            p.name AS [SP Name]
          , qs.total_logical_writes AS [TotalLogicalWrites]
          , qs.total_logical_writes / qs.execution_count AS [AvgLogicalWrites]
          , qs.execution_count
          , ISNULL(qs.execution_count / DATEDIFF(MINUTE, qs.cached_time, GETDATE()), 0) AS [Calls/Minute]
          , qs.total_elapsed_time
          , qs.total_elapsed_time / qs.execution_count AS [avg_elapsed_time]
          , qs.cached_time
 FROM       sys.procedures AS p WITH (NOLOCK)
 INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id]
 WHERE      qs.database_id = DB_ID() AND qs.total_logical_writes > 0
 ORDER BY   qs.total_logical_writes DESC
OPTION (RECOMPILE);

-- This helps you find the most expensive cached stored procedures from a write I/O perspective
-- You should look at this if you see signs of I/O pressure or of memory pressure


-- Lists the top statements by average input/output usage for the current database  (Query 54) (Top IO Statements)
SELECT TOP (50)
            OBJECT_NAME(qt.objectid, dbid) AS [SP Name]
          , (qs.total_logical_reads + qs.total_logical_writes) / qs.execution_count AS [Avg IO]
          , qs.execution_count AS [Execution Count]
          , SUBSTRING(qt.[text]
                    , qs.statement_start_offset / 2
                    , (CASE
                           WHEN qs.statement_end_offset = -1
                               THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2
                           ELSE qs.statement_end_offset
                       END - qs.statement_start_offset) / 2) AS [Query Text]
 FROM       sys.dm_exec_query_stats AS qs WITH (NOLOCK)
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
 WHERE      qt.[dbid] = DB_ID()
 ORDER BY   [Avg IO] DESC
OPTION (RECOMPILE);

-- Helps you find the most expensive statements for I/O by SP