SSMS — Missing Index Warning Ignored: 30-Second Page Loads

-- ============================================
-- SSMS Server Overview Queries
-- Run these in SSMS to understand your environment
-- ============================================

-- 1. Server version and edition
SELECT
  SERVERPROPERTY('ProductVersion') AS version,
  SERVERPROPERTY('ProductLevel') AS service_pack,
  SERVERPROPERTY('Edition') AS edition,
  SERVERPROPERTY('EngineEdition') AS engine_edition,
  SERVERPROPERTY('ServerName') AS server_name;

-- 2. All databases with size and recovery model
SELECT
  d.name AS database_name,
  d.recovery_model_desc AS recovery_model,
  d.state_desc AS state,
  CAST(SUM(mf.size) * 8.0 / 1024 AS DECIMAL(10,2)) AS size_mb,
  d.compatibility_level,
  d.create_date
FROM sys.databases d
INNER JOIN sys.master_files mf ON d.database_id = mf.database_id
GROUP BY d.name, d.recovery_model_desc, d.state_desc,
         d.compatibility_level, d.create_date
ORDER BY size_mb DESC;

-- 3. Active connections per database
SELECT
  DB_NAME(dbid) AS database_name,
  COUNT(*) AS connection_count,
  loginame AS login_name
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY DB_NAME(dbid), loginame
ORDER BY connection_count DESC;

-- 4. Current running queries with duration
SELECT
  r.session_id,
  r.status,
  r.command,
  r.cpu_time,
  r.total_elapsed_time / 1000.0 AS elapsed_seconds,
  r.reads,
  r.writes,
  t.text AS query_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id > 50
ORDER BY r.total_elapsed_time DESC;

-- 5. Memory configuration
SELECT
  physical_memory_in_use_kb / 1024 AS memory_used_mb,
  large_page_allocations_kb / 1024 AS large_pages_mb,
  locked_page_allocations_kb / 1024 AS locked_pages_mb,
  total_virtual_address_space_kb / 1024 AS total_virtual_mb,
  available_commit_limit_kb / 1024 AS available_commit_mb
FROM sys.dm_os_process_memory;

-- 6. Wait statistics (top waits indicating bottlenecks)
SELECT TOP 10
  wait_type,
  wait_time_ms / 1000.0 AS wait_seconds,
  waiting_tasks_count,
  wait_time_ms / NULLIF(waiting_tasks_count, 0) AS avg_wait_ms,
  signal_wait_time_ms / 1000.0 AS signal_wait_seconds
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%'
  AND wait_type NOT LIKE '%IDLE%'
  AND wait_type NOT LIKE '%QUEUE%'
ORDER BY wait_time_ms DESC;

-- ============================================
-- SSMS Post-Installation Configuration
-- Run these queries to configure your environment
-- ============================================

-- 1. Set default database for new connections
-- (Do this in SSMS: Tools > Options > Query Execution > SQL Server > General)
-- Set "Set nocount on" by default to reduce network traffic
SET NOCOUNT ON;

-- 2. Verify your connection settings
SELECT
  @@SERVERNAME AS server,
  @@VERSION AS version,
  DB_NAME() AS current_database,
  SUSER_SNAME() AS login_name,
  SYSTEM_USER AS system_user,
  SESSION_USER AS session_user;

-- 3. Configure recommended server settings (run as sysadmin)
-- Enable Query Store for performance baselining
ALTER DATABASE YourDatabase SET QUERY_STORE = ON
(
  OPERATION_MODE = READ_WRITE,
  DATA_FLUSH_INTERVAL_SECONDS = 900,
  INTERVAL_LENGTH_MINUTES = 60,
  MAX_STORAGE_SIZE_MB = 1024,
  QUERY_CAPTURE_MODE = AUTO,
  SIZE_BASED_CLEANUP_MODE = AUTO,
  MAX_PLANS_PER_QUERY = 200
);

-- 4. Enable automatic statistics updates
ALTER DATABASE YourDatabase SET AUTO_UPDATE_STATISTICS ON;
ALTER DATABASE YourDatabase SET AUTO_CREATE_STATISTICS ON;

-- 5. Verify TempDB configuration (should have multiple files)
SELECT
  name,
  physical_name,
  size * 8 / 1024 AS size_mb,
  growth,
  max_size
FROM sys.master_files
WHERE database_id = DB_ID('tempdb')
ORDER BY type, file_id;

-- 6. Check max memory setting
SELECT
  name,
  value_in_use
FROM sys.configurations
WHERE name IN (
  'max server memory (MB)',
  'min server memory (MB)',
  'max degree of parallelism',
  'cost threshold for parallelism',
  'backup compression default'
)
ORDER BY name;

-- 7. Set recommended memory (leave 4GB for OS, adjust for your server)
-- EXEC sp_configure 'show advanced options', 1;
-- RECONFIGURE;
-- EXEC sp_configure 'max server memory (MB)', 28672; -- For 32GB server
-- RECONFIGURE;

-- ============================================
-- Execution Plan Analysis Queries
-- Use these with SSMS execution plans to diagnose performance
-- ============================================

-- 1. Find queries with missing index recommendations from Query Store
SELECT
  q.query_id,
  qt.query_sql_text,
  rs.avg_duration / 1000.0 AS avg_duration_ms,
  rs.avg_logical_reads,
  rs.count_executions,
  p.query_plan
FROM sys.query_store_query q
INNER JOIN sys.query_store_query_text qt ON q.query_text_id = qt.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
WHERE p.query_plan LIKE '%MissingIndex%'
ORDER BY rs.avg_duration DESC;

-- 2. Compare estimated vs actual rows (parameter sniffing indicator)
-- Run this query, then view actual execution plan (Ctrl+M)
-- Look for large discrepancies between estimated and actual rows
SELECT
  o.OrderId,\  o.OrderDate,
  c.CustomerName,
  SUM(oi.Quantity * oi.UnitPrice) AS OrderTotal
FROM Orders o
INNER JOIN Customers c ON o.CustomerId = c.CustomerId
INNER JOIN OrderItems oi ON o.OrderId = oi.OrderId
WHERE o.OrderDate >= '2025-01-01'
  AND o.OrderDate < '2026-01-01'
  AND c.Region = 'North America'
GROUP BY o.OrderId, o.OrderDate, c.CustomerName
ORDER BY OrderTotal DESC;

-- 3. Find queries causing index scans (full table reads)
SELECT TOP 20
  qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
  qs.execution_count,
  SUBSTRING(st.text,
    (qs.statement_start_offset / 2) + 1,
    ((CASE qs.statement_end_offset
      WHEN -1 THEN DATALENGTH(st.text)
      ELSE qs.statement_end_offset
    END - qs.statement_start_offset) / 2) + 1
  ) AS query_text,
  qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qp.query_plan LIKE '%IndexScan%'
ORDER BY avg_logical_reads DESC;

-- 4. Identify implicit conversions (hidden performance killer)
SELECT
  TOP 10
  qs.execution_count,
  qs.total_worker_time / 1000.0 AS total_cpu_ms,
  st.text AS query_text,
  qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qp.query_plan LIKE '%CONVERT_IMPLICIT%'
ORDER BY qs.total_worker_time DESC;

-- 5. Query Store: find regressed queries (plans that got worse)
SELECT
  q.query_id,
  qt.query_sql_text,
  p.plan_id,
  ROUND(rs_first.avg_duration / 1000.0, 2) AS first_avg_ms,
  ROUND(rs_last.avg_duration / 1000.0, 2) AS last_avg_ms,
  ROUND(
    (rs_last.avg_duration - rs_first.avg_duration) /
    NULLIF(rs_first.avg_duration, 0) * 100, 1
  ) AS degradation_pct
FROM sys.query_store_query q
INNER JOIN sys.query_store_query_text qt ON q.query_text_id = qt.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_first
  ON p.plan_id = rs_first.plan_id
INNER JOIN sys.query_store_runtime_stats_interval i_first
  ON rs_first.runtime_stats_interval_id = i_first.runtime_stats_interval_id
INNER JOIN sys.query_store_runtime_stats rs_last
  ON p.plan_id = rs_last.plan_id
INNER JOIN sys.query_store_runtime_stats_interval i_last
  ON rs_last.runtime_stats_interval_id = i_last.runtime_stats_interval_id
WHERE i_first.start_time < DATEADD(HOUR, -24, GETDATE())
  AND i_last.start_time > DATEADD(HOUR, -1, GETDATE())
  AND rs_first.avg_duration > 0
  AND (rs_last.avg_duration - rs_first.avg_duration) /
      rs_first.avg_duration > 0.5
ORDER BY degradation_pct DESC;

-- ============================================
-- Object Management Queries for SSMS
-- Use these instead of GUI clicks for auditable operations
-- ============================================

-- 1. List all tables with row counts and size
SELECT
  s.name AS schema_name,
  t.name AS table_name,
  p.rows AS row_count,
  CAST(SUM(a.total_pages) * 8.0 / 1024 AS DECIMAL(10,2)) AS total_mb,
  CAST(SUM(a.used_pages) * 8.0 / 1024 AS DECIMAL(10,2)) AS used_mb
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.indexes i ON t.object_id = i.object_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
WHERE t.is_ms_shipped = 0
  AND i.type <= 1 -- heap and clustered index only
GROUP BY s.name, t.name, p.rows
ORDER BY total_mb DESC;

-- 2. List all indexes with usage statistics
SELECT
  s.name AS schema_name,
  t.name AS table_name,
  i.name AS index_name,
  i.type_desc AS index_type,
  ius.user_seeks,
  ius.user_scans,
  ius.user_lookups,
  ius.user_updates,
  ius.last_user_seek,
  ius.last_user_scan
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
LEFT JOIN sys.dm_db_index_usage_stats ius
  ON i.object_id = ius.object_id
  AND i.index_id = ius.index_id
  AND ius.database_id be performed through = DB_ID()\ ius.user_updates
ORDER BY index_size_mb DESC;

-- 4. List all stored procedures with last execution time
SELECT
  s.name AS schema_name,
  p.name AS procedure_name,
  p.create_date,
  p.modify_date,
  qs.last_execution_time,
  qs.execution_count,
  qs.total_elapsed_time / 1000.0 / NULLIF(qs.execution_count, 0) AS avg_elapsed_ms
FROM sys.procedures p
INNER JOIN sys.schemas s ON p.schema_id = s.schema_id
LEFT JOIN sys.dm_exec_procedure_stats qs
  ON p.object_id = qs.object_id
  AND qs.database_id = DB_ID()
WHERE p.is_ms_shipped = 0
ORDER BY qs.last_execution_time DESC;

-- 5. Find foreign key relationships
SELECT
  fk.name AS foreign_key_name,
  SCHEMA_NAME(tp.schema_id) AS parent_schema,
  tp.name AS parent_table,
  cp.name AS parent_column,
  SCHEMA_NAME(tr.schema_id) AS referenced_schema,
  tr.name AS referenced_table,
  cr.name AS referenced_column
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc
  ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tp ON fkc.parent_object_id = tp.object_id
INNER JOIN sys.columns cp
  ON fkc.parent_object_id = cp.object_id
  AND fkc.parent_column_id = cp.column_id
INNER JOIN sys.tables tr ON fkc.referenced_object_id = tr.object_id
INNER JOIN sys.columns cr
  ON fkc.referenced_object_id = cr.object_id
  AND fkc.referenced_column_id = cr.column_id
ORDER BY parent_schema, parent_table, foreign_key_name;

-- ============================================
-- Performance Monitoring Queries for SSMS
-- Use with Activity Monitor and Query Store
-- ============================================

-- 1. Current blocking chain (who is blocking whom)
SELECT
  blocking.session_id AS blocker_session_id,
  blocked.session_id AS blocked_session_id,
  blocked.wait_type,
  blocked.wait_time / 1000.0 AS wait_seconds,
  blocked.wait_resource,
  blocking_text.text AS blocker_query,
  blocked_text.text AS blocked_query
FROM sys.dm_exec_requests blocked
INNER JOIN sys.dm_exec_sessions blocking
  ON blocked.blocking_session_id = blocking.session_id
OUTER APPLY sys.dm_exec_sql_text(blocking.most_recent_sql_handle) blocking_text
OUTER APPLY sys.dm_exec_sql_text(blocked.sql_handle) blocked_text
WHERE blocked.blocking_session_id != 0
ORDER BY blocked.wait_time DESC;

-- 2. Top 10 most expensive queries by CPU
SELECT TOP 10
  qs.total_worker_time / 1000.0 AS total_cpu_ms,
  qs.execution_count,
  qs.total_worker_time / 1000.0 /
    NULLIF(qs.execution_count, 0) AS avg_cpu_ms,
  qs.total_logical_reads / NULLIF(qs.execution_count, 0) AS avg_logical_reads,
  qs.total_elapsed_time / 1000.0 /
    NULLIF(qs.execution_count, 0) AS avg_elapsed_ms,
  SUBSTRING(st.text,
    (qs.statement_start_offset / 2) + 1,
    ((CASE qs.statement_end_offset
      WHEN -1 THEN DATALENGTH(st.text)
      ELSE qs.statement_end_offset
    END - qs.statement_start_offset) / 2) + 1
  ) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY total_cpu_ms DESC;

-- 3. Wait statistics breakdown (what SQL Server waits for)
SELECT TOP 15
  wait_type,
  wait_time_ms / 1000.0 AS wait_seconds,
  waiting_tasks_count,
  CAST(100.0 * wait_time_ms / SUM(wait_time_ms) OVER()
    AS DECIMAL(5,2)) AS wait_pct,
  CASE wait_type
    WHEN 'CXPACKET' THEN 'Parallelism — check MAXDOP and cost threshold'
    WHEN 'PAGEIOLATCH_SH' THEN 'Data file I/O — check disk and memory'
    WHEN 'PAGEIOLATCH_EX' THEN 'Data file write I/O — check disk speed'
    WHEN 'WRITELOG' THEN 'Transaction log I/O — check log disk speed'
    WHEN 'LCK_M_S' THEN 'Shared lock blocked — check blocking queries'
    WHEN 'LCK_M_X' THEN 'Exclusive lock blocked — long transactions'
    WHEN 'SOS_SCHEDULER_YIELD' THEN 'CPU pressure — queries yielding scheduler'
    WHEN 'ASYNC_NETWORK_IO' THEN 'Client not consuming results fast enough'
    ELSE 'Investigate further'
  END AS diagnosis
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%'
  AND wait_type NOT LIKE '%IDLE%'
  AND wait_type NOT LIKE '%BROKER%'
  AND wait_time_ms > 0
ORDER BY wait_time_ms DESC;

-- 4. Memory usage by database
SELECT
  DB_NAME(database_id) AS database_name,
  COUNT(*) * 8 / 1024 AS cached_mb,
  COUNT(CASE WHEN is_modified = 1 THEN 1 END) * 8 / 1024 AS dirty_mb,
  COUNT(CASE WHEN is_modified = 0 THEN 1 END) * 8 / 1024 AS clean_mb
FROM sys.dm_os_buffer_descriptors
WHERE database_id > 4 -- exclude system databases
GROUP BY database_id
ORDER BY cached_mb DESC;

-- 5. Index fragmentation (needs periodic maintenance)
SELECT
  s.name AS schema_name,
  t.name AS table_name,
  i.name AS index_name,
  ips.avg_fragmentation_in_percent,
  ips.page_count,
  CASE
    WHEN ips.avg_fragmentation_in_percent < 5 THEN 'No action needed'
    WHEN ips.avg_fragmentation_in_percent < 30 THEN 'ALTER INDEX REORGANIZE'
    ELSE 'ALTER INDEX REBUILD'
  END AS recommended_action
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
INNER JOIN sys.indexes i
  ON ips.object_id = i.object_id AND ips.index_id = i.index_id
INNER JOIN sys.tables t ON i.object_id = t.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE ips.avg_fragmentation_in_percent > 5
  AND ips.page_count > 1000
ORDER BY ips.avg_fragmentation_in_percent DESC;

-- 6. Database file I/O statistics
SELECT
  DB_NAME(mf.database_id) AS database_name,
  mf.name AS file_name,
  mf.type_desc AS file_type,
  vfs.num_of_reads,
  vfs.num_of_writes,
  vfs.io_stall_read_ms / 1000.0 AS read_stall_sec,
  vfs.io_stall_write_ms / 1000.0 AS write_stall_sec,
  CAST(vfs.io_stall_read_ms /
    NULLIF(vfs.num_of_reads, 0) AS DECIMAL(10,2)) AS avg_read_ms,
  CAST(vfs.io_stall_write_ms /
    NULLIF(vfs.num_of_writes, 0) AS DECIMAL(10,2)) AS avg_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
INNER JOIN sys.master_files mf
  ON vfs.database_id = mf.database_id
  AND vfs.file_id = mf.file_id
ORDER BY vfs.io_stall_read_ms + vfs.io_stall_write_ms DESC;

// io.thecodeforge — database tutorial

-- Validate SSMS binary exists before blaming the connection
-- Run from cmd.exe, not PowerShell to avoid path length bugs
sqlcmd -S ProductionServer\PROD01 -E -Q "SELECT @@SERVERNAME AS IsAlive;"

-- Output if SSMS GUI fails but core client works:
-- IsAlive
-- PRODUCTION-SRV-01\PROD01

// io.thecodeforge — database tutorial

-- Launch SSMS directly to the incident database with the query you need
-- Save this as a .bat file on your jump box
@echo off
SET SRV=ProdCluster
SET DB=Orders
SET QUERY=C:\Runbooks\DeadlockInvestigation.sql
start "" "C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Ssms.exe" -S %SRV% -d %DB% -E -nosplash -query %QUERY%

// io.thecodeforge — database tutorial

-- Test TCP port availability from SSMS context
SELECT DISTINCT local_tcp_port 
FROM sys.dm_exec_connections 
WHERE session_id = @@SPID;

-- Check if SQL Server is listening
EXEC xp_readerrorlog 0, 1, 'Server is listening';

-- Quick login mode check
SELECT CASE 
    WHEN SERVERPROPERTY('IsIntegratedSecurityOnly') = 1 
    THEN 'Windows Only'
    ELSE 'Mixed Mode'
END AS AuthenticationMode;

// io.thecodeforge — database tutorial

DECLARE @OrderCount INT;

SELECT @OrderCount = COUNT(*)
FROM Sales.Orders WITH (NOLOCK)
WHERE OrderDate >= '2024-01-01';

IF @OrderCount > 1000
BEGIN
    PRINT 'High volume detected. Running full analysis...';
    
    SELECT ProductID, SUM(Quantity) AS TotalSold
    FROM Sales.OrderDetails
    GROUP BY ProductID
    ORDER BY TotalSold DESC;
END
ELSE
    PRINT 'Low volume. Skipping detailed analysis.';