slide1 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Les Webcasts PowerPoint Presentation
Download Presentation
Les Webcasts

Loading in 2 Seconds...

play fullscreen
1 / 21

Les Webcasts - PowerPoint PPT Presentation


  • 66 Views
  • Uploaded on

Les Webcasts. Groupe des Utilisateurs SQL Server. Juin 2013 – Query memory grants David Baffaleuf– CAPDATA MVP SQL Server. David Baffaleuf http://blog.capdata.fr @dbaffaleuf. Management d’infrastructures IT hétérogènes www.osmozium.com Support Management Technical Management

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Les Webcasts' - pello


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide1

Les Webcasts

Groupe des Utilisateurs SQL Server

Juin 2013 – Query memory grants

David Baffaleuf– CAPDATA

MVP SQL Server

slide2

David Baffaleuf

http://blog.capdata.fr

@dbaffaleuf

  • Management d’infrastructures IT hétérogènes
  • www.osmozium.com
  • Support Management
  • Technical Management
  • Data Management
  • Production Management
  • Leader SGBD reconnu en France
  • www.capdata.fr
  • Conseil
  • Service
  • Formation
  • DBA à distance

http://www.youtube.com/user/CapdataTV/

30 chrono
30 ‘ chrono

Comprendre qu’est-ce qu’un query memory grant

Pourquoi ça peut être un problème

Comment identifier

Comment résoudre

Démos !

Questions / réponses

Query Memory Grants en 30’

de la m moire pour une requ te
De la mémoire pour une requête

Max 75% Buffer Pool

Plan

Query

Memory

Grant

Compilation

Query Memory Grants en 30’

les op rateurs concern s
Les opérateurs concernés

SORT

EXCHANGE

HASH MATCH

Query Memory Grants en 30’

evaluation des besoins
Evaluation des besoins

2 besoins évalués à la compilation :

  • Mémoire requise (minimum grant): minimum syndical pour supporter les opérateurs concernés. La requête ne peut pas commencer son exécution sans cette valeur (par défaut, min memory per query = 1Mb)
  • Mémoire additionnelle (idealgrant): nécessaire pour faire toute la passe (tri, hash) en mémoire. Pas obligatoire, pas garantie.

Note: Pour optimiser les besoins, certains opérateurs peuvent partager des fractionsde QMG. (F4 sur opérateur)

Query Memory Grants en 30’

arbitrage des qmg
Arbitrage des QMG

Demandes mémoire contrôlées par des sémaphores.

2 sémaphores par pool RG:

  • Un pour les requêtes à faible coût ( cost < 3 && idealgrant < 5Mb)
  • Un pour les requêtes à coût plus élevé (tout le reste)

3 sets par sémaphore (paramètre RG), une ou plusieurs queues par set

  • LOW SET: pool RG de faible importance
  • MEDIUM SET: pool RG de moyenne importance
  • HIGH SET: pool RG de haute importance

Les requêtes à plus faible coût sont prioritaires sur les requêtes au coût plus élevé.

Query Memory Grants en 30’

file d attente et s maphores 1 2
File d’attente et sémaphores 1/2

SELECT TOP(1000)

* FROM Production.TransactionHistory

ORDER BY ActualCost DESC

OPTION (MAXDOP 1)

GO

RG POOL DEFAULT

Query Memory Grants en 30’

file d attente et s maphores 2 2
File d’attente et sémaphores 2/2

RG POOL (DEFAULT)

SMALL SEMAPHORE

(cost < 3 && idealgrant < 5Mb)

  • LARGE SEMAPHORE
  • (tout le reste)
  • 5 queues par SET en Large RS:
  • qid=0, cost < 10
  • qid=1, 10<=cost < 99
  • qid=2, 100<=cost < 999
  • qid=3, 1000<=cost < 9999
  • qid=4, cost > 10000

LOW

MED

  • qid=5, cost < 10
  • qid=6, 10<=cost < 99
  • qid=7, 100<=cost < 999
  • qid=8, 1000<=cost < 9999
  • qid=9, cost > 10000 …

MEDIUM SET

HIGH SET

LOW SET

  • Large RS, MEDIUM SET, qid=0

MEDIUM SET

HIGH SET

LOW SET

Query Memory Grants en 30’

priorit s et attentes 1 2
Priorités et Attentes 1/2

1 requête MEDIUM SET

QID = 2

100 < Cost < 999

Attente sur

RESOURCE_SEMAPHORE …

100+ requêtes

MEDIUM SET

QID = 0

Cost < 10

=> Prioritaires

Query Memory Grants en 30’

Une fois dans une des queues, la requête va devoir attendre que 150% de la mémoire demandée soir disponible, …

… et qu’il n’y ait plus d’autres requêtes prioritaires.

Les requêtes favorisées sont celles dont le coût est le plus faible et l’importance la plus élevée.

L’attente est comptabilisée sur RESOURCE_SEMAPHORE.

priorit s et attentes 2 2
Priorités et Attentes 2/2

Par défaut, la requête va attendre jusqu’à atteindre un timeout, qui est égal à 25 fois le coût de la requête en secondes avec une limite de 24 heures (!!).

Sinon paramètre instance querywait (s).

Sinon request_memory_grant_timeout dans le pool RG.

Lorsque le timeout est atteint:

  • Soit l’idealgrant peut être réduit à la valeur de minimum grant, et le reste sera stocké sur disque (tempdb).
  • S’il n’y a plus assez de mémoire au runtime pour honorer le minimum, Erreur 8645: "A timeout occurred while waiting for memory resources to execute the query in resource pool '%ls' (%ld). Rerun the query."

Query Memory Grants en 30’

pourquoi a peut tre un probl me
Pourquoi ça peut être un problème

Ideal Grant = pas de garantie.

Besoin évalué à la compilation et basé sur l’estimation des cardinalités (nombre de lignes produites x taille de la ligne) en entrée de l’opérateur.

Au runtime, SQL Server peut n’accorder qu’une partie de ce qui a été demandé en fonction de l’état des ressources, le reste se fera sur disque en 1 ou plusieurs passes.

Utilisation d’entrées / sorties synchrones (IO_COMPLETION).

Mélange requêtes à fort coût et faible coût (DSS vs OLTP)

Query Memory Grants en 30’

probl me avec hash match 1 2
Problème avec Hash match 1/2

Hashtable

PROPALOUER

DEPARTEMENT

BUILD (1)

PROBE (2)

hash(ID_DEPT)

Query Memory Grants en 30’

probl me avec hash match 2 2
Problème avec Hash match 2/2

La phase de Build nécessite de réserver de la mémoire pour les N buckets créés (estimation des cardinalités).

Les buckets qui ne tiennent pas en mémoire vont sur disque (tempdb).

Les lignes de probe qui joignent des buckets sur disque vont sur disque (tempdb).

Une fois que toutes les jointures sur les buckets en mémoire sont terminées, on va relire les buckets + lignes de probe sur disque.

Si la seconde passe ne tient pas davantage en mémoire, certains couples buckets + probes sont réécrites sur disque (recursion).

Trop de recursion => Hash bailout. On laisse tomber la table de hachage et la jointure est faite en utilisant un NLJ non optimisé.

Visible avec SQL Trace : Hash Warning ou Xevent : hash_warning (map = bailout), et depuis SQL Server 2012 un avertissement dans l’opérateur.

Compteur perfmon: Workfilescreated /sec

Query Memory Grants en 30’

probl me avec sort
Problème avec Sort

AlgoritmeQuicksort trie en mémoire.

Si le memory grant est dépassé, tout le tri va sur disque (tempdb) et utilise un algoritmemerge sort moins efficace.

Visible grâce à SQL Trace: Sort Warning ou Xevent : sort_warning, et depuis SQL Server 2012 un avertissement dans l’opérateur.

Techniquement ni worktable ni workfile.

Query Memory Grants en 30’

probl me avec exchange 1 2
Problème avec Exchange 1/2

DOP*2

Buffers

DOP = 8

DOP*2

Buffers

DOP*2

Buffers

DOP*2

Buffers

Query Memory Grants en 30’

Nécessite DOP*2 buffers par flux (producteur / consommateur).

  • Distribute: 1 flux en entrée + DOP flux en sortie.
  • Repartition: DOP flux en entrée + DOP flux en sortie.
  • Gather: DOP flux en entrée + 1 flux en sortie.

La taille du buffer est déterminée en fonction de l’estimation des cardinalités .

probl me avec exchange 2 2
Problème avec Exchange 2/2

1

3

5

7

8

1

4

6

3

5

6

7

8

4

1

3

9

2

9

2

2

10

10

x

Arrive rarement. Souvent visible sur un Merge Exchange (parallélisme + ORDER BY, MJ, Stream AGG) lorsque l’ordre doit être préservé.

Lorsqu’un worker récupère plus de lignes que les autres, et que l’opérateur Merge ne peut plus préserver l’ordre, l’ensemble des lignes en entrées vont sur disque (Intra-QueryParallelDeadlock)

Visible grâce à SQL Trace: Exchange Spill Event ou Xevent : exchange_spill .

Query Memory Grants en 30’

comment d tecter les probl mes de qmg
Comment détecter les problèmes de QMG

DBCC MEMORYSTATUS

  • Small Query Memory Objects (RG Pool, en pages)
  • Query Memory Objects (RG Pool, en pages)

DMO:

  • sys.dm_exec_query_resource_semaphores
  • sys.dm_exec_query_memory_grants
  • sys.dm_os_waiting_tasks(RESOURCE_SEMAPHORE)

Évènements SQL Trace:

  • Hash Warning
  • Sort Warning
  • Exchange Spill Event.
  • Trace par défaut (v >= 2012)

Évènements XEvents:

  • hash_warning
  • sort_warning
  • exchange_spill.

Query Memory Grants en 30’

comment r soudre les probl mes de qmg
Comment résoudre les problèmes de QMG

DEMOS

Query Memory Grants en 30’

Indexation

Réécriture des requêtes

Gérer les priorités en utilisant les pools de ressource RG.

Plus de mémoire…

des questions
Des questions ?

Query Memory Grants en 30’

slide21

Les Webcasts

Groupe des Utilisateurs SQL Server

GUSS.fr