SQL Server Performance Tuning

Breaking Down Complex Execution Plans

As a DBA, whether working as a full time employee of a company or as a consultant, you’re often handed stored procedures or queries long after they’ve been “finished.” These finished queries are frequently enormous, free-flowing affairs with between 50 and 500 statements joining up to 80 tables, views, derived tables and functions. Together they […]

How SQL Cluster Resource DLLs work with Cluster Components to Improve the Failover Process

The objective of this article is to explain the SQL Server clustering internal process.  This article will explain the functions used to interact with the cluster components. The article doesn’t really show how to cluster SQL Server. Most of this article talks about SQL Server 2000 but the same information applies to later versions of SQL […]

SQL Server 2008’s Management Data Warehouse

I’ve been on a performance kick lately.  I’ve been sharing with you how to use SQL Server 2005 DMVs and DMFs to identify the resource intensive queries for SQL Server 2005.  Well now I’m going to switch gears and look at SQL Server 2008 and discuss one of the new concepts known as Management Data […]

Data Comparison with Data Dude

I have already written two articles regarding Data Dude (aka Visual Studio Team Edition for Database Professionals); one published as Schema Comparison with Data Dude and other published as Test Data Generation with Data Dude. This is my third article regarding Data Dude, and in this article I will illustrate the way of doing “Data Comparison” with […]

New Features in ASP.NET 3.5

ASP.NET has for long been a popular web application development platform. ASP.NET 2.0 added a lot of new features compared to its earlier counterpart, i.e., ASP.NET 1.1. With ASP.NET 3.5, you have even more features. This article will present the new features that have been added to ASP.NET and Visual Studio 2008. Fine, but, what’s […]

How to Use System Monitor to Identify SQL Server Hardware Bottlenecks

Performance Audit Checklist  Counter Name    Average   Minimum   Maximum   Memory: Pages/sec        Memory: Available Bytes        Physical Disk: % Disk time        Physical Disk: Avg. Disk Queue Length        Processor: % Processor Time        System: Processor Queue Length        SQL Server Buffer: Buffer Cache […]

Monitoring Stored Procedure Performance with sys.dm_exec_cached_plans

In my last article (http://www.sql-server-performance.com/articles/per/tsql_statement_performance_p1.aspx) I showed how to use the sys.dm_exec_query_stats dynamic management view (DMV).  This article showed how to use the information from this DMV to determine what TSQL statements where the worst performing statements on an instance of SQL Server 2005.  In this article I will continue on with my performance monitoring […]

Schema Comparison with Visual Studio Team Edition for Database Professionals

Most DBAs work with different environments such as production, quality assurance and development where the same database schema exists. One of the biggest issues we have been facing while maintaining the same database schema in various environments is making sure that the objects exist in the databases are the same in all environments and if […]

SSIS Usage of Checkpoint File

If you are an ETL application developer or administrator, one problem you may have faced is rerunning the entire ETL application in the event of failure. Rerunning the application from the point of failure is often not possible due to many reasons, but mainly due to the difficulty in implementing logging and maintaining each step of the application and […]

Query Execution Statistics

In my last article I talked about tracking performance of TSQL statements (http://www.sql-server-performance.com/articles/per/tsql_statement_performance_p1.aspx).   In that article I discussed a number of different ways to obtain performance information for your query statement or set of statements.  One of my methods I mentioned to obtain query statistics was to use the “sys.dm_exec_query_statistics” dynamic management view (DMV).   In […]