sql server異常 以及系統(tǒng) 函數(shù)(2)

1、 Sql server 數(shù)據(jù)庫異常 數(shù)據(jù)庫對象異常 常見數(shù)據(jù)庫對象主要包括：數(shù)據(jù)庫 表（able ） 索引（Index ） 視圖（View）圖表（Diagram） 缺省值（default）規(guī)則（Rule）觸發(fā)器（Trigger）存儲過程（Stored procedure）用戶（User）數(shù)據(jù)庫監(jiān)視服務(wù)器運(yùn)行情況查看當(dāng)前緩存哪些批處理或過程占用了大部分的CPU資源使用sql語句SELECT TOP 50 SUM(qs.total_worker_time) AS total_cpu_time,SUM(qs.execution_count) AS total_execution_count,COUN

2、T(*) AS number_of_statements,qs.sql_handle FROM sys.dm_exec_query_stats AS qs GROUP by qs.sql_handle ORDER BY SUM(qs.total_worker_time) DESC運(yùn)行結(jié)果2000無效對象名 sys.dm_exec_query_stats 無效。運(yùn)行得到的數(shù)據(jù)項 total_cup_time 總工作時間total_execution_count 執(zhí)行行數(shù)number_of_statements, 查詢行數(shù)sql_handle 不解（sql 執(zhí)行？）查詢緩存計劃所占用的CPU總是用

3、率（帶sql文本）執(zhí)行以下sql語句SELECT total_cpu_time, total_execution_count, number_of_statements, s2.text -(SELECT SUBSTRING(s2.text, statement_start_offset / 2, (CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), s2.text) * 2) ELSE statement_end_offset END) - statement_start_offset) / 2) ) AS

4、 query_text FROM (SELECT TOP 50 SUM(qs.total_worker_time) AS total_cpu_time, SUM(qs.execution_count) AS total_execution_count, COUNT(*) AS number_of_statements, qs.sql_handle -, -MIN(statement_start_offset) AS statement_start_offset, -MAX(statement_end_offset) AS statement_end_offset FROM sys.dm_exe

5、c_query_stats AS qs GROUP BY qs.sql_handle ORDER BY SUM(qs.total_worker_time) DESC) AS stats CROSS APPLY sys.dm_exec_sql_text(stats.sql_handle) AS s2運(yùn)行的到的結(jié)果2000報錯第 19 行: APPLY 附近有語法錯誤。運(yùn)行得到的數(shù)據(jù)項total_cup_time 總工作時間total_execution_count 執(zhí)行行數(shù)number_of_statements, 查詢行數(shù)text 執(zhí)行的sql文本 查詢顯示哪個查詢占用了最多的 CPU 累計使

6、用率。 SELECT highest_cpu_queries.plan_handle, highest_cpu_queries.total_worker_time, q.dbid, q.objectid, q.number, q.encrypted, q.textfrom (select top 50 qs.plan_handle, qs.total_worker_time from sys.dm_exec_query_stats qs order by qs.total_worker_time desc) as highest_cpu_queries cross apply sys.dm_e

7、xec_sql_text(plan_handle) as qorder by highest_cpu_queries.total_worker_time desc2000錯誤第 16 行: apply 附近有語法錯誤。參考資料地址 參考 除CPU外還有IO和內(nèi)存的瓶頸數(shù)據(jù)庫異常問題數(shù)據(jù)庫空間問題： 使用語句：exec sp_MSforeachtable exec sp_spaceused 或者Exec sp_spaceused可以得到數(shù)據(jù)庫大小信息 執(zhí)行后得到數(shù)據(jù)如下 Sql 2000可以運(yùn)行運(yùn)行sql語句得到的數(shù)據(jù)項Database_base 庫名稱Datanase_size 庫大小unal

8、locatedspace 未分配空間 ; 各磁盤剩余磁盤大小 剩余磁盤大小 Exec master.dbo.xp_fixeddrives： 運(yùn)行該語句得到的數(shù)據(jù)是 2000 可用運(yùn)行得到的數(shù)據(jù)項Drive :磁盤盤符MB可用空間： 對應(yīng)磁盤剩余的可用空間得到數(shù)據(jù)庫所在磁盤的剩余空間的大小 declare drivename char(1)select drivename=left(filename,1) from sysfiles where fileid= 1if not exists(select 1 from tempdb.dbo.sysobjects where name like #

9、FreeSpace% and type=U) create table #FreeSpace( Drive char(1), MB_Free int )else truncate table #FreeSpace insert into #FreeSpace exec xp_fixeddrivesselect MB_Free from #FreeSpace where Drive = drivenamego執(zhí)行以上sql語句可直接得到數(shù)據(jù)庫所在磁盤的剩余空間的大??；如圖2000可用運(yùn)行得到的數(shù)據(jù)項 MB_Free：數(shù)據(jù)庫所在盤符剩余空間；數(shù)據(jù)庫死鎖問題死鎖的四個必要條件：互斥條件(Mutual

10、 exclusion)：資源不能被共享，只能由一個進(jìn)程使用。請求與保持條件(Hold and wait)：已經(jīng)得到資源的進(jìn)程可以再次申請新的資源。非剝奪條件(No pre-emption)：已經(jīng)分配的資源不能從相應(yīng)的進(jìn)程中被強(qiáng)制地剝奪。循環(huán)等待條件(Circular wait)：系統(tǒng)中若干進(jìn)程組成環(huán)路，該環(huán)路中每個進(jìn)程都在等待相鄰進(jìn)程正占用的資源。目前找到排除死鎖的方法未經(jīng)測試CREATETable#Who(spidint,ecidint,statusnvarchar(50),loginnamenvarchar(50),hostnamenvarchar(50),blkint,dbnamenva

11、rchar(50),cmdnvarchar(50),request_IDint);CREATETable#Lock(spidint,dpidint,objidint,indldint,Typenvarchar(20),Resourcenvarchar(50),Modenvarchar(10),Statusnvarchar(10);INSERTINTO#WhoEXECsp_whoactive-看哪個引起的阻塞，blkINSERTINTO#LockEXECsp_lock-看鎖住了那個資源id，objidDECLAREDBNamenvarchar(20);SETDBName=NameOfDataBa

12、seSELECT#Who.*FROM#WhoWHEREdbname=DBNameSELECT#Lock.*FROM#LockJOIN#WhoON#Who.spid=#Lock.spidANDdbname=DBName;-最后發(fā)送到SQLServer的語句DECLAREcrsrCursorFORSELECTblkFROM#WhoWHEREdbname=DBNameANDblk0;DECLAREblkint;opencrsr;FETCHNEXTFROMcrsrINTOblk;WHILE(FETCH_STATUS=0)BEGIN;dbccinputbuffer(blk);FETCHNEXTFROMc

13、rsrINTOblk;END;closecrsr;DEALLOCATEcrsr;-鎖定的資源SELECT#Who.spid,hostname,objid,type,mode,object_name(objid)asobjNameFROM#LockJOIN#WhoON#Who.spid=#Lock.spidANDdbname=DBNameWHEREobjid0;DROPTable#Who;DROPTable#Lock;執(zhí)行結(jié)果待解釋中內(nèi)容不解表異常問題 目前只有解決方法常用系統(tǒng)存儲過程1 sp_helpdb:報告數(shù)據(jù)庫的信息execute sp_helpdb -查看所有數(shù)據(jù)庫信息2000可用ex

14、ecute sp_helpdb 數(shù)據(jù)庫名-查看當(dāng)前數(shù)據(jù)庫信息2000可用2 sp_help:查看某個數(shù)據(jù)庫中表的信息execute sp_help -查看某個數(shù)據(jù)庫中表的所有信息2000可用execute sp_help 表名-查看某個數(shù)據(jù)庫中單個表的信息2000可用3 sp_helpfile:查看當(dāng)前數(shù)據(jù)庫文件的.mdf和.ldf的位置 execute sp_helpfile運(yùn)行結(jié)果：2000可用4 sp_helpindex:查看某個表的索引 execute sp_helpindex 表名2000可用5 sp_helpconstraint:查看某個表的約束 execute sp_helpconstraint 表名2000可用6 sp_stored_procedures:返回當(dāng)前數(shù)據(jù)庫中的存儲過程的列表.execute sp_stored_procedures2000可用7 sp_tables:查看當(dāng)前環(huán)境下可查詢的對象的列表 execute sp_tables execute sp_tables 表名2000可用8 sp_columns:查看表中列的信息execute sp_columns 表名2000可用9 sp_databases:列出服務(wù)器上所有的數(shù)