SQL Server Performance Tuning

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

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