Indexing

  1. Home
  2. Indexing
  3. (Page 8)

Does Creating an Indexed View Require Exclusive Locks on an Underlying Table?

An interesting question came up in our SQL Server Performance Tuning course in Chicago: when creating an indexed view, does it require an exclusive lock on the underlying table or tables? Let’s test it out with a simple indexed view run against a non-production environment. (AKA, a VM on my laptop running SQL Server 2014.)…
Read More

Filtered Indexes and IS NOT NULL

Indexing, SQL Server
22 Comments
Filtered indexes can be tricky. Just getting your queries to use the filtered index can be a real pain. Parameterization may mean it’s “unsafe” to use the filtered index. The optimizer might want the filtered column in your key or include column list when it doesn’t seem like it should have to be there. The…
Read More

Finding Tables with Nonclustered Primary Keys and no Clustered Index

Indexing, SQL Server
30 Comments
i’ve seen this happen Especially if you’ve just inherited a database, or started using a vendor application. This can also be the result of inexperienced developers having free reign over index design. Unless you’re running regular health checks on your indexes with something like our sp_BlitzIndex® tool, you might not catch immediately that you have…
Read More

Indexing for GROUP BY

Indexing, SQL Server
9 Comments
It’s not glamorous And on your list of things that aren’t going fast enough, it’s probably pretty low. But you can get some pretty dramatic gains from indexes that cover columns you’re performing aggregations on. We’ll take a quick walk down demo lane in a moment, using the Stack Overflow database. Query outta nowhere! Transact-SQL…
Read More

Indexing for Windowing Functions

Indexing, SQL Server, T-SQL
6 Comments
Hooray Windowing Functions They do stuff that used to be hard to do, or took weird self-joins or correlated sub-queries with triangular joins to accomplish. That’s when there’s a standalone inequality predicate, usually for getting a running total. With Windowing Functions, a lot of the code complexity and inefficiency is taken out of the picture,…
Read More

Do Foreign Keys Matter for Insert Speed

Indexing
52 Comments
Do you have the key? Do foreign keys hurt the performance of inserts? Okay, we all know that foreign keys do require some work, but the crazy people of the internet have wildly varying guesses as to just how much work is involved. Estimates varied from “barely any” to “as much as 50% slower”. I…
Read More

Using “OR” and “IN” with SQL Server’s Filtered Indexes

Indexing, SQL Server
3 Comments
You can’t do everything with filtered indexes in SQL Server. For instance, you can’t create the following index: CREATE INDEX IX_Votes_filter ON dbo.Votes (PostId) WHERE (VoteTypeId = 1 OR VoteTypeId = 2); GO 123 CREATE INDEX IX_Votes_filter ON dbo.Votes (PostId)     WHERE (VoteTypeId = 1 OR VoteTypeId = 2);GO If you try, you’ll get the error message:…
Read More

Testing ALTER INDEX REBUILD with WAIT_AT_LOW_PRIORITY in SQL Server 2014

One of the blocking scenarios I find most interesting is related to online index rebuilds. Index rebuilds are only mostly online. In order to complete they need a very high level of lock: a schema modification lock (SCH-M). Here’s one way this can become a big problem: An online index rebuild starts against a large index A…
Read More

How to Configure Ola Hallengren’s Maintenance Scripts

If you’re a production database administrator responsible for backups, corruption checking, and index maintenance on SQL Server, try Ola Hallengren’s free database maintenance scripts. They’re better than yours (trust me), and they give you more flexibility than built-in maintenance plans. However, the index maintenance defaults aren’t good for everyone. Here’s how they ship: Transact-SQL CREATE PROCEDURE…
Read More

Who’s Allowed to Add Indexes to ISV Apps?

Performance tuning of independent software vendor (ISV) databases is a little tricky. To understand who does what, let’s think through all of the work required with building and hosting a database application: Who adds indexes and tunes queries? Typically the two parties involved – the vendor and the customer – start at opposite ends of…
Read More
Menu