Ok, there is no way to effectively blog about SQL Server performance. It’s a HUGE topic. But I need to add some content to this blog as a starting point. I’ve been giving it a lot of thought, that is, where do I start in this blog around SQL Server performance?
What I’m going to do is to use a recent customer correspondence that was a very broad ask for more information on SQL Server performance. I’ve cobbled that together into a series of focus areas below with links to details in the form of whitepapers and on-line articles.
So what I will do going forward for MSSQLDUDE blog, is to dive into each of these areas as a focus area for SQL Server performance with summarizations of some of these very large, lengthy, detailed and complex whitepapers. My intent here will be to provide my casual readers with a way to quickly grasp the concepts around SQL Server performance monitoring & tuning.
So, away we go!
We have to start somewhere, so a good starting point is to understand Clustered Indexes on tables vs. Heap tables. Then I would make use of the awesome out-of-the-box SQL Server tool called the Database Engine Tuning Advisor that can analyze your queries and give you guidance on where you need additional index coverage. For those who wish to really dig deeper into SQL Server’s engine internals to understand performance, there is a very detailed whitepaper here on plan caching from the unbeatable SQL Server CAT team at Microsoft in Redmond.
SQL Server uses the out-of-the-box concurrency mode, READ COMMITTED, to prevent dirty reads by specifying that statements cannot read data values that have been modified but not yet committed by other transactions. Other transactions can still modify, insert, or delete data between executions of individual statements within the current transaction, resulting in non-repeatable reads, or “phantom” data. Snapshot Isolation concurrency mode was first delivered in SQL Server 2005 and can be useful in very high-transaction OLTP systems. Use the out-of-the-box SQL Profiler tool to identify blockers or deadlocks and read here more about Snapshot Isolation.
Hardware & Storage Level
There is very important guidance in this best-practice page regarding how to configure storage, in particular, to maximize performance of your SQL Server instances. A note here is that what I find most frequently at customer sites using SQL Server is that not enough attention was paid up-front into understand the ways that SQL Server accesses data from TempDB and how SQL Server reads from disk. Also, SQL Server is very good at scale-up in SQL Server & Windows Server 2008, taking advantage of kick-butt hardware where it can utilize literally hundreds of virutal processors and 2 TBs of RAM. So if you are a DBA or developer, make sure your storage and system admins read through the whitepapers on configuring the right server hardware and SAN techniques that are particular to SQL Server and are different than you will see in Oracle’s database recommendations.
It is critical that, as a DBA, you actively monitor for memory pressure on your SQL Server boxes and measure and update your baselines regularly to determine hardware strategy and possible scale-out or scale-up requirements. Here is a link on how to identify memory pressure on your SQL Server box using the Windows Server System Monitor.