In this post, we continue with another beginner’s blog of database features that may be unknown to many. Let’s take a look at filtered indexes. Many database administrators are fully aware of the power of indexes and know how to create them. However, I find that some have yet to dive into fully optimizing their indexes and taking advantage of what filtered indexes has to offer. What is a filtered index? Simply it’s an index with a where clause. It is an optimized non clustered index that can be narrowed down in scope to better fit a subset of data. Example being date ranges, years, non NULLs or specific product…
-
-
One of the easiest things to fix when performance tuning queries are Key Lookups or RID Lookups. The key lookup operator occurs when the query optimizer performs an index seek against a specific table and that index does not have all of the columns needed to fulfill the result set. SQL Server is forced to go back to the clustered index using the Primary Key and retrieve the remaining columns it needs to satisfy the request. A RID lookup is the same operation but is performed on a table with no clustered index, otherwise known as a heap. It uses a row id instead of a primary key to do…
-
Microsoft has long given us DBA tools and suggestions to help tune databases, to fix queries with indexes, and many times they were of questionable merit. We have all seen the dreaded dta_index names in a database and rolled our eyes knowing that sometimes these indexes are not what is needed. While the Database Tuning Advisor, has gotten better over time it is still not a tool more senior DBAs use. Microsoft made big steps in helping DBAs with performance with the introduction of Query Store in both Azure SQL Database and SQL Server 2016. Query Store collects execution and run-time performance information. Microsoft has taken an additional leap with…
-
Awhile back, if you are on Twitter, you can probably recall my ranting about the 949 indexes I was reviewing. The process was to determine duplicate indexes and consolidate them or discard the unneeded ones. My ranting was not about the duplicates per se it was about the index names. It only takes a second to name an object with some name that tells what the thing is. Below I will show you some examples and give you an easy script that will help you generate your index names. Taking a little time to name things appropriately can go a long way, it can not only be time saving but…
