--所有没有主键的表select name from sysobjects where xtype='U' and id not in(select i.parent_obj from sysobjects iwhere i.xtype='PK' ) order by name
--逻辑读最多的语句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;
--总耗CPU最多的前个SQL:SELECT TOP 50 total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数], qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)], min_worker_time /1000 AS [最小执行时间(ms)], max_worker_time /1000 AS [最大执行时间(ms)],last_execution_time AS [最后一次执行时间], SUBSTRING(qt.text,qs.statement_start_offset/2+1, (CASE WHEN qs.statement_end_offset = -1 THEN DATALENGTH(qt.text) ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1) AS [使用CPU的语法], dbname=db_name(qt.dbid), object_name(qt.objectid,qt.dbid) ObjectNameFROM sys.dm_exec_query_stats qs WITH(nolock)CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qtWHERE execution_count>1 and (db_name(qt.dbid)='xxxx' or db_name(qt.dbid) is null)--and (qs.total_worker_time/qs.execution_count/1000)>300and last_execution_time>'2015-05-12'ORDER BY total_worker_time desc,(qs.total_worker_time/qs.execution_count/1000) desc
--查询用户表的索引SELECT c.name as 表名 ,a.name as 索引名称,d.name as 索引字段名 ,d.colid as 索引字段位置 ,case d.xusertype when 104 then '索引键列' else '包含性列' end 列类型FROM sysindexes a JOIN sysindexkeys b ON a.id=b.id AND a.indid=b.indid JOIN sysobjects c ON b.id=c.id JOIN syscolumns d ON b.id=d.id AND b.colid=d.colid WHERE a.indid NOT IN(0,255) and c.xtype='U'AND c.name='BillDetail' --查指定表 ORDER BY c.name,a.name
--当前库所有表的记录数select a.name as 表名,max(b.rows) as 记录条数 from sysobjects a ,sysindexes b where a.id=b.id and a.xtype='u' group by a.name order by max(b.rows) desc