• 
  

• 二、查看當(dāng)前正在執(zhí)行的sql語句

• 三、查詢前 10 個(gè)可能是性能最差的 SQL 語句

• 四、查詢邏輯讀取最高的sql

• 五、 查詢索引碎片

• 六、修改索引填充因子

• 七、查詢未使用過的索引

• 八、查詢表下索引使用情況

• 九、查詢表結(jié)構(gòu)信息

一、查看是否有死鎖

dECLARE ?@tab TABLE(NAME varchar(100),value varchar(200)); INSERT INTO @tab EXEC('DBCC OPENTRAN WITH TABLERESULTS'); SELECT name,CAST(value AS DATETIME) startDate,getdate() currentDate ,DATEDIFF(s,CAST(value AS DATETIME),getdate()) diffsecond FROM @tab WHERE name in ('OLDACT_STARTTIME') SELECT ? spid, ? ? ? ? blocked, ? ? ? ? DB_NAME(sp.dbid) AS DBName, ? ? ? ? program_name, ? ? ? ? waitresource, ? ? ? ? lastwaittype, ? ? ? ? sp.loginame, ? ? ? ? sp.hostname, ? ? ? ? a.[Text] AS [TextData], ? ? ? ? SUBSTRING(A.text, sp.stmt_start / 2, ? ? ? ? (CASE WHEN sp.stmt_end = -1 THEN DATALENGTH(A.text) ELSE sp.stmt_end ? ? ? ? END - sp.stmt_start) / 2) AS [current_cmd] FROM ? ? sys.sysprocesses AS sp OUTER APPLY sys.dm_exec_sql_text (sp.sql_handle) AS A WHERE ?spid =(SELECT CASE WHEN ISNUMERIC(value)=0 THEN -1 ELSE value end FROM @tab WHERE name in ('OLDACT_SPID') )

二、查看當(dāng)前正在執(zhí)行的sql語句

? ? SELECT ?[Spid] = session_id , ecid , ? ? ? ? ? ?[Database] = DB_NAME(sp.dbid) ,[User] = nt_username , ? ? ? ? ? ?[Status] = er.status , [Wait] = wait_type , ? ? ? ? ? ?[Individual Query] = SUBSTRING(qt.text, ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? er.statement_start_offset / 2, ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ( CASE WHEN er.statement_end_offset = -1 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? * 2 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?ELSE er.statement_end_offset ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? END - er.statement_start_offset ) ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? / 2) , ? ? ? ? ? ?[Parent Query] = qt.text , Program = program_name ,hostname , ?nt_domain , start_time ? ?FROM ? ?sys.dm_exec_requests er ? ? ? ? ? ?INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid ? ? ? ? ? ?CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt ? ?WHERE ? session_id > 50 -- Ignore system spids. ? ? ? ? ? ?AND session_id NOT IN ( @@SPID ) -- Ignore this current statement. ORDER BY ? ?1 , ? ? ? ? ? ?2 --刪除解鎖 KILL 1000 ?--spid

三、查詢前 10 個(gè)可能是性能最差的 SQL 語句

SELECT TOP 10 TEXT AS 'SQL Statement' ? ?,last_execution_time AS 'Last Execution Time' ? ?,(total_logical_reads + total_physical_reads + total_logical_writes) / execution_count AS [Average IO] ? ?,(total_worker_time / execution_count) / 1000000.0 AS [Average CPU Time (sec)] ? ?,(total_elapsed_time / execution_count) / 1000000.0 AS [Average Elapsed Time (sec)] ? ?,execution_count AS "Execution Count" ? ?,qp.query_plan AS "Query Plan" FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp ORDER BY total_elapsed_time / execution_count DESC

四、查詢邏輯讀取最高的sql

SELECT TOP ( 25 ) ? ? ? ?P.name AS [SP Name] , ? ? ? ?Deps.total_logical_reads AS [TotalLogicalReads] , ? ? ? ?deps.total_logical_reads / deps.execution_count AS [AvgLogicalReads] , ? ? ? ?deps.execution_count , ? ? ? ?ISNULL(deps.execution_count / DATEDIFF(SECOND, deps.cached_time, ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? GETDATE()), 0) AS [Calls/Second] , ? ? ? ?deps.total_elapsed_time , ? ? ? ?deps.total_elapsed_time / deps.execution_count AS [avg_elapsed_time] , ? ? ? ?deps.cached_time FROM ? ?sys.procedures AS p ? ? ? ?INNER JOIN sys.dm_exec_procedure_stats AS deps ON p.[Object_id] = deps.[Object_id] WHERE ? deps.Database_id = DB_ID() ORDER BY deps.total_logical_reads DESC

五、 查詢索引碎片

--創(chuàng)建變量 指定要查看的表 declare @table_id int set @table_id=object_id('TableName') --執(zhí)行 dbcc showcontig(@table_id) Logical Scan Fragmentation-邏輯掃描碎片：該百分比應(yīng)該在0％到10％之間，高了則說明有外部碎片。 Extent Scan Fragmentation-擴(kuò)展盤區(qū)掃描碎片：該百分比應(yīng)該是0％，高了則說明有外部碎片。 掃描密度［最佳值:實(shí)際值］：該百分比應(yīng)該盡可能靠近100％。低了則說明有外部碎片。

六、修改索引填充因子

（FILLFACTOR：填充因子,ONLINE:ON 重建索引時(shí)表仍然可以正常使用） --修改表下所有索引填充因子 ALTER INDEX ALL ON TableName REBUILD WITH (FILLFACTOR=90,ONLINE=ON) --修改表下指定索引填充因子 ALTER INDEX indexName ON ?TableName REBUILD WITH (FILLFACTOR = 80); ?

七、查詢未使用過的索引

SELECT ?DB_NAME(diu.database_id) ? ? ? ? ? ? ? ? ?AS DatabaseName , ? ? ? ?s.name +'.' +QUOTENAME(o.name) ? ? ? ? ? ?AS TableName ? ?, ? ? ? ?i.index_id ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?AS IndexID ? , ? ? ? ?i.name ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?AS IndexName ? ? ? ?, ? ? ? ?CASE WHEN i.is_unique =1 THEN 'UNIQUE INDEX' ? ? ? ? ? ELSE 'NOT UNIQUE INDEX' ? ?END ? ? ? ? AS IS_UNIQUE, ? ? ? ?CASE WHEN i.is_disabled=1 THEN 'DISABLE' ? ? ? ? ? ELSE 'ENABLE' ? ? ? ? ? ?END ? ? ? ? ? AS IndexStatus, ? ? ? ?o.create_date ? ? ? ? ? ? ? ? ? ? ? ? ? ? AS IndexCreated, ? ? ? ?STATS_DATE(o.object_id,i.index_id) ? ? ? ?AS StatisticsUpdateDate, ? ? ? ?diu.user_seeks ? ? ? ? ? ? ? ? ? ? ? ? ? ?AS UserSeek , ? ? ? ?diu.user_scans ? ? ? ? ? ? ? ? ? ? ? ? ? ?AS UserScans , ? ? ? ?diu.user_lookups ? ? ? ? ? ? ? ? ? ? ? ? ?AS UserLookups , ? ? ? ?diu.user_updates ? ? ? ? ? ? ? ? ? ? ? ? ?AS UserUpdates , ? ? ? ?p.TableRows , ? ? ? ?'DROP INDEX ' + QUOTENAME(i.name) ? ? ? ?+ ' ON ' + QUOTENAME(s.name) + '.' ? ? ? ?+ QUOTENAME(OBJECT_NAME(diu.object_id)) +';' AS 'Drop Index Statement' FROM ? ?sys.dm_db_index_usage_stats diu ? ? ? ?INNER JOIN sys.indexes i ON i.index_id = diu.index_id ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?AND diu.object_id = i.object_id ? ? ? ?INNER JOIN sys.objects o ON diu.object_id = o.object_id ? ? ? ?INNER JOIN sys.schemas s ON o.schema_id = s.schema_id ? ? ? ?INNER JOIN ( SELECT SUM(p.rows) TableRows , ? ? ? ? ? ? ? ? ? ? ? ? ? ?p.index_id , ? ? ? ? ? ? ? ? ? ? ? ? ? ?p.object_id ? ? ? ? ? ? ? ? ? ? FROM ? sys.partitions p ? ? ? ? ? ? ? ? ? ? GROUP BY p.index_id , ? ? ? ? ? ? ? ? ? ? ? ? ? ?p.object_id ? ? ? ? ? ? ? ? ? ) p ON p.index_id = diu.index_id ? ? ? ? ? ? ? ? ? ? ? ? ?AND diu.object_id = p.object_id WHERE ? OBJECTPROPERTY(diu.object_id, 'IsUserTable') = 1 ? ? ? ?AND diu.database_id = DB_ID() ? ? ? ?AND i.is_primary_key = 0 ? ? ? ?--排除主鍵索引 ? ? ? ?AND i.is_unique_constraint = 0 ? ? ? ? --排除唯一索引 ? ? ? ?AND diu.user_updates <> 0 ? ? ? ? ? ? ?--排除沒有數(shù)據(jù)變化的索引 ? ? ? ?AND diu.user_lookups = 0 ? ? ? ?AND diu.user_seeks = 0 ? ? ? ?AND diu.user_scans = 0 ? ? ? ?AND i.name IS NOT NULL ? ? ? ? ? ? ? ? --排除那些沒有任何索引的堆表 ORDER BY ( diu.user_seeks + diu.user_scans + diu.user_lookups ) ASC,diu.user_updates DESC; GO

八、查詢表下索引使用情況

select db_name(database_id) as N'數(shù)據(jù)庫(kù)名稱', ? ? ? object_name(a.object_id) as N'表名', ? ? ? b.name N'索引名稱', ? ? ? user_seeks N'用戶索引查找次數(shù)', ? ? ? user_scans N'用戶索引掃描次數(shù)', ? ? ? max(last_user_seek) N'最后查找時(shí)間', ? ? ? max(last_user_scan) N'最后掃描時(shí)間', ? ? ? max(rows) as N'表中的行數(shù)' from sys.dm_db_index_usage_stats a join ? ? sys.indexes b ? ? on a.index_id = b.index_id ? ? and a.object_id = b.object_id ? ? join sysindexes c ? ? on c.id = b.object_id where database_id=db_id('數(shù)據(jù)庫(kù)名稱') ? --指定數(shù)據(jù)庫(kù) ? ? and object_name(a.object_id) not like 'sys%' ? ? and object_name(a.object_id) like '表名' ?--指定索引表 ? ? and b.name is not null ? ? --and b.name like '索引名' --指定索引名稱 可以先使用 sp_help '你的表名' 查看表的結(jié)構(gòu)和所有的索引信息 group by db_name(database_id) , ? ? ? object_name(a.object_id), ? ? ? b.name, ? ? ? user_seeks , ? ? ? user_scans order by user_seeks,user_scans,object_name(a.object_id)

九、查詢表結(jié)構(gòu)信息

? ? ? ?SELECT ?表名 = CASE WHEN a.colorder = 1 THEN d.name ? ? ? ? ? ? ? ? ? ? ? ? ?ELSE '' ? ? ? ? ? ? ? ? ? ? END , ? ? ? ? ? ? ? ?表說明 = CASE WHEN a.colorder = 1 THEN ISNULL(f.value, '') ? ? ? ? ? ? ? ? ? ? ? ? ? ELSE '' ? ? ? ? ? ? ? ? ? ? ?END , ? ? ? ? ? ? ? ?字段序號(hào) = a.colorder , ? ? ? ? ? ? ? ?字段名 = a.name , ? ? ? ? ? ? ? ?標(biāo)識(shí) = CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 ? ? ? ? ? ? ? ? ? ? ? ? ?THEN '√' ? ? ? ? ? ? ? ? ? ? ? ? ?ELSE '' ? ? ? ? ? ? ? ? ? ? END , ? ? ? ? ? ? ? ?主鍵 = CASE WHEN EXISTS ( SELECT ?1 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?FROM ? ?sysobjects ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?WHERE ? xtype = 'PK' ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?AND parent_obj = a.id ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?AND name IN ( ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?SELECT ?name ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?FROM ? ?sysindexes ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?WHERE ? indid IN ( ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?SELECT ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?indid ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?FROM ?sysindexkeys ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?WHERE id = a.id ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?AND colid = a.colid ) ) ) ? ? ? ? ? ? ? ? ? ? ? ? ?THEN '√' ? ? ? ? ? ? ? ? ? ? ? ? ?ELSE '' ? ? ? ? ? ? ? ? ? ? END , ? ? ? ? ? ? ? ?類型 = b.name , ? ? ? ? ? ? ? ?占用字節(jié)數(shù) = a.length , ? ? ? ? ? ? ? ?長(zhǎng)度 = COLUMNPROPERTY(a.id, a.name, 'PRECISION') , ? ? ? ? ? ? ? ?小數(shù)位數(shù) = ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) , ? ? ? ? ? ? ? ?允許空 = CASE WHEN a.isnullable = 1 THEN '√' ? ? ? ? ? ? ? ? ? ? ? ? ? ELSE '' ? ? ? ? ? ? ? ? ? ? ?END , ? ? ? ? ? ? ? ?默認(rèn)值 = ISNULL(e.text, '') , ? ? ? ? ? ? ? ?字段說明 = ISNULL(g.[value], '') ? ? ? ?FROM ? ?syscolumns a ? ? ? ? ? ? ? ?LEFT JOIN systypes b ON a.xusertype = b.xusertype ? ? ? ? ? ? ? ?INNER JOIN sysobjects d ON a.id = d.id ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? AND d.xtype = 'U' ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? AND d.name <> 'dtproperties' ? ? ? ? ? ? ? ?LEFT JOIN syscomments e ON a.cdefault = e.id ? ? ? ? ? ? ? ?LEFT JOIN sys.extended_properties g ON a.id = G.major_id ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? AND a.colid = g.minor_id ? ? ? ? ? ? ? ?LEFT JOIN sys.extended_properties f ON d.id = f.major_id ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? AND f.minor_id = 0 ? ? ? ? WHERE ? d.name = 'TableName' ? ?--如果只查詢指定表,加上此紅色where條件，tablename是要查詢的表名；去除紅色where條件查詢說有的表信息 ORDER BY ? ? ? ?a.id , ? ? ? ? ? ? ? ?a.colorder