--all tables without a primary keySelectName fromsysobjectswhereXtype='U' andId not inch(SelectI.parent_obj fromsysobjects iwhereI.xtype='PK' ) Order byName
--total CPU Time-consuming query planSELECT TOP 5Total_worker_time, Last_worker_time, Max_worker_time, Min_worker_time,SUBSTRING(St.text, (Qs.statement_start_offset/ 2)+ 1, (( CaseStatement_end_offset when -1 Then datalength(St.text) ELSEQs.statement_end_offsetEND -Qs.statement_start_offset)/ 2)+ 1) asstatement_text fromSys.dm_exec_query_stats asQS CrossAPPLY sys.dm_exec_sql_text (Qs.sql_handle) asStORDER byTotal_worker_timeDESC
--the most logically read statementsSELECT TOP Ten(Total_logical_reads/Execution_count) asavg_logical_reads, (total_logical_writes/Execution_count) asavg_logical_writes, (total_physical_reads/Execution_count) asavg_phys_reads, Execution_count, (SELECT SUBSTRING(text, Statement_start_offset/ 2 + 1, ( Case whenStatement_end_offset=-1 Then LEN(CONVERT(NVARCHAR(MAX),text))* 2 ELSEStatement_end_offsetEND -Statement_start_offset)/ 2) fromSys.dm_exec_sql_text (sql_handle)) asQuery_text--, Plan_handle,db_name(qp.dbid), Qp.query_plan fromsys.dm_exec_query_stats Crossapply Sys.dm_exec_query_plan (plan_handle) QPORDER by(total_logical_reads+Total_logical_writes)/Execution_countDESC
--indexes that have not been usedSELECTO.nameobject_name --Table name, I.name index_name--Index name, I.type_desc--whether the clustered index, S.user_seeks--number of Index seek, S.user_scans--number of index scan, s.user_updates--number of index update, s.user_lookups--Number of indexed lookup fromSys.objects asoJOINSys.indexes asI onO.object_id =I.object_id Left OUTER JOINSys.dm_db_index_usage_stats ass onI.object_id =S.object_id andi.index_id=s.index_idWHEREO.type= 'u' andI.type_desc<> 'Heap' andUser_seeks=0 andUser_scans=0ORDER by object_name;
SQL Server Tuning Common SQL statements