SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS [index_advantage], migs.last_user_seek, mid.[statement] AS...

-- Possible Bad NC Indexes (writes > reads) SELECT OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id,...

SELECT TOP(50) OBJECT_NAME(qt.objectid) AS [SP Name], (qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count AS [Avg IO],...

-- Logical writes relate to both memory and disk I/O pressure SELECT TOP(25) p.name AS [SP Name], qs.total_logical_writes AS...

SELECT TOP(25) p.name AS [SP Name],qs.total_physical_reads AS [TotalPhysicalReads], qs.total_physical_reads/qs.execution_count AS...

-- Top Cached SPs By Total Logical Reads. Logical reads relate to memory pressure SELECT TOP(25) p.name AS [SP Name],...

SELECT TOP(25) p.name AS [SP Name], qs.total_worker_time AS [TotalWorkerTime], qs.total_worker_time/qs.execution_count AS...

SELECT TOP(25) p.name AS [SP Name], qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], qs.total_elapsed_time,...

SELECT distinct d.name AS 'Database', SUM(m.size * 8/1024000) OVER (PARTITION BY d.name) AS 'Database Total Size GB' FROM...

-- Top Cached SPs By Execution Count (SQL 2008) SELECT TOP(250) p.name AS [SP Name], qs.execution_count, --ISNULL(qs.execution_count/DATE...

-- I/O Statistics by file for the current database SELECT DB_NAME(DB_ID()) AS [Database Name],a.[file_id],b.name as [File_Name],...

-- Get transaction log size and space information for the current database SELECT DB_NAME(database_id) AS [Database Name], database_id,...

-- **** Switch to a user database ***** USE YourDatabaseName; GO -- Individual File Sizes and space available for current database SELECT...

-- Find single-use, ad-hoc queries that are bloating the plan cache SELECT TOP(20) [text] AS [QueryText], cp.size_in_bytes/1024 Size_MB...

-- Memory Clerk Usage for instance -- Look for high value for CACHESTORE_SQLCP (Ad-hoc query plans) SELECT TOP(10) [type] AS [Memory...

-- Memory Grants Pending value for default instance SELECT cntr_value AS [Memory Grants Pending] FROM sys.dm_os_performance_counters WITH...

-- Memory Grants Outstanding value for default instance SELECT cntr_value AS [Memory Grants Outstanding] FROM sys.dm_os_performance_count...

-- Page Life Expectancy (PLE) value for default instance SELECT cntr_value AS [Page Life Expectancy] FROM sys.dm_os_performance_counters...

-- SQL Server Process Address space info --(shows whether locked pages is enabled, among other things) SELECT physical_memory_in_use_kb/1...

-- Good basic information about memory amounts and state SELECT total_physical_memory_kb/1024000 Total_Physical_MM_GB,...