September 24, 2024 at 10:05 am
I am looking for a SQL query optimization tool that can enhance my query execution times. I have already tried EverSQL and SQL Tuning, but they are not meeting my requirements for the following reasons:
1. Parameterized Queries: Both tools seem to struggle with parameterized queries and do not optimize them correctly.
2. WHERE Clause Optimization: While optimizing the queries, neither tool seems to handle optimization of conditions within the WHERE clause effectively.
My requirement is a tool that can:
Accept any type of SQL query, including parameterized queries.
Perform deep optimization, including optimizing conditions within the WHERE clause.
Focus on improving execution time.
Has anyone come across a tool that could meet these requirements or have any suggestions for how I could approach this problem?
September 24, 2024 at 6:24 pm
I've worked on SQL Server databases for 30 years now. I've written multiple books on the topic of query tuning. I want to lead in with that because the next thing is not going to make you happy.
There ain't one.
There are simply too many things that can go wrong. Sure, most of the time it's the code. But when you throw a 10,000 line script at one of these tools, they quickly lose their minds and are incapable of recognizing common code smells and bad patterns, let alone appropriately fixing them. Then, toss in problems with the data architecture, incorrect normalization, missing constraints, poor choices on clustered indexes, again, they can't manage it. Bad or missing statistics and improper statistics maintenance also leads to lots of issues that tools can't always ID. On top of that, throw in server and database settings, MAXDOP, Cost Threshold for Parallelism, others. And, all of this, the code, the servers, the structures, in combination have to be taken into account.
For good or for ill, really, the single best mechanism I know of for getting good performance out of databases is to throw money at the problem. Buy bigger, more and better hardware. Go to a higher service tier on your cloud. Just keep spending money.
Second best way, get the Mk I Eyeball polished up, start gathering metrics to identify poor performers, peruse the code for common bad practices (really, this is usually your biggest bang for the buck, people don't mess up code in one place, they mess it up in ALL the places, the same way, over and over), start fixing those. Then, execution plans to see where other problems lie. Fix those. Wash, rinse, repeat.
Sorry to be the bearer of bad tidings. There is no tool that does all this. Someone ought to build one, but as you can see, it's a challenge.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply