Developer ToolsFree worksheetPDF, Excel, Word export

SQL Query Performance Tuning Log Worksheet

Tuning work is easy to lose: nobody remembers which index was added or how much it helped. This worksheet logs each slow ERP query with before and after timings, the fix applied and how often it runs, then totals the improvement and the daily time saved.

Download:

Query or report	System	Fix applied	Before (ms) (ms)	After (ms) (ms)	Runs per day	Improvement	Elapsed hours saved per day	Tested on
						97.3%	3.455
						80.7%	0.8611
						72.4%	0.4583
						77.4%	0.0903
						45.9%	0.1867

Total elapsed hours saved per day

5.0514

Average improvement

74.7%

Elapsed hours saved per year (365 days)

1,843.76

Queries tuned

Record timings under comparable load and with warm and cold cache noted. Elapsed time saved is not the same as CPU time saved when queries run in parallel. For CPU, capture total_worker_time from sys.dm_exec_query_stats before and after.

Top queries review

Optional: we can look at your most expensive ERP queries read-only and send a ranked tuning list.

Optional. The tool and every export work without this. No spam.

How to use it

Add one row per query or report you tune. Record the elapsed time before and after under similar load, the number of runs per day, and the fix you applied. The sheet calculates the percent improvement and the elapsed hours saved per day. Use the dropdowns to keep fix categories consistent, then export to Excel to share with the team or attach to a change record. Review the log after upgrades to catch regressions.

Capture a before timing first, always
Use the same parameters for before and after
Log the date so regressions can be traced to releases

Choosing what to tune

Rank candidates by total cost, not by the slowest single run. A query that takes 7 seconds and runs 300 times a day costs more elapsed time than a 90 second report that runs once. Pull candidates from sys.dm_exec_query_stats ordered by total_elapsed_time or total_worker_time, from your ERP's own slow-query logs or from user complaints about a specific screen. Log the highest total cost first.

Reading the totals

Improvement percent shows the relative gain; elapsed hours saved per day shows the practical value. In the sample, the open sales order query dropped from 14 seconds to under half a second and runs 900 times a day, saving more than three hours of waiting. That is a stronger case than a large percentage on a rare report. The yearly figure helps justify DBA time.

Common mistakes

Comparing a cold-cache before with a warm-cache after exaggerates the gain. Changing parameters between tests does the same. Another trap is adding an index that speeds one query and slows writes on a busy table, so also note write impact. Keep a rollback script for every change.

Frequently Asked Questions

What counts as a good improvement?

It depends on frequency. A 90 percent gain on a query that runs thousands of times a day matters. A 50 percent gain on a monthly report may not be worth the risk. Use the elapsed hours saved per day to decide where effort pays.

How do I get reliable before and after timings?

Run each version several times with the same parameters on the same data and take the median. Note whether cache was warm. SET STATISTICS TIME ON or Query Store runtime statistics give consistent numbers on SQL Server.

Should I log CPU time as well?

Yes, if parallelism is involved. Elapsed time can fall while CPU time rises. You can add a column in Excel after export, or log total_worker_time from sys.dm_exec_query_stats for each query before and after.

Can I use this for non-SQL Server systems?

Yes. The sheet is database-neutral. Record timings from Oracle AWR, PostgreSQL pg_stat_statements, DB2 or your ERP's own tracing. The improvement and time-saved formulas are the same.

If you want help finding the most expensive queries in your ERP database, we can run a read-only analysis on your own servers.

Talk to an Expert Explore More Free Tools

Related Tools

Developer Tools

More Developer Tools tools

See all

Back to All Tools