November 2010 - Posts

How to choose a DBA or any other IT practitioner for that matter!
22 November 10 10:45 PM | MartinBell | 1 comment(s)

A discussion that quite often occurs between MVPs is the value of tests during the interview process. Whilst some argue that a written test is a good means of determining whether a candidate is suitable, it will indicate the knowledge the candidates has; others may argue that it doesn’t reflect how people actually work, it only shows their adeptness at taking tests and that they know the particular answers. These people may then suggest a better solution would be to sit them in front of a laptop and watch how they solve the problem. This option gives them the tools which they should be familiar and will also give you a better idea of the candidate’s ability to get the job done. Personally I have always maintained that it is better to check something that you are not 100% sure of and get it correct first time, rather than guessing, getting it wrong and having to correct it. With an interactive test you can also watch if/how the candidate solves the problem and even if they test their solution, something you will not get with a written test.

Few SQL Server professionals are accredited examiners/interviewers (including myself), but hopefully this some of this will help!

If you are going to do a written test what should you include?

I don’t think there is any one answer to that, although it would help to ask questions relevant to the position for which the candidate is applying! One thing you need to make sure of when you are deciding on the questions to ask is to make sure that you are not trying to show your own SQL knowledge, but to test the candidates. Don't use questions off the internet or certification test questions as they may be recognise and may not be relevant, it may also infringe copyright. Use work colleagues to review the questions or even sit a mock test, if they do, remember they are not going to be under the same pressures as a candidate. This will also check that your answers are correct! Don't put a test together 5 minutes before the candidate arrives, and you should certainly not tell them that's what you have done. Don’t give questions a level of difficulty as this is subjective. Be selective in what questions you ask, don’t present 6 sheets of questions to the candidate and tell them they have 30 minutes to answer them all, or even worse, tell them they have 30 minutes but usually a candidate would get 45. This not only puts the candidate under additional pressure, but it also tells them that he that they are not getting an equal assessment. I don't think  I am alone finding it takes significantly longer to write an answer to a question, than to type it. Even if you can't touch type this may still be the case and I know many very fast one fingered typists. The process of answering a question includes, reading the question (possibly more than once, and if the question poorly presented it will take significantly longer), deciding what to put down, then actually doing the writing. If the candidate is to write the answer on the same piece of paper as the question, make sure that you leave enough room; it is better to allow the candidate to answer the questions separately. If you change your mind it takes longer to erase and re-write something if you are forced to write your answer on a question sheet. Make sure questions are clear and concise, asking too much in a single part of a question will increase the risk of it being unclear of what is being asked. If you have a part that sets the scene e.g.DDL or database diagram keep this separate to the questions, it will be clearer and helps the candidate if they don't have to keep turning the paper over to review this information, this is more important if the same scenario is used in more than one question.

How should a test be administered?


A friend of mine believes that a test should always be at the start of an interview, and then you can review/mark the test and ask follow up questions to the candidate. I think this will also give you a better idea about the candidate’s ability, and the candidate gets a chance to redeem themselves. When I was sitting exams at school, we would always have 5 minutes to read the paper before the exam started. This is good exam technique regardless of whether you get a specific 5 minutes, but for someone who has not taken a test for a long time; it may not be something they think of. Make sure the candidate is at ease; don’t do anything to increase the tension they will undoubtedly be feeling stressful, even if they outwardly don't seem to be nervous. At school we would also have a warning that the test was coming to an end, this allows the candidate to check their answers and add anything that they wanted to put into an answer, but hadn’t done so on the first pass.

So does a written test have any value?

I am not averse to written tests, but a badly presented or constructed test (written or otherwise) is not going to tell you anything. If you are having problems finding recruits because they are failing the test, then maybe you should probably take a look at the test or the way it is administered. I am not an expert on employment law, even if I was it may not be the same in your country, make sure the way an interview is conducted does not infringe it. Some organisations insist that a HR representative is present at any interview; this should remove the risk of the interview being conducted incorrectly.

A test should only be part of an interview process; you can always teach people things they don’t know. But if it is not helping to bring good candidates forward then an alternate solution may be to offer positions for a trial period. This way you can really see how a person reacts in a work environment and they can produce a solution to real problems using whatever methods they choose. If they produce timely working solutions and good code then they can graduate. This does assume there are peers to assess this or someone is brought in to do a review. Another option might be use a whiteboard or flipchart to write down the answer and talk their solution through, this way you may provide the information the candidate would look up. If you are still not sure, check out Simpsons Season 21 Episode 2 - "Bart Gets A 'Z'" at about 9 minutes in you have the following dialog:

Zachary Vaughn: "Who can tell me what the Monroe Doctrine was?"
Martin: "The policy of President Monroe that America has the right as a nation to..."
Zachary Vaughn: "Wait, wait, wait." 
Zachary Vaughn: "Are you telling me you memorized that fact when anyone with a cell phone can find it out in 30 seconds?"
Martin: "I've crammed my head full of garbage!"
Zachary Vaughn: "Yes, you have."

Filed under:
WAITFOR what?
22 November 10 01:12 PM | MartinBell | with no comments

I raised a feedback to sqldocfb@microsoft.com regarding the documentation for the WAITFOR statement in BOL. It has been the same ever since SQL 2000 and I think it is wrong

WAITFOR
{
    DELAY 'time_to_pass
'
  | TIME 'time_to_execute
'
  | [ ( receive_statement ) | ( get_conversation_group_statement )
    [ , TIMEOUT timeout ]
}

But if it was consistent with other items in BOL such as RAISERROR it would be documented as

WAITFOR
{
    DELAY { time_to_pass | @local_variable }
  | TIME { time_to_execute | @local_variable }
  | [ ( receive_statement ) | ( get_conversation_group_statement )
    [ , TIMEOUT timeout ]
}

Because of this, I’m not sure how many people know about the ability to use variables when doing a WAITFOR statement so here are a couple of examples:

DECLARE @datetime datetime ;

SET @datetime = '00:00:10.000' ;

WAITFOR DELAY @datetime ;
GO

or

DECLARE
@datetime datetime ;

SET @datetime = DATEADD(ss,10,GETDATE())
;

WAITFOR TIME @datetime
;
GO

BOL talks about being able to specify an acceptable formats for a datetime datatype, then excludes having a specifying a date, which does make things clear. But this may also lead you to believe that only the datetime datatype can be used as a the local variable, when this isn't the case and the following will also works:

DECLARE @chartime char(12) ;

SET @chartime = '00:00:10.000' ;

WAITFOR DELAY @chartime ;
GO

What does seem a shame is that it doesn’t support the new time datatype. This will not work in SQL 2008

DECLARE @time time(3) ;

SET @time = '00:00:10.000' ;

WAITFOR DELAY @time ;
GO

and you get the error:

Msg 9815, Level 16, State 0, Line 5

Waitfor delay and waitfor time cannot be of type time.

This issue was raised as a connect by Steve Kass as the issue https://connect.microsoft.com/SQLServer/feedback/details/292843/katmai-issues-with-waitfor-delay-and-new-types# before SQL 2008 was released, but it hasn’t been subsequently fixed and on the Denali CTP1 is it still an issue. It certainly annoys me, so if you feel the same vote for it on connect.

Filed under: ,
Using Powershell to not script database collations for columns
19 November 10 10:10 PM | MartinBell | with no comments

If you use the SMO scripting object to script a table as I did in my previous article on Powershell for the following script which scripts out the Person.Contact table in the Adventureworks database:

cd SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks\Tables

$Scripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter") ("(local)")
$Scripter.Options.DriAll=$False
$Scripter.Options.IncludeHeaders=$False
$Scripter.Options.ToFileOnly=$True
$Scripter.Options.WithDependencies=$False

foreach ($Item in Get-ChildItem | Where {$_.Schema -eq "Person" -And $_.Name -eq "Contact" } ) {
$Scripter.Options.FileName="C:\PowerShell\" + $Item.Schema + "_" + $Item.Name + ".sql"
$Scripter.Options.AppendToFile=$False
$Scripter.Options.ScriptDrops=$True;
$Scripter.Options.IncludeIfNotExists=$True;
$Scripter.Script($Item)
$Scripter.Options.ScriptDrops=$False;
$Scripter.Options.IncludeIfNotExists=$False;
$Scripter.Options.AppendToFile=$True
$Scripter.Script($Item)
}


You will get the following file produced:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Person].[Contact]') AND type in (N'U'))
DROP TABLE [Person].[Contact]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Person].[Contact](
    [ContactID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [NameStyle] [dbo].[NameStyle] NOT NULL,
    [Title] [nvarchar](8) COLLATE Latin1_General_CI_AS NULL,
    [FirstName] [dbo].[Name] NOT NULL,
    [MiddleName] [dbo].[Name] NULL,
    [LastName] [dbo].[Name] NOT NULL,
    [Suffix] [nvarchar](10) COLLATE Latin1_General_CI_AS NULL,
    [EmailAddress] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
    [EmailPromotion] [int] NOT NULL,
    [Phone] [dbo].[Phone] NULL,
    [PasswordHash] [varchar](128) COLLATE Latin1_General_CI_AS NOT NULL,
    [PasswordSalt] [varchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
    [AdditionalContactInfo] [xml](CONTENT [Person].[AdditionalContactInfoSchemaCollection]) NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

GO

One thing I am not keen on is that all varchar columns are given a collation. This can be an issue if you are wanting to use the database collation for the column as this is effectively hard coding them.

If you add the following line to the code:

$Scripter.Options.NoCollation=$True;

Then no collations at all are produced including the ones that are set to a specific collation that is different to the database default.

So how do you get around this and remove the database specific collations?

A simple solution would be to take the code from this Scripting Guy article and just re-parse the file:

cd SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks\Tables

$Scripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter") ("(local)")
$Scripter.Options.DriAll=$False
$Scripter.Options.IncludeHeaders=$False
$Scripter.Options.ToFileOnly=$True
$Scripter.Options.WithDependencies=$False

foreach ($Item in Get-ChildItem | Where {$_.Schema -eq "Person" -And $_.Name -eq "Contact" } ) {
$FileName="C:\PowerShell\" + $Item.Schema + "_" + $Item.Name + ".sql"
$Scripter.Options.FileName=$FileName
$Scripter.Options.AppendToFile=$False
$Scripter.Options.ScriptDrops=$True;
$Scripter.Options.IncludeIfNotExists=$True;
$Scripter.Script($Item)
$Scripter.Options.ScriptDrops=$False;
$Scripter.Options.IncludeIfNotExists=$False;
$Scripter.Options.AppendToFile=$True
$Scripter.Script($Item)
(Get-Content $FileName) |
Foreach-Object {$_ -replace " COLLATE Latin1_General_CI_AS", ""} |
Set-Content $FileName
}

I don’t think this is a great solution as it re-reads the whole file and writes it out for a second time. I’ve also hard coded the collation to be substituted, and I can get the database collation from a database object. So I decided to modify the Books Online example and here is what I came up with:

$srv = New-Object Microsoft.SqlServer.Management.Smo.Server("(local)")
$db = New-Object Microsoft.SqlServer.Management.Smo.Database
$db = $srv.Databases.Item("AdventureWorks")
$collation = $db.collation
$scrp = New-Object Microsoft.SqlServer.Management.Smo.Scripter($srv)
$scrp.Options.DriAll=$False
$scrp.Options.IncludeHeaders=$False
$scrp.Options.ToFileOnly=$False
$scrp.Options.WithDependencies=$False
$tb = New-Object Microsoft.SqlServer.Management.Smo.Table
foreach ($tb in $db.Tables | Where {$_.Schema -eq "Person" -And $_.Name -eq "Contact" } )
{
   if ($tb.IsSystemObject -eq $FALSE)
   {
      $scrp.Options.IncludeIfNotExists=$True; 
      $scrp.Options.ScriptDrops=$True
      $filename="C:\PowerShell\" + $tb.Schema + "_" + $tb.Name + ".sql"
      $header="/*`r`nFile: " + $filename  + "`r`nObject: " + $tb.Schema + "." + $tb.Name  + "`r`nCreated: " + (Get-Date).ToString() + "`r`n*/`r`n"
      out-file $filename -Encoding ASCII -InputOBJECT $header
      $sc=$scrp.Script($tb)
      $sc+="GO`r`n"
      $scrp.Options.ScriptDrops=$False; 
      $scrp.Options.IncludeIfNotExists=$False; 
      $sc+=$scrp.Script($tb)
      $sc+="GO"
      foreach ($st in $sc )
      {
           $st.replace(" COLLATE " + $collation,"")  | out-file $filename -Append -Encoding ASCII
      }
   }
}

One of the issues I encountered was that I did not get any delimiters (GOs!) when I don’t write it to file, so I have added my own! The carriage returns and newlines ("`r`n") have also been added. I use the out-file cmdlet to write to the file, but I need to use the –Append option otherwise only the final line will be in the file. The –Append option requires the file to already exist, I have added a file header be written when the file is created. The output is as follows:

/*
File: C:\PowerShell\Person_Contact.sql
Object: Person.Contact
Created: 19/11/2010 21:56:58
*/

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Person].[Contact]') AND type in (N'U'))
DROP TABLE [Person].[Contact]
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Person].[Contact](
 [ContactID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
 [NameStyle] [dbo].[NameStyle] NOT NULL,
 [Title] [nvarchar](8) NULL,
 [FirstName] [dbo].[Name] NOT NULL,
 [MiddleName] [dbo].[Name] NULL,
 [LastName] [dbo].[Name] NOT NULL,
 [Suffix] [nvarchar](10) NULL,
 [EmailAddress] [nvarchar](50) NULL,
 [EmailPromotion] [int] NOT NULL,
 [Phone] [dbo].[Phone] NULL,
 [PasswordHash] [varchar](128) NOT NULL,
 [PasswordSalt] [varchar](10) NOT NULL,
 [AdditionalContactInfo] [xml](CONTENT [Person].[AdditionalContactInfoSchemaCollection]) NULL,
 [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
 [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

GO


 




Filed under: , ,
Using XML Schemas
08 November 10 07:50 PM | MartinBell | with no comments
A few weeks ago someone asked how to load XML schemas from a file. I thought I’d previously blogged about this, but upon checking the only place I found it was in a presentations I gave when SQL 2005 was released… so I thought it was about time I posted something!

First lets get an example of an XML schema in use! If you look at the sys.columns system catalog you will see the xml_collection_id column, this is populated if the data type of the column is xml and it is associated with an XML schema collection.

Looking at the Adevntureworks2008 database and running the following query gives all the columns that are associated with XML schemas:

USE AdventureWorks2008 ;
GO

SELECT
s.name AS [Schema], t.name AS [Table], c.name AS [Column], sc.name AS [XML Schema Collection]
FROM   sys.columns cJOIN   sys.tables t ON c.object_id = t.object_idJOIN   sys.schemas s ON s.schema_id = t.schema_idJOIN   sys.xml_schema_collections sc ON c.xml_collection_id =sc.xml_collection_id

GO

There should be 6 rows returned one of which is:

Schema Table Column XML Schema Collection
Production ProductModel CatalogDescription ProductDescriptionSchemaCollection

If you scripted the create statement for the ProductModel table you’d get something like:

CREATE TABLE [Production].[ProductModel](
       [ProductModelID] [int] IDENTITY(1,1) NOT NULL,
       [Name] [dbo].[Name] NOT NULL,
       [CatalogDescription] [xml](CONTENT
[Production]
.[ProductDescriptionSchemaCollection]) NULL,
       [Instructions] [xml](CONTENT [Production].[ManuInstructionsSchemaCollection]) NULL,
       [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
       [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_ProductModel_ProductModelID] PRIMARY KEY CLUSTERED
(
       [ProductModelID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]

The line:

[CatalogDescription] [xml](CONTENT [Production].[ProductDescriptionSchemaCollection]) NULL,

Shows that ProductDescriptionSchemaCollection xml collection is associated with the column. The CONTENT keyword indicates that multiple XML fragments can be contained in the column. If only a single document can be held in the column use the DOCUMENT keyword. To show this XML schema you can use the function xml_schema_namespace:

SELECT xml_schema_namespace(N'Production',N'ProductDescriptionSchemaCollection') ;
GO


If this is displayed in a grid in SSMS the XML will be displayed in a new window if you click the link in the output. This can subsequently be saved as a file (say C:\XML Demo\ProductDescriptionSchema.xsd )

SQL 2005 introduced the OPENROWSET function with the BULK parameter that allows you to open a file and place the contents into a column or variable. So we can load the XML schema into a variable in the same way and then use the variable in a CREATE XML SCHEMA statement e.g:

DECLARE @XmlSchema xml ;

SELECT @XmlSchema = BulkColumn FROM OPENROWSET ( BULK 'C:\XML Demo\ProductDescriptionSchema.xsd', SINGLE_BLOB ) AS b ;

CREATE XML SCHEMA COLLECTION dbo.MyProductDescriptionSchema AS @XmlSchema ;
GO

The full script for the test tables are here.

You might expect that the Test_Production_Content table could just be populated with a statement like:

INSERT INTO [dbo].[Test_ProductModel_Content](  [CatalogDescription] )
SELECT [CatalogDescription]
FROM AdventureWorks2008.Production.ProductModel
WHERE [CatalogDescription] IS NOT NULL ;
GO

But if you try this you get the error:

Msg 527, Level 16, State 2, Line 1
Implicit conversion between XML types constrained by different XML schema collections is not allowed. Use the CONVERT function to run this query.

If you do convert the column the insert statement will work e.g.

INSERT INTO [dbo].[Test_ProductModel_Content](  [CatalogDescription] )SELECT CONVERT(XML, [CatalogDescription] )FROM AdventureWorks2008.Production.ProductModelWHERE [CatalogDescription] IS NOT NULL ;
GO

As there is only a single fragment in the Adventureworks2008 table I can use the same statement to populate the the Test_Production_Document table e.g.

INSERT INTO [dbo].[Test_ProductModel_Document](  [CatalogDescription] )
SELECT CONVERT(varchar(max), [CatalogDescription])
FROM AdventureWorks2008.Production.ProductModel
WHERE [CatalogDescription] IS NOT NULL ;
GO

As the column in Test_Production_Content allows multiple XML fragments I can therefore add more then one XML fragments into the column with the following statement:

INSERT INTO [dbo].[Test_ProductModel_Content](  [CatalogDescription] )
SELECT CONVERT(varchar(max), [CatalogDescription]) + CONVERT(varchar(max), [CatalogDescription])
FROM AdventureWorks2008.Production.ProductModel
WHERE [CatalogDescription] IS NOT NULL ;
GO

But when I try to do something similar this with the document table:

INSERT INTO [dbo].[Test_ProductModel_Document](  [CatalogDescription] )
SELECT CONVERT(varchar(max), [CatalogDescription]) + CONVERT(varchar(max), [CatalogDescription])
FROM AdventureWorks2008.Production.ProductModel
WHERE [CatalogDescription] IS NOT NULL ;
GO


You get the following error:

Msg 6901, Level 16, State 1, Line 1
XML Validation: XML instance must be a document.

If you try to insert an invalid node you will get the error similar to the following:

Msg 6965, Level 16, State 1, Line 13
XML Validation: Invalid content. Expected element(s): '{
http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription}Manufacturer','{http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription}Specifications','{http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription}Picture','{http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription}Features','{http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription}Category'. Found: element 'InvalidNode' instead. Location: /*:ProductDescription[1]/*:InvalidNode[1].

This was generated with the following script:

DECLARE @InvalidNode xml, @xml xml ;
SET @InvalidNode = '<InvalidNode>This is not in the Schema!</InvalidNode>' ;

SET @xml = ( SELECT TOP 1 [CatalogDescription]
FROM AdventureWorks2008.Production.ProductModel
WHERE [CatalogDescription] IS NOT NULL ) ;

SET @xml.modify('
declare namespace p1="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription" ;
insert sql:variable("@InvalidNode") 
after (/p1:ProductDescription/p1:Summary)[1]'
) ;
 

SELECT @xml ;

INSERT INTO [dbo].[Test_ProductModel_Content](  [CatalogDescription] )
VALUES ( @xml ) ;
GO


Hopefully I’ve shown some of the things you can do with XML schemas and this blog post will be useful when you need to use them.

Filed under: ,
Converting Deadlock Reports to Graphs
06 November 10 05:34 PM | MartinBell | with no comments

I’d only just posted my blog about persisting deadlock reports from extended events when I had the following email conversation with David Betteridge

David: It sounds like you hit the same issues as me,  the deadlock xml is invalid until you apply CU1 to 2008 R2.  Only then you discover that xml format isn’t the same as a .xdl file!

Me: Yes, maybe the next thing to do is work out a transformation to convert it.

David: The main difference seems to be that profiler only supports a single deadlock victim and so includes the victim’s processID in deadlock element

<deadlock victim="process7d2988">

However,  extended events now support multiple victims and has a slightly different structure

  <victim-list>

    <victimProcess id="process13f708" />

  </victim-list>

I should be able to write an xslt to transform one structure to another without too much hassle.  Then it’s just a question of working out how to call it.  Maybe powershell?

And low and behold in my inbox the next day I get the following email from David:

Attached is an xslt and powershell script which will convert a deadlock file generated by extended events into a format suitable for display within SSMS.

Steps are…

· Get the deadlock_report from the ring buffer

· Save the xml as a file on the local disk.

· Run the attached powershell script and pass in the full path as a parameter

· It generates a new file with a .xdl extension.

It worked on my single test case!

I checked my test case and it certainly does work, providing you have the cumulative updates that produce valid XML. When you have multiple victims it will only display one victim.

To run this from a command prompt use the command:

powershell –file convert.ps1 <xml_file>

You will need an execution policy that will allow scripts to be run (hopefully not unrestricted!). You will either need to add a path to the location of transform.xslt or execute the script in the same directory. If you want to read more about the changes to the deadlock graphs in Extended Events check out Jonathan's post.

Here are David’s files.

 

The Colditz upgrade
04 November 10 10:17 AM | MartinBell | with no comments

The other day I watched a documentary on the Colditz prisoner of war camp. Whilst you may have thought that Colditz from a British perspective had been “documentaried” to the limit one thing they mentioned did surprise me. Apparently in conjunction to any escape attempt, additional prisoners would be hidden somewhere in the castle. This struck me as a remarkable piece of forward planning.

I was reminded of this when I read Martin Croft’s post on 5 recommendations for a successful upgrade. You may ask why? Previously I have blogged about the using the Database Upgrade Advisor not only for an upgrade, but also as part of the development cycle to remove upgrade issues before you upgrade. The reasoning is simple, the fewer changes you make during an upgrade, the less likely something will go wrong. Remember features deprecated in version n will not be in version n+1!

So my first recommendation to anyone planning an upgrade would be to make sure you start planning early, even if you have no immediate plans for escape an upgrade.

Filed under:
Extended Events and Deadlock Graphs
03 November 10 08:10 PM | MartinBell | 1 comment(s)

Extended events are a new feature in SQL Server 2008 available in the Enterprise Edition. When I demonstrate Extended events, two questions are usually asked.

1. Can I raise an alert when an extended event is raised?

2. Can I write to a Service Broker queue?

Unfortunately, at the moment the answer to both is no! The reasoning behind the questions lie the questioner’s desire to pro-active monitor SQL Server.

So without these two how useful are Extended Events? I think that they are still very useful. SQL Server Auditing is based on Extended Events so anyone wanting to use SQL Server Audit to monitor system or database configuration changes or audit DML statements on their systems you will be using them even if they don’t realise it.

Articles like
this from MVP Jonathan Kehayias shows you how you can extract deadlock graphs from the default System Health session. One thing to remember when using a ring buffers target for an extended event is that they have a limited size and to avoid becoming full they are maintained on a FIFO (first-in first-out) basis. In the case of the System Health session this is a limit of 4MB. As the System Health session monitors several events it is possible that your deadlock graphs are forced out of the ring buffer, therefore I would recommend that, if you want to be certain of obtaining the deadlock graph, then you create your own Extended Event session. This is very easy if you have the SQL Server 2008 Extended Events SSMS Addin from Codeplex installed on your system. With this you can script out the System Health session and modify it to your own requirements. In true Blue Peter fashion here is one I prepared earlier!!

CREATE EVENT SESSION [deadlock_capture]
ON SERVER
ADD
EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.ring_buffer(
     SET max_memory=4096)
WITH (MAX_MEMORY = 4096KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 30 SECONDS, MAX_EVENT_SIZE = 0KB, MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF, STARTUP_STATE = ON)

ALTER EVENT SESSION [deadlock_capture] ON SERVER STATE = START

Unfortunately this doesn’t mean that all your problems have been solved, as a ring buffer is held in memory, when you stop the session the information collected will be lost, this also means that when you cycle SQL Server the information is lost. You will therefore need to persist the data in some way.

In my article on WMI Event alerts I used the database DBAEventManagement and a table DeadlockEvents to store deadlock graphs, so I will re-use the table to store deadlock graphs from the Extended Event.

You could write a SQL Agent job that checks the ring buffer for new data after a specific period. A full example script for such a job can be found here, but the step is as follows:

SET QUOTED_IDENTIFIER ON ;
DECLARE @startdate datetime2 ;

SET @startdate = ISNULL(( SELECT MAX(alerttime)  FROM dbo.DeadlockEvents ),'20000101') ;

WITH ring_buffer ( xmlcol )
AS (
       SELECT CONVERT(XML, st.target_data) as ring_buffer
       FROM sys.dm_xe_sessions s
       JOIN sys.dm_xe_session_targets st ON st.event_session_address = s.[address]
       WHERE s.name = 'deadlock_capture'
)
INSERT INTO dbo.DeadlockEvents ( AlertTime, DeadlockGraph )
SELECT CAST(T2.evntdata.value('(@timestamp)[1]','varchar(24)') AS datetime2) AS [AlertTime]
       ,CAST(T2.evntdata.value('(data[@name="xml_report"]/value)[1]','varchar(max)') AS XML) AS
deadlock_report
FROM ring_buffer
CROSS APPLY xmlcol.nodes('/RingBufferTarget/event[@timestamp > sql:variable("@startdate") and @name="xml_deadlock_report"]') as T2(evntdata) ;

You may notice that I don't do the changes for the mal-formed XML in the deadlock graph, if you are running SQL Server 2008 SP1 CU6 or higher this will not be necessary, also the RTM version of SQL Server 2008 R2 has the same problem which is fixed in CU1.

For anyone who's not looked at deadlock capture before will need to know that when you use the ring buffer target, rather than create new rows for each deadlock only one row is created and the XML within the data is updated for each subsequent deadlock graph. This may save a little space (153 bytes per deadlock graph!), but it does make it slightly more difficult to process individual deadlock graphs when persisting them in a table.  This is an example of a single deadlock and this is an example of two deadlocks. If you look at Jonathan’s solution you will see that using CROSS APPLY and the nodes function will allow you to retrieve each event as a separate row. For my SQL Agent job, I also do the event name check within XQuery for the nodes method and I also check the timestamp attribute for the event against the most recent timestamp of the deadlock graphs (AlertTime) stored, this makes sure I don’t insert duplicate rows.

Using a polling method may not be a suitable way to gather this information especially on a heavily loaded system. Although you can’t raise an alert directly from an Extended Event, it would be possible to use the Extended Event data in conjunction to a WMI Alert. As the deadlock graph can be retrieved from the EventData of the WMI Alert why use the Extended Event? One reason is, that to extract the deadlock graph, you have to enable token replacement in SQL Agent Alerts (see my previous article). This could have a security implications and retrieving the deadlock graph from an Extended Event will not require token replacement to be enabled.

To create the job to service the WMI alert use the script here. If you look at the job step you will see the following SQL:

SET QUOTED_IDENTIFIER ON ;

DECLARE @startdate datetime2, @time datetime, @rowcount int, @max_iterations int = 5 ;

SET @startdate = ISNULL(( SELECT MAX(alerttime)  FROM dbo.DeadlockEvents ),'20000101') ;

SELECT @rowcount = 0 , @time = DATEADD (ms,max_dispatch_latency,0)
FROM sys.server_event_sessions
WHERE name = 'deadlock_capture' ;

WHILE @rowcount = 0 AND @max_iterations > 0
BEGIN
       WITH ring_buffer ( xmlcol )
       AS (
              SELECT CONVERT(XML, st.target_data) as ring_buffer
              FROM sys.dm_xe_sessions s
              JOIN sys.dm_xe_session_targets st ON st.event_session_address = s.[address]
              WHERE s.name = 'deadlock_capture'
       )
       INSERT INTO dbo.DeadlockEvents ( AlertTime, DeadlockGraph )
       SELECT CAST(T2.evntdata.value('(@timestamp)[1]','varchar(24)') AS datetime2) AS [AlertTime]
              ,CAST(T2.evntdata.value('(data[@name="xml_report"]/value)[1]','varchar(max)') AS XML) AS deadlock_report
       FROM ring_buffer
       CROSS APPLY xmlcol.nodes('/RingBufferTarget/event[@timestamp > sql:variable("@startdate") and @name="xml_deadlock_report"]') as T2(evntdata) ;

       SET @rowcount = @@ROWCOUNT ;

       IF @rowcount = 0
              WAITFOR DELAY @time ;

       SET @max_iterations-=1 ;
END

The SQL for this step is more complex than the SQL used in the polling job. The additional logic is required because writing to the ring buffer is an asynchronous  task. Therefore when the WMI Alert fires the data may not be in the Extended Event’s target ring buffer. To cater for this, I do a check to see if a row has been inserted into the DeadlockEvents table. If it hasn’t I wait for a given period and retry. The delay is governed by the maximum dispatch latency configured for the Extended Event Session, if you want you can configure this to less than this maximum. I also have a backup counter that will mean that the loop will only iterate a maximum number of times.

To create the WMI alert you can use the following script that will run the Deadlock Capture job if an alert is raised:

USE [msdb]
GO

DECLARE
@job_id uniqueidentifier ;

SELECT @job_id = job_id FROM msdb.dbo.sysjobs_view WHERE name = N'Deadlock Capture'

EXEC msdb.dbo.sp_add_alert @name=N'Deadlock Capture',
              @message_id=0,
              @severity=0,
              @enabled=1,
              @delay_between_responses=0,
              @include_event_description_in=0,
              @category_name=N'[Uncategorized]',
              @wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
              @wmi_query=N'SELECT * FROM DEADLOCK_GRAPH',
              @job_id=@job_id
GO

If you wanted to be emailed when a deadlock occurs you can add notifications to this alert.

I should also point out that saving the deadlock graph from the Extended Event as an .xdl file, will not allow SSMS to display it in a graphical format as the deadlock graphs from Extended Events use a different schema to those created using the SQL Trace event.

This Blog

SQL Blogs

Syndication