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


Replayable Extended Events?


Replayable Extended Events?

Author
Message
SQL Guy 1
SQL Guy  1
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13810 Visits: 2779
Before, when we needed to collect workload from production, we used to run replayable trace. But in new versions, since SQL Server is moving away from trace, is it possible to get a workload from X.E. which will be replayable on another server?

Thanks
TheSQLGuru
TheSQLGuru
SSC Guru
SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)

Group: General Forum Members
Points: 121569 Visits: 8988
SQL Guy 1 - Wednesday, January 3, 2018 9:25 AM
Before, when we needed to collect workload from production, we used to run replayable trace. But in new versions, since SQL Server is moving away from trace, is it possible to get a workload from X.E. which will be replayable on another server?

Thanks

No, it is not. This is one of many reasons why I will still be using SQL Profiler/Trace 17 years from now. I mention that number of years because SQL Server 2017 still has SQL Profiler and I still have clients using SQL 2000. Cool


Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
GilaMonster
GilaMonster
SSC Guru
SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)

Group: General Forum Members
Points: 902866 Visits: 48741
There is a way to convert an extended events output to trace so that DReplay can read it. Don't have links offhand, google should turn them up.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


SQL Guy 1
SQL Guy  1
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13810 Visits: 2779

TheSQLGuru: that sounds encouraging. The only reason why our managers don't want to run trace (not Profiler but script-based tracing) is that it has greater impact than X.E. and can hamper production performance.

B.T.W., while I was reading your post I thought that you are going to retire in 17 years from now.Smile


GilaMonster: tried to google it but unfortunately could not find anything like that.


GilaMonster
GilaMonster
SSC Guru
SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)

Group: General Forum Members
Points: 902866 Visits: 48741
Google search for "DReplay Extended Events" First result:
https://borishristov.com/blog/t-sql-tuesday-67-extended-events-and-distributed-replay/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


TheSQLGuru
TheSQLGuru
SSC Guru
SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)

Group: General Forum Members
Points: 121569 Visits: 8988
SQL Guy 1 - Wednesday, January 3, 2018 11:43 AM

TheSQLGuru: that sounds encouraging. The only reason why our managers don't want to run trace (not Profiler but script-based tracing) is that it has greater impact than X.E. and can hamper production performance.

B.T.W., while I was reading your post I thought that you are going to retire in 17 years from now.Smile


GilaMonster: tried to google it but unfortunately could not find anything like that.


I have run traces to local disk on multiple systems with many thousands of SQL batches per second with VERY little overhead. Most benchmarks I have seen do stupid tracing, including collecting numerous BLOATED fields. My profiler trace has just 9 fields. TextData is the only bloated one, but that's the queries so you got to have it. There is one datetime, and the rest are various numeric types.

I love what I do too much to retire I think. :-D


Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (354K reputation)SSC Guru (354K reputation)SSC Guru (354K reputation)SSC Guru (354K reputation)SSC Guru (354K reputation)SSC Guru (354K reputation)SSC Guru (354K reputation)SSC Guru (354K reputation)

Group: General Forum Members
Points: 354341 Visits: 34353
The principal reason to use Extended Events isn't performance (although, filtering). There are two primary reasons. One, absolutely no new functionality has been added to Trace since 2008. Nothing. Anything on the servers you need to monitor beyond queries, you're going to have to start to use ExEvents. Two, the ability to chain the events together (although this adds some overhead) is completely indispensable now. It's like SQL Prompt. I don't know how I operated without it. There are a whole bunch of other bells and whistles that people miss or don't understand, but those are the two big ones for me (plus filtering).

----------------------------------------------------
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 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
Product Evangelist for Red Gate Software
TheSQLGuru
TheSQLGuru
SSC Guru
SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)

Group: General Forum Members
Points: 121569 Visits: 8988
grant@scarydba.com - Thursday, January 4, 2018 5:56 AM
The principal reason to use Extended Events isn't performance (although, filtering). There are two primary reasons. One, absolutely no new functionality has been added to Trace since 2008. Nothing. Anything on the servers you need to monitor beyond queries, you're going to have to start to use ExEvents. Two, the ability to chain the events together (although this adds some overhead) is completely indispensable now. It's like SQL Prompt. I don't know how I operated without it. There are a whole bunch of other bells and whistles that people miss or don't understand, but those are the two big ones for me (plus filtering).

Counter-Point:

1) There's a LOT of traceable events other than those related to queries:

Trace Events List

2) As you know Grant all I do is SQL Server consulting, and have been for about 25 years now. Since XEs were first released, I have had to use them just TWO TIMES to solve a client need that could not be uncovered via some other means. And for those I just copied-and-pasted code from elsewhere. The overhead of learning how to effectively use them is still huge in my book, irrespective of improved GUIness. There is also quite a number of scenarios where you can harm (sometimes substantially so) the SQL Server by using them (adding to the overhead of learning them - making darn sure you know what NOT to do). I know how to avoid those issues with tracing. COULD XEs make my job easier? Quite possibly. But I still haven't crossed the threshold where the need is greater than the effort to really learn them.

3) I note that sp_trace_generateevent is NOT deprecated, and using that with user-defined eventIDs is a powerful debugging tool for application code and functionality.

4) I will definitely acknowledge that if you are using some new-fangled feature that came out since tracing was put on the deprecated list and you need to see what is going on with it or troubleshoot beyond built-in system sprocs/GUIs then yep, XEs are your tool because tracing simply doesn't know about them.

To anyone reading this I note that YMMV. Cool


Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
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