Charge CPU

**slefevre01** · 12/12/2013, 11h46

Bonjour,
J'ai un serveur SQL2005 32 bits qui consomme beaucoup de cpu depuis qq semaines. 80% en journée.

Les deux principales attentes sont dans l'ordre:
LCK_M_SCH_M: 50% attente pour poser un verrou de modification de schema
SOS_SCHEDULER_YIELD: 25% du à la pression CPU

J'ai mis une trace qq minutes: les requêtes sont très courtes (<30ms) mais très nombreuses. difficile d'isoler une requête fautive.

J'ai remarqué qu'il y avait beaucoup de compilations/sec (par des requêtes adhoc car pas de proc stock dans la base)
Ratio requêtes exécutées/sec (moyenne : 800), requêtes compilées/sec (moyenne :300) est trop élevé.

Ces deux problèmes peuvent être liés, une table qui est modifiée (alter, reindex…) provoque une compilation des requêtes utilisées par cette table.

J'essaie d'isoler les requêtes responsables des LCK_M_SCH_M
avec la requete:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT resource_type, resource_associated_entity_id,
    request_status, request_mode,request_session_id,
    resource_description 
    FROM sys.dm_tran_locks
    WHERE resource_database_id = 9

voici les wait de type Sch-M que je retrouve:
A priori, c'est sur les metadata. Par contre, je ne comprends pas la ressource description ($hash =0x9:0x0 ). Pouvez-vous m'aider à ce sujet ?

METADATA 0 GRANT Sch-M 70 $hash = 0x9:0x0
METADATA 0 WAIT Sch-M 91 $hash = 0x9:0x0
METADATA 0 WAIT Sch-M 67 $hash = 0x9:0x0
METADATA 0 WAIT Sch-M 107 $hash = 0x9:0x0
METADATA 0 WAIT Sch-M 61 $hash = 0x9:0x0
METADATA 0 WAIT Sch-M 54 $hash = 0x9:0x0

Merci

Sebastien

**mikedavem** · 12/12/2013, 12h51

Hello,

Tu dis avoir beaucoup de requêtes adhoc qui tournent sur ton serveur SQL. Effectivement cela peut être une source d'utilisation excessive de la CPU dû à une compilation excessive.

Tu as essayé de corréler ces compteurs perfmon pour valider ce fait :

SQLServer: SQL Statistics: SQL Compilations/Sec
SQLServer: SQL Statistics: Auto-Param Attempts/Sec
SQLServer: SQL Statistics: Failed Auto-Param/Sec

++

**slefevre01** · 12/12/2013, 14h21

Bonjour,

voici les informations:
batchs request/sec: 800
compilations/sec: 300
Failed Auto-Param/Sec: 70
Auto-Param Attempts/Sec: 70

Je précise qu'il y a très peu d'écritures sur la base. La plupart des requêtes sont des select (avec beaucoup d'union)

Merci

**slefevre01** · 12/12/2013, 14h55

cette requête me remonte un peu plus d'informations:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
select
TL1.resource_type
,DB_NAME(TL1.resource_database_id) as [DB Name]
,CASE TL1.resource_type
WHEN 'OBJECT' THEN OBJECT_NAME(TL1.resource_associated_entity_id, TL1.resource_database_id)
WHEN 'DATABASE' THEN 'DB'
ELSE
CASE
WHEN TL1.resource_database_id = DB_ID()
THEN
(
select OBJECT_NAME(object_id, TL1.resource_database_id)
from sys.partitions
where hobt_id = TL1.resource_associated_entity_id
)
ELSE
'(Run under DB context)'
END
END as ObjectName
,TL1.resource_description
,TL1.request_session_id
,TL1.request_mode
,TL1.request_status
,WT.wait_duration_ms as [Wait Duration (ms)]
,(
select
SUBSTRING(
S.Text,
(ER.statement_start_offset / 2) + 1,
((
CASE
ER.statement_end_offset
WHEN -1
THEN DATALENGTH(S.text)
ELSE ER.statement_end_offset
END - ER.statement_start_offset) / 2) + 1)
from
sys.dm_exec_requests ER
cross apply sys.dm_exec_sql_text(ER.sql_handle) S
where
TL1.request_session_id = ER.session_id
) as [Query]
from
sys.dm_tran_locks as TL1 join sys.dm_tran_locks TL2 on
TL1.resource_associated_entity_id = TL2.resource_associated_entity_id
left outer join sys.dm_os_waiting_tasks WT on
TL1.lock_owner_address = WT.resource_address and TL1.request_status = 'WAIT'
 
where
TL1.request_status <> TL2.request_status and
(
TL1.resource_description = TL2.resource_description OR
(TL1.resource_description is null and TL2.resource_description is null)
)

---
a priori, ce select fait une demande de Sch-M:

METADATA	master	(Run under DB context)	$hash = 0x0:0x13
106	Sch-M	WAIT	NULL

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
  SELECT 'Imagence:Igws:Domain:IgwsElements:VersionElements:VersionElement' AS [EntityType]
       , [e0].[IgwsElementId]        AS [ParentId]
       , [e1].[VersionElementId]     AS [pkVersionElementId]
       , [e1].[IgwsId]               AS [IgwsId]
       , [e1].[VersionType]          AS [VersionType]
       , [e1].[VersionNumber]        AS [VersionNumber]
       , [e1].[Title]                AS [Title]
       , [e1].[Abstract]             AS [Abstract]
       , [e1].[Content]              AS [Content]
       , [e1].[Link]                 AS [Link]
       , [e1].[CreationDate]         AS [CreationDate]
       , [e1].[LastModificationDate] AS [LastModificationDate]
       , [e1].[VisibleDate]          AS [VisibleDate]
       , NULL                        AS [EventLocation]
       , NULL                        AS [EventSponsor]
       , NULL                        AS [EventMinutes]
       , NULL                        AS [EventStartDate]
       , NULL                        AS [EventEndDate]
       , NULL                        AS [EventSpeakers]
       , NULL                        AS [EventProgram]
    FROM [VersionElement]            AS [e1]
    JOIN [IgwsElement]               AS [e0]
      ON ([e0].[IgwsElementId]        = [e1].[FK_VersionableElementId]  )
   WHERE ([e0].[IgwsElementType]     IN ('Article' ) )
     And ([e0].[IgwsElementId]       IN ('a57065c4-c384-47dd-8769-86084da91278' ) )
     And ([e1].[VersionElementType]   = 'VersionElement' )
   UNION ALL
  SELECT 'Imagence:Igws:Domain:IgwsElements:VersionElements:IgwsEventVersion' AS [EntityType]
       , [e0].[IgwsElementId]        AS [ParentId]
       , [e4].[VersionElementId]     AS [pkVersionElementId]
       , [e4].[IgwsId]               AS [IgwsId]
       , [e4].[VersionType]          AS [VersionType]
       , [e4].[VersionNumber]        AS [VersionNumber]
       , [e4].[Title]                AS [Title]
       , [e4].[Abstract]             AS [Abstract]
       , [e4].[Content]              AS [Content]
       , [e4].[Link]                 AS [Link]
       , [e4].[CreationDate]         AS [CreationDate]
       , [e4].[LastModificationDate] AS [LastModificationDate]
       , [e4].[VisibleDate]          AS [VisibleDate]
       , [e4].[EventLocation]        AS [EventLocation]
       , [e4].[EventSponsor]         AS [EventSponsor]
       , [e4].[EventMinutes]         AS [EventMinutes]
       , [e4].[EventStartDate]       AS [EventStartDate]
       , [e4].[EventEndDate]         AS [EventEndDate]
       , [e4].[EventSpeakers]        AS [EventSpeakers]
       , [e4].[EventProgram]         AS [EventProgram]
    FROM [VersionElement]            AS [e4]
    JOIN [IgwsElement]               AS [e0]
      ON ([e0].[IgwsElementId]        = [e4].[FK_VersionableElementId]  )
   WHERE ([e0].[IgwsElementType]     IN ('Article' ) )
     And ([e0].[IgwsElementId]       IN ('a57065c4-c384-47dd-8769-86084da91278' ) )
     And ([e4].[VersionElementType]   = 'IgwsEventVersion' )
   UNION ALL
  SELECT 'Imagence:Igws:Domain:IgwsElements:VersionElements:HtmlTextVersion' AS [EntityType]
       , [e0].[IgwsElementId]        AS [ParentId]
       , [e3].[VersionElementId]     AS [pkVersionElementId]
       , [e3].[IgwsId]               AS [IgwsId]
       , [e3].[VersionType]          AS [VersionType]
       , [e3].[VersionNumber]        AS [VersionNumber]
       , [e3].[Title]                AS [Title]
       , [e3].[Abstract]             AS [Abstract]
       , [e3].[Content]              AS [Content]
       , [e3].[Link]                 AS [Link]
       , [e3].[CreationDate]         AS [CreationDate]
       , [e3].[LastModificationDate] AS [LastModificationDate]
       , [e3].[VisibleDate]          AS [VisibleDate]
       , NULL                        AS [EventLocation]
       , NULL                        AS [EventSponsor]
       , NULL                        AS [EventMinutes]
       , NULL                        AS [EventStartDate]
       , NULL                        AS [EventEndDate]
       , NULL                        AS [EventSpeakers]
       , NULL                        AS [EventProgram]
    FROM [VersionElement]            AS [e3]
    JOIN [IgwsElement]               AS [e0]
      ON ([e0].[IgwsElementId]        = [e3].[FK_VersionableElementId] )
   WHERE ([e0].[IgwsElementType]     IN ('Article' ) )
     And ([e0].[IgwsElementId]       IN ('a57065c4-c384-47dd-8769-86084da91278' ) )
     And ([e3].[VersionElementType]   = 'HtmlTextVersion' )
   UNION ALL
  SELECT 'Imagence:Igws:Domain:IgwsElements:VersionElements:ArticleVersion' AS [EntityType]
       , [e0].[IgwsElementId]        AS [ParentId]
       , [e2].[VersionElementId]     AS [pkVersionElementId]
       , [e2].[IgwsId]               AS [IgwsId]
       , [e2].[VersionType]          AS [VersionType]
       , [e2].[VersionNumber]        AS [VersionNumber]
       , [e2].[Title]                AS [Title]
       , [e2].[Abstract]             AS [Abstract]
       , [e2].[Content]              AS [Content]
       , [e2].[Link]                 AS [Link]
       , [e2].[CreationDate]         AS [CreationDate]
       , [e2].[LastModificationDate] AS [LastModificationDate]
       , [e2].[VisibleDate]          AS [VisibleDate]
       , NULL                        AS [EventLocation]
       , NULL                        AS [EventSponsor]
       , NULL                        AS [EventMinutes]
       , NULL                        AS [EventStartDate]
       , NULL                        AS [EventEndDate]
       , NULL                        AS [EventSpeakers]
       , NULL                        AS [EventProgram]
    FROM [VersionElement]            AS [e2]
    JOIN [IgwsElement]               AS [e0]
      ON ([e0].[IgwsElementId]        = [e2].[FK_VersionableElementId]  )
   WHERE ([e0].[IgwsElementType]     IN ('Article' ) )
     And ([e0].[IgwsElementId]       IN ('a57065c4-c384-47dd-8769-86084da91278' ) )
     And ([e2].[VersionElementType]   = 'ArticleVersion' )
ORDER BY [pkVersionElementId];

Comme se fait-il qu'un select (complexe tout même) demande un verrou de modification de schéma ?

**mikedavem** · 12/12/2013, 15h05

et que te donne cette requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
select 
	objtype, 
	cacheobjtype, 
	count(*) as nb_entries,
	sum(size_in_bytes) / 1024 as total_size_in_kb
from sys.dm_exec_cached_plans
group by objtype, cacheobjtype
order by count(*) desc

++

**slefevre01** · 12/12/2013, 15h11

ca fluctue à chaque exécution
1ere fois:
Adhoc Compiled Plan 16 3912
Prepared Compiled Plan 2 88
View Parse Tree 2 32

autre fois::
Adhoc Compiled Plan 2 256

Charge CPU

Administration SQL Server

Vue hybride

Discussions similaires

Partager

Partager