Início > Não categorizado > Memory Grant, Sort Warnings

Memory Grant, Sort Warnings

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

clip_image002

clip_image004

clip_image006

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

clip_image008

clip_image010

clip_image012

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

clip_image014

clip_image016

clip_image018

64 MB of Memory

clip_image020

clip_image022

clip_image024

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

Anúncios
Categorias:Não categorizado
  1. Nenhum comentário ainda.
  1. No trackbacks yet.

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

%d blogueiros gostam disto: