SQL Server MVP Deep Dives- P16

SQL Server MVP Deep Dives- P16: Each year Microsoft invites all the MVPs from every technology and country to Redmondfor an MVP Summit—all top secret—“don’t tweet what you see!” During theMVP Summit, each product team holds a series of presentations where they explaintheir technologies, share their vision, and listen to some honest feedback.
Nội dung trích xuất từ tài liệu:
SQL Server MVP Deep Dives- P16 554 CHAPTER 42 Tracing the deadlock frame procname=WF.dbo.ViewThread line=20 stmtstart=1090 ➥ stmtend=1362 ➥ sqlhandle=0x03000600b15244168cf9db002b9b00000100000000000000 This section lists the full three-part name of the procedure that the process was run- ning. If the call was ad hoc SQL, rather than a stored procedure, then the procname will read adhoc. The line number indicates on which line of the procedure the spe- cific SQL statement starts. If the line number is 1, it’s a strong indication that the specific SQL statement is a piece of dynamic SQL. The statement start and statement end values specify the offsets within the proce- dure where the query starts and ends. The sql_handle can be used with the sys.dm_exec_sql_text DMF to get the SQL statement from the server’s procedure cache. This usually isn’t necessary, as most of the time the statement is reproduced in full in the deadlock graph right below this line. The input buffer lists either the entire query (for ad hoc SQL) or the database ID and object ID for a stored procedure: inputbuf Proc [Database Id = 6 Object Id = 373576369] The object ID can be translated back to an object name using the object name function: SELECT OBJECT_NAME(373576369, 6) In this case it returns ViewThread, matching what was shown for the process name ear- lier in the deadlock graph. NOTE The Object_Name function took only one parameter, the object ID, prior to SQL Server 2005 SP2. From SP2 onward, it accepts an optional second parameter, the database ID. The second process listed in the deadlock graph contains the same information and can be read in much the same way. I won’t go through it all in detail, as many of the explanations given for the first process apply to the second as well. The second process has a different waitresource than the first one did. In the case of the key lock, it was trivial to identify the table involved. The second process was waiting on a page lock. process id=process809f8748 waitresource=PAGE: 6:1:351 spid=55 The numbers listed for the page indicate database ID (6), file ID (1), and page ID (351). The object that owns the page can be identified using DBCC PAGE, but in this case, it’s not necessary, as the name of the table is given later in the deadlock graph in the list of resources. If DBCC Page were to be used, it would show that the page 6:1:351 belongs to a table with the ID 85575343. Using the Object_Name function reveals that the table’s name is Threads. The procedure that the second process was running is WF.dbo.ViewForum and the statement began on line 9.Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Deadlock graph 555 DBCC PAGE DBCC Page is an undocumented but well-known command that shows the contents of database pages. The command takes four parameters, the last being optional. The first three are the database ID or database name, the file ID, and the page ID. The last parameter indicates the print options. Among the information that can be retrieved from the file header is the object and index that the page belongs to (if it’s a data or index page). To return the results to a query window, trace flag 3604 has to be enabled. An exam- ple use of DBCC Page would be DBCC TRACEON (3604) DBCC PAGE (1,1,215,0) DBCC TRACEOFF(3604) By this point, we have a fairly clear idea as to what was happening when the deadlock occurred. The process with a session ID of 53 requested a shared lock on the index key 6:72057594038845440 (1900f638aaf3), in the Users table, so that it could run a select that starts on line 20 of the procedure ViewThread. The second process, with a session ID of 55, requested a shared lock on the page 6:1:351 belonging to the Threads table so that it could run a select ...