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