[Guide] SQL Server Performance Tuning and Optimization
The only SQL tuning guide you will ever need.
Home > SQL Server Performance Tuning
What is SQL Server Performance Tuning?
Its a process of taking existing T-SQL query, modifying it which makes it run 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 from clients is “This was fast (or OK) before, and now it’s too slow”.
Slow SQL performance surfaces in a few ways:
- T-SQL call was fast before, and gradually it became slower.
- TSQL was always slow (or never completed because of performance).
- T-SQL was has acceptable performance run times, but after a “change” (often seemingly unrelated), SQL response time is now too slow (doesn’t meet your expectations).
What cause SQL Server to become slow:
Here are a few reasons of SQL slow performance 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.
- Indexing issues. 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…
Types of slow SQL:
- Easy to catch slowness or fast degradation. Example: The function used to take half a second, and now it’s been 2 minutes and function still hasn’t finished running.
- Tough to catch slowness or slow degradation over time. Example: Query used to take 4 min 37 seconds to run, and now run time seems to go up several minutes each week.
Steps to take 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. You will suspect a wrong query as the culprit. Which will 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 when tuning SQL Server performance:
- 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.
- Not prepared for the performance problems in future. Slowness should not surprise you. You need to know the steps to take before it happens. Have plan and scripts ready to go.
SQL Performance tuning requirements:
- 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.
Prepare like a PRO for the next SQL slowness
- 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.S
How we handle SQL Server performance tuning and few reports to show the results:
Below are a few performance tuning reports, so you can get a sense of what’s possible. Most explain what exactly was changed, so you can learn from this too.
- Performance Tuning Report 1 – 24,000 times faster!
- Performance Tuning Report 2 – 8,992 times faster!
- Performance Tuning Report 3 – 2,307 times faster!
- Performance Tuning Report 4 – 753 times faster!
- Performance Tuning Report 5 – 679 times faster!
- Performance Tuning Report 6 – 589 times faster!
- Performance Tuning Report 7 – 562 times faster!
- Performance Tuning Report 8 – 354 times faster!
- Performance Tuning Report 9 – 175 times faster!
- Performance Tuning Report 10 – 103 times faster!
- Performance Tuning Report 11 – 31,084 times faster!
- Performance Tuning Report 12 – 7,675 times faster!
- Performance Tuning Report 13 – 1,152 times faster!
- Performance Tuning Report 14 – 419 times faster!
- Performance Tuning Report 15 – 57 times faster!
- Performance Tuning Report 16 – 70 times faster!
- Performance Tuning Report 17 – 40 times faster!
- Performance Tuning Report 18 – 31 times faster!
- Performance Tuning Report 19 – 16 times faster!
- Performance Tuning Report 20 – 16 times faster!
- Performance Tuning Report 21 – 13 times faster!
- Performance Tuning Report 22 – 8 times faster!
- Performance Tuning Report 23 – 5 times faster!
- Performance Tuning Report 24 – 915 times faster!
- Performance Tuning Report 25 – Detecting and resolving SQL Deadlocks
- Performance Tuning Report 26 – A distributed replay test on Azure managed instance
- Performance Tuning Report 27 – High CPU usage emergency
- Performance Tuning Report 28 – Slow storage affecting SQL Server performance
- Performance Tuning Report 29 – High Availability and Disaster Recovery
- Performance Tuning Report 30 – Stored procedure 354 times faster!
- Performance Tuning Report 31 – Preventing possible performance issues before they happen
- Performance Tuning Report 32 – Stored procedure 181 times faster!
- Performance Tuning Report 33 – Improving performance using indexes
- Performance Tuning Report 34 – Removing a bad SQL query hint
- Performance Tuning Report 35 – Tuning a slow report
- Performance Tuning Report 36 – Deadlocks
Let our expert SQL Server Consulting team handle the technical complexities, so you can focus on driving your business forward with confidence.
"*" indicates required fields
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."