[Guide] SQL Server Performance Tuning and Optimization

Slow SQL performance surfaces in a few ways:

1. T-SQL call was fast before, and gradually it became slower.
2. TSQL was always slow (or never completed because of performance).
3. 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:

1. 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.
2. Queries to complex. Good example is query that goes on for 5 pages long with 30 joins. There are easier ways to do this.
3. 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.
4. Queries written by code. This works. 95% of the time. And that’s the problem. Because times when it becomes slow, it hurts.
5. 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.
6. 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.
7. 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.
8. 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.
9. 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. 
10. 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.
11. Code deployment. After code deployment, things started to run slow.
12. 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”.
13. 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.
14. And many more…

Types of slow SQL:

1. 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.
2. 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:

1. Is there any T-SQL being blocked right now?
2. Is there T-SQL that has been running for a long time or consumed lots of resources?
3. Are there transactions that are open and never closed?
4. 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:

1. 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.
2. 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.
3. Not narrowing down what is slow. The more specifics you have about what exactly is slow, the easier it is to troubleshoot.
4. Not knowing the exact parameters used when slow running query or stored procedure ran.
5. 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:

1. 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.
2. You can’t run some tools. You are simply unaware of all tiny and obscure tools that can help. Never had to use any.
3. Old SQL Server version. The older the SQL Server version, the less options you have to identify and tune performance.
4. The older the Win OS, the harder it will be.
5. 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.)
6. Not having any tools or scripts ready to go. You googling as you seeing the problem.
7. 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?
8. Not allowed to make changes.
9. No place to test in.
10. Making performance tuning someone’s problem with zero experience.

Prepare like a PRO for the next SQL slowness

1. 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.
2. 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.
3. Know what’s normal. What is your baseline. Does this stored procedure typically run in 4 seconds or .4?
   
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