http://www.sqlservercentral.com/Content tagged Indexing posted on SQLServerCentral.comen-us360sjones@sqlservercentral.com (Steve Jones)Checking XML indexs with a sample.http://www.sqlservercentral.com/scripts/T-SQL/63781/2008/07/20http://www.sqlservercentral.com/scripts/T-SQL/63781/Expert SQL Server trainer Andy Warren brings us a new article on a lesser used feature in SQL server 2005: the ability to disable an index.http://www.sqlservercentral.com/articles/Indexing/63533/2008/07/22http://www.sqlservercentral.com/articles/Indexing/63533/This stored procedure is capable of performing defragmentation indices with avg_fragmentation_in_percent greater than the threshold passed.http://www.sqlservercentral.com/scripts/SQL+Server+2005/63287/2008/07/09http://www.sqlservercentral.com/scripts/SQL+Server+2005/63287/Manual SP to Index Defrag a SQL 2000 database on SQL 2005 that run at spesific times at nighthttp://www.sqlservercentral.com/scripts/Indexing/63036/2008/06/19http://www.sqlservercentral.com/scripts/Indexing/63036/Two weeks ago Andy started his wildly successful series on Worst Practices. This week he continues that series discussing why failing to use primary keys and clustered indexes are worst practices. Agree or disagree, read the article and join the discussion. One thing you'll have to admit, reading an article by Andy is a lot more interesting than reading Books Online! http://www.sqlservercentral.com/articles/Miscellaneous/worstpracticesnotusingprimarykeysandclusteredindex/488/2008/05/09http://www.sqlservercentral.com/articles/Miscellaneous/worstpracticesnotusingprimarykeysandclusteredindex/488/One of the issues you'll face with SQL Server is blocking which is caused by other processes that are holding locks on objects. Until the locks are removed on an object the next process will wait before proceeding. This is a common process that runs within SQL Server to ensure data integrity, but depending on how transactions are run this can cause some issues. Are there ways to get around blocking by using different indexes to cover the queries that may be running?http://www.sqlservercentral.com/redirect/articles/62913/2008/05/02http://www.sqlservercentral.com/redirect/articles/62913/Indexing a SQL Server database in some respects is considered both an art and a science. Since this is the case, what are some considerations when designing indexes for a new database or an existing one in production? Are these the same types of steps or not? Do any best practices really exist when it comes to indexing? Where does indexing fall in the priority list from an application or production support perspective?http://www.sqlservercentral.com/redirect/articles/62577/2008/04/03http://www.sqlservercentral.com/redirect/articles/62577/When is it okay to let SQL Server to make a statistic for columns in your queries, and when should you take those statistics and make an index out of them? What I do is to test all of my procs, and if they generate system statistics (_WA_SYS%), then I add an index to the table for the column in the statistic. But is this a good practice?http://www.sqlservercentral.com/redirect/articles/62315/2008/03/11http://www.sqlservercentral.com/redirect/articles/62315/http://www.sqlservercentral.com/questions/SQL+Server+2005/62226/2008/03/07http://www.sqlservercentral.com/questions/SQL+Server+2005/62226/Optimize SQL Server non-clustered indexes and queries by considering index fields, compound indexes and SQL Server statistics' impact on non-clustered indexes.http://www.sqlservercentral.com/redirect/articles/62300/2008/03/05http://www.sqlservercentral.com/redirect/articles/62300/http://www.sqlservercentral.com/questions/SQL+Server+2008/62229/2008/02/21http://www.sqlservercentral.com/questions/SQL+Server+2008/62229/http://www.sqlservercentral.com/questions/Indexing/62225/2008/02/19http://www.sqlservercentral.com/questions/Indexing/62225/Paul Randal of SQLskills takes a look at lock escalation in SQL Server 2008http://www.sqlservercentral.com/redirect/articles/62004/2008/01/30http://www.sqlservercentral.com/redirect/articles/62004/One of the new features found in SQL Server 2005 is the ability to add additional columns, called Included Columns, to a non-clustered index. This article will explain the advantages of using included columns and what impact they will have on your database.http://www.sqlservercentral.com/redirect/articles/61972/2008/01/28http://www.sqlservercentral.com/redirect/articles/61972/ Find why SQL Server clustered index design should be narrow and static and how clustered indexes affect many-to-many tables to improve database performance.http://www.sqlservercentral.com/redirect/articles/61924/2008/01/16http://www.sqlservercentral.com/redirect/articles/61924/Generate create statements for missing FK indexes, based on DRIhttp://www.sqlservercentral.com/scripts/Indexing/61391/2007/12/21http://www.sqlservercentral.com/scripts/Indexing/61391/Most DBAs are constantly looking for ways to tune their servers to run better. Joe Doherty brings us the second part of his series on indexes with an in depth look at how the indexes are stored in your database. http://www.sqlservercentral.com/articles/Indexing/2760/2007/12/13http://www.sqlservercentral.com/articles/Indexing/2760/Indexes directly affect the performance of database applications. This article uses analogies to describe how indexes work. The estimated execution plan feature of the Query Window is utilized to compare the performance of two queries in a batch.http://www.sqlservercentral.com/redirect/articles/61605/2007/12/04http://www.sqlservercentral.com/redirect/articles/61605/Most DBAs are constantly looking for ways to tune their servers to run better. Joe Doherty brings us the second part of his series on indexes with an in depth look at how the indexes are stored in your database. http://www.sqlservercentral.com/articles/Indexing/2760/2007/12/13http://www.sqlservercentral.com/articles/Indexing/2760/This article covers a few, more advanced topics about the usage of clustered indexes in SQL Server. Not only will I try to convince you of the absolute necessity of using clustered indexes, I'll also give you some tips on how to use them in a not so obvious context. http://www.sqlservercentral.com/redirect/articles/2210/2006/01/04http://www.sqlservercentral.com/redirect/articles/2210/Index Creation Guidelines for SQL Server can be pretty sparse. Usually there are a couple, clustered index for ranges, nonclustered, etc. Leo Peysakhovich has taken some time to write down his guidelines based on his experience for creating indexes and the rational for doing so. He's also taken a few minutes to look at which indexes NOT to create, something that might be worth knowing.. http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/indexcreationguidelines/1389/2005/10/21http://www.sqlservercentral.com/articles/Performance+Tuning+and+Scaling/indexcreationguidelines/1389/Two weeks ago Andy started his wildly successful series on Worst Practices. This week he continues that series discussing why failing to use primary keys and clustered indexes are worst practices. Agree or disagree, read the article and join the discussion. One thing you'll have to admit, reading an article by Andy is a lot more interesting than reading Books Online! http://www.sqlservercentral.com/articles/Miscellaneous/worstpracticesnotusingprimarykeysandclusteredindex/488/2008/05/09http://www.sqlservercentral.com/articles/Miscellaneous/worstpracticesnotusingprimarykeysandclusteredindex/488/SQL Server 2000 has indexed views, which can greatly improve database performance. However there are a number of restrictions on building the view, including the restriction against outer joins. So how can this work? New author Jean Charles Bulinckx brings us a technique that can help you get around this restriction. http://www.sqlservercentral.com/articles/Indexing/indexedviewswithouterjoins/1884/2005/05/19http://www.sqlservercentral.com/articles/Indexing/indexedviewswithouterjoins/1884/Do you use the maintenance plans or hate them? Wish they would do more? Curious about how they work under the hood? Cmon, you gotta read this one! Trust us, it's not another "how-to" article! Well, maybe just a little bit! http://www.sqlservercentral.com/articles/Administering/sqlmaintenanceplans/663/2005/03/04http://www.sqlservercentral.com/articles/Administering/sqlmaintenanceplans/663/When it comes to auditing index use in SQL Server databases, I sometimes get overwhelmed. For example, how to do you go about auditing indexes in a database with over 1,500 tables? While auditing a single index is relatively straight-forward, auditing thousands of them in multiple databases is not an easy task. Whether the task is easy or not, it is an important task if you want to optimize the performance of your SQL Server databases. http://www.sqlservercentral.com/redirect/articles/1663/2005/01/18http://www.sqlservercentral.com/redirect/articles/1663/There is nothing spectacular about using indexes per say. However, on many occasions I have come across a variety of SQL coders that never consider validating that the index they think they are using is efficient or even being used at all. We can all put indexes on the columns that we think will be required to satisfy individual queries, but how do we know if they will ever be used. You see, if the underlying table data is constructed, contains, or is ordered in a particular way, our indexes may never be used. One of the factors around the use of an index is its clustering factor and this is what this article is about.http://www.sqlservercentral.com/redirect/articles/1617/2004/12/23http://www.sqlservercentral.com/redirect/articles/1617/SQL Server depends heavily on good indexing to perform well. The optimizer requires relevant and intelligent indexing in order to do its job well. Author Andre Vigneau brings us a script that he uses to detect possible index problems in the database design as well as a number of hints to ensure that your database is performing optimally. http://www.sqlservercentral.com/articles/Indexing/boostyourperformancebadindexdetection/1625/2004/12/08http://www.sqlservercentral.com/articles/Indexing/boostyourperformancebadindexdetection/1625/Indexing performance has always been the Achilles' heel of SQL Server's full-text feature. Although the search performance itself is fast (very few customers complain about the search speed), users do complain (often loudly and frequently) about how slow the indexing process is–especially for large tables. Here's a few tips for you.http://www.sqlservercentral.com/redirect/articles/1538/2004/10/15http://www.sqlservercentral.com/redirect/articles/1538/A short piece on indexing using multiple columns. For those of you that have 10 single column indexes, read a little about why you might choose to index on multiple columns.http://www.sqlservercentral.com/redirect/articles/1511/2004/09/20http://www.sqlservercentral.com/redirect/articles/1511/What happens to SQL Server 2000 indexes over time? Fragmentation is one of the effects and Steve Jones looks at how you can work with and identity fragmentation. http://www.sqlservercentral.com/articles/Indexing/sqlserver2000indexing/1479/2004/09/15http://www.sqlservercentral.com/articles/Indexing/sqlserver2000indexing/1479/