SQL Server Performance Tuning

How to Set Up a SQL Server Stress Test Environment in 8 Steps: Lessons From the Field

This article is the third in a series of four where I share my findings on baselining, monitoring, stress testing and performance tuning. It builds on the foundations of my previous articles on baselining and monitoring. Having a monitoring system in place is an important step in making you feel comfortable with what goes on […]

Using Master..Sysprocesses to Isolate Performance Issues – Part 1

It’s 4:30 P.M. on Friday and your boss comes to you in a panic. The e-commerce system is down, and the database is to blame. You go through your usual what’s-going-on checklist and all you can tell is the CPU on the SQL Server is pegged at 100 percent. You check Profiler, but no high […]

Is Your Java Middleware Impacting Application Performance?

A serious performance problem can arise when connecting a Java application to a SQL Server 2000 database. Many developers are not aware of the issue or of how to avoid it. The essence of the problem is this: By default, most, if not all, Java middleware drivers send string parameters to SQL Server as Unicode. […]

Using TRY/CATCH to Resolve a Deadlock in SQL Server 2005

A deadlock is an inevitable situation in the RDBMS architecture and very common in high-volume OLTP environments. A deadlock situation is when at least two transactions are waiting for each other to complete. The Common Language Runtime (CLR) of .NET lets SQL Server 2005 provide developers with the latest way to deal with error handling. […]

High Call Volume SQL Server Applications on NUMA Systems

One of the most difficult database operations to scale-up on high-end server systems is the network call. In fact, without special precautions, a high call volume database application can have severe negative scaling on large systems. It is suspected that this problem is more pronounced on NUMA systems than basic SMP systems. A NUMA system […]

How Using GUIDs in SQL Server Affect Index Performance

Recently, I was asked my opinion on a database design schema our company developers were working on. When I heard they were using GUID’s (Global Unique Identifier) as PK’s (primary keys), my jaw about dropped. It turns out the reasoning behind the use of GUID’s for keys was completely legitimate, and actually is the reason […]

Finding Duplicate Indexes in Large SQL Server Databases

One important component of tuning a large, heavily used database, is to ensure that the tables are indexed optimally: enough indexing, but not too much indexing for the application you are running. There are rules of thumb about index tuning, but the entire issue is so complex that there’s no “silver bullet” solution that will […]

Are UDFs Harmful to SQL Server Performance?

Well, not really. But it is vitally important to understand when to use them and when to avoid them, as well as how best to construct them so as not to shoot your foot off. SQL Server 2000 introduced user-defined functions (UDFs), and they were immediately hailed as a great tool for encapsulating repetitive code, […]

SQL Server Database Backup Performance with Quest (Imceda) LiteSpeed for SQL Server; 3 Terabytes in Under 1 Hour

Introduction For the last several weeks, I have been working with Imceda (now Quest Software) on improving the performance of LiteSpeed for SQL Server, particularly for high–end storage systems. This report represents my assessment of LiteSpeed performance characteristics. I am an independent consultant, so this report does not represent the views or claims of Quest. […]

SQL Server XML Statistics and Execution Plans

One of SQL Server’s deficiencies when using XML queries is the lack of statistics capability in the XML driver. This is surprising because the ODBC API contains a function for providing statistics on remote data sources. SQL Server defaults to the fixed assumed row count values for remote servers, which is unreasonably high for XML […]