TodoSQL en Twitter
En línea
En este momento hay 0 usuarios y %count invitados en línea.
Foro - Temas Activos
- Problemas con Backup Diferencial
- PLANES DE MANTENIMIENTO SQL Server
- RESTAURAR BASE DE DATOS
- Bloqueo SQL Server 2005 al guardar diagrámas
- Archivo en SQL Server 2000
- Re-Generar Indices de una DB Fragmentada
- Replica SQL Server 2005
- Seguridad en SQL Server
- Solicitud de Ayuda
- trigger para insertar datos de otra tabla
- ayuda con alerta
- URGENTE AYUDA CON RESTAURACION DE BACKUP
- Estamos de vuelta!
- Tablas INNODB pero en SQL
- Ejecutar sentencias T-SQL de SQL Server 2008 en CmdExec
- Leer archivo que se encuentra en un servidor remoto desde tarea programada en Sql Server 2008
- Ayuda: Generar datos ficticios automaticamente
- Optimizar el rendimiento de los procedimientos almacenados de SQL Server 2008
- Errores en Backups
- Maraton SQL Server 2008
Re-Generar Indices de una DB Fragmentada
Posted on: Jue, 10/28/2010 - 13:10
fdonosoc
Joined: 2010-10-28
Points: 0
Offline
Re-Generar Indices de una DB Fragmentada
Estimados, soy nuevo en esto de SQL y necesito de vuestra ayuda.
Tengo un servidor con Win2K Server y SQL Server 2000 v8.0.0.2039 (SP4).
Tengo una DB que esta en producción (funcionamiento) muy fragmentada. Realice la siguiente instruccion para ver como estaban mis tablas y los resultados de estas se las doy mas abajo. Me comentaron que debia Re-Generar los indices para defragmentar las tablas de mi DB, pero la verdad de las cosas que no se como realizar esto, y no quiero hacer nada que me afecte la DB en funcionamiento.
Si me pueden ayudar se los agradezco muchisimo (no olviden que soy nuevo en esto).
Saludos cordiales,
FDC.
Aca los resultados de DBCC SHOWCONTIG:
DBCC SHOWCONTIG scanning 'sysobjects' table...
Table: 'sysobjects' (1); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned................................ : 2
- Extents Scanned.............................. : 2
- Extent Switches.............................. : 1
- Avg. Pages per Extent........................ : 1.0
- Scan Density [Best Count:Actual Count]....... : 50. 00% [1:2]
- Logical Scan Fragmentation.................. : 0. 00%
- Extent Scan Fragmentation................... : 50. 00%
- Avg. Bytes Free per Page..................... : 5051.0
- Avg. Page Density (full)..................... : 37. 60%
DBCC SHOWCONTIG scanning 'sysindexes' table...
Table: 'sysindexes' (2); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned................................ : 5
- Extents Scanned.............................. : 5
- Extent Switches.............................. : 4
- Avg. Pages per Extent........................ : 1.0
- Scan Density [Best Count:Actual Count]....... : 20. 00% [1:5]
- Logical Scan Fragmentation.................. : 20. 00%
- Extent Scan Fragmentation................... : 80. 00%
- Avg. Bytes Free per Page..................... : 3971.6
- Avg. Page Density (full)..................... : 50. 93%
DBCC SHOWCONTIG scanning 'syscolumns' table...
Table: 'syscolumns' (3); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned................................ : 13
- Extents Scanned.............................. : 8
- Extent Switches.............................. : 8
- Avg. Pages per Extent........................ : 1.6
- Scan Density [Best Count:Actual Count]....... : 22. 22% [2:9]
- Logical Scan Fragmentation.................. : 7. 69%
- Extent Scan Fragmentation................... : 87. 50%
- Avg. Bytes Free per Page..................... : 4892.2
- Avg. Page Density (full)..................... : 39. 56%
DBCC SHOWCONTIG scanning 'systypes' table...
Table: 'systypes' (4); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned................................ : 1
- Extents Scanned.............................. : 1
- Extent Switches.............................. : 0
- Avg. Pages per Extent........................ : 1.0
- Scan Density [Best Count:Actual Count]....... : 100. 00% [1:1]
- Logical Scan Fragmentation.................. : 0. 00%
- Extent Scan Fragmentation................... : 0. 00%
- Avg. Bytes Free per Page..................... : 6712.0
- Avg. Page Density (full)..................... : 17. 07%
DBCC SHOWCONTIG scanning 'syscomments' table...
Table: 'syscomments' (6); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned................................ : 13
- Extents Scanned.............................. : 5
- Extent Switches.............................. : 5
- Avg. Pages per Extent........................ : 2.6
- Scan Density [Best Count:Actual Count]....... : 33. 33% [2:6]
- Logical Scan Fragmentation.................. : 15. 38%
- Extent Scan Fragmentation................... : 40. 00%
- Avg. Bytes Free per Page..................... : 4012.2
- Avg. Page Density (full)..................... : 50. 43%
DBCC SHOWCONTIG scanning 'sysfiles1' table...
Table: 'sysfiles1' (8); index ID: 0, database ID: 16
TABLE level scan performed.
- Pages Scanned................................ : 1
- Extents Scanned.............................. : 1
- Extent Switches.............................. : 0
- Avg. Pages per Extent........................ : 1.0
- Scan Density [Best Count:Actual Count]....... : 100. 00% [1:1]
- Extent Scan Fragmentation................... : 0. 00%
- Avg. Bytes Free per Page..................... : 6508.0
- Avg. Page Density (full)..................... : 19. 59%
DBCC SHOWCONTIG scanning 'syspermissions' table...
Table: 'syspermissions' (9); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned................................ : 1
- Extents Scanned.............................. : 1
- Extent Switches.............................. : 0
- Avg. Pages per Extent........................ : 1.0
- Scan Density [Best Count:Actual Count]....... : 100. 00% [1:1]
- Logical Scan Fragmentation.................. : 0. 00%
- Extent Scan Fragmentation................... : 0. 00%
- Avg. Bytes Free per Page..................... : 6974.0
- Avg. Page Density (full)..................... : 13. 84%
DBCC SHOWCONTIG scanning 'sysusers' table...
Table: 'sysusers' (10); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned................................ : 1
- Extents Scanned.............................. : 1
- Extent Switches.............................. : 0
- Avg. Pages per Extent........................ : 1.0
- Scan Density [Best Count:Actual Count]....... : 100. 00% [1:1]
- Logical Scan Fragmentation.................. : 0. 00%
- Extent Scan Fragmentation................... : 0. 00%
- Avg. Bytes Free per Page..................... : 6662.0
- Avg. Page Density (full)..................... : 17. 69%
DBCC SHOWCONTIG scanning 'sysdepends' table...
Table: 'sysdepends' (12); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned................................ : 2
- Extents Scanned.............................. : 2
- Extent Switches.............................. : 1
- Avg. Pages per Extent........................ : 1.0
- Scan Density [Best Count:Actual Count]....... : 50. 00% [1:2]
- Logical Scan Fragmentation.................. : 0. 00%
- Extent Scan Fragmentation................... : 50. 00%
- Avg. Bytes Free per Page..................... : 4456.0
- Avg. Page Density (full)..................... : 44. 95%
DBCC SHOWCONTIG scanning 'sysfilegroups' table...
Table: 'sysfilegroups' (96); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned................................ : 1
- Extents Scanned.............................. : 1
- Extent Switches.............................. : 0
- Avg. Pages per Extent........................ : 1.0
- Scan Density [Best Count:Actual Count]....... : 100. 00% [1:1]
- Logical Scan Fragmentation.................. : 0. 00%
- Extent Scan Fragmentation................... : 0. 00%
- Avg. Bytes Free per Page..................... : 8058.0
- Avg. Page Density (full)..................... : 0. 44%
DBCC SHOWCONTIG scanning 'Maestro Productos' table...
Table: 'Maestro Productos' (357576312); index ID: 0, database ID: 16
TABLE level scan performed.
- Pages Scanned................................ : 1
- Extents Scanned.............................. : 1
- Extent Switches.............................. : 0
- Avg. Pages per Extent........................ : 1.0
- Scan Density [Best Count:Actual Count]....... : 100. 00% [1:1]
- Extent Scan Fragmentation................... : 0. 00%
- Avg. Bytes Free per Page..................... : 7986.0
- Avg. Page Density (full)..................... : 1. 33%
DBCC SHOWCONTIG scanning 'Maestro Locales' table...
Table: 'Maestro Locales' (373576369); index ID: 0, database ID: 16
TABLE level scan performed.
- Pages Scanned................................ : 1
- Extents Scanned.............................. : 1
- Extent Switches.............................. : 0
- Avg. Pages per Extent........................ : 1.0
- Scan Density [Best Count:Actual Count]....... : 100. 00% [1:1]
- Extent Scan Fragmentation................... : 0. 00%
- Avg. Bytes Free per Page..................... : 8046.0
- Avg. Page Density (full)..................... : 0. 59%
DBCC SHOWCONTIG scanning 'Vtas_Consolidadas' table...
Table: 'Vtas_Consolidadas' (453576654); index ID: 0, database ID: 16
TABLE level scan performed.
- Pages Scanned................................ : 5625571
- Extents Scanned.............................. : 703309
- Extent Switches.............................. : 703308
- Avg. Pages per Extent........................ : 8.0
- Scan Density [Best Count:Actual Count]....... : 99. 98% [703197:703309]
- Extent Scan Fragmentation................... : 0. 11%
- Avg. Bytes Free per Page..................... : 1271.0
- Avg. Page Density (full)..................... : 84. 30%
DBCC SHOWCONTIG scanning 'Ventas Historicas' table...
Table: 'Ventas Historicas' (469576711); index ID: 0, database ID: 16
TABLE level scan performed.
- Pages Scanned................................ : 640
- Extents Scanned.............................. : 84
- Extent Switches.............................. : 83
- Avg. Pages per Extent........................ : 7.6
- Scan Density [Best Count:Actual Count]....... : 95. 24% [80:84]
- Extent Scan Fragmentation................... : 4. 76%
- Avg. Bytes Free per Page..................... : 390.2
- Avg. Page Density (full)..................... : 95. 18%
DBCC SHOWCONTIG scanning 'paso' table...
Table: 'paso' (645577338); index ID: 0, database ID: 16
TABLE level scan performed.
- Pages Scanned................................ : 1
- Extents Scanned.............................. : 1
- Extent Switches.............................. : 0
- Avg. Pages per Extent........................ : 1.0
- Scan Density [Best Count:Actual Count]....... : 100. 00% [1:1]
- Extent Scan Fragmentation................... : 0. 00%
- Avg. Bytes Free per Page..................... : 8094.0
- Avg. Page Density (full)..................... : 0. 00%
DBCC SHOWCONTIG scanning 'Rotacion_Semanal_Fasa' table...
Table: 'Rotacion_Semanal_Fasa' (677577452); index ID: 0, database ID: 16
TABLE level scan performed.
- Pages Scanned................................ : 17109
- Extents Scanned.............................. : 2167
- Extent Switches.............................. : 2166
- Avg. Pages per Extent........................ : 7.9
- Scan Density [Best Count:Actual Count]....... : 98. 71% [2139:2167]
- Extent Scan Fragmentation................... : 99. 72%
- Avg. Bytes Free per Page..................... : 275.5
- Avg. Page Density (full)..................... : 96. 60%
DBCC SHOWCONTIG scanning 'FT_Venta_Mensual_Fasa' table...
Table: 'FT_Venta_Mensual_Fasa' (709577566); index ID: 0, database ID: 16
TABLE level scan performed.
- Pages Scanned................................ : 13050
- Extents Scanned.............................. : 1637
- Extent Switches.............................. : 1636
- Avg. Pages per Extent........................ : 8.0
- Scan Density [Best Count:Actual Count]....... : 99. 69% [1632:1637]
- Extent Scan Fragmentation................... : 99. 88%
- Avg. Bytes Free per Page..................... : 320.3
- Avg. Page Density (full)..................... : 96. 04%
DBCC SHOWCONTIG scanning 'FT_Venta_Mensual_CV' table...
Table: 'FT_Venta_Mensual_CV' (725577623); index ID: 0, database ID: 16
TABLE level scan performed.
- Pages Scanned................................ : 16177
- Extents Scanned.............................. : 2027
- Extent Switches.............................. : 2026
- Avg. Pages per Extent........................ : 8.0
- Scan Density [Best Count:Actual Count]....... : 99. 80% [2023:2027]
- Extent Scan Fragmentation................... : 99. 80%
- Avg. Bytes Free per Page..................... : 315.2
- Avg. Page Density (full)..................... : 96. 11%
DBCC SHOWCONTIG scanning 'Rotacion_Semanal_cv' table...
Table: 'Rotacion_Semanal_cv' (837578022); index ID: 0, database ID: 16
TABLE level scan performed.
- Pages Scanned................................ : 39175
- Extents Scanned.............................. : 4915
- Extent Switches.............................. : 4914
- Avg. Pages per Extent........................ : 8.0
- Scan Density [Best Count:Actual Count]....... : 99. 63% [4897:4915]
- Extent Scan Fragmentation................... : 99. 74%
- Avg. Bytes Free per Page..................... : 308.5
- Avg. Page Density (full)..................... : 96. 19%
DBCC SHOWCONTIG scanning 'venta_FASA_diaria' table...
Table: 'venta_FASA_diaria' (1029578706); index ID: 0, database ID: 16
TABLE level scan performed.
- Pages Scanned................................ : 1360
- Extents Scanned.............................. : 172
- Extent Switches.............................. : 171
- Avg. Pages per Extent........................ : 7.9
- Scan Density [Best Count:Actual Count]....... : 98. 84% [170:172]
- Extent Scan Fragmentation................... : 99. 42%
- Avg. Bytes Free per Page..................... : 219.3
- Avg. Page Density (full)..................... : 97. 29%
DBCC SHOWCONTIG scanning ''#csv)datos_fuente(1)$'' table...
Table: ''#csv)datos_fuente(1)$'' (1093578934); index ID: 0, database ID: 16
TABLE level scan performed.
- Pages Scanned................................ : 64
- Extents Scanned.............................. : 9
- Extent Switches.............................. : 8
- Avg. Pages per Extent........................ : 7.1
- Scan Density [Best Count:Actual Count]....... : 88. 89% [8:9]
- Extent Scan Fragmentation................... : 11. 11%
- Avg. Bytes Free per Page..................... : 260.5
- Avg. Page Density (full)..................... : 96. 78%
DBCC SHOWCONTIG scanning 'Mexico_Benavides_Mensual' table...
Table: 'Mexico_Benavides_Mensual' (1157579162); index ID: 0, database ID: 16
TABLE level scan performed.
- Pages Scanned................................ : 1593
- Extents Scanned.............................. : 205
- Extent Switches.............................. : 204
- Avg. Pages per Extent........................ : 7.8
- Scan Density [Best Count:Actual Count]....... : 97. 56% [200:205]
- Extent Scan Fragmentation................... : 2. 44%
- Avg. Bytes Free per Page..................... : 367.1
- Avg. Page Density (full)..................... : 95. 46%
DBCC SHOWCONTIG scanning 'FT_Venta_Semanal_SB' table...
Table: 'FT_Venta_Semanal_SB' (1221579390); index ID: 0, database ID: 16
TABLE level scan performed.
- Pages Scanned................................ : 8853
- Extents Scanned.............................. : 1120
- Extent Switches.............................. : 1119
- Avg. Pages per Extent........................ : 7.9
- Scan Density [Best Count:Actual Count]....... : 98. 84% [1107:1120]
- Extent Scan Fragmentation................... : 7. 77%
- Avg. Bytes Free per Page..................... : 315.2
- Avg. Page Density (full)..................... : 96. 11%
DBCC SHOWCONTIG scanning 'FT_Venta_Mensual_SB' table...
Table: 'FT_Venta_Mensual_SB' (1237579447); index ID: 0, database ID: 16
TABLE level scan performed.
- Pages Scanned................................ : 4880
- Extents Scanned.............................. : 619
- Extent Switches.............................. : 618
- Avg. Pages per Extent........................ : 7.9
- Scan Density [Best Count:Actual Count]....... : 98. 55% [610:619]
- Extent Scan Fragmentation................... : 5. 49%
- Avg. Bytes Free per Page..................... : 313.2
- Avg. Page Density (full)..................... : 96. 13%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
- Inicie sesión o regístrese para enviar comentarios
- 2562 lecturas
Recomendá TodoSQL
Comentarios
Cordial saludo
Por experiencia propia le recomiendo ante de hacer un reconstruccion de indices hacer un backup full de la base de datos
seguidamente ejecute este script
select 'ALTER INDEX all ON dbo.' + name + ' REBUILD' from sys.tables
Y luego en el motor el resultado copielo como texto ekl resultado debe ser todos los indices
USE PRUEBAS
GO
ALTER INDEX all ON dbo.solicitudcanapro REBUILD
ALTER INDEX all ON dbo.parametrosbiometria REBUILD
ALTER INDEX all ON dbo.clasificacion REBUILD
ALTER INDEX all ON dbo.multiplicadorporsalariocodeudores REBUILD
ALTER INDEX all ON dbo.inventarioprestamomercanciasalida REBUILD
ALTER INDEX all ON dbo.devengosfeip REBUILD
ALTER INDEX all ON dbo.tmp#3 REBUILD
ALTER INDEX all ON dbo.cajaconsecutivogeneral REBUILD
ALTER INDEX all ON dbo.tipocarteraytasa REBUILD
ALTER INDEX all ON dbo.webcuposexitorangos REBUILD
ALTER INDEX all ON dbo.fuentesporagencia REBUILD
ALTER INDEX all ON dbo.correspondenciainternaseguimientoanexo REBUILD
ALTER INDEX all ON dbo.scoringcentralesriesgo REBUILD
ALTER INDEX all ON dbo.establecimientocitassolicitudes REBUILD
ALTER INDEX all ON dbo.tmpcreditosenvacaciones0158 REBUILD
ALTER INDEX all ON dbo.pagaresnovedadseguro REBUILD
ALTER INDEX all ON dbo.personasJuridicas REBUILD
ALTER INDEX all ON dbo
y ejecute
este proceso en mi caso se demora 1 hora y media en una base de datos de 40 GB
Hola como estas !, bueno lo que puedes hacer y como te lo detallo en la respuesta dada en este foro. Te paso el link.
http://www.todosql.com/foro/201007/optimizar-rendimiento-procedimientos-...
Espero tus comentarios,
Un abrazo virtual.
Hola JuanPLG:
Mira, realicé los pasos que indicas en el link que me enviaste en una DB de prueba (realizando un export de la DB Fragmentada) realizando estas query:
DBCC SHOWCONTIG #Para ver que tan fragmentadas están las tablas.
#luego según el link, para saber el fillfactor de la tabla, la query es:
select name,fill_factor from sys.indexes #donde name es ¿el nombre de una columna de la tabla?
salect ticket,fill_factor from sys.indexes
si fuese como digo me da el siguiente error:
Server: Msg 208, Lavel 16, State 1, Line 1
Invalid object name 'sys.indexes'.
Con esto estoy parado, me ayudas por favor.
Hola como estas !, bueno de acuerdo al error detallado es que la version de sql server no es >= 2005, por lo tanto el query para un motor version <= 2000 es:
use [mibase]
go
select b.id,a.indid,a.name,a.OrigFillFactor from sysindexes as a
left join sysobjects as b
on
a.id = b.id
where indid <> '0' and indid <>'255'
and b.xtype ='U'
order by a.name
go
El resultado te va a dar id de la tabla, id de indice, nombre de indice y fillfactor del mismo.
Espero te sirve,
Abrazo Virtual.
Hola JuanPLG.
Así es, el tema era sacarle el punto al sys.indexes que estaba en el link.
Lo que hice fue lo siguiente:
use DB --Posicionarme en la DB Fragmentada
select name,origfillfactor from sysindexes
--con esto veo el valor del origfillfactor de las tablas
DBCC DBREINDEX (Rotacion_Semanal_Fasa, '', 0);
-- cero era el valor origfillfactor de la tabla
UPDATE STATISTICS Rotacion_Semanal_Fasa WITH FULLSCAN
Hasta aquí todo bien. Pero cuando quise ver si la Fragmentacion de la tabla habia bajado realicé la query DBCC SHOWCONTIG en la DB y la Fragmentacion de la tabla es la misma hasta antes de todo esto.
DBCC SHOWCONTIG scanning 'Rotacion_Semanal_Fasa' table...
Table: 'Rotacion_Semanal_Fasa' (677577452); index ID: 0, database ID: 16
TABLE level scan performed.
- Pages Scanned................................: 17250
- Extents Scanned..............................: 2185
- Extent Switches..............................: 2184
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 98.72% [2157:2185]
- Extent Scan Fragmentation ...................: 99.73%
- Avg. Bytes Free per Page.....................: 275.3
- Avg. Page Density (full).....................: 96.60%
En que estaré mal? o estoy viendo los valores mal?
No estoy entendiendo.
Hola, como estas !, te cuento el valor que debes tener en cuenta, para un tipo de index id = 0 y para un fillfactor o factor de relleno de 0 o bien 100 %, es:
Avg. Page Density (full).....................: 96.60%
Pues el valor por defecto en el motor al crear una tabla es de 0 y que significa esto, que 0 y 100 son lo mismo. Es decir aplica un factor de relleno de datos en las paginas al 100 % o bien trata de dejar un 0% de espacio libre en las mismas.
En tu caso, desde mi punto de vista, estaría bien con el valor 96.60 % pues es el valor próximo al 100 %.
Espero te sirva, un abrazo virtual.
Hola JuanP
Y que significa este valor:
- Extent Scan Fragmentation................... : 99. 74%
A mi entender, es la fragmentacion que tiene la tabla??? Si no me equivoco existen 2 fragmentaciones, una interna o otra externa. Estoy bien?
Este valor me indica, o me da a entender, que tendré una mala respuesta o performance cuando realice una consulta a la tabla?
Si fuese así, como bajo ese valor, o... de nuevo estoy interpretando mal los valores?
Gracias por tus explicaciones y ayudas JuanPLG.
Hola ! como estas !, te cuento es el porcentaje de extend fuera de orden de las 'leaf pages' de un indice.
para el caso planteado no tenes que tenerlo en consideración pues no es relevante para un heap.(index ID: 0)
Table: 'Rotacion_Semanal_Fasa' (677577452); index ID: 0, database ID:
Abrazo virtual,