SQLServerCentral.com Articles tagged Indexing

Stairway to SQL Server Indexes: Level 1, Introduction to Indexes

Fri, 05 Apr 2013 06:00:00 UT

Indexes are the database objects that enable SQL Server to satisfy each data access request from a client application with the minimum amount of effort, resulting in the maximum performance of individual requests while also reducing the impact of one request upon another. Prerequisites: Familiarity with the following relational database concepts: Table, row, primary key, foreign key

New! Safeguard your SQL backups
Protect your backups from onsite disaster with SQL Backup Pro and a Hosted Storage account from Red Gate. Learn more

Precision Indexing: Basics of Selective XML Indexes in SQL Server 2012

Wed, 06 Feb 2013 07:00:00 UT

Seldom has a SQL Server Service pack had such an effect on database development as when SQL Server 2012 SP1 introduced selective XML indexes. These transform the practicality of querying large amounts of XML data. Seth Delconte demonstrates how and why this feature makes all the difference.

SQL Server Performance Tuning – Index Tuning

Wed, 30 Jan 2013 07:00:00 UT

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

Stairway to SQL Server Indexes: Level 3, Clustered Indexes

Fri, 25 Jan 2013 07:00:00 UT

Now that we've seen the basics of indexing, and taken a deeper dive into Nonclustered Indexes, this Level will focus on searching the table, which will, in turn, will lead us to a discussion of clustered indexes.

Review Your Indexing

Mon, 26 Nov 2012 07:00:00 UT

Today Steve Jones reminds us that we should analyze and re-evalute our indexing strategy on a regular basis.

Partitioned Tables, Indexes and Execution Plans: a Cautionary Tale

Thu, 08 Nov 2012 07:00:00 UT

Table partitioning is a blessing in that it makes large tables that have varying access patterns more scalable and manageable, but it is a mixed blessing. It is important to understand the down-side before using table partitioning.

Make working with SQL a breeze
SQL Prompt 5 is the effortless way to write, edit, and explore SQL. It's packed with features such as code completion, script summaries, and SQL reformatting, that make working with SQL a breeze. Try it now.

Tune Your Indexing Strategy with SQL Server DMVs

Tue, 23 Oct 2012 06:00:00 UT

SQL Server Indexes need to be effective. It is wrong to have too few or too many. The ones you create must ensure that the workload reads the data quickly with a minimum of I/O. As well as a sound knowledge of the way that relational databases work, it helps to be familiar with the Dynamic Management Objects that are there to assist with your indexing strategy.

Getting Started with the New Column Store Index of SQL Server 2012

Tue, 09 Oct 2012 06:00:00 UT

Column Store Index, a new feature in SQL Server 2012, improves performance of data warehouse queries several folds. Arshad Ali shows you how to create column store index, and how to use index query hint to include or exclude a column store index.

Fragmentation Fear

Mon, 01 Oct 2012 06:00:00 UT

Index fragmentation can certainly cause performance problems, but should it really dictate the way we design our clustered indexes?

Targeted Index Performance Improvements

Fri, 03 Aug 2012 06:00:00 UT

This article identifies which indexes are used when T-SQL code runs, and proposes ways of improving these targeted indexes, leading to better performing code.

Introduction to Indexes: Part 3 – The nonclustered index

Mon, 23 Jul 2012 06:00:00 UT

Part 3 of a great series on the basics of indexes. Learn the structure, definition, and how to examine the use of these indexes in your queries.

Introduction to Indexes: Part 2 – The clustered index

Fri, 06 Jul 2012 06:00:00 UT

In the second part of her series on indexing, MVP Gail Shaw examines clustered indexes. Learn what this index is, how it differs from a heap, and how it can help your queries.

Indexes, Indexes, Indexes

Wed, 04 Jul 2012 06:00:00 UT

This article has a description of an index utilization tool that Ron Johnson uses to analyze the effectiveness and use of indexes in a system.

Introduction to Indexes

Fri, 29 Jun 2012 06:00:00 UT

Indexes are critical to good performance. However many people don't understand how indexes well. MVP Gail Shaw provides us with an introductory article on the basics of indexing.

Stairway to SQL Server Indexes: Level 15, Indexing Best Practices

Wed, 28 Mar 2012 06:00:00 UT

Dos and don’ts that lead to a healthy, well-performing database.

New! Safeguard your SQL backups
Protect your backups from onsite disaster with SQL Backup Pro and a Hosted Storage account from Red Gate. Learn more

Stairway to SQL Server Indexes: Level 14, Index Statistics

Fri, 16 Mar 2012 06:00:00 UT

A look at the information that helps SQL Server make better decisions.

Stairway to SQL Server Indexes: Level 13, Insert Update Delete

Wed, 29 Feb 2012 07:00:00 UT

What happens to your indexes during normal database activity?

Stairway to SQL Server Indexes: Level 12, Create Alter Drop

Fri, 17 Feb 2012 07:00:00 UT

Options and impacts when creating, altering and dropping an index

Tips to optimize your SQL statements

Fri, 10 Feb 2012 07:00:00 UT

Don't blame SQL Server if your SQL Statements perform badly! But where do you start to investigate the problem? This article is a primer on were to start looking when your SQL runs slowly.

Rebuilding SQL Server indexes using the ONLINE option

Wed, 08 Feb 2012 07:00:00 UT

As each year goes by the uptime requirement for our databases gets larger and larger, meaning the downtime that we have to do maintenance on our databases is getting smaller and smaller. This tip will look at a feature that was introduced in SQL Server 2005 that allows us to leave our indexes online and accessible while they are being rebuilt.

New! Safeguard your SQL backups
Protect your backups from onsite disaster with SQL Backup Pro and a Hosted Storage account from Red Gate. Learn more

Stairway to SQL Server Indexes: Level 11, Index Fragmentation

Wed, 01 Feb 2012 07:00:00 UT

Internal and external fragmentation – Causes, cures, and when not to care

New! Safeguard your SQL backups
Protect your backups from onsite disaster with SQL Backup Pro and a Hosted Storage account from Red Gate. Learn more

Indexing - Take the Hint and Leave it to the Experts

Mon, 30 Jan 2012 07:00:00 UT

The most common T-SQL command in use has to be the SELECT statement, it is the bedrock of any SQL Professional's day. Sometimes it's used to snatch some data from a table or two while some quick investigation is done, other times it is at the heart of a stored procedure or view that will inform business decisions for coming months or even years.

Stairway to SQL Server Indexes: Level 10, Index Internal Structure

Fri, 20 Jan 2012 07:00:00 UT

A detailed look the B-Tree structure – Leaf level and non-leaf level.

Reduce heap table fragmentation

Tue, 13 Dec 2011 07:00:00 UT

A table that does not have a clustered index is referred to as a Heap. While a lot has been written about index fragmentation and how to defrag indexes, there is not much that talks about how to defrag a heap table.

Using a Clustered Index to Solve a SQL Server Deadlock Issue

Tue, 01 Nov 2011 06:00:00 UT

At some time or another every DBA has been faced with the challenge of solving a deadlock issue in their SQL Server database. In the following tip we will look at how indexes and more specifically clustered indexes on the right columns can help reduce the chance of your applications receiving this dreaded error

Stairway to SQL Server Indexes: Level 9, Reading Query Plans

Wed, 05 Oct 2011 06:00:00 UT

Determining how, and if, SQL Server is using your indexes.

Stairway to SQL Server Indexes: Level 8, Unique Indexes

Wed, 14 Sep 2011 06:00:00 UT

Indexes that ensure data integrity in addition to locating rows.

SQL Server Index Statistics

Wed, 14 Sep 2011 06:00:00 UT

Are your index statistics up to date? The SQL Server database engine uses statistics to determine which execution plan to use when a query is compiled. Index statistics are used to help the SQL Server engine determine the cardinality of different column values in a SQL Server table. If you want to give SQL Server a fighting chance at picking an optimal execution plan then you need to make sure you maintain your index statistics.

Are remote databases fragmented? Let's Defrag.

Mon, 12 Sep 2011 06:00:00 UT

This article shows an automated reorg/rebuild index for remote servers with notifications for the DBA.

Stairway to SQL Server Indexes: Level 7, Filtered Indexes

Wed, 24 Aug 2011 06:00:00 UT

A filtered index eliminates unusable entries from the index, producing an index that is as beneficial as, but much smaller than, an unfiltered index. If a certain subset of a table's data is frequently requested, a filtered index can also be a covering index; resulting in a considerable reduction in IO.