.NET Tips and Tricks

Blog archive

When Indexes Are No Help

In an earlier tip I discussed how indexes can speed up processing. It's only fair that I also talk about when indexes don't help.

First: Because indexes don't always help but do always increase the time it takes to update a table (though usually by a negligible amount) you should always run a test. Before applying an index to a table, take a moment to run a query that you think your index will help run faster -- ideally, some query used in production. Run the query twice and measure how long the query takes to complete the second time. Then, after applying your index, run the query twice again and compare the results of the second run.

If you don't get a difference that you can measure with your wrist watch, drop the index: SQL Server has decided that your index isn't helpful and is ignoring it. 

Which raises the question: Why is SQL Server ignoring the index? If your table has a lot of rows and SQL Server is ignoring your index, typically the reason is that your index isn't subdividing the table sufficiently to make your index worthwhile to use.  If using the index will still require SQL Server to look at "too many" rows, SQL Server will decide it's faster to just do a scan of the whole table to find the rows it needs.

How many is "too many" will vary from one version of SQL Server to another (and other factors, including the size of the table) but SQL Server typically requires an index to return a relatively small percentage of the total number of rows in the table. This number is often as small as 20 percent -- if SQL Server has to look at more than 20 percent of your rows, SQL Server will decide that it's faster just to look at all of the rows in the table. An index on a column holding gender information (where 51 percent are F and 49 percent are M) is probably going to be ignored, for example.

Posted by Peter Vogel on 04/03/2015


comments powered by Disqus

Featured

  • Vibe Coding Pioneer Advises 'Tight Leash' to Rein In AI BS

    Andrej Karpathy, who started the whole "vibe coding" thing to describe AI-drive software development, is now warning developers to keep tight control of their "new over-eager junior intern savant with encyclopedic knowledge of software, but who also bull* you all the time, has an over-abundance of courage and shows little to no taste for good code."

  • Microsoft Unifies Cloud and AI Development with .NET Aspire and AI Template Updates

    Microsoft announced the availability of .NET Aspire 9.2 and the second preview of the .NET AI Chat Web App template, highlighting new capabilities that bring cloud-native orchestration and AI integration closer together in the .NET ecosystem.

  • Hands On with Microsoft's AI Playground in a Windows App

    Microsoft reintroduced the AI Dev Gallery, a Windows application that serves as a comprehensive playground for AI development using .NET. It simplifies AI development with .NET through interactive samples, easy model downloads, and exportable source code.

  • Vibe Coding Advances in Visual Studio 2022 Previews

    Preview 3 features next edit suggestions -- or tab, tab, tab coding -- following previous previews of GPT-4o code completions and assorted Copilot enhancements.

  • Mastering Modern JavaScript

    Master the latest features of modern JavaScript in a hands-on workshop covering clean code techniques, async programming, templating, and full-stack development with Node.js.

Subscribe on YouTube

Upcoming Training Events

0 AM
Visual Studio Live! San Diego
September 8-12, 2025
Live! 360 Orlando
November 16-21, 2025
Cloud & Containers Live! Orlando
November 16-21, 2025
Data Platform Live! Orlando
November 16-21, 2025
Visual Studio Live! Orlando
November 16-21, 2025