Sql Server常用的幾種分析性能、死鎖、資源損耗的語句

/檢視對象定義/

sp_executesql N'IF OBJECT_ID(@name) IS NOT NULL IF EXISTS(SELECT * FROM sys.objects where name=''''+@name+N'''' and type=''U'') EXEC(N''SP_HELP ''+@name)else EXEC(N''SP_HELPTEXT ''+@name)else print''該對象在資料庫中不存在''',N'@name nvarchar(100),@n int=1000',      

/檢視表占用空間/

create table ##Data(name varchar(100),row varchar(100),reserved varchar(100),data varchar(100),index_size varchar(100),unused varchar(100));declare @name varchar(100);declare cur cursor for select name from sysobjects where xtype='u' order by name;open cur;fetch next from cur into @name;while @@fetch_status=0 begin insert into ##data exec sp_spaceused @name;print @name;fetch next from cur into @name;end;close cur;deallocate cur;create table ##DataNew(表名 varchar(100),行數 int,保留白間 int,占用空間 int,索引占用 int,未使用 int);insert into ##dataNew select name,convert(int,row) as row,convert(int,replace(reserved,'KB','')) as reserved,convert(int,replace(data,'KB','')) as data,convert(int,replace(index_size,'KB','')) as index_size,convert(int,replace(unused,'KB','')) as unused from ##data;select * from ##dataNew order by 占用空間 desc,行數 desc;drop table ##Data;drop table ##dataNew      

/檢視前1000行/

sp_executesql N'IF OBJECT_ID(@tablename) IS NOT NULL EXEC(N''SELECT TOP(''+@n+N'') * FROM ''+@tablename)',N'@tablename nvarchar(100),@n int=1000',      

/死鎖清除/

select 'kill '+ convert(varchar(20),request_session_id) 程序ID, OBJECT_NAME(resource_associated_entity_id) 表名 from sys.dm_tran_locks where resource_type='OBJECT' AND OBJECT_NAME(resource_associated_entity_id) IS NOT NULL      

/慢語句監控/

SELECT top 1000 creation_time N'語句編譯時間' ,last_execution_time N'上次執行時間' ,total_physical_reads N'實體讀取總次數' ,total_logical_reads/execution_count N'每次邏輯讀次數' ,total_logical_reads N'邏輯讀取總次數' ,total_logical_writes N'邏輯寫入總次數' ,execution_count N'執行次數' ,total_worker_time/1000 N'所用的CPU總時間ms' ,total_elapsed_time/1000 N'總花費時間ms' ,(total_elapsed_time / execution_count)/1000 N'平均時間ms' ,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) N'執行語句' FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st where 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) not like '%fetch%' ORDER BY total_elapsed_time / execution_count DESC      

sp_spaceused      

sp_executesql N'SELECT columns=STUFF((SELECT '', ''+name FROM sys.columns WHERE object_id = OBJECT_ID(@tablename) FOR XML PATH('''')),1,2,'''')',N'@tablename nvarchar(100)' ,      

sp_executesql N'SELECT name 名稱, case type when ''U'' then ''表'' when ''P'' then ''存儲過程'' when ''V'' then ''視圖'' when ''FN'' then ''函數'' when ''S'' then ''系統表'' when ''TR'' then ''觸發器'' when ''TF'' then ''表值函數'' else ''其它：''+type_desc end 類型 FROM sys.objects WHERE type IN (''U'',''V'',''P'',''FN'',''TF'') AND name LIKE ''%''+@keyword+''%'' ORDER BY 類型,name',N'@keyword nvarchar(50)',