by Ryan Huber
A continuation of the blog post Database 101-4: Why should you care about index maintenance?
Now that we’ve discussed how data is stored and indexed in SQL Server, I want to discuss how data is processed. How does all of this data movement and processing get done? In our real-world example, it’s a human worker who does the work of taking the returns in and out of the folder, making data changes on the paper pages, and keeping the returns in order.
In SQL, the worker is the CPU. The CPU does all of those same activities. However, in terms of performance, this is where the real world vs SQL example breaks down. Let’s say you had to sum up all of tax data you have by state.
The longest part of the task for a human worker would be paging through each return and doing the calculation by hand. In SQL though, the CPU can do that same calculation in milliseconds.
Because of that, the bottleneck for that transaction is almost never the calculation itself, but the other work of pulling the data pages out of the cabinet so you can do the calculation.
This is the reason why, on most SQL instances, you usually don’t see CPU usage run above 50%. Your performance bottleneck is usually pulling the data pages off of disk and into the next component we’re going to discuss.
Stepping back to our real-world example for a minute, the one thing we haven’t discussed yet is where the worker does all of their tasks. They need a desk where they can spread out all of the paper pages to do their work.
As you can imagine, if you provide a bigger desk, you have more room for paper which keeps workers from having to make trips back and forth to the cabinet. Everything gets done faster.
SQL is no different. SQL’s desk is RAM, specifically a section called the buffer pool. It’s the space where SQL puts data pages to be read and modified. The bigger the space you provide to SQL to do its work, the faster everything goes.
Ryan Huber is the Senior Database Architect at SoftWriters, a Pittsburgh-based company that offers pharmacy management software solutions to pharmacies serving the long-term care community. Ryan is our in-house SQL guru, providing technical expertise in every facet of database operations, including architecture and code review, resolving performance issues for customers, and formulating a strategy for existing and future product features and enhancements.