SQL Server最佳化常用SQL語句

標籤：

--所有沒有主鍵的表select name from sysobjects where xtype=‘U‘ and id not in(select i.parent_obj from sysobjects iwhere i.xtype=‘PK‘ ) order by name

--總計耗費CPU時間最長的查詢計劃SELECT TOP 5        total_worker_time       ,last_worker_time       ,max_worker_time       ,min_worker_time       ,SUBSTRING(st.text,(qs.statement_start_offset / 2) + 1,                  ((CASE statement_end_offset                      WHEN -1 THEN DATALENGTH(st.text)                      ELSE qs.statement_end_offset                    END - qs.statement_start_offset) / 2) + 1) AS statement_textFROM    sys.dm_exec_query_stats AS qsCROSS   APPLY sys.dm_exec_sql_text(qs.sql_handle) AS stORDER BY total_worker_time DESC 

--邏輯讀最多的語句SELECT TOP 10        (total_logical_reads / execution_count) AS avg_logical_reads       ,(total_logical_writes / execution_count) AS avg_logical_writes       ,(total_physical_reads / execution_count) AS avg_phys_reads       ,execution_count       ,(SELECT SUBSTRING(text,statement_start_offset / 2 + 1,                          (CASE WHEN statement_end_offset=-1                                THEN LEN(CONVERT(NVARCHAR(MAX),text)) * 2                                ELSE statement_end_offset                           END - statement_start_offset) / 2)         FROM   sys.dm_exec_sql_text(sql_handle)) AS query_text       --,plan_handle       ,db_name(qp.dbid)       ,qp.query_planFROM    sys.dm_exec_query_statscross apply sys.dm_exec_query_plan(plan_handle) qpORDER BY (total_logical_reads + total_logical_writes)/execution_count DESC

--未被使用過的索引SELECT  o.name Object_Name     --表名      , i.name Index_name     --索引名      , i.Type_Desc             --是否叢集索引      , s.user_seeks         --索引seek的次數      , s.user_scans         --索引scan的次數      , s.user_updates         --索引update的次數      , s.user_lookups         --索引lookup的次數FROM    sys.objects AS oJOIN    sys.indexes AS iON      o.object_id = i.object_idLEFT OUTER   JOIN sys.dm_db_index_usage_stats AS sON      i.object_id = s.object_id        AND i.index_id = s.index_idWHERE   o.type = ‘u‘ and i.type_desc <> ‘heap‘and user_seeks=0 and user_scans=0ORDER BY OBJECT_NAME;

 

SQL Server最佳化常用SQL語句