SQL Server Performance Tuning

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

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