Blog – Fabiano Neves Amorim

I was doing some tests with memory grant,sort warnings and amount of avaliable memory, so here are some pictures of my tests…

When I wrote this is an indication, I don´t mean 100% of certain that if you add more memory you will have a performance gain. But sort warnings can indicate memory pressure.

For instance, suppose a very concurrently scenario, with many queries requiring memory grant. When I run a query the server calculates the memory limit for that query. By default, this is 25% (20% on 32bit SQL 2005) of total query memory (which is set by memory broker as about 90% of server memory). This per-query limit helps to prevent one query from dominating the whole server.

Following is some picture of a simulation of this scenario.

1024 MB of Memory

EXEC sys.sp_configure N’max server memory (MB)’, N’1024′

GO

RECONFIGURE WITH OVERRIDE

GO

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

GO

512 MB of Memory

EXEC sys.sp_configure N’max server memory (MB)’, N’512′

GO

RECONFIGURE WITH OVERRIDE

GO

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

GO

256 MB of Memory

EXEC sys.sp_configure N’max server memory (MB)’, N’256′

GO

RECONFIGURE WITH OVERRIDE

GO

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

GO

64 MB of Memory

As you can see according to the memory change, the SQL can´t grant memory to some queries. And with just a few memory available the sort is exhibited.

Instead of change the available memory I could add more threads into SQLQueryStress to simulate the concurrency into the server, if you do this, may you see

some Sort Warnings into Profiler.

I’m not saying that you are wrong, I totally agree with you that SQL uses cardinality to estimate the amount of memory to the query, and the main problem is, a wrong estimation can lead to a bad memory grant. The point here is, yes, the sort warning can indicate a memory pressure.

SQLQueryStrees by Adam Machanic http://www.datamanipulation.net