... from following BAE's lead?
Patrick Index (Guest Editor)
... common volunteer DBA mistake - ineffective indexing. (For a background discussion about indexing I refer the reader to my paper Understanding Indexes and the many other excellent papers available ... process I use to identify and track index utilization statistics.
We have ... 1,800 tables upon which 392 indexes are defined totaling about 375 GB ...
The index internals has always been an interesting ... facts about non-clustered and clustered indexes based on these two statements:
In a unique non-clustered index, the clustered index key is added to the leaf level of the non ... structure
In a non-unique non-clustered index, the clustered index key is added to the leaf and non-leaf ...
Index management has always been an integral ... we do have a lot of articles and scripts dedicated to it. Well, here's my take on Index Management.
An index management strategy should be comprehensive enough to touch all aspects of indexes. A quick google gave me 1000's of link for index management; however none of them were ... complete enough to cater to all needs of index management.
After a bit of brainstorming I ...
Good indexes are the key to good performance in SQL Server and the key to creating good indexes is to understand what indexes are and how SQL Server uses them ... going to look at the very basics of what indexes are, what types exist in SQL and how they’re used.
What is an index?
An index is a structure within SQL that is used to quickly ... within a table. It can be useful to imaging an index at the back of a textbook when thinking about SQL indexes. They both serve the same purpose – to find ...
... run a query, the choice of whether to use an index or
not is decided by the query optimizer. Using appropriate indexes on tables so
that the query optimizer chooses ... few scenarios to
familiarize ourselves with the way indexes are used to retrieve data and also
try to figure out which index clustered or non clustered is good on each ... 9000
Let us say there is a clustered index on the member_number
SQL Server ...
Columnstore indexes are a feature that was introduced in SQ LServer 2016 ... comment in the discussion for this article.
Stairway to Columnstore Indexes - A good general introduction to the feature and learning ... Server 2016
Books Online - Columnstore Indexes Versioned Feature SummaryBooks Online - Columnstore Indexes DescribedSQL Server 2016 - In-Memory Columnstore IndexesColumnstore Indexes - part 43 - Thoughts on upcoming improvements ...
... up in my research was the idea of temporary indexes. I had never used them ... intrigued by the idea. I have dropped indexes before a large load and then recreated them, but these are indexes I planned to keep on the table indefinitely ... Friday:
Do you use temporary indexes?
By temporary, I mean indexes that you create for a process and then discard at the end of a process. These could be indexes that you use to supplement some type of data load, perhaps indexes used in staging tables. Or they could be indexes that are added to some OLTP tables for the processing ...
Using Index Intersection
First introduced in SQL Server version 7, Index Intersection gives you new options for creating indexes on tables to maximize performance
To start with I ... table from the Pubs database and explain the indexes that exist on that table, then look at how we would use indexes on that table for queries prior to Index Intersection, then we will look at the options that Index Intersection gives us.
A cautionary note ... so we can see how indexes were chosen without the benefit of Index Intersection. It is also well known and available ...
... every row of the table generates an entry in an index. Some indexes have fewer entries than the corresponding table has rows. These indexes are called filtered indexes; a feature that was introduced with SQL Server 2008 ... Resources section at the end of the article.
Filtering an Index
You filter an index the same way you filter a SELECT ... Sales.SalesOrderDetail')
AND name = 'FI_SpecialOfferID' )
DROP INDEX Sales.SalesOrderDetail.FI_SpecialOfferID ;
CREATE INDEX FI_SpecialOfferID
ON Sales.SalesOrderDetail (SpecialOfferID)