SQLServer筆試題

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

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

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

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

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

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

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

8、#160;                                                  

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

10、60;                             begin                   

11、0;                                               select getdate()

12、                                                  

13、      - 從文件級看tempdb使用情況dbcc show                                       

14、0;              - Query 1- 返回所有做過空間申請的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- 這個管理視圖能夠反映當(dāng)時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 ,                               - 反映每個session累計空間申請sys.dm_exec_sessions as t3- 每個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- 返回正在運行并且做過空間申請的session正在運行的語句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 在運行這個腳本的連接（連接B）里（圖1），我們選擇好“Result to File”。先開始運行它，指定輸出文件路徑。然后，我們再運行連接A（圖2）。連接A運行結(jié)束后，手工停止連接B的運行。圖2 在連接A的結(jié)果中（），可以得到四個時間。

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

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

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

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

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

38、成對我當(dāng)前機器的影響，我的某個客戶的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因為我進行了大量的過濾，因此這個值還是能夠接受的，影響不是特別大。· 行結(jié)果集的方式，其實也是我們最熟悉的，就是使用SQL Server Profile監(jiān)控GUI 直接展現(xiàn)給我們看到的。但是，我是非常不建議使用的，首先如果Buffer滿了，它有一定的延遲，可能會拋棄事件已 清空緩存區(qū)繼續(xù)接受事件，而事件沒有發(fā)送到Client，也沒有寫到物理文件，自然就丟失了。比如，SQL Server Profile 在DB服務(wù)器進行監(jiān)控，因為高負載的機器再用來展示，很有可能就會丟失事件，另外物理文件方式，其實是接受一個足夠大的Buffer，進行的大塊寫操作， 性

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

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

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

43、trace_setfilter 設(shè)置過濾· sp_trace_setstatus 設(shè)置跟蹤的狀態(tài)  常用的是  sp_trace_setstatus traceid,0 停止功能 、sp_trace_setstatus traceid,2 移除跟蹤，這將導(dǎo)致sys.traces最終查詢不到該跟蹤其實整個跟蹤還是比較簡單的。我這里有一個常用的腳本：用來 監(jiān)控超過指定秒數(shù) 和 數(shù)據(jù)庫 的 批處理和存儲過程 語句（超過5MB的文件，會執(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ù)庫名稱、執(zhí)行事件超過多少秒、保存的路徑。當(dāng)我們運行這個腳本一段事件以后，可以快速的發(fā)現(xiàn)大量耗時的T-SQL，我們可以通過SELECT * FROM fn_trace_gettable(N'監(jiān)控文件路徑',1);來查看行方式的結(jié)果。同樣的富有創(chuàng)造力的讀者可以自己創(chuàng)建監(jiān)控鎖定，監(jiān)控死鎖等方式保存文件，但是我的建議是盡可能的減少噪

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

48、的腳本一樣，超過5MB RollOver。 我們要定期的執(zhí)行這個跟蹤，雖然不建議長期開啟，但是定期監(jiān)控處理異常是有利我們系統(tǒng)更加長時間運作的。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í)行吧，同志們，找異常。默認跟蹤和黑盒跟蹤    在sys.traces中的TraceID = 1的跟蹤是SQL Server 默認跟蹤，這個跟蹤比較輕量級，一般監(jiān)控服務(wù)器的啟用停止，對象的創(chuàng)建和刪除，日志和數(shù)據(jù)文件自動增長以及其他數(shù)據(jù)庫的變化。（監(jiān)控那些沒事刪錯了表的 人，是最好的，當(dāng)然前提不要都使用一個帳號?。┛梢酝ㄟ^EXEC sp_configure 'default trace enabled',0;RE

52、CONFIGURE WITH OVERRIDE;來關(guān)閉默認跟蹤。黑盒跟蹤，就是可以幫助我們診斷數(shù)據(jù)庫沒事自個奔了的異常，在MSDN 搜索sp_create_trace的時候應(yīng)該也發(fā)現(xiàn)了的選項，那么我們也能創(chuàng)建一個類似的存儲過程來快速的創(chuàng)建黑盒跟蹤，幫助我們診斷一些異常！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ù)，這個默認就保存在SQL Server的數(shù)據(jù)文件夾中。問題四、 阻塞如何跟蹤和查找;當(dāng)一個數(shù)據(jù)庫會話中的事務(wù)正鎖定一個或多個其他會話事務(wù)想要讀取或修改的資源時，會產(chǎn)生阻塞(Blocking)。通常短時間的阻塞沒有問題，且是較忙的應(yīng)用程序所需要的。然而，設(shè)計糟糕的應(yīng)用程序會導(dǎo)致長時間的阻塞，這就不必要地鎖定了資源，而且阻塞了其他會話讀取和更新它們。當(dāng)一個數(shù)據(jù)庫會話中的事務(wù)正鎖定一個或多個其他會話事務(wù)想要讀取或修改的資源時，會產(chǎn)生阻塞(Blocking)。通常短時間的阻塞沒有問題，且是較忙的應(yīng)用程序所需要的。然而，設(shè)計糟糕的應(yīng)用程序會導(dǎo)致長時間的

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

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

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

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

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

59、ID = 1 AND 4. LocationID = 1 第三個窗口：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的會話阻塞了SessionID為54的會話。那么，52正在干啥壞事呢？在第三

60、個窗口中執(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. */ 注意：這并不是第一個查

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

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

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

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

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

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

68、是能夠換一種方法實現(xiàn)。很多時候改一下語句，把一條大的語句拆分成若干 條小的語句，或者去掉一些不必要的邏輯，會達到事半功倍的效果  在談?wù)撊绾巫稣Z句調(diào)優(yōu)的具體方法之前，必須先介紹一下最必需的背景知識。不了解這些知識 ，做語句調(diào)優(yōu)就只能基本靠猜。所需要的背景知識主要包括理解索引和統(tǒng)計信息，理解什么是統(tǒng)計和重編譯，并且能夠基本讀懂語句的執(zhí)行計劃。以下為例子，借助 MS示例數(shù)據(jù)庫AdventureWordks來介紹。-測試用例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)建日期、客戶編號、合同編號、銷售員編號等，每個訂單都有一個單獨的訂單號。在訂單號這個字段上，有一個聚集索引。SalesOrderDetail_TEST 里存放的是訂單的詳細內(nèi)容。一張訂單可以銷售多個產(chǎn)品給同一個客戶，所以