SQL Server Performance Tuning

SQL Server Processor Performance, 2006

This historical view on the progression in processor performance over time is presented as a guide to upgrading older server systems and as an acquisition strategy for new systems. The focus is on the Intel IA-32 line because Intel designs processors to Moore’s Law, and a common set of performance results are available for this […]

Processing event logs using DumpEvt and SQL Server

Introduction As a DBA, you can find very useful information in the Windows event logs. About important events, the health of your SQL Server and the operating system it runs on. Unfortunately, the logs also contain a lot of useless information. Some applications have a tendency to log hundreds of events every day, filling up […]

How to Detect Table Fragmentation in SQL Server 2000 and 2005

How do you know when a table is fragmented? Poor query performance over time. More disk activity. Poor cache utilization. Verify the I/O of a query. Verify scan density in SQL 2000 using DBCC SHOWCONTIG and in SQL 2005 using the dynamic management view sys.dm_db_index_physical_stats. Table fragmentation can occur when modifying data with INSERT, UPDATE, […]

Techniques for Indexing Low-Selectivity Columns in SQL Server

Most of us have probably faced this situation at one time or another: there’s a deceptively simple query in your system that’s performing poorly: SELECT col1, col2, col3 FROM aLargeTable WHERE flag = 1 On the surface, this seems like an easy thing to solve. Looking closer, though, it’s likely that our offending “flag” column […]

Ranking Functions and Performance in SQL Server 2005

Ranking functions, introduced in SQL Server 2005, are a great enhancement to Transact-SQL. Many tasks, like creating arrays, generating sequential numbers, finding ranks, and so on, which in pre-2005 versions requires many lines of code, now can be implemented much easier and faster. Let’s look at the syntax of ranking functions: ROW_NUMBER () OVER ([<partition_by_clause>] […]

Practical Solution to SQL Server Performance Monitoring

The list of common DBA tasks includes setting up a server performance base line and comparing the performance of two or more servers. We often have to do this urgently when our customer calls and expects us to identify and remove a performance bottleneck right away. The following article includes code and hands-on instruction on […]

System and Storage Configuration for SQL Server

A proper, technically correct system and storage sizing assessment of a SQL Server system and storage configuration needs to consider a broad range of CPU, memory and IO usage analysis. The cost of this assessment might be $10-20K with proper accounting for personnel time, depending on the availability of an existing production environment from which […]

SQL Server Performance: Query Tuning vs. Process Tuning

In the different projects where I perform a performance audit or am involved in performance tuning, I often find that while queries and stored procedures function correctly, they are not efficient in processing larger data sets. Many OLTP databases are set up as OLAP databases to handle single records instead of a record set as […]

Boost Performance and Reduce Code Use With SQL Server Aggregate Functions

Aggregate functions, a staple means of summarizing large volumes of data for serious database developers, are mainly used for generating business data reports. Aggregate functions allow you to retrieve summarized information from a table by operating on a set of data as a whole rather than on each data element. These functions contribute to keeping […]

Clustered Indexes in SQL Server: Things You Need to Know

This article covers a few, more advanced topics about the usage of clustered indexes in SQL Server. Not only will I try to convince you of the absolute necessity of using clustered indexes, I’ll also give you some tips on how to use them in a not so obvious context. Why You Need Clustered Indexes! […]