Azure SQL Database Documentation

-- Covering index for frequently queried columns
CREATE NONCLUSTERED INDEX IX_Orders_CustomerDate
ON dbo.Orders (CustomerID, OrderDate)
INCLUDE (TotalAmount, Status);

Use this index to eliminate lookups for queries that filter by CustomerID and OrderDate while selecting TotalAmount and Status.

-- Create additional TempDB data files (recommended: 1 per logical CPU core)
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev2, FILENAME = 'E:\SQLData\tempdev2.ndf', SIZE = 1024MB, MAXSIZE = UNLIMITED, FILEGROWTH = 256MB);
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev3, FILENAME = 'E:\SQLData\tempdev3.ndf', SIZE = 1024MB, MAXSIZE = UNLIMITED, FILEGROWTH = 256MB);
GO
-- Ensure equal size for all files
DBCC CHECKIDENT ('tempdb..sysfiles', RESEED, 0);

Multiple data files reduce contention on allocation structures, improving overall throughput.

SELECT *
FROM dbo.Sales
WHERE SaleDate BETWEEN @StartDate AND @EndDate
OPTION (RECOMPILE);

Use OPTION (RECOMPILE) for queries with highly variable predicate values to avoid suboptimal cached plans.

-- Create a workload group for reporting
CREATE WORKLOAD GROUP ReportingGroup
WITH (MAX_CPU_PERCENT = 20);
GO
-- Assign a classifier function
CREATE FUNCTION dbo.rg_classifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
    IF ORIGINAL_LOGIN() = 'report_user'
        RETURN 'ReportingGroup';
    RETURN 'default';
END;
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.rg_classifier);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;

Restricts reporting queries to 20% of CPU resources, preserving capacity for OLTP workloads.