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
Dee splits a tour bus order across four cooks; three finish fast while Jesse disappears behind a mountain of pancakes and Dee taps a foot at the pass. Casey sums it up: "The team worked. The split didn't."

CXPACKET Wait Stats: What Parallel Waits Mean

February 6, 2011
Pinal Dave
SQL Performance
SQL DMV, SQL Performance, SQL Scripts, SQL Wait Stats

CXPACKET wait stats measure the threads of a parallel query waiting on each other. On Night 6 at the Clipboard Diner, a tour bus of forty shows why splitting work helps, and how one overloaded cook holds everyone up. Learn CXPACKET, CXCONSUMER and the two CXSYNC waits, and how to spot skew.

Read More
Casey refuses to erase the clipboard, photographs it before and after the dinner rush, and subtracts, while Ace photobombs both pictures. Casey finds the answer: "Dinner belonged to the stairs, not the night truck."

Wait Stats Over Time: Measuring One Time Window

February 5, 2011
Pinal Dave
SQL Performance
SQL DMV, SQL Performance, SQL Scripts, SQL Wait Stats

Wait stats over time means measuring the waits of one window, such as your busy hour, instead of reading totals since the last restart. On Night 5 at the Clipboard Diner, Casey photographs the clipboard at 6:00 and at 7:00 and subtracts. Then one script does the same thing with two snapshots of sys.dm_os_wait_stats.

Read More
During the Saturday rush Casey asks each ticket what it waits for, finds three orders stuck behind a finished party in the corner booth, and clears the booth so Ace can fire all three plates. Casey says, "The quiet booth was the head blocker."

Live Wait Stats: What Is Waiting Right Now

February 4, 2011
Pinal Dave
SQL Performance
SQL DMV, SQL Lock, SQL Scripts, SQL Wait Stats

Live wait stats show what every request in SQL Server is waiting on at this moment, and who is in its way. On Night 4 at the Clipboard Diner, the Saturday rush stalls behind a party in booth 7 that isn’t waiting for anything. Then three queries find the waiting tasks, the head blocker and one session’s own waits.

Read More
Late at night Casey reads the whole clipboard, finds the biggest line belongs to the sleeping night baker, and slashes it out with a giant red pen while Ace and Quinn duck. Casey says, "Cross out the sleepers first. Then read."

sys.dm_os_wait_stats: Reading the Wait Stats List

February 3, 2011
Pinal Dave
SQL Performance
SQL DMV, SQL Performance, SQL Scripts, SQL Wait Stats

sys.dm_os_wait_stats is the running list of every wait SQL Server has recorded since its last restart. On Night 3 at the Clipboard Diner, Casey reads the clipboard at 2 AM and crosses out the sleepers. Then one query filters out the harmless waits and shows the share, the average and the signal part of every wait that matters.

Read More
Kit gets the potatoes fast, but every burner is busy, so the order waits on the rail while the potatoes grow bored little faces and Casey hangs a second stopwatch. Quinn sums it up: "Ready to cook, stuck in line. That's a signal wait!"

Signal Wait Stats: CPU Waits vs Resource Waits

February 2, 2011
Pinal Dave
SQL Performance
SQL CPU, SQL DMV, SQL Scripts, SQL Wait Stats

Signal wait stats show how long a SQL Server query sat ready to run, waiting in line for a CPU. On Night 2 at the Clipboard Diner, the potatoes arrive on time and the Garden Hash waits anyway. Then two queries split your server’s waits into the CPU part and the resource part.

Read More
Casey times one Garden Hash: Ace stalls at an empty potato bin, and by the time Quinn serves the plate, a cobweb has grown on the trucker's coffee cup. Casey reads the stopwatch: "Cooking: four minutes. Waiting: nine."

Wait Stats Basics: How Waits Work in SQL Server

February 1, 2011
Pinal Dave
SQL Performance
SQL DMV, SQL Scripts, SQL Wait Stats

Wait stats basics start with one idea: a query in SQL Server is always either working or waiting. On Night 1 at the Clipboard Diner, one plate of Garden Hash shows the three states every query moves through. Then two queries show the same thing on your own server.

Read More
SELECT NEXT VALUE FOR Seq As SeqNumber.

SQL SERVER – Denali – SEQUENCE is not IDENTITY

January 30, 2011
Pinal Dave
SQL Tips and Tricks
SQL Scripts

SEQUENCE is not IDENTITY. A reader asked about the difference: identity belongs to a single column, while a sequence does not depend on any table.

Read More
Previous 1 … 236 237 238 239 240 241 242 … 363 Next
  • Books
  • Testimonials
  • Privacy Policy

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