Troubleshooting practice questions

A database administrator is troubleshooting a query that runs slowly only when executed by a specific application login. The same query runs quickly when executed by the administrator's login. The administrator suspects parameter sniffing. Which action should the administrator take to resolve the issue while minimizing the impact on other queries?

Trap 1: Create a plan guide for the query using sp_create_plan_guide.

A plan guide can force a specific query plan, but it requires determining the optimal plan and may not adapt to changing data distributions. It is more complex to implement and maintain than a query hint. While it can resolve parameter sniffing, it is not the simplest or most flexible solution for a single query. It also does not allow the plan to vary with parameter values.

Trap 2: Disable parameter sniffing at the instance level using trace flag…

Trace flag 4136 disables parameter sniffing for the entire instance. While this would resolve the issue for the specific query, it could negatively impact other queries that benefit from parameter sniffing. Disabling parameter sniffing globally is a heavy-handed approach and not recommended unless all queries on the instance suffer from parameter sniffing problems. A more targeted solution is preferred.

Trap 3: Update statistics on all tables involved in the query.

Updating statistics can improve cardinality estimates and potentially change the query plan, but it does not directly address parameter sniffing. The issue is that the cached plan was optimized for a specific parameter value, not that statistics are outdated. Updating statistics might temporarily help, but the problem could recur. It is not a targeted fix for parameter sniffing.

A
Create a plan guide for the query using sp_create_plan_guide.
Why it fails: A plan guide can force a specific query plan, but it requires determining the optimal plan and may not adapt to changing data distributions. It is more complex to implement and maintain than a query hint. While it can resolve parameter sniffing, it is not the simplest or most flexible solution for a single query. It also does not allow the plan to vary with parameter values.
B
Disable parameter sniffing at the instance level using trace flag 4136.
Why it fails: Trace flag 4136 disables parameter sniffing for the entire instance. While this would resolve the issue for the specific query, it could negatively impact other queries that benefit from parameter sniffing. Disabling parameter sniffing globally is a heavy-handed approach and not recommended unless all queries on the instance suffer from parameter sniffing problems. A more targeted solution is preferred.
C
Update statistics on all tables involved in the query.
Why it fails: Updating statistics can improve cardinality estimates and potentially change the query plan, but it does not directly address parameter sniffing. The issue is that the cached plan was optimized for a specific parameter value, not that statistics are outdated. Updating statistics might temporarily help, but the problem could recur. It is not a targeted fix for parameter sniffing.
D
Use the OPTION (RECOMPILE) query hint on the specific query.
The OPTION (RECOMPILE) hint forces the query optimizer to generate a new plan for each execution, using the current parameter values. This eliminates the parameter sniffing issue for that query without affecting other queries. It is a targeted solution that addresses the specific problematic query while leaving the rest of the workload unaffected. The trade-off is a slight increase in CPU for compilation.

Question 2hardmultiple choice

When troubleshooting a performance bottleneck in a visualization, which action is most effective if the data source is a large SQL view?

Trap 1: Changing the chart color

Changing chart colours alters only rendering properties, leaving the SQL view's row count and query execution plan untouched, so the bottleneck persists. It is tempting because colour adjustments genuinely help when the delay stems from visual rendering, such as excessive marks or complex custom shapes, rather than from data retrieval against a large view.

Trap 2: Increasing the number of filters

Additional filters narrow the result set but the view still executes its full underlying query before filtering applies, so cost is unchanged or higher. Filters suit interactive slicing of already-modelled data, not reducing the work a large SQL view performs at source.

Trap 3: Using a different visualization software

Swapping visualisation software does not address the SQL view's performance; the bottleneck persists because the query still scans the same data. It is tempting when the tool feels slow, but the effective fix is optimising the view or query. Different software would only be correct if the tool itself were the constraint.

A
Changing the chart color
Why it fails: Changing chart colours alters only rendering properties, leaving the SQL view's row count and query execution plan untouched, so the bottleneck persists. It is tempting because colour adjustments genuinely help when the delay stems from visual rendering, such as excessive marks or complex custom shapes, rather than from data retrieval against a large view.
B
Materializing the view
Materialising the view precomputes and stores the result set, so the visualisation queries a physical table instead of re-executing the underlying joins and aggregations on every refresh. This directly removes the repeated full-view scan that causes the bottleneck, satisfying the large SQL view constraint.
C
Increasing the number of filters
Why it fails: Additional filters narrow the result set but the view still executes its full underlying query before filtering applies, so cost is unchanged or higher. Filters suit interactive slicing of already-modelled data, not reducing the work a large SQL view performs at source.
D
Using a different visualization software
Why it fails: Swapping visualisation software does not address the SQL view's performance; the bottleneck persists because the query still scans the same data. It is tempting when the tool feels slow, but the effective fix is optimising the view or query. Different software would only be correct if the tool itself were the constraint.

Question 3mediummulti select

Which THREE of the following are benefits of implementing a standardized database schema migration process?

Trap 1: Faster hardware startup times

Hardware startup time is governed by firmware, boot devices and OS initialisation, not by schema versioning; a migration process alters database objects. It is tempting because migrations do run during deployments, so teams associate them with faster environment provisioning, but the correct benefits concern consistency, rollback and auditability of schema changes.

Trap 2: Automatic conversion of user passwords

Schema migrations change table, index and constraint definitions; password hashes sit in user data and are handled by authentication or identity tooling, so no migration step converts them. It is tempting because migrations often touch credential tables, but the correct benefits are version control, repeatability and reduced deployment risk.

A
Version control for schema changes
Storing each migration script in a repository gives schema changes the same versioning as application code, so any environment can be rebuilt at a known revision. This directly satisfies the stem's benefit of traceable, repeatable schema evolution rather than ad-hoc manual alterations.
B
Audit trails for all schema modifications
Recording who ran each migration, when, and what DDL it executed creates an immutable history of schema modifications. This satisfies the stem's benefit by providing the forensic evidence needed for change review, rollback decisions and regulatory audit of database structure alterations.
C
Faster hardware startup times
Why it fails: Hardware startup time is governed by firmware, boot devices and OS initialisation, not by schema versioning; a migration process alters database objects. It is tempting because migrations do run during deployments, so teams associate them with faster environment provisioning, but the correct benefits concern consistency, rollback and auditability of schema changes.
D
Automatic conversion of user passwords
Why it fails: Schema migrations change table, index and constraint definitions; password hashes sit in user data and are handled by authentication or identity tooling, so no migration step converts them. It is tempting because migrations often touch credential tables, but the correct benefits are version control, repeatability and reduced deployment risk.
E
Consistency of database structure across environments
Applying identical, ordered migration scripts to development, test and production removes drift, so every environment converges on the same schema revision. This satisfies the stem's benefit directly: structural parity across environments, eliminating the configuration divergence that manual changes introduce.

Question 4hardmultiple choice

A database administrator is troubleshooting a query that occasionally runs slowly. The query uses a parameterized stored procedure. The execution plan shows that sometimes the query uses an index seek and sometimes a clustered index scan. The DBA suspects parameter sniffing. Which action should the DBA take to mitigate the issue while preserving the ability to use the optimal plan for each execution?

Trap 1: Enable Trace Flag 2371 at the server level.

Trace Flag 2371 changes the threshold at which statistics are updated automatically, which can affect plan quality but does not directly address parameter sniffing. It is unrelated to the plan reuse behavior that causes parameter sniffing and does not ensure per-execution optimal plans.

Trap 2: Use the OPTIMIZE FOR UNKNOWN query hint.

OPTIMIZE FOR UNKNOWN creates a plan based on average selectivity rather than the actual parameter values, which can reduce the impact of parameter sniffing but may not produce the optimal plan for specific executions. It sacrifices plan quality for stability and does not preserve the ability to use the best plan for each execution.

Trap 3: Create a plan guide to force the index seek plan.

A plan guide forces a specific plan, which would eliminate the variability but also prevent the optimizer from choosing a scan when it might be better. This does not preserve the ability to use the optimal plan for each execution and can degrade performance for parameter values that benefit from a scan.

A
Enable Trace Flag 2371 at the server level.
Why it fails: Trace Flag 2371 changes the threshold at which statistics are updated automatically, which can affect plan quality but does not directly address parameter sniffing. It is unrelated to the plan reuse behavior that causes parameter sniffing and does not ensure per-execution optimal plans.
B
Use the OPTIMIZE FOR UNKNOWN query hint.
Why it fails: OPTIMIZE FOR UNKNOWN creates a plan based on average selectivity rather than the actual parameter values, which can reduce the impact of parameter sniffing but may not produce the optimal plan for specific executions. It sacrifices plan quality for stability and does not preserve the ability to use the best plan for each execution.
C
Create a plan guide to force the index seek plan.
Why it fails: A plan guide forces a specific plan, which would eliminate the variability but also prevent the optimizer from choosing a scan when it might be better. This does not preserve the ability to use the optimal plan for each execution and can degrade performance for parameter values that benefit from a scan.
D
Add OPTION (RECOMPILE) to the query inside the stored procedure.
OPTION (RECOMPILE) forces a new plan to be generated for each execution, allowing the optimizer to use the actual parameter values and choose an optimal plan per execution. This mitigates parameter sniffing by avoiding reuse of a plan optimized for a different parameter value, while still allowing seeks or scans as appropriate.

Question 5hardmultiple choice

A DBA is troubleshooting a slow query. The execution plan indicates an 'Implicit Conversion' is occurring, which prevents the use of an index seek. What is the most likely cause?

Trap 1: The database collation is incorrect

Collation mismatches cause implicit conversion between string columns in joins or comparisons, but the stem points to a datatype mismatch, not a sort-order conflict. It is tempting because aligning collations is correct when joining columns that differ in collation and the plan shows a conversion.

Trap 2: The table is not partitioned

Partitioning affects partition elimination and scan range, not datatype matching between column and literal; implicit conversion stems from mismatched types. It is tempting because partitioning large tables is the right remedy when queries scan many partitions that pruning cannot exclude.

Trap 3: The statistics are outdated

Outdated statistics cause bad cardinality estimates and poor plan choices, but they do not introduce implicit conversion between mismatched datatypes. It is tempting because updating statistics is the correct fix when the optimiser misestimates row counts and selects a nested loop over a hash join.

A
The database collation is incorrect
Why it fails: Collation mismatches cause implicit conversion between string columns in joins or comparisons, but the stem points to a datatype mismatch, not a sort-order conflict. It is tempting because aligning collations is correct when joining columns that differ in collation and the plan shows a conversion.
B
The table is not partitioned
Why it fails: Partitioning affects partition elimination and scan range, not datatype matching between column and literal; implicit conversion stems from mismatched types. It is tempting because partitioning large tables is the right remedy when queries scan many partitions that pruning cannot exclude.
C
Data type mismatch between column and parameter
When the parameter's data type differs from the column's, the optimiser applies an implicit conversion to the column side, making the predicate non-sargable and forcing a scan instead of an index seek. This satisfies the execution plan's implicit conversion warning.
D
The statistics are outdated
Why it fails: Outdated statistics cause bad cardinality estimates and poor plan choices, but they do not introduce implicit conversion between mismatched datatypes. It is tempting because updating statistics is the correct fix when the optimiser misestimates row counts and selects a nested loop over a hash join.