Of Profiler, Deadlock Graphs and XDLs

SQL Server Profiler is deprecated in favour of the more versatile Extended Events, but it still is a mighty useful tool to have.

You can save the Deadlock graphs separately (as .XDL files) when setting up Profiler. Once you select the necessary lock/deadlock events in the “Events Selection” tab, a new tab will appear called “Event Extraction Settings”. There, you have the option of saving the Deadlock graphs separately, perhaps in another folder. This can be very useful instead of sifting through a large Profiler trace file trying to find the Deadlock graphs.

Of course, if you havent done this earlier, and all you have is the profiler trace file, there is still another method to get the XDL files. Find the line with the deadlock graph (in Profiler) and right click on it and select “Extract event data”. You can then save it as an XDL file in another folder.

So now you have saved the XDLs and you want to see them. You can open them with SQL Server Management Studio to see the graphical view. To zoom in, there is no right click option; instead the solution is embarassingly simple : hold down CTRL and roll the mouse scroll wheel ….

But to get the full info, its better to open with Notepad++, which automatically formats the XML to give a nice presentable view.

The XML output might look daunting at first, but we can decipher the contents with a few chosen tags. You will notice that the entire XML is located between <deadlock-list> and </deadlock-list> tags.

The first tag to observe is the <deadlock victim=”xxx”> tag. As the name implies, this is the victim; ie this process was killed in the cyclic lock. The process is represented by a process id (“xxx” in this example), and you can also get other details in the next tag.

That next tag is <process id>. Here, it begins the tag by repeating the process id (eg: <process id=”xxx” ) and continues with other details. The important one to take away is the spid detail. It will show you what was the spid of this process that was terminated.

Now observe the first <fname> tag. It will show you what was the exact query that was deadlocked. It can be a single query, or a procedure. If it’s a procedure (or a function), it will show you the exact line that caused the deadlock rollback. Even though this is very useful, it can sometimes be more intuitive to go into the procedure and look at the preceding code. This is simple. Suppose line # 99 caused this rollback. Now script out your procedure and remove the formatting lines that SQL Server has added. This will include the USE database line, the GO line, perhaps a SET ANSI_NULLS ON or SET QUOTED IDENTIFIER On, the procedure name enclosed within “/***” and “***/” tags, more GO lines until you reach the first line of your actual procedure. Then Ctrl+G to goto the line number you want, eg: 99.

If you skip the removing of the first lines as above, you may find yourself puzzled when line 99 refers to an empty line or a comment 😉

Now you know the details of the victim using just 3 tags : the <deadlock victim>, <process id> and <fname> tags. Look for the </process> which will signal the end of the victim process information.

You can use the three tags above to now find the victorious process(es). Depending on your query, you may be lucky to find just two processes interlocked, ie a victor and a victim. But in some cases, there may be many more of these. In either situation, the 3 tags above will help you find out the details of these processes.

Note, for example, the first <process id> tag that appears immediately after the </process> tag of your victim. You can similarly deduce the spid details, the lock modes, the time waited on it, the query details and so forth.

Ok thus far, we have spoken of the Process details. There is also a Resource section in this XML, which is represented by <resource-list> and </resource-list>. Within each <resource-list> tag, you can see very concise text regarding what the problem code is, and what type of lock was applied, etc. This corresponds exactly to the details that were captured earlier in the <process> section.

Once you have isolated the Profiler details like this, you can return to the code and start figuring out how to prevent deadlocks.

Advertisements
Posted in Uncategorized | Leave a comment

Quickly output a stored proc to Excel with SQLCMD

Now XP_CMDSHELL raises one too many red flags, and we dont encourage its use.

But lets not forget the trusty ol’ tool that we have : SQLCMD.

Try the following line to get the output of the proc to a CSV file (which can be reasd by Excel) :

sqlcmd -S server_name -d database_name -E -Q “exec stored_proc_name;” -o E:\OUTPUT.csv -s, -W

In the code above, -S is followed by the server name, then the database name, -E stands for integrated security (Windows authentication), -Q will execute your stored proc and exit the command prompt, -s is the column separator which is a comma and -W will remove trailing spaces.

Posted in Uncategorized | Leave a comment

Extended Events : Why is “retrieving event information from server” empty ?

Extended Events is a very powerful new feature of SQL Server 2012. The best thing about this is that its so powerful, yet very lightweight (in terms of resource utilization) and extremely easy to set up and use.

Except for one gotcha.

I had set up an Extended Events session and fired it up. A few minutes later, it was still showing “retrieving event information from server” and the  panes were empty. What was wrong? Why wasnt it populating data?

I had a hunch and changed the case of the database name filter. Bingo!

Here in the SQL Server world, we are used to typing away without checking uppercase / lowercase. This was the issue. A small thing, but knowing this can help avoid going after unnecessary code checks!

Posted in Uncategorized | Leave a comment

Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Ok, this is usually seen when reading Job history details through SSMS. So what are your options?

One way is to go and see how many records you have in the history table. It is usually safe to remove some which go back several years:

EXEC sp_purge_jobhistory @oldest_date = @JobHistoryDeleteDate

You could also use a filter on the SSMS window to return a range of dates. Perhaps you only want to know if the job was successful yesterday. In SQL 2005, you can only graphically select a date, but not the time. Instead, open the same in SQL 2008 and now you can specify a time also.

Now if you are really short of time and you need the results, you have two approaches. Either write a complex query to get a quick look at the details. Or else, create an index on the sysjobhistory system table to get the details fast. Lets look at the index first :

CREATE NONCLUSTERED INDEX IX_SysJobHistory
ON [dbo].[sysjobhistory] ([job_id])
INCLUDE ([instance_id],[step_id],[sql_message_id],[sql_severity],
[run_status],[run_date],[run_time],[run_duration],[operator_id_emailed],
[operator_id_netsent],[operator_id_paged],[retries_attempted],[server])

The code above returned more than 50k records without any filters. Thx, @SQLFool ! ref: http://sqlfool.com/2009/04/a-look-at-missing-indexes/

The other approach is to write a lengthy code :

SELECT top 10

CONVERT(DATETIME, RTRIM(run_date)) +

((run_time/10000 * 3600) + ((run_time%10000)/100*60) + (run_time%10000)%100 /*run_time_elapsed_seconds*/) / (23.999999*3600 /* seconds in a day*/) AS Start_DateTime

, CONVERT(DATETIME, RTRIM(run_date)) + ((run_time/10000 * 3600) + ((run_time%10000)/100*60) + (run_time%10000)%100) / (86399.9964 /* Start Date Time */)

+ ((run_duration/10000 * 3600) + ((run_duration%10000)/100*60) + (run_duration%10000)%100 /*run_duration_elapsed_seconds*/) / (86399.9964 /* seconds in a day*/) AS End_DateTime,

server,

step_id, step_name, message,

case run_status

when 0 then ‘Failed’

when 1 then ‘Succeeded’

when 2 then ‘Retry’

when 3 then ‘Cancelled’

when 4 then ‘In Progress’

end as Run_Status

FROM msdb.dbo.sysjobhistory

where job_id = ‘50947C95-DE44-4BD7-AA06-92D419004F5F’

and step_name = ‘(Job outcome)’

order by run_date desc

Code courtesy Timothy J Hartford :

http://www.sqlservercentral.com/Forums/Topic542581-145-1.aspx

Hopefully, one of these will work for you!

Posted in Uncategorized | 1 Comment

Find blocking query details from sysprocesses, including text, host, time, etc

Here is a query that I wrote to find details of the blocked queries :

SELECT

— Get Blocked details :

‘spid ‘ + cast(A.spid as varchar(100))       + ‘ is being blocked by spid ‘ + cast(A.blocked as varchar(100)) as Blocked_SPIDs,

A.loginame as Blocked_Loginame, A.open_tran as Blocked_Open_transaction, T.text as BlockedText,       DB_NAME(A.dbid) as Blocked_DatabaseName,

A.program_name as Blocked_Program_name, A.nt_domain as Blocked_NT_Domain, A.nt_username as Blocked_OS_Username, A.login_time as Blocked_LoginTime,

A.status as Blocked_Status, A.waittime as Blocked_Time_In_Milliseconds, SUBSTRING(A.hostname,1, 12) as Blocked_Host,

— Get Blocking details :

B.loginame as Blocker_Loginame, B.open_tran as Blocker_Open_transaction, R.text as BlockerText,       DB_NAME(B.dbid) as Blocker_DatabaseName,

B.program_name as Blocker_Program_name, B.nt_domain as Blocker_NT_Domain, B.nt_username as Blocker_OS_Username, B.login_time as Blocker_LoginTime,

B.status as Blocker_Status, B.waittime as Blocker_Time_In_Milliseconds, SUBSTRING(B.hostname,1, 12) as Blocker_Host

FROM sys.sysprocesses A

OUTER APPLY sys.dm_exec_sql_text(sql_handle) T — this is the blocked text

INNER JOIN sys.sysprocesses B

OUTER APPLY sys.dm_exec_sql_text(sql_handle) R — this is the blocker text

ON B.spid = A.blocked

and A.spid>50

and A.blocked ! = 0 — Identifies any blocking spids

Posted in Uncategorized | Leave a comment

Invoke or BeginInvoke cannot be called on a control until the window handle has been created.

A frustrating error.. and the solution is even more so!

If you get this error during SQL Server 2008 installation, ensure the following :

1. All other windows are closed. That includes IE tabs.

2. The currently active installation window (which shows you the status of the SQL Server installs) is always maximized.

That fixed the problem.

Is it a GUI bug? Beats me… where is that SQL Server 2012 Core installation now…..

Posted in Uncategorized | Leave a comment

Drop a temp table

Well folks we know that temp tables are closed when the user closes his/her session. In fact, BOL explicitly states :

“Local temporary tables are visible only to their creators during the same connection to an instance of SQL Server as when the tables were first created or referenced. Local temporary tables are deleted after the user disconnects from the instance of SQL Server.”

Now here is the problem. One of our users complained that he had disconnected from SQL Server. When he came back, the temp table that he had created was still present. It was preventing him from running a query successfully.

Note that though he disconnected from the instance and then re-connected, the temp table was still present.

We could see that his sessions were still active in many databases. After getting his permission, we proceeded to close all his sessions.

As expected, it dropped the temp table also.

Hope this helps!

Posted in Uncategorized | Leave a comment