SQL Elemental

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:

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.
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.
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.
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 atacar problemas de rendimiento de Código T-SQL

Mi intencion con este proyecto es comentar un poco de lo que hago cuando encuentro problemas con los SQL que escribo o han escrito otras personas en el lugar donde trabajo.

Por razones de simplificar esta sección voy a utilizar codigo de la base de datos adventureWorks de MS para algunas de las pruebas.
Si no tienes una copia de esta base de datos o una instancia de SQL server corriendo puedes bajar tanto la base de datos como el motor en su version para desarrollador de las siguientes paginas de Microsoft.

Que T-SQL? es la implementacion de ANSI SQL de Microsoft, es el lenguaje que utilizamos para comunicarnos con el motor de base de datos Microsoft SQL Server.

Que son los planes de Ejecucion en SQL?

Los planes de ejecucion son las instrucciones que el motor de base datos requiere para poder recuperar la informacion que cumpla con las condiciones que estamos definiendo para nuestras consultas.

No vamos a ahondar mucho en el tema, pero basicamente se hace una validacion de sintaxis del codigo que el usuario introduce, despues se valida si todos los objetos que incluyo en mi consulta existen en la base de datos. Los planes de ejecucion son una tarea relativamente cara por lo que MSSQL validara si existe un plan para la consulta que introducimos, de lo contrario intentera generar un plan para la consulta.

Como comente hace unas lineas el plan de ejecucion se compone de instrucciones para acceder los datos que estamos buscando, algunos ejemplos de estas instrucciones son table Scans, index scans, index seeks, etc. Para conocer mas acerca de estas instrucciones se puede buscar en informacion acá.

Los planes de ejecucion dependen de las estadisticas de las tablas, la mismas pueden ser vistas como histogramas, son representaciones variables que nos indican como estan distribuidos los datos en una estructura particular. Podemos decir que nuestras consultas (queries) seran tan buenos como las estadisticas que tenga la base de datos a consultar

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.

Ejecutar el comando [SET IDENTITY_INSERT dbo.MiTabla ON;]
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:

No usamos replicacion
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

&lt;span 				data-mce-type=&quot;bookmark&quot; 				id=&quot;mce_SELREST_start&quot; 				data-mce-style=&quot;overflow:hidden;line-height:0&quot; 				style=&quot;overflow:hidden;line-height:0&quot; 			&gt;&lt;/span&gt;
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.

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

Encontrar las dependencias en mi base de datos

Escribe esta consulta temprano para buscar las dependencias de un procedimiento que estaba modificando, pude haber utilizado el sp_depends para que me liste todas las dependencias pero no me hubiese listado los totales que es lo que realmente estaba buscando.

SELECT DISTINCT
OBJECT_NAME(o.object_id) ,
v.name ,
COUNT(1)
FROM
sys.objects o
INNER JOIN master.dbo.spt_values v ON o.type = SUBSTRING(v.name, 1, 2) COLLATE catalog_default
AND v.type = 'O9T'
INNER JOIN sys.sql_dependencies d ON o.object_id = d.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE
o.object_id = OBJECT_ID('<MIPROCEDIMIENTO>')
AND d.class < 2
GROUP BY
OBJECT_NAME(o.object_id) ,
v.name
ORDER BY
3 DESC;
GO


sp_depends '<MIPROCEDIMIENTO>';</pre>
<pre>