/*--调用示例
SQLServer查询死锁语句
exec p_lockinfo
--*/
create proc p_lockinfo
@kill_lock_spid bit=1, --是否杀掉死锁的进程,1 杀掉, 0 仅显示
@show_spid_if_nolock bit=1 --如果没有死锁的进程,是否显示正常进程信息,1 显示,0 不显示
as
declare @count int,@s nvarchar(1000),@i int
select id=identity(int,1,1),标志,
进程ID=spid,线程ID=kpid,块进程ID=blocked,数据库ID=dbid,
数据库名=db_name(dbid),用户ID=uid,用户名=loginame,累计CPU时间=cpu,
登陆时间=login_time,打开事务数=open_tran, 进程状态=status,
工作站名=hostname,应用程序名=program_name,工作站进程ID=hostprocess,
域名=nt_domain,网卡地址=net_address
into #t from(
select 标志='死锁的进程',
spid,kpid,a.blocked,dbid,uid,loginame,cpu,login_time,open_tran,
status,hostname,program_name,hostprocess,nt_domain,net_address,
s1=a.spid,s2=0
from master..sysprocesses a join (
select blocked from master..sysprocesses group by blocked
)b on a.spid=b.blocked where a.blocked=0
union all
select '|_牺牲品_>',
spid,kpid,blocked,dbid,uid,loginame,cpu,login_time,open_tran,
status,hostname,program_name,hostprocess,nt_domain,net_address,
s1=blocked,s2=1
from master..sysprocesses a where blocked<>0
)a order by s1,s2
select @count=@@rowcount,@i=1
if @count=0 and @show_spid_if_nolock=1
begin
insert #t
select 标志='正常的进程',
spid,kpid,blocked,dbid,db_name(dbid),uid,loginame,cpu,login_time,
open_tran,status,hostname,program_name,hostprocess,nt_domain,net_address
from master..sysprocesses
set @count=@@rowcount
end
if @count>0
begin
create table #t1(id int identity(1,1),a nvarchar(30),b Int,EventInfo nvarchar(255))
if @kill_lock_spid=1
begin
declare @spid varchar(10),@标志 varchar(10)
while @i<=@count
begin
select @spid=进程ID,@标志=标志 from #t where id=@i
insert #t1 exec('dbcc inputbuffer('+@spid+')')
if @标志='死锁的进程' exec('kill '+@spid)
set @i=@i+1
end
end
else
while @i<=@count
begin
select @s='dbcc inputbuffer('+cast(进程ID as varchar)+')' from #t where id=@i
insert #t1 exec(@s)
set @i=@i+1
end
select a.*,进程的SQL语句=b.EventInfo
from #t a join #t1 b on a.id=b.id
end
go
--查询SP
/*--处理死锁
查看当前进程,或死锁进程,并能自动杀掉死进程
因为是针对死的,所以如果有死锁进程,只能查看死锁进程
当然,你可以通过参数控制,不管有没有死锁,都只查看死锁进程
*/
Exec p_lockinfo 0,1
其它语句：
--sql server 查询哪条语句引起的锁表 select * from sys.dm_tran_locks Exec sp_LOCK sp_who2 pid(2) dbcc inputbuffer(52)
sql server性能分析--查询死锁的sql语句
--查询死锁和阻塞的sql语句
SELECT CAST(REPLACE(REPLACE(XEventData.XEvent.value('(data/value)[1]',
'varchar(max)'),
'<victim-list>', '<deadlock><victim-list>'),
'<process-list>', '</victim-list><process-list>') AS XML) AS DeadlockGraph
FROM ( SELECT CAST(target_data AS XML) AS TargetData
FROM sys.dm_xe_session_targets st
JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
WHERE [name] = 'system_health'
) AS Data
CROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS XEventData ( XEvent )
WHERE XEventData.XEvent.value('@name', 'varchar(8000)') = 'xml_deadlock_report' ;
SQL Server 性能分析
数据库系统异常排查之DMV（转)
摘要: 来源： http://www.cnblogs.com/fygh/archive/2012/03/12.html 数据库系统异常是DBA经常要面临的情景，一名有一定从业经验的DBA，都会有自己一套故障排查的方法和步骤，此文为为大家介绍一下通过系统 性能视图（SQLServer05以上版本）来排查系统异常的基本方法，希望能对大家有所帮助。 这里分两部分来介绍： 一. 从数据库连接情况来判断异常： 1. 首先我们来看一下目前数据库系统所有请求情况： --request infoselect s.session_id, s.status,db_name(r.database_id) as da...
阅读全文
posted @ 2012-03-16 17:23 zping 阅读(35) | 评论 (0) 编辑
处理“远程主机强迫关闭了一个现有的连接”
摘要: 最近，数据库系统经常出现“ 消息[298] SQLServer Error: 10054, TCP Provider: 远程主机强迫关闭了一个现有的连接。 [SQLSTATE 08S01]” 一开始很迷惑，不知道原因，后来查询资料，发现max worker thread设置问题，由于前期max worker thread设置为255，连接过小，修改成0
阅读全文
posted @ 2011-12-05 16:29 zping 阅读(176) | 评论 (0) 编辑
批量导出表索引
摘要: 批量导出索引： SELECT'CREATEINDEX'+[name]+'ON['+OBJECT_NAME(object_id)+']('+REVERSE(SUBSTRING(REVERSE((SELECTname+CASEWHENsc.is_descending_key=1THEN'DESC'ELSE'ASC'END+','FROMsys.index_columnsscJOINsys.columnscONsc.object_id=c.object_idANDsc.column_id=c.column
阅读全文
posted @ 2011-11-11 16:03 zping 阅读(94) | 评论 (0) 编辑
SQL Server 监控锁定信息
摘要: 查看当前数据库锁定信息: declare@tbaTABLE(spidint,dbidINT,objectidINT,indldINT,TYPEVARCHAR(20),resourceVARCHAR(50),modeVARCHAR(20),statusVARCHAR(10))insertinto@tbaexecsp_lockselectspid,OBJECT_name(objectid)tab,TYPE,resource,mode,statusfrom@tbaWHEREdbid=DB_ID()andOBJECT_name(objectid)<>''ORDERBY2
阅读全文
posted @ 2011-10-12 14:27 zping 阅读(106) | 评论 (0) 编辑
查询表的使用空间和可用空间
摘要: 查询表的使用空间和可用空间 setnocountonexecsp_MSForEachTable@precommand=N'createtable##(idintidentity,表名sysname,字段数int,记录数int,保留空间varchar(20),使用空间varchar(20),索引使用空间varchar(20),未用空间varchar(20))',@command1=N'insert##(表名,记录数,保留空间,使用空间,索引使用空间,未用空间)execsp_spaceused''?''update##set字段数=(sele
阅读全文
posted @ 2011-07-06 16:38 zping 阅读(141) | 评论 (0) 编辑
查询长事务和SQL执行等待间隔时间
摘要: 通过SQL Profile跟踪SQL，并将跟踪的sql存入表中，运行下列语句就可以获取长事务的id，在EXCEL中通过TransactionID 筛选就可以得到事务的sql和运行时间。selectTransactionID[事务编号],count(*)[SQL条数],datediff(second,min(StartTime),max(EndTime))[事务时间S]fromyywhereTransactionIDisnotnullgroupbyTransactionIDorderby3 SQL执行等待间隔时间SELECTt.TransactionID事务ID,DATEDIFF(s,t.En.
阅读全文
posted @ 2011-03-31 15:37 zping 阅读(221) | 评论 (0) 编辑
查询SQL Server存储过程的执行信息
摘要: View Code createVIEWview_job_infoasSELECTb.[name][Job名称],CASEWHENb.enabled=1THEN'启用'ELSE'禁用'END[是否启用],a.step_name[步骤名称],a.commandFROMmsdb.dbo.sysjobstepsaINNERJOINmsdb.dbo.sysjobsbONa.job_id=b.job_idSELECTb.*,OBJECT_NAME(object_id,database_id)存储过程名,d.cached_time编译时间,d.last_execution_
阅读全文
posted @ 2011-02-24 17:48 zping 阅读(200) | 评论 (0) 编辑
SQL Server 查询Job中的存储过程
摘要: SQL中一旦job比较多，里面执行的存储过程，就不知道是有哪些，下列语句可以用来查询Job的存储过程，将'%msdb%'替换成你要查的存储过程名称代码Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/-->SELECTb.[name][Job名称],CASEWHENb.enabled=1THEN'启用'ELSE'禁用'END[是否启用],b.description[Job描述],a.step_name[步骤名称],a.command[执行语句]FROMms
阅读全文
posted @ 2011-01-28 11:20 zping 阅读(254) | 评论 (0) 编辑
使用SQL Server动态管理视图确认缺失索引(转)
摘要: 由于有了很多新功能，我们可以看到在SQL Server 2005和现在有SQL Server 2008中，Microsoft引进了一些动态管理视图来协助确认基于查询历史的可能索引候选人。 这些动态管理视图是： n sys.dm_db_missing_index_details –返回关于缺失索引的详细信息。 n sys.dm_db_missing_index_group_stats - 返回缺失索引组的摘要信息 n sys.dm_db_missing_index_groups – 返回一个具体组的缺失索引的信息。 n sys.dm_db_missing_index_columns(
阅读全文
posted @ 2011-01-27 15:13 zping 阅读(159) | 评论 (0) 编辑
sql server 清理日志存储过程
摘要: 代码Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/-->ALTERPROC[dbo].[CleanTranLog]ASBEGINDECLARE@numTINYINT--执行次数DECLARE@backLogNameVARCHAR(100);--备份日志文件名称DECLARE@backLogPathVARCHAR(100);--备份日志文件的路径SET@num=0;SET@backLogPath=N'C:\SQLBackup';--设定备份日志的路径--
阅读全文
posted @ 2011-01-25 09:33 zping 阅读(134) | 评论 (0) 编辑
sql 表分区信息查看
摘要: 代码Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/-->createprocedure[dbo].[sp_show_partition_range](@partition_tablenvarchar(255)=null,@partition_functionnvarchar(255)=null)asbeginsetnocountondeclare@function_idintset@function_id=null--get@function_idba
阅读全文
posted @ 2011-01-12 14:02 zping 阅读(140) | 评论 (0) 编辑
统计用户表中表行数，行平均长度，总页数
摘要: --如果指定 0，则使用当前数据库，使用前更新统计信息DBCC UPDATEUSAGE (0)--或者DBCC UPDATEUSAGE ('DB')--DBCC UPDATEUSAGE 对表和聚集索引中 sysindexes 表的 rows、used、reserved 和 dpages 列进行更正。-- 不对非聚集索引的大小信息进行维护--DBCC UPDATEUSAGE 将针对表或索引中的每个分区更正行、已用页、保留页、叶级页和数据页的计数[代码]代码Code highlighting produced by Actipro CodeHighlighter (freeware)http://
阅读全文
posted @ 2011-01-12 09:52 zping 阅读(174) | 评论 (0) 编辑
sql server性能分析--查看表数据页数
摘要: 返回表名、索引名和行数SELECT object_name(i.object_id) as objectName, i.[name] as indexName, sum(p.rows) as rowCntFROM sys.indexes iINNER JOIN sys.partitions pON i.object_id = p.object_idAND i.index_id = p.index_idWHERE i.object_id = object_id('dbo.Meeting')AND i.index_id <= 1GROUP BY i.object_id, i.index_id, i
阅读全文
posted @ 2010-12-20 14:47 zping 阅读(183) | 评论 (0) 编辑
检查SQL Server 2005的索引密度和碎片信息(转)
摘要: 查询数据库中所有表的索引密度和碎片信息，以便为索引的重建和整理提供依据，也可以参考DBCC SHOWCONTIG，通常FRAGMENTATIOIN在30%以上建议重建，否则建议整理SELECT i.name AS indexname, o.name AS tablename, s.name AS schemaname, f.index_type_desc AS indextype, f.avg_p...
阅读全文
posted @ 2009-09-24 13:45 zping 阅读(451) | 评论 (0) 编辑
SQL Server 查看数据库基本信息
posted @ 2008-09-17 16:32 zping 阅读(616) | 评论 (0) 编辑
sql server性能分析--定时收集系统运行情况
posted @ 2008-09-12 14:33 zping 阅读(579) | 评论 (0) 编辑
sql server性能分析--执行sql次数和逻辑次数
posted @ 2008-07-15 13:47 zping 阅读(1786) | 评论 (4) 编辑
sql server性能分析--执行计划重用次数
posted @ 2008-07-15 13:46 zping 阅读(704) | 评论 (0) 编辑
sql server性能分析--索引使用效率评估
posted @ 2008-07-15 13:45 zping 阅读(617) | 评论 (0) 编辑
sql server性能分析--检测数据库阻塞语句
posted @ 2008-06-01 14:58 zping 阅读(967) | 评论 (2) 编辑