-2

How can I capture the query or performance metrics over a period of time, say a month, so that later they can be analysed for performance? I'd like to use DMVs or extended events.

I am trying to achieve something similar to query store but since we are on SQL Server 2012 and 2014, I can't leverage that feature.

We found open Query store and sp_blitzcache to be some of options available but due to restriction for any third party scripts, it's a blocker for us now.

I tried to find this info under system health events but seems it does not collect this info.

Please suggest.

closed as too broad by mustaccio, hot2use, LowlyDBA, Gaius, Andriy M Mar 8 at 14:04

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    If you can't use third party scripts, then you'll have to write your own which won't be as good, or pay for a third party monitoring solution. Why no love for open source third party scripts? Microsoft and plenty of consultants have used them :) – scsimon Mar 5 at 20:05
  • 5
    Yeah, if you can't write your own and you also can't use a 3rd party, you're kind of stuck. – Aaron Bertrand Mar 5 at 20:31
  • @scsimon, its not about no love for open source i've used brent ozar's scripts and others like dba tools in my previous organization. Its just that the domain i am working currently is very restricted and to get any third party scripts goes through lot of scanning and will fail the eng test before one can use. Its just the company standards and i don't mind using and implementing them unless green for a go. So my question was here more like how to get started when i dont have an option of 3rd party here. I tried using extended events but they just made the server slow for actions and events – BeginnerDBA Mar 5 at 21:57
  • I feel you. If you can write you OWN custom scripts then I’d think your client wouldn’t be the wiser that you used an open source script. If they are, and they are ok with your custom scripts but not those of Adam Mechanic, Brent Ozar, @AaronBertrand, Ola, and other MVPs and SQL gurus, then I’d be flattered, no offense. I don’t mean to lose focus on your question but as Aaron pointed out, you’d just have to reinvent the wheel or purchase some monitoring software IMHO. I’d have a serious talk with them and try to convince them. Set up a call with Brent or other consultants :) – scsimon Mar 6 at 2:04
1

You cannot monitor an instance without there being an impact. There will always be some overhead. The more detailed, and hence more useful, the information collected is the more overhead there will be. The question is - is the pain of collecting this data less than the value from having it.

Decide what you're most interested in. Is it long-running queries, index access, memory spills .. or something else. Identify the minimum amount of information you would need to take action should you detect this specific problem. Collect that information for a short period of time and turn off the collection routines. If performance was acceptable run the routines for longer, perhaps during known busy periods. Address any problems found and repeat.

Once you're comfortable running this small routine continuously you can incrementally add further data points to it.

The monitoring routines, database and analysis are best run on a server separate to the production box. The only additional load there is the queries / scripts to collect the specific data points.

0

You have to write your own stuff. I did this at a previous organization I worked at. DBA's had our own SQL server and I scrounged up 5TB of storage for it over the years and set up a process to audit most connections and queries.

Anytime we had a problem I was able to provide an answer 90% of the time. A few times I screwed up and blocked critical apps. You win some, you lose some.

Not sure if I used this query to collect data, but it's a start for what you need.

select runtime = getdate(), server_name = @@servername, a.session_id as 
exec_session_id, b.session_id as sessions_session_id, c.session_id as conn_session_id, 
a.transaction_id, c.most_recent_session_id, a.blocking_session_id,
a.connection_id as exec_conn_id, c.connection_id as conn_connection_id, c.connect_time, 
c.net_transport, c.protocol_type, c.protocol_version, c.net_packet_size,
a.database_id,  b.login_time, b.last_successful_logon, b.last_unsuccessful_logon, 
b.host_name, b.program_name, b.client_interface_name, c.client_net_address, 
c.client_tcp_port,
b.login_name, c.local_tcp_port, c.local_net_address,
a.start_time, b.last_request_start_time, b.last_request_end_time, a.status, a.command, 
a.blocking_session_id, a.wait_type, a.wait_time, a.last_wait_type,
a.cpu_time, a.reads as reads_exec, b.reads as reads_session, a.writes as writes_exec, 
b.writes as writes_session, a.logical_reads as logical_reads_exec, 
b.logical_reads as logical_reads_session, c.last_read, c.last_write, a.command, 
a.percent_complete, a.row_count, st.text
from sys.dm_exec_requests a
inner join sys.dm_exec_sessions b on a.session_id = b.session_id
inner join sys.dm_exec_connections c on c.session_id = b.session_id
cross apply sys.dm_exec_sql_text (c.most_recent_sql_handle) as st
where a.session_id is not null

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