Activation / désaction index et dead lock

[cpdump]

Bonjour

Dans le cadre d'un ETL avec SSIS, je fais un miroir d'une base Oracle (sur une sélection de table) que je copie dans une base SQL. Lors du traitement j'ai des problèmes de dead lock sur les index.

Pour chaque table, j'ai créé un package SSIS qui exécute les opérations suivantes :

1. Désactivation des index non clustered
2. Création d'une requête SQL permettant de recréer l'index plus tard
3. Suppression de l'index Clustered
4. Copie de table oracle vers SQL
5. Re créaction de l'index Clustered via la requête SQL préalablement créée
6. Ré activation des index non clustered

Pour les opérations sur les index, je passe par une procédure stockée, et c'est dans ces procédures qu'il y a des dead lock. Je ne comprend pas trop pourquoi il y a des problèmes de dead lock car les packages sont indépendants puisque chacun traite un table différente.

Les non clustered ne sont pas supprimés car écrire une procédure pour les régénérer est complexe, et cela permet de les modifier sans impacter les packages SSIS

Est ce ce que quelqu'un a une idée ?
Merci

PS : Je n'ai pas encore regardé avec sql profiler car je n'ai pas les droits suffisants pour l'utiliser.

PPS : je peux poster le code des procédures stockées pour les index clustered si cela est utile

Procédure pour la gestion des index non clustered :

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
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
CREATE PROCEDURE [dbo].[up_DisableEnableNonClusteredIndexes] (
 @DB_NAME SYSNAME,
 @TABLE_NAME SYSNAME,
 @MODEID CHAR(1)
)
AS
-- ************** Functionality ********************
-- This script Disables or Enables Non-Clustered indexes that are not part of a primary key or unique key.
 
-- ************** Input Parameters ********************
-- @Db_Name, Name of database for which you want to disable /Rebuild Non-Clustered Indexes
-- @TABLE_NAME, nom de la table AVEC le schema
-- MODEID = 1 SCRIPT DISABLES ALL NON-CLUSTERED INDEXES.
-- MODEID = 2 SCRIPT ENABLES / REBUILD ALL NON-CLUSTERED INDEXES.
 
--  ******************** Compatiblility  ********************
-- Compatible with Sql Server 2005 and higher versions.
--
-- Basé sur la procedure disponible sur
-- http://blog.sqlauthority.com/2009/02/19/sql-server-enable-and-disable-index-non-clustered-indexes-using-t-sql/
-- le ChangeRecoveryModel a été supprimé
--
 
SET NOCOUNT ON
--DECLARE @RECOVERYMODEL VARCHAR(20)
DECLARE @SQL1 VARCHAR(1000)
DECLARE @SQL2 VARCHAR(1000)
DECLARE @SCH_NAME VARCHAR(200)
DECLARE @STORENCINDEX TABLE (IDENT int IDENTITY (1,1),
					  FULLOBJECTNAME varchar(200),
					  INDEXNAME varchar(100))
 
-- Deduit le schema et la table
SET @SCH_NAME = SUBSTRING(@TABLE_NAME, 1, CHARINDEX('.',@TABLE_NAME)-1)
--SET @SCH_NAME='dbo'
PRINT 'Schema : '+@SCH_NAME
SET @TABLE_NAME = SUBSTRING(@TABLE_NAME, CHARINDEX('.',@TABLE_NAME)+1, 200)
PRINT 'Table : '+@TABLE_NAME
 
-- IDENT = IDENTITY (INT,1,1),
SET  @SQL1 = 'USE '+@DB_NAME+ '
SELECT	SC.[TABLE_SCHEMA]+''.''+SC.[TABLE_NAME] [FULLOBJECTNAME]
		,SI.[NAME] [INDEXNAME]
FROM	SYS.INDEXES I JOIN SYS.TABLES T ON I.[OBJECT_ID] = T.[OBJECT_ID] JOIN SYSINDEXES SI ON SI.ID = T.[OBJECT_ID] JOIN INFORMATION_SCHEMA.TABLES SC ON SC.TABLE_NAME = OBJECT_NAME (T.[OBJECT_ID])
WHERE	I.[INDEX_ID] > 1 
		AND		I.[TYPE] = 2 
		AND		I.[IS_PRIMARY_KEY] <> 1
		AND		I.[IS_UNIQUE_CONSTRAINT] <> 1
		AND		I.[INDEX_ID] = SI.INDID
		AND		SC.TABLE_SCHEMA = '''+@SCH_NAME+'''
		AND		SC.TABLE_NAME = '''+@TABLE_NAME+''''
--PRINT @SQL1
--EXEC (@SQL1)
INSERT INTO @STORENCINDEX EXEC (@SQL1)
 
-- SYS.INDEXES.TYPE=1 pour les index clustered
-- Suppression d'un index clustered
-- ALTER TABLE [Clarity].[INV_INVESTMENTS] DROP CONSTRAINT [PK_INV_INVESTMENTS]
-- Création d'un index clustered
-- ALTER TABLE [Clarity].[INV_INVESTMENTS] ADD  CONSTRAINT [PK_INV_INVESTMENTS] PRIMARY KEY CLUSTERED 
-- (
--	[ID] ASC
-- )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
 
 
DECLARE @VAR1 INT
DECLARE @TOTALCOUNT INT
DECLARE @COUNT INT
SET  	@TOTALCOUNT = 0
SET		@COUNT  = 0
DECLARE @MODEDESCRIPTION VARCHAR(50)
SELECT @MODEDESCRIPTION  =	CASE	WHEN @MODEID = 1 THEN 'Disabl'
									ELSE 'Enabl'
							END	
PRINT 'Started '+@MODEDESCRIPTION +'ing all Non-clustered indexes...'
SET @VAR1 = 1
WHILE @VAR1 < = ( SELECT COUNT(*) FROM @STORENCINDEX)
BEGIN 
	DECLARE @OBJECTNAME VARCHAR(256)
	DECLARE @INDEXNAME VARCHAR(128)
	DECLARE @SQLCMD VARCHAR(1000)
 
	SELECT	@OBJECTNAME = [FULLOBJECTNAME]
			, @INDEXNAME = [INDEXNAME] 
	FROM	@STORENCINDEX 
	WHERE	[IDENT] = @VAR1
 
	IF @MODEID = 1 -- DISABLE
	  BEGIN
		SET @SQLCMD ='USE '+@DB_NAME+' ALTER INDEX '+@INDEXNAME +' ON '+@OBJECTNAME+' DISABLE '
		--PRINT 'Query ' + @SQLCMD
		PRINT 'Disabling '+@INDEXNAME
		EXEC (@SQLCMD)	
	END
 
	IF @MODEID = 2 -- ENABLE/REBUILD
	  BEGIN
		SET @SQLCMD ='USE '+@DB_NAME+' ALTER INDEX '+@INDEXNAME +' ON '+@OBJECTNAME+' REBUILD'
		--PRINT 'Query ' + @SQLCMD
		PRINT 'Enabling '+@INDEXNAME
		EXEC (@SQLCMD)
	END
 
	--PRINT @COUNT 
	--PRINT @@ROWCOUNT 
	SET @TOTALCOUNT = @TOTALCOUNT + @COUNT 
 
	SET @VAR1 = @VAR1 + 1
 
	IF (SELECT COUNT(*) FROM @STORENCINDEX )= @VAR1 
	 BEGIN
		SET @TOTALCOUNT = @VAR1
	 END
END
 
--PRINT @TOTALCOUNT
IF @TOTALCOUNT = (SELECT COUNT(*) FROM @STORENCINDEX)
  BEGIN
	PRINT 'Successfully finished '+@MODEDESCRIPTION+'ing all Non-clustered indexes for '+@SCH_NAME+'.'+@TABLE_NAME+' table'
	--PRINT 'Successfully finished '+@MODEDESCRIPTION+'ing all Non-clustered indexes for '+DB_NAME()+' database'
  END 
 
IF @TOTALCOUNT <> (SELECT COUNT(*) FROM @STORENCINDEX)
  BEGIN
	PRINT 'Could not '+@MODEDESCRIPTION+'e all Non-clustered index for '+@SCH_NAME+'.'+@TABLE_NAME+' due to some reason for more information check sql server logs'
  END
 
SET NOCOUNT OFF
-- End of Stored procedure 
 
 
GO

[elsuket]

Bonjour,

Je ne comprend pas trop pourquoi il y a des problèmes de dead lock car les packages sont indépendants puisque chacun traite un table différente.

Ces packages sont-ils exécutés en même temps, ou séquentiellement ?
Est-ce que la base de données cible de l'exécution de ces packages est utilisée, où êtes vous seul à exécuter les packages ?
Si vous ne parvenez pas à savoir comment se produit le deadlock, le plus simple reste effectivement de les capturer à l'aide de SQL Profiler et de l’événement Deadlock Graph

@++

[SQLpro]

Envoyé par cpdump

Est ce ce que quelqu'un a une idée ?

Oui : péter les CLUSTRED => tous les index impactés par toute nouvelles lignes. C'est EXACTEMENT le contraire que vous devez faire !

Si vous êtes en 2005 ou 2008, désactivez les index avec :
ALTER INDEX ??? DISABLE.
Pour réactiver :
ALTER INDEX ??? REBUILD.
Rien de plus simple.

Sous 2000, utilisez la procédure que j'ai écrite à ce sujet :
http://sqlpro.developpez.com/cours/s...ivation-index/

A +

[cpdump]

Envoyé par elsuket

Bonjour,
Ces packages sont-ils exécutés en même temps, ou séquentiellement ?
Est-ce que la base de données cible de l'exécution de ces packages est utilisée, où êtes vous seul à exécuter les packages ?
@++

Les packages peuvent être exécuté en parallèle, SSIS doit être configuré pour lancer 2 ou 4 packages max en parallèle. Je pourrais les lancer séquentiellement mais au détriment des performances.

Le problème est que les dead locks n'arrivent pas à chaque traitement, et en plus la dernière fois, j'ai eu des Deadloack Chain, des deadlock mais pas de deadlock graph bien qu'il soit dans les events tracés

Si vous êtes en 2005 ou 2008, désactivez les index avec :
ALTER INDEX ??? DISABLE.
Pour réactiver :
ALTER INDEX ??? REBUILD.
Rien de plus simple.

Je suis en 2008, je ne peux pas uniquement désactiver les clustered index car de nombreuses opérations ne sont plus possibles. C'est pourquoi je les supprime pour les recréer après le chargement (truncate des anciennes données + récupération de données Oracle)

[elsuket]

des deadlock mais pas de deadlock graph bien qu'il soit dans les events tracés

Quel événement du Profiler avez-vous utilisé ?

Je suis en 2008, je ne peux pas uniquement désactiver les clustered index car de nombreuses opérations ne sont plus possibles. C'est pourquoi je les supprime pour les recréer après le chargement (truncate des anciennes données + récupération de données Oracle)

Dans ce cas faites les manipulations de données dans des tables spécialement réservées à cet effet puis procédez à l'insertion par lots de lignes

@++

[SQLpro]

Envoyé par cpdump

Je suis en 2008, je ne peux pas uniquement désactiver les clustered index car de nombreuses opérations ne sont plus possibles. C'est pourquoi je les supprime pour les recréer après le chargement (truncate des anciennes données + récupération de données Oracle)

Soyez plus précis :
1) désactiver les index les supprime en laissant leur définition dans les tables système afin que vous puissiez les recréer à l'identique.
2) ne supprimez ni les index CLUSTERED ni les index sémantiques (contraintes UNIQUE, FK et PRIMARY)?
3) si vous avez des erreurs, montrez les nous !

A +

[cpdump]

Voila quelques précisions, je peux en donner d'autres au besoin.

Pour supprimer les index clustered, je passe par une procédure stockée (cf. code plus bas), en gros, elle fait un DROP INDEX index ON table WITH ( ONLINE = OFF ) ou
ALTER TABLE table DROP CONSTRAINT index selon le type d'index clustered

Pour activer / désactiver les index non clustered je fais un ENABLE / DISABLE (cf. code dans le premier post)

Les erreurs dans les packges SSIS se situent lors des l’exécution des procédures stockées d'activation / dé activation des index non clustered :

Code :

1
2
3
4
5
6
7
8
Error: 2011-10-25 12:43:14.58
   Code: 0xC002F210
   Source: SQL Disable Non Clustered INDEX Execute SQL Task
   Description: Executing the query "EXECUTE [dbo].up_DisableNonClusteredIndexes ?, ?" failed WITH the following error:
 "Transaction (Process ID 62) was deadlocked on lock resources with another process and has been chosen as the deadlock victim.*
Rerun the transaction.". Possible failure reasons: Problems WITH the query, "ResultSet" property NOT SET correctly, parameters NOT SET
 correctly, OR connection NOT established correctly.
End Error

Procédure de suppression de l'index clustered

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
 
CREATE Procedure [dbo].[up_DropClusteredIndex] (
	@DB_NAME sysname,
	@SchemaNameTableName sysname)
AS
--
/*
 
http://msdn.microsoft.com/en-us/library/ms176118.aspx
The DROP INDEX statement does not apply to indexes created by defining PRIMARY KEY or UNIQUE constraints.
To remove the constraint and corresponding index, use ALTER TABLE with the DROP CONSTRAINT clause.
 
--------- i.is_primary_key 0, i.is_unique 1 , i.is_unique_constraint 0 ---------
 
DROP INDEX [IX_Table2] ON [dbo].[Table2] WITH ( ONLINE = OFF )
 
--------- i.is_primary_key 1, i.is_unique 1 , i.is_unique_constraint 0 -----------
 
ALTER TABLE [dbo].[Table2] DROP CONSTRAINT [PK_Table2]
 
http://msdn.microsoft.com/fr-fr/library/ms177623.aspx
sys.stats
SELECT     i.name AS indexname, i.is_primary_key, i.is_unique, i.is_unique_constraint, t.name AS tablename, st.no_recompute
FROM         sys.tables AS t INNER JOIN
                      sys.indexes AS i ON t.object_id = i.object_id INNER JOIN
                      sys.stats AS st ON i.object_id = st.object_id
*/
BEGIN 
SET NOCOUNT ON
 
DECLARE @SQL1 VARCHAR(1000)	-- contient la requete pour chercher l'index
DECLARE @SCH_NAME VARCHAR(100)
DECLARE @TABLE_NAME VARCHAR(100)
 
-- info sur l'index CLUSTERED
DECLARE @INDEX_NAME VARCHAR(100)
DECLARE @IS_PK BIT
DECLARE @IS_UNIQUE BIT
DECLARE @IS_UNIQUE_CONSTRAINT BIT
 
-- Tables temporaires
DECLARE @STORE_CLUST_INDEX TABLE (INDEXNAME varchar(100),
								  IS_PK bit,
								  IS_UNIQUE bit,
								  IS_UNIQUE_CONSTRAINT bit,
								  ALLOW_ROW_LOCKS bit,
								  ALLOW_PAGE_LOCKS bit)
 
SET @SCH_NAME = SUBSTRING(@SchemaNameTableName, 1, CHARINDEX('.',@SchemaNameTableName)-1)
SET @TABLE_NAME = SUBSTRING(@SchemaNameTableName, CHARINDEX('.',@SchemaNameTableName)+1, 200)
 
-- Création de la requete pour recuperer des infos sur l'eventuel index CLUSTERED
SET  @SQL1 = 'USE '+@DB_NAME+ ';
SELECT	i.name, i.is_primary_key, i.is_unique, i.is_unique_constraint, i.allow_row_locks, i.allow_page_locks
FROM	sys.tables AS t INNER JOIN
                      sys.schemas AS s ON t.schema_id = s.schema_id INNER JOIN
                      sys.indexes AS i ON i.object_id = t.object_id
WHERE	(i.type = 1)
		AND (i.index_id = 1)
		AND (s.name = '''+@sch_NAME+''')
		AND (t.name ='''+@TABLE_NAME+''')'
 
-- Insere dans la table temporaire
INSERT INTO @STORE_CLUST_INDEX EXEC (@SQL1)
 
-- Verifie s'il y a un index
IF (SELECT COUNT(*) FROM @STORE_CLUST_INDEX) = 1
	BEGIN
 
	-- Recupere les infos de l'index
	SELECT	@INDEX_NAME=INDEXNAME, @IS_UNIQUE=IS_UNIQUE, @IS_PK=IS_PK, @IS_UNIQUE_CONSTRAINT=IS_UNIQUE_CONSTRAINT
	FROM	@STORE_CLUST_INDEX
	--DEBUG
	--PRINT @INDEX_NAME
 
	-- Selon le type d'index le statment est different
	IF (@IS_PK = 1) OR (@IS_UNIQUE_CONSTRAINT = 1)
	BEGIN
		-- ALTER TABLE
		SET @SQL1= 'USE ' + @DB_NAME + '; ALTER TABLE ' + @SchemaNameTableName + ' DROP CONSTRAINT ' + @INDEX_NAME
		PRINT @SQL1
		EXEC (@SQL1)
	END
	ELSE
	BEGIN
		-- DROP INDEX
		SET @SQL1= 'USE ' + @DB_NAME + '; DROP INDEX ' + @INDEX_NAME + ' ON ' + @SchemaNameTableName + ' WITH ( ONLINE = OFF )' 
		PRINT @SQL1
		EXEC (@SQL1)
	END
END
 
END

[Christianbt]

bonjour,
avez-vous bien défini des liaisons dans votre package ? n'y a t-il pas des étapes qui se chevauchent ?
Ajoutez des attentes entre les étapes.

Christian

[cpdump]

Les packages peuvent être exécutés en parallèle, mais comme il s'agit de tables indépendantes, cela ne devrait pas poser de problème.