how to find out if new query has been running against the database

then how can i pin point or stored procedures or queries that are newly introduced as a part of release. I need some kind of auditing or alerting mechanism that will tell me in advance that this new query is performing badly on the server.

If you want to know in advance whether new code is having a performance issue, you need a Q&A environment where you can test your code.

For SQL 2014 and earlier, it's not realistic to be proactive in my opinion. That is, running traces or extended-events session just in case, can themselves bog the server unnecessarily. Once you find that you have an issue, you can start monitoring.

However, Eric Mila has an excellent suggestion in using sys.dm_exec_query_stats. This data is always collected, so it is always there. So if you have a performance issue, it can be a good place to start, and you may find things that stand out. Maybe you could even construct a baseline from it, but since not everything is in the plan cache, it is not waterproof.

Going back to trace and extended events, I typically filter for events that have a duration of 50 ms or more. (Keep in mind that by default the filter is in microseconds.) Then I aggregate reoccurring queries. This can be quite challenging with applications that inline parameters. When looking at the aggregation, I sort both by Duration, CPU, and Reads to find culprits.

With Query Store, Query Store does part of the job for you - it stores the data per execution plan per hour. But the same query can have multiple plans over the day. Query Store exposes six views which you can query to get the data. But you can also use the reports in SSMS. They are quite powerful with many options. Myself, I prefer to run queries, but when I demo Query Store to clients I show them the reports, and they quickly get excited.

The learning curve with Query Store is leaner than with Trace/X-Events, and once you are on SQL 2016+, you will not find much use for Trace/X-Events for this purpose. So it is questionable whether it is worth to get started with them. sys.dm_exec_query_stats may have to suffice while you still are on SQL 2014.

Yufei gave some links for Query Store. Here is a link to a series of posts by my fellow MVP Enrico van der Laar on Simple Talk that gives more details: http://www.simple-talk.com/sql/database-administration/the-sql-server-2016-query-store-overview-and-architecture/.

And what's wrong with Query Store? The only good reason, as I see it, to not use Query Store is that you are on SQL 2014 or earlier. (And I'm tempted to say that is only a half-good version. You should really upgrade-)

Alternatives are Extended Events or Trace, but both them come with more overhead than Query Store. I would not have them running constantly as a matter of routine, but only if there is a real performance issue. Which means that the issue first needs to be identified. With Query Store, the information is right there.

Hi Heisenberg,

If you have any problem with slows queries, you can just run this code :

select top 1000
    creation_time,
    total_worker_time / execution_count as [Avg CPU time],
    st.text

from
    sys.dm_exec_query_stats as qs
    cross apply sys.dm_exec_sql_text(qs.sql_handle) as st

order by
    creation_time desc
    ;

You can see the 1000 last Queries with their execution time. When you catch in the list a slow query, you copy the SQL code, and work in SQL Manager to optimize it. So patch your application to resolve this problem with this new query that is faster.

Best Regards

Thank you for the answer both. @Erland Sommarskog we are still on 2014 and planning to upgrade soon. however i need to find alternative to that for now.

I think im not able to explain my problem clearly. So assuming there was a new stored procedure introduced in the latest database release in my environment if for some reason this stored procedure started to eat up large chunk of cpu on my db server, then how can i pin point or stored procedures or queries that are newly introduced as a part of release. I need some kind of auditing or alerting mechanism that will tell me in advance that this new query is performing badly on the server.

@Erland Sommarskog can you point me to a link that will explain me, how can i achieve above scenario using query store or extended events as well. Thank you