SQL Server General DBA

Product Review: SQL Database Repair Software

  Introduction Though many SQL Administrators have found the SQL Database Repair software as one of the most competitive database repair and recovery tools, yet I thought of trying the software on my own. Let me explain you why?     Installation & configuration Installation is very simple with couple of steps. No special configurations […]

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

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

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

Monitoring Transactional Replication – The Distribution Queue

Note : The scripts for this article can be downloaded here. Monitoring the distribution queue is an important link in the chain of transactions delivery over a replicated topology. A chain that includes the log reader agent scanning the published database’s transaction log for commands to be moved to the distribution database from which a […]

Performance Tuning Re-indexing and Update Statistics – A Case Study

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

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

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

New T-SQL Commands in SQL Server 2017

Despite the maturity of TSQL, SQL Server 2017 still introduces several new features which to the T-SQL family which can be very useful in database development. CONCAT_WS The CONCAT command was first introduced with SQL Server 2012< to simplify joining multiple columns as below: SELECT CONCAT(Title,' ' , FirstName, ' ' , MiddleName, ' ' […]