January 3, 2018 at 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
January 3, 2018 at 10:20 am
SQL Guy 1 - Wednesday, January 3, 2018 9:25 AMBefore, 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. 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 3, 2018 at 11:29 am
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
January 3, 2018 at 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.🙂
GilaMonster: tried to google it but unfortunately could not find anything like that.
January 3, 2018 at 12:04 pm
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
January 3, 2018 at 8:46 pm
SQL Guy 1 - Wednesday, January 3, 2018 11:43 AMTheSQLGuru: 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.🙂
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. 😀
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 4, 2018 at 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).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 4, 2018 at 2:13 pm
grant@scarydba.com - Thursday, January 4, 2018 5:56 AMThe 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:
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. 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply