SQL Server Performance Tuning

Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance

There are three major theoretical differences between temporary tables: create table #T (…) And table variables: declare @T table (…) The first difference is that transaction logs are not recorded for the table variables. Hence, they are out of scope of the transaction mechanism, as is clearly visible from this example: create table #T (s […]

Online Indexing in SQL Server 2005

Online Indexing is a new feature available in SQL Server 2005. In SQL Server 2005, DBAs can create, rebuild, or drop indexes online. The index operations on the underlying table can be performed concurrently with update or query operations. This was not possible in previous versions of SQL Server. In the past, indexing operations (reorganizing […]

Parallel Statistics Update

Recently in my own practice, I was abruptly reminded of the value of SQL Server’s Update Statistics with Full Scan: to make a long story short, we had a system that was getting a bad query plan that would consistently peg one of the CPUs for hours at a time. If multiple requests for the […]

Making the Most Out of the SQL Server 2005 Performance Dashboard

If you have been using SQL Server 2005 for a while now, you may wonder what I am talking about when I refer to the SQL Server 2005 Performance Dashboard. No, you are not suffering from overwork and fatigue, causing you to lose your mind and forget what new features have been included with SQL […]

Benchmarking SQL Server 2005 Covering Indexes

What is a Covering Index? A covering index is a form of a non-clustered composite index, which includes all of the columns referenced in the SELECT, JOIN, and WHERE clauses of a query. Because of this, the index contains the data the query is looking for and SQL Server does not have to look up […]

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