SQLServerCentral.com Content tagged Indexing

Stairway to SQL Server Indexes

Thu, 17 Feb 2011 07:00:00 UT

Indexes are fundamental to database design, and tell the developer using the database a great deal about the intentions of the designer. Unfortunately indexes are too often added as an afterthought when performance issues appear. Here at last is a simple series of articles that should bring any database professional rapidly 'up to speed' with them

DTA and Hypothetical Indexes

Wed, 15 May 2013 06:00:00 UT

For those of you that don’t know DTA stands for Database Engine Tuning Adviser and is available from the Tools...

Hunt Down Tables Without Clustered Indexes

Thu, 09 May 2013 06:00:00 UT

This one's a gimme, but an underrated utility script when I enter a new environment and look for low-hanging fruit...

SQL Server : Part 10: Importance of Key Column Position While Creating Index

Mon, 22 Apr 2013 06:00:00 UT

Now we have discussed about different types of indexes in the last posts. In this post, let us discuss about...

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

Identify Obsolete Indexes

Fri, 08 Mar 2013 07:00:00 UT

Drop indexes no longer being used.

‘Disturbing Development’
Grant Fritchey & the DBA Team present the latest installment of the Top 5 hard-earned lessons of a DBA – read it now

Clustered Index

Tue, 05 Mar 2013 07:00:00 UT

Reorganize indexes for all tables in all user databases

Thu, 14 Feb 2013 07:00:00 UT

Script to reorganize all indexes on all tables in user databases

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.

2008 Index Fragmentation Maintenance

Fri, 11 Jan 2013 07:00:00 UT

Just a stab at automating index maintainenance in SQL Server 2008

Defragmenting and rebuilding indexes

Wed, 09 Jan 2013 07:00:00 UT

Following code snippet will selectively rebuild indexes that have been thrashed after a big indert

The Ultimate Missing Index Finder

Mon, 07 Jan 2013 07:00:00 UT

Reports stats on what the query optimizer records in the DMVs as missing indexes and what it says the cost savings will be if they were present. Can limit by table / schema name patterns.

The Ultimate Index Usage Reporter

Thu, 27 Dec 2012 07:00:00 UT

Reports index stats, index size+rows, member seek + include columns as two comma separated output columns, and index usage stats for one or more tables and/or schemas.

Index Analysis: The Re-Return

Tue, 25 Dec 2012 07:00:00 UT

A couple years back, I wrote a few blog posts about an indexing script that I use with clients from...

Scripting CREATE INDEX Statements Automatically From Your Tables

Tue, 27 Nov 2012 07:00:00 UT

Stored proc for scripting out full CREATE INDEX statements from your table indexes with all filegroup, filter and WITH parameters.

Intelligent Index Reorganize and Rebuild Script - v1.0

Mon, 26 Nov 2012 07:00:00 UT

This script will provide a dynamic mechanism to decide between REBUILDING an index or simply REORGANIZING an index. Tested and approved for SQL Server 2005 & 2008 Standard or Enterprise or R2.

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.

Script for no of Index in a table

Fri, 23 Nov 2012 07:00:00 UT

This script will tell you how many indexes there are for a single table and also it specifically shows how many non clustered index, and also which tables have a heap index.

Smart Index Rebuild - All Databases

Thu, 08 Nov 2012 07:00:00 UT

Rebuilds all indexes from all databases over a specified fragmentation threshold.

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.

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.

Tables, Queues, and Indices as a Percent of DB Size

Tue, 09 Oct 2012 06:00:00 UT

Two views that give you a variety of stats, sortable as a percent of DB size, for tables, indiceses, XML indices, and Service Broker Queues.

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?

Index Defragmentation

Wed, 26 Sep 2012 06:00:00 UT

Index Defragmentation is one of the most important DBA tasks. This will significantly improve query performance.

Top 5 hard-earned Lessons of a DBA
New! Part 4, ‘Disturbing Development’ by Grant Fritchey, features the return of Joe Deebeeay and a server-threatening encounter with ORMs - read it here

Index types 2

Thu, 30 Aug 2012 06:00:00 UT

Defragment all indexes on Database

Fri, 17 Aug 2012 06:00:00 UT

It finds the Indexes on database which needs to be Defragmented and Rebuilds those indexes. Only you need to replace 'database_name' with your database.

2008 Index Rebuild using DMV

Thu, 09 Aug 2012 06:00:00 UT

Index Maintenance procedure with HTML Reporting capabilities. Please refer to attached script for detailed information.