What are the best practices for improving SQL Server database performance?

Question

I’m learning SQL Server and would like to understand the most important practices for keeping a database performant .

Which areas should I focus on first, such as indexing, query optimization, statistics, execution plans, and database configuration?

Answer 1

Get your hands dirty. First of all, you need to master to write queries at all, before you can look into performance. But if you work with data of some size, you will sooner or later face situations where a query is slow and you want to make it run faster.

Indexes and query plans go hand in hand. Indexes are relatively easy to grasp. Query plans less so. But don't be deterred! Query plans are very deterring if you are going to learn exact what each operator does. You will have to learn them bit by bit. Here are two things to look for in query plans:

1. The thickness of arrows. The thicker the arrow, the more rows flows through.
2. The numbers below the operators which may say something like 12 of 99123. This means that 12 rows were read when the estimate was 99123. This is a gross misestimate, and could be part of the story why the query is slow.

On the other hand, ignore things like Cost 2%, because everything about costs are estimates, and estimates may be correct - or they be completely off.

When it comes to server and database configuration, ignore that for now and save that for later.

Answer 2

Hi @Steve John

First you have understand the query execution and Joins as well. then you have to focus on indexing, query optimization, statistics, execution plans, and database configuration.

• first you have to learn how to write query and which is the efficient way to execute the query. like will use inner joins or outer joins, union.
• suppose you have huge to data that time you will focus on indexing, query optimization and others.

below link having what is sql and how is working.

https://learn.microsoft.com/en-us/training/modules/introduction-to-transact-sql/1-introduction

https://stackoverflow.com/questions/13997365/sql-joins-as-venn-diagram

above link is very basic and how to two tables join or intersection with data.

https://sqlfiddle.com/ this site will helpful for test your query and table creation. it's online compiler.

Thanks.

Answer 3

Start with query performance and indexing. Learn how SQL Server executes queries, how to read execution plans, and how appropriate indexes can reduce scans, I/O, and CPU usage. Focus on avoiding unnecessary columns, filtering efficiently, and understanding when joins, sorts, and aggregations become expensive.

Next, understand statistics and execution plans. Statistics help the optimizer estimate how many rows a query will return, while execution plans show how SQL Server actually chooses to retrieve and process those rows. Learn to recognize common problems such as table/index scans, inaccurate cardinality estimates, expensive key lookups, implicit conversions, and poorly chosen joins.

Then learn database and server configuration, including memory allocation, tempdb configuration, file sizing and autogrowth, MAXDOP, and cost threshold for parallelism. These settings matter, but they generally should come after you understand query and indexing problems rather than being the first place you tune.

Finally, develop good monitoring and maintenance practices. Learn to identify expensive or frequently executed queries, monitor CPU, memory and I/O, maintain indexes appropriately, and keep statistics current. The most important principle is to measure first, identify the actual bottleneck, and then make a targeted change rather than tuning settings based on assumptions.

I'd recommend you start with https://learn.microsoft.com/en-us/sql/relational-databases/performance/performance-center-for-sql-server-database-engine-and-azure-sql-database?view=sql-server-ver17

This page provides links to help you locate the information that you need about performance in the SQL Server Database Engine and Azure SQL Database.

---

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Answer 4

Start with the areas that usually have the biggest impact:

1. Indexing
   • Design indexes based on actual query patterns, table characteristics, and column usage.
   • Avoid over-indexing heavily updated tables, because INSERT, UPDATE, DELETE, and MERGE operations must also maintain those indexes.
   • Keep indexes narrow where possible.
   • Indexing small tables might not help, because a table scan can be cheaper than traversing an index.
   • Monitor index usage and remove unused indexes over time.
   • Query Store can help identify the most frequent and resource-consuming queries so index tuning is focused where it matters most.
2. Query optimization
   • Review and analyze slow or complex queries.
   • Common optimization techniques include rewriting queries, avoiding the N+1 query pattern, reordering joins, reducing redundant operations, and using caching for frequently run queries.
   • Evaluate query plans and wait statistics to identify bottlenecks.
   • Avoid unnecessary or restrictive lock hints, and review isolation levels to balance consistency and performance.
3. Execution plans and Query Store
   • Use the Query Optimizer and execution plans to understand how SQL Server accesses data.
   • Execution plans help identify inefficient access patterns and expensive operators.
   • Query Store is one of the most useful first tools for performance troubleshooting because it keeps query history, execution plans, runtime statistics, and wait statistics.
   • Use Query Store to identify regressed or poorly performing queries.
   • For SQL Server 2016 and 2017 high-performance workloads, Query Store capture mode should be set to Auto.
4. Statistics and plan stability
   • SQL Server uses statistics to build execution plans, so plan quality depends heavily on them.
   • Execution plans can be invalidated and recompiled when statistics are updated, indexes change, or referenced tables/views are altered.
   • Using database compatibility level 130 or later improves statistics behavior for larger tables, including better sampling and locking for statistics updates.
   • Compatibility level 140 or later adds improvements such as adaptive joins, interleaved execution for multi-statement table-valued functions, and memory grant feedback.
5. Database compatibility level and intelligent query processing
   • Enabling compatibility level 140 makes workloads eligible for adaptive query processing features in SQL Server 2017.
   • These features include batch mode memory grant feedback, batch mode adaptive joins, and interleaved execution.
   • If query regressions are related to cardinality estimation, evaluate the appropriate cardinality estimator option, such as database-scoped LEGACY_CARDINALITY_ESTIMATION, query hint LEGACY_CARDINALITY_ESTIMATION, database compatibility level, or trace flag 9481.
   • For join and filter queries affected by containment assumptions in the default cardinality estimator, trace flag 9476 can be evaluated for simple containment.
6. Memory and server configuration
   • Assign the Lock Pages in Memory user right to the SQL Server service startup account when appropriate.
   • Set maximum server memory to about 90 percent of total physical memory.
   • Ensure memory settings account only for nodes configured with affinity mask settings.
   • Consider optimize for ad hoc workloads and FORCED PARAMETERIZATION if plan cache entries are being evicted because of cache pressure.
7. Storage and database file configuration
   • Enable Instant File Initialization by granting the SQL Server service account the Perform Volume Maintenance Tasks right.
   • Review autogrow and autoshrink settings and align them with recommended guidance.
   • For SQL Server 2012 and 2014, consider enabling indirect checkpoints on user databases to optimize I/O behavior.
8. Special workload features
   • If live query troubleshooting is needed in SQL Server 2014 SP2 or later, and a 1.5% to 2% overhead is acceptable, trace flag 7412 enables lightweight profiling.
   • In Availability Group or Log Shipping environments where primary and secondary log disks use different sector sizes, trace flag 1800 can correct slow synchronization.
   • For Query Store performance in high availability and disaster recovery scenarios, trace flags 7745 and 7752 are recommended.

A practical order of focus is:

1. Review slow queries with Query Store and execution plans.
2. Fix or add the right indexes.
3. Check statistics and compatibility level.
4. Review memory, autogrowth, and file initialization settings.
5. Apply workload-specific features only when the scenario matches.

This order works well because indexing and query plan analysis usually produce the fastest and most visible improvements, while configuration changes help sustain performance at scale.

---

• Recommended updates and configuration options for SQL Server 2017 and 2016 with high-performance workloads
• Recommended updates and configuration options for SQL Server 2014 and 2012 with high-performance workloads
• Take an Access excursion through SQL Server
• Index architecture and design guide
• Architecture strategies for optimizing data performance