Convenience
Like having a built-in type to make dynamic SQL more easily managed, it would also be nice to have some mechanism to manage dynamic searches.
Of course, what I mean by dynamic searches is when you have a variety of parameters that users can potentially search on, with none or few of them being required.
Right now, of course, your first best option is writing parameterized dynamic SQL that generates a where clause specific to the set of non-null parameters being sent to the procedure.
Your second best option is to just slap a statement-level recompile hint on things.
There are of course pros and cons to each.
Dynamic SQL vs Option Recompile
For dynamic SQL, the main cons are:
- Relying on developers to write it safely
- Detachment from stored procedure (harder to identify in query store/plan cache)
- Potentially managing permissions
- Handling increasingly complex scenarios (lists of columns, custom ordering, optional joins, etc.)
The main pros are:
- Execution plan reusability
- Transparent optimizer predicates
- Consistent performance for indexed searches
- Incredible flexibility
For OPTION(RECOMPILE), there’s really not a whole lot of up and down.
- Pro: Really simple to slap on the end of a query
- Con: Query constantly compiles a new plan (no plan reuse, but no cache bloat either)
As long as you have query store enabled, tracking the plans over time is available to you.
I don’t see queries that take a long time to compile all too frequently, but it’s worth keeping an eye on compilation time if you’re trying to balance these options locally.
Trust me on this one.
Managing Performance
The most difficult part, once you’ve made a choice, is figuring out indexing. Assuming you want searches to be fast, this is important.
Assuming you have just one table, and just 6 optional parameters, there are over 700 potential index key column combinations to consider.
Once you consider includes, and throwing columns into the mix for sorting, the mind begins to boggle.
In many cases, nonclustered column store indexes make this a lot easier. Row store indexes are built in a way that may make having many similar ones necessary.
Key column order makes a lot of difference when speeding up searches generally. An index on columns (a, b, c) will certainly make a variety of searches fast, so long as column a is part of the predicates.
Searches that do not filter on column a will have to scan the full index. Now you’re stuck trying to figure out what the most common or important searches are.
In this video, I talk about solving indexing problems with this type of query.
Thanks for reading (and watching)!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
Which post or posts are you talking about from Kimberly?
I presume you’re talking about these:
https://www.sqlskills.com/blogs/kimberly/category/dynamic-string-execution/
1. Clearing the cache – are there other options?
2. Using the OPTION (RECOMPILE) option for a statement
3. EXEC and sp_executesql – how are they different?
4. Looking for security vulnerabilities in database code
5. “EXECUTE AS” and an important update your DDL Triggers (for auditing or prevention)
6. Little Bobby Tables, SQL Injection and EXECUTE AS
I’m hoping the list is fairly inclusive.