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 kitchen counter crowded with bowls around one large vermilion bowl of tomatoes

Cached Data Per Object in Memory: Read the Buffer Pool

March 31, 2021
Pinal Dave
SQL Performance
SQL DMV, SQL Memory, SQL Scripts, SQL Server

Cached data per object tells you which tables and indexes fill the buffer pool. This post builds two demo tables, empties their cache and reads part of the data. A query then lists the pages, megabytes and share of each index in memory. It also explains why the view is costly on a big server.

Read More
Gouache painting of a narrow water channel with a vermilion board across it and water flowing past

Disable Adaptive Join for One Query or a Whole Database

March 30, 2021
Pinal Dave
SQL Performance
Execution Plan, SQL Joins, SQL Scripts, SQL Server

To disable adaptive join for one query, add a USE HINT to it. This post also shows a Query Store hint for code you cannot edit and the database setting that switches the feature off for everything. Each switch is tested on a small demo database and checked in the plan.

Read More
Gouache painting of a canal lock with a vermilion sluice wheel between the closed gates

Enable Adaptive Join in SQL Server: Why It Does Not Appear

March 26, 2021
Pinal Dave
SQL Performance
Execution Plan, SQL Joins, SQL Scripts, SQL Server

To enable adaptive join in SQL Server, the compatibility level, the database setting and the query must all agree. This post builds a demo database and probes the plan under each condition. It also corrects older advice whose statements switch the feature off.

Read More
Gouache painting of a blue locomotive on a track beside a platform with small crates and a post with a round vermilion signal

Adaptive Threshold Rows: How an Adaptive Join Chooses

March 25, 2021
Pinal Dave
SQL Performance
Execution Plan, SQL Joins, SQL Scripts, SQL Server

Adaptive threshold rows is the row count at which an adaptive join switches from nested loops to a hash join. This post runs two queries on WideWorldImporters, reads each threshold from the cached plan, and shows which branch ran. The first query stays below its threshold and the second goes above it.

Read More
Gouache painting of a wooden table with a bowl of pears in front of a leaning mirror, with a small vermilion magnifying glass between them

Table or View in SQL Server: Check With OBJECTPROPERTY

March 24, 2021
Pinal Dave
SQL Tips and Tricks
SQL Scripts, SQL Table Operation, SQL View, System Object

To settle table or view in SQL Server, ask OBJECTPROPERTY. This post builds a table, a view, a synonym and a procedure, and reads the answers for each. It also shows a trap with other databases and a catalog query for every object.

Read More
Expensive Query

Find Expensive Queries – SQL in Sixty Seconds #159

March 23, 2021
Pinal Dave
SQL Video
SQL CPU, SQL in Sixty Seconds, SQL Memory, SQL Scripts, SQL Server

Earlier I wrote a blog post about SQL SERVER – List Expensive Queries – Updated March 2021. Let us learn how to Find Expensive Queries.

Read More
Gouache painting of a full wooden spool rack with one vermilion spool left over on the table

Maximum Columns in an Index: 32 Keys and the Way Around

March 22, 2021
Pinal Dave
SQL Tips and Tricks
SQL Column, SQL Index, SQL Scripts, SQL Statistics

The maximum columns in an index is 32 key columns, and a second limit on key size can stop an index earlier. This post builds a 40 column table, shows the error for a 33rd key column, and adds included columns to get around it. It also shows the 900 and 1,700 byte key limits and tests statistics on 33 and 40 columns.

Read More
Previous 1 … 17 18 19 20 21 22 23 … 363 Next
  • Books
  • Testimonials
  • Privacy Policy

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