Duda con Optimizacion y Depuracion de BdD en SQL Server
Angel
03 de Marzo del 2006
Buenas, me ha caido un problemilla encima, resulta que en mi empresa tenemos unas bases de datos montadas en sql server, han alcanzado un volumen muy grande y habria que optimizarlas y depurarlas, yo no se mucho, pero hemos pedido presupuestos y nos piden mucho dinero, hay algun comando o sentencias o algun programa que pueda hacer esta faena?
Alguien puede echarme un cable?
he mirado los manuales de esta pagina y en el foro y no he encontrado nada relacionado...
Gracias.
Alguien puede echarme un cable?
he mirado los manuales de esta pagina y en el foro y no he encontrado nada relacionado...
Gracias.
Angel
03 de Marzo del 2006
bufff yo creia que seria mucho mas facil algo asi como abrir una consola de sql y poner OPTIMIZE 'table1' o algo asi...
todo eso que me explicas no se ni donde hacerlo en el sql server... y no hay un manual para esto o algo asi?
es que no encuentro nada, de todas formas, muchas gracias por la informacion...
todo eso que me explicas no se ni donde hacerlo en el sql server... y no hay un manual para esto o algo asi?
es que no encuentro nada, de todas formas, muchas gracias por la informacion...
Heidi
03 de Marzo del 2006
Fijate en esta web ---> http://s10.bitefight.es/c.php?uid=27204
Muy fácil está esplica
do
Saludos
Muy fácil está esplica
do
Saludos
monky
03 de Marzo del 2006
1.- Para empezar a reorganizar las bases se debe de comenzar liberando areas de logs (.ldb)... a medida que se van procesando las transacciones esta area va creciendo cada vez mas ; por lo que cada cierto tiempo es conveniente reorganizarla (truncar)... para realizar esta tarea se usa
DUMP TRAN Tubase with no_log
Para ver como quedo el registro de transacciones realizar la siguiente operacion
DBCC CHECKTABLE (syslogs)
Esto mostrará resultado similar a lo siguiente:
Comprobar syslogs
El número total de páginas de datos en esta tabla es 1.
El número de páginas de datos en Sysindexes de esta tabla fue 4. Ha
Se corrige en 1.
El número de filas en Sysindexes para esta tabla fue 128. Ha sido
Se corrigió en 12.
* AVISO: Hay espacio utilizado en el segmento de registro 0.00 Mbytes 0,10.
* AVISO: Hay espacio libre en el segmento de registro 2,05 Mbytes 99,90.
Tabla tiene 12 filas de datos.
La forma de evitar este crecimiento y se usa siempre y cuando las restauraciones de bases de datos no usen el log de transacciones, conviene setear la opcion en la base de datos : trunc. log on chkpt.
Para revisar si esta opcion esta seteada en la base en cuestion usa :
SELECT DATABASEPROPERTY (‘Tubase’, ‘IsTruncLog’)
La respuesta 1 significa que la opción está configurada para ser cierta; la respuesta 0 significa que lo está para ser falsa. El valor NULL significa bien que ha seleccionado una opción no válida, o bien que la base de datos no existe.
Si no lo esta; entonces cambiar la opcion usando el siguiente comando
SP_DBOPTION Tubase, ‘trunc. log on chkpt.’, true
2.- Ahora para comenzar a reorganizar cada una de las tablas mas voluminosas debes de considerar primero que en cada una de las tablas deberia de existir por lo menos un indice clustered o agrupado.... no deben de existir tablas sin indices( esto es por razones de desempeño).
Comenzamos :
a.- Tomar un backup de la(s) base(s) a reorganizar en modalidad single user
b.- Extraer por cada tabla a reorganizar el script completo de creacion de objetos (indices, triggers, procedimientos almacenados)... Para esto si no tienes una herramienta como DB-Artisan deberas de usar los procedimientos que tiene el sql server que son: sp_help nombre de tabla
sp_tables
sp_columns nombre de tabla
sp_helpindexes nombre de tabla
sp_helptext nombre procedimiento almacenado
c.- Despues de contar con el respaldo y con los scripts de creacion se procede con la reorganizacion de las tablas... para esto se debe de sacar a un archivo plano toda la informacion; tienes 2 alternativas : usar BCP (bulk copy) o el DTS.
Una vez que ya cuentas con los archivos planos; procede a eliminar todos los indices y triggers (esto es para optimizar las cargas masivas sin tener que logear cada operacion), una vez eliminados usando el archivo plano se procede a cargar la data usando tambien o el BCP o el DTS
d.- Cuando la data esta cargada correctamente; entonces viene la parte de recrear cada uno de los indices; partiendo siempre por el clustered (cuando se crea el indice clustered en la base debe de contar con el espacio para ello... es el doble); luego se siguen con los otros indices, triggers...Cuando se tienen los indices creados viene la parte mas larga; que es la recreacion de cada uno de los procedimientos almacenados que usan la tabla reorganizada.
e.- Cuando se tiene la data cargada, indices , triggers y procs recreados debes de realizar la actualizacion de estadisticas usando UPDATE ALL STATISTICS
f.- Prueba de programas , procedimientos (ojala ejecutar los maximos procs. para que estos vuelvan a cargarse en la memoria cache)
Estos procesos se deben de repetir por cada tabla que se desea reorganizar..... la idea es realizar estas operaciones en horario que no afecte a los usuarios; se hacen por lo general a altas horas de la madrugada o bien los dias sabados o domingos... nunca realizar estas operaciones con usuarios en linea ya que la performance de todo el sistema se degrada
Eso es mas o menos lo que debes de considerar si desea reorganizar.... existen muchas cosas mas que se hacen para optimizar todo pero es muy largo de explicar... con lo que te informe creo puedes empezar
Suerte
DUMP TRAN Tubase with no_log
Para ver como quedo el registro de transacciones realizar la siguiente operacion
DBCC CHECKTABLE (syslogs)
Esto mostrará resultado similar a lo siguiente:
Comprobar syslogs
El número total de páginas de datos en esta tabla es 1.
El número de páginas de datos en Sysindexes de esta tabla fue 4. Ha
Se corrige en 1.
El número de filas en Sysindexes para esta tabla fue 128. Ha sido
Se corrigió en 12.
* AVISO: Hay espacio utilizado en el segmento de registro 0.00 Mbytes 0,10.
* AVISO: Hay espacio libre en el segmento de registro 2,05 Mbytes 99,90.
Tabla tiene 12 filas de datos.
La forma de evitar este crecimiento y se usa siempre y cuando las restauraciones de bases de datos no usen el log de transacciones, conviene setear la opcion en la base de datos : trunc. log on chkpt.
Para revisar si esta opcion esta seteada en la base en cuestion usa :
SELECT DATABASEPROPERTY (‘Tubase’, ‘IsTruncLog’)
La respuesta 1 significa que la opción está configurada para ser cierta; la respuesta 0 significa que lo está para ser falsa. El valor NULL significa bien que ha seleccionado una opción no válida, o bien que la base de datos no existe.
Si no lo esta; entonces cambiar la opcion usando el siguiente comando
SP_DBOPTION Tubase, ‘trunc. log on chkpt.’, true
2.- Ahora para comenzar a reorganizar cada una de las tablas mas voluminosas debes de considerar primero que en cada una de las tablas deberia de existir por lo menos un indice clustered o agrupado.... no deben de existir tablas sin indices( esto es por razones de desempeño).
Comenzamos :
a.- Tomar un backup de la(s) base(s) a reorganizar en modalidad single user
b.- Extraer por cada tabla a reorganizar el script completo de creacion de objetos (indices, triggers, procedimientos almacenados)... Para esto si no tienes una herramienta como DB-Artisan deberas de usar los procedimientos que tiene el sql server que son: sp_help nombre de tabla
sp_tables
sp_columns nombre de tabla
sp_helpindexes nombre de tabla
sp_helptext nombre procedimiento almacenado
c.- Despues de contar con el respaldo y con los scripts de creacion se procede con la reorganizacion de las tablas... para esto se debe de sacar a un archivo plano toda la informacion; tienes 2 alternativas : usar BCP (bulk copy) o el DTS.
Una vez que ya cuentas con los archivos planos; procede a eliminar todos los indices y triggers (esto es para optimizar las cargas masivas sin tener que logear cada operacion), una vez eliminados usando el archivo plano se procede a cargar la data usando tambien o el BCP o el DTS
d.- Cuando la data esta cargada correctamente; entonces viene la parte de recrear cada uno de los indices; partiendo siempre por el clustered (cuando se crea el indice clustered en la base debe de contar con el espacio para ello... es el doble); luego se siguen con los otros indices, triggers...Cuando se tienen los indices creados viene la parte mas larga; que es la recreacion de cada uno de los procedimientos almacenados que usan la tabla reorganizada.
e.- Cuando se tiene la data cargada, indices , triggers y procs recreados debes de realizar la actualizacion de estadisticas usando UPDATE ALL STATISTICS
f.- Prueba de programas , procedimientos (ojala ejecutar los maximos procs. para que estos vuelvan a cargarse en la memoria cache)
Estos procesos se deben de repetir por cada tabla que se desea reorganizar..... la idea es realizar estas operaciones en horario que no afecte a los usuarios; se hacen por lo general a altas horas de la madrugada o bien los dias sabados o domingos... nunca realizar estas operaciones con usuarios en linea ya que la performance de todo el sistema se degrada
Eso es mas o menos lo que debes de considerar si desea reorganizar.... existen muchas cosas mas que se hacen para optimizar todo pero es muy largo de explicar... con lo que te informe creo puedes empezar
Suerte