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
A walker taking a short straight garden path to a cottage door beside an elaborate hedge maze

Trivial Plan: Why Some Queries Skip Full Optimization

July 1, 2019
Pinal Dave
SQL Performance
Execution Plan, SQL Cache, SQL Performance, SQL Server

Identify a trivial plan in SSMS and cached XML, understand skipped optimizer work, and compare a more complex query without forcing it.

Read More
Gouache painting of a line of boxcars waiting on one track before a vermilion switch lever

PAGELATCH_UP Waits and Suspended Sessions: Find the Hot Page

June 26, 2019
Pinal Dave
SQL Performance
SQL Data Storage, SQL Memory, SQL TempDB, SQL Wait Stats

PAGELATCH_UP waits mean that sessions queue for the same page in memory, and that page can be named. This post reads the waiting sessions, turns the resource into a database, a file and a page, and tells an allocation page from an ordinary one. It then lists the fixes for tempdb and for a user database.

Read More
A string of glowing garden lights with one dark bulb, a replacement waiting on a stepladder.

Removing a Bad Plan From Cache Without Clearing Everything

June 24, 2019
Pinal Dave
SQL Performance
Execution Plan, Parameter Sniffing, SQL Cache, SQL Performance, SQL Server

Remove one bad plan from cache by handle, compare narrower recompilation choices, and fix the cause so the plan stays healthy.

Read More
Gouache painting of a row of wooden trays of sawdust on a workbench with the overflowing one in vermilion

TempDB Performance: Five Settings to Check in SQL Server

June 19, 2019
Pinal Dave
SQL Performance
Best Practices, SQL Data Storage, SQL Scripts, SQL Server, SQL TempDB

TempDB performance depends on five settings that you can read in a few minutes: how big it is, how many data files it has, whether they match, how it grows and where it lives. Three read-only queries check each one. The post also explains what to do with the answers.

Read More
Gouache painting of a vermilion cart rolling crates up a wide ramp onto a boat while a narrow plank stands unused

CHECKPOINT Duration: Can You Speed Up a Manual CHECKPOINT?

June 18, 2019
Pinal Dave
SQL Performance
Best Practices, SQL Memory, SQL Scripts, SQL Server

The CHECKPOINT duration argument sets a target time in seconds. This post times a manual checkpoint on a database with 130 MB of dirty pages, and shows that a long duration slows it badly while a short one does not make it faster. It also shows that a backup flushes dirty pages by itself.

Read More
Gouache painting of a stack of clean folded towels beside a vermilion basket of crumpled muddy ones

Dirty Pages and Clean Pages in the SQL Server Buffer Pool

June 14, 2019
Pinal Dave
SQL Performance
SQL DMV, SQL Memory, SQL Scripts, SQL Server

Dirty pages are pages in memory that SQL Server has changed but not yet written to the data file. This post counts dirty pages and clean pages per database and per object, then watches a CHECKPOINT bring the dirty count to zero. It also explains why you should not try to clean the buffer pool on a production server.

Read More
compiled

SQL SERVER – Natively Compiled Stored Procedures and Location of Compiler

June 12, 2019
Pinal Dave
SQL Performance
In-Memory OLTP, SQL Memory, SQL Scripts, SQL Server

I recently implemented a Memory Optimized Table solution at one of the large financial organizations while working on Natively Compiled Stored Procedures.

Read More
Previous 1 … 39 40 41 42 43 44 45 … 128 Next
  • Books
  • Testimonials
  • Privacy Policy

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