SQL Server index Articles

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

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

Guide to SQL Server Table Indexes – Part 4

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 the second and third part, you learned about the different types of indexes available […]

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

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

Index hints cannot be specified within a schema-bound object.

Error Message:Msg 2011, Level 16, State 1, Procedure MyOrderView, Line 4Index hints cannot be specified within a schema-bound object. Severity level:16. Description:This error message appears when you try to use an index hint within a schema-bound object. Consequences:The T-SQL statement can be parsed, but causes the error at runtime. Resolution:Error of the Severity Level 16 […]

SQL Server Performance Tuning Articles (All)

Performance Tuning for SQL Server Developers How to Take Advantage of SQL Server 2000 Performance Tuning Tools Implementing SQL Server in an OLTP Environment How to Do SQL Server Performance Trend Analysis Part 1: Using Performance Monitor to Log Data How to Do SQL Server Performance Trend Analysis Part 2: Storing Performance Data in SQL […]