0

My DB performance management experiences is primarily with Oracle and I am looking for an equivalent set of tools in MSSQL. In particular something to easily spot historical SQL peaks and identify the culprit SQL. It seems most of the built-in tools (Activity monitor/DMV) offered by MS only caputure active issues, but cannot retrive and chart issues that occurred much longer than a few minutes in to the past.

For Oracle, we have Oracle Enterprise Manager which views the AWR repository and by default can see, 7 days worth of Activity including the exact Sessions/SQL statements that caused an issue and why.
Example screen shot: https://docs.oracle.com/cd/B28359_01/server.111/b28275/tdppt_sqlid.htm#TDPPT142

For MSSQL aka SQL Server, the only thing I have been able to find is a 3rd party utility from Solar Winds.
Example screen shot: http://logicalread.com/wp-content/uploads/2017/05/meritsSQLblogpost5.png

Does Microsoft have something native that offers this capability?

Improve this question
2
  • 1
    Depending on what you want to monitor. SQL has the "Query Store" that you can enable (depending on your SQL version). This feature will capture information about queries that were run on the instance. If you want to capture information about the "hardware" like CPu utilization for example, then an external monitoring tools will be a better option.
    – Dominique Boucher
    Jan 17, 2020 at 15:30
  • Thanks, I've read about query store which starts in 2016. learn.microsoft.com/en-us/sql/relational-databases/performance/… I see that it can leverage DMV, but I don't see anything there that gives you that chart of Top Activity over time.
    – DreadPirateRoberts
    Jan 17, 2020 at 17:08

2 Answers 2

Reset to default
1

I think what you are looking for is Query Store, but this is available only for SQL Server 2016 or higher.

For SQL Server 2014 or lower, you can set up an Extended Events (XEvents) session to capture the queries running longer than X amount of time. XEvents are really easy to use and quite intuitive, the only thing you should pay attention to is how much data you are capturing as it can have some impact on performance or fill up the disk quickly. The only draw back of XEvents is that it will not provide you with a chart.

Improve this answer
1
  • Good tip on the XEvents! You're right, it seems like Query Store is Microsoft's answer to Oracle's AWR.
    – DreadPirateRoberts
    Jan 21, 2020 at 17:14

Your Answer

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

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