SQL Server Performance Tuning

Guide to SQL Server Table Indexes – Part 2

In the first part of this article series, you learned about the basics of SQL Server database table’s indexes, the difference between the clustered and non-clustered indexes, and how the leaf nodes, non-leaf nodes, and heaps associated with data storage. In this part and next part, you will learn about the different types of indexes […]

Guide to SQL Server Table Indexes – Part 1

Overview One of the most important tasks for every database administrator (DBA) is to ensure that query times are consistent with service-level agreement (SLAs) or within user expectations. One of the most effective techniques to achieve this objective is to create indexes on tables for your queries.  SQL Server uses indexes to sort and organize […]

Using The System Monitor Tool For Measuring SQL Server Performance

System Monitor, a program to measure the performance of SQL Server allows you to view the objects of SQL Server and performance counters in addition to the behavior of other objects, including threads, processors, memory, and cache. These objects individually associate set of computers for measuring delays, device usage, and other counters indicating overall performance […]

Understanding SQL Server Query Optimization Statistics

In this article, you will learn what query optimisation statistics are and how SQL Server query optimizer uses statistics. You will also learn how to create and manage statistics, and what are the different ways to determine when statistics were last updated in SQL Server. What are Query Optimization Statistics? The term statistics refers specifically […]

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