Tag: SQL Development

Databases, DP-800, Microsoft Certification, SQL August 1, 2026

Identify and resolve query performance issues, including blocking and deadlocks – Part 2 (DP-800 Exam Prep)

This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Secure, optimize, and deploy database solutions (35–40%)
   --> Optimize database performance
      --> Identify and resolve query performance issues, including blocking and deadlocks

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

In Part 1, we discussed locking, blocking, deadlocks, transaction isolation levels, and concurrency controls. In this section, we will focus on the tools and techniques that SQL developers use to diagnose and resolve performance issues. These tools are frequently referenced throughout Microsoft documentation and are highly relevant for the DP-800 certification exam.

After completing this article, you should be able to:

Interpret query execution plans.
Use Query Store to analyze historical query performance.
Leverage Dynamic Management Views (DMVs) to monitor database activity.
Capture performance issues with Extended Events.
Interpret wait statistics.
Optimize indexes.
Address parameter sniffing issues.
Maintain statistics.
Follow a structured performance tuning methodology.
Recognize common DP-800 exam scenarios.

A Structured Performance Tuning Process

Performance tuning should follow a systematic approach rather than relying on guesswork.

A recommended workflow is:

Identify the slow query.
Capture the execution plan.
Examine wait statistics.
Check index usage.
Review Query Store history.
Examine DMVs.
Optimize the query or indexes.
Test the improvement.
Monitor ongoing performance.

Following a structured process helps avoid unnecessary changes that may introduce new problems.

Understanding Query Execution Plans

An execution plan is a roadmap that shows how SQL Server processes a query.

It displays:

Order of operations
Index usage
Join methods
Estimated and actual row counts
Operator costs
Memory grants
Parallelism decisions

Execution plans help identify why a query is slow.

Estimated vs. Actual Execution Plans

Estimated Execution Plan

Generated before execution.

Advantages:

No query execution required
Useful during development
Quick to generate

Limitations:

Uses estimated statistics
Does not show runtime behavior

Actual Execution Plan

Generated while the query executes.

Advantages:

Shows actual row counts
Displays actual execution times
More accurate for troubleshooting

Requires executing the query.

Reading Execution Plans

Several operators frequently appear in execution plans.

Index Seek

The optimizer directly locates matching rows.

Characteristics:

Fast
Efficient
Low I/O
Preferred operation

Index Scan

Reads most or all index pages.

May be acceptable when:

Returning many rows
Small tables

May indicate missing indexes if unexpected.

Table Scan

Reads the entire table.

Usually indicates:

Missing indexes
Poor filtering
Small tables

Large table scans often create significant I/O.

Nested Loop Join

Efficient when one input is small.

Ideal for:

Primary key lookups
Highly selective joins

Merge Join

Efficient when both inputs are sorted.

Often used with:

Clustered indexes
Ordered datasets

Hash Match

Builds hash tables.

Common for:

Large joins
Large aggregations

Requires considerable memory.

Cost Percentages

Execution plans assign estimated costs.

Example:

Operator	Cost
Index Seek	5%
Nested Loop	10%
Sort	35%
Hash Match	50%

These percentages are estimates, not actual elapsed time.

Focus on expensive operators as starting points for optimization.

Warning Indicators

Execution plans may include warnings such as:

Missing indexes
Implicit conversions
Hash spills
Sort spills
Excessive memory grants
Parallelism skew

Warnings deserve investigation but should not automatically be implemented without testing.

Query Store

Query Store records query history over time.

It stores:

Query text
Execution plans
Runtime statistics
Resource consumption
Plan history
Wait statistics (supported versions)

Unlike the plan cache, Query Store persists across restarts.

Benefits of Query Store

Query Store enables developers to:

Identify regressed queries
Compare historical execution plans
Detect parameter-sensitive plan changes
Force a known good execution plan
Analyze workload trends

Query Store is one of the most valuable performance troubleshooting features available in SQL Server and Azure SQL.

Common Query Store Reports

Useful reports include:

Top Resource Consuming Queries
Queries with High Duration
Queries with High CPU
Query Wait Statistics
Regressed Queries
Plan Comparison

These reports quickly identify problematic queries.

Forcing Execution Plans

Occasionally, SQL Server chooses a poor plan.

Query Store allows administrators to force a previous stable plan.

Advantages:

Quick recovery
No code modification
Useful after upgrades
Helps address parameter-sensitive regressions

Forced plans should be monitored to ensure they remain optimal as data changes.

Dynamic Management Views (DMVs)

DMVs provide real-time information about SQL Server activity.

They are essential for performance troubleshooting.

Examples include:

Active requests
Sessions
Index usage
Missing indexes
Wait statistics
Cached execution plans
Memory usage

Frequently Used DMVs

sys.dm_exec_requests

Displays currently executing requests.

Useful columns include:

session_id
status
wait_type
blocking_session_id
cpu_time
logical_reads

Example:

			
SELECT
    session_id,
    status,
    cpu_time,
    logical_reads,
    blocking_session_id
FROM sys.dm_exec_requests;

		

sys.dm_exec_sessions

Displays connected sessions.

Useful for identifying:

Login information
Application names
Client connections
Session status

sys.dm_exec_query_stats

Provides cumulative statistics.

Includes:

Execution count
CPU usage
Logical reads
Elapsed time

Excellent for identifying expensive queries.

sys.dm_db_index_usage_stats

Shows index usage.

Useful for identifying:

Unused indexes
Frequently used indexes
Missing optimization opportunities

sys.dm_db_missing_index_details

Recommends potential indexes.

Important:

These recommendations should always be evaluated carefully rather than implemented automatically.

Extended Events

Extended Events is SQL Server’s modern monitoring framework.

It replaces SQL Trace and SQL Server Profiler for most workloads.

Advantages:

Lightweight
Highly configurable
Lower overhead
Suitable for production environments

Common Extended Event Sessions

Extended Events can capture:

Deadlocks
Blocking
Long-running queries
Login failures
Wait statistics
Query execution
Memory grants

The built-in system_health session captures many important diagnostic events by default.

Wait Statistics

Wait statistics show where SQL Server spends time waiting.

Rather than measuring CPU usage alone, waits reveal resource bottlenecks.

Common categories include:

CPU
Disk I/O
Memory
Locks
Network
Parallelism

Common Wait Types

LCK_M_*

Lock waits.

Indicate blocking.

Possible causes:

Long transactions
Lock contention
Missing indexes

PAGEIOLATCH_*

Waiting for data pages from disk.

May indicate:

Slow storage
Large scans
Insufficient memory

CXPACKET / CXCONSUMER

Related to parallel query execution.

May indicate:

Large parallel queries
Uneven workload distribution

Not always a problem.

WRITELOG

Waiting for transaction log writes.

May indicate:

Heavy write activity
Slow storage subsystem

SOS_SCHEDULER_YIELD

CPU scheduling wait.

May indicate CPU pressure.

Index Optimization

Indexes greatly influence performance.

Well-designed indexes reduce:

Logical reads
CPU usage
Query duration
Blocking

Poor indexes increase maintenance costs.

Clustered vs. Nonclustered Indexes

Clustered Index

Determines physical row order.
One per table.
Ideal for range queries.

Nonclustered Index

Separate structure.
Many allowed.
Ideal for selective lookups.

Covering Indexes

A covering index contains all columns required by a query.

Benefits include:

Eliminates key lookups
Reduces logical reads
Improves performance

Example:

			
CREATE INDEX IX_Orders_Customer
ON Sales.Orders(CustomerID)
INCLUDE(OrderDate, TotalAmount);

Index Fragmentation

Fragmented indexes reduce performance.

Maintenance options include:

Fragmentation	Recommended Action
Less than 5%	No action
5–30%	Reorganize
Greater than 30%	Rebuild

Regular maintenance improves read performance.

Parameter Sniffing

SQL Server caches execution plans.

Sometimes the first parameter value generates a plan that performs poorly for later executions.

Example:

A plan optimized for one customer with only a few orders may perform poorly when reused for a customer with millions of orders.

Potential mitigation techniques include:

OPTION (RECOMPILE)
OPTIMIZE FOR
Local variables (used judiciously)
Query Store plan forcing
Query redesign

Understanding parameter sniffing is an important DP-800 objective.

Statistics Maintenance

Statistics help SQL Server estimate row counts.

Outdated statistics lead to:

Poor cardinality estimates
Incorrect join selection
Poor execution plans

Maintenance options include:

UPDATE STATISTICS Sales.Orders;

EXEC sp_updatestats;

Automatic statistics updates are generally sufficient for many workloads, but large or highly volatile databases may benefit from scheduled maintenance.

Intelligent Performance Features

Modern SQL Server and Azure SQL include intelligent features such as:

Automatic tuning
Automatic plan correction
Automatic index recommendations
Intelligent Insights (Azure SQL)
Automatic statistics updates

These features assist administrators but should complement—not replace—performance analysis and testing.

Common Performance Optimization Techniques

When troubleshooting slow queries:

Retrieve only required columns.
Avoid SELECT *.
Use appropriate indexes.
Write SARGable predicates.
Keep transactions short.
Avoid cursors when set-based operations are possible.
Maintain indexes and statistics.
Reduce unnecessary sorting.
Limit large result sets.
Batch large modifications.

Performance Troubleshooting Checklist

When investigating a slow query:

☐ Is an appropriate index available?

☐ Is SQL Server performing an Index Seek or Table Scan?

☐ Are statistics current?

☐ Are implicit conversions occurring?

☐ Is blocking present?

☐ Is parameter sniffing affecting performance?

☐ Are waits indicating CPU, I/O, or locking problems?

☐ Does Query Store show a regression?

☐ Can the query be rewritten more efficiently?

☐ Has the improvement been tested before deployment?

Real-World Scenario 1: Missing Index

A customer search query takes 18 seconds.

Execution plan shows:

Table Scan
Missing Index recommendation

Resolution:

Create an appropriate nonclustered index and validate the improvement with the actual execution plan.

Real-World Scenario 2: Parameter-Sensitive Plan

A stored procedure runs quickly for most customers but very slowly for one large customer.

Investigation shows a cached plan optimized for a small data set.

Resolution:

Evaluate parameter-sensitive plan optimization techniques such as OPTION (RECOMPILE), OPTIMIZE FOR, Query Store plan forcing, or redesigning the query, depending on the workload.

Real-World Scenario 3: Blocking Chain

Users report intermittent timeouts.

DMVs reveal:

Session 52 blocks Session 63.

Session 63 blocks Session 81.

Session 81 blocks Session 96.

Root cause:

A long-running transaction remained open while waiting for application logic.

Resolution:

Reduce transaction duration and ensure commits occur as quickly as possible.

DP-800 Exam Tips

Understand the differences between Estimated and Actual execution plans.
Know when Index Seeks are preferred over Table Scans.
Be familiar with Query Store, including plan history, runtime statistics, and plan forcing.
Recognize the most commonly used DMVs for monitoring active requests, sessions, query performance, and index usage.
Understand how Extended Events have largely replaced SQL Trace and SQL Server Profiler for production monitoring.
Learn to interpret common wait types such as LCK_M_*, PAGEIOLATCH_*, CXPACKET, WRITELOG, and SOS_SCHEDULER_YIELD.
Understand the role of statistics, parameter sniffing, covering indexes, and fragmentation in query performance.
Remember that Microsoft recommends making tuning decisions based on evidence from execution plans and monitoring tools, rather than assumptions.

Key Takeaways

Performance tuning is an iterative process that combines analysis, measurement, and optimization. SQL Server provides a rich set of diagnostic tools—including execution plans, Query Store, DMVs, Extended Events, wait statistics, and index analysis—that help developers identify and resolve bottlenecks. For the DP-800 exam, you should be comfortable selecting the appropriate diagnostic tool, interpreting its results, and recommending effective solutions to improve query performance while maintaining scalability and concurrency.

Go to the DP-800 Exam Prep Hub main page

AI, AI Strategy, Data Development, DP-800, Microsoft Certification, SQL August 1, 2026

Create and configure GitHub Copilot instruction files (DP-800 Exam Prep)

This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Design and develop database solutions (35–40%)
   --> Design and implement SQL solutions by using AI-assisted tools
      --> Create and configure GitHub Copilot instruction files

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

GitHub Copilot is an AI-powered coding assistant that generates code, explains existing code, creates documentation, and assists with debugging. While Copilot is powerful out of the box, organizations often need the AI to follow company-specific standards instead of producing generic code.

GitHub Copilot instruction files provide persistent guidance to Copilot. Rather than repeatedly telling Copilot the same preferences during every chat session, developers can store instructions in version-controlled files inside the repository. They help ensure that AI-generated code follows an organization’s coding standards, security requirements, architectural patterns, naming conventions, and SQL development best practices. Candidates should understand not only how to create these files, but also how they influence Copilot’s responses.

Instruction files improve:

Consistency
Security
Coding standards
SQL development practices
Documentation quality
Team collaboration
AI response quality

For the DP-800 exam, understand:

What instruction files are
Where they are stored
What types of instructions they contain
How they affect Copilot responses
Best practices for SQL development

Why Use Instruction Files?

Without instruction files:

			
Developer:
Create a stored procedure.
Copilot:
Creates one using SELECT * and no error handling.

Next time:

			
Developer:
Remember to avoid SELECT *
Use TRY...CATCH
Use PascalCase
Include comments
Use parameters

		

The developer must continually repeat instructions.

With instruction files:

			
Repository contains instructions.
Copilot automatically follows them.

Every developer receives consistent AI assistance.

What Are GitHub Copilot Instruction Files?

Instruction files are Markdown files that contain natural-language guidance for Copilot.

They describe:

Coding style
Naming conventions
Architecture
Security practices
SQL standards
Documentation requirements
Testing expectations

Instead of writing prompts repeatedly, the repository permanently stores the instructions.

Benefits

Instruction files provide:

Consistency

Every developer receives similar AI suggestions.

Faster Development

Less prompt engineering.

Developers spend less time explaining requirements.

Higher Code Quality

Instructions encourage:

Proper formatting
Secure coding
Error handling
Documentation

Better Security

Organizations can require Copilot to:

Parameterize SQL
Avoid dynamic SQL
Validate input
Follow least privilege

Team Standards

New developers immediately receive guidance that matches experienced developers.

Repository-Level Instructions

Instruction files are stored with the project.

Example:

			
Repository
├── .github
│      copilot-instructions.md
│
├── Database
├── Procedures
├── Functions
└── Tables

		

The instructions become part of source control.

Everyone cloning the repository receives them.

What Can Instruction Files Contain?

Common guidance includes:

Coding conventions

Example

			
Use PascalCase for object names.
Avoid abbreviations.
Use descriptive variable names.

SQL Standards

Example

			
Never use SELECT *
Always qualify object names.
Always use schema prefixes.
Prefer explicit column lists.

Error Handling

Example

			
Always wrap stored procedures inside TRY...CATCH.
Log errors before rethrowing.

Documentation

Example

			
Document all procedures.
Include parameter descriptions.
Explain business rules.

Performance

Example

			
Avoid cursors.
Prefer set-based operations.
Use appropriate indexing.
Avoid unnecessary temp tables.

Security

Example

			
Always use parameterized queries.
Never concatenate SQL strings.
Validate inputs.
Follow least privilege.

SQL Example

Instruction:

			
Use schema dbo.
Always include SET NOCOUNT ON.
Use TRY...CATCH.
Document parameters.
Never use SELECT *.

		

Prompt:

Create a procedure to retrieve customers.

Generated procedure might include:

			
CREATE PROCEDURE dbo.GetCustomers
(
    @Country NVARCHAR(50)
)
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
SELECT
CustomerID,
CustomerName,
Country
FROM dbo.Customers
WHERE Country=@Country;
END TRY
BEGIN CATCH
THROW;
END CATCH
END;

		

The instructions influence the generated output.

SQL Development Standards Commonly Included

Organizations commonly include instructions such as:

Use schema-qualified names
Use explicit column lists
Avoid SELECT *
Use transactions appropriately
Always parameterize SQL
Avoid SQL injection
Use TRY…CATCH
Return meaningful errors
Comment complex logic
Format SQL consistently

Database Naming Standards

Instruction files frequently define naming conventions.

Example

Tables

			
SalesOrders
Customers
Invoices

Procedures

			
usp_GetOrders
usp_InsertCustomer

Views

vwCustomerSales

Functions

fnCalculateTax

Documentation Standards

Instructions often require:

Every procedure includes:

Purpose
Parameters
Return values
Modification history

Example

			
Purpose:
Returns active customers.
Parameters:
@Country
Returns:
Customer list.

		

Security Guidance

Instruction files often include security rules.

Examples:

Do not:

SELECT *

Do not:

EXEC(@SQL)

Do:

sp_executesql

Do:

Parameterized queries

Require:

Least privilege
Input validation
Data masking awareness
Sensitive data handling

Performance Guidance

Example instructions:

Prefer:

Set-based operations
Appropriate indexes
EXISTS
Window functions

Avoid:

Nested cursors
RBAR processing
Unnecessary DISTINCT
Scalar UDFs inside large queries

AI Prompt Consistency

Instead of writing:

			
Generate a procedure.
Use TRY...CATCH.
No SELECT *
Include comments.
Use PascalCase.

		

Simply write:

Generate a procedure.

Copilot automatically follows repository guidance.

Version Control Benefits

Instruction files are version controlled.

Benefits include:

Change history
Code reviews
Branch support
Rollback capability
Team collaboration

Team Collaboration

Instruction files help ensure:

Developer A

↓

Developer B

↓

Developer C

↓

Copilot

↓

Consistent code

Everyone receives similar recommendations.

Best Practices

Microsoft recommends:

Keep instructions concise.
Focus on project-specific guidance.
Store instruction files with the repository.
Update instructions as standards evolve.
Use clear, natural language.
Include coding, security, testing, and documentation expectations.
Review instruction files during pull requests.
Avoid contradictory instructions.
Combine repository instructions with task-specific prompts when necessary.
Regularly validate that generated code still meets organizational standards.

Common Mistakes

Avoid:

❌ Extremely long instruction files

❌ Conflicting rules

❌ Outdated architecture guidance

❌ Security rules that contradict current policy

❌ Generic instructions that provide little value

❌ Forgetting to update instructions after framework changes

❌ Assuming Copilot always follows instructions perfectly without human review

DP-800 Exam Tips

Candidates should know:

Instruction files provide persistent repository guidance.
They improve consistency across AI-generated code.
They are stored with the project and version controlled.
They can define coding standards, SQL conventions, security requirements, testing expectations, and documentation guidelines.
They reduce repetitive prompting.
They complement, rather than replace, user prompts.
Developers remain responsible for validating all AI-generated code.
Well-written instruction files improve code quality and team productivity.

Summary

GitHub Copilot instruction files are an important mechanism for guiding AI-generated code within a project. By defining repository-specific coding standards, security practices, documentation requirements, and SQL development conventions, organizations can improve consistency, reduce repetitive prompting, and ensure AI-generated code better aligns with business requirements. However, instruction files do not eliminate the need for developer review. AI-generated code should always be validated for correctness, performance, maintainability, and security before deployment.

Practice Exam Questions

Question 1

A development team wants GitHub Copilot to always generate SQL stored procedures that include SET NOCOUNT ON, TRY...CATCH blocks, and schema-qualified object names. What is the best way to accomplish this?

A. Add these requirements to a GitHub Copilot instruction file stored in the repository.

B. Modify SQL Server configuration settings.

C. Configure database compatibility level.

D. Enable Query Store.

Answer: A

Explanation: Repository instruction files provide persistent guidance that GitHub Copilot automatically considers when generating code.

Question 2

What is the primary purpose of a GitHub Copilot instruction file?

A. Improve SQL Server query performance.

B. Define repository-specific guidance that influences AI-generated code.

C. Store database credentials.

D. Configure Azure SQL firewall rules.

Answer: B

Explanation: Instruction files define coding conventions, security requirements, architectural guidance, and other project-specific expectations for Copilot.

Question 3

Which instruction would most directly reduce the likelihood of SQL injection vulnerabilities in AI-generated code?

A. Use uppercase SQL keywords.

B. Always include comments.

C. Always use parameterized queries and avoid dynamic SQL string concatenation.

D. Use table aliases.

Answer: C

Explanation: Parameterized queries are a primary defense against SQL injection attacks.

Question 4

A team updates its SQL naming conventions. What is the best way to ensure GitHub Copilot follows the new standards for all developers?

A. Send an email describing the new conventions.

B. Create a shared prompt document.

C. Ask every developer to memorize the standards.

D. Update the repository’s Copilot instruction file and commit the changes.

Answer: D

Explanation: Version-controlled instruction files distribute updated guidance to everyone working with the repository.

Question 5

Which guidance is most appropriate for inclusion in a GitHub Copilot instruction file?

A. Temporary debugging notes for one developer.

B. Personal keyboard shortcuts.

C. Repository-wide SQL coding standards and documentation requirements.

D. SQL Server service account passwords.

Answer: C

Explanation: Instruction files should contain reusable project guidance, never personal settings or sensitive information.

Question 6

Why are GitHub Copilot instruction files commonly stored in source control?

A. To improve SQL Server indexing.

B. To enable versioning, collaboration, and consistent AI guidance.

C. To reduce database storage.

D. To encrypt SQL scripts.

Answer: B

Explanation: Source control ensures instruction changes are tracked, reviewed, and shared across the team.

Question 7

Which statement about GitHub Copilot instruction files is correct?

A. They eliminate the need to review AI-generated code.

B. They guarantee every generated query is optimized.

C. They replace database security policies.

D. They supplement prompts by providing persistent project-specific guidance.

Answer: D

Explanation: Instruction files enhance Copilot responses but do not replace human review or additional task-specific prompting.

Question 8

A database team wants Copilot to avoid generating SELECT * statements. Where should this requirement be documented?

A. SQL Server Agent.

B. Azure Key Vault.

C. GitHub Copilot instruction file.

D. SQL Profiler.

Answer: C

Explanation: Coding conventions such as avoiding SELECT * are ideal candidates for repository instruction files.

Question 9

Which practice improves the long-term usefulness of GitHub Copilot instruction files?

A. Adding every possible coding preference.

B. Keeping instructions concise, current, and focused on project standards.

C. Storing passwords for easier AI access.

D. Avoiding updates after the initial creation.

Answer: B

Explanation: Effective instruction files are clear, maintainable, and updated as project standards evolve.

Question 10

A developer receives SQL code from GitHub Copilot that follows all repository instruction files. What should the developer do before committing the code?

A. Commit it immediately because instruction files guarantee correctness.

B. Only verify formatting.

C. Disable Copilot.

D. Review the code for correctness, performance, security, and compliance with business requirements.

Answer: D

Explanation: AI-generated code should always undergo human review, testing, and validation, even when instruction files are used.

Go to the DP-800 Exam Prep Hub main page