DB处理方案（dbe处理）

DB处理方案（dbe处理）

SQL Server故障对应流程

一、服务器性能1、查看当前SQL服务器的CPU和内存情况，看看sql server.exe进程是否占用CPU 和内存2、确认数据库备份是否在进行3、确认windows计划任务是否在执行二、服务器的日志1.服务器本身的日志是否异常2.确认 sql server 的日志是否异常三、利用SQL Server的分析工具分析问题的办法：1.利用系统视图初步分析问题。查询各数据库的运行情况SELECT * FROM sys.[sysprocesses] WHERE [spid]>50 ORDER BY DB_NAME([dbid]) DESC查询最耗时的会话：SELECT TOP 20 [session_id], [request_id], [start_time] AS '开始时间', [status] AS '状态', [command] AS '命令', dest.[text] AS 'sql语句', DB_NAME([database_id]) AS '数据库名', [blocking_session_id] AS '正在阻塞其他会话的会话ID', [wait_type] AS '等待资源类型', [wait_time] AS '等待时间', [wait_resource] AS '等待的资源', [reads] AS '物理读次数', [writes] AS '写次数', [logical_reads] AS '逻辑读次数', [row_count] AS '返回结果行数' FROM sys.[dm_exec_requests] AS der CROSS APPLY sys.​​dm_exec_sql_text​​ AS dest

WHERE [session_id]>50 --AND DB_NAME(der.[database_id])='TimeSheet' ORDER BY DB_NAME([dbid]),[cpu_time] DESC查询最耗时的语句：SELECT TOP 20 DEST.Text AS 'SQL语句' FROM sys.[dm_exec_requests] AS Requests CROSS APPLY sys.dm_exec_sql_text(Requests.[sql_handle]) AS DEST WHERE [Session_ID]>50 ORDER BY [CPU_Time] DESC查询SQL Server启动以来累计使用CPU资源最多的语句。SELECT T.Last_Execution_Time, T.Execution_Count, T.total_logical_reads, T.total_logical_writes, T.total_elapsed_time, T.last_elapsed_time, Plan_Handle.[text] FROM (SELECT top 50 Query_Stats.* FROM sys.dm_exec_query_stats AS Query_Stats ORDER BY Query_Stats.Total_Worker_Time DESC ) T CROSS APPLY sys.dm_exec_sql_text(plan_handle) Plan_Handle ORDER BY T.Total_Worker_Time DESC查询最常重编译的存储过程SELECT TOP 50 SQL_Text.Text, SQL_Handle, Plan_Generation_Num, Execution_Count, DBID, Objectid FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS SQL_Text WHERE Plan_Generation_Num >1 ORDER BY Plan_Generation_Num DESC查询引起阻塞的问题 ：CREATE procedure [dbo].[USP_Who_Lock]AS begin declare @spid int declare @blk int declare @count int declare @index int declare @lock tinyint set @lock=0 create table #temp_who_lock ( id int identity(1,1), spid int, blk int ) if @@error<>0 return @@error insert into #temp_who_lock(spid,blk) select 0 ,blocked from (select * from master..sysprocesses where blocked>0)a where not exists(select * from master..sysprocesses where a.blocked =spid and blocked>0) union select spid,blocked from master..sysprocesses where blocked>0 if @@error<>0 return @@error select @count=count(*),@index=1 from #temp_who_lock if @@error<>0 return @@error if @count=0 begin select '没有阻塞和死锁信息' return 0 end while @index<=@count begin if exists(select 1 from #temp_who_lock a where id>@index and exists(select 1 from #temp_who_lock where id<=@index and a.blk=spid)) begin set @lock=1 select @spid=spid,@blk=blk from #temp_who_lock where id=@index select '引起数据库死锁的是: '+ CAST(@spid AS VARCHAR(10)) + '进程号,其执行的SQL语法如下' select @spid, @blk dbcc inputbuffer(@spid) dbcc inputbuffer(@blk) end set @index=@index+1 end if @lock=0 begin set @index=1 while @index<=@count begin select @spid=spid,@blk=blk from #temp_who_lock where id=@index if @spid=0 select '引起阻塞的是:'+cast(@blk as varchar(10))+ '进程号,其执行的SQL语法如下' else select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@blk AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下' dbcc inputbuffer(@spid) dbcc inputbuffer(@blk) set @index=@index+1 end end drop table #temp_who_lock return 0 end

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。