Boris Hristov

Founder of 356labs

Founder of 356labs
and PowerPoint MVP.
Speaker, Trainer & Author.
A guy that loves to do sports, have fun and enjoy life. Gallery / Blog / Contact

Copyright © 2021 ·

T-SQL Tuesday #67:
Extended Events and…
Distributed Replay?

June 9, 2015 By Boris Hristov 8 Comments

TSQL2sDaySome of you may know that I am kind of a huge fan of Distributed Replay – a technology that not that many people know about, but at the same time a feature that can help you a ton especially when you are planning an upgrade, migration or you simply have a performance problem. DReplay has a problem though – you need to capture your application workload and that needs to happen with a Profiler trace and as you probably have heard already – Profiler is deprecated. Recently, however, Robert Dorr from Microsoft just mentioned as part of some other discussion that the latest version of the RML Utilities – 9.04.0004 – can convert an .xel file to a .trc. I immediately asked myself – so does that mean that now I can capture my workload with xEvent session -> convert the workload to a trace file and then replay it with Distributed Replay? Let’s find out…

By the way, this post was not going to be possible(as you will find out why below) without the help of Robert, so thank him and not me!

The first thing I did was to open a Profiler Replay template(as it includes exactly all the events that you need to capture in order to make DReplay work) and take a look at every event that was captured by it and select those same events for my xEvent session. Replay_to_xEvent_Events
Once I have done that, I started the session and ran my workload by retaining the events into a file on my local machine. Once the workload was captured, I downloaded the latest version of the RML Utilities and I remember that Robert mentioned that in order to convert the .xel to .trc we need to use the -M parameter. I did not find any documentation on this, so I started guessing how the command should look like. At one point I ended up having this:

readtrace -I"C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\XECapture_0_130781709497210000.xel" -M"C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\DReplayTRC.trc"

However, when I tried to convert the file it was throwing errors for events that were not captured. That was kinda strange because remember – I mimicked the same events from the Replay template of Profiler. I read the output log which I found in some nasty directory – C:\Users\YourUser\AppData\Local\Temp. Once I reconfigured the session with all the new events, I again captured the workload and tried to convert to a trace file.

…………………………………………………………………………………………………………………………………………………………………

A lot hours later I was going back and forth, capturing, trying to convert and receiving those same errors for missing events. At that point I decided to write to Robert and ask him if there is some guidance or article, or something that I can use in order to make that work. He replied to me that there is a .sql script that will create the xEvent session with everything needed in order to make that work as part of the samples in the download. Ooooooh… okay! Let’s go there:

xEvent_to_trc_session_template

After entering the path where the .xel file should be, I again captured my workload and tried to convert. This time something different happened when I ran my readtrace command:

readtrace -I"C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\XECapture_0_130781709497210000.xel" -M"C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\DReplayTRC.trc"

The Reporter.exe opened up with analysis of the workload, but more importantly – there was no DReplayTRC.trc in the destination folder I selected?Reporter
Went back and forth, back and forth again with no success(again – there is no documentation on this – nothing anywhere). I again wrote to Robert and he said that the -M parameter does not accept output(and he was surprised that it was not throwing me an error), so I needed to do -M -o”the path to the file”. This means that if I run the below, it should work:

readtrace -I"C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\XECapture_0_130781709497210000.xel" -M -o"C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\DReplayTRC.trc"

And it did. Partially… The problem this time was that the -o parameter in this case was accepting DreplayTRC.trc as a folder and placing all the generated files there + there was a .trc file for each session from the workload and I did not want that. Again to the command line, this time using readtrace /?
ReadTrace_Help
Seeing the -MS parameter I was now optimist that I will finally be able to convert to a single trace file. Let’s see (+changed the directory output folder):

readtrace -I"C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\XECapture_0_130781709497210000.xel" -MS -o"C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\DReplayTRC"

xEvent_to_trc_Session_output
Finally! We have managed to convert a .xel file containing our application workload to a SQL Profiler trace .trc file. Now that .trc file can be given to the Distributed Replay Controller to prepare it for replay…

I don’t know how you feel about this process, but I kinda feel that there should be a better way for doing all of these things in the future. If Distributed Replay is “the technology that we are going to be using for replaying mission critical workload in the future” as Microsoft said in 2012, then let’s hope that first DReplay will get a GUI(because you remember how many people were using Extended Events in SQL Server 2008R2, don’t you?) and second why not DReplay “accepting” .xel files in first place?

Thanks for hosting this one, Jes! Hoping you will be able to count to more than 100 at the end of this Tuesday! 🙂

Oh, by the way, if you want to stop the Reporter.exe to show up at the end of each export, just put -T18 at the end of the cmd command above. It should look like this.

readtrace -I"C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\XECapture_0_130781709497210000.xel" -MS -o"C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\DReplayTRC" -T18

Don’t ask. 🙂

Filed Under: SQL Server Tagged With: Distributed Replay, Extended Events, RML Utilities, SQL Profiler, T-SQL Tuesday

Yet Again I was on RunAs Radio

April 22, 2015 By Boris Hristov Leave a Comment

RunAsRadio

More than one year ago I joined Richard Campbell(b|t) for my first RunAs Radio podcast ever. It was a ton of fun and we talked about one of my favourite topics – Distributed Replay. You can listen to that show here. Now, more than 12 month laster I joined Richard for another podcast and once again we talked about a favourite topic of mine – Policy-Based Management. A topic that I love so much, I recorded a course on. I am amazed that still a ton of DBAs nowadays don’t know about this feature and not to mention they haven’t heard how it can be combined with CMS and the EPM Framework because that’s one of the biggest time saver a DBA can have and use. So if you are a podcast fan or just want to listen to and learn more about all of those things that I mentioned above, go to the website, download the show and enjoy! It’s free at the end of the day! 🙂

+ 1 more thing – the first 2 people who listen to the podcast and ping me on twitter with a sincere interest for learning more about the subject(I will decide what sincere means :)) will get a Pluralsight voucher for one month allowing them to watch the almost 6-hour course I recorded on the subject + every other course on the website! So are you ready to learn how to make your life easier with Policy-Based Management?

Filed Under: Public Speaking, SQL Server Tagged With: CMS, Distributed Replay, EPM Framework, Podcast, Policy-Based Management

Speaking for the PASS DBA Virtual Chapter

February 19, 2014 By Boris Hristov 6 Comments

PASS_VC_DataAdministration

On 26th of February I will be speaking for the PASS DBA Virtual Chapter! I will present on the topic of Distributed Replay in SQL Server 2012 and how it can help you do performance tuning, application compatibility testing and forecasting. So if you are interested in any of those three above or you feel like they are important for you and your job, register for the webinar and I hope to see you online!

Update: Below are the recording and the questions that I did not have time to cover during the webinar. I hope that they will be helpful to you: [Read more…]

Filed Under: Public Speaking, SQL Server Tagged With: Distributed Replay, PASS, RML Utilities, SQL Profiler, Webinar

Speaking at SQLSaturday #275, Copenhagen

February 13, 2014 By Boris Hristov 4 Comments

I will be speaking in Copenhagen on 29th of March! This one is a huge one for me because I will again have the chance to be among rock star professionals and SQL Server experts! Unfortunately, because of this engagement, I declined to speak on the Global Azure Bootcamp, but I promised to do it next year! My session in Denmark will be on Distributed Replay in SQL Server 2012(and above) and I can’t wait already! See you there, #sqlfamily (watch out for me – I will be wearing a T-Shirt with that hashtag!).

Update: The event was just fantastic! Rock star speakers, fantastic sessions, beautiful venue, amazing sponsors and much, much more! Thank you, Regis (t|b), for inviting me! It was a huge pleasure to be part of the speaker’s crew! Below is the slide deck of my presentation + here you can find all the materials that I used together with the scripts and last, but not at least here are the photos from the event (but not only)!

Replay your workload as it is your actual one! from Boris Hristov

Filed Under: Public Speaking, SQL Server Tagged With: Conferences, Distributed Replay, RML Utilities, SQLSaturday

Speaking at SQLSaturday #280, Vienna

February 8, 2014 By Boris Hristov 2 Comments

On 6th of March I will be speaking in Vienna! This time SQLSaturday is actually on Thursday, but that only makes it even more special! I will be delivering 2 sessions – one on Distributed Replay and one on SQL Server Concurrency with almost 20 more rock star speakers (just check out the agenda!). Already looking forward to the conference and visiting Vienna again! Let this SQLSaturday be a great SQL Thursday! See you there!

Update: The event went great and here you can find some of the photos that I took, my slide deck + the scripts from my 2 sessions. Thanks so much to all attendees and be absolutely sure that rating me as the best speaker of the event means the world to me! Thank you so much again and see you soon!

Replay your workload as it is your actual one! from Boris Hristov

The nightmare of locking, blocking and isolation levels! from Boris Hristov

Filed Under: Public Speaking, SQL Server Tagged With: Blocking, Conferences, Deadlocks, Distributed Replay, Isolation Levels, Locking, RML Utilities, SQL Profiler, SQLSaturday

  • 1
  • 2
  • 3
  • Next Page »