<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom" xmlns:dc="http://purl.org/dc/elements/1.1/">
  <channel>
    <title>DEV Community: LingamSuyambu</title>
    <description>The latest articles on DEV Community by LingamSuyambu (@lingamsuyambu).</description>
    <link>https://dev.to/lingamsuyambu</link>
    <image>
      <url>https://media2.dev.to/dynamic/image/width=90,height=90,fit=cover,gravity=auto,format=auto/https:%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Fuser%2Fprofile_image%2F4215876%2F22af5bb9-86c8-4459-b5b6-4bb8f288078c.png</url>
      <title>DEV Community: LingamSuyambu</title>
      <link>https://dev.to/lingamsuyambu</link>
    </image>
    <atom:link rel="self" type="application/rss+xml" href="https://dev.to/feed/lingamsuyambu"/>
    <language>en</language>
    <item>
      <title>SQL Server Columnstore Indexes: Fast Reporting on Large Tables</title>
      <dc:creator>LingamSuyambu</dc:creator>
      <pubDate>Fri, 20 Nov 2026 04:30:00 +0000</pubDate>
      <link>https://dev.to/lingamsuyambu/sql-server-columnstore-indexes-fast-reporting-on-large-tables-f3j</link>
      <guid>https://dev.to/lingamsuyambu/sql-server-columnstore-indexes-fast-reporting-on-large-tables-f3j</guid>
      <description>&lt;p&gt;&lt;em&gt;Originally published at &lt;a href="https://sqlcloudlab.blogspot.com/2026/10/sql-server-columnstore-indexes-fast.html" rel="noopener noreferrer"&gt;SQL &amp;amp; Cloud Lab&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

&lt;p&gt;&lt;em&gt;This post was drafted with AI assistance.&lt;/em&gt;&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto/https%3A%2F%2Fblogger.googleusercontent.com%2Fimg%2Fb%2FR29vZ2xl%2FAVvXsEhm-cfD45wYxTGKLz33Z9lkpB8_XhbnLFC7AnTSQ0bCsyV4fED7l2fBG1etl5SnLGE_bsSIFh-geA0ZZhkFVJ06NG_RXrn5YlagDHFfqfNY8tPOWHOXfxdY7m6KdlSE6GQWUwCzVASIQRpwdGp-OSrjGZ03EDgugEiyZhUEh93YGaTmfquD6xWxKmIJ3Cs%2Fs1600%2Fimage.webp" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto/https%3A%2F%2Fblogger.googleusercontent.com%2Fimg%2Fb%2FR29vZ2xl%2FAVvXsEhm-cfD45wYxTGKLz33Z9lkpB8_XhbnLFC7AnTSQ0bCsyV4fED7l2fBG1etl5SnLGE_bsSIFh-geA0ZZhkFVJ06NG_RXrn5YlagDHFfqfNY8tPOWHOXfxdY7m6KdlSE6GQWUwCzVASIQRpwdGp-OSrjGZ03EDgugEiyZhUEh93YGaTmfquD6xWxKmIJ3Cs%2Fs1600%2Fimage.webp" title="SQL Server columnstore indexes for reporting" alt="SQL Server columnstore indexes for reporting" width="800" height="420"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;h2&gt;
  
  
  Rowstore vs columnstore
&lt;/h2&gt;

&lt;p&gt;Normal tables and indexes are &lt;strong&gt;rowstore&lt;/strong&gt;: each page holds complete rows. That is ideal for transactions, which read or write a few whole rows at a time.&lt;/p&gt;

&lt;p&gt;A &lt;strong&gt;columnstore&lt;/strong&gt; stores each column separately, compressed, in groups of up to about a million rows (row groups). A query that sums &lt;code&gt;Amount&lt;/code&gt; by &lt;code&gt;PayCode&lt;/code&gt; 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.&lt;/p&gt;

&lt;h2&gt;
  
  
  Batch mode
&lt;/h2&gt;

&lt;p&gt;Queries on columnstore indexes can run in &lt;strong&gt;batch mode&lt;/strong&gt;, 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 level 150), batch mode can also be used on rowstore tables in some analytic queries.&lt;/p&gt;

&lt;h2&gt;
  
  
  Clustered columnstore: for fact tables
&lt;/h2&gt;

&lt;p&gt;A &lt;strong&gt;clustered columnstore index&lt;/strong&gt; (CCI) makes the whole table columnar. It suits large, append-mostly tables used for reporting, such as a payroll history or sales fact table:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="n"&gt;CLUSTERED&lt;/span&gt; &lt;span class="n"&gt;COLUMNSTORE&lt;/span&gt; &lt;span class="k"&gt;INDEX&lt;/span&gt; &lt;span class="n"&gt;CCI_PayrollHistory&lt;/span&gt;
&lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="n"&gt;dbo&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;PayrollHistory&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;You can still add rowstore nonclustered indexes on top of a CCI to support a few selective lookups.&lt;/p&gt;

&lt;h2&gt;
  
  
  Nonclustered columnstore: analytics on an OLTP table
&lt;/h2&gt;

&lt;p&gt;A &lt;strong&gt;nonclustered columnstore index&lt;/strong&gt; (NCCI) adds a columnar copy of selected columns to a normal transactional table, so reports can run against live data without a separate reporting database:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="n"&gt;NONCLUSTERED&lt;/span&gt; &lt;span class="n"&gt;COLUMNSTORE&lt;/span&gt; &lt;span class="k"&gt;INDEX&lt;/span&gt; &lt;span class="n"&gt;NCCI_PayrollLine_Reporting&lt;/span&gt;
&lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="n"&gt;dbo&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;PayrollLine&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;PayrollRunId&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;EmployeeId&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;PayCode&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;Amount&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;CreatedAt&lt;/span&gt;&lt;span class="p"&gt;);&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Writes become slightly more expensive, because the extra index must be maintained, so test the impact on busy transactional tables.&lt;/p&gt;

&lt;h2&gt;
  
  
  Load data in large batches
&lt;/h2&gt;

&lt;p&gt;New rows first go into a rowstore &lt;strong&gt;delta store&lt;/strong&gt; and are compressed into columnstore row groups later. Bulk inserts of 102,400 rows or more at a time can go straight into compressed row groups, which is faster and gives better compression. Many small inserts leave lots of uncompressed delta rows. You can check and tidy them:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;state_desc&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="k"&gt;COUNT&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="o"&gt;*&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;row_groups&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="k"&gt;SUM&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;total_rows&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;total_rows&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;sys&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;dm_db_column_store_row_group_physical_stats&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;object_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;OBJECT_ID&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s1"&gt;'dbo.PayrollHistory'&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;GROUP&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;state_desc&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;

&lt;span class="k"&gt;ALTER&lt;/span&gt; &lt;span class="k"&gt;INDEX&lt;/span&gt; &lt;span class="n"&gt;CCI_PayrollHistory&lt;/span&gt; &lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="n"&gt;dbo&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;PayrollHistory&lt;/span&gt;
&lt;span class="n"&gt;REORGANIZE&lt;/span&gt; &lt;span class="k"&gt;WITH&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;COMPRESS_ALL_ROW_GROUPS&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="k"&gt;ON&lt;/span&gt;&lt;span class="p"&gt;);&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h2&gt;
  
  
  Segment elimination
&lt;/h2&gt;

&lt;p&gt;Each column segment stores its minimum and maximum values. If data is loaded roughly in date order, a query for one month can skip row groups that cannot contain that month. Loading in a sensible order, or partitioning by date, makes this work well.&lt;/p&gt;

&lt;h2&gt;
  
  
  When not to use columnstore
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;Tables where the main access pattern is fetching or updating single rows by key.&lt;/li&gt;
&lt;li&gt;Small tables, where the overhead is not worth it.&lt;/li&gt;
&lt;li&gt;Tables with very frequent updates and deletes to existing rows; these are stored as deleted markers plus new rows and degrade compression over time.&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Quick checklist
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;Use a clustered columnstore for large, append-mostly reporting tables.&lt;/li&gt;
&lt;li&gt;Use a nonclustered columnstore for real-time analytics on OLTP tables.&lt;/li&gt;
&lt;li&gt;Load in batches of 102,400+ rows and monitor the delta store.&lt;/li&gt;
&lt;li&gt;Load or partition by date to benefit from segment elimination.&lt;/li&gt;
&lt;li&gt;Keep rowstore indexes for single-row lookups.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;Related reading:&lt;/strong&gt;&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;&lt;a href="https://sqlcloudlab.blogspot.com/2026/10/sql-server-index-tuning-for-beginners.html" rel="noopener noreferrer"&gt;SQL Server Index Tuning for Beginners&lt;/a&gt;&lt;/li&gt;
&lt;li&gt;&lt;a href="https://sqlcloudlab.blogspot.com/2026/10/temp-tables-vs-table-variables-in-sql.html" rel="noopener noreferrer"&gt;Temp Tables vs Table Variables in SQL Server&lt;/a&gt;&lt;/li&gt;
&lt;li&gt;&lt;a href="https://sqlcloudlab.blogspot.com/2026/10/sql-server-statistics-explained-why.html" rel="noopener noreferrer"&gt;SQL Server Statistics Explained&lt;/a&gt;&lt;/li&gt;
&lt;/ul&gt;




&lt;p&gt;&lt;em&gt;More SQL Server guides on my blog, &lt;a href="https://sqlcloudlab.blogspot.com/" rel="noopener noreferrer"&gt;SQL &amp;amp; Cloud Lab&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>sql</category>
      <category>sqlserver</category>
      <category>database</category>
      <category>performance</category>
    </item>
    <item>
      <title>How to Delete or Update Millions of Rows in SQL Server Safely (Batching)</title>
      <dc:creator>LingamSuyambu</dc:creator>
      <pubDate>Thu, 19 Nov 2026 04:30:00 +0000</pubDate>
      <link>https://dev.to/lingamsuyambu/how-to-delete-or-update-millions-of-rows-in-sql-server-safely-batching-1jd6</link>
      <guid>https://dev.to/lingamsuyambu/how-to-delete-or-update-millions-of-rows-in-sql-server-safely-batching-1jd6</guid>
      <description>&lt;p&gt;&lt;em&gt;Originally published at &lt;a href="https://sqlcloudlab.blogspot.com/2026/10/how-to-delete-or-update-millions-of.html" rel="noopener noreferrer"&gt;SQL &amp;amp; Cloud Lab&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

&lt;p&gt;&lt;em&gt;This post was drafted with AI assistance.&lt;/em&gt;&lt;/p&gt;

&lt;p&gt;Delete or update millions of rows in SQL Server without blocking users or filling the log: batching scripts, lock escalation and log tips.&amp;nbsp;&amp;nbsp; &amp;nbsp;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.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto/https%3A%2F%2Fblogger.googleusercontent.com%2Fimg%2Fb%2FR29vZ2xl%2FAVvXsEi-0kHfSqEBQNr7FSIhFOSlfAgdlUz0EDGeemOUzbz7o3SZRiyrQIVkCZKnUsf4ZagWc77NSMBvUuQh2p24YuOIa5VTdI_5zHNcWMKWpS72mcJ97GGcn_zRVhvCKa5IreW7wendWK1IU1tFmKIO2d9qZv8Yh_gevzkX1B44Kdc30Z0eACsw7sG7iHvM0pw%2Fs1600%2Fimage.webp" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto/https%3A%2F%2Fblogger.googleusercontent.com%2Fimg%2Fb%2FR29vZ2xl%2FAVvXsEi-0kHfSqEBQNr7FSIhFOSlfAgdlUz0EDGeemOUzbz7o3SZRiyrQIVkCZKnUsf4ZagWc77NSMBvUuQh2p24YuOIa5VTdI_5zHNcWMKWpS72mcJ97GGcn_zRVhvCKa5IreW7wendWK1IU1tFmKIO2d9qZv8Yh_gevzkX1B44Kdc30Z0eACsw7sG7iHvM0pw%2Fs1600%2Fimage.webp" title="How to batch large deletes and updates in SQL Server" alt="How to batch large deletes and updates in SQL Server" width="800" height="420"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;h2&gt;
  
  
  Why one big statement hurts
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;One huge transaction:&lt;/strong&gt; every row change is logged and kept until the statement commits, so the transaction log grows and cannot be reused until it finishes.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Lock escalation:&lt;/strong&gt; 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.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Painful rollback:&lt;/strong&gt; if you cancel after 40 minutes, SQL Server has to undo everything, which can take just as long.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Replicas fall behind:&lt;/strong&gt; Availability Group secondaries and replication must replay the whole change.&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Step 1: make sure the filter is indexed
&lt;/h2&gt;

&lt;p&gt;Each batch has to find its next set of rows quickly. If the &lt;code&gt;WHERE&lt;/code&gt; clause has no supporting index, every batch scans the table and the whole job gets slower as it goes. For example, deleting old audit rows by date needs an index on the date column:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="n"&gt;NONCLUSTERED&lt;/span&gt; &lt;span class="k"&gt;INDEX&lt;/span&gt; &lt;span class="n"&gt;IX_AuditLog_CreatedAt&lt;/span&gt;
&lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="n"&gt;dbo&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;AuditLog&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;CreatedAt&lt;/span&gt;&lt;span class="p"&gt;);&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h2&gt;
  
  
  Step 2: delete in batches
&lt;/h2&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;DECLARE&lt;/span&gt; &lt;span class="o"&gt;@&lt;/span&gt;&lt;span class="n"&gt;Cutoff&lt;/span&gt;    &lt;span class="n"&gt;DATETIME2&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;'2021-01-01'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
        &lt;span class="o"&gt;@&lt;/span&gt;&lt;span class="n"&gt;BatchSize&lt;/span&gt; &lt;span class="nb"&gt;INT&lt;/span&gt;       &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;4000&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
        &lt;span class="o"&gt;@&lt;/span&gt;&lt;span class="k"&gt;Rows&lt;/span&gt;      &lt;span class="nb"&gt;INT&lt;/span&gt;       &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;

&lt;span class="n"&gt;WHILE&lt;/span&gt; &lt;span class="o"&gt;@&lt;/span&gt;&lt;span class="k"&gt;Rows&lt;/span&gt; &lt;span class="o"&gt;&amp;gt;&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;
&lt;span class="k"&gt;BEGIN&lt;/span&gt;
    &lt;span class="k"&gt;DELETE&lt;/span&gt; &lt;span class="n"&gt;TOP&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="o"&gt;@&lt;/span&gt;&lt;span class="n"&gt;BatchSize&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;dbo&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;AuditLog&lt;/span&gt;
    &lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;CreatedAt&lt;/span&gt; &lt;span class="o"&gt;&amp;lt;&lt;/span&gt; &lt;span class="o"&gt;@&lt;/span&gt;&lt;span class="n"&gt;Cutoff&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;

    &lt;span class="k"&gt;SET&lt;/span&gt; &lt;span class="o"&gt;@&lt;/span&gt;&lt;span class="k"&gt;Rows&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="o"&gt;@@&lt;/span&gt;&lt;span class="n"&gt;ROWCOUNT&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;

    &lt;span class="n"&gt;WAITFOR&lt;/span&gt; &lt;span class="n"&gt;DELAY&lt;/span&gt; &lt;span class="s1"&gt;'00:00:00.200'&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;   &lt;span class="c1"&gt;-- give other sessions room to work&lt;/span&gt;
&lt;span class="k"&gt;END&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;ul&gt;
&lt;li&gt;Each &lt;code&gt;DELETE&lt;/code&gt; is its own short transaction, so locks are released quickly and the log space can be reused.&lt;/li&gt;
&lt;li&gt;A batch size below about 5,000 rows usually avoids lock escalation. Start there, then tune: bigger batches finish faster, smaller ones block less.&lt;/li&gt;
&lt;li&gt;Do &lt;strong&gt;not&lt;/strong&gt; wrap the loop in &lt;code&gt;BEGIN TRAN ... COMMIT&lt;/code&gt;; that turns it back into one giant transaction.&lt;/li&gt;
&lt;li&gt;The job can be stopped and restarted safely; it simply picks up the rows that are left.&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Step 3: update in batches by walking the key
&lt;/h2&gt;

&lt;p&gt;Updates are trickier because the updated rows may still match the filter. The cleanest approach is to walk through the clustered key in ranges:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;DECLARE&lt;/span&gt; &lt;span class="o"&gt;@&lt;/span&gt;&lt;span class="n"&gt;BatchSize&lt;/span&gt; &lt;span class="nb"&gt;BIGINT&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;5000&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
        &lt;span class="o"&gt;@&lt;/span&gt;&lt;span class="n"&gt;StartId&lt;/span&gt;   &lt;span class="nb"&gt;BIGINT&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
        &lt;span class="o"&gt;@&lt;/span&gt;&lt;span class="n"&gt;MaxId&lt;/span&gt;     &lt;span class="nb"&gt;BIGINT&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;

&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="o"&gt;@&lt;/span&gt;&lt;span class="n"&gt;MaxId&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="k"&gt;MAX&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;PayrollLineId&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;dbo&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;PayrollLine&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;

&lt;span class="n"&gt;WHILE&lt;/span&gt; &lt;span class="o"&gt;@&lt;/span&gt;&lt;span class="n"&gt;StartId&lt;/span&gt; &lt;span class="o"&gt;&amp;lt;&lt;/span&gt; &lt;span class="o"&gt;@&lt;/span&gt;&lt;span class="n"&gt;MaxId&lt;/span&gt;
&lt;span class="k"&gt;BEGIN&lt;/span&gt;
    &lt;span class="k"&gt;UPDATE&lt;/span&gt; &lt;span class="n"&gt;dbo&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;PayrollLine&lt;/span&gt;
    &lt;span class="k"&gt;SET&lt;/span&gt;    &lt;span class="n"&gt;PayCode&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;'BASIC'&lt;/span&gt;
    &lt;span class="k"&gt;WHERE&lt;/span&gt;  &lt;span class="n"&gt;PayrollLineId&lt;/span&gt; &lt;span class="o"&gt;&amp;gt;&lt;/span&gt; &lt;span class="o"&gt;@&lt;/span&gt;&lt;span class="n"&gt;StartId&lt;/span&gt;
      &lt;span class="k"&gt;AND&lt;/span&gt;  &lt;span class="n"&gt;PayrollLineId&lt;/span&gt; &lt;span class="o"&gt;&amp;lt;=&lt;/span&gt; &lt;span class="o"&gt;@&lt;/span&gt;&lt;span class="n"&gt;StartId&lt;/span&gt; &lt;span class="o"&gt;+&lt;/span&gt; &lt;span class="o"&gt;@&lt;/span&gt;&lt;span class="n"&gt;BatchSize&lt;/span&gt;
      &lt;span class="k"&gt;AND&lt;/span&gt;  &lt;span class="n"&gt;PayCode&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;'BAS'&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;

    &lt;span class="k"&gt;SET&lt;/span&gt; &lt;span class="o"&gt;@&lt;/span&gt;&lt;span class="n"&gt;StartId&lt;/span&gt; &lt;span class="o"&gt;+=&lt;/span&gt; &lt;span class="o"&gt;@&lt;/span&gt;&lt;span class="n"&gt;BatchSize&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;span class="k"&gt;END&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Each batch uses a fast range seek on the clustered key, and progress is easy to track. If you need to resume after stopping, record &lt;code&gt;@StartId&lt;/code&gt; in a small control table.&lt;/p&gt;

&lt;h2&gt;
  
  
  Step 4: keep the transaction log under control
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;In the &lt;strong&gt;FULL&lt;/strong&gt; recovery model, log space is only reused after a log backup. Make sure log backups keep running during the job, or schedule extra ones.&lt;/li&gt;
&lt;li&gt;In the &lt;strong&gt;SIMPLE&lt;/strong&gt; recovery model, space is reused after each checkpoint, so short batches keep the log small on their own.&lt;/li&gt;
&lt;li&gt;Watch free log space while the job runs, for example with &lt;code&gt;sys.dm_db_log_space_usage&lt;/code&gt;.&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Step 5: run it gently
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;Run large jobs outside business hours where possible.&lt;/li&gt;
&lt;li&gt;Watch for blocking while it runs, and lower the batch size or add a longer delay if users are affected.&lt;/li&gt;
&lt;li&gt;Print or log progress so you know how far it has got.&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Faster alternatives when they fit
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;TRUNCATE TABLE&lt;/strong&gt; removes every row almost instantly, but cannot filter and has restrictions such as foreign keys.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Copy and swap:&lt;/strong&gt; when you are deleting most of a table, it can be faster to copy the rows you keep into a new table, then swap names in a short maintenance window.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Partition switching:&lt;/strong&gt; on a partitioned table, you can switch out or truncate an entire old partition almost instantly. This is the best long-term design for tables that grow by date.&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Quick checklist
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;Index the filter column first.&lt;/li&gt;
&lt;li&gt;Use batches of a few thousand rows, each in its own transaction.&lt;/li&gt;
&lt;li&gt;Walk the clustered key for large updates.&lt;/li&gt;
&lt;li&gt;Keep log backups running and watch log space.&lt;/li&gt;
&lt;li&gt;Monitor blocking and adjust batch size and delay.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;Related reading:&lt;/strong&gt;&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;&lt;a href="https://sqlcloudlab.blogspot.com/2026/10/how-to-fix-sql-server-deadlocks-causes.html" rel="noopener noreferrer"&gt;How to Fix SQL Server Deadlocks&lt;/a&gt;&lt;/li&gt;
&lt;li&gt;&lt;a href="https://sqlcloudlab.blogspot.com/2026/10/sql-server-isolation-levels-explained.html" rel="noopener noreferrer"&gt;SQL Server Isolation Levels Explained&lt;/a&gt;&lt;/li&gt;
&lt;li&gt;&lt;a href="https://sqlcloudlab.blogspot.com/2026/10/sql-server-index-tuning-for-beginners.html" rel="noopener noreferrer"&gt;SQL Server Index Tuning for Beginners&lt;/a&gt;&lt;/li&gt;
&lt;/ul&gt;




&lt;p&gt;&lt;em&gt;More SQL Server guides on my blog, &lt;a href="https://sqlcloudlab.blogspot.com/" rel="noopener noreferrer"&gt;SQL &amp;amp; Cloud Lab&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>sql</category>
      <category>sqlserver</category>
      <category>database</category>
      <category>performance</category>
    </item>
    <item>
      <title>Entity Framework Core Performance: 8 Common Mistakes and How to Fix Them</title>
      <dc:creator>LingamSuyambu</dc:creator>
      <pubDate>Wed, 18 Nov 2026 04:30:00 +0000</pubDate>
      <link>https://dev.to/lingamsuyambu/entity-framework-core-performance-8-common-mistakes-and-how-to-fix-them-3o45</link>
      <guid>https://dev.to/lingamsuyambu/entity-framework-core-performance-8-common-mistakes-and-how-to-fix-them-3o45</guid>
      <description>&lt;p&gt;&lt;em&gt;Originally published at &lt;a href="https://sqlcloudlab.blogspot.com/2026/10/entity-framework-core-performance-8.html" rel="noopener noreferrer"&gt;SQL &amp;amp; Cloud Lab&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

&lt;p&gt;&lt;em&gt;This post was drafted with AI assistance.&lt;/em&gt;&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto/https%3A%2F%2Fblogger.googleusercontent.com%2Fimg%2Fb%2FR29vZ2xl%2FAVvXsEjqFpxmv8d59Oc15ibVFuiz4zZhRZomS8y7aLT5OGHr10exEiwNqIz0Tl8KceECwcXIWU0tyDcbIL_umvtDP6RWV4py3hF0B4S3aCrbE-BU7cuzSGZgQ7mhMfyBIYvrE00pD7YPE3BB6dubafHG9rRwUK0Z-3KT4-sHxihvYcplmF5jdxusZDALMSuKero%2Fs1600%2Fimage.webp" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto/https%3A%2F%2Fblogger.googleusercontent.com%2Fimg%2Fb%2FR29vZ2xl%2FAVvXsEjqFpxmv8d59Oc15ibVFuiz4zZhRZomS8y7aLT5OGHr10exEiwNqIz0Tl8KceECwcXIWU0tyDcbIL_umvtDP6RWV4py3hF0B4S3aCrbE-BU7cuzSGZgQ7mhMfyBIYvrE00pD7YPE3BB6dubafHG9rRwUK0Z-3KT4-sHxihvYcplmF5jdxusZDALMSuKero%2Fs1600%2Fimage.webp" title="Entity Framework Core performance mistakes" alt="Entity Framework Core performance mistakes" width="800" height="420"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;h2&gt;
  
  
  1. Tracking queries you only read
&lt;/h2&gt;

&lt;p&gt;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.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight csharp"&gt;&lt;code&gt;&lt;span class="kt"&gt;var&lt;/span&gt; &lt;span class="n"&gt;employees&lt;/span&gt; &lt;span class="p"&gt;=&lt;/span&gt; &lt;span class="k"&gt;await&lt;/span&gt; &lt;span class="n"&gt;db&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;Employees&lt;/span&gt;
    &lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;AsNoTracking&lt;/span&gt;&lt;span class="p"&gt;()&lt;/span&gt;
    &lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;Where&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;e&lt;/span&gt; &lt;span class="p"&gt;=&amp;gt;&lt;/span&gt; &lt;span class="n"&gt;e&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;DepartmentId&lt;/span&gt; &lt;span class="p"&gt;==&lt;/span&gt; &lt;span class="n"&gt;deptId&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;ToListAsync&lt;/span&gt;&lt;span class="p"&gt;();&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;If a whole context is read-only, set &lt;code&gt;QueryTrackingBehavior.NoTracking&lt;/code&gt; as its default.&lt;/p&gt;

&lt;h2&gt;
  
  
  2. The N+1 query problem
&lt;/h2&gt;

&lt;p&gt;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:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight csharp"&gt;&lt;code&gt;&lt;span class="c1"&gt;// 1 query for orders + 1 query per order for its customer&lt;/span&gt;
&lt;span class="k"&gt;foreach&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="kt"&gt;var&lt;/span&gt; &lt;span class="n"&gt;order&lt;/span&gt; &lt;span class="k"&gt;in&lt;/span&gt; &lt;span class="k"&gt;await&lt;/span&gt; &lt;span class="n"&gt;db&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;Orders&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;ToListAsync&lt;/span&gt;&lt;span class="p"&gt;())&lt;/span&gt;
    &lt;span class="n"&gt;Console&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;WriteLine&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;order&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;Customer&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;Name&lt;/span&gt;&lt;span class="p"&gt;);&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Load what you need up front with &lt;code&gt;Include&lt;/code&gt;, or better, project only the fields you need (see mistake 4).&lt;/p&gt;

&lt;h2&gt;
  
  
  3. Cartesian explosion from multiple Include calls
&lt;/h2&gt;

&lt;p&gt;Including two or more collections in one query joins them all together, so the number of rows returned can multiply dramatically:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight csharp"&gt;&lt;code&gt;&lt;span class="kt"&gt;var&lt;/span&gt; &lt;span class="n"&gt;orders&lt;/span&gt; &lt;span class="p"&gt;=&lt;/span&gt; &lt;span class="k"&gt;await&lt;/span&gt; &lt;span class="n"&gt;db&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;Orders&lt;/span&gt;
    &lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;Include&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;o&lt;/span&gt; &lt;span class="p"&gt;=&amp;gt;&lt;/span&gt; &lt;span class="n"&gt;o&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;Lines&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;Include&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;o&lt;/span&gt; &lt;span class="p"&gt;=&amp;gt;&lt;/span&gt; &lt;span class="n"&gt;o&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;Payments&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;AsSplitQuery&lt;/span&gt;&lt;span class="p"&gt;()&lt;/span&gt;          &lt;span class="c1"&gt;// one SQL query per collection&lt;/span&gt;
    &lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;ToListAsync&lt;/span&gt;&lt;span class="p"&gt;();&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;code&gt;AsSplitQuery()&lt;/code&gt; runs a separate query per collection instead of one huge join. Measure both ways: split queries mean more round trips, so they help most when collections are large.&lt;/p&gt;

&lt;h2&gt;
  
  
  4. Loading whole entities when you need three columns
&lt;/h2&gt;

&lt;p&gt;Selecting full entities pulls every column, including large text fields, and tracks them. Project into a small type instead:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="n"&gt;var&lt;/span&gt; &lt;span class="n"&gt;list&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;await&lt;/span&gt; &lt;span class="n"&gt;db&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;Employees&lt;/span&gt;
    &lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="k"&gt;Where&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;e&lt;/span&gt; &lt;span class="o"&gt;=&amp;gt;&lt;/span&gt; &lt;span class="n"&gt;e&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;IsActive&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="k"&gt;Select&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;e&lt;/span&gt; &lt;span class="o"&gt;=&amp;gt;&lt;/span&gt; &lt;span class="k"&gt;new&lt;/span&gt; &lt;span class="n"&gt;EmployeeListItem&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;e&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;EmployeeId&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;e&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;FullName&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;e&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;Department&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;Name&lt;/span&gt;&lt;span class="p"&gt;))&lt;/span&gt;
    &lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;ToListAsync&lt;/span&gt;&lt;span class="p"&gt;();&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Projections are not tracked, read fewer columns, and let SQL Server use narrower covering indexes.&lt;/p&gt;

&lt;h2&gt;
  
  
  5. Filtering in memory instead of in SQL
&lt;/h2&gt;

&lt;p&gt;Calling &lt;code&gt;ToList()&lt;/code&gt;, &lt;code&gt;AsEnumerable()&lt;/code&gt; or &lt;code&gt;ToListAsync()&lt;/code&gt; too early loads the whole table and filters in C#:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight csharp"&gt;&lt;code&gt;&lt;span class="c1"&gt;// Bad: loads every employee, then filters in memory&lt;/span&gt;
&lt;span class="kt"&gt;var&lt;/span&gt; &lt;span class="n"&gt;bad&lt;/span&gt; &lt;span class="p"&gt;=&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;await&lt;/span&gt; &lt;span class="n"&gt;db&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;Employees&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;ToListAsync&lt;/span&gt;&lt;span class="p"&gt;())&lt;/span&gt;
          &lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;Where&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;e&lt;/span&gt; &lt;span class="p"&gt;=&amp;gt;&lt;/span&gt; &lt;span class="n"&gt;e&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;HireDate&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;Year&lt;/span&gt; &lt;span class="p"&gt;==&lt;/span&gt; &lt;span class="m"&gt;2026&lt;/span&gt;&lt;span class="p"&gt;);&lt;/span&gt;

&lt;span class="c1"&gt;// Good: filter runs in SQL&lt;/span&gt;
&lt;span class="kt"&gt;var&lt;/span&gt; &lt;span class="n"&gt;good&lt;/span&gt; &lt;span class="p"&gt;=&lt;/span&gt; &lt;span class="k"&gt;await&lt;/span&gt; &lt;span class="n"&gt;db&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;Employees&lt;/span&gt;
    &lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;Where&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;e&lt;/span&gt; &lt;span class="p"&gt;=&amp;gt;&lt;/span&gt; &lt;span class="n"&gt;e&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;HireDate&lt;/span&gt; &lt;span class="p"&gt;&amp;gt;=&lt;/span&gt; &lt;span class="k"&gt;new&lt;/span&gt; &lt;span class="nf"&gt;DateOnly&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="m"&gt;2026&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="m"&gt;1&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="m"&gt;1&lt;/span&gt;&lt;span class="p"&gt;))&lt;/span&gt;
    &lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;ToListAsync&lt;/span&gt;&lt;span class="p"&gt;();&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;The second version also avoids wrapping the column in a function, so SQL Server can use an index on &lt;code&gt;HireDate&lt;/code&gt;.&lt;/p&gt;

&lt;h2&gt;
  
  
  6. Calling SaveChanges in a loop
&lt;/h2&gt;

&lt;p&gt;Each &lt;code&gt;SaveChanges&lt;/code&gt; call is a round trip and its own transaction. Add or update everything first, then save once (or in batches of a few thousand for very large imports):&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight csharp"&gt;&lt;code&gt;&lt;span class="k"&gt;foreach&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="kt"&gt;var&lt;/span&gt; &lt;span class="n"&gt;item&lt;/span&gt; &lt;span class="k"&gt;in&lt;/span&gt; &lt;span class="n"&gt;items&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="n"&gt;db&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;PayrollLines&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;Add&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;item&lt;/span&gt;&lt;span class="p"&gt;);&lt;/span&gt;

&lt;span class="k"&gt;await&lt;/span&gt; &lt;span class="n"&gt;db&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;SaveChangesAsync&lt;/span&gt;&lt;span class="p"&gt;();&lt;/span&gt;   &lt;span class="c1"&gt;// EF Core batches the INSERTs&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h2&gt;
  
  
  7. Loading rows just to update or delete them
&lt;/h2&gt;

&lt;p&gt;For bulk changes, loading entities into memory only to modify and save them is slow. Since EF Core 7, &lt;code&gt;ExecuteUpdate&lt;/code&gt; and &lt;code&gt;ExecuteDelete&lt;/code&gt; run a single SQL statement directly:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="n"&gt;await&lt;/span&gt; &lt;span class="n"&gt;db&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;Employees&lt;/span&gt;
    &lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="k"&gt;Where&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;e&lt;/span&gt; &lt;span class="o"&gt;=&amp;gt;&lt;/span&gt; &lt;span class="n"&gt;e&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;DepartmentId&lt;/span&gt; &lt;span class="o"&gt;==&lt;/span&gt; &lt;span class="n"&gt;oldDeptId&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;ExecuteUpdateAsync&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;s&lt;/span&gt; &lt;span class="o"&gt;=&amp;gt;&lt;/span&gt; &lt;span class="n"&gt;s&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;SetProperty&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;e&lt;/span&gt; &lt;span class="o"&gt;=&amp;gt;&lt;/span&gt; &lt;span class="n"&gt;e&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;DepartmentId&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;newDeptId&lt;/span&gt;&lt;span class="p"&gt;));&lt;/span&gt;

&lt;span class="n"&gt;await&lt;/span&gt; &lt;span class="n"&gt;db&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;AuditLogs&lt;/span&gt;
    &lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="k"&gt;Where&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;a&lt;/span&gt; &lt;span class="o"&gt;=&amp;gt;&lt;/span&gt; &lt;span class="n"&gt;a&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;CreatedAt&lt;/span&gt; &lt;span class="o"&gt;&amp;lt;&lt;/span&gt; &lt;span class="n"&gt;cutoff&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;ExecuteDeleteAsync&lt;/span&gt;&lt;span class="p"&gt;();&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;These bypass the change tracker, so entities already loaded in the context will not reflect the change. For very large deletes, batch them to avoid long locks.&lt;/p&gt;

&lt;h2&gt;
  
  
  8. Never looking at the generated SQL
&lt;/h2&gt;

&lt;p&gt;You cannot tune what you cannot see. Log the SQL EF Core sends, at least in development:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="n"&gt;optionsBuilder&lt;/span&gt;
    &lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;UseSqlServer&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;connectionString&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;LogTo&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;Console&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;WriteLine&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;LogLevel&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;Information&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;EnableSensitiveDataLogging&lt;/span&gt;&lt;span class="p"&gt;();&lt;/span&gt;   &lt;span class="o"&gt;//&lt;/span&gt; &lt;span class="n"&gt;development&lt;/span&gt; &lt;span class="k"&gt;only&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="n"&gt;shows&lt;/span&gt; &lt;span class="k"&gt;parameter&lt;/span&gt; &lt;span class="k"&gt;values&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;You can also call &lt;code&gt;ToQueryString()&lt;/code&gt; on any LINQ query to see its SQL. Then take the slow statements to SQL Server and check their execution plans like any other query.&lt;/p&gt;

&lt;h2&gt;
  
  
  Bonus tips
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;Use &lt;code&gt;AnyAsync()&lt;/code&gt; instead of &lt;code&gt;CountAsync() &amp;gt; 0&lt;/code&gt; to check existence.&lt;/li&gt;
&lt;li&gt;Use &lt;code&gt;AddDbContextPool&lt;/code&gt; in high-throughput web apps to reuse context instances.&lt;/li&gt;
&lt;li&gt;Use &lt;code&gt;FromSql&lt;/code&gt; with interpolated parameters (not string concatenation) for raw SQL, to stay safe from SQL injection.&lt;/li&gt;
&lt;li&gt;Make sure your indexes match the filters EF Core generates.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;Related reading:&lt;/strong&gt;&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;&lt;a href="https://sqlcloudlab.blogspot.com/2026/10/sql-server-index-tuning-for-beginners.html" rel="noopener noreferrer"&gt;SQL Server Index Tuning for Beginners&lt;/a&gt;&lt;/li&gt;
&lt;li&gt;&lt;a href="https://sqlcloudlab.blogspot.com/2026/10/how-to-find-slow-queries-in-sql-server.html" rel="noopener noreferrer"&gt;How to Find Slow Queries in SQL Server&lt;/a&gt;&lt;/li&gt;
&lt;li&gt;&lt;a href="https://sqlcloudlab.blogspot.com/2026/10/sql-server-parameter-sniffing-what-it.html" rel="noopener noreferrer"&gt;SQL Server Parameter Sniffing: What It Is and How to Fix It&lt;/a&gt;&lt;/li&gt;
&lt;/ul&gt;




&lt;p&gt;&lt;em&gt;More SQL Server guides on my blog, &lt;a href="https://sqlcloudlab.blogspot.com/" rel="noopener noreferrer"&gt;SQL &amp;amp; Cloud Lab&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>sql</category>
      <category>sqlserver</category>
      <category>database</category>
      <category>dotnet</category>
    </item>
    <item>
      <title>SQL Server Isolation Levels Explained (with NOLOCK and RCSI)</title>
      <dc:creator>LingamSuyambu</dc:creator>
      <pubDate>Tue, 17 Nov 2026 04:30:00 +0000</pubDate>
      <link>https://dev.to/lingamsuyambu/sql-server-isolation-levels-explained-with-nolock-and-rcsi-37ij</link>
      <guid>https://dev.to/lingamsuyambu/sql-server-isolation-levels-explained-with-nolock-and-rcsi-37ij</guid>
      <description>&lt;p&gt;&lt;em&gt;Originally published at &lt;a href="https://sqlcloudlab.blogspot.com/2026/10/sql-server-isolation-levels-explained.html" rel="noopener noreferrer"&gt;SQL &amp;amp; Cloud Lab&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

&lt;p&gt;&lt;em&gt;This post was drafted with AI assistance.&lt;/em&gt;&lt;/p&gt;

&lt;p&gt;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 &lt;code&gt;NOLOCK&lt;/code&gt; sometimes returns rows twice. Understanding them helps you fix blocking without trading it for wrong results.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto/https%3A%2F%2Fblogger.googleusercontent.com%2Fimg%2Fb%2FR29vZ2xl%2FAVvXsEhjLOjDc0NKqvt8HAVJUNRy5Dd1D-8INkfSncPO6g9eoMbYoyC36eNd7dkB2KmlQWU_WYa4GW8Q4Z8oM1zoYl3615y4uEWOlHNztkoztUYFW5hIZ5t5o1Tt7LKv-IXkRBZCb1o6Y9w53pyWiMAAhyfY3Va-JooXYhY1j_U80ROlp7IwDPpaBbbXYZSINa4%2Fs1600%2Fimage.webp" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto/https%3A%2F%2Fblogger.googleusercontent.com%2Fimg%2Fb%2FR29vZ2xl%2FAVvXsEhjLOjDc0NKqvt8HAVJUNRy5Dd1D-8INkfSncPO6g9eoMbYoyC36eNd7dkB2KmlQWU_WYa4GW8Q4Z8oM1zoYl3615y4uEWOlHNztkoztUYFW5hIZ5t5o1Tt7LKv-IXkRBZCb1o6Y9w53pyWiMAAhyfY3Va-JooXYhY1j_U80ROlp7IwDPpaBbbXYZSINa4%2Fs1600%2Fimage.webp" title="SQL Server transaction isolation levels explained" alt="SQL Server transaction isolation levels explained" width="800" height="420"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;h2&gt;
  
  
  The three problems isolation levels prevent
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Dirty read:&lt;/strong&gt; you read a change another transaction has not committed yet, and it may later be rolled back.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Non-repeatable read:&lt;/strong&gt; you read the same row twice in one transaction and get different values, because someone updated it in between.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Phantom read:&lt;/strong&gt; you run the same query twice and new rows appear, because someone inserted rows that match your filter.&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  The isolation levels
&lt;/h2&gt;

&lt;h3&gt;
  
  
  READ UNCOMMITTED (and NOLOCK)
&lt;/h3&gt;

&lt;p&gt;Readers take no shared locks, so they are never blocked by writers. The &lt;code&gt;WITH (NOLOCK)&lt;/code&gt; 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, and because pages can move during a scan, a query can &lt;strong&gt;skip rows or read the same row twice&lt;/strong&gt;. Use it only where approximate answers are acceptable, never for money or anything you write back.&lt;/p&gt;

&lt;h3&gt;
  
  
  READ COMMITTED (the default)
&lt;/h3&gt;

&lt;p&gt;You only see committed data. In its classic, locking form, a reader takes a short shared lock on each row it reads. If a writer holds an exclusive lock on that row, the reader waits. This is the most common source of readers and writers blocking each other.&lt;/p&gt;

&lt;h3&gt;
  
  
  READ COMMITTED SNAPSHOT (RCSI)
&lt;/h3&gt;

&lt;p&gt;RCSI is a database option that changes how READ COMMITTED works. Instead of waiting on locks, readers see the &lt;strong&gt;last committed version&lt;/strong&gt; of each row, kept in a version store in tempdb. Readers no longer block writers, and writers no longer block readers. It is &lt;strong&gt;enabled by default in Azure SQL Database&lt;/strong&gt;.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;ALTER&lt;/span&gt; &lt;span class="k"&gt;DATABASE&lt;/span&gt; &lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;YourDb&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;
&lt;span class="k"&gt;SET&lt;/span&gt; &lt;span class="n"&gt;READ_COMMITTED_SNAPSHOT&lt;/span&gt; &lt;span class="k"&gt;ON&lt;/span&gt;
&lt;span class="k"&gt;WITH&lt;/span&gt; &lt;span class="k"&gt;ROLLBACK&lt;/span&gt; &lt;span class="k"&gt;IMMEDIATE&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;   &lt;span class="c1"&gt;-- needs a moment with no other active sessions&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Test before enabling on SQL Server: it adds tempdb load, and code that relied on readers waiting for writers (for example, a "read the latest value then update" pattern without proper locking) can behave differently.&lt;/p&gt;

&lt;h3&gt;
  
  
  REPEATABLE READ
&lt;/h3&gt;

&lt;p&gt;Shared locks on rows you read are held until the transaction ends, so no one can change those rows under you. New rows can still appear (phantoms). Blocking increases because locks are held longer.&lt;/p&gt;

&lt;h3&gt;
  
  
  SNAPSHOT
&lt;/h3&gt;

&lt;p&gt;The whole transaction sees the database as it was when the transaction started, using row versions, without taking shared locks. It must be allowed at database level first:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;ALTER&lt;/span&gt; &lt;span class="k"&gt;DATABASE&lt;/span&gt; &lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;YourDb&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="k"&gt;SET&lt;/span&gt; &lt;span class="n"&gt;ALLOW_SNAPSHOT_ISOLATION&lt;/span&gt; &lt;span class="k"&gt;ON&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;

&lt;span class="k"&gt;SET&lt;/span&gt; &lt;span class="n"&gt;TRANSACTION&lt;/span&gt; &lt;span class="k"&gt;ISOLATION&lt;/span&gt; &lt;span class="k"&gt;LEVEL&lt;/span&gt; &lt;span class="n"&gt;SNAPSHOT&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;span class="k"&gt;BEGIN&lt;/span&gt; &lt;span class="n"&gt;TRAN&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
    &lt;span class="c1"&gt;-- every read here sees the same consistent point in time&lt;/span&gt;
&lt;span class="k"&gt;COMMIT&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;If two snapshot transactions update the same row, the second one fails with an &lt;strong&gt;update conflict&lt;/strong&gt; (error 3960) and must be retried. That makes it ideal for consistent reports and less suited to busy update workloads.&lt;/p&gt;

&lt;h3&gt;
  
  
  SERIALIZABLE
&lt;/h3&gt;

&lt;p&gt;The strictest level: it also locks the &lt;em&gt;ranges&lt;/em&gt; you read, so no matching rows can be inserted until you finish. Results are fully consistent, but it causes the most blocking and is a common source of deadlocks. Use it only for short, specific operations that truly need it.&lt;/p&gt;

&lt;h2&gt;
  
  
  Quick comparison
&lt;/h2&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Level&lt;/th&gt;
&lt;th&gt;Dirty reads&lt;/th&gt;
&lt;th&gt;Non-repeatable reads&lt;/th&gt;
&lt;th&gt;Phantoms&lt;/th&gt;
&lt;th&gt;Readers block writers?&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Read Uncommitted&lt;/td&gt;
&lt;td&gt;Possible&lt;/td&gt;
&lt;td&gt;Possible&lt;/td&gt;
&lt;td&gt;Possible&lt;/td&gt;
&lt;td&gt;No&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Read Committed (locking)&lt;/td&gt;
&lt;td&gt;No&lt;/td&gt;
&lt;td&gt;Possible&lt;/td&gt;
&lt;td&gt;Possible&lt;/td&gt;
&lt;td&gt;Briefly&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Read Committed Snapshot&lt;/td&gt;
&lt;td&gt;No&lt;/td&gt;
&lt;td&gt;Possible&lt;/td&gt;
&lt;td&gt;Possible&lt;/td&gt;
&lt;td&gt;No&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Repeatable Read&lt;/td&gt;
&lt;td&gt;No&lt;/td&gt;
&lt;td&gt;No&lt;/td&gt;
&lt;td&gt;Possible&lt;/td&gt;
&lt;td&gt;Yes&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Snapshot&lt;/td&gt;
&lt;td&gt;No&lt;/td&gt;
&lt;td&gt;No&lt;/td&gt;
&lt;td&gt;No&lt;/td&gt;
&lt;td&gt;No&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Serializable&lt;/td&gt;
&lt;td&gt;No&lt;/td&gt;
&lt;td&gt;No&lt;/td&gt;
&lt;td&gt;No&lt;/td&gt;
&lt;td&gt;Yes&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;h2&gt;
  
  
  Check what you are using
&lt;/h2&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="c1"&gt;-- Database-level row versioning settings&lt;/span&gt;
&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;name&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;is_read_committed_snapshot_on&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;snapshot_isolation_state_desc&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;sys&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;databases&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;name&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;DB_NAME&lt;/span&gt;&lt;span class="p"&gt;();&lt;/span&gt;

&lt;span class="c1"&gt;-- Isolation level of current sessions&lt;/span&gt;
&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;session_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;transaction_isolation_level&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;sys&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;dm_exec_sessions&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;is_user_process&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;In &lt;code&gt;transaction_isolation_level&lt;/code&gt;, 1 = Read Uncommitted, 2 = Read Committed, 3 = Repeatable Read, 4 = Serializable and 5 = Snapshot.&lt;/p&gt;

&lt;h2&gt;
  
  
  Practical advice
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;Replace blanket &lt;code&gt;NOLOCK&lt;/code&gt; hints with &lt;strong&gt;RCSI&lt;/strong&gt;; you get non-blocking reads without dirty or duplicated rows.&lt;/li&gt;
&lt;li&gt;Use &lt;strong&gt;Snapshot&lt;/strong&gt; for long reports that need one consistent point in time.&lt;/li&gt;
&lt;li&gt;Keep &lt;strong&gt;Repeatable Read&lt;/strong&gt; and &lt;strong&gt;Serializable&lt;/strong&gt; for short, specific transactions.&lt;/li&gt;
&lt;li&gt;Watch out for libraries that silently raise isolation. For example, &lt;code&gt;System.Transactions.TransactionScope&lt;/code&gt; in .NET defaults to Serializable unless you set the isolation level explicitly.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;Related reading:&lt;/strong&gt;&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;&lt;a href="https://sqlcloudlab.blogspot.com/2026/10/how-to-fix-sql-server-deadlocks-causes.html" rel="noopener noreferrer"&gt;How to Fix SQL Server Deadlocks&lt;/a&gt;&lt;/li&gt;
&lt;li&gt;&lt;a href="https://sqlcloudlab.blogspot.com/2026/10/how-to-delete-or-update-millions-of.html" rel="noopener noreferrer"&gt;How to Delete or Update Millions of Rows Safely&lt;/a&gt;&lt;/li&gt;
&lt;li&gt;&lt;a href="https://sqlcloudlab.blogspot.com/2026/10/how-to-find-slow-queries-in-sql-server.html" rel="noopener noreferrer"&gt;How to Find Slow Queries in SQL Server&lt;/a&gt;&lt;/li&gt;
&lt;/ul&gt;




&lt;p&gt;&lt;em&gt;More SQL Server guides on my blog, &lt;a href="https://sqlcloudlab.blogspot.com/" rel="noopener noreferrer"&gt;SQL &amp;amp; Cloud Lab&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>sql</category>
      <category>sqlserver</category>
      <category>database</category>
      <category>tutorial</category>
    </item>
    <item>
      <title>Temp Tables vs Table Variables in SQL Server: Which Should You Use?</title>
      <dc:creator>LingamSuyambu</dc:creator>
      <pubDate>Mon, 16 Nov 2026 04:30:00 +0000</pubDate>
      <link>https://dev.to/lingamsuyambu/temp-tables-vs-table-variables-in-sql-server-which-should-you-use-20mh</link>
      <guid>https://dev.to/lingamsuyambu/temp-tables-vs-table-variables-in-sql-server-which-should-you-use-20mh</guid>
      <description>&lt;p&gt;&lt;em&gt;Originally published at &lt;a href="https://sqlcloudlab.blogspot.com/2026/10/temp-tables-vs-table-variables-in-sql.html" rel="noopener noreferrer"&gt;SQL &amp;amp; Cloud Lab&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

&lt;p&gt;&lt;em&gt;This post was drafted with AI assistance.&lt;/em&gt;&lt;/p&gt;

&lt;p&gt;SQL Server gives you two ways to hold intermediate results: temporary tables (&lt;code&gt;#temp&lt;/code&gt;) and table variables (&lt;code&gt;@table&lt;/code&gt;). 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.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto/https%3A%2F%2Fblogger.googleusercontent.com%2Fimg%2Fb%2FR29vZ2xl%2FAVvXsEhfwNTARdHO8lQWRIIbhf_DJys0hTEqod1G6dnOMCMrnm7UXy-XCbMVY7AGgAt744GKtqWsdH-z62qE3etGfFjXj5umVwLSn_y8uyWYGGcHL0S719YPyGSk88yqKBXNEyIJNXEPO9eA9XY65cQTHCQl82wJtORarIOajtklVh_oRyHz7LJBbeoTmB3zgJY%2Fs1600%2Fimage.webp" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto/https%3A%2F%2Fblogger.googleusercontent.com%2Fimg%2Fb%2FR29vZ2xl%2FAVvXsEhfwNTARdHO8lQWRIIbhf_DJys0hTEqod1G6dnOMCMrnm7UXy-XCbMVY7AGgAt744GKtqWsdH-z62qE3etGfFjXj5umVwLSn_y8uyWYGGcHL0S719YPyGSk88yqKBXNEyIJNXEPO9eA9XY65cQTHCQl82wJtORarIOajtklVh_oRyHz7LJBbeoTmB3zgJY%2Fs1600%2Fimage.webp" title="SQL Server temp tables vs table variables comparison" alt="SQL Server temp tables vs table variables comparison" width="800" height="420"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;h2&gt;
  
  
  The syntax
&lt;/h2&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="c1"&gt;-- Temporary table&lt;/span&gt;
&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="k"&gt;TABLE&lt;/span&gt; &lt;span class="o"&gt;#&lt;/span&gt;&lt;span class="n"&gt;Employees&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="n"&gt;EmployeeId&lt;/span&gt;   &lt;span class="nb"&gt;INT&lt;/span&gt; &lt;span class="k"&gt;PRIMARY&lt;/span&gt; &lt;span class="k"&gt;KEY&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;DepartmentId&lt;/span&gt; &lt;span class="nb"&gt;INT&lt;/span&gt; &lt;span class="k"&gt;NOT&lt;/span&gt; &lt;span class="k"&gt;NULL&lt;/span&gt;
&lt;span class="p"&gt;);&lt;/span&gt;

&lt;span class="c1"&gt;-- Table variable&lt;/span&gt;
&lt;span class="k"&gt;DECLARE&lt;/span&gt; &lt;span class="o"&gt;@&lt;/span&gt;&lt;span class="n"&gt;Employees&lt;/span&gt; &lt;span class="k"&gt;TABLE&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="n"&gt;EmployeeId&lt;/span&gt;   &lt;span class="nb"&gt;INT&lt;/span&gt; &lt;span class="k"&gt;PRIMARY&lt;/span&gt; &lt;span class="k"&gt;KEY&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;DepartmentId&lt;/span&gt; &lt;span class="nb"&gt;INT&lt;/span&gt; &lt;span class="k"&gt;NOT&lt;/span&gt; &lt;span class="k"&gt;NULL&lt;/span&gt;
&lt;span class="p"&gt;);&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;A common myth is that table variables live in memory and temp tables live on disk. In fact, both are created in &lt;strong&gt;tempdb&lt;/strong&gt;, and both stay in memory while there is enough of it. The real differences are elsewhere.&lt;/p&gt;

&lt;h2&gt;
  
  
  Difference 1: statistics and row estimates
&lt;/h2&gt;

&lt;p&gt;This is the one that matters most for performance.&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Temp tables&lt;/strong&gt; get column statistics, just like normal tables, so the optimizer can estimate how many rows a filter or join will return.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Table variables&lt;/strong&gt; do not get column statistics. In older versions, the optimizer also assumed a table variable held just &lt;strong&gt;one row&lt;/strong&gt; when the plan was compiled.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;With 100,000 rows in a table variable, that one-row guess leads to plans built for tiny inputs, such as nested loops joins that run 100,000 times. Since SQL Server 2019 (database compatibility level 150 and above), &lt;strong&gt;table variable deferred compilation&lt;/strong&gt; uses the actual row count at first compile, which helps a lot. There are still no column statistics, though, so estimates for filters and joins on table variable columns remain weak.&lt;/p&gt;

&lt;h2&gt;
  
  
  Difference 2: indexes
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Temp tables&lt;/strong&gt; can have any index, added at any time with &lt;code&gt;CREATE INDEX&lt;/code&gt;.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Table variables&lt;/strong&gt; can only have indexes declared inline when they are created, such as a primary key, unique constraint or inline &lt;code&gt;INDEX&lt;/code&gt; definition.
&lt;/li&gt;
&lt;/ul&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;DECLARE&lt;/span&gt; &lt;span class="o"&gt;@&lt;/span&gt;&lt;span class="n"&gt;Orders&lt;/span&gt; &lt;span class="k"&gt;TABLE&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="n"&gt;OrderId&lt;/span&gt;    &lt;span class="nb"&gt;INT&lt;/span&gt; &lt;span class="k"&gt;PRIMARY&lt;/span&gt; &lt;span class="k"&gt;KEY&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;CustomerId&lt;/span&gt; &lt;span class="nb"&gt;INT&lt;/span&gt; &lt;span class="k"&gt;NOT&lt;/span&gt; &lt;span class="k"&gt;NULL&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="k"&gt;INDEX&lt;/span&gt; &lt;span class="n"&gt;IX_Customer&lt;/span&gt; &lt;span class="n"&gt;NONCLUSTERED&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;CustomerId&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="p"&gt;);&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h2&gt;
  
  
  Difference 3: transactions and rollback
&lt;/h2&gt;

&lt;p&gt;Changes to a table variable are &lt;strong&gt;not&lt;/strong&gt; undone by a &lt;code&gt;ROLLBACK&lt;/code&gt;. That sounds like a bug, but it is useful: you can collect error or audit details in a table variable inside a transaction and still read them after rolling back.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;DECLARE&lt;/span&gt; &lt;span class="o"&gt;@&lt;/span&gt;&lt;span class="n"&gt;Log&lt;/span&gt; &lt;span class="k"&gt;TABLE&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;Msg&lt;/span&gt; &lt;span class="n"&gt;NVARCHAR&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;200&lt;/span&gt;&lt;span class="p"&gt;));&lt;/span&gt;
&lt;span class="k"&gt;BEGIN&lt;/span&gt; &lt;span class="n"&gt;TRAN&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
    &lt;span class="k"&gt;INSERT&lt;/span&gt; &lt;span class="k"&gt;INTO&lt;/span&gt; &lt;span class="o"&gt;@&lt;/span&gt;&lt;span class="n"&gt;Log&lt;/span&gt; &lt;span class="k"&gt;VALUES&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;N&lt;/span&gt;&lt;span class="s1"&gt;'Step 1 failed'&lt;/span&gt;&lt;span class="p"&gt;);&lt;/span&gt;
&lt;span class="k"&gt;ROLLBACK&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="o"&gt;*&lt;/span&gt; &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="o"&gt;@&lt;/span&gt;&lt;span class="n"&gt;Log&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;   &lt;span class="c1"&gt;-- the row is still there&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h2&gt;
  
  
  Difference 4: scope and recompiles
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;A &lt;strong&gt;table variable&lt;/strong&gt; exists only in the batch or procedure that declares it.&lt;/li&gt;
&lt;li&gt;A &lt;strong&gt;#temp table&lt;/strong&gt; is visible to the procedure that creates it and to procedures it calls, and is dropped when the creating scope ends.&lt;/li&gt;
&lt;li&gt;Temp tables can trigger &lt;strong&gt;statement recompiles&lt;/strong&gt; as their data changes, because statistics change. That is usually what gives them good plans, but in very high-frequency procedures the recompiles can add CPU overhead.&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Side-by-side comparison
&lt;/h2&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;&lt;/th&gt;
&lt;th&gt;#temp table&lt;/th&gt;
&lt;th&gt;@table variable&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Stored in&lt;/td&gt;
&lt;td&gt;tempdb&lt;/td&gt;
&lt;td&gt;tempdb&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Column statistics&lt;/td&gt;
&lt;td&gt;Yes&lt;/td&gt;
&lt;td&gt;No&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Row count at compile&lt;/td&gt;
&lt;td&gt;Known&lt;/td&gt;
&lt;td&gt;Known from SQL Server 2019 (compat 150+); assumed 1 before&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Indexes&lt;/td&gt;
&lt;td&gt;Any, any time&lt;/td&gt;
&lt;td&gt;Inline at declaration only&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Affected by ROLLBACK&lt;/td&gt;
&lt;td&gt;Yes&lt;/td&gt;
&lt;td&gt;No&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Visible to called procedures&lt;/td&gt;
&lt;td&gt;Yes&lt;/td&gt;
&lt;td&gt;No&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Recompiles&lt;/td&gt;
&lt;td&gt;Possible&lt;/td&gt;
&lt;td&gt;Rare&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;h2&gt;
  
  
  Which should you use?
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Use a #temp table&lt;/strong&gt; when the data is more than a few hundred rows, when you join or filter on it, or when you need extra indexes. This covers most real reporting and batch work.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Use a table variable&lt;/strong&gt; for small sets (lookups, IDs, a few dozen rows), in very frequently called code where recompiles matter, or when you need data to survive a rollback.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;When in doubt, test both.&lt;/strong&gt; Compare the actual execution plans and logical reads with realistic data volumes, not with the ten rows in your development database.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;Related reading:&lt;/strong&gt;&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;&lt;a href="https://sqlcloudlab.blogspot.com/2026/10/how-to-read-sql-server-execution-plan.html" rel="noopener noreferrer"&gt;How to Read a SQL Server Execution Plan&lt;/a&gt;&lt;/li&gt;
&lt;li&gt;&lt;a href="https://sqlcloudlab.blogspot.com/2026/10/sql-server-statistics-explained-why.html" rel="noopener noreferrer"&gt;SQL Server Statistics Explained&lt;/a&gt;&lt;/li&gt;
&lt;li&gt;&lt;a href="https://sqlcloudlab.blogspot.com/2026/10/sql-server-parameter-sniffing-what-it.html" rel="noopener noreferrer"&gt;SQL Server Parameter Sniffing: What It Is and How to Fix It&lt;/a&gt;&lt;/li&gt;
&lt;/ul&gt;




&lt;p&gt;&lt;em&gt;More SQL Server guides on my blog, &lt;a href="https://sqlcloudlab.blogspot.com/" rel="noopener noreferrer"&gt;SQL &amp;amp; Cloud Lab&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>sql</category>
      <category>sqlserver</category>
      <category>database</category>
      <category>tutorial</category>
    </item>
    <item>
      <title>SQL Server Query Store: A Practical Beginner's Guide</title>
      <dc:creator>LingamSuyambu</dc:creator>
      <pubDate>Sun, 15 Nov 2026 04:30:00 +0000</pubDate>
      <link>https://dev.to/lingamsuyambu/sql-server-query-store-a-practical-beginners-guide-44g1</link>
      <guid>https://dev.to/lingamsuyambu/sql-server-query-store-a-practical-beginners-guide-44g1</guid>
      <description>&lt;p&gt;&lt;em&gt;Originally published at &lt;a href="https://sqlcloudlab.blogspot.com/2026/10/sql-server-query-store-practical.html" rel="noopener noreferrer"&gt;SQL &amp;amp; Cloud Lab&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

&lt;p&gt;&lt;em&gt;This post was drafted with AI assistance.&lt;/em&gt;&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto/https%3A%2F%2Fblogger.googleusercontent.com%2Fimg%2Fb%2FR29vZ2xl%2FAVvXsEhTRsV3qj3J9OoTKqFVDLyV8LgbiBy-IKTNgnaYBNEFX-MYnOGPisdj3cPESzsKSwLdOyRqg0fJ5zAOqjnQGpErK9iPPE6juBJzSllDrXKJSKlswczBEmctXChB28Jdr_cz0O9mAjw8eMdJzdLfe64vCLzlzeAK-olzUh33zHBZV5kVwXkDr8pBFOmdHCw%2Fs1600%2Fimage.webp" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto/https%3A%2F%2Fblogger.googleusercontent.com%2Fimg%2Fb%2FR29vZ2xl%2FAVvXsEhTRsV3qj3J9OoTKqFVDLyV8LgbiBy-IKTNgnaYBNEFX-MYnOGPisdj3cPESzsKSwLdOyRqg0fJ5zAOqjnQGpErK9iPPE6juBJzSllDrXKJSKlswczBEmctXChB28Jdr_cz0O9mAjw8eMdJzdLfe64vCLzlzeAK-olzUh33zHBZV5kVwXkDr8pBFOmdHCw%2Fs1600%2Fimage.webp" title="SQL Server Query Store beginner's guide" alt="SQL Server Query Store beginner's guide" width="800" height="420"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;h2&gt;
  
  
  Is Query Store already on?
&lt;/h2&gt;

&lt;p&gt;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:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;actual_state_desc&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;desired_state_desc&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="n"&gt;current_storage_size_mb&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;max_storage_size_mb&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="n"&gt;query_capture_mode_desc&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;sys&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;database_query_store_options&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;If &lt;code&gt;actual_state_desc&lt;/code&gt; shows &lt;code&gt;READ_ONLY&lt;/code&gt; when you expected &lt;code&gt;READ_WRITE&lt;/code&gt;, the most common reason is that it has reached its maximum size and stopped collecting.&lt;/p&gt;

&lt;h2&gt;
  
  
  Turn it on with sensible settings
&lt;/h2&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;ALTER&lt;/span&gt; &lt;span class="k"&gt;DATABASE&lt;/span&gt; &lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;YourDb&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;
&lt;span class="k"&gt;SET&lt;/span&gt; &lt;span class="n"&gt;QUERY_STORE&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="n"&gt;OPERATION_MODE&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;READ_WRITE&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;QUERY_CAPTURE_MODE&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;AUTO&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;          &lt;span class="c1"&gt;-- skip trivial, rarely run queries&lt;/span&gt;
    &lt;span class="n"&gt;MAX_STORAGE_SIZE_MB&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;2048&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;SIZE_BASED_CLEANUP_MODE&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;AUTO&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;CLEANUP_POLICY&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;STALE_QUERY_THRESHOLD_DAYS&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;30&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="p"&gt;);&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;QUERY_CAPTURE_MODE = AUTO&lt;/strong&gt; avoids filling Query Store with one-off ad hoc statements.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;MAX_STORAGE_SIZE_MB&lt;/strong&gt; should be big enough for your workload; busy databases with many distinct queries need more.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;SIZE_BASED_CLEANUP_MODE = AUTO&lt;/strong&gt; removes old data as it approaches the limit, so it does not flip to read-only.&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  The four reports worth knowing
&lt;/h2&gt;

&lt;p&gt;In SSMS, expand the database, then &lt;em&gt;Query Store&lt;/em&gt;. These reports cover most day-to-day needs:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Top Resource Consuming Queries:&lt;/strong&gt; the heaviest queries by CPU, duration, reads or executions over the time window you choose. Start here for general tuning.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Regressed Queries:&lt;/strong&gt; queries that got worse compared with an earlier period. Ideal for "it was fine last week".&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Queries With High Variation:&lt;/strong&gt; queries whose performance swings a lot, often a sign of parameter sniffing.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Overall Resource Consumption:&lt;/strong&gt; a timeline of total load, useful to find when a problem started.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Click any query to see each plan it has used as a coloured dot on a chart. Different colours mean different plans, so a jump in duration that lines up with a new colour usually means a plan change caused the slowdown.&lt;/p&gt;

&lt;h2&gt;
  
  
  Compare plans and force the good one
&lt;/h2&gt;

&lt;p&gt;When a query has a good plan and a bad plan, you can select both in the report and click &lt;em&gt;Compare Plans&lt;/em&gt; to see what changed, such as a seek turning into a scan or a different join type.&lt;/p&gt;

&lt;p&gt;To stop the problem quickly, &lt;strong&gt;force&lt;/strong&gt; the good plan, either with the &lt;em&gt;Force Plan&lt;/em&gt; button or in T-SQL:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;EXEC&lt;/span&gt; &lt;span class="n"&gt;sp_query_store_force_plan&lt;/span&gt; &lt;span class="o"&gt;@&lt;/span&gt;&lt;span class="n"&gt;query_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;1234&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="o"&gt;@&lt;/span&gt;&lt;span class="n"&gt;plan_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;56&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;

&lt;span class="c1"&gt;-- Later, to remove it:&lt;/span&gt;
&lt;span class="k"&gt;EXEC&lt;/span&gt; &lt;span class="n"&gt;sp_query_store_unforce_plan&lt;/span&gt; &lt;span class="o"&gt;@&lt;/span&gt;&lt;span class="n"&gt;query_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;1234&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="o"&gt;@&lt;/span&gt;&lt;span class="n"&gt;plan_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;56&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Plan forcing is an excellent emergency fix. Treat it as temporary: find the root cause, such as statistics, parameter sniffing or a missing index, and unforce the plan once it is fixed. Forced plans can also fail if an index they rely on is dropped, so check the &lt;em&gt;Queries With Forced Plans&lt;/em&gt; report occasionally.&lt;/p&gt;

&lt;h2&gt;
  
  
  Query the data yourself
&lt;/h2&gt;

&lt;p&gt;The reports are built on catalog views you can query directly. For example, the slowest queries by average duration in the last 24 hours:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;TOP&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;10&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="n"&gt;q&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;query_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;qt&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;query_sql_text&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="k"&gt;SUM&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;rs&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;count_executions&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;  &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;executions&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="k"&gt;AVG&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;rs&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;avg_duration&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;/&lt;/span&gt; &lt;span class="mi"&gt;1000&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="mi"&gt;0&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;avg_duration_ms&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;sys&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;query_store_runtime_stats&lt;/span&gt; &lt;span class="n"&gt;rs&lt;/span&gt;
&lt;span class="k"&gt;JOIN&lt;/span&gt; &lt;span class="n"&gt;sys&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;query_store_runtime_stats_interval&lt;/span&gt; &lt;span class="n"&gt;i&lt;/span&gt;
     &lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="n"&gt;rs&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;runtime_stats_interval_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;i&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;runtime_stats_interval_id&lt;/span&gt;
&lt;span class="k"&gt;JOIN&lt;/span&gt; &lt;span class="n"&gt;sys&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;query_store_plan&lt;/span&gt; &lt;span class="n"&gt;p&lt;/span&gt;        &lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="n"&gt;rs&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;plan_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;p&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;plan_id&lt;/span&gt;
&lt;span class="k"&gt;JOIN&lt;/span&gt; &lt;span class="n"&gt;sys&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;query_store_query&lt;/span&gt; &lt;span class="n"&gt;q&lt;/span&gt;       &lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="n"&gt;p&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;query_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;q&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;query_id&lt;/span&gt;
&lt;span class="k"&gt;JOIN&lt;/span&gt; &lt;span class="n"&gt;sys&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;query_store_query_text&lt;/span&gt; &lt;span class="n"&gt;qt&lt;/span&gt; &lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="n"&gt;q&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;query_text_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;qt&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;query_text_id&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;i&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;start_time&lt;/span&gt; &lt;span class="o"&gt;&amp;gt;=&lt;/span&gt; &lt;span class="n"&gt;DATEADD&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;HOUR&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="o"&gt;-&lt;/span&gt;&lt;span class="mi"&gt;24&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;SYSUTCDATETIME&lt;/span&gt;&lt;span class="p"&gt;())&lt;/span&gt;
&lt;span class="k"&gt;GROUP&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;q&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;query_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;qt&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;query_sql_text&lt;/span&gt;
&lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;avg_duration_ms&lt;/span&gt; &lt;span class="k"&gt;DESC&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Query Store stores times in UTC and durations in microseconds, which is why the script divides by 1,000.&lt;/p&gt;

&lt;h2&gt;
  
  
  Good habits
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;Check Query Store after every deployment or upgrade, using the Regressed Queries report.&lt;/li&gt;
&lt;li&gt;Before changing database compatibility level, make sure Query Store has a baseline. It makes it much easier to spot and fix regressions afterwards.&lt;/li&gt;
&lt;li&gt;Watch its size and state; a read-only Query Store silently stops recording.&lt;/li&gt;
&lt;li&gt;Prefer fixing the cause over leaving plans forced forever.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;Related reading:&lt;/strong&gt;&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;&lt;a href="https://sqlcloudlab.blogspot.com/2026/10/how-to-find-slow-queries-in-sql-server.html" rel="noopener noreferrer"&gt;How to Find Slow Queries in SQL Server&lt;/a&gt;&lt;/li&gt;
&lt;li&gt;&lt;a href="https://sqlcloudlab.blogspot.com/2026/10/sql-server-parameter-sniffing-what-it.html" rel="noopener noreferrer"&gt;SQL Server Parameter Sniffing: What It Is and How to Fix It&lt;/a&gt;&lt;/li&gt;
&lt;li&gt;&lt;a href="https://sqlcloudlab.blogspot.com/2026/10/how-to-read-sql-server-execution-plan.html" rel="noopener noreferrer"&gt;How to Read a SQL Server Execution Plan&lt;/a&gt;&lt;/li&gt;
&lt;/ul&gt;




&lt;p&gt;&lt;em&gt;More SQL Server guides on my blog, &lt;a href="https://sqlcloudlab.blogspot.com/" rel="noopener noreferrer"&gt;SQL &amp;amp; Cloud Lab&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>sql</category>
      <category>sqlserver</category>
      <category>database</category>
      <category>performance</category>
    </item>
    <item>
      <title>SQL Server Statistics Explained: Why Your Query Plans Go Wrong</title>
      <dc:creator>LingamSuyambu</dc:creator>
      <pubDate>Sat, 14 Nov 2026 04:30:00 +0000</pubDate>
      <link>https://dev.to/lingamsuyambu/sql-server-statistics-explained-why-your-query-plans-go-wrong-1nmf</link>
      <guid>https://dev.to/lingamsuyambu/sql-server-statistics-explained-why-your-query-plans-go-wrong-1nmf</guid>
      <description>&lt;p&gt;&lt;em&gt;Originally published at &lt;a href="https://sqlcloudlab.blogspot.com/2026/10/sql-server-statistics-explained-why.html" rel="noopener noreferrer"&gt;SQL &amp;amp; Cloud Lab&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

&lt;p&gt;&lt;em&gt;This post was drafted with AI assistance.&lt;/em&gt;&lt;/p&gt;

&lt;p&gt;When a query suddenly picks a terrible plan, the root cause is very often &lt;strong&gt;statistics&lt;/strong&gt;. 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.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto/https%3A%2F%2Fblogger.googleusercontent.com%2Fimg%2Fb%2FR29vZ2xl%2FAVvXsEhtUN6Ha9LbY_-RkKyBxNPhtHJ1BJm_8odhfCm4A_8JLgDwv7RarwmQ0YSWSMkppqWlqbSVqJnpYShL7apKqGhEsXT-2LGL4TBmGekQJUt1lHqMejSpoGqII57cWbopclTXzvVLXSrlq73xhyphenhyphennZPbncGV_xrdUOu0CQ-BsWMPJofkFujkbntI9TV6JXEKQ%2Fs1600%2Fimage.webp" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto/https%3A%2F%2Fblogger.googleusercontent.com%2Fimg%2Fb%2FR29vZ2xl%2FAVvXsEhtUN6Ha9LbY_-RkKyBxNPhtHJ1BJm_8odhfCm4A_8JLgDwv7RarwmQ0YSWSMkppqWlqbSVqJnpYShL7apKqGhEsXT-2LGL4TBmGekQJUt1lHqMejSpoGqII57cWbopclTXzvVLXSrlq73xhyphenhyphennZPbncGV_xrdUOu0CQ-BsWMPJofkFujkbntI9TV6JXEKQ%2Fs1600%2Fimage.webp" title="SQL Server statistics explained" alt="SQL Server statistics explained" width="800" height="420"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;h2&gt;
  
  
  What statistics are
&lt;/h2&gt;

&lt;p&gt;A statistics object describes how values are distributed in one or more columns. SQL Server creates one automatically for every index, and, with &lt;code&gt;AUTO_CREATE_STATISTICS&lt;/code&gt; 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.&lt;/p&gt;

&lt;h2&gt;
  
  
  How to read a statistics object
&lt;/h2&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="n"&gt;DBCC&lt;/span&gt; &lt;span class="n"&gt;SHOW_STATISTICS&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s1"&gt;'dbo.PayrollLine'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s1"&gt;'IX_PayrollLine_Run_PayCode'&lt;/span&gt;&lt;span class="p"&gt;);&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;The output has three parts:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Header:&lt;/strong&gt; when the statistics were last updated, how many rows the table had, and how many rows were &lt;em&gt;sampled&lt;/em&gt;.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Density vector:&lt;/strong&gt; how unique the column combinations are, used when the actual value is unknown.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Histogram:&lt;/strong&gt; up to 200 steps showing, for ranges of values in the first column, how many rows match. This is what lets SQL Server know that &lt;code&gt;Status = 'Closed'&lt;/code&gt; matches 2 million rows but &lt;code&gt;Status = 'Error'&lt;/code&gt; matches 12.&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  When statistics update automatically
&lt;/h2&gt;

&lt;p&gt;With &lt;code&gt;AUTO_UPDATE_STATISTICS&lt;/code&gt; on (the default), statistics are refreshed when enough rows have changed and a query needs them. On modern versions (compatibility level 130 and above) the threshold is dynamic, so large tables are refreshed after a smaller percentage of changes than in old versions. Even so, on a table with hundreds of millions of rows, a lot can change before an automatic update kicks in.&lt;/p&gt;

&lt;p&gt;Automatic updates also use a &lt;strong&gt;sample&lt;/strong&gt; of rows by default, which can miss skewed values in very large tables.&lt;/p&gt;

&lt;h2&gt;
  
  
  Check how stale your statistics are
&lt;/h2&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt;
    &lt;span class="n"&gt;OBJECT_NAME&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;s&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;object_id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;  &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="k"&gt;table_name&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;s&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;name&lt;/span&gt;                    &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;stats_name&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;sp&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;last_updated&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;sp&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="k"&gt;rows&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;sp&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;rows_sampled&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;sp&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;modification_counter&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;sys&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;stats&lt;/span&gt; &lt;span class="n"&gt;s&lt;/span&gt;
&lt;span class="k"&gt;CROSS&lt;/span&gt; &lt;span class="n"&gt;APPLY&lt;/span&gt; &lt;span class="n"&gt;sys&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;dm_db_stats_properties&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;s&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;object_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;s&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;stats_id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="n"&gt;sp&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;OBJECTPROPERTY&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;s&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;object_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s1"&gt;'IsUserTable'&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;
&lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;sp&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;modification_counter&lt;/span&gt; &lt;span class="k"&gt;DESC&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;A high &lt;code&gt;modification_counter&lt;/code&gt; means many rows have changed since the last update. A low &lt;code&gt;rows_sampled&lt;/code&gt; compared with &lt;code&gt;rows&lt;/code&gt; means the statistics are based on a small sample.&lt;/p&gt;

&lt;h2&gt;
  
  
  Update statistics manually when it matters
&lt;/h2&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="c1"&gt;-- One table, default sample&lt;/span&gt;
&lt;span class="k"&gt;UPDATE&lt;/span&gt; &lt;span class="k"&gt;STATISTICS&lt;/span&gt; &lt;span class="n"&gt;dbo&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;PayrollLine&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;

&lt;span class="c1"&gt;-- One statistics object, reading every row (most accurate, most expensive)&lt;/span&gt;
&lt;span class="k"&gt;UPDATE&lt;/span&gt; &lt;span class="k"&gt;STATISTICS&lt;/span&gt; &lt;span class="n"&gt;dbo&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;PayrollLine&lt;/span&gt; &lt;span class="n"&gt;IX_PayrollLine_Run_PayCode&lt;/span&gt; &lt;span class="k"&gt;WITH&lt;/span&gt; &lt;span class="n"&gt;FULLSCAN&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;

&lt;span class="c1"&gt;-- All statistics in the database that have had modifications&lt;/span&gt;
&lt;span class="k"&gt;EXEC&lt;/span&gt; &lt;span class="n"&gt;sp_updatestats&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Use &lt;code&gt;FULLSCAN&lt;/code&gt; for important, skewed columns on large tables; it costs more I/O but gives the most accurate histogram. After a big data load, updating statistics on the affected tables before users run queries prevents a wave of bad plans.&lt;/p&gt;

&lt;h2&gt;
  
  
  The ascending key problem
&lt;/h2&gt;

&lt;p&gt;Columns that always grow, such as identity values or &lt;code&gt;CreatedAt&lt;/code&gt; dates, cause a classic issue: new rows are added beyond the highest value in the histogram, and until statistics are updated, SQL Server may estimate that very few rows exist there. Queries on "today's" or "the latest" data then get plans built for tiny row counts. Newer cardinality estimator versions handle this better, but frequent statistics updates on these tables are still the most reliable fix.&lt;/p&gt;

&lt;h2&gt;
  
  
  Index rebuilds vs statistics updates
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;Rebuilding an index also updates &lt;strong&gt;that index's&lt;/strong&gt; statistics with a full scan.&lt;/li&gt;
&lt;li&gt;It does &lt;strong&gt;not&lt;/strong&gt; update auto-created column statistics.&lt;/li&gt;
&lt;li&gt;Reorganizing an index does not update statistics at all.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;So a maintenance job that only reorganizes or rebuilds indexes can leave important column statistics stale. Include a statistics step in your maintenance.&lt;/p&gt;

&lt;h2&gt;
  
  
  Practical routine
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;Keep &lt;code&gt;AUTO_CREATE_STATISTICS&lt;/code&gt; and &lt;code&gt;AUTO_UPDATE_STATISTICS&lt;/code&gt; on.&lt;/li&gt;
&lt;li&gt;Schedule a regular statistics update, such as nightly or weekly, for busy tables.&lt;/li&gt;
&lt;li&gt;Update statistics after large loads, purges or migrations.&lt;/li&gt;
&lt;li&gt;When a plan goes bad, compare estimated and actual rows; if they differ a lot, check the statistics on those columns first.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;Related reading:&lt;/strong&gt;&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;&lt;a href="https://sqlcloudlab.blogspot.com/2026/10/sql-server-parameter-sniffing-what-it.html" rel="noopener noreferrer"&gt;SQL Server Parameter Sniffing: What It Is and How to Fix It&lt;/a&gt;&lt;/li&gt;
&lt;li&gt;&lt;a href="https://sqlcloudlab.blogspot.com/2026/10/how-to-read-sql-server-execution-plan.html" rel="noopener noreferrer"&gt;How to Read a SQL Server Execution Plan&lt;/a&gt;&lt;/li&gt;
&lt;li&gt;&lt;a href="https://sqlcloudlab.blogspot.com/2026/10/sql-server-index-tuning-for-beginners.html" rel="noopener noreferrer"&gt;SQL Server Index Tuning for Beginners&lt;/a&gt;&lt;/li&gt;
&lt;/ul&gt;




&lt;p&gt;&lt;em&gt;More SQL Server guides on my blog, &lt;a href="https://sqlcloudlab.blogspot.com/" rel="noopener noreferrer"&gt;SQL &amp;amp; Cloud Lab&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>sql</category>
      <category>sqlserver</category>
      <category>database</category>
      <category>performance</category>
    </item>
    <item>
      <title>How to Find Slow Queries in SQL Server (DMVs and Query Store)</title>
      <dc:creator>LingamSuyambu</dc:creator>
      <pubDate>Fri, 13 Nov 2026 04:30:00 +0000</pubDate>
      <link>https://dev.to/lingamsuyambu/how-to-find-slow-queries-in-sql-server-dmvs-and-query-store-fh1</link>
      <guid>https://dev.to/lingamsuyambu/how-to-find-slow-queries-in-sql-server-dmvs-and-query-store-fh1</guid>
      <description>&lt;p&gt;&lt;em&gt;Originally published at &lt;a href="https://sqlcloudlab.blogspot.com/2026/10/how-to-find-slow-queries-in-sql-server.html" rel="noopener noreferrer"&gt;SQL &amp;amp; Cloud Lab&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

&lt;p&gt;&lt;em&gt;This post was drafted with AI assistance.&lt;/em&gt;&lt;/p&gt;

&lt;p&gt;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.&amp;nbsp;&amp;nbsp; &amp;nbsp;Before you can tune anything, you need to know &lt;em&gt;which&lt;/em&gt; 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.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto/https%3A%2F%2Fblogger.googleusercontent.com%2Fimg%2Fb%2FR29vZ2xl%2FAVvXsEjjcTj_KosyJbN81U5c4vW_QrkCZr8AJl_JkuMfL94WLymOnWk3OoAzozTcGd6f0PCOMN3UxkRYS_p_al-_dH9FYpUzmNcqulWTKKlXfNGFWVrB5JaUuM3txj6JmIRygnfU3Eowj5hjw36mHsyUTeqxKMlBfTEATwpAhaUbNmmVMQpfKK8VN7ATQLsPdrk%2Fs1600%2Fimage.webp" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto/https%3A%2F%2Fblogger.googleusercontent.com%2Fimg%2Fb%2FR29vZ2xl%2FAVvXsEjjcTj_KosyJbN81U5c4vW_QrkCZr8AJl_JkuMfL94WLymOnWk3OoAzozTcGd6f0PCOMN3UxkRYS_p_al-_dH9FYpUzmNcqulWTKKlXfNGFWVrB5JaUuM3txj6JmIRygnfU3Eowj5hjw36mHsyUTeqxKMlBfTEATwpAhaUbNmmVMQpfKK8VN7ATQLsPdrk%2Fs1600%2Fimage.webp" title="How to find slow queries in SQL Server checklist" alt="How to find slow queries in SQL Server checklist" width="800" height="420"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;h2&gt;
  
  
  1. What is running right now?
&lt;/h2&gt;

&lt;p&gt;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:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt;
    &lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;session_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;status&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;start_time&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;DATEDIFF&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;SECOND&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;start_time&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;SYSDATETIME&lt;/span&gt;&lt;span class="p"&gt;())&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;running_seconds&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;wait_type&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;wait_time&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;blocking_session_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;cpu_time&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;logical_reads&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;DB_NAME&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;database_id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;database_name&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;t&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nb"&gt;text&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;query_text&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;sys&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;dm_exec_requests&lt;/span&gt; &lt;span class="n"&gt;r&lt;/span&gt;
&lt;span class="k"&gt;CROSS&lt;/span&gt; &lt;span class="n"&gt;APPLY&lt;/span&gt; &lt;span class="n"&gt;sys&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;dm_exec_sql_text&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;sql_handle&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="n"&gt;t&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;session_id&lt;/span&gt; &lt;span class="o"&gt;&amp;lt;&amp;gt;&lt;/span&gt; &lt;span class="o"&gt;@@&lt;/span&gt;&lt;span class="n"&gt;SPID&lt;/span&gt;
  &lt;span class="k"&gt;AND&lt;/span&gt; &lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;session_id&lt;/span&gt; &lt;span class="o"&gt;&amp;gt;&lt;/span&gt; &lt;span class="mi"&gt;50&lt;/span&gt;
&lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;start_time&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;ul&gt;
&lt;li&gt;A non-zero &lt;code&gt;blocking_session_id&lt;/code&gt; means the query is waiting for another session's locks.&lt;/li&gt;
&lt;li&gt;
&lt;code&gt;wait_type&lt;/code&gt; tells you what it is waiting for, such as locks (&lt;code&gt;LCK_M_*&lt;/code&gt;), disk (&lt;code&gt;PAGEIOLATCH_*&lt;/code&gt;) or parallelism (&lt;code&gt;CXPACKET&lt;/code&gt;/&lt;code&gt;CXCONSUMER&lt;/code&gt;).&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  2. Which queries cost the most overall?
&lt;/h2&gt;

&lt;p&gt;For general slowness, the most useful view is &lt;strong&gt;total&lt;/strong&gt; cost across all executions. A 50 ms query that runs 100,000 times an hour usually matters more than one 30-second report that runs once a day.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="c1"&gt;-- Top 10 queries by total CPU since they were cached&lt;/span&gt;
&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;TOP&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;10&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="n"&gt;qs&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;execution_count&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;qs&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;total_worker_time&lt;/span&gt; &lt;span class="o"&gt;/&lt;/span&gt; &lt;span class="mi"&gt;1000&lt;/span&gt;                        &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;total_cpu_ms&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;qs&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;total_worker_time&lt;/span&gt; &lt;span class="o"&gt;/&lt;/span&gt; &lt;span class="n"&gt;qs&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;execution_count&lt;/span&gt; &lt;span class="o"&gt;/&lt;/span&gt; &lt;span class="mi"&gt;1000&lt;/span&gt;   &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;avg_cpu_ms&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;qs&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;total_logical_reads&lt;/span&gt; &lt;span class="o"&gt;/&lt;/span&gt; &lt;span class="n"&gt;qs&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;execution_count&lt;/span&gt;        &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;avg_reads&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;qs&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;total_elapsed_time&lt;/span&gt; &lt;span class="o"&gt;/&lt;/span&gt; &lt;span class="n"&gt;qs&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;execution_count&lt;/span&gt; &lt;span class="o"&gt;/&lt;/span&gt; &lt;span class="mi"&gt;1000&lt;/span&gt;  &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;avg_duration_ms&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="k"&gt;SUBSTRING&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;t&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nb"&gt;text&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;qs&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;statement_start_offset&lt;/span&gt; &lt;span class="o"&gt;/&lt;/span&gt; &lt;span class="mi"&gt;2&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;+&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
        &lt;span class="p"&gt;((&lt;/span&gt;&lt;span class="k"&gt;CASE&lt;/span&gt; &lt;span class="n"&gt;qs&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;statement_end_offset&lt;/span&gt; &lt;span class="k"&gt;WHEN&lt;/span&gt; &lt;span class="o"&gt;-&lt;/span&gt;&lt;span class="mi"&gt;1&lt;/span&gt; &lt;span class="k"&gt;THEN&lt;/span&gt; &lt;span class="n"&gt;DATALENGTH&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;t&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nb"&gt;text&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
          &lt;span class="k"&gt;ELSE&lt;/span&gt; &lt;span class="n"&gt;qs&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;statement_end_offset&lt;/span&gt; &lt;span class="k"&gt;END&lt;/span&gt; &lt;span class="o"&gt;-&lt;/span&gt; &lt;span class="n"&gt;qs&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;statement_start_offset&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;/&lt;/span&gt; &lt;span class="mi"&gt;2&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;+&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;statement_text&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;sys&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;dm_exec_query_stats&lt;/span&gt; &lt;span class="n"&gt;qs&lt;/span&gt;
&lt;span class="k"&gt;CROSS&lt;/span&gt; &lt;span class="n"&gt;APPLY&lt;/span&gt; &lt;span class="n"&gt;sys&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;dm_exec_sql_text&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;qs&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;sql_handle&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="n"&gt;t&lt;/span&gt;
&lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;qs&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;total_worker_time&lt;/span&gt; &lt;span class="k"&gt;DESC&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Change the &lt;code&gt;ORDER BY&lt;/code&gt; to &lt;code&gt;qs.total_logical_reads DESC&lt;/code&gt; to find the queries that read the most data, which are often the ones missing an index.&lt;/p&gt;

&lt;p&gt;Keep in mind that this view only covers plans still in cache. It resets on restart and loses queries whose plans were evicted, which is why Query Store is better for history.&lt;/p&gt;

&lt;h2&gt;
  
  
  3. Use Query Store for history and regressions
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;Query Store&lt;/strong&gt; records query performance and plans over time inside the database. It is on by default in Azure SQL Database and for new databases in SQL Server 2022. In SSMS, open the database, expand &lt;em&gt;Query Store&lt;/em&gt;, and use:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Top Resource Consuming Queries:&lt;/strong&gt; the heaviest queries over any time window you pick.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Regressed Queries:&lt;/strong&gt; queries that got slower, usually because their plan changed.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Queries With High Variation:&lt;/strong&gt; queries whose duration swings a lot, a common sign of parameter sniffing.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;The big advantage is history: you can compare "today, when it was slow" with "last week, when it was fine".&lt;/p&gt;

&lt;h2&gt;
  
  
  4. Check what the whole server waits on
&lt;/h2&gt;

&lt;p&gt;Wait statistics show where SQL Server spends its waiting time overall, which points you to the type of problem:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;TOP&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;10&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="n"&gt;wait_type&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;wait_time_ms&lt;/span&gt; &lt;span class="o"&gt;/&lt;/span&gt; &lt;span class="mi"&gt;1000&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="mi"&gt;0&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;wait_seconds&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;waiting_tasks_count&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;sys&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;dm_os_wait_stats&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;wait_type&lt;/span&gt; &lt;span class="k"&gt;NOT&lt;/span&gt; &lt;span class="k"&gt;LIKE&lt;/span&gt; &lt;span class="s1"&gt;'SLEEP%'&lt;/span&gt;
  &lt;span class="k"&gt;AND&lt;/span&gt; &lt;span class="n"&gt;wait_type&lt;/span&gt; &lt;span class="k"&gt;NOT&lt;/span&gt; &lt;span class="k"&gt;LIKE&lt;/span&gt; &lt;span class="s1"&gt;'%QUEUE%'&lt;/span&gt;
  &lt;span class="k"&gt;AND&lt;/span&gt; &lt;span class="n"&gt;wait_type&lt;/span&gt; &lt;span class="k"&gt;NOT&lt;/span&gt; &lt;span class="k"&gt;IN&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s1"&gt;'BROKER_TASK_STOP'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;&lt;span class="s1"&gt;'CHECKPOINT_QUEUE'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;&lt;span class="s1"&gt;'WAITFOR'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
                        &lt;span class="s1"&gt;'XE_TIMER_EVENT'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;&lt;span class="s1"&gt;'DIRTY_PAGE_POLL'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;&lt;span class="s1"&gt;'HADR_FILESTREAM_IOMGR_IOCOMPLETION'&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;wait_time_ms&lt;/span&gt; &lt;span class="k"&gt;DESC&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;PAGEIOLATCH_*&lt;/strong&gt;: reading data from disk. Often too many reads, so look for missing indexes or scans.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;LCK_M_*&lt;/strong&gt;: blocking. Look at long transactions and locking patterns.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;SOS_SCHEDULER_YIELD&lt;/strong&gt; with high CPU: CPU-heavy queries.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;WRITELOG&lt;/strong&gt;: transaction log writes. Look at many tiny commits or slow log storage.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Wait stats accumulate since the last restart, so compare snapshots taken a few minutes apart for a picture of current load. This filter list is a starting point; there are more harmless background waits you may want to exclude.&lt;/p&gt;

&lt;h2&gt;
  
  
  5. Pick one query and fix it
&lt;/h2&gt;

&lt;p&gt;Once you have a shortlist, take the query with the biggest total impact, get its actual execution plan, and look for scans on large tables, key lookups, bad row estimates and warnings. Fix one thing at a time, and compare logical reads and CPU before and after.&lt;/p&gt;

&lt;h2&gt;
  
  
  Quick routine
&lt;/h2&gt;

&lt;ol&gt;
&lt;li&gt;Slow right now? Check &lt;code&gt;sys.dm_exec_requests&lt;/code&gt; for blocking and waits.&lt;/li&gt;
&lt;li&gt;Slow in general? Rank queries by total CPU and reads.&lt;/li&gt;
&lt;li&gt;Slower than last week? Use Query Store's Regressed Queries.&lt;/li&gt;
&lt;li&gt;Check wait stats to understand the type of bottleneck.&lt;/li&gt;
&lt;li&gt;Tune the top query, then measure again.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;&lt;strong&gt;Related reading:&lt;/strong&gt;&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;&lt;a href="https://sqlcloudlab.blogspot.com/2026/10/sql-server-query-store-practical.html" rel="noopener noreferrer"&gt;SQL Server Query Store: A Practical Beginner's Guide&lt;/a&gt;&lt;/li&gt;
&lt;li&gt;&lt;a href="https://sqlcloudlab.blogspot.com/2026/10/how-to-read-sql-server-execution-plan.html" rel="noopener noreferrer"&gt;How to Read a SQL Server Execution Plan&lt;/a&gt;&lt;/li&gt;
&lt;li&gt;&lt;a href="https://sqlcloudlab.blogspot.com/2026/10/sql-server-index-tuning-for-beginners.html" rel="noopener noreferrer"&gt;SQL Server Index Tuning for Beginners&lt;/a&gt;&lt;/li&gt;
&lt;/ul&gt;




&lt;p&gt;&lt;em&gt;More SQL Server guides on my blog, &lt;a href="https://sqlcloudlab.blogspot.com/" rel="noopener noreferrer"&gt;SQL &amp;amp; Cloud Lab&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>sql</category>
      <category>sqlserver</category>
      <category>database</category>
      <category>performance</category>
    </item>
    <item>
      <title>SQL Server Index Tuning for Beginners: A Practical Guide</title>
      <dc:creator>LingamSuyambu</dc:creator>
      <pubDate>Thu, 12 Nov 2026 04:30:00 +0000</pubDate>
      <link>https://dev.to/lingamsuyambu/sql-server-index-tuning-for-beginners-a-practical-guide-37ln</link>
      <guid>https://dev.to/lingamsuyambu/sql-server-index-tuning-for-beginners-a-practical-guide-37ln</guid>
      <description>&lt;p&gt;&lt;em&gt;Originally published at &lt;a href="https://sqlcloudlab.blogspot.com/2026/10/sql-server-index-tuning-for-beginners.html" rel="noopener noreferrer"&gt;SQL &amp;amp; Cloud Lab&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

&lt;p&gt;&lt;em&gt;This post was drafted with AI assistance.&lt;/em&gt;&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto/https%3A%2F%2Fblogger.googleusercontent.com%2Fimg%2Fb%2FR29vZ2xl%2FAVvXsEhfp_0JxSbqRhRPFZVur8Nr2TBg4HHWzFLvcYrEbS6AXfr2EGDdk5TSnEsZeDO0Se9lUP4_9OMKfBbSRL4bvv3PEGNvj2JHwx9wjwNHFU4LMUfrmkAG33fOkKBx9HE0wgnLPUSX7jvTGXujn1LYHIuD4JDghyO_X-91Heca8CREbhHmwahjZj6-hdNYi9A%2Fs1600%2Fimage.webp" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto/https%3A%2F%2Fblogger.googleusercontent.com%2Fimg%2Fb%2FR29vZ2xl%2FAVvXsEhfp_0JxSbqRhRPFZVur8Nr2TBg4HHWzFLvcYrEbS6AXfr2EGDdk5TSnEsZeDO0Se9lUP4_9OMKfBbSRL4bvv3PEGNvj2JHwx9wjwNHFU4LMUfrmkAG33fOkKBx9HE0wgnLPUSX7jvTGXujn1LYHIuD4JDghyO_X-91Heca8CREbhHmwahjZj6-hdNYi9A%2Fs1600%2Fimage.webp" title="SQL Server index tuning checklist for beginners" alt="SQL Server index tuning checklist for beginners" width="800" height="420"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;h2&gt;
  
  
  Clustered vs nonclustered indexes
&lt;/h2&gt;

&lt;p&gt;A &lt;strong&gt;clustered index&lt;/strong&gt; 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 &lt;code&gt;INT IDENTITY&lt;/code&gt; or &lt;code&gt;BIGINT&lt;/code&gt;.&lt;/p&gt;

&lt;p&gt;A &lt;strong&gt;nonclustered index&lt;/strong&gt; 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.&lt;/p&gt;

&lt;h2&gt;
  
  
  Index the columns you filter and join on
&lt;/h2&gt;

&lt;p&gt;Start with the queries that matter most. Look at their &lt;code&gt;WHERE&lt;/code&gt;, &lt;code&gt;JOIN&lt;/code&gt; and &lt;code&gt;ORDER BY&lt;/code&gt; clauses: those columns are your index candidates.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="c1"&gt;-- Query we want to speed up&lt;/span&gt;
&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;EmployeeId&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;PayCode&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;Amount&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;dbo&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;PayrollLine&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;PayrollRunId&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="o"&gt;@&lt;/span&gt;&lt;span class="n"&gt;RunId&lt;/span&gt;
  &lt;span class="k"&gt;AND&lt;/span&gt; &lt;span class="n"&gt;PayCode&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;'BASIC'&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;

&lt;span class="c1"&gt;-- Supporting index&lt;/span&gt;
&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="n"&gt;NONCLUSTERED&lt;/span&gt; &lt;span class="k"&gt;INDEX&lt;/span&gt; &lt;span class="n"&gt;IX_PayrollLine_Run_PayCode&lt;/span&gt;
&lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="n"&gt;dbo&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;PayrollLine&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;PayrollRunId&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;PayCode&lt;/span&gt;&lt;span class="p"&gt;);&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h2&gt;
  
  
  Get the key column order right
&lt;/h2&gt;

&lt;p&gt;SQL Server can seek on an index efficiently only from its leftmost column. Some practical rules:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Put columns used in &lt;strong&gt;equality&lt;/strong&gt; filters (&lt;code&gt;=&lt;/code&gt;) first.&lt;/li&gt;
&lt;li&gt;Put columns used in &lt;strong&gt;range&lt;/strong&gt; filters (&lt;code&gt;&amp;gt;&lt;/code&gt;, &lt;code&gt;BETWEEN&lt;/code&gt;, &lt;code&gt;LIKE 'abc%'&lt;/code&gt;) after them.&lt;/li&gt;
&lt;li&gt;An index on &lt;code&gt;(A, B)&lt;/code&gt; helps queries that filter on &lt;code&gt;A&lt;/code&gt;, or on &lt;code&gt;A&lt;/code&gt; and &lt;code&gt;B&lt;/code&gt;, but usually not queries that filter only on &lt;code&gt;B&lt;/code&gt;.&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Cover queries with INCLUDE to avoid key lookups
&lt;/h2&gt;

&lt;p&gt;If the index has the filter columns but not the selected columns, SQL Server must go back to the clustered index for each row. This shows up as a &lt;strong&gt;Key Lookup&lt;/strong&gt; in the execution plan and gets expensive when many rows match.&lt;/p&gt;

&lt;p&gt;Adding the selected columns with &lt;code&gt;INCLUDE&lt;/code&gt; makes the index &lt;strong&gt;covering&lt;/strong&gt;: the query is answered from the index alone. Included columns are stored only at the leaf level, so they do not affect the sort order.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="n"&gt;NONCLUSTERED&lt;/span&gt; &lt;span class="k"&gt;INDEX&lt;/span&gt; &lt;span class="n"&gt;IX_PayrollLine_Run_PayCode&lt;/span&gt;
&lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="n"&gt;dbo&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;PayrollLine&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;PayrollRunId&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;PayCode&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="n"&gt;INCLUDE&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;EmployeeId&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;Amount&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;WITH&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;DROP_EXISTING&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="k"&gt;ON&lt;/span&gt;&lt;span class="p"&gt;);&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Do not include every column "just in case". Wide indexes cost storage, memory and write time.&lt;/p&gt;

&lt;h2&gt;
  
  
  Find and remove unused indexes
&lt;/h2&gt;

&lt;p&gt;Every index is maintained on every write, whether or not anything reads it. This query lists nonclustered indexes with many writes and few reads since the last restart:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt;
    &lt;span class="n"&gt;OBJECT_NAME&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;i&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;object_id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="k"&gt;table_name&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="n"&gt;i&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;name&lt;/span&gt;                   &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;index_name&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="k"&gt;ISNULL&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;s&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;user_seeks&lt;/span&gt; &lt;span class="o"&gt;+&lt;/span&gt; &lt;span class="n"&gt;s&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;user_scans&lt;/span&gt; &lt;span class="o"&gt;+&lt;/span&gt; &lt;span class="n"&gt;s&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;user_lookups&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="k"&gt;reads&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="k"&gt;ISNULL&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;s&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;user_updates&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;writes&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;sys&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;indexes&lt;/span&gt; &lt;span class="n"&gt;i&lt;/span&gt;
&lt;span class="k"&gt;LEFT&lt;/span&gt; &lt;span class="k"&gt;JOIN&lt;/span&gt; &lt;span class="n"&gt;sys&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;dm_db_index_usage_stats&lt;/span&gt; &lt;span class="n"&gt;s&lt;/span&gt;
       &lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="n"&gt;s&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;object_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;i&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;object_id&lt;/span&gt;
      &lt;span class="k"&gt;AND&lt;/span&gt; &lt;span class="n"&gt;s&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;index_id&lt;/span&gt;  &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;i&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;index_id&lt;/span&gt;
      &lt;span class="k"&gt;AND&lt;/span&gt; &lt;span class="n"&gt;s&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;database_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;DB_ID&lt;/span&gt;&lt;span class="p"&gt;()&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;OBJECTPROPERTY&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;i&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;object_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s1"&gt;'IsUserTable'&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;
  &lt;span class="k"&gt;AND&lt;/span&gt; &lt;span class="n"&gt;i&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;type_desc&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;'NONCLUSTERED'&lt;/span&gt;
  &lt;span class="k"&gt;AND&lt;/span&gt; &lt;span class="n"&gt;i&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;is_primary_key&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;
  &lt;span class="k"&gt;AND&lt;/span&gt; &lt;span class="n"&gt;i&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;is_unique_constraint&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;
&lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="k"&gt;reads&lt;/span&gt; &lt;span class="k"&gt;ASC&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;writes&lt;/span&gt; &lt;span class="k"&gt;DESC&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Usage statistics reset when SQL Server restarts, so make sure the server has been running through a full business cycle, including month-end jobs, before dropping anything.&lt;/p&gt;

&lt;h2&gt;
  
  
  Treat missing-index suggestions with care
&lt;/h2&gt;

&lt;p&gt;SQL Server records indexes it wishes it had in &lt;code&gt;sys.dm_db_missing_index_details&lt;/code&gt;, and execution plans show them as green hints. They are useful clues, but the optimizer suggests them one query at a time. It does not consider existing indexes, write cost or overlap, and it often lists columns in a poor order. Combine similar suggestions into one well-designed index instead of creating each one.&lt;/p&gt;

&lt;h2&gt;
  
  
  Keep statistics up to date
&lt;/h2&gt;

&lt;p&gt;The optimizer chooses plans using &lt;strong&gt;statistics&lt;/strong&gt; about how values are distributed. Stale statistics on large tables can make a perfect index look unattractive. Keep &lt;code&gt;AUTO_UPDATE_STATISTICS&lt;/code&gt; on, and for large tables that change heavily, schedule regular statistics updates as part of maintenance.&lt;/p&gt;

&lt;h2&gt;
  
  
  What about fragmentation?
&lt;/h2&gt;

&lt;p&gt;On modern SSD storage, fragmentation matters much less than it used to. Rebuilding every index every night mostly burns CPU and log space. Focus on statistics and on indexes that genuinely support your queries; rebuild or reorganise only where you can measure a benefit.&lt;/p&gt;

&lt;h2&gt;
  
  
  Measure before and after
&lt;/h2&gt;

&lt;p&gt;Always compare before and after. Run the query with &lt;code&gt;SET STATISTICS IO, TIME ON&lt;/code&gt; and compare logical reads and CPU time, or compare the query in Query Store. Fewer logical reads is the clearest sign an index is helping.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SET&lt;/span&gt; &lt;span class="k"&gt;STATISTICS&lt;/span&gt; &lt;span class="n"&gt;IO&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="nb"&gt;TIME&lt;/span&gt; &lt;span class="k"&gt;ON&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;span class="c1"&gt;-- run the query here&lt;/span&gt;
&lt;span class="k"&gt;SET&lt;/span&gt; &lt;span class="k"&gt;STATISTICS&lt;/span&gt; &lt;span class="n"&gt;IO&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="nb"&gt;TIME&lt;/span&gt; &lt;span class="k"&gt;OFF&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h2&gt;
  
  
  Quick rules to remember
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;Index for your most important queries, not for every column.&lt;/li&gt;
&lt;li&gt;Equality columns first, range columns next, selected columns in &lt;code&gt;INCLUDE&lt;/code&gt;.&lt;/li&gt;
&lt;li&gt;Review unused and duplicate indexes regularly.&lt;/li&gt;
&lt;li&gt;Missing-index hints are suggestions, not instructions.&lt;/li&gt;
&lt;li&gt;Prove the benefit with logical reads, not guesswork.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;Related reading:&lt;/strong&gt;&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;&lt;a href="https://sqlcloudlab.blogspot.com/2026/10/how-to-read-sql-server-execution-plan.html" rel="noopener noreferrer"&gt;How to Read a SQL Server Execution Plan&lt;/a&gt;&lt;/li&gt;
&lt;li&gt;&lt;a href="https://sqlcloudlab.blogspot.com/2026/10/how-to-find-slow-queries-in-sql-server.html" rel="noopener noreferrer"&gt;How to Find Slow Queries in SQL Server&lt;/a&gt;&lt;/li&gt;
&lt;li&gt;&lt;a href="https://sqlcloudlab.blogspot.com/2026/10/entity-framework-core-performance-8.html" rel="noopener noreferrer"&gt;Entity Framework Core Performance: 8 Common Mistakes&lt;/a&gt;&lt;/li&gt;
&lt;/ul&gt;




&lt;p&gt;&lt;em&gt;More SQL Server guides on my blog, &lt;a href="https://sqlcloudlab.blogspot.com/" rel="noopener noreferrer"&gt;SQL &amp;amp; Cloud Lab&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>sql</category>
      <category>sqlserver</category>
      <category>database</category>
      <category>performance</category>
    </item>
    <item>
      <title>How to Reduce Azure SQL Costs: A Practical Checklist (2026)</title>
      <dc:creator>LingamSuyambu</dc:creator>
      <pubDate>Wed, 11 Nov 2026 04:30:00 +0000</pubDate>
      <link>https://dev.to/lingamsuyambu/how-to-reduce-azure-sql-costs-a-practical-checklist-2026-1l5a</link>
      <guid>https://dev.to/lingamsuyambu/how-to-reduce-azure-sql-costs-a-practical-checklist-2026-1l5a</guid>
      <description>&lt;p&gt;&lt;em&gt;Originally published at &lt;a href="https://sqlcloudlab.blogspot.com/2026/10/how-to-reduce-azure-sql-costs-practical.html" rel="noopener noreferrer"&gt;SQL &amp;amp; Cloud Lab&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

&lt;p&gt;&lt;em&gt;This post was drafted with AI assistance.&lt;/em&gt;&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto/https%3A%2F%2Fblogger.googleusercontent.com%2Fimg%2Fb%2FR29vZ2xl%2FAVvXsEh1izHA3Dk6sMauv56U_pPDomD5E14DLwWRiym3EqkMKV2XN-0abf7k4lU_HioVcsJCTchIFVe6y6M_GeJsuPUYiGDNiujlrwpFc27pxNwQLB5yWHKh8-HIzp2kDb1Ej7i2779jCTKxFNpRrNGMAOK_X9x8Y9GWWys0rOtf-mqEYyglTpvvElI7aJcEjk0%2Fs1600%2Fimage.webp" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto/https%3A%2F%2Fblogger.googleusercontent.com%2Fimg%2Fb%2FR29vZ2xl%2FAVvXsEh1izHA3Dk6sMauv56U_pPDomD5E14DLwWRiym3EqkMKV2XN-0abf7k4lU_HioVcsJCTchIFVe6y6M_GeJsuPUYiGDNiujlrwpFc27pxNwQLB5yWHKh8-HIzp2kDb1Ej7i2779jCTKxFNpRrNGMAOK_X9x8Y9GWWys0rOtf-mqEYyglTpvvElI7aJcEjk0%2Fs1600%2Fimage.webp" title="Checklist to reduce Azure SQL Database costs" alt="Checklist to reduce Azure SQL Database costs" width="800" height="420"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;This checklist walks through those changes in the order I would apply them: measure first, then fix licensing and commitments, then architecture, then queries.&lt;/p&gt;

&lt;h2&gt;
  
  
  1. Right-size from real usage, not from the old server
&lt;/h2&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Check at least two to four weeks of CPU, data IO, log IO and memory usage.&lt;/li&gt;
&lt;li&gt;In Azure SQL Database, &lt;code&gt;sys.dm_db_resource_stats&lt;/code&gt; shows usage for roughly the last hour at 15-second intervals, and &lt;code&gt;sys.resource_stats&lt;/code&gt; in the &lt;code&gt;master&lt;/code&gt; database keeps longer history.&lt;/li&gt;
&lt;li&gt;If average usage stays low and peaks are short, scale down one tier and watch for a week.
&lt;/li&gt;
&lt;/ul&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="c1"&gt;-- Average and peak usage over the last hour (Azure SQL Database)&lt;/span&gt;
&lt;span class="k"&gt;SELECT&lt;/span&gt;
    &lt;span class="k"&gt;AVG&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;avg_cpu_percent&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;       &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;avg_cpu&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="k"&gt;MAX&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;avg_cpu_percent&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;       &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;max_cpu&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="k"&gt;AVG&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;avg_data_io_percent&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;   &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;avg_data_io&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="k"&gt;AVG&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;avg_log_write_percent&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;avg_log_write&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;sys&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;dm_db_resource_stats&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Scaling an Azure SQL Database up or down is an online operation that usually completes in minutes, so it is low risk to try a smaller size.&lt;/p&gt;

&lt;h2&gt;
  
  
  2. Use Azure Hybrid Benefit
&lt;/h2&gt;

&lt;p&gt;If your organisation already owns SQL Server licences with Software Assurance (or qualifying subscriptions), Azure Hybrid Benefit lets you apply them in the &lt;strong&gt;vCore&lt;/strong&gt; purchasing model so you stop paying for the SQL licence again. For many workloads this is the single largest saving. It does not apply to the DTU model or to serverless compute, so check which model each database uses.&lt;/p&gt;

&lt;h2&gt;
  
  
  3. Buy reserved capacity for steady workloads
&lt;/h2&gt;

&lt;p&gt;Production databases that run 24x7 at a predictable size are the most expensive thing to run on pay-as-you-go. Reserved capacity commits you to a vCore amount for one or three years in exchange for a lower rate, and it can be combined with Azure Hybrid Benefit. Reserve only the baseline you are confident about; burst above it on pay-as-you-go.&lt;/p&gt;

&lt;h2&gt;
  
  
  4. Use serverless for dev, test and intermittent workloads
&lt;/h2&gt;

&lt;p&gt;The General Purpose &lt;strong&gt;serverless&lt;/strong&gt; tier bills compute per second and can auto-pause when idle, leaving only storage charges. It is ideal for development and test databases and for apps used mainly in business hours.&lt;/p&gt;

&lt;p&gt;Watch for anything that keeps the database awake: open SSMS windows, connection pools, monitoring probes and scheduled jobs. A serverless database that never pauses can cost more than a small provisioned one.&lt;/p&gt;

&lt;h2&gt;
  
  
  5. Put small databases in an elastic pool
&lt;/h2&gt;

&lt;p&gt;If you run many small databases with uneven load, such as one per customer, an &lt;strong&gt;elastic pool&lt;/strong&gt; lets them share a fixed amount of compute. Because their peaks rarely happen at the same time, the pool can be much cheaper than sizing each database for its own peak.&lt;/p&gt;

&lt;h2&gt;
  
  
  6. Trim backup, replica and storage costs
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Backup retention:&lt;/strong&gt; keep long-term retention only where policy requires it, and review whether geo-redundant backup storage is needed for non-production databases.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Replicas:&lt;/strong&gt; geo-replicas and failover groups are billed as full databases. Make sure each one is still required.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Allocated space:&lt;/strong&gt; after large deletes, check allocated versus used space before paying for a bigger maximum size.&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  7. Tune the top queries before buying a bigger tier
&lt;/h2&gt;

&lt;p&gt;A missing index or one bad query can push a database two tiers higher than it needs. Before scaling up, open &lt;strong&gt;Query Store&lt;/strong&gt;, sort by total CPU or duration, and fix the top five queries. That often frees enough headroom to scale down instead.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="c1"&gt;-- Top 5 queries by total CPU time (Query Store)&lt;/span&gt;
&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;TOP&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;5&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="n"&gt;q&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;query_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="k"&gt;SUM&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;rs&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;count_executions&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;executions&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="k"&gt;SUM&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;rs&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;avg_cpu_time&lt;/span&gt; &lt;span class="o"&gt;*&lt;/span&gt; &lt;span class="n"&gt;rs&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;count_executions&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;/&lt;/span&gt; &lt;span class="mi"&gt;1000&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;total_cpu_ms&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="k"&gt;MAX&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;qt&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;query_sql_text&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;query_text&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;sys&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;query_store_runtime_stats&lt;/span&gt; &lt;span class="n"&gt;rs&lt;/span&gt;
&lt;span class="k"&gt;JOIN&lt;/span&gt; &lt;span class="n"&gt;sys&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;query_store_plan&lt;/span&gt; &lt;span class="n"&gt;p&lt;/span&gt;        &lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="n"&gt;rs&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;plan_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;p&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;plan_id&lt;/span&gt;
&lt;span class="k"&gt;JOIN&lt;/span&gt; &lt;span class="n"&gt;sys&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;query_store_query&lt;/span&gt; &lt;span class="n"&gt;q&lt;/span&gt;       &lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="n"&gt;p&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;query_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;q&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;query_id&lt;/span&gt;
&lt;span class="k"&gt;JOIN&lt;/span&gt; &lt;span class="n"&gt;sys&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;query_store_query_text&lt;/span&gt; &lt;span class="n"&gt;qt&lt;/span&gt; &lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="n"&gt;q&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;query_text_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;qt&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;query_text_id&lt;/span&gt;
&lt;span class="k"&gt;GROUP&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;q&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;query_id&lt;/span&gt;
&lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;total_cpu_ms&lt;/span&gt; &lt;span class="k"&gt;DESC&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h2&gt;
  
  
  8. Set budgets, alerts and review Azure Advisor
&lt;/h2&gt;

&lt;p&gt;Savings drift back without monitoring. In &lt;strong&gt;Cost Management&lt;/strong&gt;, create a budget per subscription or resource group with alerts at 80 and 100 percent. Review &lt;strong&gt;Azure Advisor&lt;/strong&gt; cost recommendations monthly; it flags idle and under-used databases for you.&lt;/p&gt;

&lt;h2&gt;
  
  
  Quick checklist
&lt;/h2&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Action&lt;/th&gt;
&lt;th&gt;Effort&lt;/th&gt;
&lt;th&gt;Typical impact&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Right-size from real usage&lt;/td&gt;
&lt;td&gt;Low&lt;/td&gt;
&lt;td&gt;High&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Azure Hybrid Benefit&lt;/td&gt;
&lt;td&gt;Low&lt;/td&gt;
&lt;td&gt;High (if licences exist)&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Reserved capacity&lt;/td&gt;
&lt;td&gt;Low&lt;/td&gt;
&lt;td&gt;High for 24x7 workloads&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Serverless for dev/test&lt;/td&gt;
&lt;td&gt;Low&lt;/td&gt;
&lt;td&gt;Medium&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Elastic pools&lt;/td&gt;
&lt;td&gt;Medium&lt;/td&gt;
&lt;td&gt;Medium to high&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Backup and replica review&lt;/td&gt;
&lt;td&gt;Low&lt;/td&gt;
&lt;td&gt;Low to medium&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Query tuning&lt;/td&gt;
&lt;td&gt;Medium&lt;/td&gt;
&lt;td&gt;Medium to high&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Budgets and alerts&lt;/td&gt;
&lt;td&gt;Low&lt;/td&gt;
&lt;td&gt;Prevents drift&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;h2&gt;
  
  
  Final thoughts
&lt;/h2&gt;

&lt;p&gt;Start with measurement, because every other decision depends on it. Then apply licensing and commitment discounts to what you truly need, and only then look at architecture and queries. Prices and offers change, so confirm the numbers for your region in the &lt;a href="https://azure.microsoft.com/pricing/calculator/" rel="noopener noreferrer"&gt;Azure Pricing Calculator&lt;/a&gt; before committing.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Related reading:&lt;/strong&gt;&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;&lt;a href="https://sqlcloudlab.blogspot.com/2026/10/blog-post.html" rel="noopener noreferrer"&gt;Azure SQL Database vs SQL Server on Azure VM: Cost Comparison&lt;/a&gt;&lt;/li&gt;
&lt;li&gt;&lt;a href="https://sqlcloudlab.blogspot.com/2026/10/sql-server-index-tuning-for-beginners.html" rel="noopener noreferrer"&gt;SQL Server Index Tuning for Beginners&lt;/a&gt;&lt;/li&gt;
&lt;li&gt;&lt;a href="https://sqlcloudlab.blogspot.com/2026/10/how-to-find-slow-queries-in-sql-server.html" rel="noopener noreferrer"&gt;How to Find Slow Queries in SQL Server&lt;/a&gt;&lt;/li&gt;
&lt;/ul&gt;




&lt;p&gt;&lt;em&gt;More SQL Server guides on my blog, &lt;a href="https://sqlcloudlab.blogspot.com/" rel="noopener noreferrer"&gt;SQL &amp;amp; Cloud Lab&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>sql</category>
      <category>sqlserver</category>
      <category>database</category>
      <category>azure</category>
    </item>
    <item>
      <title>How to Monitor SQL Server Agent Job Failures (Alerts, Queries and Best Practices)</title>
      <dc:creator>LingamSuyambu</dc:creator>
      <pubDate>Mon, 09 Nov 2026 04:30:00 +0000</pubDate>
      <link>https://dev.to/lingamsuyambu/how-to-monitor-sql-server-agent-job-failures-alerts-queries-and-best-practices-27gf</link>
      <guid>https://dev.to/lingamsuyambu/how-to-monitor-sql-server-agent-job-failures-alerts-queries-and-best-practices-27gf</guid>
      <description>&lt;p&gt;&lt;em&gt;Originally published at &lt;a href="https://sqlcloudlab.blogspot.com/2026/10/how-to-monitor-sql-server-agent-job.html" rel="noopener noreferrer"&gt;SQL &amp;amp; Cloud Lab&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

&lt;p&gt;&lt;em&gt;This post was drafted with AI assistance.&lt;/em&gt;&lt;/p&gt;

&lt;p&gt;SQL Server Agent runs the jobs that keep a database healthy: backups, index and statistics maintenance, ETL loads and cleanup. When a job fails silently, nobody notices until something breaks, often a restore that turns out to have no recent backup. This guide shows how to get notified about &lt;strong&gt;SQL Server Agent job failures&lt;/strong&gt;, how to query job history, and how to catch jobs that hang instead of failing.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto/https%3A%2F%2Fblogger.googleusercontent.com%2Fimg%2Fb%2FR29vZ2xl%2FAVvXsEhi0FPTMlfIfaprPpk-GDyTb3qB2XIHCfXlPuktZxqX2ZknnL9LPGno5TIWMMcYSpajGz_VmPRdF0_taGRFogwswk-syzpX1QKKO3Noj7U9QhReeKsaESXHoXvOZ51OS3pMcTMtWHws41E9DnUWHteFiZ9CMLtuCEaMLmOiGwTLkVTUINGb6AShovh3z34%2Fs1600%2Fimage.webp" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto/https%3A%2F%2Fblogger.googleusercontent.com%2Fimg%2Fb%2FR29vZ2xl%2FAVvXsEhi0FPTMlfIfaprPpk-GDyTb3qB2XIHCfXlPuktZxqX2ZknnL9LPGno5TIWMMcYSpajGz_VmPRdF0_taGRFogwswk-syzpX1QKKO3Noj7U9QhReeKsaESXHoXvOZ51OS3pMcTMtWHws41E9DnUWHteFiZ9CMLtuCEaMLmOiGwTLkVTUINGb6AShovh3z34%2Fs1600%2Fimage.webp" title="SQL Server Agent job failure monitoring with Database Mail alerts" alt="SQL Server Agent job failure monitoring with Database Mail alerts" width="800" height="420"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;h2&gt;
  
  
  Step 1: set up Database Mail
&lt;/h2&gt;

&lt;p&gt;Agent notifications need Database Mail. Configure a profile in SSMS (Management, Database Mail) or with T-SQL, then send a test email:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;EXEC&lt;/span&gt; &lt;span class="n"&gt;msdb&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;dbo&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;sp_send_dbmail&lt;/span&gt;
     &lt;span class="o"&gt;@&lt;/span&gt;&lt;span class="n"&gt;profile_name&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;'DBA Alerts'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
     &lt;span class="o"&gt;@&lt;/span&gt;&lt;span class="n"&gt;recipients&lt;/span&gt;   &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;'dba-team@example.com'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
     &lt;span class="o"&gt;@&lt;/span&gt;&lt;span class="n"&gt;subject&lt;/span&gt;      &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;'Database Mail test'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
     &lt;span class="o"&gt;@&lt;/span&gt;&lt;span class="n"&gt;body&lt;/span&gt;         &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="s1"&gt;'If you can read this, Database Mail works.'&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Then, in SQL Server Agent properties, open &lt;strong&gt;Alert System&lt;/strong&gt;, tick &lt;em&gt;Enable mail profile&lt;/em&gt;, choose the profile and &lt;strong&gt;restart the Agent service&lt;/strong&gt;. Forgetting the restart is the most common reason notifications never arrive.&lt;/p&gt;

&lt;h2&gt;
  
  
  Step 2: create an operator
&lt;/h2&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;EXEC&lt;/span&gt; &lt;span class="n"&gt;msdb&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;dbo&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;sp_add_operator&lt;/span&gt;
     &lt;span class="o"&gt;@&lt;/span&gt;&lt;span class="n"&gt;name&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;N&lt;/span&gt;&lt;span class="s1"&gt;'DBA Team'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
     &lt;span class="o"&gt;@&lt;/span&gt;&lt;span class="n"&gt;email_address&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;N&lt;/span&gt;&lt;span class="s1"&gt;'dba-team@example.com'&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Use a shared team mailbox or distribution list, not one person's address, so alerts are not lost when someone is on leave.&lt;/p&gt;

&lt;h2&gt;
  
  
  Step 3: notify on failure for every job
&lt;/h2&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="c1"&gt;-- Find jobs with no failure notification&lt;/span&gt;
&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;j&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;name&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;msdb&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;dbo&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;sysjobs&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;j&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;j&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;enabled&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt; &lt;span class="k"&gt;AND&lt;/span&gt; &lt;span class="n"&gt;j&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;notify_level_email&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;

&lt;span class="c1"&gt;-- Add notification to a job (2 = on failure)&lt;/span&gt;
&lt;span class="k"&gt;EXEC&lt;/span&gt; &lt;span class="n"&gt;msdb&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;dbo&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;sp_update_job&lt;/span&gt;
     &lt;span class="o"&gt;@&lt;/span&gt;&lt;span class="n"&gt;job_name&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;N&lt;/span&gt;&lt;span class="s1"&gt;'Nightly Full Backup'&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
     &lt;span class="o"&gt;@&lt;/span&gt;&lt;span class="n"&gt;notify_level_email&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;2&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
     &lt;span class="o"&gt;@&lt;/span&gt;&lt;span class="n"&gt;notify_email_operator_name&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;N&lt;/span&gt;&lt;span class="s1"&gt;'DBA Team'&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Make this part of your job deployment standard, so new jobs never go live without it.&lt;/p&gt;

&lt;h2&gt;
  
  
  Step 4: review failures with a query
&lt;/h2&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;j&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;name&lt;/span&gt;                                   &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;job_name&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="n"&gt;h&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;step_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="n"&gt;h&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;step_name&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="n"&gt;msdb&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;dbo&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;agent_datetime&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;h&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;run_date&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;h&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;run_time&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;run_at&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="n"&gt;h&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;run_duration&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="n"&gt;h&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;message&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;msdb&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;dbo&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;sysjobhistory&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;h&lt;/span&gt;
&lt;span class="k"&gt;JOIN&lt;/span&gt; &lt;span class="n"&gt;msdb&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;dbo&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;sysjobs&lt;/span&gt;       &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;j&lt;/span&gt; &lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="n"&gt;j&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;job_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;h&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;job_id&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;h&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;run_status&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;          &lt;span class="c1"&gt;-- 0 = failed&lt;/span&gt;
  &lt;span class="k"&gt;AND&lt;/span&gt; &lt;span class="n"&gt;msdb&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;dbo&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;agent_datetime&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;h&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;run_date&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;h&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;run_time&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;&amp;gt;=&lt;/span&gt; &lt;span class="n"&gt;DATEADD&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;day&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="o"&gt;-&lt;/span&gt;&lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;GETDATE&lt;/span&gt;&lt;span class="p"&gt;())&lt;/span&gt;
&lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;run_at&lt;/span&gt; &lt;span class="k"&gt;DESC&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Job history is trimmed by default (1,000 rows in total, 100 per job), so busy servers may lose history quickly. Increase the limits in Agent properties, under History, or keep your own copy in a DBA database.&lt;/p&gt;

&lt;h2&gt;
  
  
  Step 5: catch long-running and stuck jobs
&lt;/h2&gt;

&lt;p&gt;A job that hangs never fails, so it never sends a failure email. Check for jobs still running past a threshold:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;j&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;name&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="n"&gt;ja&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;start_execution_date&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
       &lt;span class="n"&gt;DATEDIFF&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;minute&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;ja&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;start_execution_date&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;GETDATE&lt;/span&gt;&lt;span class="p"&gt;())&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;minutes_running&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;msdb&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;dbo&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;sysjobactivity&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;ja&lt;/span&gt;
&lt;span class="k"&gt;JOIN&lt;/span&gt; &lt;span class="n"&gt;msdb&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;dbo&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;sysjobs&lt;/span&gt;        &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;j&lt;/span&gt; &lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="n"&gt;j&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;job_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;ja&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;job_id&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;ja&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;session_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="k"&gt;MAX&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;session_id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;msdb&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;dbo&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;syssessions&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
  &lt;span class="k"&gt;AND&lt;/span&gt; &lt;span class="n"&gt;ja&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;start_execution_date&lt;/span&gt; &lt;span class="k"&gt;IS&lt;/span&gt; &lt;span class="k"&gt;NOT&lt;/span&gt; &lt;span class="k"&gt;NULL&lt;/span&gt;
  &lt;span class="k"&gt;AND&lt;/span&gt; &lt;span class="n"&gt;ja&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;stop_execution_date&lt;/span&gt; &lt;span class="k"&gt;IS&lt;/span&gt; &lt;span class="k"&gt;NULL&lt;/span&gt;
  &lt;span class="k"&gt;AND&lt;/span&gt; &lt;span class="n"&gt;DATEDIFF&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;minute&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;ja&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;start_execution_date&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;GETDATE&lt;/span&gt;&lt;span class="p"&gt;())&lt;/span&gt; &lt;span class="o"&gt;&amp;gt;&lt;/span&gt; &lt;span class="mi"&gt;120&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Wrap this in a small Agent job of its own that emails the team when rows are returned.&lt;/p&gt;

&lt;h2&gt;
  
  
  Step 6: better error details
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Step output:&lt;/strong&gt; in each job step's Advanced page, log output to a table or a file. The history message is truncated, but the full output often explains the failure.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Error alerts:&lt;/strong&gt; create Agent alerts for severities 17 to 25 and for errors 823, 824 and 825 (I/O and corruption warnings), all sent to the same operator.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Retry attempts:&lt;/strong&gt; for steps that hit transient issues, such as a network share being briefly unavailable, set a retry count and interval on the step.&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Don't forget the Agent itself
&lt;/h2&gt;

&lt;p&gt;If the SQL Server Agent service is stopped, no jobs run and no failure emails are sent. Set the service to start automatically, and monitor it from outside SQL Server, for example with your server monitoring tool or Windows service alerts.&lt;/p&gt;

&lt;h2&gt;
  
  
  Azure notes
&lt;/h2&gt;

&lt;p&gt;Azure SQL Managed Instance and SQL Server on Azure VMs include SQL Server Agent. Azure SQL Database does not; use &lt;strong&gt;Elastic Jobs&lt;/strong&gt;, Azure Automation or Azure Data Factory for scheduling there, and Azure Monitor alerts for failure notifications.&lt;/p&gt;

&lt;h2&gt;
  
  
  Final thoughts
&lt;/h2&gt;

&lt;p&gt;Reliable job monitoring takes an hour to set up: Database Mail, one team operator, failure notifications on every job, a daily failure query, a check for long-running jobs and severity alerts. That hour can save you from discovering a week of failed backups at the worst possible moment.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Related reading:&lt;/strong&gt;&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;&lt;a href="https://sqlcloudlab.blogspot.com/2026/10/dbcc-checkdb-sql-server-corruption.html" rel="noopener noreferrer"&gt;DBCC CHECKDB: Detect and Handle Database Corruption&lt;/a&gt;&lt;/li&gt;
&lt;li&gt;&lt;a href="https://sqlcloudlab.blogspot.com/2026/10/how-to-fix-sql-server-deadlocks-causes.html" rel="noopener noreferrer"&gt;How to Fix SQL Server Deadlocks&lt;/a&gt;&lt;/li&gt;
&lt;li&gt;&lt;a href="https://sqlcloudlab.blogspot.com/2026/10/how-to-find-slow-queries-in-sql-server.html" rel="noopener noreferrer"&gt;How to Find Slow Queries in SQL Server&lt;/a&gt;&lt;/li&gt;
&lt;/ul&gt;




&lt;p&gt;&lt;em&gt;More SQL Server guides on my blog, &lt;a href="https://sqlcloudlab.blogspot.com/" rel="noopener noreferrer"&gt;SQL &amp;amp; Cloud Lab&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>sql</category>
      <category>sqlserver</category>
      <category>database</category>
      <category>dba</category>
    </item>
    <item>
      <title>Azure SQL Database vs SQL Server on Azure VM: Cost Comparison (2026)</title>
      <dc:creator>LingamSuyambu</dc:creator>
      <pubDate>Sun, 08 Nov 2026 12:39:32 +0000</pubDate>
      <link>https://dev.to/lingamsuyambu/azure-sql-database-vs-sql-server-on-azure-vm-cost-comparison-2026-5cfn</link>
      <guid>https://dev.to/lingamsuyambu/azure-sql-database-vs-sql-server-on-azure-vm-cost-comparison-2026-5cfn</guid>
      <description>&lt;p&gt;&lt;em&gt;Originally published at &lt;a href="https://sqlcloudlab.blogspot.com/2026/10/blog-post.html" rel="noopener noreferrer"&gt;SQL &amp;amp; Cloud Lab&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

&lt;p&gt;&lt;em&gt;This post was drafted with AI assistance.&lt;/em&gt;&lt;/p&gt;

&lt;p&gt;If you are moving a SQL Server workload to Azure, the first big decision is where it will run: &lt;strong&gt;Azure SQL Database&lt;/strong&gt; (a fully managed database service) or &lt;strong&gt;SQL Server on an Azure Virtual Machine&lt;/strong&gt; (your own SQL Server, running on a VM you control). Both work well. But they bill you in very different ways, and picking the wrong one can quietly double your monthly bill.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto/https%3A%2F%2Fblogger.googleusercontent.com%2Fimg%2Fb%2FR29vZ2xl%2FAVvXsEhyv5fomi9JrVtOhHYZOLuMlHQ4lvGAqkrUSGujRfJyEQ268kHIushPNymz-ddDJR4gDVH6_kO9vOKEy5uG722dMvPRW9K8fESlFkWWF3ONuDIcDeDngwOCKB9ZGjN_zPmuAh166YcJTFr7_t0TcL-yI2Qb4R0L6b0ven64SvHnF_Hh-PJVlGeJpdy3YIo%2Fs1600%2Fimage.webp" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto/https%3A%2F%2Fblogger.googleusercontent.com%2Fimg%2Fb%2FR29vZ2xl%2FAVvXsEhyv5fomi9JrVtOhHYZOLuMlHQ4lvGAqkrUSGujRfJyEQ268kHIushPNymz-ddDJR4gDVH6_kO9vOKEy5uG722dMvPRW9K8fESlFkWWF3ONuDIcDeDngwOCKB9ZGjN_zPmuAh166YcJTFr7_t0TcL-yI2Qb4R0L6b0ven64SvHnF_Hh-PJVlGeJpdy3YIo%2Fs1600%2Fimage.webp" title="Azure SQL Database vs SQL Server on Azure VM: Cost Comparison" alt="Azure SQL Database vs SQL Server on Azure VM cost comparison" width="800" height="420"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;This guide breaks down what you actually pay for in each option, where the hidden costs are, and a simple way to decide.&lt;/p&gt;

&lt;h2&gt;
  
  
  Quick answer
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Choose Azure SQL Database&lt;/strong&gt; for new applications, single databases, variable or spiky workloads, and teams that do not want to manage patching, backups or high availability.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Choose SQL Server on an Azure VM&lt;/strong&gt; when you need full instance control: SQL Agent jobs across databases, SSIS/SSRS on the same box, OS-level access, specific SQL Server versions, or third-party software installed next to SQL Server.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Consider Azure SQL Managed Instance&lt;/strong&gt; when you want near-full SQL Server compatibility (SQL Agent, cross-database queries) but still want Microsoft to handle patching and backups.&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  What you pay for: Azure SQL Database
&lt;/h2&gt;

&lt;p&gt;Azure SQL Database is a platform service. Microsoft runs the OS, SQL Server engine, patching, backups and built-in high availability. You pay for:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Compute.&lt;/strong&gt; Either the &lt;em&gt;DTU model&lt;/em&gt; (a bundled measure of CPU, memory and I/O, simple for small apps) or the &lt;em&gt;vCore model&lt;/em&gt; (you pick cores and memory, and it supports Azure Hybrid Benefit and reserved capacity).&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Storage.&lt;/strong&gt; Data size, billed per GB (included up to a limit in DTU tiers).&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Backup storage.&lt;/strong&gt; Point-in-time backups are included up to a point. Long retention or geo-redundant backup storage adds cost.&lt;/li&gt;
&lt;/ul&gt;

&lt;h3&gt;
  
  
  Serverless: pay only while the database is active
&lt;/h3&gt;

&lt;p&gt;The &lt;strong&gt;serverless&lt;/strong&gt; tier bills compute per second while the database is in use, and can &lt;strong&gt;auto-pause&lt;/strong&gt; after an idle period. While paused, you pay only for storage. This is ideal for dev/test databases and apps used mainly during business hours.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Watch out:&lt;/strong&gt; any open connection keeps the database awake. SSMS left open, application connection pools and health probes can all prevent auto-pause, so a "cheap" serverless database can end up billing all day.&lt;/p&gt;

&lt;h3&gt;
  
  
  The free offer
&lt;/h3&gt;

&lt;p&gt;Microsoft offers up to &lt;strong&gt;10 free General Purpose serverless databases per subscription&lt;/strong&gt;. Each one gets &lt;strong&gt;100,000 vCore seconds of compute, 32 GB of data and 32 GB of backup storage every month&lt;/strong&gt;, for the lifetime of the subscription. That is enough for learning, small side projects and proofs of concept, as long as the database is allowed to pause.&lt;/p&gt;

&lt;h2&gt;
  
  
  What you pay for: SQL Server on an Azure VM
&lt;/h2&gt;

&lt;p&gt;On a VM, you get a full Windows or Linux server with SQL Server installed. You control everything, and you pay for everything:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;VM compute.&lt;/strong&gt; Billed per hour the VM is running, whether SQL Server is busy or idle.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;SQL Server license.&lt;/strong&gt; Either included in the VM price (pay-as-you-go images) or brought from your own licenses with &lt;strong&gt;Azure Hybrid Benefit&lt;/strong&gt;. Developer and Express editions have no license cost.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Managed disks.&lt;/strong&gt; OS, data and log disks. Premium SSD or Ultra disks for production performance can be a significant part of the bill.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Backups.&lt;/strong&gt; Azure Backup or backup to Blob storage, plus the storage it uses.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;High availability.&lt;/strong&gt; An Always On availability group means at least a second VM, plus its license (unless covered by your license terms) and disks.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Your time.&lt;/strong&gt; Patching, monitoring, backup testing and failover planning are now your team's job.&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Side-by-side comparison
&lt;/h2&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Cost factor&lt;/th&gt;
&lt;th&gt;Azure SQL Database&lt;/th&gt;
&lt;th&gt;SQL Server on Azure VM&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Compute billing&lt;/td&gt;
&lt;td&gt;Per DTU or vCore; serverless bills per second&lt;/td&gt;
&lt;td&gt;Per hour the VM runs&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Idle cost&lt;/td&gt;
&lt;td&gt;Near zero on serverless when paused&lt;/td&gt;
&lt;td&gt;Full price unless you shut the VM down&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;SQL license&lt;/td&gt;
&lt;td&gt;Included, or reduced with Azure Hybrid Benefit&lt;/td&gt;
&lt;td&gt;Included in VM price, or bring your own&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Backups&lt;/td&gt;
&lt;td&gt;Built in (extra for long retention)&lt;/td&gt;
&lt;td&gt;You configure and pay for storage&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;High availability&lt;/td&gt;
&lt;td&gt;Built in&lt;/td&gt;
&lt;td&gt;Extra VMs and licensing&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Patching and admin&lt;/td&gt;
&lt;td&gt;Handled by Microsoft&lt;/td&gt;
&lt;td&gt;Your responsibility&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Instance-level features&lt;/td&gt;
&lt;td&gt;Limited (no SQL Agent, no cross-database queries)&lt;/td&gt;
&lt;td&gt;Everything SQL Server supports&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;h2&gt;
  
  
  Five ways to cut the bill (either option)
&lt;/h2&gt;

&lt;ol&gt;
&lt;li&gt;
&lt;strong&gt;Use Azure Hybrid Benefit&lt;/strong&gt; if your company already owns SQL Server licenses with Software Assurance. It often removes the largest single line item.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Buy reserved capacity&lt;/strong&gt; (1 or 3 years) for steady production workloads. Pay-as-you-go is the most expensive way to run something 24x7.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Right-size after a month.&lt;/strong&gt; Most databases are over-provisioned at migration. Check actual CPU, memory and I/O, then scale down.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Use serverless or shut down non-production.&lt;/strong&gt; Dev and test environments rarely need to run nights and weekends.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Fix the queries first.&lt;/strong&gt; A missing index can force you into a tier two sizes larger than needed. Tuning is often the cheapest "upgrade".&lt;/li&gt;
&lt;/ol&gt;

&lt;h2&gt;
  
  
  A simple decision checklist
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;Does the app need SQL Agent, cross-database queries or CLR? → Managed Instance or VM.&lt;/li&gt;
&lt;li&gt;Do you need OS access or software installed on the server? → VM.&lt;/li&gt;
&lt;li&gt;Is it one database with variable load? → Azure SQL Database, likely serverless.&lt;/li&gt;
&lt;li&gt;Is it many small databases with uneven load? → Azure SQL Database elastic pool.&lt;/li&gt;
&lt;li&gt;Do you own SQL licenses with Software Assurance? → Factor in Azure Hybrid Benefit for every option.&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Practical tip: measure before you migrate
&lt;/h2&gt;

&lt;p&gt;Before choosing a tier, capture at least two weeks of real usage from your current server: average and peak CPU, memory, IOPS and log write throughput. Many teams size Azure based on the old server's hardware, not its actual load, and end up paying for capacity they never use. Start one size smaller than you think, monitor for a week after go-live, and scale up only if the numbers say so. Scaling Azure SQL Database up or down takes minutes.&lt;/p&gt;

&lt;h2&gt;
  
  
  Final thoughts
&lt;/h2&gt;

&lt;p&gt;Azure SQL Database usually wins on total cost for new and modern applications, because you stop paying for idle servers and admin work. SQL Server on a VM wins when you genuinely need full control or legacy features. Before you decide, price both options with the &lt;a href="https://azure.microsoft.com/pricing/calculator/" rel="noopener noreferrer"&gt;Azure Pricing Calculator&lt;/a&gt; using your real workload numbers, since prices vary by region and change over time.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Next in this series:&lt;/strong&gt; How to reduce Azure SQL costs, a practical checklist.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Related reading:&lt;/strong&gt;&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;&lt;a href="https://sqlcloudlab.blogspot.com/2026/10/how-to-reduce-azure-sql-costs-practical.html" rel="noopener noreferrer"&gt;How to Reduce Azure SQL Costs: A Practical Checklist&lt;/a&gt;&lt;/li&gt;
&lt;li&gt;&lt;a href="https://sqlcloudlab.blogspot.com/2026/10/sql-server-query-store-practical.html" rel="noopener noreferrer"&gt;SQL Server Query Store: A Practical Beginner's Guide&lt;/a&gt;&lt;/li&gt;
&lt;li&gt;&lt;a href="https://sqlcloudlab.blogspot.com/2026/10/how-to-monitor-sql-server-agent-job.html" rel="noopener noreferrer"&gt;How to Monitor SQL Server Agent Job Failures&lt;/a&gt;&lt;/li&gt;
&lt;/ul&gt;




&lt;p&gt;&lt;em&gt;More SQL Server guides on my blog, &lt;a href="https://sqlcloudlab.blogspot.com/" rel="noopener noreferrer"&gt;SQL &amp;amp; Cloud Lab&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>sql</category>
      <category>sqlserver</category>
      <category>database</category>
      <category>azure</category>
    </item>
  </channel>
</rss>
