SQL Server Performance Tuning

In this article series we will focus on the primary points of T-SQL performance tuning. In this first installment I will lopok at tuning temp tables, table variables and the Union command. Temp Tables and Table Variables Most database developers are used to breaking down code into small chunks using  temp tables or table variables  [...]

Recently one of my developers came to and complained of slow performance on a particular query. During the investigation I noticed that the primary cause was the poor query execution plan. In this article, I will discuss the approach which I used to improve the execution plan. For demonstration purpose, I will be creating a [...]

Recently we started experiencing a very strange issue in our production reporting environment where the Re-indexing and Update Statistics operation suddenly began taking more than 2 days to complete and was thus causing blockage in the database which in turn caused impairment in application performance. Reporting Server Configuration Details: SQL Server Version: SQL Server 2005 [...]

The purpose of this article is to demonstrate a custom scripting framework which enables the management of SQLServer databases from a Windows PowerShell session and also to extend Powershell’s flexibility by creating a set of commands customized to our needs. I will call this framework comprised of a library of modules and functions specialized to manage [...]

It is nothing really new that indexes have their right to exist in SQL Server. It is also nothing really new, that these indexes need to be maintained every now and then, even though this might be surprising for people who are lulled by the otherwise low maintenance for SQL Server. But if you think [...]

I once walked into a bar, and saw two construction workers pounding each other to a pulp.  The argument was over what was the better tool—a  hammer or screwdriver.  I feel a similar sensation when I see SQL developers arguing over whether to use natural or surrogate keys.    Few other arguments in database design can [...]

When using the BETWEEN operator on multiple columns, you are likely using a 2D range query.  Such queries perform very poorly in SQL Server.   This article will tell you how you can often use additional information to rewrite such queries for much better performance.  We’ll conclude by rewriting an actual BETWEEN query in a manner [...]

Setting up Transactional Replication in SQL Server 2008 R2. Replication is one of the High Availability features available in SQL Server. Transactional Replication is used when DML or DDL schema changes performed on an object of a database on one server needs to be reflected on the database residing on another server. This change happens [...]

General Database concurrency can be defined as the number of users that can work at a given database at the same time while not effecting or interfering with eachother’s work. The greater the number of users working at the same time the higher concurrency is. SQL Server holds Locks on user data to protect data [...]

SQL Server Optimizer, Under the Hood – Partial Aggregates Abstract: In this series of short articles, we lift the hood of the SQL Server Optimizer to examine a few of the many clever tricks used to optimize query performance.  You’ll see that the Optimizer does not limit itself to using only the instructions provided by [...]