Performance Tuning


Title and name of the article: SQL Server Performance Tuning

SQL Server perform fast enough most of the time. Only the most successful companies require SQL Server Performance Tuning. Why? Because SQL response become too slow.

 

What is SQL Server Performance Tuning?

SQL Tuning is when slow performing T-SQL is messed with and all of the sudden is now returns really fast. Very simple.

 

Examples of SQL Server Slowness:

  1. You have a website, fill out a “Search” form, hit “Search” and that takes a too long to come back.
  2. You have an app, and app takes too long to respond.
  3. You have a SQL Server, and when you look at Performance Monitor it shows CPU at 100%.
  4. SQL Agent jobs can not finish in time or start spilling over into production or high usage windows.
  5. Reports take too long to process. Or never finish.
  6. T-SQL runs fast with one set of parameters, but with others it is slow or never completes.
  7. Many more…

Most often, what we hear is “this was fast (or OK) before and now its crazy slow”

 

Slow SQL Shows Up Only In a Few Ways:

  1. T-SQL was fast before and gradually it became slower.
  2. T-SQL was always slow (or never finishes).
  3. T-SQL was fine before, but after a change (often seemingly unrelated), sql is now slow.

 

Reasons Why SQL Servers Become Slow

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

  1. Lack of good SQL Server Maintenance in place. This affects backups, index and statistics maintenance, corruption checks, etc.
  2. Lack of Configuration Optimization of the SQL Machine. You need settings tweaked in multiple places (yes, BIOS!), Win OS, Security, Group Policy, Active Directory, Instance Settings, database settings
  3. Bad Architecture. Good example is database is setup with one data file and that’s it (when it can have multiple files, spread 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 that have 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 couple of thousand when 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. Good example we see a lot is 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. Good example would be storage or network changes. This usually makes all queries to become slower.
  10. Failing hardware. Certain type of hardware failures, such as storage or networking, wont break SQL Server, but it will make it re-try same operation couple of time.
  11. And Many More…

 

How SQL Slowness Shows Up

 

Happens in two ways:

  1. Easy to catch slowness. You know that app screen used to take half the seconds and now its been 2 minutes and still spinning and hasn’t finished.
  2. Tougher to catch slowness. When query used to take 4 min 37 seconds and it is now taking 6min 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

Dont 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 that 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 that is 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 everything is slow (possibly system wide change in play)?

Then depending on what the answers are, you want to narrow down to exact T-SQL call and ideally with actual parameters that were used.

 

Mistakes People Make With SQL Server Performance Tuning

  1. Reboot too fast. That is one of the worst things you can do. Because of 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 – reboot will wipe out those performance stats that can help identify what went wrong.
  2. At the other end of the spectrum is Reboot too slow. During production times, if you know reboot will “fix” the issue, it may be better to reboot, have few minutes of an outage, rather that to try to troubleshoot for 30min and then have to reboot anyway. It may be better to reboot. And then to spend time to prepare what you will do next when this issue hits again. Prepare some scripts you will run. Write some jobs that will auto captures some details.
  3. Not knowing what is slow. The more specifics you know what exactly is slow the easier it is to troubleshoot
  4. Not knowing exact parameters that are sent into a query that is running slow.
  5. Not preparing for next slowness problem. Slowness should never surprise you. You need to know steps you will do before it happens.

 

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

  1. You dont have enough visibility into the issue. For example, you are not system admin on the server. That will limit what you can look at.
  2. You can’t run some tools. You don’t know any. Never used any.
  3. The older the SQL Server version is the harder is to tune performance.
  4. The older the Win OS the harder it will be.
  5. Not being to talk to people responsible for every layer (Windows sysadmins, Storage admins, networking admins, etc.)
  6. Not having any tools or scripts ready to go.
  7. Taking too long. Any problem can be solved. Eventually. Question is do you have that time it may take to tune or its better to pull in someone that does only tuning?
  8. Not allowed to make changes.
  9. No place to test in.
  10. Making Performance tuning someone else problem that has never done this before.

 

How To Be Ready For SQL Server Performance Slowness and Tuning

  1. Know what you will do next time. Especially if you hit performance problems before. Spend time now to educate yourself what you will do to when it happens. Fair warning: this is not an easy thing to learn. It’s a lot easier if you have good understanding how bits and bytes move, you have decent Computer Science understanding or both.
  2. Dedicate someone. Bring in database administrator, database developer or programmer in. Just know it will take time. Especially if this person doesn’t deal with performance problems everyday.
  3. Make performance someone else 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.

 

Typical SQL Performance Tuning Results

Sometimes changes can make TSQL run 20,000% faster, sometimes 50%, other times slicing off 20% of the cost is great help. Bottom line – tuning results vary. They vary based on complexity of the query, how much tuning has gone into it already, etc. But we rarely can’t make TSQL perform faster.

 

Make SQL Performance Tuning someone else problem. Hire someone. We’d love if you chose us and would love to help! We do tuning every day and have done it for over a decade now!

 

Capabilities

Service 1

This is Photoshop’s version of Lorem Ipsum. Proin gravida nibh vel velit auctor aliquet.

Service 2

This is Photoshop’s version of Lorem Ipsum. Proin gravida nibh vel velit auctor aliquet.

FREE Executive
Report

21 Revealing Questions You Should Ask Any SQL Server Consultant Before Giving Them Access To Your Database

We will email you a copy of our report, "How to Hire Database Consultant – 21 Revealing Questions You Should Ask Any SQL Consultant Before Giving Them Access To Your Company’s Database". !

Send Us a Message

Thank you for contacting us!

See What Other IT Managers Are Saying About Us...

Yielded some very impressive results

The breadth and depth of knowledge with the Microsoft SQL Database platform and ancillary technology stack that Mark has brought has already yielded some very impressive results from a physical and logical architecture perspective.

Prefer to stay anonymous

Quickly identified and fixed SQL performance issues that we struggled with for weeks

Mark was able to quickly identify and fix several SQL performance issues that we had been struggling to trace for several weeks. Additionally, he made several other recommendations and configuration changes that had a significant positive impact on database performance.

Nick Kelland
Vice President of Ops & IT

With little notice Mark was able to find and fix a number of different SQL misconfigurations and wasteful practices.

After a weekend of work the customer realized a 60% decrease in CPU utilization as a direct result of Mark's work.

Jason Milgram
Azure Practice Director

Complex and heavy 5,000+ concurrent connections databases that need tuning, indexes, best practices, up-time, and smooth performance over all

He has mastered the understanding of complex, heavy load TSQL transactions. When tuning, database structure, indexes, stored procedure, best practices, up-time, smooth performance are all important, then Mark is the type of DBA that truly knows how to keep these high standards.

Tim Janssen
Application Manager