7 Tips for Query Optimization in SQL Servers

When optimizing performance, developers and architects often overlook tuning their SQL queries. Understanding how databases work and writing better SQL queries play a huge role in boosting performance. Efficient SQL queries mean quality, scalable applications.

In this tutorial, we will look at 7 essential SQL tips for optimizing your SQL server.

Refresh your SQL knowledge.

Get hands-on practice with all the fundamentals of SQL. You’ll even get practice with common SQL interview questions, so you’ll be ready and confident to answer any question that comes your way.

An Introductory Guide to SQL

Tip 1: Choose the proper data type for the column#

Every table column in SQL has an associated data type. You can choose from integers, dates, varchars, Boolean, text, etc. When developing, it is important that you choose the proper data type. Numbers should be of numeric type, dates should be dates, etc. This is extremely important for indexing.

Let’s look at the example below.

SELECT employeeID, employeeName
FROM employee
WHERE employeeID = 13412;

The above query fetches the employee ID and name for the employee with ID 13412. What if the data type for employeeID is string? You may run into trouble there when using indexing, as it will take forever when it should be a simple scan.

Tip 2: Table Variables and Joins#

When you have complex queries like fetching the orders for customers, along with their names and order dates, you need something more than a simple select statement. In this case, we’re fetching data from the customer and order tables. That’s where joins come in.

Let’s look at the example of a join:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

SQL offers inner, full, left outer, and right outer types of joins.

Table variables are local variables that store data temporarily and have all the properties of local variables. Do not use table variables in joins as SQL sees them, as a single row. Even though they are fast, table variables do not perform well in joins.

Tip 3: Use conditional WHERE clause#

Conditional WHERE clauses are used for subsetting. Let’s say you have a situation like this:

-if SEQ_VAR in (1, 2, 3) & diff(DATE_VAR2, DATE_VAR1)≥ 0
— elif SEQ_VAR in (4, 5, 6) & diff(DATE_VAR2, DATE_VAR1) ≥1
— else diff(DATE_VAR2, DATE_VAR1) ≥2

With the conditional WHERE clause, it would look like this:

SELECT 
  DAT.ID_VAR,
  DAT.SEQ_VAR,
  DAT.NUM_VAR,
  DATE_VAR1,
  DATE_VAR2,
  TRUNC(DATE_VAR2) - TRUNC(DATE_VAR1) AS LAG_IN_DATES

FROM 
  CURRENT_TABLE      DAT 
WHERE
  (TRUNC(DATE_VAR2) - TRUNC(DATE_VAR1)) >= CASE WHEN SEQ_VAR IN (1,2,3) THEN 0 WHEN SEQ_VAR IN (4,5,6) THEN 1 ELSE 2 END 
ORDER BY ID_VAR, SEQ_VAR

Tip 4: Use SET NOCOUNT ON#

When performing INSERT, SELECT, DELETE, and UPDATE operations, use SET NOCOUNT ON. SQL always returns the affected number of rows for such operations, so when you have complex queries with a lot of joins, it can effect performance.

With SET NOCOUNT ON, SQL will not count the affected rows and improve performance.

In the following example, we are preventing the message about the number of rows affected displaying.

USE AdventureWorks2012;  
GO  
SET NOCOUNT OFF;  
GO  
-- Display the count message.  
SELECT TOP(5)LastName  
FROM Person.Person  
WHERE LastName LIKE 'A%';  
GO  
-- SET NOCOUNT to ON to no longer display the count message.  
SET NOCOUNT ON;  
GO  
SELECT TOP(5) LastName  
FROM Person.Person  
WHERE LastName LIKE 'A%';  
GO  
-- Reset SET NOCOUNT to OFF  
SET NOCOUNT OFF;  
GO  

Tip 5: Avoid ORDER BY, GROUP BY, and DISTINCT#

Use ORDER BY, GROUP BY, and DISTINCT only when necessary. SQL creates work tables and puts the data there. It then organizes the data in the work table based on the query and then returns the results.

Tip 6: Fully qualify database object names#

The goal of using fully qualified database object names is to remove ambiguity. A fully qualified object name looks like this:

DATABASE.SCHEMA.OBJECTNAME.

When you have access to multiple databases, schemas, and tables, it becomes important that you specify what you want to access. You don’t need to do this unless you’re working with large databases with multiple users and schemas, but it is a good practice.

So instead of using a statement like:

SELECT * FROM TableName

You should use:

SELECT * FROM dbo.TableName

Tip 7: Learn how to fully secure your code#

Databases store all sorts of information making them prime attack targets. Common attacks include SQL injections where the user enters a SQL statement instead of a username and retrieve or modify your database. Examples of SQL injections include:

textuserID = getRequestString("userID");
textSQL = "SELECT * FROM Users WHERE userID = " + textuserID;

Let’s say you have this, textuserID will fetch the input from the user. Here is how it can go wrong:

SELECT * FROM Users WHERE userID = 890 OR 1=1;

Since 1=1 is always true, it will fetch all the data from the Users table.

You can guard your database against SQL injections using parameterized statements, input validations, sanitizing input, etc. How you secure your database depends on the DBMS. You will need to understand your DBMS and its security issues so you can write code that is secure.

Now that you understand the basics of indexing, let’s explore more advanced indexing strategies.

Modern indexing and storage strategies#

To achieve meaningful performance improvements in SQL Server, you often need to go beyond basic B-tree indexing. Modern versions provide advanced indexing and storage options that can dramatically change how your queries perform.

Some of the most powerful techniques include:

• Columnstore indexes: Ideal for analytics and large data scans, storing data by column for better compression and reduced I/O.
• Memory-optimized tables (In-Memory OLTP): Eliminate locking overhead by keeping data entirely in memory for high-speed transactional workloads.
• Filtered indexes & indexed computed columns: Create indexes only on relevant subsets of data or on computed results to accelerate selective queries.
• Indexed views (materialized views): Precompute and persist expensive joins or aggregations that run frequently.

Each of these features comes with trade-offs. For instance, columnstore indexes can slow down updates, while indexed views require careful maintenance. The key is knowing when each technique makes sense for your workload.

Execution plans, optimizer internals, and query tuning#

No matter how well you write your queries, you won’t get far without understanding how the SQL Server optimizer interprets them. Execution plans show how the engine retrieves data, which indexes it uses, and where performance bottlenecks occur.

Start by examining the Actual Execution Plan in SQL Server Management Studio (SSMS). This visualization reveals whether the query uses index seeks or full table scans, how rows are processed, and the relative cost of each step. Pay close attention to mismatches between estimated and actual row counts — a large difference suggests stale statistics or poor cardinality estimation.

Common execution plan indicators:

Indicator	What It Means	Recommended Action
Index Seek	The optimizer uses an index to quickly find relevant rows.	Usually optimal — ensure the index covers the query.
Index Scan / Table Scan	The engine scans many rows, often due to missing or non-selective indexes.	Add or refine indexes, or filter the query.
Key Lookup	A secondary lookup occurs after an index seek to retrieve extra columns.	Consider a covering index to eliminate lookups.
Sort / Hash Match	The query requires sorting or joining large datasets in memory.	Add indexes to support the join order or sort key.
High Estimated vs Actual Rows	The optimizer misjudged row counts due to outdated stats or skewed data.	Update statistics or add filtered statistics.
Parallelism Operators	The query was split across multiple threads to improve performance.	Check if parallelism is beneficial or causing overhead.

You can also use Dynamic Management Views (DMVs) to find missing index suggestions and analyze performance patterns. When you identify a consistently good plan, you can use Query Store or hints to “force” that plan — though this should be done sparingly to avoid brittleness as your schema evolves.

Statistics, cardinality estimation, and data skew#

The query optimizer depends heavily on accurate statistics to choose the right execution plan. If statistics are outdated or incomplete, optimizer decisions can degrade significantly.

While SQL Server automatically updates statistics, large or partitioned tables might need manual updates to stay accurate. You can also create filtered or multi-column statistics to help the optimizer understand data distributions better.
In datasets with heavy skew — for instance, categorical fields dominated by a single value — the optimizer may misestimate row counts. Supplemental statistics or query rewrites can help correct those misjudgments.

Partitioning and scale strategies#

As data volumes grow, even well-written queries can slow down simply due to scale. Partitioning and sharding strategies help maintain performance predictability as tables grow into billions of rows.

• Table partitioning: Splits large tables horizontally, typically by date or key, so queries can skip irrelevant partitions.
• Sliding window pattern: Efficiently manage historical data by dropping old partitions and adding new ones without downtime.
• Sharding: Distribute data across multiple servers for horizontal scalability and reduced contention.

These approaches ensure performance stability as datasets expand.

Concurrency, locking, and isolation levels#

In multi-user environments, performance problems often come from contention rather than slow queries. Understanding SQL Server’s concurrency model is crucial for scalability.

Key strategies:

• Minimize lock escalation: Keep transactions short and avoid large scans within transactions.
• Choose appropriate isolation levels: Use READ COMMITTED SNAPSHOT or snapshot isolation to reduce blocking via versioned data.
• Deadlock prevention: Access tables in a consistent order or terminate lower-priority sessions to break deadlocks.

Monitoring and diagnostics#

Optimization doesn’t end once your queries run fast — continuous monitoring ensures they stay that way. SQL Server includes multiple diagnostic tools:

• Dynamic Management Views (DMVs): Examine query stats, index health, and active sessions.
• Query Store: Captures historical execution plans and helps spot regressions.
• Extended Events / SQL Profiler: Trace expensive queries and blocking issues.

For deeper analysis, review wait statistics — they reveal whether bottlenecks are CPU, I/O, or locking-related. Together with query metrics, they give a full picture of system performance.

Infrastructure, I/O, memory, and TempDB tuning#

Query optimization relies on solid infrastructure and configuration. Even a perfect query can perform poorly on a misconfigured server.

Essential tuning steps:

• TempDB: Use multiple data files (typically one per CPU core, up to a point) on fast storage.
• I/O optimization: Separate transaction logs from data files; use SSD/NVMe for throughput.
• Memory management: Tune max server memory to ensure buffer pool adequacy.
• CPU and parallelism: Balance workloads with appropriate NUMA, CPU affinity, and MAXDOP settings.

SQL server features and engine enhancements#

Recent releases introduce Intelligent Query Processing (IQP) — features like adaptive joins, interleaved execution, and scalar UDF inlining improve plan quality automatically.
Accelerated Database Recovery (ADR) speeds crash recovery and mitigates long rollback issues.

Automatic tuning further enhances reliability: SQL Server can detect plan regressions and fix them autonomously. Parameter sensitivity feedback adjusts plans dynamically based on runtime data.

Security and performance#

Security and performance often intersect. Poor security design can hurt performance — and vice versa.

• Parameterized queries / stored procedures: Prevent SQL injection and reduce parsing overhead.
• Least privilege: Grant only necessary access.
• Row-level security / dynamic masking: Enforce policies without rewriting queries.
• Encryption impact: Account for extra CPU/I/O load from Transparent Data Encryption (TDE) or Always Encrypted.

Real-world case studies and examples#

Theory matters, but practical evidence is the most persuasive. Showcase before-and-after scenarios that demonstrate tangible gains.

For example, a query that once required a full table scan might run 50x faster after adding a filtered index.
Compare execution plans and metrics to highlight improvements, but also explain trade-offs such as higher index maintenance or schema complexity.

These real-world cases help illustrate when advanced techniques are truly worth the investment.

What to learn next#

In this article, we covered some essential SQL tips but, there’s always more to learn. Some good next steps are:

• Optimizing views
• Nested queries
• INSERT triggers
• Foreign keys

If you’re interested in learning more about these concepts, check out Educative’s Introductory Guide to SQL. You will learn the basics of everything from creating databases, to queries, to common SQL interview questions. It’s a perfect refresher for any SQL developer.

Happy learning!

Continue reading about SQL#

• What is a Database Foreign Key? A beginner’s tutorial
• What are SQL Joins? Types of SQL joins explained
• What are database schemas? 5 minute guide with examples