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.
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.
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.
Comentarios
Saludos JBarahona
From Chile!
Saludos,
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.
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,
Agradezco tu aclaracion,
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,
saludes
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.
me podrias explicar la diferencia entre Modo de recupercion
"sencilla" V/S "completa"
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.
uso sql2005.
pdt.- tendras por ahi un link para una pag con conceptos q no seas el msdn. gracias
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.
pero no hay nada mejor que la propia ayuda de SQL Server, para mí la mejor ayuda sobre plataforma alguna de base de datos.
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
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,
atte.
Jorge Jara
jorgejarah@hotmail.com
Saludos,
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
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.
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
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.
¿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.
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/
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
gracias
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.
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
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.
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
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.
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.
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.
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.
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.
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
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
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.