0

in SQL server, i would like to keep track of all queries and their count , based on specific time frame and keep thsir historiacl record.

like every hour , which query ran for how many times. We have a database server which gets too many issues and we are able to see that number of querie is at certain timeframe increased but we cannot identify which are Extra queries running or which queries ran more often or their count is more than normal, which cause the issue. Is there any way to keep track of query count per hour, per 30 min, per 6 hour or per day ? as i tried monitoring tools like datadog, redgate, and other tools, they dont have this functionality

Improve this question
2
  • 1
    Did you've tried ExtendedEvents? It should be tested thoroughly to prevent capturing unnecessary information slowing down your system and it will not do the work for you estimating the query count, but you can capture the load.
    – Mainecoon21
    Commented Sep 19, 2023 at 11:10
  • @Mainecoon21 it looks quite complex, i am not even able to get simple queries to list out of these events,
    – Farhan
    Commented Sep 21, 2023 at 9:30

2 Answers 2

Reset to default
2

I would suggest to activate the query store:

https://learn.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver16

Improve this answer
1
  • we have millions of queries and it eats up too much cpu, disk and lowers performance. just in few hours more than 10GB was used
    – Farhan
    Commented Sep 27, 2023 at 12:57
1

FWIW, just because a query runs a lot of times doesn't mean it's a problem or is what's causing your database slowness issues necessarily.

Aside from the suggestions already mentioned with using Extended Events or Query Store (which are good suggestions), a third option that I find the most user-friendly is the Profiler. It's heavier than the other options, when unfiltered, so should be ran attentively.

But what I think a better way to tackle your problems of figuring out what your server's issues are is to utilize the tools sp_WhoIsActive and the First Responder Kit.

With sp_WhoIsActive you can run that ad-hocly when the server is running slow to immediately see what queries are running, who is running them, how long they've been running for, what they are waiting on, and if they're being blocked (in addition to other information). You can even obtain the live query plan of those slow queries and determine what part of the plan they're stuck on. This will immediately point you to issues in the moment when they're occurring.

The First Responder Kit has a series of tools, specifically sp_Blitz and sp_BlitzCache, which are helpful for retroactively finding the biggest issues with your server. So they can be ran at any point, even if you missed acting in the time of slowness.

Improve this answer
2
  • sorry to say, all these tools give reltime data, i want to get insight into historical data to analyze it later. query store looks better but its extremey resource intensive as we have millions of queries running every hour.
    – Farhan
    Commented Sep 19, 2023 at 17:32
  • @Farhan Not true, sp_BlitzCache gives you information about past runs and the current state of the cached plans for the previously ran queries. Query Store is a great option and highly recommended to turn on by default but yea definitely test in a separate environment before enabling it in PROD. Extended Events and the Profiler also give you historical information as long as you already had them running. Even sp_WhoIsActive can be scheduled with a SQL Agent Job to log to a table as well, but that's a bit low tech of a solution. Finding your issues will certainly be easier with these.
    – J.D.
    Commented Sep 19, 2023 at 18:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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