MSDN Documentation

Sample: Analyzing Query Execution Plans

This example demonstrates how to capture and interpret a query execution plan to identify performance bottlenecks.

-- Enable displaying actual execution plan for the next query
SET SHOWPLAN_ALL ON; -- Or SET SHOWPLAN_TEXT ON; for text-based plan
GO

-- Example query to analyze
SELECT
    e.FirstName,
    e.LastName,
    d.Name AS DepartmentName
FROM
    HumanResources.Employee AS e
INNER JOIN
    HumanResources.Department AS d ON e.DepartmentID = d.DepartmentID
WHERE
    e.HireDate > '2022-01-01'
ORDER BY
    e.LastName;
GO

-- Disable execution plan display
SET SHOWPLAN_ALL OFF;
GO

/*
Interpretation Notes:
- Look for Table Scans on large tables, which often indicate missing indexes.
- High-cost operators (e.g., Sort, Hash Match) might need optimization.
- Check for warnings in the execution plan.
- Ensure statistics are up-to-date.
*/
            

Sample: Demonstrating Index Effectiveness

This sample shows the difference in performance when a query can use an index versus when it cannot.

First, let's create a large sample table and populate it.

-- Create a large table (if it doesn't exist)
IF OBJECT_ID('dbo.LargeData', 'U') IS NULL
BEGIN
    CREATE TABLE dbo.LargeData (
        ID INT IDENTITY(1,1) PRIMARY KEY,
        Value NVARCHAR(100),
        CreatedDate DATETIME DEFAULT GETDATE()
    );

    -- Insert a large number of rows
    INSERT INTO dbo.LargeData (Value)
    SELECT TOP 1000000 REPLICATE('A', 100); -- Insert 1 million rows
    PRINT 'LargeData table created and populated.';
END
ELSE
BEGIN
    PRINT 'LargeData table already exists.';
END
GO

-- Enable statistics for query timing
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
GO

-- Query WITHOUT an appropriate index (will likely perform a table scan)
PRINT 'Executing query without index...';
SELECT * FROM dbo.LargeData WHERE Value = 'AAAA...'; -- Assuming 'A' repeated 100 times
GO

-- Create an index on the 'Value' column
PRINT 'Creating index on Value column...';
CREATE INDEX IX_LargeData_Value ON dbo.LargeData(Value);
GO

-- Query WITH the index (should be much faster)
PRINT 'Executing query with index...';
SELECT * FROM dbo.LargeData WHERE Value = 'AAAA...'; -- Assuming 'A' repeated 100 times
GO

-- Disable statistics
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO

-- Clean up (optional)
-- DROP INDEX IX_LargeData_Value ON dbo.LargeData;
-- DROP TABLE dbo.LargeData;
            

Observe the significant difference in execution time and I/O reported by STATISTICS TIME and IO. The query with the index will be orders of magnitude faster and use far less I/O.

Sample: Mitigating Parameter Sniffing Issues

Parameter sniffing occurs when SQL Server compiles a stored procedure or query based on the parameters provided during its first execution. If subsequent executions use different parameter values that result in a suboptimal plan for those new values, performance can suffer.

-- Example Stored Procedure (potentially vulnerable to parameter sniffing)
IF OBJECT_ID('dbo.usp_GetOrdersByCustomer', 'P') IS NOT NULL
    DROP PROCEDURE dbo.usp_GetOrdersByCustomer;
GO

CREATE PROCEDURE dbo.usp_GetOrdersByCustomer (
    @CustomerID INT
)
AS
BEGIN
    -- Assume this table has an index on CustomerID
    SELECT
        OrderID,
        OrderDate,
        TotalAmount
    FROM
        dbo.Orders
    WHERE
        CustomerID = @CustomerID;
END;
GO

-- Scenario:
-- 1. Execute with a CustomerID that has many orders (e.g., @CustomerID = 1)
--    SQL Server might create a plan optimized for scanning many rows.
-- 2. Execute with a CustomerID that has few orders (e.g., @CustomerID = 5000)
--    The plan optimized for many rows might be inefficient.

-- Mitigation Technique 1: OPTIMIZE FOR UNKNOWN
ALTER PROCEDURE dbo.usp_GetOrdersByCustomer (
    @CustomerID INT
)
AS
BEGIN
    SELECT
        OrderID,
        OrderDate,
        TotalAmount
    FROM
        dbo.Orders
    WHERE
        CustomerID = @CustomerID
    OPTION (OPTIMIZE FOR UNKNOWN);
END;
GO

-- Mitigation Technique 2: RECOMPILE
ALTER PROCEDURE dbo.usp_GetOrdersByCustomer (
    @CustomerID INT
)
AS
BEGIN
    SELECT
        OrderID,
        OrderDate,
        TotalAmount
    FROM
        dbo.Orders
    WHERE
        CustomerID = @CustomerID
    OPTION (RECOMPILE);
END;
GO

-- Mitigation Technique 3: LOCAL VARIABLE
ALTER PROCEDURE dbo.usp_GetOrdersByCustomer (
    @CustomerID INT
)
AS
BEGIN
    DECLARE @LocalCustomerID INT = @CustomerID;
    SELECT
        OrderID,
        OrderDate,
        TotalAmount
    FROM
        dbo.Orders
    WHERE
        CustomerID = @LocalCustomerID;
END;
GO

-- To truly see the impact, you would need a large 'Orders' table
-- and execute the procedure multiple times with different parameter values
-- and analyze the execution plans.
            

Using OPTION (RECOMPILE)Forces SQL Server to generate a new execution plan every time the procedure is called, which can add overhead but ensures an optimal plan for the current parameters. or local variables can help prevent suboptimal plans due to parameter sniffing.

Sample: Creating Clustered and Nonclustered Indexes

Understand the difference between clustered and nonclustered indexes and how to implement them.

-- Assume 'Products' table exists
-- CREATE TABLE Products (ProductID INT IDENTITY PRIMARY KEY, ProductName VARCHAR(100), CategoryID INT, Price DECIMAL(10, 2));

-- Creating a Clustered Index (often on the Primary Key)
-- The PRIMARY KEY constraint typically creates a clustered index by default on INT columns.
-- If we had a non-clustered primary key or wanted to change it:
/*
ALTER TABLE Products DROP CONSTRAINT PK_Products; -- If PK was defined separately
ALTER TABLE Products ADD CONSTRAINT PK_Products PRIMARY KEY CLUSTERED (ProductID);
*/
PRINT 'Clustered index on ProductID (usually default PK).';

-- Creating a Nonclustered Index for faster lookups on ProductName
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_Products_ProductName' AND object_id = OBJECT_ID('dbo.Products'))
BEGIN
    CREATE NONCLUSTERED INDEX IX_Products_ProductName
    ON dbo.Products (ProductName);
    PRINT 'Nonclustered index IX_Products_ProductName created.';
END
ELSE
BEGIN
    PRINT 'Nonclustered index IX_Products_ProductName already exists.';
END

-- Creating a Composite Nonclustered Index for filtering/sorting on CategoryID and Price
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_Products_CategoryPrice' AND object_id = OBJECT_ID('dbo.Products'))
BEGIN
    CREATE NONCLUSTERED INDEX IX_Products_CategoryPrice
    ON dbo.Products (CategoryID, Price);
    PRINT 'Composite nonclustered index IX_Products_CategoryPrice created.';
END
ELSE
BEGIN
    PRINT 'Composite nonclustered index IX_Products_CategoryPrice already exists.';
END

-- Example of a query that would benefit from IX_Products_ProductName
-- SELECT ProductID, ProductName FROM dbo.Products WHERE ProductName = 'Awesome Gadget';

-- Example of a query that would benefit from IX_Products_CategoryPrice
-- SELECT ProductID, ProductName FROM dbo.Products WHERE CategoryID = 5 AND Price > 100.00 ORDER BY Price;

-- Clean up (optional)
-- DROP INDEX IX_Products_ProductName ON dbo.Products;
-- DROP INDEX IX_Products_CategoryPrice ON dbo.Products;
            

Clustered indexes determine the physical order of data in the table, while nonclustered indexes are separate structures that point back to the data rows.