SQL Server Performance Tuning

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

WebSite Performance Optimization – Core Concepts

[Note : This was originally published on my personal blog at JudeOKelly.com. However, since the topic is on performance optimization and many SQL Server installations are the backend for a website/webapp I am cross posting this.] When it comes to performance tuning a site, there are a multitude of possible optimizations so I thought it […]

Using PowerShell to Manage SQL Server

The purpose of this article is to demonstrate a custom scripting framework which enables the management of SQLServer databases from a Windows PowerShell session and also to extend Powershell’s flexibility by creating a set of commands customized to our needs. I will call this framework comprised of a library of modules and functions specialized to manage […]

SQL Server Index Maintenance Performance Tuning for Large Tables

It is nothing really new that indexes have their right to exist in SQL Server. It is also nothing really new, that these indexes need to be maintained every now and then, even though this might be surprising for people who are lulled by the otherwise low maintenance for SQL Server. But if you think […]
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 |