Top Reasons for Slow Performance

Poor Architecture. This seems to be the unanimously top reason for the poor performing SQL Server. However, the topic of architecture is very wide and it is impossible to discuss every single point of the architecture inefficiency. The topic of architecture spread from indexes to query re-writing and AlwaysOn to a read-only replica, there are infinite things we can include under this topic. However, here are a few topics that were highlighted during the conversation.

It is not easy for anyone to build a future proof schema and code. When we start our business, the requirement of the business is very different than when we are running the same business for many years. The success of the business also brings expansion in different geography and growth of the data which all leads to lots of custom code and temporary fixes, which becomes permanent. Here is a quick list that leads to the poor performance of the SQL Server.

Reason #1: Bad Schema Designing

You may find it surprising lots of people start building a schema without fully grasping the requirements and that leads to poor design at the beginning which leads to very complex queries and terrible server performance.

Here are the common mistakes:

  • Poor Normalization – Flat wide tables or over normalization
  • Redundant data in databases
  • Bad referential integrity (foreign keys and constraints)
  • Wide composite primary keys (and clustered indexes)
  • No stress testing of schema robustness adhering growth patterns

There are many more bad schema designing practices, however, many times we only come across them after our business has been successful and it is difficult to change the schema after a while. If you are starting a new project, it is always recommended to have a proper schema designing from the beginning and follow all the necessary steps of the health check.

Reason #2: Inefficient T-SQL Queries / Code

There are so many reasons for poor coding and most of the time it is because the developer who is writing the code is not 100% familiar with the schema or new T-SQL features. I have seen lots of users using cursors and while loop to insert the data where they can simply insert the data using INSERT…SELECT or SELECT…INTO TABLE.

Here are the common mistakes:

  • Using NOT IN or IN instead of NOT EXISTS or EXISTS
  • Using cursors or white loop instead of INSERT…SELECT or SELECT…INTO TABLE
  • Using SELECT * instead of only necessary column names
  • Forgetting to use parenthesis when using logical OR or AND operators
  • Nesting of subqueries creating a complex execution plan
  • Using functions on Indexed Column in WHERE clause
  • Overuse of scalar user-defined functions
  • Datatype mismatch in predicates (where condition or joins)
  • Interchanging usage of UNION vs UNION ALL
  • Unnecessary using DISTINCT everywhere
  • Dynamic SQL
  • … this can be a month-long series

Reason #3: Poor Indexing Strategies

It is so common to see people using indexing as a solution to get out of the jail card and quite often they end up in jail again for using the card. Indexing is often considered a silver bullet to solve performance problems but honestly, the story is very different. Indexing can work absolutely against you if you using it incorrectly.

Here are the common mistakes:

  • Indexing every single foreign key
  • Indexing every column in the table
  • Many single-column indexes
  • Preferring Heap Table over the Clustered index
  • Underindexing your table
  • Not maintaining your index

Reason #4: Incorrect Server Deployment

The biggest complaint, I hear when people upgrade their SQL Server is the despite newer software and hardware they are getting poor performance. There are many different parameters involved in the slow performance of the SQL Server and the biggest one is the proper deployment.

Here are the common mistakes:

  • Keeping Maximum Degree of Parallelism to 0
  • Not setting the index level to fill factor
  • Lower Filegrwoth
  • Single TempDB files
  • Hosting SQL Server data and log files together
  • Running antivirus on SQL Server files
  • Incorrect value in Max Memory Configuration
  • High latency for your log files
  • … and many more.

Reason #5: Under Provisioned Hardware

I really wish this was the major problem in the industry. Most of the time, I just see over-provisioned hardware but incorrectly deployed SQL Server. Let us see some of the sample questions, I often receive.

  • Our CPU is running 100%, should we provide more CPU?
  • Our memory is confused 95% by SQL Server, how to keep them free?
  • Our disk queue is always very high, should we add more disks and a separate workload?

Honestly, you can always throw more hardware and improve the performance but that is never the case, to begin with. Giving more hardware may temporarily solve the problem but that may not be a permanent solution. One should go through a more scientific way to look at how your resources are consumed and based on that make decision of the hardware.

SQL performance tuning can be an incredibly difficult task, particularly when working with large-scale data where even the most minor change can have a dramatic (positive or negative) impact on performance.

Determining where to start with SQL Server performance tuning can be challenging. Query tuning isn’t an exact science; strictly speaking, there aren’t technically “right” and “wrong” answers. Instead, DBAs are expected to discover the most appropriate solution for any given circumstance or issue.