Skip to content

Erik Darling Data

SQL Server Consulting, Education, and Training

Erik Darling Data
  • Blog
  • Learn
    • Pre-Recorded Classes
    • In-Person Events
  • Scripts
    • sp_HumanEvents
    • sp_PressureDetector
    • sp_QuickieStore
  • Contact
  • Request a Call

Category: Memory Grants

Using Views To Reduce Memory Grants In SQL Server

Posted on May 11, 2022May 16, 2022 by Erik Darling

We All Have It


You know those tables, right? The ones where developers went and got lazy or didn’t know any better and decided every string column was going to be gigantic.

They may have read, of course, that SQL …

Posted in Execution Plans, Indexing, Memory Grants, Query Tuning, Server Tuning, Spills, SQL ServerTagged create index in sql server, create temp table sql, DBA, Deadlock, Erik Darling, Erik Darling Data, how to create temp table in sql, Index Tuning, Indexing, Parallelism, Performance, performance tuning, Query, Query Plan, Query Tuning, sp_executesql, SQL, sql indexes, SQL Server, SQL Server Consultant, SQL Server Index, SQL Server Training, sql temp table, temporary table sql

CPU & RAM Don’t Lie: Query Metrics I Care About For Tuning

Posted on May 9, 2022May 10, 2022 by Erik Darling

Discarded


There are metrics that I care and don’t care about when I’m looking for queries to tune.

Metrics I don’t care about:

  • Logical Reads
  • Costs

If a query does “a lot” of reads or has a high “cost”, I …

Posted in Execution Plans, Memory Grants, Query Tuning, SQL ServerTagged create index in sql server, create temp table sql, DBA, Deadlock, Erik Darling, Erik Darling Data, how to create temp table in sql, Index Tuning, Indexing, Parallelism, Performance, performance tuning, Query, Query Plan, Query Tuning, sp_executesql, SQL, sql indexes, SQL Server, SQL Server Consultant, SQL Server Index, SQL Server Training, sql temp table, temporary table sql

Common SQL Server Consulting Advice: Enabling Resource Governor To Fix Memory Grants

Posted on April 26, 2022May 16, 2022 by Erik Darling

With Or Without You


One thing I really hate about Standard Edition is the lack of a way to globally reduce the allowed memory grant default.

Out of the box, any query can come along and ask for ~25% of …

Posted in Consulting, Memory Grants, SQL ServerTagged create index in sql server, create temp table sql, DBA, Deadlock, Erik Darling, Erik Darling Data, how to create temp table in sql, Index Tuning, Indexing, Parallelism, Performance, performance tuning, Query, Query Plan, Query Tuning, sp_executesql, SQL, sql indexes, SQL Server, SQL Server Consultant, SQL Server Index, SQL Server Training, sql temp table, temporary table sql2 Comments on Common SQL Server Consulting Advice: Enabling Resource Governor To Fix Memory Grants

Common SQL Server Consulting Advice: Setting Min Server Memory And Max Server Memory

Posted on April 12, 2022May 16, 2022 by Erik Darling

The Max For The Minimum


I’ve written several hundred thousand times about how important memory is to SQL Server, and to most databases in general. But once you have it, how do you make the most of it?

Well, there’ll …

Posted in Consulting, Memory Grants, Server Tuning, SQL ServerTagged create index in sql server, create temp table sql, DBA, Deadlock, Erik Darling, Erik Darling Data, how to create temp table in sql, Index Tuning, Indexing, Parallelism, Performance, performance tuning, Query, Query Plan, Query Tuning, sp_executesql, SQL, sql indexes, SQL Server, SQL Server Consultant, SQL Server Index, SQL Server Training, sql temp table, temporary table sql5 Comments on Common SQL Server Consulting Advice: Setting Min Server Memory And Max Server Memory

Does My SQL Server Need More Memory?

Posted on March 28, 2022May 16, 2022 by Erik Darling

Work It


Memory is S-Tier crucial for most workloads to run reliably fast. It’s where SQL Server caches data pages, and it’s what it gives to queries to process Sorts and Hashes (among other things, but these are most common).…

Posted in Hardware, Memory Grants, Plan Cache, SQL Server, Wait StatsTagged create index in sql server, create temp table sql, DBA, Deadlock, Erik Darling, Erik Darling Data, how to create temp table in sql, Index Tuning, Indexing, Parallelism, Performance, performance tuning, Query, Query Plan, Query Tuning, sp_executesql, SQL, sql indexes, SQL Server, SQL Server Consultant, SQL Server Index, SQL Server Training, sql temp table, temporary table sql9 Comments on Does My SQL Server Need More Memory?

A Resource Governor Request: Cap Total Query Memory Grants (Without Capping Other Stuff)

Posted on March 23, 2022May 16, 2022 by Erik Darling

Easy Peas


I know what you’re gonna say: You’re gonna say, Erik, you can do this by setting the Maximum Memory Percent for each and every resource pool other than the internal one.

And I’ll tell you something wacky: That …

Posted in Memory Grants, SQL ServerTagged create index in sql server, create temp table sql, DBA, Deadlock, Erik Darling, Erik Darling Data, how to create temp table in sql, Index Tuning, Indexing, Parallelism, Performance, performance tuning, Query, Query Plan, Query Tuning, sp_executesql, SQL, sql indexes, SQL Server, SQL Server Consultant, SQL Server Index, SQL Server Training, sql temp table, temporary table sql4 Comments on A Resource Governor Request: Cap Total Query Memory Grants (Without Capping Other Stuff)

Signs You Need More Memory In Your SQL Server

Posted on November 18, 2021May 16, 2022 by Erik Darling

Guessotron


Most of these signs have to do with wait stats. One could venture out and say that if you have way less memory than data, you need more memory, but… If the server is sitting around bored, who cares?…

Posted in Hardware, Memory Grants, Query Tuning, Server Tuning, SQL Server, Wait StatsTagged create index in sql server, create temp table sql, DBA, Deadlock, Erik Darling, Erik Darling Data, how to create temp table in sql, Index Tuning, Indexing, Parallelism, Performance, performance tuning, Query, Query Plan, Query Tuning, sp_executesql, SQL, sql indexes, SQL Server, SQL Server Consultant, SQL Server Index, SQL Server Training, sql temp table, temporary table sql5 Comments on Signs You Need More Memory In Your SQL Server

When Query Spills Cause tempdb Contention And Performance Issues In SQL Server

Posted on October 18, 2021May 16, 2022 by Erik Darling

Temptables


I was helping a client troubleshoot a scalability issue recently, where during one point in their workload tempdb went out of control.

What was puzzling them was that nothing in the stored procedure did anything with temporary objects, cursors, …

Posted in Extended Events, Memory Grants, Spills, SQL Server, Table Variables, tempdb, Temporary TablesTagged create index in sql server, create temp table sql, DBA, Deadlock, Erik Darling, Erik Darling Data, how to create temp table in sql, Index Tuning, Indexing, Parallelism, Performance, performance tuning, Query, Query Plan, Query Tuning, sp_executesql, SQL, sql indexes, SQL Server, SQL Server Consultant, SQL Server Index, SQL Server Training, sql temp table, temporary table sql1 Comment on When Query Spills Cause tempdb Contention And Performance Issues In SQL Server

How Do I Estimate How Much Memory My SQL Server Needs?

Posted on September 28, 2021May 16, 2022 by Erik Darling

It’s Brand New


If you’re looking to scope out hardware for SQL Server, whether it’s in the cloud, or… not in the cloud, you’re gonna wanna get memory right.

Lots of people complain about SQL Server being a memory hog, …

Posted in Hardware, Memory Grants, SQL ServerTagged create index in sql server, create temp table sql, DBA, Deadlock, Erik Darling, Erik Darling Data, how to create temp table in sql, Index Tuning, Indexing, Parallelism, Performance, performance tuning, Query, Query Plan, Query Tuning, sp_executesql, SQL, sql indexes, SQL Server, SQL Server Consultant, SQL Server Index, SQL Server Training, sql temp table, temporary table sql2 Comments on How Do I Estimate How Much Memory My SQL Server Needs?

CHAR vs VARCHAR Memory Grants In SQL Server Queries

Posted on June 3, 2021May 16, 2022 by Erik Darling

Strings Were A Mistake


While working with a client recently, we found that someone, at some point in time, probably during the original migration from Access, had chosen CHAR columns rather than VARCHAR columns.

Okay, fine. How bad could it …

Posted in Memory Grants, SQL ServerTagged create index in sql server, create temp table sql, DBA, Deadlock, Erik Darling, Erik Darling Data, how to create temp table in sql, Index Tuning, Indexing, Parallelism, Performance, performance tuning, Query, Query Plan, Query Tuning, sp_executesql, SQL, sql indexes, SQL Server, SQL Server Consultant, SQL Server Index, SQL Server Training, sql temp table, temporary table sql8 Comments on CHAR vs VARCHAR Memory Grants In SQL Server Queries

Posts navigation

Older posts

Recent Posts

  • SQL Server IF Branches And Query Performance Part 5: Does Dynamic SQL Work?
  • SQL Server IF Branches And Query Performance Part 4: Do Stored Procedures Work?
  • SQL Server IF Branches And Query Performance Part 3: Local Variables Still Suck

Subscribe

Categories

  • Accelerated Database Recovery
  • Angry Aggregates
  • Batch Mode
  • Bugs
  • Cardinality
  • Change Data Capture
  • Change Tracking
  • CHECKDB
  • Cider
  • columnstore
  • Common Query Plan Patterns
  • Common Table Expression
  • Computed Columns
  • Consulting
  • Cost Threshold For Parallelism
  • Cursors
  • Deadlock
  • Dynamic SQL
  • Error Handling
  • Execution Plans
  • Extended Events
  • Filtered Indexes
  • Filtered Statistics
  • Forced Parameterization
  • FROID
  • Functions
  • Hardware
  • IF Branching
  • Implicit Transactions
  • Indexed Views
  • Indexing
  • Interviewing
  • Isolation Levels
  • Joe Obbish
  • Local Variables
  • Locking
  • Managed Instance
  • MAXDOP
  • Memory Grants
  • Office Hours
  • OPTIMIZE_FOR_SEQUENTIAL_KEY
  • OtherDB
  • Parallelism
  • Parameter Sniffing
  • Parameterization
  • Partitioning
  • perfview
  • Plan Cache
  • Plan Cache Liars
  • Plan Guides
  • Postgres
  • Query Store
  • Query Tuning
  • Row Goal
  • SARGability
  • Server Tuning
  • Software Vendor Mistakes
  • sp_HumanEvents
  • sp_PressureDetector
  • sp_QuickieStore
  • sp_WhoIsActive
  • Spills
  • Spools
  • SQL Saturday
  • SQL Server
  • SQL Server 2019
  • SQL Server 2022
  • SQLBits
  • SSMS
  • Stack Column Store
  • Starting SQL
  • Statistics
  • Streaming
  • T-SQL
  • Table Variables
  • Tasting Menu
  • tempdb
  • Temporary Tables
  • TOP
  • Trace Flag
  • Training
  • Triggers
  • Variables
  • vNext
  • Wait Stats
Copyright © 2019-2023 Erik Darling Data