Nonclustered indexes are awesome in SQL Server: they can get you huge performance gains.
But we can’t always create the perfect index for every query. And sometimes when SQL Server finds an index that isn’t quite perfect and decides to use it, it might make your query slower instead of faster.
If you need to add, remove, or replace hints from ad-hoc queries where you can’t change the code, plan guides can help. See a demo of removing a query hint from parameterized TSQL run from an application, and get tips on how to make your plan guides work in SQL Server.
The name of the “SQL Statistics \ Batch Requests/sec” counter is confusing: exactly what is a request? The request is the WHOLE batch. See a demo of this counter in this quick 5 minute video.
Batch mode was introduced as a way to help SQL Server process data from columnstore indexes faster. The whole idea with columnstore is that you pull big compressed sets of rows out for aggregation or other operations in big chunks.
Batch mode is a way that operators can work on a “batch” of up to 900 values at a time, instead of working on individual rows. Batch mode can reduce the overhead of metadata and make more efficient use of your CPUs.
Want to up your query tuning game? For a limited time, you can snag a free enrollment to the new course, Query Tuning with Hints & Optimizer Hotfixes.
Learn the pros and cons of using hints and optimizer hotfixes in SQL Server, and see how hints can give you insight into the optimization process. Sorry, this free giveaway has ended.
Perfmon counters are great for measuring workload, but choosing which counter to baseline can be confusing.
Find out if getting certified will help you land an entry level DBA job. If you do want to get certified, get Kendra’s tips on preparing for the exam in this 17 minute episode.
Copyright (c) 2025, Catalyze SQL, LLC; all rights reserved. Opinions expressed on this site are solely those of Kendra Little of Catalyze SQL, LLC. Content policy: Short excerpts of blog posts (3 sentences) may be republished, but longer excerpts and artwork cannot be shared without explicit permission.