SQL Server Performance Tuning

Pivot and UnPivot with SSIS

Introduction The presentation of your data is required for easy analysis. Turning columns into rows and rows into columns is another way of presenting your data so that end-users can understand it easily. Other than presentation purposes, you need to transform your data within your data warehouse application into different data formats. This process can […]

The “sys.dm_os_performance_counters” Dynamic Management View

SQL Server 2005 performance can be tracked and monitored by using performance counters.  The performance counters can be displayed by either the System Monitor (PERFMON) tool, or by using the “sys.os_exec_performance_counters” Dynamic Management View (DMV).  This DMV is new with SQL Server 2005.  This article will explore the different counters exposed by this DMV and […]

Index related DMVs and DMFs – sys.dm_db_index_usage_stats

Examining statistics of indexes is useful for optimizing the performance of queries. Statistics help us determine the usage and worth of indexes. There are many different methods to determine their usability; , which gives core statistics of indexes. The first article in the index-related DMVs and DMFs series discussed the output of sys.dm_db_index_physical_stats, while this, the second of […]

Index related DMVs and DMFs

SQL Server 2005 introduced three new index-related dynamic management views and functions. All these views and functions are information rich and allowing to analysis of indexes. This A look at avg_page_space_used_in_percent This output column gives a clear indication of internal fragmentation of a particular level of an index. Internal fragmentation occurs when the number of […]

Reduce Aggravating Aggregation: Improve the Performance of History or Status Tables

A third-party system that I manage has “history” or “status” tables following a common pattern: in the database, there is some entity (in my case, apartment units) that is labeled with a value indicating the entity’s status, and the value changes over time. Each of these entities has exactly one value representing the current status, […]

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

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 |