tag:blogger.com,1999:blog-84982424780154497752024-02-20T12:27:15.974-05:00Aravind's BlogAnonymoushttp://www.blogger.com/profile/15470156382298034140noreply@blogger.comBlogger19125tag:blogger.com,1999:blog-8498242478015449775.post-23778356361142919682013-06-19T00:12:00.001-04:002013-06-19T00:12:19.992-04:00Query to list Deadlocks thru Extended Event <div dir="ltr" style="text-align: left;" trbidi="on">
I have been used to turning on the Trace flags 1222 or 1204 to get information on the deadlocks.Depending upon the type of environment I would either have them turned on at start-up or on demand in response to errors that were logged in previous night's batch log file and look for this behavior to recur.<br />
On top of this, if I need to dig in a bit further, I would turn on the Profiler trace.<br />
<br />
SQL Server 2008 onwards,there are extended events that runs a default lightweight session that captures deadlock info (and a bunch of others see Jonathan Kehayias article) in a ring buffer and stores it in memory.We could query the Extended Event DMVs to fetch deadlock info from the memory.This is especially awesome since we do not have to turn on the trace flag or profiler.<br />
There is a very informative article by Jonathan Kehayias on Extended Event for deadlocks.My query has been built on Jonathan's query.<br />
<br />
There are a few changes I had made.I have listed the deadlock timestamp alongside the XML.<br />
Also, I faced an issue with the query running forever in certain 2008\ R2 instances whereas in others it just worked fine.I am not sure if this is a bug. However,I was able to workaround this by moving the inline table's output to a variable (or a temporary table) .<br />
<br />
Since I have to be able to capture this info in environments with varying versions and service packs I have added a TRY CATCH block to make sure that the query worked irrespective of whether the issue with the missing tag has been fixed or not.<br />
<br />
<div style="text-align: left;">
<span style="color: blue; font-family: 'Courier New';">DECLARE</span><span style="font-family: 'Courier New';"> </span><span style="color: #8000ff; font-family: 'Courier New';">@deadlock_info</span><span style="font-family: 'Courier New';"> </span><span style="font-family: 'Courier New';"><i>XML</i></span><span style="color: silver; font-family: 'Courier New';">,</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: #8000ff; font-family: 'Courier New';">@last_n_days</span><span style="font-family: 'Courier New';"> </span><span style="font-family: 'Courier New';"><i>INT</i></span><span style="font-family: 'Courier New';"> </span><span style="color: silver; font-family: 'Courier New';">=</span><span style="font-family: 'Courier New';"> </span><span style="font-family: 'Courier New';">10</span><span style="color: silver; font-family: 'Courier New';">;</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><br style="font-family: 'Courier New';" /><span style="color: blue; font-family: 'Courier New';">SELECT</span><span style="font-family: 'Courier New';"> </span><span style="color: #8000ff; font-family: 'Courier New';">@deadlock_info</span><span style="font-family: 'Courier New';"> </span><span style="color: silver; font-family: 'Courier New';">=</span><span style="font-family: 'Courier New';"> </span><span style="color: magenta; font-family: 'Courier New';"><i>Cast</i></span><span style="color: maroon; font-family: 'Courier New';">(</span><span style="color: maroon; font-family: 'Courier New';">target_data</span><span style="font-family: 'Courier New';"> </span><span style="color: blue; font-family: 'Courier New';">AS</span><span style="font-family: 'Courier New';"> </span><span style="font-family: 'Courier New';"><i>XML</i></span><span style="color: maroon; font-family: 'Courier New';">)</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="color: blue; font-family: 'Courier New';">FROM</span><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">sys</span><span style="color: silver; font-family: 'Courier New';">.</span><span style="color: maroon; font-family: 'Courier New';">dm_xe_session_targets</span><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">st</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: blue; font-family: 'Courier New';">JOIN</span><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">sys</span><span style="color: silver; font-family: 'Courier New';">.</span><span style="color: maroon; font-family: 'Courier New';">dm_xe_sessions</span><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">s</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: blue; font-family: 'Courier New';">ON</span><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">s</span><span style="color: silver; font-family: 'Courier New';">.</span><span style="color: maroon; font-family: 'Courier New';">address</span><span style="font-family: 'Courier New';"> </span><span style="color: silver; font-family: 'Courier New';">=</span><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">st</span><span style="color: silver; font-family: 'Courier New';">.</span><span style="color: maroon; font-family: 'Courier New';">event_session_address</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="color: blue; font-family: 'Courier New';">WHERE</span><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">name</span><span style="font-family: 'Courier New';"> </span><span style="color: silver; font-family: 'Courier New';">=</span><span style="font-family: 'Courier New';"> </span><span style="color: red; font-family: 'Courier New';">'system_health'</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><br style="font-family: 'Courier New';" /><span style="color: blue; font-family: 'Courier New';">BEGIN</span><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">try</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: blue; font-family: 'Courier New';">SELECT</span><span style="font-family: 'Courier New';"> </span><span style="color: magenta; font-family: 'Courier New';"><i>Cast</i></span><span style="color: maroon; font-family: 'Courier New';">(</span><span style="color: maroon; font-family: 'Courier New';">xeventdata</span><span style="color: silver; font-family: 'Courier New';">.</span><span style="color: maroon; font-family: 'Courier New';">xevent</span><span style="color: silver; font-family: 'Courier New';">.</span><span style="color: #ff0080; font-family: 'Courier New';"><b>value</b></span><span style="color: maroon; font-family: 'Courier New';">(</span><span style="color: red; font-family: 'Courier New';">'(data/value)[1]'</span><span style="color: silver; font-family: 'Courier New';">,</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: red; font-family: 'Courier New';">'varchar(max)'</span><span style="color: maroon; font-family: 'Courier New';">)</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: blue; font-family: 'Courier New';">AS</span><span style="font-family: 'Courier New';"> </span><span style="font-family: 'Courier New';"><i>XML</i></span><span style="color: maroon; font-family: 'Courier New';">)</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: blue; font-family: 'Courier New';">AS</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">DeadlockGraph</span><span style="color: silver; font-family: 'Courier New';">,</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">xeventdata</span><span style="color: silver; font-family: 'Courier New';">.</span><span style="color: maroon; font-family: 'Courier New';">xevent</span><span style="color: silver; font-family: 'Courier New';">.</span><span style="color: #ff0080; font-family: 'Courier New';"><b>value</b></span><span style="color: maroon; font-family: 'Courier New';">(</span><span style="color: red; font-family: 'Courier New';">'@timestamp'</span><span style="color: silver; font-family: 'Courier New';">,</span><span style="font-family: 'Courier New';"> </span><span style="color: red; font-family: 'Courier New';">'datetime2'</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">)</span><span style="font-family: 'Courier New';"> </span><span style="color: blue; font-family: 'Courier New';">AS</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">Error_Timestamp</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: blue; font-family: 'Courier New';">FROM</span><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">(</span><span style="color: blue; font-family: 'Courier New';">SELECT</span><span style="font-family: 'Courier New';"> </span><span style="color: #8000ff; font-family: 'Courier New';">@deadlock_info</span><span style="font-family: 'Courier New';"> </span><span style="color: blue; font-family: 'Courier New';">AS</span><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">TargetData</span><span style="color: maroon; font-family: 'Courier New';">)</span><span style="font-family: 'Courier New';"> </span><span style="color: blue; font-family: 'Courier New';">AS</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">deadlock_info</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: blue; font-family: 'Courier New';">CROSS</span><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">apply</span><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">targetdata</span><span style="color: silver; font-family: 'Courier New';">.</span><span style="color: #ff0080; font-family: 'Courier New';"><b>nodes</b></span><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">(</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: red; font-family: 'Courier New';">'//RingBufferTarget/event'</span><span style="color: maroon; font-family: 'Courier New';">)</span><span style="font-family: 'Courier New';"> </span><span style="color: blue; font-family: 'Courier New';">AS</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">XEventData</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">(</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">xevent</span><span style="color: maroon; font-family: 'Courier New';">)</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: blue; font-family: 'Courier New';">WHERE</span><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">xeventdata</span><span style="color: silver; font-family: 'Courier New';">.</span><span style="color: maroon; font-family: 'Courier New';">xevent</span><span style="color: silver; font-family: 'Courier New';">.</span><span style="color: #ff0080; font-family: 'Courier New';"><b>value</b></span><span style="color: maroon; font-family: 'Courier New';">(</span><span style="color: red; font-family: 'Courier New';">'@name'</span><span style="color: silver; font-family: 'Courier New';">,</span><span style="font-family: 'Courier New';"> </span><span style="color: red; font-family: 'Courier New';">'varchar(4000)'</span><span style="color: maroon; font-family: 'Courier New';">)</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: silver; font-family: 'Courier New';">=</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: red; font-family: 'Courier New';">'xml_deadlock_report'</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: blue; font-family: 'Courier New';">AND</span><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">xeventdata</span><span style="color: silver; font-family: 'Courier New';">.</span><span style="color: maroon; font-family: 'Courier New';">xevent</span><span style="color: silver; font-family: 'Courier New';">.</span><span style="color: #ff0080; font-family: 'Courier New';"><b>value</b></span><span style="color: maroon; font-family: 'Courier New';">(</span><span style="color: red; font-family: 'Courier New';">'@timestamp'</span><span style="color: silver; font-family: 'Courier New';">,</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: red; font-family: 'Courier New';">'datetime2'</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">)</span><span style="font-family: 'Courier New';"> </span><span style="color: silver; font-family: 'Courier New';">></span><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">(</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: magenta; font-family: 'Courier New';"><i>Getutcdate</i></span><span style="color: maroon; font-family: 'Courier New';">(</span><span style="color: maroon; font-family: 'Courier New';">)</span><span style="font-family: 'Courier New';"> </span><span style="color: silver; font-family: 'Courier New';">-</span><span style="font-family: 'Courier New';"> </span><span style="color: #8000ff; font-family: 'Courier New';">@last_n_days</span><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">)</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="color: blue; font-family: 'Courier New';">END</span><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">try</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><br style="font-family: 'Courier New';" /><span style="color: blue; font-family: 'Courier New';">BEGIN</span><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">catch</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: blue; font-family: 'Courier New';">SELECT</span><span style="font-family: 'Courier New';"> </span><span style="color: magenta; font-family: 'Courier New';"><i>Cast</i></span><span style="color: maroon; font-family: 'Courier New';">(</span><span style="color: magenta; font-family: 'Courier New';"><i>Replace</i></span><span style="color: maroon; font-family: 'Courier New';">(</span><span style="color: magenta; font-family: 'Courier New';"><i>Replace</i></span><span style="color: maroon; font-family: 'Courier New';">(</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">xeventdata</span><span style="color: silver; font-family: 'Courier New';">.</span><span style="color: maroon; font-family: 'Courier New';">xevent</span><span style="color: silver; font-family: 'Courier New';">.</span><span style="color: #ff0080; font-family: 'Courier New';"><b>value</b></span><span style="color: maroon; font-family: 'Courier New';">(</span><span style="color: red; font-family: 'Courier New';">'(data/value)[1]'</span><span style="color: silver; font-family: 'Courier New';">,</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: red; font-family: 'Courier New';">'varchar(max)'</span><span style="color: maroon; font-family: 'Courier New';">)</span><span style="color: silver; font-family: 'Courier New';">,</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: red; font-family: 'Courier New';">'<victim-list>'</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: silver; font-family: 'Courier New';">,</span><span style="font-family: 'Courier New';"> </span><span style="color: red; font-family: 'Courier New';">'<deadlock><victim-list>'</span><span style="color: maroon; font-family: 'Courier New';">)</span><span style="color: silver; font-family: 'Courier New';">,</span><span style="font-family: 'Courier New';"> </span><span style="color: red; font-family: 'Courier New';">'<process-list>'</span><span style="color: silver; font-family: 'Courier New';">,</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: red; font-family: 'Courier New';">'</victim-list><process-list>'</span><span style="color: maroon; font-family: 'Courier New';">)</span><span style="font-family: 'Courier New';"> </span><span style="color: blue; font-family: 'Courier New';">AS</span><span style="font-family: 'Courier New';"> </span><span style="font-family: 'Courier New';"><i>XML</i></span><span style="color: maroon; font-family: 'Courier New';">)</span><span style="font-family: 'Courier New';"> </span><span style="color: blue; font-family: 'Courier New';">AS</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">DeadlockGraph</span><span style="color: silver; font-family: 'Courier New';">,</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">xeventdata</span><span style="color: silver; font-family: 'Courier New';">.</span><span style="color: maroon; font-family: 'Courier New';">xevent</span><span style="color: silver; font-family: 'Courier New';">.</span><span style="color: #ff0080; font-family: 'Courier New';"><b>value</b></span><span style="color: maroon; font-family: 'Courier New';">(</span><span style="color: red; font-family: 'Courier New';">'@timestamp'</span><span style="color: silver; font-family: 'Courier New';">,</span><span style="font-family: 'Courier New';"> </span><span style="color: red; font-family: 'Courier New';">'datetime2'</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">)</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: blue; font-family: 'Courier New';">AS</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">Error_Timestamp</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: blue; font-family: 'Courier New';">FROM</span><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">(</span><span style="color: blue; font-family: 'Courier New';">SELECT</span><span style="font-family: 'Courier New';"> </span><span style="color: #8000ff; font-family: 'Courier New';">@deadlock_info</span><span style="font-family: 'Courier New';"> </span><span style="color: blue; font-family: 'Courier New';">AS</span><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">TargetData</span><span style="color: maroon; font-family: 'Courier New';">)</span><span style="font-family: 'Courier New';"> </span><span style="color: blue; font-family: 'Courier New';">AS</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">deadlock_info</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: blue; font-family: 'Courier New';">CROSS</span><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">apply</span><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">targetdata</span><span style="color: silver; font-family: 'Courier New';">.</span><span style="color: #ff0080; font-family: 'Courier New';"><b>nodes</b></span><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">(</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: red; font-family: 'Courier New';">'//RingBufferTarget/event'</span><span style="color: maroon; font-family: 'Courier New';">)</span><span style="font-family: 'Courier New';"> </span><span style="color: blue; font-family: 'Courier New';">AS</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">XEventData</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">(</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">xevent</span><span style="color: maroon; font-family: 'Courier New';">)</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: blue; font-family: 'Courier New';">WHERE</span><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">xeventdata</span><span style="color: silver; font-family: 'Courier New';">.</span><span style="color: maroon; font-family: 'Courier New';">xevent</span><span style="color: silver; font-family: 'Courier New';">.</span><span style="color: #ff0080; font-family: 'Courier New';"><b>value</b></span><span style="color: maroon; font-family: 'Courier New';">(</span><span style="color: red; font-family: 'Courier New';">'@name'</span><span style="color: silver; font-family: 'Courier New';">,</span><span style="font-family: 'Courier New';"> </span><span style="color: red; font-family: 'Courier New';">'varchar(4000)'</span><span style="color: maroon; font-family: 'Courier New';">)</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: silver; font-family: 'Courier New';">=</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: red; font-family: 'Courier New';">'xml_deadlock_report'</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: blue; font-family: 'Courier New';">AND</span><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">xeventdata</span><span style="color: silver; font-family: 'Courier New';">.</span><span style="color: maroon; font-family: 'Courier New';">xevent</span><span style="color: silver; font-family: 'Courier New';">.</span><span style="color: #ff0080; font-family: 'Courier New';"><b>value</b></span><span style="color: maroon; font-family: 'Courier New';">(</span><span style="color: red; font-family: 'Courier New';">'@timestamp'</span><span style="color: silver; font-family: 'Courier New';">,</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: red; font-family: 'Courier New';">'datetime2'</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">)</span><span style="font-family: 'Courier New';"> </span><span style="color: silver; font-family: 'Courier New';">></span><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">(</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="font-family: 'Courier New';"> </span><span style="color: magenta; font-family: 'Courier New';"><i>Getutcdate</i></span><span style="color: maroon; font-family: 'Courier New';">(</span><span style="color: maroon; font-family: 'Courier New';">)</span><span style="font-family: 'Courier New';"> </span><span style="color: silver; font-family: 'Courier New';">-</span><span style="font-family: 'Courier New';"> </span><span style="color: #8000ff; font-family: 'Courier New';">@last_n_days</span><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">)</span><span style="font-family: 'Courier New';"> </span><br style="font-family: 'Courier New';" /><span style="color: blue; font-family: 'Courier New';">END</span><span style="font-family: 'Courier New';"> </span><span style="color: maroon; font-family: 'Courier New';">catch</span><span style="font-family: 'Courier New';"> </span></div>
<div style="text-align: left;">
<span style="font-family: 'Courier New'; font-size: x-small;"><br /></span></div>
References<br />
<a href="http://www.sqlservercentral.com/articles/deadlock/65658/" target="_blank">Jonathan Kehayias Article</a><span class="Apple-tab-span" style="white-space: pre;"> </span><br />
<a href="http://blogs.msdn.com/b/bartd/archive/tags/sql+deadlocks/default.aspx" target="_blank">Bart Duncan's SQL Weblog</a> </div>
Anonymoushttp://www.blogger.com/profile/15470156382298034140noreply@blogger.com1tag:blogger.com,1999:blog-8498242478015449775.post-36503232625121209642013-06-13T17:02:00.001-04:002013-06-14T23:42:23.573-04:00List Index details with Included columns and identify duplicate indexes<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Arial, Helvetica, sans-serif;">Viewing all columns in an index in a single row has not always been easy.In addition to the regular columns, there are also the included columns and at times we want to view this info all in one place.Here is the code to view all this information along with a query to identify duplicate indexes.</span><br />
<br />
SET NOCOUNT ON<br />
DECLARE Duplicate_index_cur CURSOR FOR<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>select sch.name +'.'+ tbl.name,idx.name,col.name,inc.index_column_id,inc.is_included_column<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span> from sys.indexes idx inner join sys.index_columns inc on idx.object_id = inc.object_id<br />
and idx.index_id = inc.index_id<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span> join sys.columns col on idx.object_id = col.object_id and inc.column_id = col.column_id <br />
join sys.tables tbl on tbl.object_id = idx.object_id<br />
join sys.schemas sch on sch.schema_id = tbl.schema_id<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>order by sch.name, tbl.name,idx.index_id,inc.index_column_id desc<br />
<br />
DECLARE @table_name<span class="Apple-tab-span" style="white-space: pre;"> </span>varchar(256),<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span> @indexname<span class="Apple-tab-span" style="white-space: pre;"> </span>varchar(256),<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span> @column_name<span class="Apple-tab-span" style="white-space: pre;"> </span>varchar(256),<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span> @index_order<span class="Apple-tab-span" style="white-space: pre;"> </span>int ,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span> @included_column int,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span> @inc_@column_name<span class="Apple-tab-span" style="white-space: pre;"> </span>varchar(256),<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span> @col_list<span class="Apple-tab-span" style="white-space: pre;"> </span>varchar(4000) = '',<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span> @inc_col_list<span class="Apple-tab-span" style="white-space: pre;"> </span> varchar(4000) = '',<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span> @table<span class="Apple-tab-span" style="white-space: pre;"> </span> <span class="Apple-tab-span" style="white-space: pre;"> </span> varchar(256) ,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span> @index<span class="Apple-tab-span" style="white-space: pre;"> </span> varchar(256)<br />
<br />
Declare @Indextable table ( tablename<span class="Apple-tab-span" style="white-space: pre;"> </span>varchar(256),<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>indexname<span class="Apple-tab-span" style="white-space: pre;"> </span>varchar(256),<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>columnname<span class="Apple-tab-span" style="white-space: pre;"> </span>varchar(256),<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>included_columns<span class="Apple-tab-span" style="white-space: pre;"> </span>varchar(256))<br />
<br />
OPEN<span class="Apple-tab-span" style="white-space: pre;"> </span>Duplicate_index_cur<br />
<br />
FETCH NEXT<br />
FROM Duplicate_index_cur<br />
INTO<span class="Apple-tab-span" style="white-space: pre;"> </span>@table_name,@indexname,@column_name,@index_order,@included_column<br />
<br />
/*Set the Initial Parameters*/<br />
SET @table = @table_name<br />
SET @index = @indexname<br />
<br />
WHILE @@FETCH_STATUS = 0<br />
BEGIN<br />
/*Reinitialize Variables*/<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>IF (@table <> @table_name or @index <> @indexname)<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>BEGIN<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>Insert into @Indextable<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>Select @table,@index,@col_list,@inc_col_list<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>SET @table = @table_name<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>SET @index = @indexname<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>SET @col_list = ''<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>SET @inc_col_list = ''<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>END<br />
<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>IF(@included_column = 0)<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>select @col_list = @col_list +','+ @column_name<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>ELSE<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>select @inc_col_list = @inc_col_list +','+ @column_name<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span><br />
FETCH NEXT<br />
FROM Duplicate_index_cur<br />
INTO<span class="Apple-tab-span" style="white-space: pre;"> </span>@table_name,@indexname,@column_name,@index_order,@included_column<br />
<br />
END<br />
<br />
Insert into @Indextable<span class="Apple-tab-span" style="white-space: pre;"> </span><br />
Select @table,@index,@col_list,@inc_col_list<br />
CLOSE<span class="Apple-tab-span" style="white-space: pre;"> </span>Duplicate_index_cur<br />
DEALLOCATE Duplicate_index_cur<br />
<br />
/*<br />
Run this query to list all indexes,columns and included columns<br />
Select<span class="Apple-tab-span" style="white-space: pre;"> </span>tablename,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>indexname,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>substring(columnname,2,len(columnname)) as columnname,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>substring(included_columns,2,len(included_columns)) as Included_Columns <br />
from @Indextable<br />
*/<br />
/*List Duplicate Indexes*/<br />
select org.tablename,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>org.indexname,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>dup.indexname,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>substring(org.columnname,2,len(org.columnname)) as columnname,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>substring(org.included_columns,2,len(org.included_columns)) as Included_Columns <br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>from @Indextable org , @Indextable dup<br />
where org.tablename = dup.tablename<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>and org.indexname<span class="Apple-tab-span" style="white-space: pre;"> </span> > dup.indexname<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>and org.columnname = dup.columnname<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>and org.Included_Columns = dup.Included_Columns<br />
<div>
<br /></div>
</div>
Anonymoushttp://www.blogger.com/profile/15470156382298034140noreply@blogger.com0tag:blogger.com,1999:blog-8498242478015449775.post-50016462863486903792013-06-12T17:47:00.000-04:002013-06-12T17:47:36.075-04:00Rebuild Vs Reorganize Index (and Update Statistics)<div dir="ltr" style="text-align: left;" trbidi="on">
<b>Does rebuilding the clustered index rebuild all the other indexes on the table? What happens to the statistics on that column?</b><br />
<br />
I decided to create a table and rebuild it to see what happened to the index.Here is what I did.<br />
<br />
<div style="text-align: left;">
</div>
<ul style="text-align: left;">
<li>Create Test table,create index and insert records</li>
</ul>
<br />
DROP TABLE [dbo].[Index_table]<br />
GO<br />
CREATE TABLE [dbo].[Index_table](<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>[COL1] [int] NOT NULL,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>[COL2] [int] NOT NULL,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>[COL3] [int] NOT NULL<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>)<br />
GO<br />
CREATE CLUSTERED INDEX<span class="Apple-tab-span" style="white-space: pre;"> </span> [CIX] ON [dbo].[Index_table](COL1)<br />
CREATE NONCLUSTERED INDEX [NCIX1] ON [dbo].[Index_table](COL2)<br />
CREATE NONCLUSTERED INDEX [NCIX2] ON [dbo].[Index_table](COL3)<br />
GO<br />
SET NOCOUNT ON<br />
GO<br />
Insert into Index_table<br />
SELECT ROUND(RAND() * 100,4,0) ,ROUND(RAND() * 100,3,0),ROUND(RAND() * 100,2,0)<br />
GO 50<br />
DROP VIEW [dbo].[Index_table_stats]<br />
GO<br />
<br />
<br />
<ul style="text-align: left;">
<li>Create a view to query the DMVs after each operation</li>
</ul>
<br />
<div>
CREATE View Index_table_stats</div>
as<br />
select OBJECT_NAME(ius.object_id) as 'Table Name',idx.name as 'Index Name',last_system_scan as 'Rebuild Time',system_scans as 'Rebuild count',idx.type_desc,STATS_DATE(ius.OBJECT_ID, ius.index_id) AS 'Stats Updated'<br />
FROM sys.dm_db_index_usage_stats ius join sys.indexes idx <br />
ON OBJECT_NAME(ius.object_id) = OBJECT_NAME(idx.object_id) and ius.index_id = idx.index_id<br />
WHERE OBJECT_NAME(ius.object_id) = 'Index_table'<br />
<br />
<br />
<ul style="text-align: left;">
<li>Run the below queries and to check the index properties and rebuild the column</li>
</ul>
<br />
<br />
select * from Index_table_stats order by type_desc<br />
ALTER INDEX CIX on dbo.Index_table REBUILD<br />
select * from Index_table_stats order by type_desc<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgqqrl2PRm40JKKVJ_ZTBNHicz8royoDG8pYC1gscUfXWJA5-jsF88QdGNP4GEPt7oSvX1LEvn3Uk8a0zjxOkUao36RQfKRA0i4f0DgDgC8LVcuuPaDLdb_hCQxL4RlK1DZcH5MH_17S6Q/s1600/Rebuild.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="321" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgqqrl2PRm40JKKVJ_ZTBNHicz8royoDG8pYC1gscUfXWJA5-jsF88QdGNP4GEPt7oSvX1LEvn3Uk8a0zjxOkUao36RQfKRA0i4f0DgDgC8LVcuuPaDLdb_hCQxL4RlK1DZcH5MH_17S6Q/s640/Rebuild.jpg" width="640" /></a></div>
<br />
As seen from the result,rebuilding the clustered index just rebuilds that specific index as indicated by the "Rebuild Time" column.Every rebuild also updates the statistics for that column with a fullscan.<br />
I did notice one weird behavior in SQL Server 2008 R2. When the index is rebuilt the very first time,the "Rebuild count" is displayed as 2.I am not sure why the index is rebuilt twice.<br />
<br />
<b>Does rebuilding the non clustered index rebuild just that index?What happens to the statistics on that column?</b><br />
<br />
ALTER INDEX NCIX1 on dbo.Index_table REBUILD<br />
ALTER INDEX NCIX2 on dbo.Index_table REBUILD<br />
select * from Index_table_stats order by type_desc<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg0aOyZZL7B70Wrxa2xeY9YFop4PPweS7Wwua3qlmtAE1tvvoSskMeEG7Wu07ILShyphenhyphen9TpCLhTh7LDYh5c7DksaEdCxsrq6I_GZpYm6myIGHBsfS_1oY6xUG35ZSSHDIZ0cFiXyVB5eS15M/s1600/Rebuild2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="76" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg0aOyZZL7B70Wrxa2xeY9YFop4PPweS7Wwua3qlmtAE1tvvoSskMeEG7Wu07ILShyphenhyphen9TpCLhTh7LDYh5c7DksaEdCxsrq6I_GZpYm6myIGHBsfS_1oY6xUG35ZSSHDIZ0cFiXyVB5eS15M/s640/Rebuild2.jpg" width="640" /></a></div>
<br />
As evident from the result,for every index that is rebuilt,statistics are also updated with a fullscan<br />
<br />
<b>Does the Rebuild ALL syntax rebuild all indexes? </b><br />
<br />
ALTER INDEX ALL on dbo.Index_table REBUILD<br />
select * from Index_table_stats order by type_desc<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjUzQUmVyw9TyigIYoQY9EBGCuA_m2X41yWJLcRZlBHVLkF7ePjfJotEp_2e_JJdPgIwOl2f5B6cWdUQYT_npNb67eUEoU_e_c8ozqmkoXiQSAus36VY6GoTAhbfelDYh_NFlpg8ZnMt_0/s1600/Rebuild3.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="74" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjUzQUmVyw9TyigIYoQY9EBGCuA_m2X41yWJLcRZlBHVLkF7ePjfJotEp_2e_JJdPgIwOl2f5B6cWdUQYT_npNb67eUEoU_e_c8ozqmkoXiQSAus36VY6GoTAhbfelDYh_NFlpg8ZnMt_0/s640/Rebuild3.jpg" width="640" /></a></div>
<br />
Rebuild All would rebuild and update statistics with fullscan all the indexes on that table<br />
<br />
<b>Does reorganizing a clustered or non clustered index reorganize the rest of the indexes? What happens to the statistics on that column?</b><br />
<br />
It would be easier if you drop and recreate the table and reorg the index as you could see null values,though this step is not mandatory.<br />
<br />
ALTER INDEX CIX on dbo.Index_table REORGANIZE<br />
ALTER INDEX NCIX1 on dbo.Index_table REORGANIZE<br />
ALTER INDEX NCIX2 on dbo.Index_table REORGANIZE<br />
ALTER INDEX ALL on dbo.Index_table REORGANIZE<br />
select * from Index_table_stats order by type_desc<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjLTL3kDtfAalC1hmqsJB_aZv2XN-u9csx__nNpS_0HVZm8lhmxJdaxVj0kVKW20hfNsu7yungtj_1fgKeOl-kB5caPxyevnl60CZs__I09O4gbCIReWT6YXts-Gz7BRHlOC4DV8ihrkKc/s1600/Rebuild4.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="94" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjLTL3kDtfAalC1hmqsJB_aZv2XN-u9csx__nNpS_0HVZm8lhmxJdaxVj0kVKW20hfNsu7yungtj_1fgKeOl-kB5caPxyevnl60CZs__I09O4gbCIReWT6YXts-Gz7BRHlOC4DV8ihrkKc/s640/Rebuild4.jpg" width="640" /></a></div>
<br />
REORGANIZE affects just the index on which the operation is performed (BOL).I was not able to find a DMV\column that dispalys the time REORGANIZE was last performed.<br />
Unlike rebuild,reorg does NOT update statistics on any of the indexes as evident from the "Stats Updated" column.</div>
Anonymoushttp://www.blogger.com/profile/15470156382298034140noreply@blogger.com0tag:blogger.com,1999:blog-8498242478015449775.post-41817324892308073872013-03-27T20:33:00.000-04:002013-03-27T20:33:45.738-04:00RoboCopy and RichCopy<div dir="ltr" style="text-align: left;" trbidi="on">
Here are two microsoft tools which extend the copy functionality.I have used the <a href="http://technet.microsoft.com/en-us/magazine/2006.11.utilityspotlight.aspx" target="_blank">Robocopy</a> earlier.I tested <a href="http://technet.microsoft.com/en-us/magazine/2009.04.utilityspotlight.aspx" target="_blank">RichCopy </a> recently and liked this GUI.</div>
Anonymoushttp://www.blogger.com/profile/15470156382298034140noreply@blogger.com0tag:blogger.com,1999:blog-8498242478015449775.post-79605335925421323352013-03-23T00:37:00.000-04:002013-03-23T00:37:46.123-04:00Multiple options to Clear query cache and buffer pool<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div class="MsoNormal">
<span style="color: #1f497d; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin; mso-themecolor: text2;">While tuning queries it becomes important to run the existing
query and obtain initial execution time.<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="color: #1f497d; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin; mso-themecolor: text2;">Changes are then made to the query or underlying indexes or
statistics. The modified query is executed and the execution time is compared with
the initial value.<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="color: #1f497d; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin; mso-themecolor: text2;">The query gets compiled the very first time it is executed
and the query plan is cached. Similarly the results of the first execution are stored
in the buffer pool.<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="color: #1f497d; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin; mso-themecolor: text2;">As a result the second time the test query is executed, unless
the query is recompiled the execution time would be faster and undependable.<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="color: #1f497d; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin; mso-themecolor: text2;">With every change the query should be executed with the exact
same set of conditions as the initial execution. How can this be done?<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="color: #1f497d; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin; mso-themecolor: text2;">This can be achieved by clearing the plan cache and buffer
pool using these commands<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">CHECKPOINT</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">DBCC</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">DROPCLEANBUFFERS</span><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">DBCC</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">FREEPROCCACHE</span><o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="color: #1f497d; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin; mso-themecolor: text2;">Note: Do not run these commands on a production server.
These commands will cause queries to be recompiled
and it may adversely impact performance. This changes clear plan cache and
buffer pool for the entire instance.<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">DBCC</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">FREEPROCCACHE</span><o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="color: #1f497d; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin; mso-themecolor: text2;">Removes all cached query plans and execution contexts from
the plan cache,<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="color: #1f497d; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin; mso-themecolor: text2;">Removes a specific plan from the plan cache by specifying
a plan handle or SQL handle or <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="color: #1f497d; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin; mso-themecolor: text2;">Removes all cache entries associated with a specified resource
pool.<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">Example:</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-left: .5in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">SELECT</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: grey;">*</span> <span style="color: blue;">FROM</span> <span style="color: teal;">Person</span><span style="color: grey;">.</span><span style="color: blue;">Address</span><span style="color: grey;">;</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-left: .5in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">GO</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-left: .5in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">SELECT</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">plan_handle</span><span style="color: grey;">,</span> <span style="color: teal;">st</span><span style="color: grey;">.</span><span style="color: blue;">text</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-left: .5in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">FROM</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: green;">sys</span><span style="color: grey;">.</span><span style="color: green;">dm_exec_cached_plans</span> <o:p></o:p></span></div>
<div class="MsoNormal" style="margin-left: .5in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: grey; font-family: Consolas; font-size: 9.5pt;">CROSS</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: grey;">APPLY</span> <span style="color: green;">sys</span><span style="color: grey;">.</span><span style="color: green;">dm_exec_sql_text</span><span style="color: grey;">(</span><span style="color: teal;">plan_handle</span><span style="color: grey;">)</span> <span style="color: blue;">AS</span> <span style="color: teal;">st</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-left: .5in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">WHERE</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">text</span> <span style="color: grey;">LIKE</span> <span style="color: red;">N'SELECT * FROM Person.Address%'</span><span style="color: grey;">;</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-left: .5in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">GO</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-left: .5in; mso-layout-grid-align: none; text-autospace: none;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">dbcc</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">freeproccache</span><span style="color: grey;">(</span>0x060005123<span style="color: teal;">RGCA270E306E5EF00200000001000000000000000000000000000000000000000000000000000000</span><span style="color: grey;">)</span><o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">DBCC</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">FREESYSTEMCACHE</span>
<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="color: teal; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin;">Removes</span> <span style="color: grey;">all</span> <span style="color: teal;">plans</span> <span style="color: blue;">from</span> <span style="color: grey;">all</span> <span style="color: teal;">caches</span> <span style="color: grey;">or</span> <span style="color: blue;">from</span> <span style="color: teal;">a</span> <span style="color: teal;">specified</span> <span style="color: blue;">Resource</span> <span style="color: blue;">Governor</span> <span style="color: blue;">pool</span><span style="color: grey;">.</span><o:p></o:p></div>
<div class="MsoNormal">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">Example:</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">DBCC</span>
<span style="color: teal;">FREESYSTEMCACHE</span><span style="color: grey;">(</span><span style="color: red;">'SQL Plans'</span><span style="color: grey;">)</span> <o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">DBCC</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">FREESESSIONCACHE</span>
<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="color: #1f497d; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin; mso-themecolor: text2;">Flushes the distributed query connection cache used by distributed
queries against an instance of Microsoft SQL Server.<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">DBCC</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">FLUSHPROCINDB</span><span style="color: grey;">(</span> <span style="color: magenta;">db_id</span> <span style="color: grey;">)<o:p></o:p></span></span></div>
<div class="MsoNormal">
<span style="color: #1f497d; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin; mso-themecolor: text2;">Removes all cached plans from the plan cache for a particular
database. <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">Example:</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">Declare</span> <span style="color: teal;">@dbid</span> <span style="color: blue;">int</span> <span style="color: grey;">=</span> <span style="color: magenta;">DB_ID</span><span style="color: grey;">()</span><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: blue;">DBCC</span> <span style="color: teal;">FLUSHPROCINDB</span><span style="color: grey;">(</span><span style="color: teal;">@dbid</span><span style="color: grey;">)</span><o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">DBCC</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: teal;">DROPCLEANBUFFERS</span><o:p></o:p></span></div>
<div class="MsoNormal">
<span style="color: #1f497d; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin; mso-themecolor: text2;">Removes all clean buffers from the buffer pool. Issue a checkpoint
to write all dirty pages to disk and then execute the command.<o:p></o:p></span></div>
</div>
Anonymoushttp://www.blogger.com/profile/15470156382298034140noreply@blogger.com0tag:blogger.com,1999:blog-8498242478015449775.post-45671138675441479002013-02-20T11:20:00.000-05:002013-02-20T11:20:44.747-05:00SQL Server Ebook collection<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<span style="font-family: Verdana, sans-serif;">I found Performance Tuning with SQL Server Dynamic Management Views by Tim Ford and Louis Davidson really really helpful.</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span>
<span style="font-family: Verdana, sans-serif;">I have the paperback edition and I was looking for the online version to avoid typing the sample queries.The entire book and many more are available for free <a href="http://www.sqlservercentral.com/Books/" target="_blank">download</a>.Happy reading!</span><br />
</div>
Anonymoushttp://www.blogger.com/profile/15470156382298034140noreply@blogger.com0tag:blogger.com,1999:blog-8498242478015449775.post-69640517801044655012013-02-19T22:26:00.001-05:002013-02-19T22:26:09.693-05:00SSMS Tool Pack<div dir="ltr" style="text-align: left;" trbidi="on">
Checkout the features of <a href="http://www.ssmstoolspack.com/Features" target="_blank">SSMS Tool pack</a>. SSMS2012 needs a license.Prior versions are free.</div>
Anonymoushttp://www.blogger.com/profile/15470156382298034140noreply@blogger.com0tag:blogger.com,1999:blog-8498242478015449775.post-27695130630586096132013-01-09T10:35:00.000-05:002013-01-09T10:35:14.263-05:00MySQL Database on HP Cloud (free private beta)<div dir="ltr" style="text-align: left;" trbidi="on">
Check this out.You can host an MySQL database on HP Cloud now.<br />
<br />
<span style="color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: 16px; line-height: 22px;"><a href="https://www.hpcloud.com/products/RDB" target="_blank">HP Cloud Relational Database for MySQL</a> uses an open source distribution of MySQL built upon OpenStack™ technology. This</span><span style="color: #222222; font-family: Arial, Helvetica, sans-serif; font-size: 16px; line-height: 22px;"> is offered on-demand as a pay-per-use service (offered for free during our private beta period for this service)</span></div>
Anonymoushttp://www.blogger.com/profile/15470156382298034140noreply@blogger.com0tag:blogger.com,1999:blog-8498242478015449775.post-61948011921977119582012-12-26T00:25:00.002-05:002012-12-26T00:29:00.612-05:00Host your database on Amazon cloud for FREE<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Verdana, sans-serif;">You could setup your database on the amazon cloud <a href="http://aws.amazon.com/rds/free/" target="_blank">here</a> for free.The database can have a 20GB capacity with 10 million I/Os.</span><br />
<span style="font-family: Verdana, sans-serif;"><br />
It is easy to signup for an account, create an instance, provide access and launch it.For step by step guide view this <a href="http://docs.amazonwebservices.com/AmazonRDS/latest/GettingStartedGuide/Welcome.html" target="_blank">link</a>.</span><br />
<span style="font-family: Verdana, sans-serif;"><br />
</span><br />
<div style="text-align: left;">
<br /></div>
</div>
Anonymoushttp://www.blogger.com/profile/15470156382298034140noreply@blogger.com0tag:blogger.com,1999:blog-8498242478015449775.post-28164828504463067162012-12-23T01:31:00.003-05:002012-12-23T01:31:58.107-05:00Rename Logical Filename<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
What would be a good reason to change the logical name of a database?<br />
<br />
Well, every time there is a requirement to create a database for development,the DBA provides a name that would be most applicable to that database at that point in time.In most cases an abbreviation of the project.<br />
<br />
Couple of months/years later,this database has to be setup for System test,Technical test and so on.A backup of the development database would be restored on to the new environment.While the data and log files are named based upon the destination environment,the logical names remains the same.In most cases, by the time the product is delivered the logical and physical names are way different.<br />
<br />
The logical name of the database can be changed using the below syntax.<br />
<br />
ALTER DATABASE AdventureWorks2012<br />
MODIFY FILE ( NAME = AdventureWorks2012_Data ,<br />
NEWNAME = AdventureWorks2012_lob_data )<br />
<br />
Though the logical name can be changed post restore for each environment,I would steer clear of this approach.Instead a better way would be to avoid environment names in the logical file name.<br />
<br />
A sample template could be to just use<br />
<br />
<Projectname>_data/log or <Projectname>_<purpose>_data/log<br />
Eg. Proj_LOB_Data/Log.<br />
<br />
This would make sure that the filename does not have to be modified at a later stage and the initial name remains vaild throughout the lifecycle of the project.</div>
Anonymoushttp://www.blogger.com/profile/15470156382298034140noreply@blogger.com0tag:blogger.com,1999:blog-8498242478015449775.post-12363319744839581952012-12-20T13:57:00.000-05:002012-12-20T13:57:34.280-05:00Free Microsoft Ebooks<div dir="ltr" style="text-align: left;" trbidi="on">
Here is the <a href="http://social.technet.microsoft.com/wiki/contents/articles/11608.e-book-gallery-for-microsoft-technologies.aspx#SQL" target="_blank">link</a> to Free Microsoft Ebooks. The best thing about this is, it is available in a format that could be saved and accessed from your tablet or thru Kindle apps.<br /></div>
Anonymoushttp://www.blogger.com/profile/15470156382298034140noreply@blogger.com0tag:blogger.com,1999:blog-8498242478015449775.post-35518512316996264832012-11-04T22:18:00.000-05:002012-11-04T22:18:15.902-05:00Dropping functions referenced as default constraints<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
When user defined functions are referenced as defaults values for columns and there is a need to Alter or Drop the function, SQL Server will not allow this without dropping the dependencies first.<br />
<br />
It is manageable if the function is referred in one or two columns.However if these functions are referred across the database, it would become time consuming and cumbersome to manually delete these default constraints.<br />
<br />
The below queries will provide the table,schema,constraint and column names that are needed to successfully drop and recreate the constraints.<br />
<br />
I was able to come up with three ways of doing this.This one is my personal favorite since this uses just two tables and does the job.Here in this example [schema].[defaultfunction()] will be the function that is being referenced in the default constraints<br />
<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">SELECT '[' + SCHEMA_NAME(def.schema_id) + '].[' + OBJECT_NAME(def.parent_object_id) + ']' AS 'Table Name',col.name AS 'Column Name',def.name AS 'Constraint Name',def.definition AS 'Default Value'</span><br />
<span style="font-family: Courier New, Courier, monospace;">FROM sys.default_constraints def,sys.columns col</span><br />
<span style="font-family: Courier New, Courier, monospace;">WHERE def.parent_OBJECT_ID = col.object_id</span><br />
<span style="font-family: Courier New, Courier, monospace;">AND def.parent_column_id = col.column_id</span><br />
<span style="font-family: Courier New, Courier, monospace;">AND def.definition = '([schema].[defaultfunction()])'</span><br />
<br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">This is the next approach.The results are as good as the first.</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">SELECT '[' + SCHEMA_NAME(obj.uid) + '].[' + OBJECT_NAME(obj.parent_obj) + ']' AS 'Table Name',col.name AS 'Column Name',obj.name AS 'Constraint Name',com.text AS 'Default Value'</span><br />
<span style="font-family: Courier New, Courier, monospace;">FROM sysobjects obj,syscolumns col,syscomments com</span><br />
<span style="font-family: Courier New, Courier, monospace;">WHERE col.cdefault = obj.id</span><br />
<span style="font-family: Courier New, Courier, monospace;">AND obj.id = com.id</span><br />
<span style="font-family: Courier New, Courier, monospace;">AND com.text = '([schema].[defaultfunction()])'</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: inherit;">This is the third approach.This would work fine if there is just one default schema.However there is a drawback.When there are multiple </span>schema<span style="font-family: inherit;"> this would need the schema name and the existing columns will not be able to supply that information.There needs to be an extra join on sysusers.uid to fetch this information.</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">SELECT OBJECT_NAME(con.id) AS 'Table Name',col.name AS 'Column Name',OBJECT_NAME(con.constid) AS 'Constraint Name', com.text AS 'Default Value'</span><br />
<span style="font-family: Courier New, Courier, monospace;">FROM SYSCONSTRAINTS con,sys.columns col,syscomments com</span><br />
<span style="font-family: Courier New, Courier, monospace;">WHERE con.constid = col.default_object_id</span><br />
<span style="font-family: Courier New, Courier, monospace;">AND con.constid = com.id</span><br />
<span style="font-family: Courier New, Courier, monospace;">AND com.text = '([schema].[defaultfunction()])'</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: inherit;">Now, going back to the first one, lets execute the it on AdventureWorks2012,</span> using getdate() as the function referenced in default constraints .This is going to <span style="font-family: inherit;">bring up the 77 rows.</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">The first column will provide the </span>syntax<span style="font-family: inherit;"> to drop the constraint and the second column will recreate it.</span><br />
<span style="font-family: Courier New, Courier, monospace;"></span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">USE AdventureWorks2012</span><br />
<span style="font-family: Courier New, Courier, monospace;">GO</span><br />
<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">SELECT 'ALTER TABLE [' + SCHEMA_NAME(def.schema_id) + '].[' + OBJECT_NAME(def.parent_object_id) + '] DROP CONSTRAINT ' + def.name as 'drop constraint',</span><br />
<span style="font-family: Courier New, Courier, monospace;">'ALTER TABLE [' + SCHEMA_NAME(def.schema_id) + '].[' + OBJECT_NAME(def.parent_object_id) + '] WITH NOCHECK ADD CONSTRAINT ' + def.name + ' DEFAULT(getdate()) FOR ' + col.name as 'create constraint'</span><br />
<span style="font-family: Courier New, Courier, monospace;">FROM sys.default_constraints def,sys.columns col</span><br />
<span style="font-family: Courier New, Courier, monospace;">WHERE def.parent_OBJECT_ID = col.object_id</span><br />
<span style="font-family: Courier New, Courier, monospace;">AND def.parent_column_id = col.column_id</span><br />
<span style="font-family: Courier New, Courier, monospace;">AND def.definition = '(getdate())'</span><br />
<div>
<br /></div>
<div>
<br /></div>
<div>
-Aravind</div>
<br />
</div>
Anonymoushttp://www.blogger.com/profile/15470156382298034140noreply@blogger.com0tag:blogger.com,1999:blog-8498242478015449775.post-64735046973313254262012-09-25T17:53:00.000-04:002012-09-25T17:58:18.610-04:00GO <div dir="ltr" style="text-align: left;" trbidi="on">
Recently at an <a href="http://sqlsaturday.com/" target="_blank">SQLSaturday</a> ,I learnt a trick - using GO to execute a statement or block of statements in a loop.<br />
<br />
GO is a batch separator and it is not a T-SQL command.A long time back, I used to change the batch separator option to my name instead of GO and have a unwary developer look at SQL Server execute a piece of code with my name on it and GO crazy .<br />
<div>
<br /></div>
<div>
Since SQL Server 2005,the GO syntax has been changed to GO [count], where count is an integer.</div>
<div>
Specifying a value for [count] would cause the block of code preceding the GO to be executed [count] times.</div>
<div>
<br /></div>
<div>
For example lets say we have a block of code<br />
<br /></div>
<div>
/*Start SQL Block*/</div>
<div>
:</div>
<div>
:</div>
<div>
/*End SQL Block*/</div>
<div>
GO 10</div>
<div>
<br /></div>
<div>
This will cause all the statements in this block to execute 10<br />
times.<br />
<br />
This is the code from Books online that is used to generate a random number 4 times<br />
<br />
<br />
DECLARE @counter smallint;<br />
SET @counter = 1;<br />
WHILE @counter < 5<br />
BEGIN<br />
SELECT RAND() Random_Number<br />
SET @counter = @counter + 1<br />
END;<br />
GO<br />
<br />
This can be replaced by<br />
<br />
SELECT RAND() Random_Number<br />
GO 4<br />
<br />
<br />
Generally, while tuning a query, I would execute a block of statements or a stored procedure multiple number of times to get a distribution of the execution times each time after clearing the cache and repeat this under varying test conditions.Whenever there is a necessity to execute statements in a loop, GO [count] syntax would come handy without having to code additional logic.</div>
<div>
<br /></div>
</div>
Anonymoushttp://www.blogger.com/profile/15470156382298034140noreply@blogger.com0tag:blogger.com,1999:blog-8498242478015449775.post-30777812121333904222012-09-21T17:17:00.001-04:002012-09-21T17:17:57.074-04:00SQLSaturday in Pittsburgh<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div style="text-align: left;">
I am double minded about going to this one....Its going to be a 4.5 hour drive</div>
<div style="text-align: left;">
<br /></div>
<h1 style="background-attachment: scroll; background-color: white; background-image: none; background-position: 0% 0%; background-repeat: repeat repeat; border-bottom-color: rgb(137, 155, 177); border-bottom-style: solid; border-width: 0px 0px 1px; color: #272727; font-family: Arial, helvetica, sans-serif; font-size: 22px; font-weight: normal; margin: 0px; outline: invert none 0px; padding: 0px 0px 10px; vertical-align: baseline;">
Welcome to <a href="http://www.sqlsaturday.com/171/eventhome.aspx" target="_blank">SQLSaturday #171</a></h1>
<div style="background-attachment: scroll; background-color: white; background-image: none; background-position: 0% 0%; background-repeat: repeat repeat; border: 0px; color: #272727; font-family: Arial, helvetica, sans-serif; font-size: 12px; line-height: 20px; margin-bottom: 18px; outline: invert none 0px; padding: 10px 0px; vertical-align: baseline;">
SQLSaturday is a full day training event for SQL Server professionals and those wanting to learn about SQL Server. This event will be held Oct 6, 2012 at La Roche College, Zappala Center, 9000 Babcock Boulevard, Pittsburgh , PA 15237. Admittance to this event is free, but we do charge a lunch fee of $10.00 so that we can provide a cafeteria lunch - not pizza! This donation helps to defer the cost of lunch and morning coffee, juice and water. </div>
</div>
Anonymoushttp://www.blogger.com/profile/15470156382298034140noreply@blogger.com0tag:blogger.com,1999:blog-8498242478015449775.post-84329640074864806232012-09-18T11:30:00.000-04:002012-09-18T11:30:10.302-04:0024 Hours of PASS <div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div class="DNN_HTMLContent" id="dnn_ctr13202_ModuleContent">
<div class="Normal" id="dnn_ctr13202_HtmlModule_lblContent">
<strong><a href="http://www.sqlpass.org/24hours/fall2012/default.aspx" target="_blank">24 Back-to-back Hours of Free SQL Server Training!</a></strong><br /><br />Get your learning on - indulge in free online SQL
Server training with the upcoming 24 Hours of PASS Summit Preview event on
September 20, 2012. Join SQL Server experts as they take to the virtual stage
with in-depth sessions on the<a href="http://www.sqlpass.org/24hours/fall2012/SessionsbyTrack.aspx"> hottest SQL
Server topics</a> over 24 consecutive (and free!) technical webcasts. No matter
where you are in the world, you’ll gain access to best SQL Server training,
delivered to your computer.<br />
<strong><br /></strong><br />
<strong>Browse the sessions by </strong><a href="http://www.sqlpass.org/24hours/fall2012/SessionsbySchedule.aspx"><strong>schedule</strong></a><strong>
or </strong><a href="http://www.sqlpass.org/24hours/fall2012/SessionsbyTrack.aspx"><strong>track</strong></a><strong>
and </strong><a href="http://www2.ibtalk.net/index.php?cmp=24HPASS_V2"><strong>register
today</strong></a><strong>, it's free.</strong><br />
This 24 Hours of PASS event gives you a glimpse at the unmatched technical
content <a href="http://www.sqlpass.org/summit/2012/">PASS Summit 2012</a> has
to offer across <a href="http://www.sqlpass.org/summit/2012/Sessions.aspx">190+
technical sessions</a>, <a href="http://www.sqlpass.org/summit/2012/Sessions/PreConferenceSessions.aspx">pre-conference
seminars</a>, and invaluable networking opportunities. Find out <a href="http://www.sqlpass.org/summit/2012/About/WhoShouldAttend.aspx">why you
should attend </a>and <a href="http://www.sqlpass.org/summit/2012/About/ROI.aspx">justify your
attendance</a> today. You won’t want to miss PASS Summit 2012 in Seattle, WA,
Nov. 6-9.</div>
</div>
<!-- End_Module_13202 --></div>
Anonymoushttp://www.blogger.com/profile/15470156382298034140noreply@blogger.com0tag:blogger.com,1999:blog-8498242478015449775.post-54742656564976031642012-09-06T22:19:00.000-04:002012-09-06T22:19:24.292-04:00Powershell 3.0 is available<div dir="ltr" style="text-align: left;" trbidi="on">
Powershell 3.0 is available for download.Yeah!.It reqires .net framework 4.0 as a prerequisite.You can down load it <a href="http://www.microsoft.com/en-us/download/details.aspx?id=34595" target="_blank">here</a>.</div>
Anonymoushttp://www.blogger.com/profile/15470156382298034140noreply@blogger.com0tag:blogger.com,1999:blog-8498242478015449775.post-20146504534735398942012-08-09T11:17:00.000-04:002012-08-09T11:17:00.698-04:00Webcast on High Availability features in SQL Server 2012<div dir="ltr" style="text-align: left;" trbidi="on">
Quest has this webcast on High Availability features in SQL Server 2012 coming up on August 16th.<br />
<br />
Register to attend the <a href="http://www.quest.com/webcast/experts-perspective-webcast-sql-server-2012-high-availability-features817475.aspx?utm_campaign=35074-17408-MON-NA-SQLSvr2012WebcastInvite&utm_medium=email&utm_source=Eloqua">Experts’ Perspective Webcast: Achieving High Availability with SQL Server 2012</a></div>Anonymoushttp://www.blogger.com/profile/15470156382298034140noreply@blogger.com0tag:blogger.com,1999:blog-8498242478015449775.post-34552688735742083732012-07-24T15:55:00.001-04:002012-07-24T15:55:48.078-04:00Data Dictionary for SQL Server<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Verdana, sans-serif;">Every now and then I face this scenario where I am required to provide a quick Data Dictionary in a spreadsheet.This spreadsheet needs to have just the list of all tables and their properties.This is not one of the cases where the comments are required, rather a quick view to just skim thru the table list or for somebody to be aware of what table structures are there is the database.</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span><br />
<span style="font-family: Verdana, sans-serif;">I was able to come up with a query to consolidate all the required information and to </span><span style="background-color: white; font-family: Verdana, sans-serif;">format it so that it can be copied to a spreadsheet and sent out.</span><br />
<br />
<br />
<span style="color: blue;"><i>select </i></span><br />
<span style="color: blue;"><i>case when clm.column_id = 1 then object_schema_name(clm.object_id) else '' end as 'Schema',</i></span><br />
<span style="color: blue;"><i>case when clm.column_id = 1 then object_name(clm.object_id) else '' end as 'Table',</i></span><br />
<span style="color: blue;"><i>clm.name<span class="Apple-tab-span" style="white-space: pre;"> </span>as 'Column',</i></span><br />
<span style="color: blue;"><i>typ.name<span class="Apple-tab-span" style="white-space: pre;"> </span>as 'Datatype',</i></span><br />
<span style="color: blue;"><i>clm.max_length<span class="Apple-tab-span" style="white-space: pre;"> </span>as 'Length',</i></span><br />
<span style="color: blue;"><i>case </i></span><br />
<span style="color: blue;"><i><span class="Apple-tab-span" style="white-space: pre;"> </span>when typ.name in ('int','smallint','decimal','money','tinyint','date','smallmoney','time','numeric','float','real','bigint','datetime2','datetimeoffset') </i></span><br />
<span style="color: blue;"><i><span class="Apple-tab-span" style="white-space: pre;"> </span> then ltrim(rtrim(str(clm.precision))) +',' + ltrim(rtrim(str(clm.scale))) </i></span><br />
<span style="color: blue;"><i><span class="Apple-tab-span" style="white-space: pre;"> </span>else '' </i></span><br />
<span style="color: blue;"><i><span class="Apple-tab-span" style="white-space: pre;"> </span>end<span class="Apple-tab-span" style="white-space: pre;"> </span>as 'Precision,Scale',</i></span><br />
<span style="color: blue;"><i>clm.is_nullable as 'Nullability',</i></span><br />
<span style="color: blue;"><i>case </i></span><br />
<span style="color: blue;"><i><span class="Apple-tab-span" style="white-space: pre;"> </span>when cns.definition is null </i></span><br />
<span style="color: blue;"><i><span class="Apple-tab-span" style="white-space: pre;"> </span> then '' </i></span><br />
<span style="color: blue;"><i>/* below condition not reqd for SQL 2000 database upgraded to later versions*/</i></span><br />
<span style="color: blue;"><i><span class="Apple-tab-span" style="white-space: pre;"> </span>when isnumeric(substring(cns.definition,3,len(cns.definition)-4)) = 1 </i></span><br />
<span style="color: blue;"><i><span class="Apple-tab-span" style="white-space: pre;"> </span> then substring(cns.definition,3,len(rtrim(cns.definition))-4) --2005 onwards</i></span><br />
<span style="color: blue;"><i><span class="Apple-tab-span" style="white-space: pre;"> </span>else substring(cns.definition,2,len(cns.definition)-2) --for 2000 database upgraded to later versions </i></span><br />
<span style="color: blue;"><i><span class="Apple-tab-span" style="white-space: pre;"> </span>end<span class="Apple-tab-span" style="white-space: pre;"> </span>as 'Default'</i></span><br />
<span style="color: blue;"><i>from sys.columns clm join sys.types typ </i></span><br />
<span style="color: blue;"><i> on clm.user_type_id = typ.user_type_id</i></span><br />
<span style="color: blue;"><i><span class="Apple-tab-span" style="white-space: pre;"> </span> left join sys.default_constraints cns</i></span><br />
<span style="color: blue;"><i> on clm.default_object_id = cns.object_id</i></span><br />
<span style="color: blue;"><i>where objectpropertyex ( clm.object_id , 'IsUserTable' ) = 1</i></span><br />
<span style="color: blue;"><i>order by object_name(clm.object_id),clm.column_id</i></span><br />
<br />
<span style="color: blue; font-family: Times, 'Times New Roman', serif;"><br /></span><br />
<span style="color: blue; font-family: Times, 'Times New Roman', serif;"><br /></span><br />
<span style="font-family: Verdana, sans-serif;">When I got the defaults to be displayed I could see that SQL Server 2005 onward there were two sets of parentheses around the default value especially for numeric datatypes as against just one for character default values. </span><span style="background-color: white; font-family: Verdana, sans-serif;">Whereas</span><span style="background-color: white; font-family: Verdana, sans-serif;"> for </span><span style="background-color: white; font-family: Verdana, sans-serif;">SQL </span><span style="background-color: white; font-family: Verdana, sans-serif;">Server 2000 databases upgraded to later versions, there was an uniform </span><span style="background-color: white; font-family: Verdana, sans-serif;">parentheses</span><span style="background-color: white; font-family: Verdana, sans-serif;"> around the default value.</span><br />
<span style="background-color: white; font-family: Verdana, sans-serif;">I added a condition to check if the datatype being processed is numeric so that the query works across SQL Server 2005/2008/2012.</span><br />
<span style="background-color: white; font-family: Verdana, sans-serif;">For SQL 2000 database upgraded to later versions just comment out the </span><br />
<span style="background-color: white; font-family: Verdana, sans-serif;"><br /></span><br />
<span style="font-family: Verdana, sans-serif;"></span><br />
<span style="font-family: Verdana, sans-serif;">Also I was not sure if I had to be using the system_type_ids or user_type_ids while getting the datatypes.</span><br />
<span style="font-family: Verdana, sans-serif;">The user_type_ids are distinct whereas for certain datatypes system_type_ids are repetitive.So I have used the user_type_ids to get the datatypes.</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span><br />
<span style="font-family: Verdana, sans-serif;">I have also displayed precision and scale for the datypes where this information is relavent and blanks for the rest.</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span><br />
<span style="font-family: Verdana, sans-serif;"><span style="background-color: white;">--Aravind</span></span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span><br />
</div>Anonymoushttp://www.blogger.com/profile/15470156382298034140noreply@blogger.com1tag:blogger.com,1999:blog-8498242478015449775.post-65030992411478323582012-07-18T16:39:00.000-04:002012-07-18T16:39:59.499-04:00First Blog<div dir="ltr" style="text-align: left;" trbidi="on">
For quite sometime now ,I have been thinking about creating a blog to post my views and thoughts mostly from a technical perspective.I am a SQL Server DBA and I would be posting information I find interesting, ones I have been working on,thinking about and have fun blogging<br />
<br /></div>Anonymoushttp://www.blogger.com/profile/15470156382298034140noreply@blogger.com0