SQL Query Performance Audit of a Reporting Workload
Find the queries that cost most and the common causes.
The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.
[VERIFY] is an editorial flag, not a blank to fill. Wherever the prompt shows [VERIFY], check that item against a reliable source before you use it.
Audit query performance. Data: top queries by total time with execution count, average time, rows read and rows returned, from the database's monitoring view: [QUERY_STATS]. Database system and workload times: [DATABASE_AND_WORKLOAD]. Present a ranking table with query, executions, average time, total time, share of the total, and rows read versus returned. Total time is count times average: a query run 500 times at 4 seconds uses 2,000 seconds, 40 percent of 5,000 seconds. Prioritise slow and frequent queries and treat rarely run ones separately. For the top five look for functions on indexed columns in filters, SELECT * on wide tables, missing join conditions, repeated subqueries, unneeded DISTINCT, ORDER BY on large results, and reading far more rows than returned. Worked example: 1,200 runs a day at 3.2 seconds is 3,840 seconds, or 64 minutes; filtering on an indexed date column instead of applying a function to it, tested on a copy, reduces the average to 0.4 seconds and saves about 56 minutes a day. Offer the first change to test for each query and the measure of success (same results with at least half the time). Test on a copy with the same parameters, three runs, plan and time recorded; verify results are identical and review the effect on writes of any new index [VERIFY]. Statistics from a short period may mislead. Recommend a monthly review and a rule for testing before deployment. Mark system view names [VERIFY].
Fill in before use
Optional: type your details and the prompt above updates. Nothing is saved or sent.
Replace each bracketed placeholder with your own details, then paste into Any AI or another assistant.