SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Better Way of XQuerying


Better Way of XQuerying

Author
Message
jvkondapalli
jvkondapalli
SSC Veteran
SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)

Group: General Forum Members
Points: 277 Visits: 684
Gurus, I am very new to XQuery. Trying to figure out how I can make use of extended events to monitor long running queries in my system.

I know, I am not thinking straight and pretty sure there must be a better way of doing this. So, I am here to seek your help. Please help. Below are the XML and SQL Code that I started.

Basically, my ultimate goal is to get eventname, timestamp, dataname, data value, data type name, action name, action value in the same select result set. Please help.

<RingBufferTarget truncated="0" processingTime="0" totalEventsProcessed="77" eventCount="77" droppedCount="0" memoryUsed="227166">
<event name="sql_statement_starting" package="sqlserver" timestamp="2013-02-19T02:55:08.072Z">
<data name="state">
<type name="statement_starting_state" package="sqlserver" />
<value>0</value>
<text>Normal</text>
</data>
<data name="line_number">
<type name="int32" package="package0" />
<value>1</value>
</data>
<data name="offset">
<type name="int32" package="package0" />
<value>0</value>
</data>
<data name="offset_end">
<type name="int32" package="package0" />
<value>-1</value>
</data>
<data name="statement">
<type name="unicode_string" package="package0" />
<value>USE [master]</value>
</data>
<action name="username" package="sqlserver">
<type name="unicode_string" package="package0" />
<value>LEGALZOOM\jvkondapalli</value>
</action>
<action name="tsql_stack" package="sqlserver">
<type name="xml" package="package0" />
<value>
<frames>
<frame level="1" handle="0x010001005307591E009CA8D90200000000000000000000000000000000000000000000000000000000000000" line="1" offsetStart="0" offsetEnd="-1" />
</frames>
</value>
</action>
<action name="tsql_frame" package="sqlserver">
<type name="xml" package="package0" />
<value>
<frame level="0" handle="0x010001005307591E009CA8D90200000000000000000000000000000000000000000000000000000000000000" line="1" offsetStart="0" offsetEnd="-1" />
</value>
</action>
<action name="transaction_sequence" package="sqlserver">
<type name="uint64" package="package0" />
<value>0</value>
</action>
<action name="transaction_id" package="sqlserver">
<type name="int64" package="package0" />
<value>0</value>
</action>
<action name="sql_text" package="sqlserver">
<type name="unicode_string" package="package0" />
<value>USE [master]</value>
</action>
<action name="session_resource_group_id" package="sqlserver">
<type name="uint32" package="package0" />
<value>2</value>
</action>
<action name="session_nt_username" package="sqlserver">
<type name="unicode_string" package="package0" />
<value>LEGALZOOM\jvkondapalli</value>
</action>
<action name="session_id" package="sqlserver">
<type name="uint16" package="package0" />
<value>55</value>
</action>
<action name="server_instance_name" package="sqlserver">
<type name="unicode_string" package="package0" />
<value>GIT3JVKONDA</value>
</action>
<action name="query_plan_hash" package="sqlserver">
<type name="uint64" package="package0" />
<value>0</value>
</action>
<action name="query_hash" package="sqlserver">
<type name="uint64" package="package0" />
<value>0</value>
</action>
<action name="plan_handle" package="sqlserver">
<type name="binary_data" package="package0" />
<value>060001005307591e40bdaad90200000001000000000000000000000000000000000000000000000000000000</value>
</action>
<action name="is_system" package="sqlserver">
<type name="boolean" package="package0" />
<value>false</value>
</action>
<action name="database_name" package="sqlserver">
<type name="unicode_string" package="package0" />
<value>master</value>
</action>
<action name="database_id" package="sqlserver">
<type name="uint16" package="package0" />
<value>1</value>
</action>
<action name="client_hostname" package="sqlserver">
<type name="unicode_string" package="package0" />
<value>GIT3JVKONDA</value>
</action>
<action name="client_app_name" package="sqlserver">
<type name="unicode_string" package="package0" />
<value>Microsoft SQL Server Management Studio</value>
</action>
<action name="task_time" package="sqlos">
<type name="uint64" package="package0" />
<value>15969513</value>
</action>
<action name="task_elapsed_quantum" package="sqlos">
<type name="uint64" package="package0" />
<value>0</value>
</action>
<action name="task_address" package="sqlos">
<type name="ptr" package="package0" />
<value>0x00000002e4013868</value>
</action>
<action name="scheduler_id" package="sqlos">
<type name="uint32" package="package0" />
<value>0</value>
</action>
<action name="scheduler_address" package="sqlos">
<type name="ptr" package="package0" />
<value>0x00000002e4140040</value>
</action>
<action name="cpu_id" package="sqlos">
<type name="uint32" package="package0" />
<value>0</value>
</action>
<action name="last_error" package="package0">
<type name="uint32" package="package0" />
<value>0</value>
</action>
<action name="event_sequence" package="package0">
<type name="uint64" package="package0" />
<value>1</value>
</action>
<action name="collect_system_time" package="package0">
<type name="filetime" package="package0" />
<value>2013-02-19T02:55:08.072Z</value>
</action>
<action name="collect_cpu_cycle_time" package="package0">
<type name="cpu_cycle" package="package0" />
<value>978307646018484</value>
</action>
</event>
<event name="sql_statement_starting" package="sqlserver" timestamp="2013-02-19T02:55:14.686Z">
<data name="state">
<type name="statement_starting_state" package="sqlserver" />
<value>0</value>
<text>Normal</text>
</data>
<data name="line_number">
<type name="int32" package="package0" />
<value>1</value>
</data>
<data name="offset">
<type name="int32" package="package0" />
<value>0</value>
</data>
<data name="offset_end">
<type name="int32" package="package0" />
<value>-1</value>
</data>
<data name="statement">
<type name="unicode_string" package="package0" />
<value>SELECT
dtb.name AS [Name],
dtb.database_id AS [ID]
FROM
master.sys.databases AS dtb
ORDER BY
[Name] ASC</value>
</data>
<action name="username" package="sqlserver">
<type name="unicode_string" package="package0" />
<value>LEGALZOOM\jvkondapalli</value>
</action>
<action name="tsql_stack" package="sqlserver">
<type name="xml" package="package0" />
<value>
<frames>
<frame level="1" handle="0x02000000C382AC2696F6952BF2E6CE3813C6B794735D856C0000000000000000000000000000000000000000" line="1" offsetStart="0" offsetEnd="-1" />
</frames>
</value>
</action>
<action name="tsql_frame" package="sqlserver">
<type name="xml" package="package0" />
<value>
<frame level="0" handle="0x02000000C382AC2696F6952BF2E6CE3813C6B794735D856C0000000000000000000000000000000000000000" line="1" offsetStart="0" offsetEnd="-1" />
</value>
</action>
<action name="transaction_sequence" package="sqlserver">
<type name="uint64" package="package0" />
<value>0</value>
</action>
<action name="transaction_id" package="sqlserver">
<type name="int64" package="package0" />
<value>0</value>
</action>
<action name="sql_text" package="sqlserver">
<type name="unicode_string" package="package0" />
<value>SELECT
dtb.name AS [Name],
dtb.database_id AS [ID]
FROM
master.sys.databases AS dtb
ORDER BY
[Name] ASC</value>
</action>
<action name="session_resource_group_id" package="sqlserver">
<type name="uint32" package="package0" />
<value>2</value>
</action>
<action name="session_nt_username" package="sqlserver">
<type name="unicode_string" package="package0" />
<value>LEGALZOOM\jvkondapalli</value>
</action>
<action name="session_id" package="sqlserver">
<type name="uint16" package="package0" />
<value>57</value>
</action>
<action name="server_instance_name" package="sqlserver">
<type name="unicode_string" package="package0" />
<value>GIT3JVKONDA</value>
</action>
<action name="query_plan_hash" package="sqlserver">
<type name="uint64" package="package0" />
<value>9240582280910057056</value>
</action>
<action name="query_hash" package="sqlserver">
<type name="uint64" package="package0" />
<value>2744306676576674402</value>
</action>
<action name="plan_handle" package="sqlserver">
<type name="binary_data" package="package0" />
<value>06000100c382ac2630d31cd80200000001000000000000000000000000000000000000000000000000000000</value>
</action>
<action name="is_system" package="sqlserver">
<type name="boolean" package="package0" />
<value>false</value>
</action>
<action name="database_name" package="sqlserver">
<type name="unicode_string" package="package0" />
<value>master</value>
</action>
<action name="database_id" package="sqlserver">
<type name="uint16" package="package0" />
<value>1</value>
</action>
<action name="client_hostname" package="sqlserver">
<type name="unicode_string" package="package0" />
<value>GIT3JVKONDA</value>
</action>
<action name="client_app_name" package="sqlserver">
<type name="unicode_string" package="package0" />
<value>Microsoft SQL Server Management Studio - Transact-SQL IntelliSense</value>
</action>
<action name="task_time" package="sqlos">
<type name="uint64" package="package0" />
<value>2697572</value>
</action>
<action name="task_elapsed_quantum" package="sqlos">
<type name="uint64" package="package0" />
<value>0</value>
</action>
<action name="task_address" package="sqlos">
<type name="ptr" package="package0" />
<value>0x00000002e401d498</value>
</action>
<action name="scheduler_id" package="sqlos">
<type name="uint32" package="package0" />
<value>1</value>
</action>
<action name="scheduler_address" package="sqlos">
<type name="ptr" package="package0" />
<value>0x00000002e4160040</value>
</action>
<action name="cpu_id" package="sqlos">
<type name="uint32" package="package0" />
<value>1</value>
</action>
<action name="last_error" package="package0">
<type name="uint32" package="package0" />
<value>0</value>
</action>
<action name="event_sequence" package="package0">
<type name="uint64" package="package0" />
<value>2</value>
</action>
<action name="collect_system_time" package="package0">
<type name="filetime" package="package0" />
<value>2013-02-19T02:55:14.686Z</value>
</action>
<action name="collect_cpu_cycle_time" package="package0">
<type name="cpu_cycle" package="package0" />
<value>978324800911629</value>
</action>
</event>
</RingBufferTarget>



-- Idea is to check How many events are there in the .xel file. 
-- Iterate through the event to grab what I want.
-- When I look at my half completed code, makes me feel like,
-- I am not using set theory here. I hate using loops when set theory can be applied.
DECLARE @xmlTable TABLE (XMLDATA XML)
INSERT INTO @xmlTable
SELECT CAST(dt.target_data AS XML)
FROM sys.dm_xe_session_targets dt
JOIN sys.dm_xe_sessions ds ON ds.Address = dt.event_session_address
JOIN sys.server_event_sessions ss ON ds.Name = ss.Name
WHERE dt.target_name = 'ring_buffer'
AND ds.Name = 'EE_LongRunningQueries'

DECLARE @EventData TABLE (ID INT IDENTITY(1,1) ,Eventname VARCHAR(MAX), RecordTime DATETIME)
INSERT INTO @EventData
SELECT d.x.value('(@name)[1]', 'VARCHAR(MAX)') AS EventName ,
d.x.value('(@timestamp)[1]', 'DATETIME') AS RecordTime
FROM @xmlTable
CROSS APPLY XMLdata.nodes('//RingBufferTarget/event') AS d ( x )

DECLARE @EventCount INT
, @LogID INT
, @TimeStamp DATETIME

SELECT @EventCount = COUNT(*) FROM @EventData

WHILE (@EventCount >0)
BEGIN
SELECT TOP 1 @LogID = ID, @TimeStamp = RecordTime FROM @EventData
-- select the count of the nodes in the current event
-- run a while loop to get the data inserted to a table
-- you might have to use the count of nodes to acheive this
-- decrement the EventCount variable
END



Thanks
Jagan K
jvkondapalli
jvkondapalli
SSC Veteran
SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)

Group: General Forum Members
Points: 277 Visits: 684
Can one of the gurus help me please...Crying

Thanks
Jagan K
Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20279 Visits: 7660
The short form is that you have to work from the ringbuffertarget\event node for your counts, which you're doing, but then join to another table or XMLdata.node that's deeper to apply the data in a flatter form, so you can query to the right information.

You have to end up normalizing it by calling different nodes simultaneously.

However, it's 1AM here and XQuery is enough to give me fits when I'm wide awake. I know where you're trying to go conceptually but my brain isn't putting the pieces together right. I'll try to come back tomorrow to check up on this thread if my world doesn't asplode with a recent rollout's fallout.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20279 Visits: 7660
JV are you still having difficulties with this, before I spend some time on it?


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
jvkondapalli
jvkondapalli
SSC Veteran
SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)

Group: General Forum Members
Points: 277 Visits: 684
Hello, Thank you so much for responding for my thread. I am going to share my thoughts here out loud so that someone can guide me in the right direction. To start with, This is what I wrote. Trick is the CROSS APPLY.
DECLARE @xml XML
SELECT @xml = CAST(dt.target_data AS XML)
FROM sys.dm_xe_session_targets dt
JOIN sys.dm_xe_sessions ds ON ds.Address = dt.event_session_address
JOIN sys.server_event_sessions ss ON ds.Name = ss.Name
WHERE dt.target_name = 'ring_buffer'
AND ds.Name = 'EE_LongRunningQueries'

SELECT
Events.Info.value('@name', 'VARCHAR(MAX)'),
Events.Info.value('@timestamp', 'DATETIME'),
Datas.ChildInfo.value('@name', 'VARCHAR(MAX)'),
Datas.ChildInfo.value('value[1]','VARCHAR(MAX)')
FROM @xml.nodes('/RingBufferTarget/event') AS Events(Info)
CROSS APPLY Events.Info.nodes('*') AS Datas(childInfo)



If you have any better thoughts, please share with me.

Reading few blogs online on EEs made me realize how power EE is. I want to use it more extensively for monitoring. Currently, EE in my hand is like tool with a fool.

Driving the discussion further X-query -

I want to add some more information so that you can guide me.

What we have today:

we query exec_requests and sessions and get the list of queries running in our system every 1 minute. If anything is running more than 1 minute, we will alert ourselves and get hold of developer to fix the code or add indexes. We also have a config table to exclude certain legacy stuff and maintenance jobs.

What we want to do:

Firstly, I want to know, if our current monitoring system for the sqls running in our system is good or not. Also, we want to use extended events(EE) to monitor our system. the vision I have in my mind is to grab everything that is running on the system through EE, and have a job to wake up every minute look back 1 minute based on timestamp and alert us if there is no sql_statement_completed event is not associated with that session id. This way, I know I am not missing any queries within that 1 minute window. Please let me know if my vision is in the correct path.

Also, I want to know, what else, I can do with EEs. I have plans of collecting dead lock reports. logical drive counters(I know Johnathan has a ticket opened for this. waiting for that ticket to be addressed.)

Please help the fool to use the tool!

Thanks
Jagan K
jvkondapalli
jvkondapalli
SSC Veteran
SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)

Group: General Forum Members
Points: 277 Visits: 684
Hello, Thank you so much for responding for my thread. I am going to share my thoughts here out loud so that someone can guide me in the right direction. To start with, This is what I wrote. Trick is the CROSS APPLY.
DECLARE @xml XML
SELECT @xml = CAST(dt.target_data AS XML)
FROM sys.dm_xe_session_targets dt
JOIN sys.dm_xe_sessions ds ON ds.Address = dt.event_session_address
JOIN sys.server_event_sessions ss ON ds.Name = ss.Name
WHERE dt.target_name = 'ring_buffer'
AND ds.Name = 'EE_LongRunningQueries'

SELECT
Events.Info.value('@name', 'VARCHAR(MAX)'),
Events.Info.value('@timestamp', 'DATETIME'),
Datas.ChildInfo.value('@name', 'VARCHAR(MAX)'),
Datas.ChildInfo.value('value[1]','VARCHAR(MAX)')
FROM @xml.nodes('/RingBufferTarget/event') AS Events(Info)
CROSS APPLY Events.Info.nodes('*') AS Datas(childInfo)



If you have any better thoughts, please share with me.

Reading few blogs online on EEs made me realize how power EE is. I want to use it more extensively for monitoring. Currently, EE in my hand is like tool with a fool.

Driving the discussion further X-query -

I want to add some more information so that you can guide me.

What we have today:

we query exec_requests and sessions and get the list of queries running in our system every 1 minute. If anything is running more than 1 minute, we will alert ourselves and get hold of developer to fix the code or add indexes. We also have a config table to exclude certain legacy stuff and maintenance jobs.

What we want to do:

Firstly, I want to know, if our current monitoring system for the sqls running in our system is good or not. Also, we want to use extended events(EE) to monitor our system. the vision I have in my mind is to grab everything that is running on the system through EE, and have a job to wake up every minute look back 1 minute based on timestamp and alert us if there is no sql_statement_completed event is not associated with that session id. This way, I know I am not missing any queries within that 1 minute window. Please let me know if my vision is in the correct path.

Also, I want to know, what else, I can do with EEs. I have plans of collecting dead lock reports. logical drive counters(I know Johnathan has a ticket opened for this. waiting for that ticket to be addressed.)

Please help the fool to use the tool!

Thanks
Jagan K
Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20279 Visits: 7660
Sorry Jagan, I haven't done much work with Extended Events myself, so I'm not sure I can help you much there. I've put out a request to some others to check in on this and see if they can offer some wisdom.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97325 Visits: 33013
Instead of running XML queries against the server, a relatively high process event, I'd output the Extended Events to a file, then you can read that file from another server and never impact your production machine in any way. That approach makes a lot more sense to me.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212301 Visits: 41977
Grant Fritchey (2/22/2013)
Instead of running XML queries against the server, a relatively high process event, I'd output the Extended Events to a file, then you can read that file from another server and never impact your production machine in any way. That approach makes a lot more sense to me.


I've been up to my eyes with work and they only have SQL Server 2005 so I haven't even broken the surface on Extended Events. That explains my apparent shock and mortification when I say "REALLY!!!! They screwed EE up with XML???" Sick

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97325 Visits: 33013
Jeff Moden (2/22/2013)


I've been up to my eyes with work and they only have SQL Server 2005 so I haven't even broken the surface on Extended Events. That explains my apparent shock and mortification when I say "REALLY!!!! They screwed EE up with XML???" Sick


Ha! The output is XML, yes. But, you have options to avoid it. There's the Data Explorer in 2012 that lets you just look at the output from the buffer or open files, no XML required. There is also a way to query the files directly through an extended stored procedure, again, avoiding XML.

However, if you want direct, raw access to the data, yep, it's XML. Personally, I'm OK with that.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search