1

I want to capture all and everything running in my SQL Server instance. That include stored procedures executions, adhoc SQL query, batches etc. What events should I use in an Extended Event session? Right now I'm using sqlserver.rpc_completed and sqlserver.sql_batch_completed. Is this enough? I saw some people also use sqlserver.sp_statement_completed. Is this necessary?

I want to capture the SQLs so that I can replay on another server to mimic real database load.

4
  • By "all executed SQL" you mean only successful executions or the ones that generated errors too? It sounds as if you want to configure an audit on your server... Can you share the reason you want to capture everything?
    – Ronaldo
    Commented Jan 14, 2023 at 10:58
  • Do you need to capture dynamic SQL statements executed in proc code in addition to the call to the proc itself? That would be the reason to capture sqlserver.sp_statement_completed.
    – Dan Guzman
    Commented Jan 14, 2023 at 12:00
  • @DanGuzman That's reasonable. Thanks for the explanation. Commented Jan 14, 2023 at 14:08
  • @Ronaldo Successful and failed. I want to capture the SQLs so that I can replay then on another server to mimic real database load. Commented Jan 14, 2023 at 14:09

1 Answer 1

4

I want to capture the SQLs so that I can replay then on another server to mimic real database load

For replay you only want to capture what is sent from the client to the server, not each individual statement. So capture API Cursor operations, RPCs, and Batches. There are many tools that can help you with this, eg

Replay Markup Language Tools

or

Microsoft® Database Experimentation Assistant

or even humble SQL Server Profiler which can capture and replay traces too.

enter image description here

If you're generating a load test RML Utilites is the best of these, as it doesn't just replay traces, it uses the traces to generate "Replay Markup Language" files that you can run selectively or edit, and also supports replaying SQL scripts. Also there are tools specifically for load testing like HammerDB.

3
  • Thanks David for the answer. By "API Cursor operations", which specific events are you referring to? I'm not familiar with this. Commented Jan 14, 2023 at 17:13
  • Check the Profiler Trace template for reference. But some clients "prepare" commands and then execute them multiple times without sending the SQL each time. These are the API Cursors or "Client cursor" statements lke sp_prepare sp_execute, etc. Commented Jan 14, 2023 at 17:16
  • 1
    Thank you very much for your time! Commented Jan 14, 2023 at 17:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.