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 six clay pots on a potting bench, five tipped over and empty, one upright vermilion pot

Drop Tables by Prefix in SQL Server and Azure SQL Database

June 21, 2019
Pinal Dave
SQL Tips and Tricks
SQL Azure, SQL Cursor, SQL Scripts, SQL Table Operation

To drop tables by prefix, list the matching tables first and drop them with a script that quotes every schema and table name. This post shows why the underscore in a prefix is a trap, a one-statement version and a cursor version. It works in SQL Server and in Azure SQL Database.

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
Diagram of the memory buffer with dirty and clean pages: after CHECKPOINT the dirty pages are written to disk and every page in memory is clean; after DBCC DROPCLEANBUFFERS the clean pages are removed from memory and the dirty pages stay

CHECKPOINT vs DBCC DROPCLEANBUFFERS: Impact on Memory

June 17, 2019
Pinal Dave
SQL Tips and Tricks
SQL Cache, SQL Memory, SQL Scripts, SQL Server, SQL Server DBCC

CHECKPOINT vs DBCC DROPCLEANBUFFERS comes down to which pages leave memory. CHECKPOINT writes dirty pages to disk, and DBCC DROPCLEANBUFFERS removes clean pages from the buffer pool. Here is the effect on memory, with results.

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
Gouache painting of an open wooden music box on a piano stool with a jammed vermilion cylinder inside

Error 41368: Memory-Optimized Tables and Transactions

June 11, 2019
Pinal Dave
SQL Tips and Tricks
In-Memory OLTP, SQL Error Messages, SQL Scripts, SQL Transactions, Transaction Isolation

Error 41368 appears when an explicit transaction reads a memory-optimized table at the read committed level. This post reproduces the error, fixes it with a table hint or a database option, and shows the two related errors that the fixes do not cure. It also answers what the option changes for other tables.

Read More
Previous 1 … 71 72 73 74 75 76 77 … 363 Next
  • Books
  • Testimonials
  • Privacy Policy

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