This month's discount Comprehensive Database Performance Health Check · Testimonials
SQLAuthority with Pinal Dave
Home
  • Consulting
    • Health Check
  • Free Videos
  • All Articles
    • AI
    • SQL Performance
    • SQL Tips and Tricks
    • Interview Questions and Answers
    • SQL Puzzle
    • SQL Video
    • SQLAuthority News
    • Personal
  • Books
  • Consulting
Gouache painting of a shelf of cream pots with plants and one differently shaped vermilion pot

Memory-Optimized TempDB Tables: List Them and Check Status

May 15, 2020
Pinal Dave
SQL Performance
In-Memory OLTP, SQL DMV, SQL Memory, SQL TempDB

Memory-optimized TempDB tables are the system tables that SQL Server 2019 and later can keep in memory. This post shows two queries: one that checks whether the feature is on, and one that lists the tables it covers. It also explains the limits that come with the feature.

Read More
Gouache painting of a large orange pumpkin beside a barn door with smaller pumpkins nearby

Metadata Contention in TempDB: When Memory-Optimized Metadata Helps

May 14, 2020
Pinal Dave
SQL Performance
In-Memory OLTP, SQL Memory, SQL TempDB, SQL Wait Stats

Metadata contention in TempDB happens when many sessions create and drop temporary tables at the same moment. This post reads the waits that point to it, explains why RESOURCE_SEMAPHORE is a different problem, and shows how SQL Server 2019 and later can keep that metadata in memory.

Read More
Gouache painting of a row of open beach umbrellas with one closed vermilion umbrella at the end

Visible Offline Schedulers: Find CPUs SQL Server Ignores

May 12, 2020
Pinal Dave
SQL Performance
SQL CPU, SQL DMV, SQL Monitoring, SQL Server Configuration

Visible offline schedulers are CPUs that Windows shows and SQL Server refuses to use. This post reads the scheduler status with tested queries, compares it with the CPU count and affinity, and explains the two common causes. It also shows the statement that returns the CPUs to automatic affinity.

Read More
Gouache painting of pears spread evenly across a market table with one vermilion pear in the middle

Nonclustered Primary Key Test: Last Page Insert Waits

May 11, 2020
Pinal Dave
SQL Performance
Primary Key, SQL Constraint and Keys, SQL Identity, SQL Wait Stats, Testing

A nonclustered primary key test shows whether a heap fixes last page insert waits. On SQL Server 2025 it doesn’t, because the key index still rises. A lane layout that spreads the inserts removes the latch wait, and the post shows the cost of that layout.

Read More
Gouache painting of an empty wooden pier ending in a lamp post, small boats bunched around its end and a small vermilion buoy

OPTIMIZE_FOR_SEQUENTIAL_KEY: Test the Last Page Insert Fix

May 6, 2020
Pinal Dave
SQL Performance
Primary Key, SQL Identity, SQL Server 2019, SQL Wait Stats, Testing

OPTIMIZE_FOR_SEQUENTIAL_KEY is an index option that targets last page insert contention. This post tests it with 32 connections that insert into a clustered identity key, with the option off and on. The option changes the kind of waiting, and the post shows how to decide whether it helps your table.

Read More
Illustrated cover: Last Page Contention

SQL SERVER – Last Page Insert PAGELATCH_EX Contention Due to Identity Column

May 5, 2020
Pinal Dave
SQL Performance
Primary Key, SQL Constraint and Keys, SQL Identity, SQL Scripts, SQL Server, SQL Server Management Studio, SQL Wait Stats, Testing

Today’s goal is to understand what is the Last Page Insert PAGELATCH_EX Contention Due to Primary Key. Let us start understanding it together.

Read More
Gouache painting of two pottery benches, one cluttered with kept clay scraps and one swept clean beside a vermilion bucket

Recovery Model Insert Test: FULL vs SIMPLE Under Load

May 4, 2020
Pinal Dave
SQL Performance
Best Practices, SQL Backup and Restore, SQL Log, SQL Wait Stats, Testing

A recovery model insert test compares FULL and SIMPLE under a load of 16 connections. The log written is almost the same in both, and the elapsed time depends more on the server than on the model. The real difference is when the log can be reused, and this post shows that step by step.

Read More
Previous 1 … 24 25 26 27 28 29 30 … 128 Next
  • Books
  • Testimonials
  • Privacy Policy

© 2006 – 2026 All rights reserved. pinal @ SQLAuthority.com