SQL Server Tuning Common SQL statements

--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