SQL & Cloud Lab

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 ...

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...

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...

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...

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 < ...

"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...

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...

Entity Framework Core is productive, and with a few habits it is also fast. Most "EF Core is slow" problems I have seen in production come from the same handful of patterns, and each one has a simple fix. Here are the eight that matter most. 1. Tracking queries you only read By default, EF Core tracks every entity it loads so it can detect changes. For read-only screens and APIs that is wasted memory and CPU. var employees = await db.Employees .AsNoTracking() .Where(e => e.DepartmentId == deptId) .ToListAsync(); If a whole context is read-only, set QueryTrackingBehavior.NoTracking as its default. 2. The N+1 query problem Loading a list and then touching a navigation property for each item, especially with lazy loading enabled, runs one query for the list plus one per row: // 1 query for orders + 1 query per order for its customer foreach (var order in await db.Orders.ToListAsync()) Console.WriteLine(order.Customer.Name); Load what you need up front with In...

Isolation levels decide what a transaction is allowed to see while other transactions are changing the same data. They are the reason a report can block an order screen, or why NOLOCK sometimes returns rows twice. Understanding them helps you fix blocking without trading it for wrong results. The three problems isolation levels prevent Dirty read: you read a change another transaction has not committed yet, and it may later be rolled back. Non-repeatable read: you read the same row twice in one transaction and get different values, because someone updated it in between. Phantom read: you run the same query twice and new rows appear, because someone inserted rows that match your filter. The isolation levels READ UNCOMMITTED (and NOLOCK) Readers take no shared locks, so they are never blocked by writers. The WITH (NOLOCK) table hint does the same thing for one table. The cost is more than "slightly stale data": you can read uncommitted changes that are later rolled back, a...

SQL Server gives you two ways to hold intermediate results: temporary tables ( #temp ) and table variables ( @table ). They look almost the same, and many developers pick one out of habit. But they behave differently in ways that can turn a 2-second procedure into a 2-minute one. Here is how to choose. The syntax -- Temporary table CREATE TABLE #Employees ( EmployeeId INT PRIMARY KEY, DepartmentId INT NOT NULL ); -- Table variable DECLARE @Employees TABLE ( EmployeeId INT PRIMARY KEY, DepartmentId INT NOT NULL ); A common myth is that table variables live in memory and temp tables live on disk. In fact, both are created in tempdb , and both stay in memory while there is enough of it. The real differences are elsewhere. Difference 1: statistics and row estimates This is the one that matters most for performance. Temp tables get column statistics, just like normal tables, so the optimizer can estimate how many rows a filter or join will return. Table variables do n...

Query Store is like a flight recorder for your database. It keeps a history of the queries that ran, the execution plans they used and how they performed, so you can answer questions such as "what changed since yesterday?" without having caught the problem live. If you use SQL Server 2016 or later, or Azure SQL, it is one of the most valuable features you are probably not using enough. Is Query Store already on? Query Store is enabled by default in Azure SQL Database and for new databases created in SQL Server 2022. Older databases, including those upgraded from earlier versions, may have it off. Check with: SELECT actual_state_desc, desired_state_desc, current_storage_size_mb, max_storage_size_mb, query_capture_mode_desc FROM sys.database_query_store_options; If actual_state_desc shows READ_ONLY when you expected READ_WRITE , the most common reason is that it has reached its maximum size and stopped collecting. Turn it on with sensible settings ALTER DATABASE...

When a query suddenly picks a terrible plan, the root cause is very often statistics . SQL Server's optimizer does not look at your data when it builds a plan; it looks at statistics about your data. If those are missing or out of date, it guesses wrong, and a wrong guess can mean a scan instead of a seek, the wrong join type or a memory spill. What statistics are A statistics object describes how values are distributed in one or more columns. SQL Server creates one automatically for every index, and, with AUTO_CREATE_STATISTICS on, for columns you filter or join on. The optimizer uses them to estimate how many rows each step will return, and those estimates drive almost every plan choice. How to read a statistics object DBCC SHOW_STATISTICS ('dbo.PayrollLine', 'IX_PayrollLine_Run_PayCode'); The output has three parts: Header: when the statistics were last updated, how many rows the table had, and how many rows were sampled . Density vector: how unique the column...

Find the slow queries in SQL Server fast: ready-to-run DMV scripts for running queries, top CPU and reads, wait stats, plus Query Store reports.      Before you can tune anything, you need to know which queries are the problem. Users report "the system is slow", but the cause is usually a handful of queries. SQL Server already tracks everything you need to find them. This guide gives you the scripts I use first, in the order I use them. 1. What is running right now? When the system is slow at this moment, start with live requests. This shows each running query, how long it has run, what it is waiting on and whether it is blocked: SELECT r.session_id, r.status, r.start_time, DATEDIFF(SECOND, r.start_time, SYSDATETIME()) AS running_seconds, r.wait_type, r.wait_time, r.blocking_session_id, r.cpu_time, r.logical_reads, DB_NAME(r.database_id) AS database_name, t.text AS query_text FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_...

When a query is slow, the execution plan tells you exactly what SQL Server did to run it: which indexes it used, how it joined tables and where the time went. Plans can look intimidating, but you only need a few habits to find the problem in most of them. Estimated vs actual plans SQL Server Management Studio (SSMS) can show two kinds of plan: Estimated plan (Ctrl+L): what SQL Server plans to do, without running the query. Useful for queries that are too slow or too risky to run. Actual plan (Ctrl+M, then run the query): the same plan plus what really happened, including actual row counts and warnings. Use this whenever you can. Turn on IO and time statistics at the same time so you have numbers to compare after each change: SET STATISTICS IO, TIME ON; -- run the query with "Include Actual Execution Plan" enabled SET STATISTICS IO, TIME OFF; Read from right to left Data flows from right to left. The operators on the far right read data from tables and indexes; each operato...

A deadlock happens when two sessions each hold a lock the other one needs. Neither can continue, so SQL Server picks one as the victim , rolls its transaction back and raises error 1205 . The other session carries on. Occasional deadlocks are normal in busy systems; frequent ones point to a fixable pattern in your code or indexes. What a deadlock looks like The classic example is two transactions updating the same two rows in opposite order: -- Session 1 BEGIN TRAN; UPDATE dbo.Account SET Balance -= 100 WHERE AccountId = 1; UPDATE dbo.Account SET Balance += 100 WHERE AccountId = 2; COMMIT; -- Session 2 (runs at the same time) BEGIN TRAN; UPDATE dbo.Account SET Balance -= 50 WHERE AccountId = 2; UPDATE dbo.Account SET Balance += 50 WHERE AccountId = 1; COMMIT; Session 1 locks row 1 and waits for row 2. Session 2 locks row 2 and waits for row 1. That cycle is a deadlock. Step 1: Capture the deadlock graph Do not guess. SQL Server records every deadlock in the built-in system_health Ext...

Indexes are the single most effective way to speed up a slow SQL Server query, and also one of the easiest ways to make a database slower if you add them carelessly. This guide covers the core ideas a developer needs to design useful indexes, with scripts you can run today. Clustered vs nonclustered indexes A clustered index defines the order in which the table's rows are stored. A table can have only one, and in most designs it is the primary key. A good clustering key is narrow, unique, static and ever-increasing, such as an INT IDENTITY or BIGINT . A nonclustered index is a separate structure that stores the indexed columns in sorted order plus a pointer back to the row. You can have many of them, and each one speeds up certain reads while adding work to every insert, update and delete. Index the columns you filter and join on Start with the queries that matter most. Look at their WHERE , JOIN and ORDER BY clauses: those columns are your index candidates. -- Query we want t...

Azure SQL is easy to start and easy to overspend on. Most teams size their databases once, during migration, and never look again, so they keep paying for capacity they do not use. The good news is that the biggest savings usually come from a handful of changes that take hours, not weeks. This checklist walks through those changes in the order I would apply them: measure first, then fix licensing and commitments, then architecture, then queries. 1. Right-size from real usage, not from the old server The most common mistake is sizing Azure to match the on-premises server's hardware. Old servers were bought for peak load plus growth, and often sat at 10 to 20 percent CPU. Check at least two to four weeks of CPU, data IO, log IO and memory usage. In Azure SQL Database, sys.dm_db_resource_stats shows usage for roughly the last hour at 15-second intervals, and sys.resource_stats in the master database keeps longer history. If average usage stays low and peaks are short, scale down on...