SQL Server Performance Tuning

How to Correlate a SQL Server 2005 Profiler Trace With System Monitor Performance Counter Data

Before SQL Server 2005 became available, I often ran into two situations when using either Profiler or System Monitor that were completely frustrating. First, I’d see a long duration event in Profiler and I wanted to know how it affected server performance. Or second, I would see a spike in server activity in System Monitor […]

An Introduction to Dynamic Management Views and Functions in SQL Server 2005

One my most favorite features of SQL Server 2005 is the introduction of Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs). They allow the DBA to take a peek into the internal workings of SQL Server at both the server and database levels. Using DMVs and DMFs, the DBA can better monitor the health […]

New Isolation Levels Available in SQL Server 2005

One of the more mysterious features of SQL Server is isolation levels. Whenever a statement is executed, or a data modification is made, it runs under the influence of an isolation level. The isolation level directly impacts the performance of SQL Server, along with the integrity of the data. So what exactly is this mysterious […]

Using Plan Guides in SQL Server 2005

One of the biggest frustrations for a DBA is performance tuning third-party applications. In most cases you can’t modify the code directly, so you have to live with the code the application provides you. In most cases, your only opportunity for tuning third-party applications is to add or modify indexes. Sometimes, this can really help […]

Beware: New Query Hints Added to SQL Server 2005

If you have spent much time reading the tips, FAQs, and articles I have written on this Web site, you have probably noticed that I am not in favor of using query hints in queries. For those of you who may be a little new to SQL Server, a query hint is a way to […]

Adding Indexes to Persisted Computed Columns in SQL Server 2005

Previous to SQL Server 2005, SQL Server supported the concept of computed columns. A computed column is simply a column in a table that is computed from an expression that can include other columns in the table, constants, functions, variables, and most any combination of operators. What many DBAs may not realize is that computed […]

Using Asynchronous Statistics Updating in SQL Server 2005

Most DBAs are familiar with the AUTO_UPDATE_STATISTICS database option. Under most conditions, this feature provides the Query Optimizer with up-to-date index and column statistics so that optimum query plans can be created to execute queries. But what many DBAs don’t know is how this option affects the performance of queries. For example, the AUTO_UPDATE_STATISTICS option, […]

Forced Parameterization in SQL Server 2005

One of the main benefits of using a stored procedure to execute Transact-SQL code is that once a stored procedure is compiled and executed the first time, the query plan is cached by SQL Server. So the next time the same stored procedure is run (assuming the same connection parameters are used), SQL Server does […]

An Instance of SQL Server 2000 Performance Tuning

This article is about altering a table and adding NOT NULL columns into it. The table being altered contains MILLIONS of records. One straight forward approach could be: Step 1: Alter Table — Add Nullable Column. Step 2: Update Newly Added Column. Step 3: Alter Table — Modify New Column to Not Null. I tried […]

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 […]

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |