January 11, 2021 at 4:54 am
Hi,
What is the best way to do real time logging of SQL server in production environment without affecting the performance of the same. Logging of events like cause of Time Out errors or state of the sql server after the command to exec a stp was called and before that stp was started to execute...or any other events which leads to show stopper.
Something just like WIRE SHARK
In one such incident we couldn't get any information from SQL event logs or windows event logs and we cannot run profiler as they are heavy.
Hence request to provide help for the same as the production environment is very critical.
Thanks
Saumik Vora
January 11, 2021 at 12:43 pm
The best way to get detailed information about the behavior of SQL Server is through the use of Extended Events. They are not zero cost, but they are the lowest cost you're going to see. No other alternatives are as safe. Trace/Profiler is much more expensive.
You can see some behaviors in aggregate by looking at the Dynamic Management Views (DMV). Access that data is just shy of zero cost. However, it won't have a lot of information on timeouts and stuff like that. There, again, Extended Events is better.
One other way to capture behaviors is through the Query Store. The cost is low, but not zero. However, the data again is aggregated and won't include timeouts.
"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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply