Réindexation - sql serveur 2000.

Version imprimable

11/01/2008, 10h42
ylarvor

Réindexation - sql serveur 2000.

Bonjour,

Nous avons un site Aloes, dont certaines requêtes sql dépassent le time out de la base.

1 - Existe t'il une méthode pour augmenter le time-out entre asp.net et sql serveur ? Cela dépannerait dans un premier temps, nous sommes tenus de répondre au plus vite.

Je me suis proposé d'optimiser la requête afin qu'elle rentre dans le time out. :o))
2 - Comme aucune réindexation n'a été effectué sur le serveur de production, je me suis proposé de lancer celle ci.
Sur sql serveur 2005, il existe un plan de maintenance avec la fonction réindexer la base. Sur Sql serveur 2000, comment procéder-vous pour réindexer la totalité d'une base ?

3 - Aprés la réindexation, je dois vérifier que l'ensemble des index de la requêtes sont bon pour réduire encore le temps de chargement. On verra ca apres...

A+

indexation sql serveur 2000

1 - Existe t'il une méthode pour augmenter le time-out entre asp.net et sql serveur ? Cela dépannerait dans un premier temps, nous sommes tenus de répondre au plus vite.

2 - réindexation

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
USE aloes
go
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FORSELECT table_name FROM information_schema.tablesWHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
go

Exemple de requête à optimiser : 35 secondes.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
declare @id_culture bigint
declare @id_pays bigint
declare @date_debut datetime
declare @date_fin datetime
set @id_culture = 2 
set @id_pays = 1 
set @date_debut ='01/01/2006'
set @date_fin ='01/01/2007'
select l.id_laboratoire,count(a.id_analyse)as nb_analyses 
from fn_analyse_validee_datee_culture_multi(@id_culture) a innerjoin bulletin_analyse ba on a.id_bulletin = ba.id_bulletin 
innerjoin laboratoire l on ba.id_laboratoire = l.id_laboratoire 
innerjoin(selectdistinct id_analyse from partie_analyse where detection = 1) pa on a.id_analyse = pa.id_analyse where a.id_analyse notin(selectdistinct id_analyse from partie_analyse where qte_residu > dbo.fn_lmr_partie(id_partie, @id_pays))and date_reception between @date_debut and @date_fin 
groupby l.id_laboratoire

essayes un truc dans ce style

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
declare @id_culture bigint
declare @id_pays bigint
declare @date_debut datetime
declare @date_fin datetime
set @id_culture = 2 
set @id_pays = 1 
set @date_debut ='01/01/2006'
set @date_fin ='01/01/2007'
 
create table #analyse_culture
(id_analyse int)
 
create index idx_an on #analyse_culture (id_analyse)
 
insert into #analyse_culture
select id_analyse from fn_analyse_validee_datee_culture_multi(@id_culture)
 
create table #analyse_detection
(id_analyse int)
 
insert into #analyse_detection
select distinct id_analyse from partie_analyse where detection = 1
 
create index idx_cu on #analyse_detection (id_analyse)
 
delete #analyse_detection a
from partie_analyse b
where 
a.id_analyse=b.id_analyse
and qte_residu > dbo.fn_lmr_partie(id_partie, @id_pays))
and date_reception between @date_debut and @date_fin
 
select l.id_laboratoire,count(a.id_analyse)as nb_analyses 
from #analyse a 
inner join bulletin_analyse ba on a.id_bulletin = ba.id_bulletin 
inner join laboratoire l on ba.id_laboratoire = l.id_laboratoire 
inner join #analyse_detection pa on a.id_analyse = pa.id_analyse 
group by l.id_laboratoire

merci serge.

salut,

comme j'étais pressé de donner une réponse... le site buggue actuellement... j'ai modifié le timeout.
Code:

1 2 3 4 5 6 7 Dim cmd As SqlCommand = SConnAloes.CreateCommand() cmd.CommandText = SqlAnalyseInfLMR cmd.CommandType = CommandType.Text cmd.CommandTimeout = 300000 Dim da As SqlDataAdapter = New SqlDataAdapter(cmd) da.Fill(DTAnalyseInfLMR)
Maintenant, je vais prendre le temps de réduire le temps de la requete.

11/01/2008, 12h18
ylarvor

sql.

Citation:

DELETE #analyse_detection a
FROM partie_analyse b
WHERE
a.id_analyse=b.id_analyse
AND qte_residu > dbo.fn_lmr_partie(id_partie, @id_pays))
AND date_reception BETWEEN @date_debut AND @date_fin

est ce possible ?
11/01/2008, 12h54
serge0934

oui

Evolution.

Cette requête : 34 secondes.
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 declare @id_culture bigint declare @id_pays bigint declare @date_debut datetime declare @date_fin datetime SET @id_culture = 2 SET @id_pays = 1 SET @date_debut ='01/01/2006' SET @date_fin ='01/01/2007' DROPTABLE #analyse_detection CREATETABLE #analyse_detection(id_analyse bigint) INSERTINTO #analyse_detection SELECTDISTINCT id_analyse FROM partie_analyse WHERE detection = 1 CREATEINDEX idx_cu ON #analyse_detection (id_analyse) DELETE #analyse_detection FROM partie_analyse b WHERE #analyse_detection.id_analyse=b.id_analyse AND qte_residu > dbo.fn_lmr_partie(id_partie, @id_pays) SELECT l.id_laboratoire,count(a.id_analyse)AS nb_analyses FROM dbo.fn_analyse_validee_datee_culture_multi(@id_culture) a INNERJOIN bulletin_analyse ba ON a.id_bulletin = ba.id_bulletin INNERJOIN laboratoire l ON ba.id_laboratoire = l.id_laboratoire INNERJOIN #analyse_detection pa on a.id_analyse = pa.id_analyse AND date_reception BETWEEN @date_debut AND @date_fin GROUPBY l.id_laboratoire
La fonction dbo.fn_analyse_validee_datee_culture_multi(@id_culture) renvoie une table. J'ai dù modifié ton code.

J'ai trouvé pourquoi la requete prend 34 secondes... La fonction dbo.fn_lmr_partie(id_partie, @id_pays) fait appel à une fonction qui fait appel à une grosse fonction...

La requete ci dessous prend 34 secondes à cause de cela...

Citation:

DELETE #analyse_detection
FROM partie_analyse b
WHERE
#analyse_detection.id_analyse=b.id_analyse
AND qte_residu > dbo.fn_lmr_partie(id_partie, @id_pays)

merci serge.

13/01/2008, 17h05
SQLpro

Les fonctions renvoyant des tables ne peuvent pas être optimisées de manière simple. Essayer de les éradiquer : utilisez par exemple une vue.

A +

Script testé qui defrag tous les indexes d'une base qui sont fragmentés à plus de x% ou x est défini dans le code. Utilise dbcc indexdefrag et donc ne crée normalement pas de locks :

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
 
/*Perform a 'USE <database name>' to select the database in which to run the script.*/
-- Declare variables
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr   VARCHAR (255)
DECLARE @objectid  INT
DECLARE @indexid   INT
DECLARE @frag      DECIMAL
DECLARE @maxfrag   DECIMAL
 
-- Decide on the maximum fragmentation to allow
SELECT @maxfrag = 10.0
 
-- Declare cursor
DECLARE tables CURSOR FOR
   SELECT TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE' --and table_name not in ('table_act_entry','table_case')
 
-- Create the table
CREATE TABLE #fraglist (
   ObjectName CHAR (255),
   ObjectId INT,
   IndexName CHAR (255),
   IndexId INT,
   Lvl INT,
   CountPages INT,
   CountRows INT,
   MinRecSize INT,
   MaxRecSize INT,
   AvgRecSize INT,
   ForRecCount INT,
   Extents INT,
   ExtentSwitches INT,
   AvgFreeBytes INT,
   AvgPageDensity INT,
   ScanDensity DECIMAL,
   BestCount INT,
   ActualCount INT,
   LogicalFrag DECIMAL,
   ExtentFrag DECIMAL)
 
-- Open the cursor
OPEN tables
 
-- Loop through all the tables in the database
FETCH NEXT
   FROM tables
   INTO @tablename
 
WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
   INSERT INTO #fraglist 
   EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') 
      WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
   FETCH NEXT
      FROM tables
      INTO @tablename
END
 
-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables
 
-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
   SELECT ObjectName, ObjectId, IndexId, LogicalFrag
   FROM #fraglist
   WHERE LogicalFrag >= @maxfrag
      AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
 
-- Open the cursor
OPEN indexes
 
-- loop through the indexes
FETCH NEXT
   FROM indexes
   INTO @tablename, @objectid, @indexid, @frag
 
WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
      ' + RTRIM(@indexid) + ') - fragmentation currently '
       + RTRIM(CONVERT(varchar(15),@frag)) + '%'
   SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
       ' + RTRIM(@indexid) + ')'
   EXEC (@execstr)
 
 
 
   FETCH NEXT
      FROM indexes
      INTO @tablename, @objectid, @indexid, @frag
END
 
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
DROP TABLE #fraglist
GO

Sinon je te propose ce script ci, plus lourd (dbcc dbreindex) mais qui recrée tous les index d'une DB :

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
 
set nocount on
---------------------------
-- Variables declaration --
---------------------------
 
declare @i int -- Table Count
declare @j int -- Index Count
declare @nbTable int -- Table number
declare @nbIndex int -- Index number
declare @tableName varchar(255)  -- Table name
declare @indexName varchar(255)  -- Table name
 
 
-------------------------------------------------------------------
-- Declare temporary tables which will be used to create the loop --
-------------------------------------------------------------------
create table #tmpTableList
(
	id int identity(1,1),
	name varchar(255)
)
 
create table #tmpIndexList
(
	id int identity(1,1),
	name varchar(255)
)
 
 
--------------------------------------------
-- Load user table list in the temp table --
--------------------------------------------
insert into #tmpTableList
select name 
from sysobjects 
where xtype = 'U' 
order by name
 
----------------------------
-- Counter initialisation --
----------------------------
set @i = 1
select @nbTable = count(id) from #tmpTableList -- Initialise nbTable variable
 
----------
-- Loop --
----------
while @i <= @nbTable
begin
	-----------------------------
	-- Get the iteration table --
	-----------------------------
	select @tableName = name from #tmpTableList where id = @i
 
 
	--------------------
	-- Main loop code --
	--------------------
 
	-- Empty the indexes list table
	truncate table #tmpIndexList
 
	-- Fill the indexes list table with the indexes related from the table @tableName
	insert into #tmpIndexList
	select si.name from sysobjects so
	join sysindexes si on so.id = si.id
	where so.name = @tableName
 
	-- Index count initialisation
	set @j = 1
 
	-- Initialise nbIndex variable
	select @nbIndex = count(id) from #tmpIndexList
 
	while @j <= @nbIndex
	begin
		-- Get the iteration index --
		select @indexName = name from #tmpIndexList where id = @j
 
		-- Reindex the @indexName index from the @tableName table
		DBCC DBREINDEX(@tableName,@indexName)
 
		-- Next iteration		
		set @j = @j + 1
	end
 
	--------------------	
	-- Next iteration --
	--------------------
	set @i = @i + 1
 
end
 
------------------------
-- Post loop commands --
------------------------
 
-------------------------
-- Drop the temp table --
-------------------------
drop table #tmpTableList
drop table #tmpIndexList

Pour plus d'info sur les 2 commandes dbcc, BOL :)

script modifié ?

Bonjour,

Comment modifier ce script afin qu'il defragmente les indexes compris entre 0 et 30 ?

SELECT @maxfrag < 30.0 ??? SELECT @maxfrag between 0 and 30 ?

Merci d'avance

Script:

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
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr   VARCHAR (255)
DECLARE @objectid  INT
DECLARE @indexid   INT
DECLARE @frag      DECIMAL
DECLARE @maxfrag   DECIMAL
 
-- Decide on the maximum fragmentation to allow
SELECT @maxfrag = 10.0
 
-- Declare cursor
DECLARE TABLES CURSOR FOR
   SELECT TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE' --and table_name not in ('table_act_entry','table_case')
 
-- Create the table
CREATE TABLE #fraglist (
   ObjectName CHAR (255),
   ObjectId INT,
   IndexName CHAR (255),
   IndexId INT,
   Lvl INT,
   CountPages INT,
   CountRows INT,
   MinRecSize INT,
   MaxRecSize INT,
   AvgRecSize INT,
   ForRecCount INT,
   Extents INT,
   ExtentSwitches INT,
   AvgFreeBytes INT,
   AvgPageDensity INT,
   ScanDensity DECIMAL,
   BestCount INT,
   ActualCount INT,
   LogicalFrag DECIMAL,
   ExtentFrag DECIMAL)
 
-- Open the cursor
OPEN TABLES
 
-- Loop through all the tables in the database
FETCH NEXT
   FROM TABLES
   INTO @tablename
 
WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
   INSERT INTO #fraglist 
   EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') 
      WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
   FETCH NEXT
      FROM TABLES
      INTO @tablename
END
 
-- Close and deallocate the cursor
CLOSE TABLES
DEALLOCATE TABLES
 
-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
   SELECT ObjectName, ObjectId, IndexId, LogicalFrag
   FROM #fraglist
   WHERE LogicalFrag >= @maxfrag
      AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
 
-- Open the cursor
OPEN indexes
 
-- loop through the indexes
FETCH NEXT
   FROM indexes
   INTO @tablename, @objectid, @indexid, @frag
 
WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
      ' + RTRIM(@indexid) + ') - fragmentation currently '
       + RTRIM(CONVERT(varchar(15),@frag)) + '%'
   SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
       ' + RTRIM(@indexid) + ')'
   EXEC (@execstr)
 
 
 
   FETCH NEXT
      FROM indexes
      INTO @tablename, @objectid, @indexid, @frag
END
 
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
DROP TABLE #fraglist
GO

30/03/2011, 08h29
iberserk

Citation:

Bonjour,

Comment modifier ce script afin qu'il defragmente les indexes compris entre 0 et 30 ?

SELECT @maxfrag < 30.0 ??? SELECT @maxfrag between 0 and 30 ?

Merci d'avance

Code:

WHERE LogicalFrag <= @maxfrag