Let’s Talk about SQL Server Performance … Part Uno

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.

Memory Pressure
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.

Index Maintenance Schemes, pt 1

I am going to start a new multi-part series on what is likely every DBA’s favorite topic, no matter which RDBMS it may be … indexing.

It is a topic that I talk a lot about with customers and it takes a lot of discussion that can be intricate and is very difficult to find common generalities that work well across all SQL Server workloads. There are a number of factors that affect the approach one takes to an indexing strategy including application type, database size, target workloads, system uptime requirements, among many others.

I found these 3 approaches that a DBA quoted in SSWUG by Stephen Wynkoop in a recent email he sent to SSWUG.org members very interesting (listed below). These are based on maintenance of a system that is a mix of OLTP & OLAP and I thought provide a decent starting point for database indexing in the real world with SQL Server:

Reorganize the indexes throughout the day when updates are occurring. The reorganize only updates the leaf nodes and does not lock the table allowing queries to continue without blocking.
Reindex fragmented tables daily during off hours. Originally we simply ran a maintenance plan that reindexed all tables. However, it got to the point it could not complete in the maintenance windows. I modified a process I posted to SSWUG years ago that reviews each table and it’s fragmentation. It performs an ALTER INDEX command on any table with a fragmentation percentage above my allowed level.
Separate certain tables onto dedicated drives. Even pre-allocating space in the database was not adequate. The need was for the database to allocate contiguous pages on a database file. So, placing certain tables on specific database files allowed them to expand and contract without interleaving with other tables of the same database.