SQL Server General DBA

Avoiding Parameter Sniffing in SQL Server

Parameter sniffing is when SQL Server compiles a stored procedure’s execution plan with the first parameter that has been used and then uses this plan for subsequent executions regardless of the parameters. First let’s look at a worked example. Assume we have table as below. CREATE Table TblData (ID INT IDENTITY PRIMARY KEY , Name […]

SQL Server Transactions and Locking – Part 1

In many situations, data modification requires several steps. For example, you may need to change the values ​​in two separate tables. You can use transactions to complete these two operations as a unit, or if an error occurs, does not change any of the tables. Another key consideration is that most databases must be compatible […]

Find Duplicate Indexes on SQL Server (Script)

Like other mainstream commercial database systems, Microsoft SQL Server allows you to create multiple indexes on the same column of a table. This  increases the likelihood of having duplicate indexes in the database because SQL Server does not prevent you from creating duplicate indexes, infect we can create up to 999 duplicate indexes on each […]

A Journey from OLTP to OLAP

Introduction In this first Part  of the series I will discuss the OLTP and OLAP and the transition from OLTP to OLAP in pursuit performance improvements as well as the benefits of the each technology. Note**: This article is intended for the users who are new to the concepts of the data warehousing. What is […]

Managing and Connecting to AlwaysOn Availability Groups

From the previous AlwaysOn Availability Group article, we provided a name for the availability group listener which is simply a unique DNS name as a Virtual Network Name (VNN) to direct read-write requests to the primary replica and read-only requests to the read-only secondary replica. In this way the client doesn’t need to know which […]

Configuring and Creating An AlwaysOn Availability Group in SQL Server 2012

In the previous article on AlwaysOn Availability Groups in SQL Server 2012, we looked at The Environmental Setup for An AlwaysOn Availability Group. In this article we turn our attention to the creation and configuration of the Availability Group. There are two primary steps to creating an AlwaysOn availability group which has to be done […]

SQL Server 2012 AlwaysOn Availability Group Environmental Setup

Continuing from AlwaysOn Part 1 the series continues with a step-by-step guide to setting up the environment for an AlwaysOn availability group. In this article we will break down AlwaysOn environment preparation into three steps : Installation of SQL Server 2012 on each node/replica/server. Installation of the failover clustering feature on each node/replica/server. Creation of […]

Using The AlwaysOn Feature of SQL Server 2012

This is the first in a four-part series on the new AlwaysOn feature in SQL Server 2012. In this article, AlwaysOn is introduced and contrasted with previous high-availability solutions in SQL Server. The second part of the series will commence with a detailed walkthrough on preparing the environment for AlwaysOn. Prior to SQL Server 2012, […]

Transactional Replication – Unable to Replicate Data onto the Subscriber – A Practical Example

Scenario: A couple of days back, I got a call from one of my users saying that the Data is not being replicated properly onto the Production Reporting Server from the Production OLTP server. We have Transactional Replication scheduled for every 15 minutes between our OLTP and the Reporting server. Approach: I started investigating this […]

Using Temp Tables in SSIS

Temporary tables are created in the TempDB database, which persists for a particular session. The objective is to maintain that session, until the temp table information is used and dump the data into a physical table. To maintain a session in SSIS, there is a ‘Retain Same Connection’ property of the Connection Manager. If we […]