SQL Server Performance Tuning

Reasons Why SQL Server Becomes Slow

​

There can be many reasons. Here are a few we see most often in the field:

1. Lack of good SQL Server Maintenance. This affects backups, index and statistics maintenance, corruption checks, etc.

2. Lack of Best Server Configuration Practices. You need settings tweaked in multiple places (yes, BIOS!), Win OS, Security, Group Policy, Active Directory, Instance Settings, database settings

3. Faulty Architecture. A good example is a database that is set up with one data file and that’s it. It should have multiple files, spreading the load to multiple drives.

4. 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 high cost, but do not provide any help.

5. Change in 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.

6. Bad Queries. Either old queries become slow, or new ones use up all of the SQL Server Power available, leaving other queries starved for resources.

7. Bad Database Design. A good example we see a lot is the database has a single data file. And many others.

8. Code Deployment. After code deployment, things started to run slow.

9. Change in Infrastructure. This one you may know about, or you may not. A good example would be storage or network changes. This usually makes all queries become slower.

10. Failing Hardware. Certain types of hardware failures, such as storage or networking, will not break the SQL Server, but it will make it re-try the same operation a couple of times.

11. And Many More…

​

​

​

How SQL Slowness Shows Up

​

Happens in two ways:

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

2. Tougher 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 some type of SQL Server Monitoring in place.

​

What To Do When SQL Server Is Slow

​

Don’t restart the server, or start pulling cords. You want to collect some data first. Or you won’t know why it happened. Which will pretty much guarantee the issue is going to come back.

​

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)?

​

Then 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

​

1. Reboot too fast. This is one of the worst things you can do, because of the multiple issues this creates. Number one, you just wiped out all of the SQL data collection (DMV) tables. Number two, you did not look into what else was also 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 those performance stats which can help identify what went wrong.

2. A 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 to try to troubleshoot for 30 minutes and then have to reboot anyway. It may be better to reboot. Then spend some time preparing what you will do next when this issue hits again. Prepare some scripts you will run. Write some jobs that will auto-capture some problematic details.

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 exact parameters used for a slow into a slow running query or stored procedure.

5. Not preparing for the next slowness problem. Slowness should never surprise you. You need to know the steps to take before it happens.

​

When It Is Impossible To Tune SQL Server (Or Crippled Tuning):

​

• You lack enough 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 don’t know any. Never used any.

• The older the SQL Server version, the harder it is to tune performance.

• The older the Win OS, the harder it will be.

• Not being able to talk to people responsible for every layer (Windows sys-admins, storage admins, networking admins, etc.)

• Not having any tools or scripts ready to go.

• 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 tuning?

• Not allowed to make changes.

• No place to test in.

• Making Performance tuning someone’s problem who has never done this before.

​

How To Be Ready For SQL Server Performance Slowness and Tuning

​

• Prepare the exact step you will do next time. Research DMVs you will look into. Or save off. Especially if you have hit performance problems before. Spend the time now to educate yourself about what to do when this happens. Fair warning: this is not an easy thing to learn. It’s a lot easier if you have a good understanding of how bits and bytes move, or a decent Computer Science understanding, or both.

• Dedicate someone. Bring in a database administrator, database developer or programmer. Just know it will take time. Especially if this person doesn’t deal with performance problems every day.

• Make the performance of someone else’s problem. Hire a company like ours. We’d love to help! We do tuning every day and have done it for over a decade now. Take a look at our AWESOME tuning 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 great to help. Tuning results vary. They vary based on the complexity of the query, how much tuning has gone into it already, etc. But we rarely can’t make T-SQL perform faster.

​

​

Make SQL Performance Tuning someone else’s problem. Hire someone. We’d love to help! 

​

We tune slow SQL Servers every day. For over a DECADE now. We can solve your database performance problems!