SQL: Should I focus on reads or writes in SQL Server?

I’m involved in a lot of performance tuning work for SQL Server based applications. These are mostly your typical OLTP accounting, financial, or record keeping applications. One thing that constantly surprises me is that many developers and DBAs really aren’t sure about where their tuning efforts need to be focused.

In particular, what really surprises me is how much focus most people have on write/update behavior and how little on read behavior. I think there’s a perception problem.

If I ask a developer what percentage of the time they think the database will be writing (vs reading), I’m invariably told numbers like 20 or 25 percent. In fact, I remember having a discussion with a SQL Server product group member who was writing an MSDN whitepaper and I was a reviewer. He told me that he thought 40 to 60 percent was common.

But every time I measure this for these types of applications, and over a lengthy period, guess what write percentages I normally see?

Less than one percent is common and 1 or 2 percent is quite typical.

Even if you need to update a row, you have to find it first. I’m convinced that these applications that write 40 or 60 percent of the time are as commonplace as unicorns.

[caption id=“attachment_2987” align=“alignnone” width=“426”] Image by Levi Saunders[/caption]

Now I’m not suggesting that there aren’t applications where write performance is critical, but I can assure you that the vast majority of my tuning work is going to focus on making reads as fast as possible, because that’s what the systems are going to spend their times doing.

I’m going to focus on what the system is doing 99 percent of the time, not so much on the 1 percent, unless the writes are particularly time critical for some reason.

And the wonderful thing is that if you reduce the impact of the reads, the system has more time to work on writes anyway. Reducing the impact of reads will generally improve the performance of your writes.

2018-04-09