Posts

Choosing the Right SQL Server Data Types (and Why It Matters for Performance)

Image
Data types feel like a minor detail when you create a table, and they are expensive to change once the table holds millions of rows. The right types make rows smaller, so more fit in memory, indexes shrink, and queries read less. The wrong ones cause subtle bugs, implicit conversions and wasted memory grants. Text: VARCHAR vs NVARCHAR NVARCHAR stores Unicode, so it handles any language, at about 2 bytes per character for most text. VARCHAR uses 1 byte per character for characters in its code page. From SQL Server 2019, a UTF-8 collation lets VARCHAR store Unicode too, which can save space for mostly-English text. Use NVARCHAR for names, addresses and free text that may contain any language. Use VARCHAR for codes you control, such as EmployeeCode , country codes or status values. Match the type in your application parameters. Comparing a VARCHAR column with an NVARCHAR parameter can force a conversion and an index scan. Realistic lengths, not MAX Do not declare every string as ...
Post a Comment
Read more

Why Foreign Key Columns Need Indexes in SQL Server

Image
Many developers assume that creating a foreign key also creates an index on the column. In SQL Server it does not. A primary key or unique constraint automatically gets an index; a foreign key does not. Missing foreign key indexes are one of the most common causes of slow deletes, slow joins and unexpected blocking. Example CREATE TABLE dbo.PayrollLine ( PayrollLineId BIGINT IDENTITY PRIMARY KEY, PayrollRunId INT NOT NULL CONSTRAINT FK_PayrollLine_PayrollRun REFERENCES dbo.PayrollRun (PayrollRunId), EmployeeId INT NOT NULL CONSTRAINT FK_PayrollLine_Employee REFERENCES dbo.Employee (EmployeeId), Amount DECIMAL(18,2) NOT NULL ); Here PayrollLineId is indexed (primary key), but PayrollRunId and EmployeeId are not. Problem 1: deleting or updating parent rows When you delete a row from dbo.PayrollRun , SQL Server must check that no PayrollLine rows still reference it. Without an index on PayrollLine.PayrollRunId , that check scans the entire ch...
Post a Comment
Read more

SQL Server Columnstore Indexes: Fast Reporting on Large Tables

Image
If your reports aggregate millions of rows, such as totals by month, department or pay code, a columnstore index can make them dramatically faster and the table much smaller. It is one of the most effective features in SQL Server for analytics, as long as you use it for the right workload. Rowstore vs columnstore Normal tables and indexes are rowstore : each page holds complete rows. That is ideal for transactions, which read or write a few whole rows at a time. A columnstore stores each column separately, compressed, in groups of up to about a million rows (row groups). A query that sums Amount by PayCode reads only those two columns and skips the rest. Because values within one column are similar, they compress very well, often to a fraction of the rowstore size. Batch mode Queries on columnstore indexes can run in batch mode , processing rows in batches instead of one at a time, which cuts CPU use sharply for scans, joins and aggregations. From SQL Server 2019 (compatibility leve...
Post a Comment
Read more

SQL Server Blocking: How to Find the Head Blocker and Fix It

Image
Blocking happens when one session holds a lock and another session needs it. A little blocking is normal and clears in milliseconds. The problem is a blocking chain : one session holds locks for a long time, others queue behind it, and then more queue behind them. Users see a frozen application. The key is to find the session at the front of the chain, the head blocker . Step 1: see who is blocked right now SELECT r.session_id, r.blocking_session_id, r.wait_type, r.wait_time / 1000 AS wait_seconds, r.status, DB_NAME(r.database_id) AS database_name, t.text AS query_text FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t WHERE r.blocking_session_id <> 0 ORDER BY r.wait_time DESC; Each row is a session that is waiting. blocking_session_id tells you who it is waiting for, and lock waits show up as LCK_M_* wait types. But the blocker shown here may itself be blocked by someone else. You need the head of the chain. Step 2: find th...
Post a Comment
Read more

SARGable Queries in SQL Server: Why Functions in WHERE Slow You Down

Image
You add the perfect index, run the query, and SQL Server still scans the whole table. One of the most common reasons is a non-SARGable predicate: a WHERE clause written in a way that stops SQL Server from using the index to seek. The fix is usually a small rewrite. What SARGable means SARGable stands for S earch ARG ument- able . A predicate is SARGable when SQL Server can use it to jump straight to the matching rows in an index (an index seek). If you wrap the indexed column in a function or calculation, SQL Server can no longer search the index for a value; it has to calculate the expression for every row, which means a scan. The rule of thumb: leave the column bare on one side of the comparison , and do any calculation on the other side. Common non-SARGable patterns and their fixes Functions on date columns -- Not SARGable: YEAR() runs on every row WHERE YEAR(OrderDate) = 2026 -- SARGable: a range on the bare column WHERE OrderDate >= '2026-01-01' AND OrderDate < ...
Post a Comment
Read more

SQL Server Parameter Sniffing: What It Is and How to Fix It

Image
"The stored procedure was fast yesterday and slow today, and nothing changed." If you have heard that sentence, you have probably met parameter sniffing . It is one of the most common causes of sudden, unexplained slowdowns in SQL Server, and once you understand it, it becomes easy to diagnose. What parameter sniffing is When a parameterised query or stored procedure runs for the first time, SQL Server looks at ("sniffs") the parameter values, builds an execution plan that is optimal for those values, and caches it. Every later call reuses that plan, whatever values it passes. Most of the time this is good: compiling once saves CPU. It becomes a problem when your data is skewed . Imagine an orders table where one large customer has 2 million rows and most customers have 50: CREATE OR ALTER PROCEDURE dbo.GetOrdersByCustomer @CustomerId INT AS SELECT OrderId, OrderDate, Total FROM dbo.Orders WHERE CustomerId = @CustomerId; If the first call is for a small customer...
Post a Comment
Read more

How to Delete or Update Millions of Rows in SQL Server Safely (Batching)

Image
Delete or update millions of rows in SQL Server without blocking users or filling the log: batching scripts, lock escalation and log tips.      Sooner or later you need to delete five years of audit logs or update a column on 30 million rows. Run it as one statement and you can lock the table for an hour, fill the transaction log, block every user and, if you cancel, wait just as long again for the rollback. The fix is simple: do the work in small batches. Why one big statement hurts One huge transaction: every row change is logged and kept until the statement commits, so the transaction log grows and cannot be reused until it finishes. Lock escalation: once a single statement holds roughly 5,000 locks on a table, SQL Server tries to replace them with one table lock. Then nobody else can write to that table. Painful rollback: if you cancel after 40 minutes, SQL Server has to undo everything, which can take just as long. Replicas fall behind: Availability Group seconda...
Post a Comment
Read more