Easy Rider
When you’re trying to figure out how to store string data, it often seems easiest to just choose an extra long — even MAX — data type to avoid future truncation errors.
Even if you’re storing strings with …
Category: Memory Grants
Using Views To Reduce Memory Grants In SQL Server
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 …
CPU & RAM Don’t Lie: Query Metrics I Care About For Tuning
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 …
Common SQL Server Consulting Advice: Enabling Resource Governor To Fix Memory Grants
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 …
Common SQL Server Consulting Advice: Setting Min Server Memory And Max Server Memory
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 …
Does My SQL Server Need More Memory?
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).…
A Resource Governor Request: Cap Total Query Memory Grants (Without Capping Other Stuff)
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 …
Signs You Need More Memory In Your SQL Server
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?…
When Query Spills Cause tempdb Contention And Performance Issues In SQL Server
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, …
How Do I Estimate How Much Memory My SQL Server Needs?
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, …