Question :
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?
Answer :
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.