Feeds:
Posts
Comments

Quick one today

Once in a while, we need to process XML files (a.k.a. deadlock XML files) to retrieve some pertinent information to uncover the deadlock patterns.

In the past, we’ve seen some XML DML queries to parse XML files.

Here we’ll focus on filtering capability based on attribute values in elements:

Take an deadlock XML for example:

Deadlock_Filter_Attribute

The goal is to search for the action element with attribute value as “collect_system_time“.

--
-- Parse & Filter XMl file data
--
SELECT
	  ROW_NUMBER() OVER(ORDER BY DeadLockXML.value('(/event/@timestamp)[1]', 'DATETIME2'))											AS [RowN]
	, DeadLockXML.value('(/event/action[@name="collect_system_time"]/value)[1]', 'DATETIME') AS SystemTime
FROM  dbo.Deadlocks_07262018_Step2
GO

The [] allow us to provide the value to filter the XMl elements:

(/event/action[@name="collect_system_time"]/value)[1]'

 

Hope this helps,
_Sqltimes

 

Advertisements

Quick one today:

Quite often, we all run into this scenario where we need to convert column data into row with either comma or some other delimiter.

Below, we’ll see a couple of ways; Each serving a different purpose:

Build data set first:

--
--  Convert row data into column (without using PIVOT)
--

-- Create tables
CREATE TABLE Parent (
	  ID	INT			IDENTITY(1,1)
	, Name	VARCHAR(20)
)

CREATE TABLE Child (
	  ID		INT		IDENTITY(1,1)
	, ParentID	INT
	, Name		VARCHAR(25)
)

-- Create Parent records
INSERT INTO Parent (Name) VALUES ('Mary')
INSERT INTO Parent (Name) VALUES ('Patel')
INSERT INTO Parent (Name) VALUES ('Risvic')

-- Create child records
INSERT INTO Child (ParentID, Name) values (1, 'Jil')
INSERT INTO Child (ParentID, Name) values (1, 'Jim')
INSERT INTO Child (ParentID, Name) values (1, 'Kat')

INSERT INTO Child (ParentID, Name) values (2, 'Raja')
INSERT INTO Child (ParentID, Name) values (2, 'Kamat')
GO

SELECT * FROM Parent
SELECT * FROM Child
GO

Data set looks like this:

RowToColumn_DataSet

Option 1 : Using Variable

This method gives us result in comma delimited fashion, but it only works for one parent at a time; And we cannot combine Parent name as a column next to Children column, as Sql Server does not allow combining data-retrieval with variable manipulation query.

It spits out an error like this:

Msg 141, Level 15, State 1, Line 35
A SELECT statement that assigns a value to a variable must not be 
combined with data-retrieval operations.
--
--	Option 1 : using @Variable
--
DECLARE @Children VARCHAR(8000)

SELECT @Children = COALESCE(@Children, '') + ', ' + Name
FROM dbo.Child
WHERE ParentID = 1

SELECT STUFF(@Children, 1, 2, '')
GO

RowToColumn_Option1

Option 2 : Using XML

XML PATH gives us more control over retrieval manipulation. It allows us to retrieve data in a tabular fashion, while keeping the children names into one column.

Breakdown:

This below query has 3 parts:

  1. Main warp-around query (Parent)
  2. STUFF section
  3. XML PATH section
--
--	Using XML
--
SELECT    P.ID	AS [ParentID]
	, P.Name	AS [ParentName]
	, STUFF(
				(
					SELECT ', ' + C.Name
					FROM dbo.Child AS C
					WHERE C.ParentID = P.ID
					FOR XML PATH('')
				)
				, 1, 2, ''
		  ) AS [Children]
FROM dbo.Parent AS P
GO

--
-- Clean up
--
DROP TABLE Parent
DROP TABLE Child
GO

RowToColumn_Option2

We’ll see the details of each query in reverse order:

XML PATH Query

This is the most important part of this whole query. It allows us to retrieve all children of a given parent in a comma delimited fashion.

Without FOR XML PATH(”) statement, this is just a query that returns children as rows. When you add FOR XML, that data set is now converted into XML format (sort of).

PATH (”) makes sure that the the element trees notation is replaced with whatever is in between ” (empty). So we get a simple comma delimited child list.

Now we have an extra comma to remove. That’s where STUFF comes into play.

STUFF query section replaces comma with empty space.

Main wrap-around query is to bring this all together with ParentID & Parent Name to make it more usable.

 

Hope this helps,
_Sqltimes

 

Quick one today:

Extended Events make it easy to capture deadlock details into an XEL file, that has deadlock data in XML format.

This XEL file could be imported into a Sql table using sys.fn_xe_file_target_read_file function; It is similar to ::fn_trace_gettable function to load Sql trace file into a table.


SELECT * INTO dbo.DeadlocksXML
FROM sys.fn_xe_file_target_read_file ('I:\Deadlocks\xml_deadlock_report_0_131771103583570000.xel', null, null, null)
GO

MSDN also has an article on this function.

Hope this helps,
_Sqltimes

 

Quick one today:

Earlier, while troubleshooting deadlocks, we needed a quick way to count the number of victims & processes in each deadlock incident. Since deadlock information is available in XML, we could use COUNT function to measure the occurrences.

Deadlock_Counts

In the past, we’ve covered some techniques to uncover details from deadlock XML data.

XML provides many tools to parse XML files. Below query makes it easy to gather such counts at each level; Be it Victim count or processes count, etc

SELECT
	, DeadLockXML.value('count(/event/data/value/deadlock/victim-list/victimProcess)', 'INT') AS [Count_Victims]
FROM dbo.DeadlocksXMLTable
GO

We could send XML Path we want to count as a parameter to COUNT function to gather the number.

DeadLockXML.value(‘count(/event/data/value/deadlock/victim-list/victimProcess)’, ‘INT‘)
Below is an expanded TSQL query that includes more columns from deadlock XML file.

SELECT
	  ROW_NUMBER() OVER(ORDER BY DeadLockXML.value('(/event/@timestamp)[1]', 'DATETIME2'))											AS [RowN]
	, DeadLockXML.value('(/event/@timestamp)[1]', 'DATETIME2')																		AS [TimeStamp]
	, DeadLockXML.value('count(/event/data/value/deadlock/victim-list/victimProcess)', 'INT')										AS [Count_Victims]
	, DeadLockXML.value('(/event/data/value/deadlock/victim-list/victimProcess/@id)[1]', 'VARCHAR(100)')							AS [Victim1]
	, DeadLockXML.value('(/event/data/value/deadlock/victim-list/victimProcess/@id)[2]', 'VARCHAR(100)')							AS [Victim2]
	, DeadLockXML.value('(/event/data/value/deadlock/victim-list/victimProcess/@id)[3]', 'VARCHAR(100)')							AS [Victim3]
	, DeadLockXML.value('(/event/data/value/deadlock/victim-list/victimProcess/@id)[4]', 'VARCHAR(100)')							AS [Victim4]
FROM dbo.DeadlocksXMLTable
GO

 

Hope this helps,
_Sqltimes

Quick one today:

XML DML is interesting feature of Sql Server. It is vast and has lots of features in manipulating XML files and its elements & attributes. Today, we’ll look at exists function to check if a particular element exists or an element with a specific attribute exists.

Let’s use this below XML as a sample:

DECLARE   @X XML = '

                Marco
                Polo

                Hello

                Hello

        '

 

XMLDML_Exists_Sample.PNG

Let’s look at a way to check if

  • If a “ParenetElement” element with “Style” attribute exists
  • If a “Child” element with “Polo” as element text value exists

Check at Attribute Level

--
-- Check if ParentElement with Attribute value of "Style" exists
--
DECLARE   @X XML = '

                Marco
                Polo

                Hello

                Hello

        '
SELECT @X.exist('(/Root/ParentElement[@Att1="Style"])') AS [Exists_Or_Not]
GO

XMLDML_Exists

Check Element Text Level

--
-- If a "Child" element with "Polo" as element text value exists
--
DECLARE   @X XML = '

                Marco
                Polo

                Hello

                Hello

        '
SELECT @X.exist('(/Root/ParentElement/Child2[(text()[1] = "Polo" )])') AS [Polo_Exists?]
GO

XMLDML_Exists2.PNG

Hope this helps,
_Sqltimes

Interesting one today:

Earlier, when performing some cluster maintenance work, this error popped-up on the screen.

The specified disk or volume is managed by the Microsoft Failover cluster 
component. The disk must be in the cluster maintenance mode and the 
cluster resource status must be online to perform this operation

When attempting to format a new LUN to 64K allocation unit, this error popped up. Since the LUN/Drive is already added to the cluster, new format changes could not be made.

Resolution:

As the verbose error message suggests, assign this particular disk into “Maintenance Mode”, then perform formatting steps.

Go to Failover Cluster Manager and go to Storage > Disks; Identify the particular disk and right click and go to More Actions >> Turn On Maintenance Mode.

Cluster_LUN_Error

Once the disk is in maintenance mode, you’ll see under Status column as Online (Maintenance Mode).

Now we are free to format the disk. Computer Management >> Storage >> Disk Management go to the individual disk and right click and Format.

FormatClusterLUN

Now formatting works and once completed, go back to Failover Cluster Manager and set the disk back out of Maintenance Mode.

 

Hope this helps,
_Sqltimes

Quick one today:

In the recent past, we’ve seen several XML posts. Today, we’ll cover a way to check if an element text is empty. Let’s take a sample XML file

XMLDML_Exists_Sample

Lets check if the Child2 element in Brand ParentElement is empty or not

XMLDML_EmptyElement

--
-- Check if an element is empty.<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>
--
DECLARE   @X XML = '
        
            
                Marco
                Polo
            
            
                Hello
                
            
            
                Hello
            
        '
--SELECT @X.value('(/Root/ParentElement[@Att1="Brand"]/Child1)[1]', 'VARCHAR(20)')
IF	(
		SELECT @X.value('(/Root/ParentElement[@Att1="Brand"]/Child2)[1]', 'VARCHAR(20)')
	) = ''
	PRINT 'Empty'
ELSE
	PRINT 'Not Empty'

 

Hope this helps,
_Sqltimes