SQL Server Performance Tuning

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

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

Forwarding Pointers in Heaps

When no clustered index is defined on a table, that table is said to be a Heap. Heaps are not ideal when it comes to performance but there are lots of instances where you have heaps.   Operations on heaps INSERT -  New rows can be placed in the first available page with sufficient space. […]

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

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

Using Filtered Indexes to Improve Performance

A Filtered Index is a new index type introduced in SQL Server 2008. In simple terms, rather than adding a non-clustered index to the entire table, you can add an index to a filtered data set. The syntax for creating a filtered index is : CREATE NONCLUSTERED INDEX <Index_Name> ON <Table Name>(<Column(s)>) WHERE <Filter Condition> […]

SQL Server Performance Tuning – Index Tuning

In earlier installments of this series we looked at T-SQL Performance optimizations along with different T-SQL practices, we can now turn our attention to the second part of this series which is index Tuning In looking into Views we will break the article into two sections : Basic Index Tuning using DTA (Database Tuning Advisor) […]

VARCHAR (MAX), NVARCHAR (MAX) and NTEXT Data Types

Despite NTEXT and TEXT being deprecated in SQL Server for some time they are still both used in production systems. In this article, I will briefly demonstrate the difference between a VARCHAR (MAX), NVARCHAR (MAX) and the NTEXT data types, and the impact on performance from using NTEXT/TEXT. Let’s create a table named Student using […]

SQL Server T-SQL Tuning – NOT IN and NOT Exists

Following on from the previous TSQL performance tuning article in this series, we now focus on the important topic of T-SQL “Not IN” And “Not Exists “ Not in and Not Exists commands : In some circumstances we will need to select/update/delete orphan data – that is data does not exists in the major table […]

SQL Server T-SQL Tuning -TVF and Scalar Functions

In the first of this series we looked at TSQL tuning for temp tables, table variables and the Union command , we now turn our attention to User Defined Functions – either Table-Valued Functions or Scalar Functions. A UDF is very convenient for centralising business logic as we can specify a set of business logic […]
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 |