无SHOWPLAN权限时，如何查看查询统计信息及优化SQL查询？

Great questions—working without SHOWPLAN permission can feel like trying to tune a car without looking under the hood, but there are still plenty of practical ways to gather insights and optimize your queries. Let's dive in:

You don't need the SHOWPLAN permission to get critical performance data. Here are your best options:

• Enable SET STATISTICS TIME, IO ON: This is my go-to trick. It returns detailed IO and CPU metrics for your query without requiring any special permissions. Just run this before your query:

  SET STATISTICS TIME, IO ON;
  GO
  -- Your target query here
  SELECT Column1, Column2 FROM YourTable WHERE FilterColumn = 'YourValue';
  GO
  SET STATISTICS TIME, IO OFF;
  

  Look for high logical reads (indicates missing indexes or inefficient filtering) or excessive CPU time (signals compute-heavy operations like unnecessary functions).

• Query sys.dm_exec_query_stats (if you have limited server access): If your DBA has granted you VIEW SERVER STATE permission (far more common than SHOWPLAN), this DMV lets you inspect cached query statistics. You can see total reads, execution counts, and average CPU time for queries that have run recently:

  SELECT 
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, 
    ((CASE qs.statement_end_offset 
        WHEN -1 THEN DATALENGTH(qt.text) 
        ELSE qs.statement_end_offset 
     END - qs.statement_start_offset)/2)+1) AS query_text,
    qs.total_logical_reads,
    qs.total_worker_time / 1000 AS total_cpu_ms,
    qs.execution_count
  FROM sys.dm_exec_query_stats qs
  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
  WHERE qt.text LIKE '%YourQueryPattern%'; -- Filter to your query
  

• Use Query Store (if enabled): If your database has Query Store turned on, ask your DBA for VIEW QUERY STORE permission. This tool retains historical execution stats and even plan details for all queries. You can use views like sys.query_store_query and sys.query_store_runtime_stats to find slow-performing query versions without needing real-time execution plans.

Even without seeing the plan, you can make smart tweaks to improve query performance:

• Analyze Index Usage: Use sys.dm_db_index_usage_stats (requires VIEW SERVER STATE) to see which indexes are actually being used, and which are just taking up space. Unused indexes slow down writes, so flagging them for removal can help:

  SELECT 
    OBJECT_NAME(s.object_id) AS table_name,
    i.name AS index_name,
    s.user_seeks,
    s.user_scans,
    s.user_lookups
  FROM sys.dm_db_index_usage_stats s
  JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
  WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1;
  

  Look for indexes with user_seeks = 0 and user_scans = 0—these are candidates for deletion.

• Inspect Table Metadata: Get a sense of table size, row counts, and existing indexes to avoid obvious pitfalls. For example, a 10-million-row table with no clustered index will be slow to query without proper filtering:

  SELECT 
    t.name AS table_name,
    SUM(p.rows) AS total_rows,
    SUM(a.total_pages) * 8 / 1024 AS total_size_mb
  FROM sys.tables t
  JOIN sys.indexes i ON t.object_id = i.object_id
  JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
  JOIN sys.allocation_units a ON p.partition_id = a.container_id
  WHERE i.index_id <= 1 -- Clustered index or heap
  GROUP BY t.name;
  

• Avoid Common Anti-Patterns: You don't need a plan to know these hurt performance:

  • Don't use functions on filtered columns (e.g., WHERE YEAR(DateCol) = 2024)—this prevents index usage. Use WHERE DateCol BETWEEN '2024-01-01' AND '2024-12-31' instead.
  • Skip SELECT *—only fetch the columns you need to reduce IO.
  • Replace correlated subqueries with joins where possible; subqueries often lead to repeated lookups.
  • Avoid unnecessary DISTINCT or ORDER BY unless your logic requires them.

• Check for Blocking: Slow queries might be stuck waiting on other sessions. Use sys.dm_exec_requests to find blocking chains:

  SELECT 
    r.session_id,
    r.blocking_session_id,
    SUBSTRING(qt.text, (r.statement_start_offset/2)+1, 
    ((CASE r.statement_end_offset 
        WHEN -1 THEN DATALENGTH(qt.text) 
        ELSE r.statement_end_offset 
     END - r.statement_start_offset)/2)+1) AS blocked_query,
    r.wait_type
  FROM sys.dm_exec_requests r
  CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) qt
  WHERE r.blocking_session_id <> 0;
  

  If you find blocking, work with your DBA to resolve the root cause (e.g., long-running transactions).

• Use SET NOCOUNT ON: While not a performance optimizer, this reduces network traffic by suppressing the "X rows affected" message, which helps when returning large result sets.

内容的提问来源于stack exchange，提问作者Osie J O'Connor