Discussion :

[olivtone]

Bonjour a Tous

J'ai un souci sur une PS qui dans le plan d'execution a ce warning

je n'ai pas de mon coté de manque de RAM, mon Page life expentency est de 3 jours donc je suis large

J'ai regarde aussi du coté de la ressource semaphore, je n'ai pas vu de souci de cote la

J'ai pas aussi des ressources en Attente du coté des DMV du cote de la DMV sys.dm_os_wait_stats



Quelqu'un aurait une idée?

merci a vous et bonne journée

[SQLpro]

texte de la procédure STP avec les DDL des tables.

A +

[elsuket]

Bonjour,

Et le plan de requête aussi svp, que vous pouvez anonymiser à l'aide de SQL Sentry Plan Explorer, comme décrit ici.

Par ailleurs dès SQL Server 2014 (SP2) et suivants, la DMV sys.dm_exec_query_statistics a été enrichie de colonnes donnant les octrois de RAM, mais aussi le degré de parallélisation, et le nombre de threads.

@++

[olivtone]

Deja merci a vous deux pour votre retour, c'est vraiment top

voici le plan d'execution :



l'execution de la PS met 1 seconde, alors qu'avant elle etait quasi immediate...(200ms)

les 66 % de l'index Scan sont dus a mon avis a une mauvaise estimation des stats



et quand je checke les stats de l'index, je suis victime je pense des stats ascendants



J'ai donc active les traceflags 2389 et 2390 pour faire des Tests mais rien n'y fait...

La table est en identity et les stats sont bien a jour

Le script SQL est celui ci :

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
 
 
CREATE procedure [dbo].[TOTO]
	@TOP_BEGIN				int,
	@TOP_END				int,
	@TEST_BOI_REF			varchar(10) = null,
	@TEST_BOI_REF_START		varchar(10) = null,
	@TEST_BOI_REF_END		varchar(10) = null,
	@SEV_ID					int = null,
	@SEV_ID_IN				varchar(255) = null,
	@TEST_DATE_CREATE_START	datetime = null,
	@TEST_DATE_CREATE_END	datetime = null,
	@TEST_DATE_MODIF_START	datetime = null,
	@TEST_DATE_MODIF_END		datetime = null,
	@CLI_SNA_IDENTITE		varchar(160) = null
as
 
create table #TOTO (TEST_BOI_REF varchar(10), TEST_TIMESTAMP datetime, RowNum int, primary key(TEST_BOI_REF,TEST_TIMESTAMP))
 
insert into	#TOTO(TEST_BOI_REF,TEST_TIMESTAMP, RowNum)
select		r.TEST_BOI_REF, r.TEST_TIMESTAMP, r.RowNum
from		(
			select		distinct f.TEST_BOI_REF, 
								 f.TEST_TIMESTAMP, 
								 ROW_NUMBER() over (order by f.TEST_BOI_REF, f.TEST_TIMESTAMP) as RowNum
			from		T_TEST f
			left join	(
							select distinct boi.CON_ID, boi.BOI_REF
							from S_T_BOITIER_BOI boi
						) b on b.BOI_REF = f.TEST_BOI_REF
			left join	(
							select		cli.*
							from		S_T_TEST cli
							inner join	(
											select		max(cl.CLI_ID) as CLI_ID
											from		S_T_CLIENTS_CLI cl
											group by	cl.CON_ID
										) cl on	cl.CLI_ID = cli.CLI_ID
						) cli on cli.CON_ID = b.CON_ID
				  where (@TEST_BOI_REF is null or f.TEST_BOI_REF = @TEST_BOI_REF)
					and (@TEST_BOI_REF_START is null or f.TEST_BOI_REF >= @TEST_BOI_REF_START)
					and (@TEST_BOI_REF_END is null or f.TEST_BOI_REF <= @TEST_BOI_REF_END)
					and (@SEV_ID is null or f.SEV_ID = @SEV_ID)
					and (@TEST_DATE_CREATE_START is null or f.TEST_DATE_CREA >= @TEST_DATE_CREATE_START)
					and (@TEST_DATE_MODIF_START is null or f.TEST_DATE_MODIF >= @TEST_DATE_MODIF_START)
					and (@TEST_DATE_CREATE_END is null or f.TEST_DATE_CREA < @TEST_DATE_CREATE_END)
					and (@TEST_DATE_MODIF_END is null or f.TEST_DATE_MODIF < @TEST_DATE_MODIF_END)
					and (@CLI_SNA_IDENTITE is null or cli.CLI_SNA_IDENTITE like @CLI_SNA_IDENTITE)
			) r
where		r.RowNum >= @TOP_BEGIN
			and r.RowNum <= @TOP_END

et la PS est appelée de cette facon :

Code :

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

1
2
 
exec PS_TOTO @TOP_BEGIN=0,@TOP_END=2147483647,@ECO_BOI_REF=NULL,@ECO_BOI_REF_START=NULL,@ECO_BOI_REF_END=NULL,@SEV_ID=4,@ECO_DATE_CREATE_START=NULL,@ECO_DATE_MODIF_START=NULL,@ECO_DATE_CREATE_END=NULL,@ECO_DATE_MODIF_END=NULL,@CLI_SNA_IDENTITE=NULL

[elsuket]

Bonjour,

A en juger par le nombre de prédicats de filtres que la requête comporte, de type and (@variable is null or maColonne >= @variable), c'est normal que vous ayez ce type d'avertissement.
D'ailleurs, il est même heureux que vous n'ayez qu'un Mo d'écart. En effet le plan de requête est optimisé suivant la valeurs des variables passées en entrée.

Donc si la requête est compilée avec un certain jeu de valeurs de variables dont certaines sont à NULL, puis re-exécutée ensuite avec un autre jeu de valeurs dont un sous-ensemble différent de celui avec laquelle elle a été compilée est à NULL, il est très probable que le plan soit sous-optimal.

Le moyen le plus simple est donc d'utiliser une requête en SQL Dynamique. Dans votre cas, cela donne :

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
CREATE PROCEDURE [dbo].[TOTO]
	@TOP_BEGIN int
	, @TOP_END int
	, @TEST_BOI_REF varchar(10) = NULL
	, @TEST_BOI_REF_START varchar(10) = NULL
	, @TEST_BOI_REF_END varchar(10) = NULL
	, @SEV_ID int = NULL
	, @SEV_ID_IN varchar(255) = NULL
	, @TEST_DATE_CREATE_START datetime = NULL
	, @TEST_DATE_CREATE_END datetime = NULL
	, @TEST_DATE_MODIF_START datetime = NULL
	, @TEST_DATE_MODIF_END datetime = NULL
	, @CLI_SNA_IDENTITE varchar(160) = NULL
AS
BEGIN
	SET NOCOUNT ON
 
	DECLARE @sql nvarchar(max) = '
	SELECT	r.TEST_BOI_REF
		, r.TEST_TIMESTAMP
		, r.RowNum
	FROM	(
			SELECT		DISTINCT f.TEST_BOI_REF
					, f.TEST_TIMESTAMP
					, ROW_NUMBER() OVER (ORDER BY f.TEST_BOI_REF, f.TEST_TIMESTAMP) AS RowNum
			FROM		dbo.T_TEST f
			LEFT JOIN	(
						SELECT	DISTINCT boi.CON_ID
							, boi.BOI_REF
						FROM	dbo.S_T_BOITIER_BOI AS boi
					) AS b
						ON b.BOI_REF = f.TEST_BOI_REF
			LEFT JOIN	(
						SELECT		cli.*
						FROM		S_T_TEST cli
						INNER JOIN	(
									SELECT	MAX(cl.CLI_ID) as CLI_ID
									FROM	dbo.S_T_CLIENTS_CLI cl
									GROUP	BY cl.CON_ID
								) cl
									ON cl.CLI_ID = cli.CLI_ID
					) cli
						ON cli.CON_ID = b.CON_ID
			 WHERE		1 = 1'
			 + CASE WHEN @TEST_BOI_REF IS NULL THEN '' ELSE ' AND f.TEST_BOI_REF = @_TEST_BOI_REF' END
			 + CASE WHEN @TEST_BOI_REF_START IS NULL THEN '' ELSE ' AND f.TEST_BOI_REF >= @TEST_BOI_REF_START' END
			 + CASE WHEN @TEST_BOI_REF_END IS NULL THEN '' ELSE ' AND f.TEST_BOI_REF <= @TEST_BOI_REF_END' END
			 + CASE WHEN @SEV_ID IS NULL THEN '' ELSE ' AND f.SEV_ID <= @SEV_ID' END
			 + CASE WHEN @TEST_DATE_CREATE_START IS NULL THEN '' ELSE ' AND f.TEST_DATE_CREA >= @TEST_DATE_CREATE_START' END
			 + CASE WHEN @TEST_DATE_MODIF_START IS NULL THEN '' ELSE ' AND f.TEST_DATE_MODIF >= @TEST_DATE_MODIF_START' END
			 + CASE WHEN @TEST_DATE_CREATE_END IS NULL THEN '' ELSE ' AND f.TEST_DATE_CREA < @TEST_DATE_CREATE_END' END
			 + CASE WHEN @TEST_DATE_MODIF_END IS NULL THEN '' ELSE ' AND f.TEST_DATE_MODIF < @TEST_DATE_MODIF_END' END
			 + CASE WHEN @CLI_SNA_IDENTITE IS NULL THEN '' ELSE ' AND cli.CLI_SNA_IDENTITE LIKE @CLI_SNA_IDENTITE' END
			 + '
		) r
	WHERE	r.RowNum >= @TOP_BEGIN
	AND	r.RowNum <= @TOP_END'
 
	CREATE TABLE #TOTO
	(
		TEST_BOI_REF varchar(10)
		, TEST_TIMESTAMP datetime
		, RowNum int
		, PRIMARY KEY(TEST_BOI_REF,TEST_TIMESTAMP)
	)
 
	INSERT INTO #TOTO
	(
		TEST_BOI_REF
		, TEST_TIMESTAMP
		, RowNum
	)
	EXEC sys.sp_executesql @sql
END

Attention je n'ai pas testé le code, mais cela vous donne une idée.

@++

[olivtone]

ok merci beaucoup

ce n'est pas le phenomene du kitchen sink ou un truc comme ca ?

j'ai une erreur de syntaxe sur le script mais je vais corriger ca

[SQLpro]

Vous ne nous avez pas donnée le DDL des tables en jeu car je soupçonne tout à fait autre chose....

A +

[olivtone]

pour un souci de confidentialité je préfère ne pas les donner sinon je peux vous les donner par mail

quelle est votre soupçon ?

merci beaucoup

[SQLpro]

Les noms de tes colonnes sont confidentiels ? Bizarre ! Dans ce cas mets Colonne1, Colonne2...

Je soupçonne l'usage de VARCHAR inadaptés...


A +

[olivtone]

oui tu as raison et merci de ton aide

1er table :

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
 
 
CREATE TABLE [dbo].[T_TOTO](
	[TEST_ID] [bigint] IDENTITY(1,1) NOT NULL,
	[TEST_BOI_REF] [varchar](10) NOT NULL,
	[TEST_TIMESTAMP] [datetime] NOT NULL,
	[SEV_ID] [int] NOT NULL,
	[TEST_FILE_NAME] [varchar](255) NOT NULL,
	[TEST_CODE_EVENT] [varchar](10) NOT NULL,
	[TEST_DATA] [text] NOT NULL,
	[TEST_DATE_CREA] [datetime] NOT NULL,
	[TEST_DATE_MODIF] [datetime] NOT NULL,
	[TEST_PREV_BAT_ID] [varchar](10) NOT NULL,
	[TEST_CURR_BAT_ID] [varchar](10) NOT NULL,
	[TEST_BAT_VOLT] [int] NOT NULL,
	[TEST_BAT_COUNT] [int] NOT NULL,
	[TEST_MANUF_DATE] [datetime] NOT NULL,
	[TEST_DEVICE_TEMP] [decimal](9, 2) NOT NULL,
	[TEST_DEVICE_FIRM] [varchar](10) NOT NULL,
	[TEST_MILEAGE_STATUT] [varchar](1) NOT NULL,
	[TEST_MILEAGE_CELLID_FREQ] [int] NOT NULL,
	[TEST_MILEAGE_BUFF_PACK_MULT] [int] NOT NULL,
	[TEST_BFR_SLEEP] [varchar](1) NOT NULL,
	[TEST_BFR_SLEEP_WAIT_TIME] [int] NOT NULL,
	[TEST_ARBIT_STATUT] [varchar](1) NOT NULL,
	[TEST_ARBIT_FREQ] [int] NOT NULL,
	[TEST_ZONE_STATUT] [varchar](1) NOT NULL,
	[TEST_ZONE_CELLID_FREQ] [int] NOT NULL,
	[TEST_HIGH_GFORCE_STATUT] [varchar](1) NOT NULL,
	[TEST_HIGH_GFORCE_X] [int] NOT NULL,
	[TEST_HIGH_GFORCE_Y] [int] NOT NULL,
	[TEST_HIGH_GFORCE_Z] [int] NOT NULL,
	[TEST_LOW_GFORCE_STATUT] [varchar](1) NOT NULL,
	[TEST_LOW_GFORCE_X] [int] NOT NULL,
	[TEST_LOW_GFORCE_Y] [int] NOT NULL,
	[TEST_LOW_GFORCE_Z] [int] NOT NULL,
	[TEST_REAL_TIME_STATUT] [varchar](1) NOT NULL,
	[TEST_REAL_TIME_FREQ_UNIT] [varchar](1) NOT NULL,
	[TEST_REAL_TIME_FREQ] [int] NOT NULL,
	[TEST_REAL_TIME_COUNT] [int] NOT NULL,
	[TEST_REAL_TIME_RTESTRD_STATUT] [varchar](1) NOT NULL,
	[COS_ID] [int] NOT NULL,
 CONSTRAINT [PK_T_TEST_CONFIG_TEST] PRIMARY KEY CLUSTERED 
(
	[TEST_ID] ASC
)

2eme table :

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
 
CREATE TABLE [dbo].[T_TOTO_2](
	[TESTCOLONNE2] [int] COLONNE2ENTITY(1,1) NOT NULL,
	[TESTREF] [varchar](10) NOT NULL,
	[YBO_COLONNE2] [int] NOT NULL,
	[SBO_COLONNE2] [int] NOT NULL,
	[CON_COLONNE2] [int] NULL,
	[TQB_COLONNE2] [int] NULL,
	[TESTANSWER] [varchar](255) NULL,
	[TESTISEMAIL_COLONNE1] [bit] NOT NULL,
	[TESTEMAIL_COLONNE1] [varchar](100) NULL,
	[TESTISTEL1_COLONNE1] [bit] NOT NULL,
	[TESTTEL1_COLONNE1] [varchar](50) NULL,
	[TESTISTEL2_COLONNE1] [bit] NOT NULL,
	[TESTTEL2_COLONNE1] [varchar](50) NULL,
	[TESTISTEL3_COLONNE1] [bit] NOT NULL,
	[TESTTEL3_COLONNE1] [varchar](50) NULL,
	[TESTEMAIL_COLONNE1_NEW] [varchar](100) NULL,
	[TESTIMSI_SIM_NUMBER] [varchar](50) NULL,
	[TESTICCCOLONNE2_SIM_NUMBER] [varchar](50) NULL,
	[TESTNUM_SERIE] [varchar](25) NOT NULL,
	[TESTNUM_TEL] [varchar](25) NOT NULL,
	[TESTCREDIT] [int] NOT NULL,
	[TBA_COLONNE2] [int] NOT NULL,
	[TESTCOLONNE1_DATE] [datetime] NULL,
	[HCO_COLONNE2_COLONNE1] [bigint] NULL,
	[TESTGUCOLONNE2] [uniqueCOLONNE2entifier] NULL,
	[USR_COLONNE2_COLONNE1] [int] NULL,
	[TESTDT_ACTIVATION] [datetime] NULL,
	[CEN_COLONNE2] [int] NULL,
	[CDB_COLONNE2] [int] NULL,
	[OPS_COLONNE2] [uniqueCOLONNE2entifier] NULL,
 CONSTRAINT [PK_T_BOITIER_BOI] PRIMARY KEY CLUSTERED 
(
	[TESTCOLONNE2] ASC
)

3eme table

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
 
CREATE TABLE [dbo].[T_COLONNE2ENTS_COLONNE2](
	[COLONNE2_ID] [int] IDENTITY(1,1) NOT NULL,
	[CON_ID] [int] NOT NULL,
	[COLONNE2_TEST_CIVILITE] [varchar](100) NULL,
	[COLONNE2_TEST_IDENTITE] [varchar](160) NULL,
	[COLONNE2_TEST_REMISE] [varchar](250) NULL,
	[COLONNE2_TEST_COMPLEMENT_LOCATION] [varchar](4000) NULL,
	[COLONNE2_TEST_MENTION] [varchar](4000) NULL,
	[COLONNE2_TEST_CP] [varchar](50) NULL,
	[COLONNE2_TEST_VILLE] [varchar](4000) NULL,
	[COLONNE2_BOO_NPAI] [bit] NULL,
	[COLONNE2_NUM_TEL] [varchar](50) NULL,
	[COLONNE2_NUM_TEL_PERSO] [varchar](50) NULL,
	[COLONNE2_NUM_TEL_PORTABLE] [varchar](50) NULL,
	[COLONNE2_NUM_FAX] [varchar](50) NULL,
	[COLONNE2_EMAIL_PRO] [varchar](100) NULL,
	[COLONNE2_EMAIL_PERSO] [varchar](100) NULL,
	[COLONNE2_BOO_STOP_RELANCE] [bit] NULL,
	[COLONNE2_ADRESSE] [varchar](4000) NULL,
	[COLONNE2_TEST_PAYS] [varchar](100) NULL,
 CONSTRAINT [PK_T_COLONNE2ENTS_COLONNE2_1] PRIMARY KEY CLUSTERED 
(
	[COLONNE2_ID] ASC
)

[SQLpro]

1) remplacez vos VARCHAR(1) par des CHAR(1).

2) votre table [dbo].[T_COLONNE2ENTS_COLONNE2] possède des VARCHAR de grand dimension (4000). Dans certaines opérations comme GROUP BY ou ORDER BY, le moteur va aligner les VARCHAR en CHAR (seul moyen d'effectuer les opérations de comparaison, tri ou dans certains cas groupage). De ce fait, la mémoire utilisée par cette opération peut être énorme ou sous estimée...

Une solution serait de vérifier cette hypothèse en remplaçant toutes les colonnes VARCHAR de grende dimension par des CHAR de petite dimension et relancer la requête pour voir si le problème persiste.

A +

[olivtone]

merci de ton retour

helas toujours le meme souci

je vais continuer a investiguer de mon coté c'est un probleme interessant

[elsuket]

Si vous êtes passé sur une nouvelle procédure stockée qui génère le code de la requête pour l'exécuter ensuite, comme dans l'exemple que je vous ai proposé, il va falloir :

• tracer les requêtes
• capturer puis analyser le plan des requêtes qui sont lentes
• OU récupérer les requêtes lentes à partir du cache de requêtes, à l'aide de la vue sys.dm_exec_query_stats.

@++

[olivtone]

Bonjour a Tous et merci encore a vous deux

j'ai reecris la requete, et l'alerte a disparu, donc je souhaitais te remercier

J'ai acheté votre livre sur SQL Server 2014 et franchement il est excellent, je le devore dans le train tous les jours

merci a vous deux encore, c'est vraiment top

[elsuket]

Merci de votre retour et bonne lecture !

A bientôt,

@++