SQLServer筆試題

1、問(wèn)題一、 請(qǐng)用T-SQL實(shí)現(xiàn)查找10萬(wàn)以內(nèi)的質(zhì)數(shù)。問(wèn)題二、 有哪些操作會(huì)使用到TempDB;如果TempDB異常變大，可能的原因是什么，該如何處理;在討論Tempdb空間使用之前，我們先簡(jiǎn)單介紹一下通常什么操作會(huì)大量使用Tempdb。在SQL 2005和SQL 2008里，使用Tempdb空間的遠(yuǎn)遠(yuǎn)不止是臨時(shí)表。常見(jiàn)的使用對(duì)象有：用戶對(duì)象(user_object_reserved_page_count)用戶對(duì)象由用戶顯式創(chuàng)建。這些對(duì)象可以位于用戶會(huì)話的作用域中，也可位于創(chuàng)建對(duì)象所用例程的作用域中。 可以是存儲(chǔ)過(guò)程、觸發(fā)器或用戶定義函數(shù)。 用戶對(duì)象可以是下列項(xiàng)之一：· 用戶定義的表和索

2、引· 系統(tǒng)表和索引· 全局臨時(shí)表和索引· 局部臨時(shí)表和索引· table 變量· 表值函數(shù)中返回的表內(nèi)部對(duì)象（internal_object_reserved_page_count）內(nèi)部對(duì)象是根據(jù)需要由 SQL Server 數(shù)據(jù)庫(kù)引擎創(chuàng)建的，用于處理 SQL Server 語(yǔ)句。 內(nèi)部對(duì)象可以在語(yǔ)句的作用域中創(chuàng)建和刪除。 內(nèi)部對(duì)象可以是下列項(xiàng)之一：· 用于游標(biāo)。· 用于哈希聯(lián)接或哈希聚合操作的查詢。· 某些 GROUP BY、ORDER BY 或 UNION 查詢的中間排序結(jié)果。版本存儲(chǔ)（version_stor

3、e_reserved_page_count）版本存儲(chǔ)區(qū)主要用來(lái)支持Snapshot事務(wù)隔離級(jí)別，以及SQL 2005以后推出的一些其他提高數(shù)據(jù)庫(kù)并發(fā)度的新功能。由此可見(jiàn)，光從用戶發(fā)過(guò)來(lái)的語(yǔ)句本身，是很難判斷這個(gè)連接的操作是否會(huì)使用Tempdb的。一個(gè)典型的例子，就是某些查詢。如果表格上有良好的索引做支持，SQL Server不需要做哈希聯(lián)接(Hash Join)，那這個(gè)查詢就不會(huì)用Tempdb。反之，如果表格很大，又沒(méi)有好的索引，那Tempdb使用量就可能不小。tempdb空間使用的一大特點(diǎn)，是只有一部分對(duì)象，例如用戶創(chuàng)建的臨時(shí)表、table變量等，可以用sys.allocation_unit

4、s 和 sys.partitions這樣的管理視圖來(lái)管理。許多內(nèi)部對(duì)象和版本存儲(chǔ)在這些管理視圖里沒(méi)有體現(xiàn)。所以，sp_spaceused的結(jié)果和真實(shí)使用 會(huì)有很大差異，tempdb的空間使用是不能用sp_spaceused來(lái)跟蹤的。必須借助sys.dm_db_這樣的管理視圖和管理函數(shù)，才能看到全貌。下面以一個(gè)實(shí)例，討論一下如何用DBCC命令、管理視圖(DMV)以及管理函數(shù)(DMF)來(lái)監(jiān)視是什么語(yǔ)句正在使用tempdb。為了使結(jié)果簡(jiǎn)單，我們?cè)跍y(cè)試之前先把SQL Server重起一次。然后我們?cè)贛anagement Studio里做一個(gè)連接（連接A），將下面語(yǔ)句輸入。這些語(yǔ)句會(huì)使用tempdb的空

5、間。select spidgouse adventureworksgoselect getdate()goselect * into #mySalesOrderDetailfrom Sales.SalesOrderDetail- 創(chuàng)建一個(gè)temp table- 這個(gè)操作應(yīng)該會(huì)申請(qǐng)user objects pagegowaitfor delay '0:0:2'select getdate()godrop table #mySalesOrderDetail- 刪除一個(gè)temp table- 這個(gè)操作后user object page數(shù)量應(yīng)該會(huì)下降gowaitfor delay &#

6、39;0:0:2'select getdate()goselect top 100000 * fromSales.SalesOrderDetailINNER JOIN Sales.SalesOrderHeaderON Sales.SalesOrderHeader .SalesOrderID = Sales.SalesOrderHeader.SalesOrderID;- 這里做了一個(gè)比較大的join.- 應(yīng)該會(huì)有internal objects的申請(qǐng).goselect getdate()- join 語(yǔ)句做完以后internal objects page數(shù)目應(yīng)該下降go那用什么腳本可用監(jiān)

7、視上面的行為呢？下面的腳本就可以監(jiān)視和發(fā)現(xiàn)當(dāng)前的Tempdb使用者。這個(gè)腳本需要在使用tempdb的語(yǔ)句開(kāi)始運(yùn)行之前開(kāi)始。（讀者當(dāng)然可以根據(jù)自己的喜好，修改這個(gè)腳本。）腳本首先用“dbcc show”語(yǔ)句查詢當(dāng)前tempdb的總體使用量。再查詢sys.dm_db_視圖，得到 Tempdb里當(dāng)前總共有多少用戶對(duì)象、內(nèi)部對(duì)象、以及版本存儲(chǔ)。然后查詢sys.dm_db_session_space_usage和 sys.dm_exec_sessions，找到當(dāng)前使用Tempdb的所有連接。最后通過(guò)sys.dm_exec_sql_text，找到這些連接正在 運(yùn)行的語(yǔ)句。use tempdb &

8、#160;                                                  

9、           - 每隔1秒鐘運(yùn)行一次，直到用戶手工終止腳本運(yùn)行while 1=1                                 

10、60;                             begin                   

11、0;                                               select getdate()

12、                                                  

13、      - 從文件級(jí)看tempdb使用情況dbcc show                                       

14、0;              - Query 1- 返回所有做過(guò)空間申請(qǐng)的session信息Select 'Tempdb' as DB, getdate() as Time,                       &

15、#160;                                    SUM (user_object_reserved_page_count)*8 as user_objects_kb,    &

16、#160;        SUM (internal_object_reserved_page_count)*8 as internal_objects_kb,     SUM (version_store_reserved_page_count)*8  as version_store_kb,         SUM (unallocated_extent_page_count)*8 as fr

17、eespace_kb               From sys.dm_db_                               

18、;          Where database_id = 2                                     &

19、#160;              - Query 2- 這個(gè)管理視圖能夠反映當(dāng)時(shí)tempdb空間的總體分配SELECT t1.session_id,                         

20、0;                         ernal_objects_alloc_page_count,  t1.user_objects_alloc_page_count,ernal_objects_dealloc_page_count , t1.user_objects_dealloc_page_c

21、ount,t3.*from sys.dm_db_session_space_usage  t1 ,                               - 反映每個(gè)session累計(jì)空間申請(qǐng)sys.dm_exec_sessions as t3- 每個(gè)session的信息w

22、heret1.session_id = t3.session_idand (ernal_objects_alloc_page_count>0or t1.user_objects_alloc_page_count >0or ernal_objects_dealloc_page_count>0or t1.user_objects_dealloc_page_count>0)- Query 3- 返回正在運(yùn)行并且做過(guò)空間申請(qǐng)的session正在運(yùn)行的語(yǔ)句SELECT t1.session_id,     

23、                                               st.text  

24、0;                                                 

25、0;   from sys.dm_db_session_space_usage as t1,                               sys.dm_exec_requests as t4    

26、60;                                         CROSS APPLY sys.dm_exec_sql_text(t4.sql_handle) AS

27、st                    where  t1.session_id = t4.session_id                       

28、;                   and t1.session_id >50                           

29、0;                    and (ernal_objects_alloc_page_count>0or t1.user_objects_alloc_page_count >0or ernal_objects_dealloc_page_count>0or t1.user_objects_dealloc_page_count>0)&#

30、160;                                             waitfor delay '0:0:1&#

31、39;                                                  &#

32、160; end                          圖1 在運(yùn)行這個(gè)腳本的連接（連接B）里（圖1），我們選擇好“Result to File”。先開(kāi)始運(yùn)行它，指定輸出文件路徑。然后，我們?cè)龠\(yùn)行連接A（圖2）。連接A運(yùn)行結(jié)束后，手工停止連接B的運(yùn)行。圖2 在連接A的結(jié)果中（），可以得到四個(gè)時(shí)間。

33、圖片上的例子，是：11:39:36.513     - 開(kāi)始創(chuàng)建temp table11:39:38.920 開(kāi)始刪除temp table11:39:40.937 開(kāi)始查詢11:39:45.733 查詢結(jié)束  連接B生成的是一個(gè)文本文件。利用一些有“列出所有包含某個(gè)特定字符串”行功能的編輯器工具，可以把每個(gè)命令結(jié)果挑出來(lái)。 從連接B生成的文本文件里所有dbcc show的結(jié)果（圖3），可以看出tempdb的使用空間有過(guò)兩次增長(zhǎng)（從23到210，從47到118），中間有一次下降（從210到47）。  圖3從

34、連接B生成的文本文件里所有Query 1的結(jié)果（圖3），我們可以看到有三段時(shí)間，user object和internal object空間有申請(qǐng)和釋放動(dòng)作。它們分別是11:39:36 11:39:37 (user_objects_kb增長(zhǎng))，11:39:40 11:39:41 (user_objects_kb下降)，11:39:40 11:39:43（internal_objects_kb增長(zhǎng)）。圖4從Query 2的結(jié)果（圖4）可以看到Connection A在這三個(gè)時(shí)間段都處于運(yùn)行狀態(tài)。圖5  根據(jù)時(shí)間，可以從Query 3的結(jié)果（圖5）里找到Connection A當(dāng)

35、時(shí)正在運(yùn)行的語(yǔ)句。例如在11:39:40 11:39:43（internal_objects_kb增長(zhǎng)）這段時(shí)間里，一直都在運(yùn)行下面這句話：圖6從上面的結(jié)果可以看出，連接A的語(yǔ)句中，用tempdb最多的時(shí)間點(diǎn)在11：39：41和11：39：42之間，連接正在做圖6里面的那條查詢語(yǔ)句。SQL Server需要空間存放一些內(nèi)部對(duì)象，來(lái)完成Inner Join。問(wèn)題三、 死鎖如何跟蹤;用Profile做跟蹤時(shí)，一般我們需要跟蹤哪些事件;SQL Server跟蹤（SQL Server Profile）的一些監(jiān)控方式和途徑使用場(chǎng)景    記得某次給一家公司調(diào)優(yōu)的時(shí)候，負(fù)責(zé)人

36、發(fā)給我一堆業(yè)務(wù)的T-SQL腳本，我面對(duì)海量腳本還是從容，雖然不了解內(nèi)部復(fù)雜的業(yè)務(wù)，但是我們得專注問(wèn)題的關(guān)鍵 “慢”，我們根據(jù)查詢的“慢”把他們篩選出來(lái)，一一調(diào)式優(yōu)化，不就迅速解決問(wèn)題嗎？三天后，負(fù)責(zé)人含淚握著我的手，哥們辛苦了，查詢響應(yīng)得到了質(zhì)的改善。跟蹤提供者    SQL Server 為我們兩者提供跟蹤的方式：一種是一個(gè)物理文件（可保存在本機(jī)或者UNC網(wǎng)絡(luò)路徑），一種是行集。對(duì)于后者大家應(yīng)該比較熟悉這個(gè)工具在 SSMS 的 工具 > SQL Profile詳細(xì)的我暫時(shí)不介紹，先說(shuō)說(shuō)兩者的區(qū)別和類同點(diǎn) DIFFAndSame(行集,文件提供者)。

37、3; 兩者都是用類似Buffer來(lái)保存當(dāng)前的事件數(shù)據(jù)，很明顯是為了減少IO的壓力，這樣可以不阻塞和盡量不遺漏 事件數(shù)據(jù)，當(dāng)Buffer 到達(dá)一定量時(shí)候可能才會(huì)Flush到磁盤(pán)或者發(fā)送到網(wǎng)絡(luò)的終端（客戶端）顯示監(jiān)控行集。· 物理文件保存監(jiān)控結(jié)果的方式的重要保證是不能遺漏任何事件，一旦IO降速的時(shí)候，可能會(huì)影響到整個(gè)T-SQL的執(zhí)行情況。 SELECT * FROM sys.dm_os_wait_stats WHERE wait_type IN ('SQLTRACE_LOCK','IO_COMPLETION'); 我使用這個(gè)語(yǔ)句來(lái)監(jiān)控TRACE 和IO 完

38、成對(duì)我當(dāng)前機(jī)器的影響，我的某個(gè)客戶的IO情況：                       wait_type  waiting_tasks_count wait_time_msmax_wait_time_ms signal_wait_time_ms IO_COMPLETION3634    418960 SQLTRACE_LOCK120

39、07 1759431001 1281因?yàn)槲疫M(jìn)行了大量的過(guò)濾，因此這個(gè)值還是能夠接受的，影響不是特別大。· 行結(jié)果集的方式，其實(shí)也是我們最熟悉的，就是使用SQL Server Profile監(jiān)控GUI 直接展現(xiàn)給我們看到的。但是，我是非常不建議使用的，首先如果Buffer滿了，它有一定的延遲，可能會(huì)拋棄事件已 清空緩存區(qū)繼續(xù)接受事件，而事件沒(méi)有發(fā)送到Client，也沒(méi)有寫(xiě)到物理文件，自然就丟失了。比如，SQL Server Profile 在DB服務(wù)器進(jìn)行監(jiān)控，因?yàn)楦哓?fù)載的機(jī)器再用來(lái)展示，很有可能就會(huì)丟失事件，另外物理文件方式，其實(shí)是接受一個(gè)足夠大的Buffer，進(jìn)行的大塊寫(xiě)操作， 性

40、能是優(yōu)于行集的。（行集）保密性原則    SQL Server的安全特性會(huì)自動(dòng)過(guò)濾 包含隱私的數(shù)據(jù)，比如密碼。我在我的SSMS中執(zhí)行了如下的語(yǔ)句：EXEC sp_password 'pp','pp1','sa'這是修改sa帳號(hào)密碼的系統(tǒng)sp，我打開(kāi)了SQL Server Profile > 選擇了T-SQL 監(jiān)控模版 然后執(zhí)行上面的存儲(chǔ)過(guò)程，監(jiān)控結(jié)果：監(jiān)控結(jié)果：-*sp_password- SQL Server Profile     使用SQL Server Pro工具

41、還是很多優(yōu)勢(shì)，首先是減少了我們監(jiān)控的復(fù)雜性，可以快速的建立監(jiān)控，在跟蹤屬性中，可以可以選擇MSSQL為我們提供的模版，包括常用的T-SQL、T-SQL Duration、T-SQL Locks模版分別監(jiān)控當(dāng)前DB運(yùn)行的所有查詢，所有查詢的耗時(shí)、所有的鎖定狀態(tài)。在跟蹤屬性 > 選擇事件選擇 我們可以選擇自己需要的事件，所有的事件在MSDN 都有定義->單擊列篩選器 可以自定義過(guò)濾，排序噪點(diǎn)干擾因素（我隨便選擇了一個(gè)耗時(shí) = 500 微妙的過(guò)濾條件）其他的模版大家可以自己看看MSDN 手冊(cè)，自己嘗試一下：SQL Server 2008 R2 本機(jī)  MSDN 服務(wù)器

42、端跟蹤和物理方式收集    SQL Server Profile 只是對(duì)一些存儲(chǔ)過(guò)程的封裝，我更傾向于，自己定義常用的腳本，將監(jiān)控結(jié)果保存在本機(jī)，用來(lái)大量的分析和存檔。當(dāng)然涉及4個(gè)存儲(chǔ)過(guò)程，雖然設(shè)置過(guò)濾的腳本非常麻煩，但是SQL Server Profile 可以利用 文件->導(dǎo)出 可以導(dǎo)出監(jiān)控腳本意味著，我們不需要編寫(xiě)復(fù)雜的T-SQL 腳本，不過(guò)還是建議大家熟悉這幾個(gè)存儲(chǔ)過(guò)程：· sp_trace_create 定義跟蹤 ，創(chuàng)建的跟蹤會(huì)在sys.traces查詢的到。· s_trace_setevent 設(shè)置監(jiān)控事件· sp_

43、trace_setfilter 設(shè)置過(guò)濾· sp_trace_setstatus 設(shè)置跟蹤的狀態(tài)  常用的是  sp_trace_setstatus traceid,0 停止功能 、sp_trace_setstatus traceid,2 移除跟蹤，這將導(dǎo)致sys.traces最終查詢不到該跟蹤其實(shí)整個(gè)跟蹤還是比較簡(jiǎn)單的。我這里有一個(gè)常用的腳本：用來(lái) 監(jiān)控超過(guò)指定秒數(shù) 和 數(shù)據(jù)庫(kù) 的 批處理和存儲(chǔ)過(guò)程 語(yǔ)句（超過(guò)5MB的文件，會(huì)執(zhí)行ROLLOVER，根據(jù)文件名在后面添加類似_1,_2.trc的跟蹤結(jié)果）：CREATE PROC dbo.sp_trace_sql_d

44、urtion DatabaseName nvarchar(128), Seconds bigint, nvarchar(260)ASBEGINDECLARE rc int,TraceID int,Max bigint;SET Max = 5; EXEC sp_trace_create TraceID OUTPUT,2,NULL; IF rc != 0 RETURN; DECLARE On bit;SET On = 1; EXEC sp_trace_setevent TraceID,10,35,On;EXEC sp_trace_setevent Trace

45、ID,10,1,On;EXEC sp_trace_setevent TraceID,10,13,On;EXEC sp_trace_setevent TraceID,41,35,On;EXEC sp_trace_setevent TraceID,41,1,On;EXEC sp_trace_setevent TraceID,41,13,On; SET Seconds = Seconds * 1000000; EXEC sp_trace_setfilter TraceID,13,0,4,Seconds; IF DatabaseName IS NOT NULL EXEC

46、sp_trace_setfilter TraceID,35,0,0,DatabaseName EXEC sp_trace_setstatus TraceID,1SELECT TraceID = TraceID; END參數(shù)非常的明了，數(shù)據(jù)庫(kù)名稱、執(zhí)行事件超過(guò)多少秒、保存的路徑。當(dāng)我們運(yùn)行這個(gè)腳本一段事件以后，可以快速的發(fā)現(xiàn)大量耗時(shí)的T-SQL，我們可以通過(guò)SELECT * FROM fn_trace_gettable(N'監(jiān)控文件路徑',1);來(lái)查看行方式的結(jié)果。同樣的富有創(chuàng)造力的讀者可以自己創(chuàng)建監(jiān)控鎖定，監(jiān)控死鎖等方式保存文件，但是我的建議是盡可能的減少噪

47、音，也就是說(shuō)我們要達(dá)到什么目地就建立什么功能，這樣才能將大問(wèn)題細(xì)化解決。在Microsfot SQL Server 2005 技術(shù)內(nèi)幕： T-SQL 程序設(shè)計(jì) 中有一個(gè)正則，用來(lái)將類似的語(yǔ)句全部組合成，只有參數(shù)形式替換具體值的SQL CLR，但是我認(rèn)為那個(gè)正則還有bug，等我空了給大家寫(xiě)一個(gè)，自己也能使用的更完善。監(jiān)控異常 在上個(gè)系列中，講述了具體的SQL Event抓去的異常，可以及時(shí)通知，但是具體的異常信息，并不是特別詳細(xì)。因此我們可以選擇事件中的Error來(lái)添加有關(guān)T-SQL批處理和SP的所有異常，用于分析，這個(gè)跟蹤非常有利于我們監(jiān)控一些異常情況！我創(chuàng)建了一個(gè)跟蹤的腳本，和上面的跟蹤事件

48、的腳本一樣，超過(guò)5MB RollOver。 我們要定期的執(zhí)行這個(gè)跟蹤，雖然不建議長(zhǎng)期開(kāi)啟，但是定期監(jiān)控處理異常是有利我們系統(tǒng)更加長(zhǎng)時(shí)間運(yùn)作的。CREATE PROC dbo.sp_trace_sql_exception nvarchar(260)ASDECLARE rc int,TraceID int,Max bigintSET max = 5   EXEC rc = sp_trace_create TraceID output, 2, , Max, NULL IF (rc != 0) RETURN; DECLARE on bitSET on = 1EXEC sp

49、_trace_setevent TraceID, 33, 1, onEXEC sp_trace_setevent TraceID, 33, 14, onEXEC sp_trace_setevent TraceID, 33, 51, onEXEC sp_trace_setevent TraceID, 33, 12, onEXEC sp_trace_setevent TraceID, 11, 2, onEXEC sp_trace_setevent TraceID, 11, 14, onEXEC sp_trace_setevent TraceID, 11, 51, onEXEC sp_trace_s

50、etevent TraceID, 11, 12, onEXEC sp_trace_setevent TraceID, 13, 1, onEXEC sp_trace_setevent TraceID, 13, 14, onEXEC sp_trace_setevent TraceID, 13, 51, onEXEC sp_trace_setevent TraceID, 13, 12, on DECLARE intfilter int,bigintfilter bigint; EXEC sp_trace_setstatus TraceID, 1 SELECT Trace

51、ID=TraceIDGOTO finish ERROR: SELECT ErrorCode=rc FINISH: 定期執(zhí)行吧，同志們，找異常。默認(rèn)跟蹤和黑盒跟蹤    在sys.traces中的TraceID = 1的跟蹤是SQL Server 默認(rèn)跟蹤，這個(gè)跟蹤比較輕量級(jí)，一般監(jiān)控服務(wù)器的啟用停止，對(duì)象的創(chuàng)建和刪除，日志和數(shù)據(jù)文件自動(dòng)增長(zhǎng)以及其他數(shù)據(jù)庫(kù)的變化。（監(jiān)控那些沒(méi)事刪錯(cuò)了表的 人，是最好的，當(dāng)然前提不要都使用一個(gè)帳號(hào)！）可以通過(guò)EXEC sp_configure 'default trace enabled',0;RE

52、CONFIGURE WITH OVERRIDE;來(lái)關(guān)閉默認(rèn)跟蹤。黑盒跟蹤，就是可以幫助我們?cè)\斷數(shù)據(jù)庫(kù)沒(méi)事自個(gè)奔了的異常，在MSDN 搜索sp_create_trace的時(shí)候應(yīng)該也發(fā)現(xiàn)了的選項(xiàng)，那么我們也能創(chuàng)建一個(gè)類似的存儲(chǔ)過(guò)程來(lái)快速的創(chuàng)建黑盒跟蹤，幫助我們?cè)\斷一些異常！CREATE PROCEDURE sp_trace_blackbox nvarchar(260)ASBEGIN DECLARE TraceID int,Max bigint SET Max = 25; EXEC sp_trace_create TraceID OUTPUT,8, EXEC sp_trace_setstatus T

53、raceID,1;END我這里提供 = NULL參數(shù)，這個(gè)默認(rèn)就保存在SQL Server的數(shù)據(jù)文件夾中。問(wèn)題四、 阻塞如何跟蹤和查找;當(dāng)一個(gè)數(shù)據(jù)庫(kù)會(huì)話中的事務(wù)正鎖定一個(gè)或多個(gè)其他會(huì)話事務(wù)想要讀取或修改的資源時(shí)，會(huì)產(chǎn)生阻塞(Blocking)。通常短時(shí)間的阻塞沒(méi)有問(wèn)題，且是較忙的應(yīng)用程序所需要的。然而，設(shè)計(jì)糟糕的應(yīng)用程序會(huì)導(dǎo)致長(zhǎng)時(shí)間的阻塞，這就不必要地鎖定了資源，而且阻塞了其他會(huì)話讀取和更新它們。當(dāng)一個(gè)數(shù)據(jù)庫(kù)會(huì)話中的事務(wù)正鎖定一個(gè)或多個(gè)其他會(huì)話事務(wù)想要讀取或修改的資源時(shí)，會(huì)產(chǎn)生阻塞(Blocking)。通常短時(shí)間的阻塞沒(méi)有問(wèn)題，且是較忙的應(yīng)用程序所需要的。然而，設(shè)計(jì)糟糕的應(yīng)用程序會(huì)導(dǎo)致長(zhǎng)時(shí)間的

54、阻塞，這就不必要地鎖定了資源，而且阻塞了其他會(huì)話讀取和更新它們。在SQL Server中，一個(gè)阻塞的進(jìn)程會(huì)無(wú)限期地保持阻塞，或者直到它超時(shí)（根據(jù)set lock_timeout）、服務(wù)器關(guān)閉、進(jìn)程被殺死、連接完成了更新或者其他發(fā)生在原始事務(wù)上的操作導(dǎo)致它釋放了資源上的鎖。發(fā)生長(zhǎng)時(shí)間阻塞的原因如下：1. 在一個(gè)沒(méi)有索引的表上的過(guò)量的行鎖會(huì)導(dǎo)致SQL Server得到一個(gè)鎖，從而阻塞其他事務(wù)。 2. 應(yīng)用程序打開(kāi)一個(gè)事務(wù)，并在事務(wù)保持打開(kāi)的時(shí)候要求用戶進(jìn)行反饋或交互。這通常是讓最終用戶在GUI上輸入數(shù)據(jù)而保持事務(wù)打開(kāi)的時(shí)候發(fā)生。此時(shí)，事務(wù)引用的任何資源都會(huì)被占據(jù)。 3. 事務(wù)BEGIN后查詢的數(shù)據(jù)

55、可能在事務(wù)事務(wù)開(kāi)始前被調(diào)用 4. 查詢不恰當(dāng)?shù)厥褂面i定提示。例如，應(yīng)用程序僅使用很少的行，但卻使用一個(gè)表鎖提示 5. 應(yīng)用程序使用長(zhǎng)時(shí)間運(yùn)行的事務(wù)，在一個(gè)事務(wù)中更新了很多行或很多表（把一個(gè)大量更新的事務(wù)變成多個(gè)更新較少的事務(wù)有助于改善并發(fā)性）一、找到并解決阻塞進(jìn)程下面我們演示使用SQL Server動(dòng)態(tài)管理視圖sys.dm_os_waiting_tasks找出阻塞進(jìn)程，該視圖用于代替早期SQL Server版本中的系統(tǒng)存儲(chǔ)過(guò)程sp_who找出阻塞的進(jìn)程后，我們使用sys.dm_exec_sql_text動(dòng)態(tài)管理函數(shù)和sys.dm_exec_Connections（DMV）找出正在執(zhí)行的查詢的S

56、QL文本，然后強(qiáng)制結(jié)束進(jìn)程。強(qiáng)制結(jié)束進(jìn)程，我們使用kill命令。kill的用法，請(qǐng)參看MSDN:該命令有三個(gè)參數(shù)：· session ID 要終止的進(jìn)程的會(huì)話 ID。session ID 是在建立連接時(shí)為每個(gè)用戶連接分配的唯一整數(shù) (int)。在連接期間，會(huì)話 ID 值與該連接捆綁在一起。連接結(jié)束時(shí)，則釋放該整數(shù)值，并且可以將它重新分配給新的連接。使用 KILL session ID 可終止與指定的會(huì)話 ID 關(guān)聯(lián)的常規(guī)非分布式事務(wù)和分布式事務(wù)。 · UOW 標(biāo)識(shí)分布式事務(wù)的工作單元 (UOW) ID。UOW 是可從 sys.dm_tran_locks 動(dòng)態(tài)管理視圖的 re

57、quest_owner_guid 列中獲取的 GUID。也可從錯(cuò)誤日志中或通過(guò) MS DTC 監(jiān)視器獲取 UOW。有關(guān)監(jiān)視分布式事務(wù)的詳細(xì)信息，請(qǐng)參閱 MS DTC 文檔。使用 KILL UOW 可終止孤立的分布式事務(wù)。這些事務(wù)不與任何真實(shí)的會(huì)話 ID 相關(guān)聯(lián)，與虛擬的會(huì)話 ID = '-2' 相關(guān)聯(lián)?？墒箻?biāo)識(shí)孤立事務(wù)變得更為簡(jiǎn)單，其方法是查詢 sys.dm_tran_locks、sys.dm_exec_sessions 或 sys.dm_exec_requests 動(dòng)態(tài)管理視圖中的會(huì)話 ID 列。 · WITH STATUSONLY 生成由于更早的 KILL 語(yǔ)句而

58、正在回滾的指定 session ID 或 UOW 的進(jìn)度報(bào)告。KILL WITH STATUSONLY 不終止或回滾 session ID 或 UOW，該命令只顯示當(dāng)前的回滾進(jìn)度。在第一個(gè)查詢窗口：1. BEGIN TRAN 2. UPDATE Production.ProductInventory 3. SET Quantity = 400 4. WHERE ProductID = 1 AND 5. LocationID = 1 第二個(gè)窗口：1. UPDATE Production.ProductInventory 2. SET Quantity = 406 3. WHERE Product

59、ID = 1 AND 4. LocationID = 1 第三個(gè)窗口：1. SELECT blocking_session_id, wait_duration_ms, session_id 2. FROM sys.dm_os_waiting_tasks 3. WHERE blocking_session_id IS NOT NULL 4. 5. /*blocking_session_id wait_duration_ms session_id 6. 52 23876 54 7. */ 可以看出是SessionID為52的會(huì)話阻塞了SessionID為54的會(huì)話。那么，52正在干啥壞事呢？在第三

60、個(gè)窗口中執(zhí)行：1. SELECT t.text 2. FROM sys.dm_exec_connections c 3. CROSS APPLY sys.dm_exec_sql_text (c.most_recent_sql_handle) t 4. WHERE c.session_id = 54 5. 6. /*text(1 int,2 tinyint,3 tinyint) 7. UPDATE Production.ProductInventory set Quantity = 1 8. WHERE ProductID=2 AND LocationID=3 9. */ 注意：這并不是第一個(gè)查

61、詢窗口中的原SQL語(yǔ)句，SQL Server進(jìn)行了自動(dòng)參數(shù)化計(jì)劃緩存（預(yù)編譯）。我們強(qiáng)制終止會(huì)話。在第三個(gè)窗口中執(zhí)行：kill 52 注意：窗口一的語(yǔ)句和窗口二的語(yǔ)句均終止。提示：第三個(gè)語(yǔ)句中，使用sys.dm_exec_connections(DMV)返回了Session ID為53的most_recent_sql_handle列。這是SQL文本在內(nèi)存中的指針。作為sys.dm_exec_sql_text動(dòng)態(tài)管理函數(shù)的輸入?yún)?shù)使用。從sys.dm_exec_sql_text返回了text列，該列顯示了阻塞進(jìn)程的SQL文本。如果阻塞成串，必須通過(guò)blocking_session_id和sess

62、ion_ID列仔細(xì)查看每一個(gè)阻塞進(jìn)程，直到發(fā)現(xiàn)原始的阻塞進(jìn)程。二、配置語(yǔ)句等待鎖釋放的時(shí)長(zhǎng)如果有一個(gè)事務(wù)或語(yǔ)句被阻塞，意味著它在等待資源上的鎖被釋放。我們可以事先通過(guò)set lock_Timeout來(lái)設(shè)定需要等待的時(shí)間。語(yǔ)法如下：SET LOCK_TIMEOUT time_period參數(shù)以毫秒為單位。超過(guò)時(shí)會(huì)返回鎖定錯(cuò)誤。示例：在第一個(gè)窗口中執(zhí)行：1. USE AdventureWorks 2. BEGIN TRAN 3. UPDATE Production.ProductInventory 4. SET Quantity = 400 5. WHERE ProductID = 1 AND 6

63、. LocationID = 1 在第二個(gè)窗口中執(zhí)行：1. USE AdventureWorks 2. SET LOCK_TIMEOUT 1000 3. UPDATE Production.ProductInventory 4. SET Quantity = 406 5. WHERE ProductID = 1 AND 6. LocationID = 1 7. 8. /*秒后的執(zhí)行結(jié)果Msg 1222, Level 16, State 51,Line 3 9. Lock request time out period exceeded. 10. The statement has been t

64、erminated. 11. */ 解析:在這個(gè)示例中，我們?cè)O(shè)置了鎖超時(shí)時(shí)間為1000毫秒，即秒。這個(gè)設(shè)置不會(huì)影響資源被進(jìn)程占有的時(shí)間，只會(huì)影響等待另一個(gè)進(jìn)程釋放資源訪問(wèn)的時(shí)間。問(wèn)題五、 發(fā)現(xiàn)有問(wèn)題的語(yǔ)句后，如何進(jìn)行處理;當(dāng)你面對(duì)一個(gè)“有問(wèn)題”的語(yǔ)句時(shí)，應(yīng)該怎么分析它的問(wèn)題所在，最后達(dá)到優(yōu)化語(yǔ)句的目的呢？首先要想一想，“有問(wèn)題”的語(yǔ)句“問(wèn)題”究竟在那里？也就是說(shuō)，你要優(yōu)化的目標(biāo)是什么。常見(jiàn)的需求有:1)語(yǔ)句需要訪問(wèn)大量的數(shù)據(jù)頁(yè)面，造成內(nèi)在壓力、磁盤(pán)繁忙等。對(duì)于這類問(wèn)題，所關(guān)心的是為什么語(yǔ)句要執(zhí)行要訪問(wèn)這么多數(shù)據(jù)頁(yè)面？是語(yǔ)句的結(jié)果集本身就比較大;還是SQL SERVER沒(méi)有辦法有效地seek，而

65、是像大炮打蒼蠅一樣從大量的原始數(shù)據(jù)里找出需要返回的結(jié)果；還是因?yàn)閿?shù)據(jù)頁(yè)面里有很多碎片，導(dǎo)致SQL SERVER讀了很多頁(yè)面，但是每個(gè)頁(yè)面里的數(shù)據(jù)量不多。這些都是要考慮的因素。2)在內(nèi)存沒(méi)有壓力的前提下（語(yǔ)句所訪問(wèn)的頁(yè)面都事先緩存在內(nèi)存里），語(yǔ)句運(yùn)行的時(shí)間還是很長(zhǎng)。語(yǔ)句的運(yùn)行時(shí)間一般會(huì)主要花在這3步上：語(yǔ)句編譯、語(yǔ)句執(zhí)行和結(jié)果集返回。結(jié)果集返回的速度和SQL SERVER自身沒(méi)有太大關(guān)系，所以一般不會(huì)在語(yǔ)句調(diào)優(yōu)的時(shí)候來(lái)考慮。語(yǔ)句調(diào)優(yōu)時(shí)要搞清楚編譯和執(zhí)行各花了多少時(shí)間，哪 一段時(shí)間有優(yōu)化的空間，以及怎么來(lái)優(yōu)化。3)單個(gè)語(yǔ)句執(zhí)行時(shí)間可以接受，但是苦CPU使用量比較大，多個(gè)語(yǔ)句并發(fā)執(zhí)行會(huì)造成SQL S

66、ERVER CPU高。有些語(yǔ)句單句執(zhí)行可能一兩秒鐘就能執(zhí)行完畢，對(duì)用戶來(lái)講還在可接受的范圍。但是它的CPU間可能也是在一兩秒，甚至更長(zhǎng)。如果同時(shí)有十幾個(gè)用戶在跑 同樣的語(yǔ)句，SQL SERVER 就會(huì)滿負(fù)荷了。語(yǔ)句的CPU時(shí)間也分編譯階段和執(zhí)行階段。優(yōu)化者要先搞清楚這兩個(gè)階段各用了多少CPU資源，然后再看看有沒(méi)有優(yōu)化降低CPU使用量的可 能。4)語(yǔ)句單獨(dú)執(zhí)行看不出有大問(wèn)題，但是并發(fā)執(zhí)行就容易遇到阻塞和死鎖。  這個(gè)也是語(yǔ)句調(diào)優(yōu)的一個(gè)重要任務(wù)。很多語(yǔ)句執(zhí)行速度很快，使用資源量SQL SERVER也能夠承受，但是就是容易引起阻塞和死鎖。這種現(xiàn)象往往是由于應(yīng)用在某個(gè)表或者索引上的

67、并發(fā)度特別高，而問(wèn)題語(yǔ)句申請(qǐng)的鎖數(shù)量比較大造成的。 當(dāng)然有時(shí)候可以使用Query Hint 來(lái)強(qiáng)制 SQL SERVER使用粒度比較小的鎖。但是這往往不是最好的解決辦法，也可能解決不了問(wèn)題。最理想的方法，是通過(guò)調(diào)整語(yǔ)句運(yùn)行方式，引導(dǎo)它申請(qǐng)盡可能少的、粒 度盡可能小的鎖。這里也要做語(yǔ)句調(diào)優(yōu)。  在做這些調(diào)優(yōu)的時(shí)候，首先要對(duì)目標(biāo)語(yǔ)句做估算，看看它優(yōu)化的空間有多大。有些語(yǔ)句本身比較簡(jiǎn)單，可以通過(guò)調(diào)整索引的方法迅速提高性能，這樣的調(diào)優(yōu)是很值得 做的。有些語(yǔ)句非常復(fù)雜，或者返回的結(jié)果集很大，通過(guò)調(diào)整SQL SERVER這里的設(shè)置，提高性能的空間往往不大。這個(gè)時(shí)候就要考慮，語(yǔ)句本身是不

68、是能夠換一種方法實(shí)現(xiàn)。很多時(shí)候改一下語(yǔ)句，把一條大的語(yǔ)句拆分成若干 條小的語(yǔ)句，或者去掉一些不必要的邏輯，會(huì)達(dá)到事半功倍的效果  在談?wù)撊绾巫稣Z(yǔ)句調(diào)優(yōu)的具體方法之前，必須先介紹一下最必需的背景知識(shí)。不了解這些知識(shí) ，做語(yǔ)句調(diào)優(yōu)就只能基本靠猜。所需要的背景知識(shí)主要包括理解索引和統(tǒng)計(jì)信息，理解什么是統(tǒng)計(jì)和重編譯，并且能夠基本讀懂語(yǔ)句的執(zhí)行計(jì)劃。以下為例子，借助 MS示例數(shù)據(jù)庫(kù)AdventureWordks來(lái)介紹。-測(cè)試用例USE AdventureWorks2008GOIF OBJECT_ID ('SalesOrderHeader_TEST') IS NOT N

69、ULL DROP TABLE dbo.SalesOrderHeader_TESTGOIF OBJECT_ID ('dbo.SalesOrderDetail_TEST') IS NOT NULL DROP TABLE dbo.SalesOrderDetail_TESTGO- (31465 行受影響)SELECT * INTO dbo.SalesOrderHeader_TESTFROM Sales.SalesOrderHeader- (121317 行受影響)SELECT * INTO dbo.SalesOrderDetail_TESTFROM Sales.SalesOrderDe

70、tail- 建立聚集索引CREATE CLUSTERED INDEX SalesOrderHeader_TEST_CL ON dbo.SalesOrderHeader_TEST(SalesOrderID)- 建立非聚集索引CREATE NONCLUSTERED INDEX SalesOrderDetail_TEST_NCL ON dbo.SalesOrderDetail_test(SalesOrderID)goSalesOrderHeader_TEST 里存放的是每一張訂單的頭信息，包括訂單創(chuàng)建日期、客戶編號(hào)、合同編號(hào)、銷售員編號(hào)等，每個(gè)訂單都有一個(gè)單獨(dú)的訂單號(hào)。在訂單號(hào)這個(gè)字段上，有一個(gè)聚集索引。SalesOrderDetail_TEST 里存放的是訂單的詳細(xì)內(nèi)容。一張訂單可以銷售多個(gè)產(chǎn)品給同一個(gè)客戶，所以