Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Better Way of XQuerying Expand / Collapse
Author
Message
Posted Tuesday, February 19, 2013 1:37 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 16, 2014 2:19 PM
Points: 58, Visits: 539
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
Post #1421470
Posted Tuesday, February 19, 2013 5:40 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 16, 2014 2:19 PM
Points: 58, Visits: 539
Can one of the gurus help me please...

Thanks
Jagan K
Post #1421902
Posted Wednesday, February 20, 2013 1:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:43 AM
Points: 5,401, Visits: 7,512
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
Post #1421968
Posted Thursday, February 21, 2013 4:24 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:43 AM
Points: 5,401, Visits: 7,512
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
Post #1422825
Posted Thursday, February 21, 2013 5:36 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 16, 2014 2:19 PM
Points: 58, Visits: 539
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
Post #1422832
Posted Thursday, February 21, 2013 6:20 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 16, 2014 2:19 PM
Points: 58, Visits: 539
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
Post #1422836
Posted Friday, February 22, 2013 12:18 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:43 AM
Points: 5,401, Visits: 7,512
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
Post #1423211
Posted Friday, February 22, 2013 1:37 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:30 AM
Points: 13,864, Visits: 28,260
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1423244
Posted Friday, February 22, 2013 1:49 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:45 PM
Points: 35,342, Visits: 31,875
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???"


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1423251
Posted Friday, February 22, 2013 1:54 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:30 AM
Points: 13,864, Visits: 28,260
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???"


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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1423254
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse