Charge CPU

Version imprimable

12/12/2013, 11h46
slefevre01

Charge CPU
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:

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 ?

Citation:

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
12/12/2013, 12h51
mikedavem

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

++
12/12/2013, 14h21
slefevre01

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

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

Code:

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:

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 ?

et que te donne cette requête :

Code:

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

12/12/2013, 15h11
slefevre01

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

autre info:
L'exemple suivant retourne le texte SQL de toutes les entrées en cache ayant été utilisées plusieurs fois.
Code:

1 2 3 4 5 6 7 8 SELECT usecounts, cacheobjtype, objtype, text FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) WHERE usecounts > 1 ORDER BY usecounts DESC; GO 8 Compiled Plan Prepared (@Name nvarchar(13)) select Value from [dbo].[ProcessSynchronizer] WITH (UPDLOCK) where Name = @Name
Je retrouve toujours sur ce select ci-dessus. Rarement les select complexes (avec les unions)

12/12/2013, 15h51
mikedavem

Cela veut peut vouloir dire (de loin) que le plan d'exécution de tes requêtes adhoc ne sont pas réutilisés et qu'il faut à chaque fois compiler un nouveau plan à chaque exécution ce qui nécessite à chaque fois de la ressource CPU.

++
12/12/2013, 16h07
SQLpro

regarde dans le sp_configure si tu as "optimize for ad hoc workloads"....

A +
12/12/2013, 16h09
slefevre01

oui, ca compile 1 fois sur 3. donc cpu pour rien.

Ce qui me surprend le plus c'est cette attente de lock de verrou de schema, alors qu'il n'y a pas d'alter/reindex...

du coup, vu que le schéma change, ca recompile derrière.

bonjour Frédéric
le serveur est en sql2005 sp3. Je ne trouve pas cette option avancée. ou alors je suis très fatigué :-)

Ad Hoc Distributed Queries	0	1	0	0
affinity I/O mask	-2147483648	2147483647	0	0
affinity mask	-2147483648	2147483647	0	0
Agent XPs	0	1	1	1
allow updates	0	1	0	0
awe enabled	0	1	0	0
blocked process threshold	0	86400	0	0
c2 audit mode	0	1	0	0
clr enabled	0	1	0	0
cost threshold for parallelism	0	32767	5	5
cross db ownership chaining	0	1	0	0
cursor threshold	-1	2147483647	-1	-1
Database Mail XPs	0	1	1	1
default full-text language	0	2147483647	1036	1036
default language	0	9999	2	2
default trace enabled	0	1	1	1
disallow results from triggers	0	1	0	0
fill factor (%)	0	100	0	0
ft crawl bandwidth (max)	0	32767	100	100
ft crawl bandwidth (min)	0	32767	0	0
ft notify bandwidth (max)	0	32767	100	100
ft notify bandwidth (min)	0	32767	0	0
index create memory (KB)	704	2147483647	0	0
in-doubt xact resolution	0	2	0	0
lightweight pooling	0	1	0	0
locks	5000	2147483647	0	0
max degree of parallelism	0	64	0	0
max full-text crawl range	0	256	4	4
max server memory (MB)	16	2147483647	3072	3072
max text repl size (B)	0	2147483647	65536	65536
max worker threads	128	32767	0	0
media retention	0	365	0	0
min memory per query (KB)	512	2147483647	1024	1024
min server memory (MB)	0	2147483647	0	0
nested triggers	0	1	1	1
network packet size (B)	512	32767	4096	4096
Ole Automation Procedures	0	1	0	0
open objects	0	2147483647	0	0
PH timeout (s)	1	3600	60	60
precompute rank	0	1	0	0
priority boost	0	1	0	0
query governor cost limit	0	2147483647	0	0
query wait (s)	-1	2147483647	-1	-1
recovery interval (min)	0	32767	0	0
remote access	0	1	1	1
remote admin connections	0	1	0	0
remote login timeout (s)	0	2147483647	20	20
remote proc trans	0	1	0	0
remote query timeout (s)	0	2147483647	600	600
Replication XPs	0	1	0	0
scan for startup procs	0	1	0	0
server trigger recursion	0	1	1	1
set working set size	0	1	0	0
show advanced options	0	1	1	1
SMO and DMO XPs	0	1	1	1
SQL Mail XPs	0	1	0	0
transform noise words	0	1	0	0
two digit year cutoff	1753	9999	2049	2049
user connections	0	32767	0	0
user options	0	32767	0	0
Web Assistant Procedures	0	1	0	0
xp_cmdshell	0	1	1	1

A regarder la requête cela m'étonne qu'elle génère ce genre de lock.

C'est une requête banale à base de SELECT et de UNION ALL donc cela devrait générer des locks de type S et Sch-S mais pas Sch-M. A moins que l'on a pas tout le détail de la requête ou de ce qui la lance ...

Code:

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
109
110
111
112
SELECT 
	'Imagence:Igwsomain: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]	
	INNER 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:Igwsomain: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] 
	INNER 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:Igwsomain: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] 
	INNER 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:Igwsomain: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] 
	INNER 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]

oui, c'est étonnant. mais c'est confirmé par la requête :
Code:

1 2 3 4 5 SELECT resource_database_id, resource_type, resource_associated_entity_id, request_status, request_mode,request_session_id, resource_description FROM sys.dm_tran_locks WHERE request_mode = 'Sch-M'
La database id 9 est la base du client.

9 METADATA 0 GRANT Sch-M 97 $hash = 0x1:0x4
9 METADATA 0 WAIT Sch-M 93 $hash = 0x1:0x4

12/12/2013, 16h55
mikedavem

Pour ton option elle n'existe pas en SQL Server 2005

Par contre tu peux tenter d'utiliser l'option de bases de données FORCED PARAMETERIZATION mais cela reste à valider si cela règle ton problème

++
12/12/2013, 17h45
SQLpro

Citation:

Envoyé par mikedavem

A regarder la requête cela m'étonne qu'elle génère ce genre de lock.

C'est une requête banale à base de SELECT et de UNION ALL donc cela devrait générer des locks de type S et Sch-S mais pas Sch-M. A moins que l'on a pas tout le détail de la requête ou de ce qui la lance ...

++

La mise à jour des stats fait-elle un schéma M ? Je sais pas mais faudrait voir....

A +
12/12/2013, 18h37
mikedavem

Aussi loin que je me souvienne la mise à jour des statistiques pose un verrou de type X sur une ressource interne de type statistique et non un verrou de type Sch-M mais peut être que d'autres infirmeront ce propos ...

Du coup je suis vraiment surpris par le type de verrou posé par la requête mais bon je dois sûrement oublier quelque chose ... si j'ai une idée je reviendrais sur le sujet :D

++
16/12/2013, 15h00
slefevre01

un peu de news. En activant la trace 'object alter' dans le profiler, j'ai trouvé la requête qui provoque les verrous de modification de schémas:

Code:

ALTER TABLE [EmailMessage] NOCHECK CONSTRAINT ALL

Cela se produit 6 à 7 fois par minutes.

L'info est remontée au développeur pour avoir une explication sur ces désactivation de contraintes.

Sebastien
16/12/2013, 16h37
mikedavem

Citation:

ALTER TABLE [EmailMessage] NOCHECK CONSTRAINT ALL
Cela se produit 6 à 7 fois par minutes.

Ok donc déjà cela me rassure ... ;)

++

Bonjour,

Voyez ce que vous retourne la requête suivante, qui montre la liste des plans qui n'ont été utilisés qu'une seule fois :

Code:

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
;WITH
	CTE AS
	(
		SELECT		QS.query_plan_hash
				, QS.query_hash
				, COUNT(*) AS entries_count
				, SUM(QS.execution_count) AS execution_count
				, MIN(QS.sql_handle) AS sample_sql_handle
				, MIN(QS.plan_handle) AS sample_plan_handle
				, SUM(QS.total_worker_time) AS total_CPU_time
				, SUM(QS.total_logical_reads) AS total_logical_reads
				, SUM(CAST(P.size_in_bytes AS bigint)) / 1024 / 1024 AS total_size_MB
		FROM		sys.dm_exec_query_stats AS QS
		INNER JOIN	sys.dm_exec_cached_plans AS P
					ON QS.plan_handle = P.plan_handle
		GROUP BY	query_plan_hash, query_hash HAVING COUNT(*) > 50
	)
SELECT		Q.entries_count
		, Q.execution_count
		, Q.total_logical_reads
		, Q.total_CPU_time
		, Q.sample_plan_handle
		, ROW_NUMBER() OVER(ORDER BY Q.entries_count DESC) AS n
		, Q.query_plan_hash
		, Q.query_hash
		, QP.objectid
		, DB_NAME(QT.dbid) AS database_name
		, Q.total_size_MB
FROM		CTE AS Q
CROSS APPLY	sys.dm_exec_sql_text(Q.sample_sql_handle) AS QT
CROSS APPLY	sys.dm_exec_query_plan(Q.sample_plan_handle) AS QP
WHERE		QT.text NOT LIKE '%sp?_replmonitorrefreshagentdata%' ESCAPE '?'
AND		QT.text NOT LIKE '%sp?_replmonitorhelppublisherhelper%' ESCAPE '?'
AND		(
			(
				QP.dbid > 4
				AND QP.dbid <> 32767
			)
			OR QP.dbid IS NULL
		)

Vous pouvez retrouver le plan et sa requête avec la requête suivante :

Code:

1
2
SELECT	*
FROM	sys.dm_exec_query_plan(valeurDansLaColonne__sample_plan_handle)

Avec l'aide de SQL Sentry Plan Explorer, c'est très facile ;)

@++ ;)

17/12/2013, 16h58
slefevre01

Bonjour et Merci Nicolas.
Je testerais cela demain.

Cdlt