// RetailVault · SQL Performance Lab

Query tuning
at scale.

Azure SQL · 100k rows · 7 indexes · 7 stored procedures · automated maintenance · engine internals

0 % total speedup
0 % turnover query
0 % fewer reads
0 sec → 3 sec
01 /

Query performance

Elapsed time comparison — before vs after index tuning

Before After
Query times: Before ranged from 191ms to 11618ms. After ranged from 30ms to 692ms.
Query Before After Improvement Visual
KPI summary 191 ms 50 ms 74%
Monthly sales 233 ms 50 ms 79%
Top products 233 ms 50 ms 79%
Low stock alerts 233 ms 30 ms 87%
Inventory turnover 11,618 ms 692 ms 94%
02 /

Logical reads

Logical reads per table — before vs after

Before After
fact_sales: 743 before, 650 after. fact_inventory: 164 before, 165 after. dim_product: 40 before, 2 after.
03 /

Index design

idx_lab_fs_date_product

fact_sales · keys: date_key, product_key

Covering

idx_lab_fs_store_date

fact_sales · keys: store_key, date_key

Covering

idx_lab_fi_below_reorder

fact_inventory · WHERE is_below_reorder = 1

Filtered

idx_lab_fi_product_date

fact_inventory · keys: product_key, store_key

Covering

idx_lab_fi_product_agg

fact_inventory · keys: product_key, date_key

Covering

idx_lab_dimdate_year_month

dim_date · keys: year, month_num, quarter

Covering

idx_lab_dp_category_price

dim_product · keys: category_name, is_current

Covering
04 /

Stored procedures

Procedure Purpose Key optimization Tag
usp_GetKpiSummary Revenue, profit, margin for a given year Covering index seek on fact_sales Analytics
usp_GetMonthlySales Month-by-month trend with running total Window function + idx_lab_fs_date_product Analytics
usp_GetTopProducts Top N products by revenue, parameterized CTE + covering index, 95% dim reads reduced Analytics
usp_GetLowStockAlerts Items below reorder level across all stores Filtered index — only reads is_below_reorder=1 Inventory
usp_GetInventoryTurnover Stock velocity ratio per product OPTION(RECOMPILE) — eliminates param sniffing Inventory
usp_MaintainIndexes Auto-rebuild / reorganize based on fragmentation Dynamic SQL + sys.dm_db_index_physical_stats DBA
usp_GetIndexHealth Fragmentation report for all indexes DMV query — REBUILD / REORGANIZE / HEALTHY DBA
05 /

Index maintenance

Fragmentation thresholds

≥ 30% fragmentation REBUILD
10–30% fragmentation REORGANIZE
< 10% fragmentation SKIP
Statistics update FULLSCAN on all tables

Last maintenance run

Indexes rebuilt 4 indexes
Indexes reorganized 1 index
Statistics updated 8 tables
Duration 70 seconds
06 /

Engine internals BONUS

Buffer pool §A — top 5 by pages

fact_sales · clustered PK1,438 pages · 11 MB
fact_sales · idx_lab_fs_date_product657 pages · 5 MB
fact_sales · idx_lab_fs_store_date617 pages · 4 MB
fact_sales · idx_fs_date_store609 pages · 4 MB
fact_sales · idx_fs_product554 pages · 4 MB

Transaction log §C — log space usage

Database RetailVault_Warehouse
Recovery model FULL
Log size 328.00 MB
Log used 5.01 MB
Utilization 1.5% ✓
Status Healthy — backups current

Plan cache §B — stored procedures

usp_GetInventoryTurnover Cached · 188 ms CPU
usp_GetKpiSummary Cached · 38 ms CPU
usp_GetMonthlySales Cached · 37 ms CPU
usp_GetTopProducts Cached · 29 ms CPU
Plan recompiles 1 per proc (first compile)
Missing index DMV 0 rows — full coverage ✓

§D — Top 15 wait statistics · Azure SQL instance

Wait type Tasks Total wait (ms) Max wait (ms) Signal wait % Class
SOS_WORK_DISPATCHER258,6201,429,396,59512,071,4500.0%infra
PREEMPTIVE_XE_DISPATCHER63465,078,821773,5440.0%infra
DIRTY_PAGE_POLL338,95036,619,1902280.0%infra
QDS_PERSIST_TASK_MAIN_LOOP_SLEEP61136,604,46160,0160.0%infra
PWAIT_EXTENSIBILITY_CLEANUP_TASK12336,600,795300,015100.0%infra
XE_LIVE_TARGET_TVF61036,544,32060,0160.0%infra
QDS_ASYNC_QUEUE6436,277,28012,597,1670.0%infra
HADR_FABRIC_CALLBACK5,07136,010,2021,800,0150.0%infra
PVS_PREALLOCATE13635,402,74717,486,8680.0%infra
HADR_TIMER_TASK9549,560,68020,0260.0%infra
WRITELOG114,283574,5255333.7%app
LOG_RATE_GOVERNOR22,239348,3831020.1%app
RESOURCE_GOVERNOR_IDLE16,712217,558350.0%infra
SOS_SCHEDULER_YIELD13,394208,192279100.0%app
PAGEIOLATCH_SH4,875185,1951,1271.7%app

All top waits are Azure SQL infrastructure threads (HA fabric, Query Store, checkpoint). Application-level waits (WRITELOG, PAGEIOLATCH_SH) are negligible — no blocking, no memory pressure, no IO bottleneck.

§E — Index usage stats · seeks vs scans since last restart

Table Index Type Seeks Scans Updates Status
dim_datePK (clustered)CLUSTERED114,259441,246active
dim_productPK (clustered)CLUSTERED211,451291active
dim_storePK (clustered)CLUSTERED110,94151active
dim_dateidx_lab_dimdate_year_monthNONCLUSTERED900active
dim_productidx_lab_dp_category_priceNONCLUSTERED080scan-only
fact_salesidx_fs_date_storeNONCLUSTERED728100,501active
fact_salesidx_fs_productNONCLUSTERED2036100,501active
fact_salesidx_lab_fs_date_productNONCLUSTERED0150scan-only
fact_salesidx_lab_fs_store_dateNONCLUSTERED04100,501scan-only
fact_inventoryidx_lab_fi_product_aggNONCLUSTERED02410,401scan-only
fact_inventoryidx_lab_fi_product_dateNONCLUSTERED02610,401scan-only
fact_inventoryidx_lab_fi_below_reorderNONCLUSTERED030scan-only
fact_inventoryidx_fi_date_storeNONCLUSTERED000scan-only

Scan-only indexes are expected at 100k rows / low cardinality (20 products, 10 stores) — optimizer correctly prefers scans. At production scale these indexes will seek. No indexes flagged write-only; all recommendations: OK.