SQL: Best Way to Scale SQL Server Database Performance

SQL: Best Way to Scale SQL Server Database Performance

I see so much written about how to scale SQL Server systems, and this generally starts with needing to improve SQL Server database performance. When I read articles from the SQL Server field support teams with titles like Top 10 Performance Problems for SQL Server, I often just smile.

The problem is one of perspective. If you are looking at the performance problems that are brought to the support teams to solve, you get a very, very skewed view of what’s typical.

That’s because most performance problems are dealt with by database developers, DBAs, application developers, etc. long before they’d go anywhere near product support. So the product support team never sees the most common problems.

If I had to rate performance problems by cause, I’d suggest the following:

  • 40% of issues are related to poor application design
  • 25% of issues are related to poor indexing
  • 20% of issues are related to how the database framework that the application has used talks to the database (either by design or by how it’s been configured)
  • 10% of issues are related to poor concurrency design (blocking issues)
  • The remaining 5% are odd things, and generally the ones that go to product support.

But if I had to provide my #1 tip for improving database performance and increasing scale, it’s this:

Stop talking to the database!

It’s that simple. So many applications that I work with are very, very chatty.

A year back, I stepped through the login process of a web application. A single login ended up causing over 400 calls to the database. That should probably be one or two.

I’ve seen processes that send 55 million calls to the database and run in half an hour. But the same process runs in 2 minutes when the number of database calls is cut to 4000.

I’ve seen windows apps that execute 90,000 remote procedure calls before the first screen comes up. (Loading 90,000 customers one row by agonizing row at a time). It’s a tribute to SQL Server that the application started in about 30 seconds, but that should have been one call, not 90,000.

I’ve recently seen frameworks that have been misconfigured. For every select of a single row of data, the framework executed this:

  • Create a cursor for the command
  • Open the cursor
  • Fetch from the cursor
  • Fetch again from the cursor (this one failed)
  • Close the cursor
  • Re-open the cursor
  • Query for the parameter metadata
  • Close the cursor

And that was done every single time that a single SELECT should have been sent. The problem is that the developers using frameworks like this are blissfully aware of the commands being generated for them in the background.

I’ve seen caching errors where an application ran the same query with the same parameters over 12,000 times per minute. The developers were unaware of this. (They had a typo in the caching code but it “looked like it worked”).

The bottom line is that you need to spend time looking at how your applications interact with the database. If you execute 400 commands every time someone logs on, you will never get any real scale.

I’ll say it again. The best way to scale a SQL Server database (and to get better performance from it) is to stop talking to it incessantly.

2018-01-08