Query Tuning
Using IIF for simple conditional expressions
Compact CASE alternatives — and when CASE (or computed columns) is clearer.
/*
================================================================================
Purpose: Identifies the top 20 statements consuming the most CPU,
physical/logical reads, or having the longest elapsed durations.
Provides: Execution counts, total/avg CPU, logical reads, duration,
query text, and associated execution plan XML.
Importance: Pinpoints the "heavy hitters" in the workload that offer the
highest return on investment for query tuning and optimization.
Interpretation: Focus on queries with high "Avg_Logical_Reads" or "Avg_CPU_ms".
Correlate with wait statistics to understand the wait type.
Action: Copy the query text from the top-ranked queries and analyze the execution plan in SSMS. Look for: (1) missing index warnings (green text), (2) table scans on large tables, (3) implicit conversions, (4) key lookups. For queries with high Avg_CPU_ms, create recommended indexes and re-evaluate. For large batches, consider breaking into smaller operations or optimizing the worst-performing steps.
Criticality: High
Author: Ravi Sharma
================================================================================
*/
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET NOCOUNT ON;
SELECT TOP (20)
qs.execution_count AS [Execution_Count],
qs.total_worker_time / 1000 AS [Total_CPU_ms],
(qs.total_worker_time / qs.execution_count) / 1000 AS [Avg_CPU_ms],
qs.total_logical_reads AS [Total_Logical_Reads],
qs.total_logical_reads / qs.execution_count AS [Avg_Logical_Reads],
qs.total_elapsed_time / 1000 AS [Total_Duration_ms],
(qs.total_elapsed_time / qs.execution_count) / 1000 AS [Avg_Duration_ms],
-- Extract the specific statement from the batch
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS [Query_Text],
qp.query_plan AS [Query_Plan_XML],
CAST('Displays worst performing queries from SQL Server plan cache. ' +
'Threshold: Queries with Avg_Logical_Reads > 10,000 or Avg_CPU_ms > 500ms are candidates for optimization. ' +
'Recommendation: Open Query_Plan_XML to locate clustered index scans or key lookups. Add missing indexes, reduce sorting operations, or verify parameter statistics.'
AS VARCHAR(1000)) AS [Metric_Context]
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY [Total_CPU_ms] DESC; -- Can change to total_logical_reads or total_elapsed_time depending on diagnostic focus
Review the full script and confirm the connected SQL Server instance before execution. The run instructions and output guide appear later in this article.
Dummy guide: IIF(condition, true_value, false_value) is syntactic sugar over a simple CASE.
Reach for IIF when you need a one-line flag or display value in a select list. Prefer explicit
CASE when there are more than two branches, nested conditions, or when the expression is part
of a persisted computed column that teammates must maintain.
Remember IIF still evaluates like CASE for type precedence — mismatched types will coerce,
sometimes in surprising ways. Check the output type in the plan or with SQL_VARIANT_PROPERTY
when mixing numerics and strings.
Why conditional expression tuning deserves a method
The central principle is that IIF compiles as a CASE expression, so the real question is how conditional logic affects estimates, types, and access paths. A single metric, recommendation, or successful command is not enough. The decision has to connect an observed symptom to a plausible cause, a controlled change, and a result that can be verified under the workload that matters.
Evidence to collect
Use evidence from the same time window whenever possible:
- implicit conversion and result data type
- expression placement in predicates
- estimated versus actual rows
- repeated scalar evaluation
A practical workflow
- Expand the expression mentally as CASE.
- Check type precedence and NULL behavior.
- Move functions away from indexed columns when possible.
- Measure the final query rather than the expression alone.
Common mistakes
Avoid these shortcuts:
- blaming syntax without reading the plan
- changing NULL semantics
- assuming a shorter expression executes faster
Verification and completion
Define completion before making the change. Confirm correctness first, then compare latency, throughput, reads, CPU, waits, storage behavior, or recovery time as appropriate for Using IIF for simple conditional expressions. Test negative and boundary cases, not only the path expected to succeed. Watch the system long enough to cover the relevant workload pattern.
Download and run the diagnostic
Top resource-consuming queries
Download top_resource_queries.sqlBefore you run it
- Review every statement and confirm the connection points to the intended SQL Server database server.
- Run through a non-production or read-only diagnostic account first when possible.
- Required access: VIEW SERVER STATE.
- Save the output with the server name, database name, collection time, and workload window so another engineer can reproduce the analysis.
What it does
Surfaces expensive cached statements, including queries where conditional expressions contribute to repeated scalar work or non-searchable predicates. The script is diagnostic and does not apply an automatic remediation. Result values still need workload context, a second supporting signal, and a before-and-after comparison.
How to read the output values
Find the query by Query_Text, then inspect its actual execution plan. The output ranks workload cost but does not prove IIF is the cause. Compare the CASE-equivalent rewrite for result types, NULL behavior, estimates, reads, and CPU.