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 drafting table with one rolled blueprint held flat by a vermilion clamp while a basket of other rolled blueprints waits beside it

RESOURCE_SEMAPHORE_QUERY_COMPILE Wait: Check Lock Pages in Memory

September 17, 2019
Pinal Dave
SQL Performance
SQL Memory, SQL Scripts, SQL Server, SQL Wait Stats

RESOURCE_SEMAPHORE_QUERY_COMPILE was the first wait type in a client’s wait statistics. Readers asked how I knew to look at Lock Pages in Memory. Here is the script I used, and the setting that resolved the problem.

Read More
One scythe in a mostly uncut hayfield while three more scythes hang idle on the barn wall.

NonParallelPlanReason: Why a Query Refuses to Go Parallel

September 14, 2019
Pinal Dave
SQL Performance
Execution Plan, MAXDOP, Parallel, SQL Performance

Read NonParallelPlanReason in actual and cached plans, then check MAXDOP, scalar functions, table variables, and query cost.

Read More
A bike rack with one long red chain looped through every slot while a cyclist waits with nowhere to park

Counting Locks per Session With sys.dm_tran_locks

September 13, 2019
Pinal Dave
SQL Performance
SQL DMV, SQL Lock, SQL Server, SQL Transactions

Count locks per session by resource and mode, connect open transactions, and recognize escalation while keeping snapshot limits clear.

Read More
Gouache painting of a garden hose with a kink held by a vermilion clip and a sputtering sprinkler beyond

Query Runs Slow Occasionally: Five Checks to Find Why

September 7, 2019
Pinal Dave
SQL Performance
Maintenance Plan, SQL Lock, SQL Scripts, SQL TempDB, SQL Wait Stats

When a query runs slow occasionally, the cause sits around the query: a lock, a job, a file growth, tempdb or a shortage of worker threads. This post gives one tested T-SQL check for each and a small demo for blocking and file growth. It also says what to do when all five checks find nothing.

Read More
Gouache painting: a row of different shaped wooden pegs and a board with matching holes, one peg that does not fit lying beside the board marked with a vermilion ribbon

In-Memory OLTP Migration Checklist: Which Features Block You

September 5, 2019
Pinal Dave
SQL Performance
In-Memory OLTP, SQL Migration, SQL Scripts, SQL Server Management Studio

An In-Memory OLTP migration checklist from SSMS lists the tables and procedures the engine will refuse. This post tests those refusals on SQL Server 2025, shows which old limits are gone, and adds a query that finds blockers in your own database.

Read More
Active parallel loom threads are inspected separately from preserved guides and worn tools.

SQL SERVER – Active Parallel Requests and Cached Parallel Query History

September 4, 2019
Pinal Dave
SQL Performance
Parallel, SQL CPU, SQL Scripts, SQL Server

I Identify parallel running requests separately from parallel queries recorded in the cache. Those lists answer different performance questions.

Read More
Gouache painting of a track splitting into four parallel tracks with a vermilion switch lever set to the wide side

Force a Parallel Plan: ENABLE_PARALLEL_PLAN_PREFERENCE Hint

August 27, 2019
Pinal Dave
SQL Performance
Parallel, Query Hint, SQL CPU, SQL Scripts

To force a parallel plan for one query, add the ENABLE_PARALLEL_PLAN_PREFERENCE hint to its OPTION clause. This post shows why a cheap query stays serial, runs a demo query with and without the hint, proves the difference from the last actual plan, and explains why the hint belongs in a test and not in every query.

Read More
Previous 1 … 36 37 38 39 40 41 42 … 128 Next
  • Books
  • Testimonials
  • Privacy Policy

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