SQL Server Performance Tuning

System and Storage Configuration for SQL Server

A proper, technically correct system and storage sizing assessment of a SQL Server system and storage configuration needs to consider a broad range of CPU, memory and IO usage analysis. The cost of this assessment might be $10-20K with proper accounting for personnel time, depending on the availability of an existing production environment from which […]

SQL Server Performance: Query Tuning vs. Process Tuning

In the different projects where I perform a performance audit or am involved in performance tuning, I often find that while queries and stored procedures function correctly, they are not efficient in processing larger data sets. Many OLTP databases are set up as OLAP databases to handle single records instead of a record set as […]

Boost Performance and Reduce Code Use With SQL Server Aggregate Functions

Aggregate functions, a staple means of summarizing large volumes of data for serious database developers, are mainly used for generating business data reports. Aggregate functions allow you to retrieve summarized information from a table by operating on a set of data as a whole rather than on each data element. These functions contribute to keeping […]

Clustered Indexes in SQL Server: Things You Need to Know

This article covers a few, more advanced topics about the usage of clustered indexes in SQL Server. Not only will I try to convince you of the absolute necessity of using clustered indexes, I’ll also give you some tips on how to use them in a not so obvious context. Why You Need Clustered Indexes! […]

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