Query for max CPU, Memory and System Queue length

Question

I am looking for a query to find maximum CPU, Memory and System Queue length for last month. Any help would be greatly appreciated.

Answer 1

SQL Server doesn't store granular history going back that far. You have a couple of options:

Option 1: Look at what's happening now. Run sp_BlitzFirst, one of the scripts in the open source First Responder Kit. It takes a 5-second sample of what's happening on the server right now, and gives you those metrics. Try experimenting with @ExpertMode = 1 to see even more details.

Option B: start collecting it yourself. Set up sp_BlitzFirst to run in an Agent job every 15 minutes, logging the metrics to tables, and then use the Power BI Dashboard for DBAs to visualize it. Here are instructions.

Disclaimer: I'm one of the authors of those open source tools.