Free Webcast: My Favorite Query Hints

One of my favorite uses for dynamic SQL is to use them to inject query hints.

Let’s say you’ve got a stored procedure with a bunch of parameters, including a @StartDate and @EndDate date range. And let’s say people usually use a small default date range, like the last week – but every now and then, they run reports across entire years or all history. When they do, they suffer from parameter sniffing, and the small-data query plan suffers from tiny memory grants, or key lookups, or nested loop joins, or whatever.

While you’re building the dynamic SQL, you can examine the incoming parameters, and based on their values, inject different query hints, like this:

This lets small date ranges get reusable plans, but if somebody decides to run a big report, they get a customized big-data query plan that doesn’t need to be reused for other folks.

That’s a simple example to get you started – and now, what query hints could you (or should you) use? I’m doing a free webcast with Idera to talk through my favorite query hints to use, and what they do.

Register here to join me on Tuesday, November 24 at 10AM Pacific, 1PM Eastern. If you can’t make it, Idera will email you a link to the recording afterwards. See you there!