lunes, 8 de octubre de 2007

Mejorar rendimiento de SQL Server 2000

En este artículo les voy a dejar algunos tips para poder mejorar el rendimiento de nuestro SQL Server 2000, para aquellos administradores que tengan problemas con su motor de base de datos.
Una de las primeras Opciones que vamos a tomar para mejorar el rendiemiento de nuestro SQL Server 2000 es desactivar las opciones de auto update stadistics y auto create stadistic, ya que esto demanda un mayor rendimiento del servidor, esto también funciona para sql 2005. Otra opción que nos puede ayudar es tener desactivado la opción de auto shink que viene por defecto inactiva.

Una muy buena opción es crear filegroup especiales para las tablas que ocupan mucho espacio en nuestros discos y cada uno de los indices que componen la tabla. Para esto debemos elegir propiedades de nuestra base de datos y luego la pestaña filegroup, donde creamos los filegroup deseados y pulsamos el botón Ok.
Luego debemos elegir nuevamente propiedades y seleccionar la pestaña data files donde le crearemos un archivo o device y asignarle el filegroup creado con anterioridad.

Luego debemos asignarle a la tabla el filegroup creado para la tabla, si no se le ha asignado un device al filegroup le va a salir un mensaje que no tiene archivos asignados.



Otra medida para mejorar el rendimiento es colocar el transaction log en un disco separado del archivo de data de nuestra base de datos y en caso de tener solo un disco, en una partición o carpeta independiente a la de data. Otra recomendación es asignar el espacio de crecimiento de los devices fijos en megabytes en vez de porciento para que de esta forma el motor de base de datos no esté solitando crecer cada vez que se inserten o realicen varias consultas.


En en table filegroup se asigna el nombre del filegroup al que va a pertenecer nuestra tabla, por defecto se le asigna PRIMARY que es el default de la base de datos y es donde se almacena la configuración del servidor.


También es bueno contar con un buen esquema de base de datos es relacionar las tablas de la base de datos mediante campos de valores numericos o uniqueidentifier, no se recomienda que se creen llaves con campos char o varchar ya que hacen que las consultas sean más lentas.


Todos los filegroups se le pueden asignar uno o varios devices que se presentan en la columna files, si no tiene archivos asignados la columna files aparece en 0.
Espero que estas opciones le sirvan para un futuro, cualquier inquietud me dejan un comentario con su correo y se le responderan su inquietud.




46 comentarios:

Anónimo dijo...

Excelente ayuda. Gracias por aportar en la web.

Saludos JBarahona
From Chile!

Michael Núñez dijo...

Espero haberte ayudado y cualquier inquietud no temas preguntar que ese es el objetivo, compartir conocimientos.

Saludos,

Anónimo dijo...

Hola que tal es un gusto.
Le tengo que molestar con lo siguiente, tengo el VS 2005 Standar Edition y estube haciendo un curso de VB .Net 2005. Tenía ya desde antes instalado el MSSQL Server 2000 en mi equipo, (aclaro: no tengo un servidor dedicado), Instalo el VS no tube drama alguno; pero a la hora de utilizar la herramienta con el MSSQL Server 2000, comencé con problemas. Tengo la DB para mis prácticas e ir provando las distintas formas de conección a la misma desde el VB .Net; pero por alguna razón la DB después de provar conección y hacer una serie de pruevas me quedaba en estado "Sospechoso (Suspect)" ... Busqué en más un tutorial la forma de recuperarla y cambiar el estado, con el sp_resetstatus", pero no pude devolverla a su estado anterior para que quede de nuevo disponible , (le comento inteté todo pero con resultados negativos). Bueno, desinstalé el motor y quiero volver a instalarlo; para esto me conseguí una buena ayuda y voy a intentar de nuevo instalar el motor, mí inquietud es por si me sucede lo mismo, de que forma puedo correr el SP que usted expone en su blog, por razones de inexperiencia me podría guiar como para ejecutarlo si me pasa de nuevo lo mismo??? ... y al instalar el MSSQL 2000 que tengo que tener en cuenta en la configuración del motor si lo voy a tener de forma local por así decirlo ??? ... La última ... Desde el VB .Net, ya que desde la herramienta puedo crear DB, SP, Vistas, etc. que tengo que configurar, toquetear, marcar o lo que sea para no tener este problema.
Muchas Gracias.
Saludos cordiales.
Pablo Dante de Argentina

PD: Mi email por si no se entendió nada de lo que le comento : appabper@hotmail.com.

Michael Núñez dijo...

Saludos Pablo,

La situación por la que pasaste es un poco molesta, pero el problema que tenía no es del motor de base de datos, a veces por problemas de apagado del equipo es que suceden estos contratiempo, ya que se apaga el equipo sin detenerse el motor de la base de datos. Sobre la primera pregunta que me haces, el Store Procedure lo ejecutas desde la base de datos master con privilegios de administrador.

Lo de instalar el SQL lo más importante es tener el cuenta que te permita loguear con Windows authentication y sql aunthentication para así tener acceso de cualquier forma.

Sobre crear procedures, vistas y base de datos con Visual Studio, debes hacerlo con SQL Server 2005 para integrarlo todo bajo la misma plataforma, porque tendrás que migrar tu base de datos a una versión anterior y posiblemente no te funcione correctamente. Además, SQL Server express edition 2005 te permite realizar tantas cosas como las demás y es completamente gratis y te permite hasta 1 GB de almacenamiento de datos que no está mal para comenzar.

Espero esto te sirva,

Mario dijo...

Buenas tardes Guru, mi pregunta es ¿porque colocar el transaction log en un disco separado del archivo data del base de datos?

Agradezco tu aclaracion,

Mario

Michael Núñez dijo...

Saludos Mario,

Muy buena pregunta la que me haces, si tu base de datos es pequeña no existe ningún problema de tenerla en el mismo disco o ruta; pero cuando las base de datos son muy grandes y se encuentran en el mismo disco, el transaction log comienza a competir con los archivos de datos e indices de la base de datos, bloqueando mucha veces el motor.

Además, esto forzaría más el disco donde se encuentra corriendo disminuyendo el rendimiento de la misma.

Espero te sirva,

Mario dijo...

Buenos dias Guru, no me podrias facilitar o recomendar alguna herramienta a parte del Profiler, para medir el rendimiento del motor de Base Datos Sql Server 2000, para tener un analisis mas cercano del comportamiento de mi Bd.

saludes

Mario

Michael Núñez dijo...

Saludos Mario,

Realmente no he utilizado otra herramienta para monitorear y darle seguimiento a las Transacciones de Base de datos, esto porque Esta herramienta me ha brindado lo que necesito.

Anónimo dijo...

Michel:

me podrias explicar la diferencia entre Modo de recupercion
"sencilla" V/S "completa"

Michael Núñez dijo...

Saludos,

Si queremos recuperar un backup completo incluyendo su log de transacciones debemos colocar el modo de recuperación Full, de lo contrario colocamos simple. Esto porque en modo Full se guarda el log en los backups de la base de datos, mientras que en el simple no se hace.

Si no es importante para tu negocio tener todas las transacciones, puedes dejar tus base de datos en simple y así puedes reducir el tamaño de tus backups.

Michael Núñez dijo...

Otra cosa, al momento de recuperar la base de datos en caso de un problema con el modo simple solo puedes recuperar el fin del Log.

Anónimo dijo...

muchas gracias por la pronta respuesta.

cesar cueto dijo...

Guru, tengo particionado mis tablas, y al file group de datos antiguos los puse en readonly para que no sean modificados, pero cuando quiero actualizar las actual, no me deja actualizar.
uso sql2005.

Igor Armando dijo...

hola disculpa q te incomode, soy estudiante y leyendo por ahi siempre mencionan a los filegroups, me podrias decir q son? enq mejoran a la base de datos y cual es la diferencia de mdf y ndf.
pdt.- tendras por ahi un link para una pag con conceptos q no seas el msdn. gracias

Michael Núñez dijo...

Saludos Igor,

No te preocupes, es una pregunta que muchos administradores de base de datos no te podrían responder porque nunca le prestan atención a las interioridades.

Dejame decirte que SQL crea un archivo primario para depositar la información de la base de datos que casi siempre es nombrebasededatos.mdf y está asociado a un filegroup llamado PRIMARY.

Cuando creamos varios filegroup para segmentar la información debemos asociar estos filegroup a nuevos devices (archivos secundarios que llevan la extensión .ndf), es decir, estos archivos contienen información secundaria de la base de datos y nos permiten segmentarla para así optimizar la velocidad de procesos.

Un tercer tipo de archivo es el .ldf que guarda el log de la base de datos.

Michael Núñez dijo...

Sobre una página buena de SQL te puedo enviar a: http://www.sqlservercentral.com/

pero no hay nada mejor que la propia ayuda de SQL Server, para mí la mejor ayuda sobre plataforma alguna de base de datos.

Anónimo dijo...

Estimado:

existe alguna forma de generar un log de un Store Procedure?? Es decir, por cada sentencia o actividad que haga, lo registre en un log???
SI me pueden responder a hugofmonteros@yahoo.es

Gracias

Michael Núñez dijo...

Saludos Hugo,

Realmente no creo que eso se pueda hacer mediante alguna configuración del Motor de Base de datos, a menos que programes algo por tu cuenta utilizando como base el sp_trace_create o dejando el store procedure dentro de un job que si guarda un log de ejecución.

Espero eso te sirva,

Anónimo dijo...

que seguridad tiene crear filegroups, por ejemplo, puede reventar el archivo ndf y es posible rescatarlo, mi base no es muy grande 600 MB, y se crea una tabla mensual ya que la cantidad es muy alta, puedo confiar en los filegroup para crear mis tablas mensuales y dejarlas ahi....

atte.
Jorge Jara
jorgejarah@hotmail.com

Michael Núñez dijo...

Sobre la seguridad de los filegrooup, te puedo decir que si es posible rescatar un archivo .ndf que se encuentre corrupto, pero el beneficio de los filegroup es dividir la información para mejorar el rendimiento del servidor. Pero, como he explicado anteriormente, el filegroup funciona si tienes otros discos donde puedes poner a apuntar el devices asociado al filegroup.

Saludos,

Anónimo dijo...

Gracias por la respuesta reapida,
eso significa que mientras mas filegroup tenga mas discos debe tener mi server...?

otra consulta cuantas tablas puedo unir a un filegroup, mira lo estoy probando y enganche una tabla sin ningun problema, intento con otra y reviso las propiedades de la tabla y me muestra que aun esta en PRIMARY, que puede suceder?,
Muchas gracias nuevamente...

atte.
Jorge Jara

Michael Núñez dijo...

Saludos Jorge,

No tienes que tener un disco por cada filegroup, solo lo digo que es lo más óptimo, por ejemplo tener todas las tabla apuntado a un disco, los indices a otro y el transaction log a otro disco, eso optimiza tu servidor.

Y puedes tener cuantas tablas desees, pero tienes que tener un devices asignado al filegroup, es decir, un espacio en disco.

Anónimo dijo...

Estimado:

Ahora poco instale donde un cliente windows server 2003 con sql server 2000 enterprise y resulta que las consultas se me hacen muy lentas a veces hasta las direccionales se vuelven lentas en mi aplicacion.

Me podrias recomendar algo ?

Saludos y Gracias de Antemano

Michael Núñez dijo...

Hola,

Gracias por escribirme, para responderte esa pregunta necesitaría tener mas información sobre el servidor, el tamaño de la base de datos, como esta configurado la localización de los archivos.

Grupo dijo...

Hola Ely,
¿Quisiera que me explicarás un poco más el porque no debemos de tener activado auto update stadistics?, lo que pasa que en la empresa estamos teniendo problemas con el sistema ya que en muchas ocasiones esta muy lento sinceramente desde que se instalo el sistema y el motor de base de datos SQL 2000 no se le ha dado mantenimiento aparte que igual investigando mas a fondo me he encontrado con articulos que nos dicen que el nombre de un procedimiento almacenado no deben empezar con el prefijo sp porque el sistema lo toma como un sp del sistema e igual lei en tu articulo que los tipos de datos char y varchar vuelven las consultas lentas y la mayoria de los campos claves lo tiene en char, considero que todo lo que ten menciono es un granito de arena a todos los problemas que estamos teniendo. Pero queremos empezar en aprender como administrar una base de datos,¿Sabras de una liga en la que me pueda apoyar? como te dije he leido pero siempre me encuentro con foros que me dices que tal funcion es buena y otros que no y es cuando me quedo con más dudas. La verdad por lo que he leido en tu blog se vé que sabes del tema y quisiera que me recomendarás en cual liga me apoyo..Saludos desde México.
Ivonne.

Michael Núñez dijo...

Hola Ivonne,

Gracias por visitar nuestro blog, voy a tratar de responder algunas de tus preguntas poco a poco.

Sobre lo de apagar el auto update statistic: Esto es para que SQL no utilice tiempo de procesamiento para calcular la cantidad de registros ejecutados en una transacción, registros afectados por cada transacción, entre otras estadísticas llevadas a cabo por el motor de base de datos.

Sobre lo de sp_ delante de los procedures: He escuchado eso, hay personas que prefieren usar el proc_ antes de cada procedimiento. No creo que eso afecte tanto la ejecución de una determinada aplicación.

Campos claves con char y varchar: eso es un problema y grave, eso hace que las búsquedas sean bastante lentas, yo prefería utilizar datos integer y de tipos identities, ya que se son mucho más eficiente al momento de hacer las relaciones entre tablas.

Sobre un enlace que te pueda ayudar:

http://msdn.microsoft.com/en-us/library/ms998577.aspx
http://www.sql-server-performance.com/

Grupo dijo...

Gracias por contestar mis dudas voy a leer las ligas que me enviaste..saludos desde México Ivonne.

Alex dijo...

He escuchado sobre un programa. El ha sido en un sitio con los softwares - reparar base de datos sql server. El programa ha terminado mis problemas muy con facilidad y de balde. Todavia sobre este programa mis amigos han estado contento de este utilidad. Ellos han dicho que con ayuda del instrumento ha economizado el tiempo. El utilidad ha demostrado sus posibilidades en guardar los datos de salidas como un solo archivo .pst o como un grupo de archivos .eml, .vcf y .txt.

Anónimo dijo...

Muchas Gracias, por compartir conocimientos... Bendiciones.

Anónimo dijo...

Estimado:

En un servidor Intel(R) Xeon(R) CPU E5530 @ 240 GHz, con 6GB de RAM , tengo instalado sql server 2000 Enterprise edition y las direccionales de mi aplicacion se vuelven lentas.

Favor que puede estar sucediendo.

Gracias de antemano

Michael Núñez dijo...

Que te puedo decir, tienes que verificar de arriba a abajo lo que está pasando, colocar Profilers, Plan execution para los queries, ver como se comportan tus discos y procesados en determinadas ejecuciones, porque si el software y la base de datos están mal diseñados, por más potencia que le pongas a tu servidor, vas a tener el mismo problema.

Anónimo dijo...

Hola que tal, tengo un problema haber si me puedes orientar, tengo un servidor HP con discos duros SAS con arreglos de raid 1 para la bd, y en otro arreglo con otros discos en Raid 1 tengo el translog, bueno opera de manera normal pero durente el dia se degrada el rendimiento de las consultas (ya sea por query analizer o por pantallas) y exiten bloqueos. pero aunque no haya bloqueos tambien se arranan las consultas, corro semanalmente, el plan de mantenimiento pero no mejora, cuando esta lento todo el sistema como lo indican mis usuarios me conecto al servidor y no esta ni siquiera al 30 % de capacidad nunca pasa de ahi, cabe mencionar que mi bd mide 130 gb, y tengo tablas muy grandes de millones de registros, la operacion de mi negocio es estilo bancaria por lo que tengo miles de clientes de clientes activos, pero lo que se me hace raro es que cuando no esta lento esas tablas enormes responden perfectamente, porque pense que se trataba de que necesitaba "rasurar" registros, he cuidado que cuando se hacen consultas siempre poner el nolock para evitar bloqueos en una simple consulta, pero no veo como solucionarlo, crees puedas ayudarme
gracias

Michael Núñez dijo...

Hola,

Sería bueno que verifiques y des seguimiento a las consultas que te están ocasionando los bloqueos en tu base de datos, ya que puedes estar utilizando los nolock, pero si las condiciones de las consultas no tienen los índices adecuados o la cantidad de campos que poseen tus tablas son sobre las 30 columnas, puede tener este tipo de problemas.

Por lo que te recomiendo que verifiques los índices de tu tablas y si estos tienen el mismo orden de las condiciones de tus consultas.

Wilmer Ramos dijo...

Buenas Noches Guru,

queria hacerte la siguiente consulta, tengo un Servidor de Aplicaciones Web que se conecta a mi Server de BD para realizar las operaciones, esta ultima semana he encontrados bloqueos en el server, con el sp_who2 he identificado el ID del proceso, pero cuando quiero ver el detalle Dbcc inputbuffer() no se muestra el detalle de ejecucion, solo me muestra RPC event, cursoropen, ¿como puedo ver el detalle de la instruccion SQL que se esta ejecutando para poder mejorarlo?

muchas gracias por anticipado

Michael Núñez dijo...

Buenos dias Wilmer,

Al parecer tu problema está siendo ocasionado por la corrida de algún cursor que está corriendo en tu base de datos, es un poco complicado darle seguimiento a un cursor luego que comienza a ejecutarse con sp_who2 y dbcc inputbuffer, te recomiendo que tomes el tiempo donde se presentan esos casos e intentes con el sql profiler y le corras un trace.

Aunque muchas personas no utilicen la sentencia nolock porque pueden traer registros sucios, muchas veces es preferible utilizarlos para evitar estos contratiempos.

Javier dijo...

Buenos dias Guru

Sabes tengo un problema al ejecutar un .exe desde mi DTS, antes de ejecutar el .exe en mi dts tengo unas tareas que insertan unos registros que depsues el .exe debe leer y este se me cae por que no encuentro que haya insertado los registros, sin embargo si ejecuto el dts por partes y en el momento de ejecutar el .exe no lo hago desde el dts sino que lo ejecuto desde el ejecutable original este funciona correctamente y lee los registros y hace todo bien. creo que necesito permiso o algo para que ese ejecutable funcione bien dentro del entorno del DTS. Espero puedas orientarme, muchos saludos y gracias

Michael Núñez dijo...

Hola Javier, no me gusta mucho llamar ejecutables desde la base de datos, y mucho menos de un DTS, ya que aunque es posible, los fines con que se desarrollaron no es para ellos. Para ejecutar la aplicación puedes ver este enlace http://consejosdelguru.blogspot.com/2007/11/como-ejecutar-una-aplicacin-desde-sql.html donde he tocado bastante este tema.

Eddycinho dijo...

Hola Guru, estoy en una encrusijada y espero puedas ayudarme, pasa que estamos definiendo la arquitectur y la forma de hacer las consultas a la BD pero estos datos son de una ampresa que por año las tablas crecen a razon del millon de registros..van apenas año y medio y tenemos millon y medio hasta ahora..y claro aumentara mucho mas..y kisiera saber una mejor y optima forma de hacer un select por ejemplo voy a buscar o hacer una consulta y quiero mostrar una bandeja con campos los cuales vienen de distintas tablas y estan amarradas por el Id, estoy haciendo agunas pruebas con indices y demoran mucho..en un par de años la busqueda se hara con un dia de anticipacion..Help..gracias

Michael Núñez dijo...

Lo primordial es que tengas indices de acuerdo al orden de los campos a usar en el select. Por ejemplo, si vas usar un id y una fecha, es necesario que Los indices tengan see mismo orden.

Otro punto, es separar Los devices de Los indices de Los de la data, si es possible en unidades diferentes, logica como fisica. Esto hace la busquedas mas eficiente.

Por ultimo, es preferible utilizer los ids de tipo numericos, ya que hacen las relaciones mas eficientes.

Anónimo dijo...

Buen dia Guru.

Desde ya agradesco tus respuestas a mi inquietud.
Estoy administrando una bd en SQL SERVER 2005, corriendo sobre Windows Server 2033. El sistema(desarrollado en Fox Pro) de la empresa se conecta a la bd por medio de ODBC.
Actualmente estoy desarrollando una aplicacionen en VB.NET 2010, lo cual me conecto mediante una cadena de conexion. El problema es el sqte, cuando mi programa esta actualizando miles de registros en hora punta, el sistema de la empresa, demora en responder a las consultas.
...Que puedo hacer yo, para que el tiempo de procesamiento y de respuesta de la bd, se optimice su rendimiento??, que herramientas utilizaria para manejar las estadisticas de las conexiones.

Michael Núñez dijo...

Hola,

Disculpa la demora en responder, he estado un poco ocupado. Para esas verificaciones tiendo a almacenar directamente un log en algún archivo txt usando Tickcount en .Net y así saber que tiempo dura cada ejecución. Puedes usar sp_who2 y el profiler para ver más o menos que sucede.

Otro punto para mejorar, si estás haciendo update masivos trata de hacerlo registro por registro o por pequeños batches de registro.

Espero te ayude.

Anónimo dijo...

Buenas,
tengo desarrollada una aplicación ADP de Access 2002 contra un servidor SQL SERVER 2000 en un servidor dedicado.
La base de datos es de unas 170 tablas y 1 GB de tamaño más o menos.
Acceden entorno a 30 usuarios.
Estamos teniendo muchos problemas de velocidad de ejecución. En principio todo está correcto. No se donde puede estar el cuello de botella.


Saludos desde España y muchas gracias por tu dedicación.

Michael Núñez dijo...

Saludos,

170 tablas son muchas para cualquier sistema y si tienes muchas tablas enlazadas y poca memoria o CPU en tu servidor por ahí puede andar tu problema.

prueba ejecutando el sp_who2 si puedes acceder desde un query analyzer y trata de ejecutar un trace con el sql profiler para que puedas ver los tiempos de ejecución.

Luego que tengas las consultas con los mayores tiempos, puedes en el query analyzer ejecutar el diplay execution para ver donde es que hay un mayor costo de uso de CPU.

Anónimo dijo...

Muchas gracias por la pronta respuesta.
El servidor es un pentium D a 3.4GHz y con 2GB de RAM y 130gb de hd libre. Y analizando el uso del CPU mientras trabaja el proceso sqlmanager.exe tiene un uso de memoria de 1.300.000 kb aproximadamente y el uso de CPU es entorno al 50-60%.

Qué configuración de servidor puede ir bien para esta aplicación?

Saludos desde España

Anónimo dijo...

Tambien comentar que el servidor está bajo Windows Server 2003.
No sé si los proyectos .ADP de Access 2002 son la plataforma más adecuada para estas aplicaciones.

¿Con qué herramientas habituales se crean las aplicaciones Cliente?

Saludos de nuevo y gracias

Michael Núñez dijo...

Al parecer el equipo que tienes puede dar un buen renfimiento, por lo que el problema puede estaren el diseno de la base de datos y con el crecimiento de la misma ha provocado que la misma se vuelva mas lenta.

Sobre la herramienta, yo uso Visual Basic y C#de microsoft y las versiones express son gratis, pero yo soy desarrollador y facilita un poco las cosas.