SQL Performance Management without using SQL Profiler

Hi,

Would anyone have any tips or suggestions on monitoring server performance without using SQL Profiler? Using profiler takes up too much resources from prod database and causes front end to crash.

Thanks in advance

Run it on a remote system and capture the trace.
Capture only the required events and apply filters.

1 Like

Profiler is deprecated, and will be removed sometime (in the distant) future. The replacement is Extended Events, which in my experience has been a lot more flexible and capable. However, it takes some getting used to. Since you can fine-tune extended events to capture exactly what you need and nothing more, it would be lighter on resource usage as well.

Here is a quick start article. Search for articles by
Jonathan Kehayias. He has written extensively about extended events.

2 Likes

Use a third-party tool - such as SQL Sentry, DPA, etc. The expense of paying for the tool is easily outweighed by the gains you get from those tools.

Maybe you can benefit from query store:

Monitoring Performance By Using the Query Store - SQL Server | Microsoft Docs