SQL Server Performance Tuning
The only SQL Performance tuning guide you will need.
What is SQL Server Performance Tuning?
SQL Server Performance Tuning is a process when a slow performing T-SQL is modified and as a result becomes faster.
Examples of SQL Server Slowness:
- You have an app, and the app takes too long to respond.
- You have a SQL Server, and when you look at the Performance Monitor, it shows CPU at 100%.
- SQL Agent jobs can not finish in time or start spilling over into production or high usage windows.
- Reports take too long to process. Or never finish.
- T-SQL runs fast with one set of parameters, but with others, it is slow or never completes.
- Many more…
Most often, what we hear is “this was fast (or OK) before, and now it’s crazy slow”.
Slow SQL Shows Up In a Few Ways:
- T-SQL was fast before, and gradually it became slower.
- T-SQL was always slow (or never completes).
- T-SQL was fine before, but after a change (often seemingly unrelated), SQL response time is too slow.
Reasons Why SQL Server Becomes Slow:
Here are a few reasons we see most often in the field:
- Bad Queries. Either old queries become slow, or new ones use up all of the SQL Server juice available, leaving other queries starved for resources.
- Queries to complex. Good example is query that goes on for 5 pages long with 30 joins. There are easier ways to do this.
- Running reporting queries on production SQL Server. Taking SSRS, SSIS, data warehousing and placing on the same server makes sense at the start. However, later those SSRS reports really start to compete with actual production queries. So now we have Dan in finance running report looking 5 years back and taking all SQL Server capacity away from John who is trying to buy something on your ecommerce store. Not good.
- Queries written by code. This works. 95% of the time. And that’s the problem. Because times when it becomes slow, it hurts.
- Bad Indexing. Lack of indexes, too many indexes, indexes on wrong columns, duplicate indexes, almost duplicate indexes, indexes that can be merged, missing index warnings, indexes with a higher cost to maintain then they help.
- Faulty Architecture and Bad DB design. A good example is db that is set up with one data file. Important dbs which need to be fast can gain a lot from having multiple filegroups and files, spreading the load across multiple drives, and strategically selecting which tables and index goes to what file.
- Lack of Best SQL Server Configuration Practices. You need settings tweaked in multiple places Win OS, Group Policy, Active Directory, SQL Instance Settings, database settings, etc.
- Lack of good SQL Server Maintenance. This affects backups, index maintenance, statistics maintenance, etc. And yes, queries may start running 10-20% slower every week, and 6 months later you have everything crawling.
- RDP’ing to SQL Server. RDP sounds harmless. But imagine, someone RDPs, starts Visual Studio, and starts working on SSIS packages. That can easily chew away significant sizes of RAM and CPU. And these activities wont even show in any of your SQL Server monitoring, unless you monitoring Win OS performance metrics too.
- Size of Data. Data has grown over time, and now SQL is processing millions or billions of rows, instead of a couple of thousand, as it was initially designed.
- Code Deployment. After code deployment, things started to run slow.
- Change in Infrastructure. A good example would be storage, network or VM changes. This usually makes all queries become slower. Its hard to diagnose, because if you ask “has anything on SQL Server changed?” the answer is “No”.
- Failing Hardware. Certain types of hardware failures, such as storage or networking, will not break the SQL Server, but it will make server re-try the same operation a couple of times slowing things down.
- And Many More…
How SQL Slowness Shows Up
SQL SQL happens in two ways:
- Easy to catch slowness. You know the app screen used to take half a second, and now it’s been 2 minutes and is still spinning and hasn’t finished.
- Tough to catch slowness. When query used to take 4 min 37 seconds, and now it is taking 6 min 11 seconds. To catch something like that, you have to have good SQL Server Monitoring in place.
What To Do When SQL Server Is Slow
Don’t restart the SQL Server, or Windows OS just yet. Collect some data first. Or you won’t know why slowness happened. Or you will suspect qry1, but it will be qry7 that was the culprit. Which will pretty much guarantee the slowness issue will happen again.
You want to narrow down why SQL is running slow. Couple questions you want to answer:
- Is there any T-SQL being blocked right now?
- Is there T-SQL that has been running for a long time or consumed lots of resources?
- Are there transactions that are open and never closed?
- Is one query slow, or is everything slow (possibly a system-wide change in play)?
Depending on what the answers are, you want to narrow down to an exact T-SQL call, ideally with actual parameters that were used.
Mistakes People Make With SQL Server Performance Tuning:
- Reboot too fast. This is one of the worst things you can do because of the multiple issues this creates. a) you just wiped out all of the SQL data collection (DMV) tables clean which contained lots of goodies which if examined properly would have shown where problem is. b) you did not look into what else was running at the same time. Unless you have a SQL Monitoring tool or your own custom data collection scripts that grab necessary performance data – a reboot will wipe out performance stats.
- The reboot is too slow. During production times, if you know a reboot will “fix” the issue, it may be better to reboot and have a few minutes of an outage, rather than trying to troubleshoot for 30 minutes and then have to reboot anyway. It may be better to reboot. Not always. But sometimes. Then spend some time preparing what you will do next time when this issue hits again. Prepare some scripts you will run. Write some TSQL to capture the details of what’s happening. Maybe even automate some of this.
- Not narrowing down what is slow. The more specifics you have about what exactly is slow, the easier it is to troubleshoot.
Not knowing the exact parameters used when slow running query or stored procedure ran.
- Poor prep for the next slowness. Slowness should not surprise you. You need to know the steps to take before it happens. Have plan and scripts ready to go.
When It Is Impossible To Tune SQL Server (Or Crippled SQL Server Tuning):
- You lack visibility into the issue. For example, you are not a system admin on the server. This will limit what you can look at.
- You can’t run some tools. You are simply unaware of all tiny and obscure tools that can help. Never had to use any.
- Old SQL Server version. The older the SQL Server version, the less options you have to identify and tune performance.
- The older the Win OS, the harder it will be.
- Not being able to talk to people responsible for every layer (AWS/Azure cloud admins, DevOps, Windows sysadmins, storage admins, VMWARE admins, networking admins, etc.)
- Not having any tools or scripts ready to go. You googling as you seeing the problem.
- Taking too long. Any problem can be solved — eventually. The question is do you have the time it may take to tune, or is it better to pull in someone who does only perf tuning?
- Not allowed to make changes.
- No place to test in.
- Making performance tuning someone’s problem with zero experience.
How To Be Ready to tune SQL Server Performance like a PRO
- Prepare the exact steps you will do next time slowness happens. Research DMVs you will check. Spend time now to educate yourself about what to do when this happens. Fair warning: this is not an easy thing to learn.
- Dedicate someone. Bring in a DBA, database dev or programmer. Just know it will take time. Especially if this person doesn’t deal with performance problems every day.
- Know what’s normal. What is your baseline. Does this stored procedure typically run in 4 seconds or .4?
- Hire a pro. Hire a company like Red9. We’d love to help! We do perf tuning every day and have done it for over a decade now. Take a look at our AWESOME testimonials and case studies.
Typical SQL Performance Tuning Results
Sometimes changes can make TSQL run 20,000% faster, sometimes 50%, other times slicing 20% off the cost is a great help. Tuning results vary. They vary based on the complexity of the query, how much tuning has gone into it already, etc. As long as there will be multiple ways to write the same query – it means it will be always way to make it go faster.
Performance tuning cases (Real-world!)
Here are a few SQL performance tuning reports:
- Before vs. After Performance Tuning Report #1 – 16x faster!
- Performance Tuning Report #2 – 67,871% gain
- SQL Server Performance Tuning Report #3 – 877% improvement
- Before vs. After Performance Tuning Report #4 – 1,152x faster!
- Performance Tuning – Report #5 – 75,336% improvement!
- SQL Server Index Tuning – 41,925% improvement
- SQL Performance Tuning Report #7 – 7,039% improvement
- Performance Tuning Report #8 – 9320x faster!
- Before vs. After Performance Tuning Report #9 – 1,600% gain
- Performance Tuning Report #10 – 340% CPU gain
- Performance Tuning Report #11 – 7,675x faster!
We have been Performance Tuning SQL Servers for 15+ years now. And we would love to help! Contact us here.
Ready To Get Started?
Call Us Now
Or, Let Us Know How We Can Help
Tight On Time?Schedule A Call:
The deep level of knowledge of MSSQL is incredible and the combined experience and expertise has been a huge asset. Additionally, the flexibility and professionalism throughout a difficult migration was highly appreciated.
With little notice Red9 was able to find and fix a number of different SQL misconfigurations and wasteful practices as our customer moved their SQL environment from a rack solution with Fiber SAN to the Azure cloud.
Red9’s cool confidence and eagerness to deliver results are surpassed only by their deep penetrating knowledge of SQL. Whether it's SQL design, development, optimization, or recovery, Red9 is a SQL Gem.
They have mastered the understanding of complex, heavy load TSQL transactions. If you would like to sleep better at night and know your database will be running well in the morning, then I would recommend finding a DBA company with the kind of skill set Red9 has proven time and time again.
"In just a few minutes, Red9 was able to find significant performance improvement opportunities in our database. These improvements were in areas we hadn’t even considered. We plan to continue to work with Red9 on a regular basis and continue to enjoy the benefits of their knowledge."
"After SQL Database query tuning – Our site is flying now. I have already have hired Red9 three times. I am very happy I found them!"
"Beyond deep technical expertise, communication is very important to us. Red9 was timely in responses, met set expectations and timelines, and kept us in the loop when an unexpected issue came up. I would absolutely recommend Red9."