Categoría: MSSQL Administration

Estrategias para optimizar consultas en SQL Server

En el desarrollo de bases de datos, la optimización de consultas es una tarea fundamental para garantizar un buen rendimiento del sistema. En este artículo, exploraremos algunas estrategias prácticas para optimizar consultas en SQL Server.

Cuando se trabaja con bases de datos SQL Server, es común encontrarse con consultas que no están optimizadas, lo que puede resultar en un rendimiento deficiente del sistema. La optimización de consultas implica identificar y resolver cuellos de botella en el rendimiento de las consultas SQL, lo que a su vez mejora la eficiencia y la velocidad de ejecución.

El primer paso en el proceso de optimización de consultas es identificar aquellas consultas que están afectando el rendimiento del sistema. Esto se puede lograr mediante el monitoreo del rendimiento del servidor SQL Server y la identificación de consultas que consumen una cantidad desproporcionada de recursos. Para esto se pueden usar muchas herramientas, en el pasado compartí algunos ejemplos de consultas para monitorear procedimientos almacenados que en realidad funcionan practicamente para cualquier tipo de codigo. Cuando busco consultas que estan dandome problemas habitualmente uso el sp_who2 o bien el sp_whoisactive de Adam Machinic, que a pesar de tener rato de no recibir actualizaciones aún es muy útil.

Una vez identificadas las consultas lentas, es importante analizar los planes de ejecución para entender cómo se están ejecutando esas consultas. El plan de ejecución es una representación visual del proceso que SQL Server sigue para ejecutar una consulta, y puede proporcionar información valiosa sobre posibles cuellos de botella y áreas de mejora.

Los índices son una herramienta fundamental para optimizar consultas en SQL Server. Al crear y mantener índices adecuados, es posible mejorar significativamente el rendimiento de las consultas al permitir un acceso más rápido a los datos.

Una herramienta muy util es el utilizar la visualización de las estadísticas de ejecución con encendiendo el set statistics io, time on;

Acá podemos ver cuáles son las tablas que estamos leyendo mas, que tipo de lectura estamos haciendo.

El diseño del esquema de la base de datos también juega un papel crucial en la optimización de consultas. Al diseñar tablas y relaciones de manera eficiente, es posible minimizar el tiempo de búsqueda y recuperación de datos, lo que contribuye a un mejor rendimiento del sistema en su conjunto.

La optimización de consultas en SQL Server es un proceso continuo que requiere un enfoque proactivo y constante. Al implementar estrategias como la identificación de consultas lentas, el análisis de planes de ejecución y el uso eficiente de índices, es posible mejorar significativamente el rendimiento del sistema y proporcionar una experiencia más rápida y eficiente a los usuarios finales.

Beneficios del Particionamiento Nativo en SQL Server

En mi experiencia profesional he utilizado diferentes sabores de particionamiento en MSSQL, algunas versiones hechas en casa, y el particionamiento nativo. Este último lo hemos usado aún más desde que está disponible para la version estándar del motor.

El particionamiento nativo en SQL Server es una característica poderosa que permite dividir grandes tablas y sus índices en objetos de base datos más pequeños y manejables. Estas particiones se almacenan y administran de forma independiente, lo que proporciona una serie de beneficios significativos tales como:

  1. Mejora del rendimiento de queries: El particionamiento permite distribuir la carga de trabajo entre múltiples particiones, lo que facilita la ejecución paralela de consultas. Esto puede resultar en un mejor rendimiento, ya que se pueden utilizar recursos de hardware de manera más eficiente para procesar consultas en paralelo.
  2. Operaciones de mantenimiento: El particionamiento facilita el mantenimiento y la administración de grandes tablas. Se puede realizar operaciones de mantenimiento, como la reorganización de índices y la actualización de estadísticas, de forma más rápida y específica en particiones individuales, en lugar de tener que aplicar estas operaciones en toda la tabla.
  3. Optimización del almacenamiento: El particionamiento nativo puede ayudar a optimizar el almacenamiento. Puedes colocar particiones más activas o frecuentemente accedidas en unidades de almacenamiento más rápidas y de mayor rendimiento, mientras que las particiones menos activas o históricas se pueden almacenar en unidades más lentas y de menor costo.
  4. Mejora de la escalabilidad: El particionamiento permite escalar verticalmente (agregar más recursos a un servidor) y horizontalmente (distribuir particiones en servidores diferentes).

Podemos concluir que el particionamiento nativo en SQL Server nos brinda mejoras en el rendimiento, mantenimiento, disponibilidad, optimización de almacenamiento y escalabilidad.

Como insertar filas en una tabla con identity?

A veces se ocupa poder insertar filas que se traen de una estructura vieja y hemos definido con anterioridad una columna con un identity. En estas ocasiones se hace necesario deshabilitar el comportamiento del identity.

Para lograrlo tenemos que hacer lo siguiente.

  1. Ejecutar el comando [SET IDENTITY_INSERT dbo.MiTabla ON;]
  2. Ejecutar un insert into, en este caso hago enfasis en que es necesario enumerar el nombre de la columna tanto en el insert como en la seccion del select.
SET IDENTITY_INSERT dbo.MiTabla ON;
GO
Insert into MiTabla (ID, Col1, Col2)
select ID, Col1, Col2
from MiTabla_old
where id> 1000
GO
SET IDENTITY_INSERT dbo.MiTabla OFF;

Es importante rescatar que cuando inserto estas nuevas filas el valor del identity se modificara a valor mas alto insertado.

Para validar el valor actual de la columna identity solamente necesitamos ejecutar el comando [DBCC CHECKIDENT], como se aprecia en el próximo ejemplo.

DBCC CHECKIDENT('MiTabla', NORESEED)

Como renombrar una instancia de SQL

Esto es algo que realmente que uso poco, pero ciertamente me ha sido bastante util en ambientes de pruebas cuando el nombre de maquina difiere del nombre de instancia y por alguna razon en particular ocupo que sean el mismo.

Antes de renombrar tenemos que descartar lo siguiente:

  1. No usamos replicacion
  2. No existen logins de acceso remoto

El codigo de la siguiente seccion nos permitira validar estas dos condiciones:

DECLARE @txt VARCHAR(512)

SET @txt = ''

IF EXISTS(
SELECT *
FROM sys.databases
where is_published = 1 or is_subscribed =1 or is_distributor = 1
)
BEGIN
SET @txt = 'No se podra renombrar por el momento, Existe Replicacion'
END

IF EXISTS(
select *
from sys.remote_logins a
INNER JOIN sys.sysservers b on b.srvid = a.server_id
)
BEGIN
IF(@txt = '')
BEGIN
SET @txt = 'No se podra renombrar por el momento, existen [Remote Logins]'
END
ELSE
BEGIN
SET @txt = '
No se podra renombrar por el momento, existen [Remote Logins]'
END
END

IF @txt = ''
BEGIN
SET @txt = 'No existen problemas para ejecutar el sp_dropserver'
END
Print @txt

A continuacion lo que hay que hacer es buscar el nombre actual de la maquina. Lo que siempre hago es buscarlo abriendo una ventana del command prompt, y utilizando el comando hostname.

Solo quedaria correr el sp_dropserver y el sp_addserver

<span 				data-mce-type="bookmark" 				id="mce_SELREST_start" 				data-mce-style="overflow:hidden;line-height:0" 				style="overflow:hidden;line-height:0" 			></span>
DECLARE @oldname sysname
DECLARE @newname sysname

SET @oldname = 'server'
SET @newname = 'newserver'

EXEC sp_dropserver
@server = @oldname
--nombre de la instancia, tipo sysname, no acepta espacios
--@droplogins = ''
--Permite Remover los Remote Logins de la insntancia

EXEC sp_addserver
@server = @newname
--nombre de la instancia, tipo sysname, no acepta espacios
,@local = local
--indica si la insntacia es local, varchar(1)
--Cuando se asigna LOCAL le indica al SQL que el nombre
--de instacia es lo que le enviamos a @server
--,@duplicate_ok =
--Permite nombres de instancia duplicados
--Solo tiene to valores posibles duplicate_OK o NULL
--Nunca lo he usado
GO

Nota: en caso de renombrar una instancia nombrada, cambiamos el los valores
@oldname y @newname incluyendo el nombre de la maquina y el de la instancia de la siguiente manera:

SET @oldname = 'server\instance'
SET @newname = 'server\newinstance'

El nombre del servidor tambien puede cambiarse

Fuente:
MS Drop Server
MS addServer

Cuando fue la ultima que se uso una tabla?

Hace rato que no escribia, he andado como siempre en mucha cosa. Estoy jugando un poco con PostGreSQL y Azure, seguramente empezare a incluir esa informacion en futuras entradas a este blog.

La respuesta  a la pregunta del titulo me ha servido muchas veces como administrador de una base de datos a lo largo de los años.

Esta consulta esta basada en una vista de systema que analiza el uso de los indices de nuestras tablas.

sys.dm_db_index_usage_stats

SELECT  groupname = @groupname, casinoname = @casinoname, DB_NAME(a.database_id) Database_Name ,
        b.name Table_Name ,
        MAX(ISNULL(last_user_update,'2001-01-01')) last_user_update ,
        MAX(ISNULL(last_user_seek,'2001-01-01')) last_user_seek ,
        MAX(ISNULL(last_user_scan,'2001-01-01')) last_user_scan ,
        MAX(ISNULL(last_user_lookup,'2001-01-01')) last_user_lookup
FROM    sys.dm_db_index_usage_stats a
        INNER JOIN sys.tables b ON b.object_id = a.object_id
		INNER JOIN sys.indexes c ON c.object_id = a.object_id
                                    AND c.index_id = a.index_id
WHERE b.name = 'playerbalances'
GROUP BY a.database_id ,
        b.name
ORDER BY a.database_id ,
        b.name ;<span 				data-mce-type="bookmark" 				id="mce_SELREST_start" 				data-mce-style="overflow:hidden;line-height:0" 				style="overflow:hidden;line-height:0" 			></span>

Utilizando este mismo codigo podemos determinar cuando fue la ultima vez que se utilizo uno de nuestros indices

SELECT  DB_NAME(a.database_id) Database_Name ,
        b.name Table_Name ,
        c.name Index_Name ,
        MAX(ISNULL(last_user_update,'2001-01-01')) last_user_update ,
        MAX(ISNULL(last_user_seek,'2001-01-01')) last_user_seek ,
        MAX(ISNULL(last_user_scan,'2001-01-01')) last_user_scan ,
        MAX(ISNULL(last_user_lookup,'2001-01-01')) last_user_lookup
FROM    sys.dm_db_index_usage_stats a
        INNER JOIN sys.tables b ON b.object_id = a.object_id
        INNER JOIN sys.indexes c ON c.object_id = a.object_id
                                    AND c.index_id = a.index_id
WHERE   a.database_id = DB_ID()
GROUP BY a.database_id ,
        b.name ,
        c.name
ORDER BY a.database_id ,
        b.name ,
        c.name;

Toda pieza de informacion es valiosa, en este caso puede ayudarme a ver si mi politica de creacion de indices esta sirviendo o no, puede indicarme cuales tablas tienen mas trafico y ayudarme a tomar decisiones para mejorar un problema de contencion.

Saludos

Referencia:
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-usage-stats-transact-sql

SQL Server Configuration Manager — Cannot Connect to VMI Provider

Esto es algo que esta muy documentado, hay notas de soporte de Microsoft, otras fuentes también han hablado de lo mismo, pero siempre esta en ingles.
Ocurre cuando vas a abrir el SQL Server Configuration Manager en versiones superiores  a 2005, honestamente solo me ha pasado este error en 2008 y superiores.
Es algo molesto, en realidad el utilitario es bastante util y te permite hacer tu trabajo mas rápidamente que en consola si no tienes un script listo con todo lo que ocupas.

managementconsole_error_vmi_connect

La solucion a este problema es relativamente sensilla, abrir una ventana de consola en modo administrador (DOS, Command Prompt, como le conozcas),  y digitar lo siguiente en ya sea el caso

Edicion Comando
SQL Server 2014 mofcomp «%programfiles(x86)%\Microsoft SQL Server\120\Shared\sqlmgmproviderxpsp2up.mof»
SQL Server 2012 mofcomp «%programfiles(x86)%\Microsoft SQL Server\110\Shared\sqlmgmproviderxpsp2up.mof»
SQL Server 2008 mofcomp «%programfiles(x86)%\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof»
SQL Server 2008 mofcomp «%programfiles(x86)%\Microsoft SQL Server\90\Shared\sqlmgmproviderxpsp2up.mof»

Leer, buscar y filtrar el contenido del log de errores

Algunas veces se hace complicado encontrar lo que buscamos en el Log de SQL Server, debido a los muchos registros de información que se llevan a cabo por parte del sistema (auditorias de seguridad, ejecución de respaldos, etc)

--SIN FILTROS
EXEC xp_readerrorlog

Ejecutar la instruccion sin ningun filtro nos va a generar la misma cantidad de informacion tan abrumadora de la interfaz grafica. Dichosamente el procedimiento tiene parametros para filtrar la informacion:

  • Parametro #1 –
    • Se usa para identificar el archivo a consultar.
    • MSSQL guarda una cantidad configurable de archivos de registro de eventos, y se crea uno nuevo con cada registro Sin no se envia ningun valor en este parametro siempre se revisara el actual cuyo valor es de 0
  • Parametro #2 –
    • Es un filtro para incluir eventos del motor o del Agente
      • 1 = SQL Server error log (default)
      • 2 = SQL Agent log
  • Parametro #3 –
    • Es un filtro de busqueda del tipo NChar
  • Parametro #4 –
    • Es un filtro adicional de busqueda del tipo NChar
  • Parametro #5 –
    • Es un filtro de busqueda por fecha, en el que se incluyen los errores anteriores a la fecha definida
  • Parametro #6 –
    • Es un filtro de busqueda por fecha, en el que se incluyen los errores posteriores a la fecha definida
  • Parametro #7 –
    • Es un parametro para definir el ordenamiento de la informacion
      • – N’ASC’
      • – N’DESC’
--Con Filtros
EXEC xp_readerrorlog 0, 1, NULL, NULL, NULL, NULL, N'ASC'

La imagen que esta justo debajo de este texto es un ejemplo de la instancia que tengo instalada en mi maquina personal, y se muestran miles de miles de filas de informacion en una maquina de escritorio; que puede depararnos un server productivo con cientos, miles o millones de transacciones diarias.

SQL Server Error Log

Ref:

https://blogs.msdn.microsoft.com/askjay/2010/09/19/the-parameters-of-xp_readerrorlog/

SQL Server en Linux

La ultima noticia y lo que parece ser la bomba de la semana y el año en el mundo de las bases de datos. MSSQL Server va a tener una versión que correrá en Linux. Para que versiones no se sabe, que funcionalidades van a estar disponibles, licenciamiento, todavía todo es un misterio, pero creo que si es buena noticia!

A la expectativa; acá el texto del anuncio en Ingles:

MSSQL Love Linux

 

Data Files – Espacio Reservado y libre

De ahora en adelante quiero compartir un poco mas frecuentemente lo que uso a diario o lo que aprendo de una u otra forma. Ciertamente he estado un poco acalambrado y ocupado por muchos cambios que se avecinaron en los últimos meses.

Espero que esta consulta para listar los archivos de una base de datos, su espacio reservado y el espacio libre para en ambos en MB, por data file les sea de utilidad

SELECT a.fileid,
       CONVERT(DECIMAL(12, 2), Round(a .size / 128.000, 2)) AS [FILESIZEINMB],
       CONVERT(DECIMAL(12, 2), Round(Fileproperty (a. NAME, 'SpaceUsed') / 128.000, 2)) AS [SPACEUSEDINMB],
       CONVERT(DECIMAL (12, 2), Round ((a. SIZE - Fileproperty (a. NAME, 'SpaceUsed')) / 128.000, 2)) AS [FREESPACEINMB],
       a.NAME AS [DATABASENAME],
       a.filename AS [FILENAME],
       b .NAME
FROM sys. sysfiles a
LEFT OUTER JOIN sys. filegroups b ON a.groupid = b. data_space_id