SQL Server General DBA

12 Essential Steps After Installing SQL Server

Rolling out SQL Servers is a key task for DBAs.  While for some DBAs this can be a frequent exercise, others will have to face it one time or other throughout their career in an organisation. Successfully planning an installation requires a fair bit of knowledge about SQL Server and a basic understanding of the […]

SQL Server Hardware Optimization

An important concern in optimizing the hardware platform is hardware components that restrict performance, known as bottlenecks. Quite often, the problem isn’t correcting performance bottlenecks as much as it is identifying them in the first place. Start with obtaining a performance baseline. You monitor the server over time so that you can determine Server average […]

Physical Join Operators in SQL Server – Nested Loops

SQL Server implements three different physical operators to perform joins. In this article series we will examine  how each  operators works, its advantages and challenges. We will try to understand the logic behind the optimizer’s decisions on which operator to use for various joins using (semi) real life examples and how to avoid common pitfalls. […]

Time Series Algorithms in SQL Server

This is the fourth article on data mining series. The below are the previous articles in this series. Shopping Basket Analysis in SQL Server Using Decision Trees in SQL Server Data Mining Cluster Analysis in SQL Server This article focuses Time Series Algorithms which are a forecasting technique. One of the most common algorithms used […]

Physical Join Operators in SQL Server – Merge Operator

In Part I of this series on physical join operators we looked at Nested Loops. We now turn our attention to the Merge operator. For this article series I am using an analogy of two sets of standard playing cards. One set with a blue back and another with a red back that need to […]

Understanding SQL Server Change Data Capture

DBA’s often encounter a requirement to audit DML activity such as INSERT/UPDATE/DELETE operations executed against a particular table or a group of tables in a particular database. Change Data Capture (first introduced in SQL Server 2008 R2) can greatly assist this function. Only members of sysadmin fixed server role can enable the Change Data Capture […]

Monitor SQL Server Replication Jobs

The Replication infrastructure in SQL Server is implemented using SQL Server Agent to execute the various components involved in the form of a job (e.g. LogReader agent job, Distribution agent job, Merge agent job) SQL Server jobs execute a binary executable file which is basically C++ code. You can download all the scripts for this […]

Tips For Minimizing Deadlocks in SQL Server

A deadlock or fatal embrace is a situation which occurs when the a process is waiting for the resource that is locked by a second process and that second process is waiting for a resource that is locked by the first process. In this article, we will learn about ways to minimise deadlocks. Tips for […]

Monitoring Table Size Growth in SQL Server

General In this article I introduce a simple process that saves tables size information at points in time which latter allows for tracking down table growth over time. The process is useful in monitoring data growth over time and lets you see what tables are growing rapidly in what databases and can also help in […]

TroubleShooting SQL Server Memory Consumption

Recently on one of our staging servers had a memory consumption issue and even 32 GB of RAM was looking insufficient and application performance was being impaired. Initially I thought the cause would be poorly designed queries, but after a thorough investigation this turned out not to be the case. I noticed that whenever the […]