index

Bonjour Christophe,

Voir ma tache "Rebuild Index..." au sein du plan de maintenance..

Fred.M.

"christophe" a écrit :

connaissez vous un moyen automatique pour pouvoir detruire et reconstruire
un index ?
je trouve le rebuild bien rapide et surtout la fragmentation est toujours
presente ?

Bonjour

christophe a écrit :

connaissez vous un moyen automatique pour pouvoir detruire et
reconstruire un index ?
je trouve le rebuild bien rapide et surtout la fragmentation est
toujours presente ?

soit :
DROP INDEX
CREATE INDEX.

soit :
CREATE INDEX ... WITH DROP EXISTING

A +

--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

On 1 sep, 10:47, Fred BROUARD <broua...@club-internet.fr> wrote:

Bonjour

christophe a écrit :> connaissez vous un moyen automatique pour pouvoir detruire et
> reconstruire un index ?
> je trouve le rebuild bien rapide et surtout la fragmentation est
> toujours presente ?

soit :
DROP INDEX
CREATE INDEX.

soit :
CREATE INDEX ... WITH DROP EXISTING

A +

--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langag e SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
*********************http://www.datasapiens.com***********************

j'ai trouvé un script sur syscentral... pour une db de 150 Gigba +-
1h30 Mais attention car ca beaucoup dans le transacation log. Mais il
y'a des solutions(patitionning table..).



set nocount ON
declare @dbname varchar(100)

set @dbname ='CdbprodV2'

CREATE TABLE #tables(
rid int identity (1,1),
tabid int,
[name] varchar(100)
)

CREATE TABLE #indexes(
rid int identity (1,1),
indid int,
[name] varchar(100)
)

CREATE TABLE #fragreport(
fid int identity (1,1),
[timestamp] datetime default getdate(),
ObjectName sysname,
ObjectId int,
IndexName sysname,
IndexId int,
[Level] int,
Pages int,
[Rows] int,
MinimumRecordSize int,
MaximumRecordSize int,
AverageRecordSize float,
ForwardedRecords int,
Extents int,
ExtentSwitches int,
AverageFreeBytes float,
AveragePageDensity float,
ScanDensity float,
BestCount int,
ActualCount int,
LogicalFragmentation float,
ExtentFragmentation float,
DBName varchar(100) NULL,
PrePost varchar(20) NULL
)

CREATE TABLE #reindex(
rid int identity (1,1),
ObjectName sysname,
IndexName sysname
)

DECLARE @numtables int,
@numindexes int,
@numreindexes int,
@tabcount int,
@indcount int,
@recount int,
@currtable int,
@tabname varchar(100),
@currind int,
@indname varchar(100)


SET @tabcount = 1

INSERT INTO #tables([tabid], [name])
EXEC ('SELECT [id], ltrim(rtrim(su.[name] + ''.'' + so.[name])) FROM '
+ @dbname + '.dbo.sysobjects so INNER JOIN ' + @dbname +
'.dbo.sysusers su ON su.uid = so.uid WHERE so.xtype = ''U'' AND so.
[name] <> ''dtproperties''')

SELECT @numtables = count(*) FROM #tables

WHILE @tabcount <= @numtables
BEGIN
SET @indcount = 1

SELECT @currtable = tabid,
@tabname = ltrim(rtrim([name]))
FROM #tables
WHERE rid = @tabcount

INSERT INTO #fragreport([ObjectName], [ObjectId], [IndexName],
[IndexId], [Level], [Pages], [Rows], [MinimumRecordSize],
[MaximumRecordSize], [AverageRecordSize], [ForwardedRecords],
[Extents], [ExtentSwitches], [AverageFreeBytes], [AveragePageDensity],
[ScanDensity], [BestCount], [ActualCount], [LogicalFragmentation],
[ExtentFragmentation])
EXEC('USE ' + @dbname + ' DBCC SHOWCONTIG ([' + @tabname + ']) WITH
ALL_INDEXES, TABLERESULTS')
--print 'EXEC(''USE ' + @dbname + ' DBCC SHOWCONTIG ([' + @tabname +
']) WITH ALL_INDEXES, TABLERESULTS'')'
DELETE FROM #fragreport
WHERE IndexID IN (0,255)

UPDATE #fragreport
SET PrePost = 'PRE'
WHERE PrePost is NULL

INSERT #indexes([indid], [name])
EXEC ('SELECT indid, [name] FROM ' + @dbname + '.dbo.sysindexes WHERE
[id] = ' + @currtable + ' AND [name] not like ''_WA%'' AND indid NOT
IN (0, 255)')

SELECT @numindexes = count(*) FROM #indexes

WHILE @indcount <= @numindexes
BEGIN
SELECT @currind = indid,
@indname = ltrim(rtrim([name]))
FROM #indexes
WHERE rid = @indcount
EXEC ('DBCC INDEXDEFRAG (''' + @dbname + ''',''' + @tabname +
''',''' + @indname + ''')')
--print 'EXEC (''DBCC INDEXDEFRAG (''''' + @dbname + ''''',''''' +
@tabname + ''''',''''' + @indname + ''''')'')'
SET @indcount = @indcount + 1
END

INSERT INTO #fragreport([ObjectName], [ObjectId], [IndexName],
[IndexId], [Level], [Pages], [Rows], [MinimumRecordSize],
[MaximumRecordSize], [AverageRecordSize], [ForwardedRecords],
[Extents], [ExtentSwitches], [AverageFreeBytes], [AveragePageDensity],
[ScanDensity], [BestCount], [ActualCount], [LogicalFragmentation],
[ExtentFragmentation])
EXEC('USE ' + @dbname + ' DBCC SHOWCONTIG ([' + @tabname + ']) WITH
ALL_INDEXES, TABLERESULTS')
--print 'EXEC(''USE ' + @dbname + ' DBCC SHOWCONTIG ([' + @tabname +
']) WITH ALL_INDEXES, TABLERESULTS'')'

DELETE FROM #fragreport
WHERE IndexID IN (0,255)

UPDATE #fragreport
SET PrePost = 'POST'
WHERE PrePost is NULL

SET @tabcount = @tabcount + 1
TRUNCATE TABLE #indexes
END

INSERT INTO #reindex([ObjectName],[IndexName])
SELECT #tables.[name], #fragreport.[IndexName]
FROM #fragreport
INNER JOIN #tables on #tables.tabid = #fragreport.objectid
WHERE #fragreport.IndexId NOT IN (0, 255)
AND (#fragreport.ScanDensity < 90 OR #fragreport.LogicalFragmentation

10)

AND #fragreport.PrePost = 'POST'

SELECT @numreindexes = count(*) FROM #reindex

SET @recount = 1

WHILE @recount <= @numreindexes
BEGIN
SELECT @tabname = ObjectName,
@indname = IndexName
FROM #reindex
WHERE rid = @recount

EXEC('DBCC DBREINDEX([' + @dbname + '.' + @tabname + '],[' + @indname
+ '])')
--print 'EXEC(''DBCC DBREINDEX([' + @dbname + '.' + @tabname + '],['
+ @indname + '])'')'

INSERT INTO #fragreport([ObjectName], [ObjectId], [IndexName],
[IndexId], [Level], [Pages], [Rows], [MinimumRecordSize],
[MaximumRecordSize], [AverageRecordSize], [ForwardedRecords],
[Extents], [ExtentSwitches], [AverageFreeBytes], [AveragePageDensity],
[ScanDensity], [BestCount], [ActualCount], [LogicalFragmentation],
[ExtentFragmentation])
EXEC('USE ' + @dbname + ' DBCC SHOWCONTIG ([' + @tabname + '],[' +
@indname + ']) WITH TABLERESULTS')
--print 'EXEC(''USE ' + @dbname + ' DBCC SHOWCONTIG ([' + @tabname +
'],[' + @indname + ']) WITH TABLERESULTS'')'

SET @recount = @recount + 1
END

UPDATE #fragreport
SET PrePost = 'REINDEXED'
WHERE PrePost is NULL

UPDATE #fragreport
SET DBName = @dbname

INSERT INTO [master].[dbo].[fragreport]([timestamp], [ObjectName],
[ObjectId], [IndexName], [IndexId], [Level], [Pages], [Rows],
[MinimumRecordSize], [MaximumRecordSize], [AverageRecordSize],
[ForwardedRecords], [Extents], [ExtentSwitches], [AverageFreeBytes],
[AveragePageDensity], [ScanDensity], [BestCount], [ActualCount],
[LogicalFragmentation], [ExtentFragmentation], [DBName], [PrePost])
SELECT [timestamp], [ObjectName], [ObjectId], [IndexName], [IndexId],
[Level], [Pages], [Rows], [MinimumRecordSize], [MaximumRecordSize],
[AverageRecordSize], [ForwardedRecords], [Extents], [ExtentSwitches],
[AverageFreeBytes], [AveragePageDensity], [ScanDensity], [BestCount],
[ActualCount], [LogicalFragmentation], [ExtentFragmentation],
[DBName], [PrePost] FROM #fragreport

DROP TABLE #tables
DROP TABLE #indexes
DROP TABLE #fragreport
DROP TABLE #reindex

le probleme c'est comment puis je recréer simplement l'index.

car si je fais un drop j'ai furieusement interet à pouvoir le refaire
pareil.
Donc cela entraine une question : comment recréer un index identique à
l'origine ?

ou alors comme avec le WITH DROP EXISTING mais sur le alter.




"zoltix" <zoltix@gmail.com> a écrit dans le message de
news:1188797772.666049.153280@g4g2000hsf.googlegroups.com...
On 1 sep, 10:47, Fred BROUARD <broua...@club-internet.fr> wrote:

Bonjour

christophe a écrit :> connaissez vous un moyen automatique pour pouvoir
detruire et
> reconstruire un index ?
> je trouve le rebuild bien rapide et surtout la fragmentation est
> toujours presente ?

soit :
DROP INDEX
CREATE INDEX.

soit :
CREATE INDEX ... WITH DROP EXISTING

A +

--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
*********************http://www.datasapiens.com***********************

j'ai trouvé un script sur syscentral... pour une db de 150 Gigba +-
1h30 Mais attention car ca beaucoup dans le transacation log. Mais il
y'a des solutions(patitionning table..).



set nocount ON
declare @dbname varchar(100)

set @dbname ='CdbprodV2'

CREATE TABLE #tables(
rid int identity (1,1),
tabid int,
[name] varchar(100)
)

CREATE TABLE #indexes(
rid int identity (1,1),
indid int,
[name] varchar(100)
)

CREATE TABLE #fragreport(
fid int identity (1,1),
[timestamp] datetime default getdate(),
ObjectName sysname,
ObjectId int,
IndexName sysname,
IndexId int,
[Level] int,
Pages int,
[Rows] int,
MinimumRecordSize int,
MaximumRecordSize int,
AverageRecordSize float,
ForwardedRecords int,
Extents int,
ExtentSwitches int,
AverageFreeBytes float,
AveragePageDensity float,
ScanDensity float,
BestCount int,
ActualCount int,
LogicalFragmentation float,
ExtentFragmentation float,
DBName varchar(100) NULL,
PrePost varchar(20) NULL
)

CREATE TABLE #reindex(
rid int identity (1,1),
ObjectName sysname,
IndexName sysname
)

DECLARE @numtables int,
@numindexes int,
@numreindexes int,
@tabcount int,
@indcount int,
@recount int,
@currtable int,
@tabname varchar(100),
@currind int,
@indname varchar(100)


SET @tabcount = 1

INSERT INTO #tables([tabid], [name])
EXEC ('SELECT [id], ltrim(rtrim(su.[name] + ''.'' + so.[name])) FROM '
+ @dbname + '.dbo.sysobjects so INNER JOIN ' + @dbname +
'.dbo.sysusers su ON su.uid = so.uid WHERE so.xtype = ''U'' AND so.
[name] <> ''dtproperties''')

SELECT @numtables = count(*) FROM #tables

WHILE @tabcount <= @numtables
BEGIN
SET @indcount = 1

SELECT @currtable = tabid,
@tabname = ltrim(rtrim([name]))
FROM #tables
WHERE rid = @tabcount

INSERT INTO #fragreport([ObjectName], [ObjectId], [IndexName],
[IndexId], [Level], [Pages], [Rows], [MinimumRecordSize],
[MaximumRecordSize], [AverageRecordSize], [ForwardedRecords],
[Extents], [ExtentSwitches], [AverageFreeBytes], [AveragePageDensity],
[ScanDensity], [BestCount], [ActualCount], [LogicalFragmentation],
[ExtentFragmentation])
EXEC('USE ' + @dbname + ' DBCC SHOWCONTIG ([' + @tabname + ']) WITH
ALL_INDEXES, TABLERESULTS')
--print 'EXEC(''USE ' + @dbname + ' DBCC SHOWCONTIG ([' + @tabname +
']) WITH ALL_INDEXES, TABLERESULTS'')'
DELETE FROM #fragreport
WHERE IndexID IN (0,255)

UPDATE #fragreport
SET PrePost = 'PRE'
WHERE PrePost is NULL

INSERT #indexes([indid], [name])
EXEC ('SELECT indid, [name] FROM ' + @dbname + '.dbo.sysindexes WHERE
[id] = ' + @currtable + ' AND [name] not like ''_WA%'' AND indid NOT
IN (0, 255)')

SELECT @numindexes = count(*) FROM #indexes

WHILE @indcount <= @numindexes
BEGIN
SELECT @currind = indid,
@indname = ltrim(rtrim([name]))
FROM #indexes
WHERE rid = @indcount
EXEC ('DBCC INDEXDEFRAG (''' + @dbname + ''',''' + @tabname +
''',''' + @indname + ''')')
--print 'EXEC (''DBCC INDEXDEFRAG (''''' + @dbname + ''''',''''' +
@tabname + ''''',''''' + @indname + ''''')'')'
SET @indcount = @indcount + 1
END

INSERT INTO #fragreport([ObjectName], [ObjectId], [IndexName],
[IndexId], [Level], [Pages], [Rows], [MinimumRecordSize],
[MaximumRecordSize], [AverageRecordSize], [ForwardedRecords],
[Extents], [ExtentSwitches], [AverageFreeBytes], [AveragePageDensity],
[ScanDensity], [BestCount], [ActualCount], [LogicalFragmentation],
[ExtentFragmentation])
EXEC('USE ' + @dbname + ' DBCC SHOWCONTIG ([' + @tabname + ']) WITH
ALL_INDEXES, TABLERESULTS')
--print 'EXEC(''USE ' + @dbname + ' DBCC SHOWCONTIG ([' + @tabname +
']) WITH ALL_INDEXES, TABLERESULTS'')'

DELETE FROM #fragreport
WHERE IndexID IN (0,255)

UPDATE #fragreport
SET PrePost = 'POST'
WHERE PrePost is NULL

SET @tabcount = @tabcount + 1
TRUNCATE TABLE #indexes
END

INSERT INTO #reindex([ObjectName],[IndexName])
SELECT #tables.[name], #fragreport.[IndexName]
FROM #fragreport
INNER JOIN #tables on #tables.tabid = #fragreport.objectid
WHERE #fragreport.IndexId NOT IN (0, 255)
AND (#fragreport.ScanDensity < 90 OR #fragreport.LogicalFragmentation

10)

AND #fragreport.PrePost = 'POST'

SELECT @numreindexes = count(*) FROM #reindex

SET @recount = 1

WHILE @recount <= @numreindexes
BEGIN
SELECT @tabname = ObjectName,
@indname = IndexName
FROM #reindex
WHERE rid = @recount

EXEC('DBCC DBREINDEX([' + @dbname + '.' + @tabname + '],[' + @indname
+ '])')
--print 'EXEC(''DBCC DBREINDEX([' + @dbname + '.' + @tabname + '],['
+ @indname + '])'')'

INSERT INTO #fragreport([ObjectName], [ObjectId], [IndexName],
[IndexId], [Level], [Pages], [Rows], [MinimumRecordSize],
[MaximumRecordSize], [AverageRecordSize], [ForwardedRecords],
[Extents], [ExtentSwitches], [AverageFreeBytes], [AveragePageDensity],
[ScanDensity], [BestCount], [ActualCount], [LogicalFragmentation],
[ExtentFragmentation])
EXEC('USE ' + @dbname + ' DBCC SHOWCONTIG ([' + @tabname + '],[' +
@indname + ']) WITH TABLERESULTS')
--print 'EXEC(''USE ' + @dbname + ' DBCC SHOWCONTIG ([' + @tabname +
'],[' + @indname + ']) WITH TABLERESULTS'')'

SET @recount = @recount + 1
END

UPDATE #fragreport
SET PrePost = 'REINDEXED'
WHERE PrePost is NULL

UPDATE #fragreport
SET DBName = @dbname

INSERT INTO [master].[dbo].[fragreport]([timestamp], [ObjectName],
[ObjectId], [IndexName], [IndexId], [Level], [Pages], [Rows],
[MinimumRecordSize], [MaximumRecordSize], [AverageRecordSize],
[ForwardedRecords], [Extents], [ExtentSwitches], [AverageFreeBytes],
[AveragePageDensity], [ScanDensity], [BestCount], [ActualCount],
[LogicalFragmentation], [ExtentFragmentation], [DBName], [PrePost])
SELECT [timestamp], [ObjectName], [ObjectId], [IndexName], [IndexId],
[Level], [Pages], [Rows], [MinimumRecordSize], [MaximumRecordSize],
[AverageRecordSize], [ForwardedRecords], [Extents], [ExtentSwitches],
[AverageFreeBytes], [AveragePageDensity], [ScanDensity], [BestCount],
[ActualCount], [LogicalFragmentation], [ExtentFragmentation],
[DBName], [PrePost] FROM #fragreport

DROP TABLE #tables
DROP TABLE #indexes
DROP TABLE #fragreport
DROP TABLE #reindex