SQL Server overall performace monitoring history

Question

Hi We have SQL server 2008, 2014 and 2016 servers and looking to have performance monitoring method which I can pull information for past history. I understand there are reports available " Slandered reports" from SSMS. but Those are live details. Is there any way to get it history details( with give time/date) when we needed . Can we create baseline performance and compare . IF there is no native way , do we have to buy redgate,spotlight any software to monitor.? Can any one advice please

thank you

Answer 1

Hi

exec script:

select
q.[text],
SUBSTRING(q.text, (highest_cpu_queries.statement_start_offset/2)+1,
((CASE highest_cpu_queries.statement_end_offset
WHEN -1 THEN DATALENGTH(q.text)
ELSE highest_cpu_queries.statement_end_offset
END - highest_cpu_queries.statement_start_offset)/2) + 1) AS statement_text,

highest_cpu_queries.total_worker_time,  
highest_cpu_queries.total_logical_reads,  

highest_cpu_queries.last_execution_time,
highest_cpu_queries.execution_count,
q.dbid,
q.objectid,
q.number,
q.encrypted,
highest_cpu_queries.plan_handle
from
(select top 50
qs.last_execution_time,
qs.execution_count,
qs.plan_handle,
qs.total_worker_time,
qs.statement_start_offset,
qs.statement_end_offset,
qs.total_logical_reads
from
sys.dm_exec_query_stats qs
order by qs.total_worker_time desc) as highest_cpu_queries
cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_logical_reads desc;

Best Regards.

Please click the Mark as answer button and vote as helpful if this reply solves your problem.

Answer 2

get it history details

Only if you already implemented a performance monitoring, otherwise it is not possible to get the information's for the history.

Answer 3

Unless you have saved the information somewhere either using query or using monitoring tools, such information wont be available as SQL Server does not keeps that stored for you for long time.

Answer 4

There is nothing built in which captures past performance. You may get some data from Windows Performance Counters. This is really a use for "server" performance monitoring.

I suggest you read this:

https://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/

Answer 5

As Olaf says, you need to set up performance monitoring. It is not automatically there for you.

One option is to get a third-party tool. Red Gate has SQL Monitor. SentryOne, now owned by SolarWinds, have theirs. And I think Quest has something as well.

SQL Server comes with the Management Data Warehouse - but don't activate it. I have not heard much good about it. But it about impossible to uninstall once you have activated it.

However, start with SQL 2016 there is a true gem: Query Store! Query Store collects information about all query executions and stores them in an aggregated fashion by intervals of one hour (the default setting). If you have Query Store active, and you find that performance degrades, you can use Query Store to see if there any plans that have regressed, and you can then quickly force a good plan to solve an urgent issue.

I should add that Query Store is not a full-stack performance monitoring tool, as it only works with queries.. But it is a great asset.