--- title: "SQL Server performance tuning: indexes, statistics and query plans" author: "Abdulaziz Akyol" author_url: https://www.abdulazizakyol.com/en/about/ url: https://www.abdulazizakyol.com/en/blog/sql-server-performance-tuning-indexes-statistics-query-plans/ language: en published: 2026-09-25 categories: ["Microsoft SQL Server", "Software development"] tags: ["SQL Server", "T-SQL", "Query Store", "indexing", "execution plans", "parameter sniffing"] translation: https://www.abdulazizakyol.com/blog/sql-server-performans-ayari-indeks-istatistik-ve-sorgu-plani/ description: "Find slow SQL Server queries by measuring, not guessing: wait statistics, Query Store, execution plans, index design, statistics and parameter sniffing." --- # SQL Server performance tuning: indexes, statistics and query plans By [Abdulaziz Akyol](https://www.abdulazizakyol.com/en/about/) · 2026-09-25 ## Key takeaways - SQL Server performance tuning works by measurement, not guesswork: wait statistics, then Query Store, then the actual execution plan, first finding what the server waits on and then which query causes the wait. - A large gap between estimated and actual row counts in an execution plan usually points to stale statistics, a non-sargable predicate or parameter sniffing. - Missing index DMVs are suggestions, not prescriptions: they give no key column order, never suggest filtered or unique indexes, ignore the cost of wide INCLUDE lists and are cleared on restart. - Microsoft's current guidance says index maintenance should not be driven by fixed fragmentation thresholds; much of the improvement seen after a rebuild actually comes from the statistics update it performs. - Intelligent Query Processing features such as Parameter Sensitive Plan optimization in SQL Server 2022 and Optional Parameter Plan Optimization in SQL Server 2025 only take effect when the database compatibility level is raised. SQL Server performance tuning means finding the bottleneck of a slow workload by measurement and removing it with the smallest, safest change: most often an index, an up-to-date statistic or a rewritten WHERE clause. The order never changes: what is the server waiting on, which query causes that wait, and what is wrong in that query's plan? This article combines what I learned in SQL Server 2014 Performance Tuning training with habits I built while running the Nebim V3 ERP databases and a data warehouse of roughly 4.5 TB at Civil Mağazacılık, a Turkish retail chain. The core method has not changed since 2014; what has changed is that SQL Server can now fix some problems on its own. I cover the current version features at the end. ## Where do you start? The most common mistake I see is adding indexes without measuring, or declaring "the server is slow" and buying bigger hardware. The loop I recommend: 1. Define the symptom: which screen, which report, which time window. 2. Find the server-wide bottleneck with wait statistics. 3. Rank the queries that produce that bottleneck with Query Store. 4. Read the actual execution plan and make one change. 5. Repeat the same measurement; if nothing improved, roll the change back. The examples use the sales table below; you can run the scripts in a test database. ```sql CREATE TABLE dbo.SalesLine ( SalesLineID bigint IDENTITY(1,1) NOT NULL CONSTRAINT PK_SalesLine PRIMARY KEY CLUSTERED, StoreCode varchar(10) NOT NULL, ItemCode varchar(30) NOT NULL, SaleDate date NOT NULL, Qty int NOT NULL, Amount decimal(18,2) NOT NULL, IsReturn bit NOT NULL CONSTRAINT DF_SalesLine_IsReturn DEFAULT (0) ); CREATE NONCLUSTERED INDEX IX_SalesLine_StoreCode ON dbo.SalesLine (StoreCode); -- Test data: 1 million rows; 60% of them in a single store (M0001), the rest spread over hundreds of stores INSERT INTO dbo.SalesLine (StoreCode, ItemCode, SaleDate, Qty, Amount, IsReturn) SELECT TOP (1000000) CASE WHEN n % 10 < 6 THEN 'M0001' ELSE CONCAT('M', RIGHT(CONCAT('000', n % 2000), 4)) END, CONCAT('ITM', n % 5000), DATEADD(DAY, CAST(n % 730 AS int), CAST('20240101' AS date)), CAST(1 + n % 5 AS int), CAST(10 + n % 990 AS decimal(18,2)), CASE WHEN n % 50 = 0 THEN 1 ELSE 0 END FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b) AS x; ``` ## Step 1: What is the server waiting on? Whenever a thread in SQL Server cannot run, the time is recorded against a wait type: reading a page from disk, a lock, the CPU queue, a log write. `sys.dm_os_wait_stats` accumulates these times since the instance started or the statistics were last cleared. A single snapshot is therefore less useful than saving the output to a table and comparing two samples. `wait_time_ms` includes `signal_wait_time_ms`, the time spent waiting for a CPU after the resource became available; a high signal share suggests CPU pressure. ```sql WITH w AS ( SELECT wait_type, wait_time_ms / 1000.0 AS wait_s, (wait_time_ms - signal_wait_time_ms) / 1000.0 AS resource_s, signal_wait_time_ms / 1000.0 AS signal_s, waiting_tasks_count FROM sys.dm_os_wait_stats WHERE waiting_tasks_count > 0 AND wait_type NOT IN ( -- benign background waits (not an exhaustive list) N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH', N'CHECKPOINT_QUEUE', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'DIRTY_PAGE_POLL', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'REQUEST_FOR_DEADLOCK_SEARCH', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK', N'SOS_WORK_DISPATCHER', N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'WAITFOR', N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT') ) SELECT TOP (10) wait_type, CAST(wait_s AS decimal(18,1)) AS wait_s, CAST(resource_s AS decimal(18,1)) AS resource_s, CAST(signal_s AS decimal(18,1)) AS signal_s, waiting_tasks_count, CAST(100.0 * wait_s / SUM(wait_s) OVER () AS decimal(5,1)) AS pct FROM w ORDER BY wait_s DESC; ``` The most common wait types and where to look first: | Wait type | What it usually means | Where to look first | | ---------------------- | ----------------------------------------------- | -------------------------------------------------------------- | | PAGEIOLATCH\_SH / \_EX | Data pages are being read from disk | Queries with high logical reads, scanning plans, memory | | LCK\_M\_\* | Waiting on locks (blocking) | Long transactions, isolation level, jobs writing the same rows | | CXPACKET / CXCONSUMER | Parallel plans; not a problem by itself | Expensive plans, MAXDOP and the parallelism threshold | | SOS\_SCHEDULER\_YIELD | CPU pressure | Top CPU-consuming queries | | WRITELOG | Transaction log write latency | Log disk, jobs that commit row by row | | RESOURCE\_SEMAPHORE | Waiting for a memory grant | Large sorts and hashes, bad row estimates | | ASYNC\_NETWORK\_IO | The client is not consuming results fast enough | Row-by-row processing in the app, oversized result sets | You can reset the numbers with `DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);`, but that also affects any other tool monitoring the same instance; a logging table is the safer way to take deltas. ## Step 2: Find the expensive queries with Query Store [Query Store](https://learn.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store) keeps queries, their plans and runtime statistics inside the database, split into time intervals; unlike the plan cache, it survives a restart. Starting with SQL Server 2022 it is on by default for new databases; in 2016, 2017 and 2019 you turn it on yourself. Always check its state on upgraded databases. ```sql ALTER DATABASE CURRENT SET QUERY_STORE = ON ( OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = AUTO, WAIT_STATS_CAPTURE_MODE = ON -- SQL Server 2017 and later ); -- Top 20 query/plan pairs by total CPU over the last 24 hours SELECT TOP (20) q.query_id, p.plan_id, SUM(rs.count_executions) AS executions, SUM(rs.avg_cpu_time * rs.count_executions) / 1000.0 AS total_cpu_ms, SUM(rs.avg_duration * rs.count_executions) / 1000.0 AS total_duration_ms, SUM(rs.avg_logical_io_reads * rs.count_executions) AS total_logical_reads, MAX(qt.query_sql_text) AS query_text FROM sys.query_store_runtime_stats AS rs JOIN sys.query_store_runtime_stats_interval AS i ON i.runtime_stats_interval_id = rs.runtime_stats_interval_id JOIN sys.query_store_plan AS p ON p.plan_id = rs.plan_id JOIN sys.query_store_query AS q ON q.query_id = p.query_id JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id WHERE i.start_time >= DATEADD(HOUR, -24, SYSDATETIMEOFFSET()) GROUP BY q.query_id, p.plan_id ORDER BY total_cpu_ms DESC; ``` For a query that was fast yesterday and slow today, the **Regressed Queries** report in SSMS shows the old and new plans side by side. If the old plan was good, you can force it with `EXEC sys.sp_query_store_force_plan @query_id = 42, @plan_id = 7;`. Treat that as a temporary patch: fix the underlying cause (statistics, an index, data growth) and then unforce it. ## Step 3: Read the actual execution plan In SSMS, turn on **Include Actual Execution Plan** (Ctrl+M) and run the query with IO and time statistics. `logical reads` is the number of 8 KB pages read; it is a steadier before/after metric than duration because it is not affected by caching. ```sql SET STATISTICS IO, TIME ON; SELECT SaleDate, ItemCode, Qty, Amount FROM dbo.SalesLine WHERE StoreCode = 'M0017' AND SaleDate >= '20250101' AND SaleDate < '20250201'; ``` Look at three things in the plan: - **Seek or scan?** An Index Seek navigates the index tree straight to the relevant range; a Scan reads the whole index or table. A scan is not always bad: for a query that returns most of the table, it is the cheapest path. What is bad is scanning millions of rows to return a few hundred. - **Key Lookup:** `IX_SalesLine_StoreCode` only contains StoreCode, so the other columns of every row found have to be fetched from the clustered index one by one. In the plan you see an Index Seek, a Key Lookup and a Nested Loops join together. Cheap for a few rows; the most expensive step for thousands. - **Estimated versus actual rows:** in the operator properties, compare "Estimated Number of Rows Per Execution" with "Actual Number of Rows for All Executions"; the latter is a total across executions, so divide by the execution count on the inner side of a loop. A gap of several times means the optimizer decided on wrong information: stale statistics, a non-sargable predicate or parameter sniffing. Yellow warning icons matter too: sorts or hashes spilling to tempdb and type conversions (`CONVERT_IMPLICIT`) are the most common. For this query, the fix is an index that covers the needed columns: ```sql CREATE NONCLUSTERED INDEX IX_SalesLine_Store_Date ON dbo.SalesLine (StoreCode, SaleDate) INCLUDE (ItemCode, Qty, Amount); -- IX_SalesLine_StoreCode is now a left prefix of this index; check its usage and consider dropping it. ``` ## Step 4: Design your indexes - **The clustered index** is the table itself, and a table can have only one. A narrow, unique, static and ever-increasing key (such as an IDENTITY) is a good default, because every nonclustered index reaches the row through it. Random GUID keys increase page splits. A table without a clustered index (a heap) is usually a forgotten detail rather than a deliberate choice. - **Key column order:** columns searched by equality come first, the column searched by range (`>`, `<`, `BETWEEN`) comes after; among equality columns, put the most selective first. `(StoreCode, SaleDate)` follows that rule. - **INCLUDE columns** live only at the leaf level; they do not take part in the search but they eliminate key lookups. Their order does not matter. - **A filtered index** covers only a subset of the table, so it is small and cheap to maintain: ```sql CREATE NONCLUSTERED INDEX IX_SalesLine_Returns ON dbo.SalesLine (SaleDate) INCLUDE (StoreCode, Amount) WHERE IsReturn = 1; ``` Filtered indexes have two traps. With a parameterized predicate such as `WHERE IsReturn = @p`, the optimizer may not use the index, because the cached plan has to be correct for every parameter value. And sessions that write to the table need SET options such as `ANSI_NULLS` and `QUOTED_IDENTIFIER` turned on. Every index adds cost to INSERT, UPDATE and DELETE, plus disk space. Check for unused indexes regularly, but remember the counters reset on restart, and indexes used only by month-end or year-end reports can look "unused" for weeks: ```sql SELECT OBJECT_NAME(i.object_id) AS table_name, i.name AS index_name, ISNULL(s.user_seeks, 0) + ISNULL(s.user_scans, 0) + ISNULL(s.user_lookups, 0) AS reads, ISNULL(s.user_updates, 0) AS writes FROM sys.indexes AS i LEFT JOIN sys.dm_db_index_usage_stats AS s ON s.object_id = i.object_id AND s.index_id = i.index_id AND s.database_id = DB_ID() WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1 AND i.type_desc = 'NONCLUSTERED' AND i.is_primary_key = 0 AND i.is_unique_constraint = 0 ORDER BY reads ASC, writes DESC; ``` ### The traps in missing index DMVs During compilation, the optimizer records cases where "this index would have made the plan cheaper". The [limitations listed on Microsoft Learn](https://learn.microsoft.com/en-us/sql/relational-databases/indexes/tune-nonclustered-missing-index-suggestions) explain why these suggestions should not be applied as-is: - They are based on estimates made while compiling a single query and are never tested after execution. - They only suggest nonclustered rowstore indexes; never unique or filtered ones. - They do not specify the order of key columns. - There is no cost-benefit analysis of the size of the INCLUDE list. - They produce near-duplicate suggestions for the same table. - At most 600 missing index groups are collected, and the data is cleared by a restart, a failover or a schema change on the table. ```sql SELECT TOP (20) CONVERT(decimal(28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS estimated_improvement, mid.statement AS table_name, mid.equality_columns, mid.inequality_columns, mid.included_columns FROM sys.dm_db_missing_index_groups AS mig JOIN sys.dm_db_missing_index_group_stats AS migs ON migs.group_handle = mig.index_group_handle JOIN sys.dm_db_missing_index_details AS mid ON mid.index_handle = mig.index_handle WHERE mid.database_id = DB_ID() ORDER BY estimated_improvement DESC; ``` My approach: put all suggestions for a table next to its existing indexes, merge them, order the keys by the equality-then-range rule, and then measure the effect in Query Store. ## Step 5: Keep statistics current The optimizer estimates how many rows a predicate returns from the statistics histogram, and the plan choice rests on that estimate. With `AUTO_UPDATE_STATISTICS` on, a statistic is refreshed when a query uses it after the number of modified rows crosses a threshold ([Microsoft Learn: Statistics](https://learn.microsoft.com/en-us/sql/relational-databases/statistics/statistics)): - Up to SQL Server 2014, and below compatibility level 130, the threshold is **500 + 20% of the table**. - From SQL Server 2016 with compatibility level 130 onwards it is **MIN(500 + 0.20 × n, √(1000 × n))**. In Microsoft's example, a 2-million-row table is refreshed every 44,721 changes instead of 400,500. As a quick calculation, the old threshold on a 100-million-row transaction table means more than 20 million changes. On tables that keep growing by date, the newest days fall outside the last histogram step and "today's" rows get underestimated. That is why, on large ERP tables, a scheduled statistics job complements the automatic updates: ```sql SELECT s.name AS stats_name, sp.last_updated, sp.rows, sp.rows_sampled, sp.modification_counter FROM sys.stats AS s CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp WHERE s.object_id = OBJECT_ID(N'dbo.SalesLine') ORDER BY sp.modification_counter DESC; -- Update a single statistic with a full scan UPDATE STATISTICS dbo.SalesLine IX_SalesLine_Store_Date WITH FULLSCAN; -- Whole table: pin the sampling rate so later updates keep using it UPDATE STATISTICS dbo.SalesLine WITH SAMPLE 25 PERCENT, PERSIST_SAMPLE_PERCENT = ON; ``` ## Step 6: Recognize parameter sniffing SQL Server compiles a parameterized query or stored procedure for the values it is first called with and reuses that plan from cache. With skewed data, a plan that is good for the first value can be terrible for others: ```sql CREATE OR ALTER PROCEDURE dbo.GetStoreSales @StoreCode varchar(10), @From date, @To date AS BEGIN SET NOCOUNT ON; SELECT SalesLineID, SaleDate, ItemCode, Qty, Amount, IsReturn FROM dbo.SalesLine WHERE StoreCode = @StoreCode AND SaleDate >= @From AND SaleDate < @To; END; GO -- If a small store is called first, a Seek + Key Lookup plan is cached; -- M0001, which holds 60% of the rows, then does hundreds of thousands of lookups with it. EXEC dbo.GetStoreSales @StoreCode = 'M0017', @From = '20240101', @To = '20260101'; EXEC dbo.GetStoreSales @StoreCode = 'M0001', @From = '20240101', @To = '20260101'; ``` The options: ```sql -- Option 1: append to the query; it is recompiled on every execution -- OPTION (RECOMPILE) -- Option 2: compile for a typical value, or use average density instead of the histogram -- OPTION (OPTIMIZE FOR (@StoreCode = 'M0001')) -- OPTION (OPTIMIZE FOR UNKNOWN) -- Option 3 (SQL Server 2022+): add a Query Store hint without touching the code. -- Find the query_id in sys.query_store_query and sys.query_store_query_text. EXEC sys.sp_query_store_set_hints @query_id = 42, @query_hints = N'OPTION(RECOMPILE)'; ``` [Parameter Sensitive Plan (PSP) optimization](https://learn.microsoft.com/en-us/sql/relational-databases/performance/parameter-sensitive-plan-optimization), introduced in SQL Server 2022 with compatibility level 160, detects skewed distributions on equality predicates from the histogram and can keep several plans (query variants) for the same query. Optional Parameter Plan Optimization (OPPO), which came with SQL Server 2025 and compatibility level 170, picks a suitable plan at runtime for optional-parameter patterns such as `@p IS NULL OR column = @p`. My order: look for the root cause first. In this example, adding `IsReturn` to the index's INCLUDE list removes the lookup and gives both calls the same good plan. If the query runs rarely, `RECOMPILE` is the least risky option; if it runs hundreds of times a second, compilation cost makes plan forcing or PSP the better choice. ## Step 7: Write sargable queries A sargable (search argument-able) predicate is one that can use an index seek. Wrap the column in a function or convert its type and the optimizer can no longer seek; it falls back to a scan: | Not sargable | Sargable equivalent | | --------------------------------------------- | -------------------------------------------------------- | | `WHERE YEAR(SaleDate) = 2025` | `WHERE SaleDate >= '20250101' AND SaleDate < '20260101'` | | `WHERE LEFT(ItemCode, 3) = 'ITM'` | `WHERE ItemCode LIKE 'ITM%'` | | `WHERE ISNULL(StoreCode, '') = 'M0017'` | `WHERE StoreCode = 'M0017'` (column is NOT NULL) | | `WHERE Amount * 1.2 > 1000` | `WHERE Amount > 1000 / 1.2` | | `WHERE StoreCode = N'M0017'` (varchar column) | `WHERE StoreCode = 'M0017'` | The last row is sneaky: when the application queries a varchar column with an nvarchar parameter, an implicit conversion happens on the column side, which depending on the collation can prevent a seek or make it more expensive. It shows up in the plan as a `CONVERT_IMPLICIT` warning, and the fix is to match the parameter type to the column in the application. ## Step 8: Don't ignore tempdb Temporary tables, table variables, spilling sorts and hashes, and row versioning (RCSI, snapshot isolation, online index operations) all use tempdb. [Microsoft's guidance](https://learn.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database): with eight or fewer logical processors use that many data files, with more use eight, and if allocation contention persists add files in multiples of four. All data files should have the same initial size and growth settings. Memory-optimized tempdb metadata, introduced in SQL Server 2019, should only be enabled when metadata contention actually shows up; SQL Server 2025 added resource governance for tempdb space. ```sql SELECT name, type_desc, size * 8 / 1024 AS size_mb, CASE WHEN is_percent_growth = 1 THEN CONCAT(growth, ' %') ELSE CONCAT(growth * 8 / 1024, ' MB') END AS growth FROM tempdb.sys.database_files; ``` ## Step 9: Measured maintenance instead of blind rebuilds [Microsoft's current index maintenance guidance](https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes) is clear: maintenance decisions should not be made on fixed fragmentation or page density thresholds alone; measure the effect on your workload. It also makes an important point: a rebuild updates the statistics on the index key columns with a full scan, and the improvement seen afterwards often comes from that. The same benefit can usually be had from a much cheaper statistics update. In practice many teams use Ola Hallengren's free SQL Server Maintenance Solution scripts: ready-made, parameterized procedures for backups, integrity checks and index and statistics maintenance. For example, a nightly job that only updates modified statistics and leaves indexes alone: ```sql EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = NULL, @FragmentationHigh = NULL, @UpdateStatistics = 'ALL', @OnlyModifiedStatistics = 'Y'; ``` ## Version features: Intelligent Query Processing Since SQL Server 2017, the [Intelligent Query Processing (IQP)](https://learn.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing) family has handled on its own some problems we used to fix by hand. Most of these features switch on not when you install a version but when you raise the database compatibility level: | Version (compatibility level) | Notable IQP features | | ----------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------ | | SQL Server 2017 (140) | Batch mode adaptive joins, interleaved execution for multi-statement table-valued functions, batch mode memory grant feedback | | SQL Server 2019 (150) | Row mode memory grant feedback, table variable deferred compilation, scalar UDF inlining, batch mode on rowstore, APPROX\_COUNT\_DISTINCT | | SQL Server 2022 (160) | Parameter Sensitive Plan optimization, CE feedback, DOP feedback, memory grant feedback persistence; Query Store on by default for new databases | | SQL Server 2025 (170) | Optional Parameter Plan Optimization, CE feedback for expressions, OPTIMIZED\_SP\_EXECUTESQL | Some features also work at lower levels and some need extra settings (OPTIMIZED\_SP\_EXECUTESQL is a database-scoped configuration); CE feedback, DOP feedback and feedback persistence require Query Store to be on. Raising the compatibility level can change plans. The safe route: with Query Store on, collect data at the current level for a while, raise the level, find regressions in the Regressed Queries report, and force the old plan where needed. ## Checklist 1. Define the symptom and the time window; "the server is slow" is not a symptom. 2. Find the dominant wait type from a `sys.dm_os_wait_stats` delta. 3. Turn on Query Store and rank the most expensive queries by CPU, duration and logical reads. 4. In the actual plan, check seek versus scan, key lookups and the gap between estimated and actual rows. 5. Design indexes with the equality-then-range rule; remove lookups with INCLUDE; merge missing index suggestions before applying them. 6. Update statistics on large tables with a scheduled job. 7. When you suspect parameter sniffing, look for the root cause first, then RECOMPILE, plan forcing or PSP. 8. Don't wrap columns in functions in WHERE clauses; match data types. 9. Keep tempdb data files equally sized and correctly counted. 10. Measure before doing maintenance; a statistics update is often enough instead of a rebuild. 11. Raise the compatibility level with a Query Store before/after comparison. I describe the same "measure first, change one thing" approach on the web side in [how this site was made fast with Astro and Cloudflare](https://www.abdulazizakyol.com/en/blog/a-fast-personal-site-with-astro-and-cloudflare-core-web-vitals/); it is a habit that goes back to my years in [enterprise IT management](https://www.abdulazizakyol.com/en/blog/from-enterprise-it-management-to-ai-entrepreneurship-what-five-years-taught-me/). ## Frequently asked questions ### How do I find slow queries in SQL Server? Start with sys.dm_os_wait_stats to see what the server waits on most: disk, locks, CPU or memory. Then rank the most expensive queries in Query Store by total CPU, duration or logical reads. Finally, read the actual execution plan and SET STATISTICS IO output of the query you picked to decide whether the problem is an index, statistics or the way the query is written. ### What is a key lookup and how do I remove it? A key lookup fetches the missing columns from the clustered index, one row at a time, for every row found through a nonclustered index. It is cheap for a handful of rows and becomes the most expensive step for thousands. The usual fix is a covering index that adds the needed columns with INCLUDE. ### Should I create missing index suggestions exactly as SQL Server proposes them? No. Suggestions are based on estimates made while compiling a single query, they do not specify key column order, they produce overlapping variations for the same table and they ignore the size cost of the INCLUDE list. Review them together with the table's existing indexes, merge them, and verify the effect in Query Store. ### What is parameter sniffing and how do I fix it? SQL Server compiles a parameterized query for the values of its first execution and reuses that cached plan; with skewed data the plan can be poor for other values. Common fixes are OPTION (RECOMPILE), OPTIMIZE FOR, plan forcing or Query Store hints, and Parameter Sensitive Plan optimization, which arrived in SQL Server 2022 with compatibility level 160. ### Do I need to rebuild indexes every night? Usually not. Microsoft recommends basing maintenance on measured workload impact rather than fixed fragmentation thresholds. The improvement people see after a rebuild often comes from the full-scan statistics update that comes with it, and a much cheaper statistics update can deliver the same benefit. ## Sources 1. [sys.dm_os_wait_stats (Microsoft Learn)](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-wait-stats-transact-sql) 2. [Monitor performance by using the Query Store (Microsoft Learn)](https://learn.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store) 3. [Tune nonclustered indexes with missing index suggestions (Microsoft Learn)](https://learn.microsoft.com/en-us/sql/relational-databases/indexes/tune-nonclustered-missing-index-suggestions) 4. [Statistics (Microsoft Learn)](https://learn.microsoft.com/en-us/sql/relational-databases/statistics/statistics) 5. [Maintain indexes optimally (Microsoft Learn)](https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes) 6. [Intelligent query processing (Microsoft Learn)](https://learn.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing) --- Abdulaziz Akyol is the founder of CX Teknoloji (AI, computer vision and IoT). Canonical version: https://www.abdulazizakyol.com/en/blog/sql-server-performance-tuning-indexes-statistics-query-plans/