Optimización en SQL Server: Consultas fundamentales

Frecuentemente nos podemos encontrar con problemas de rendimiento de nuestro servidor de bases de datos. Detectar donde se encuentra el cuello de botella y cuáles son las causas que lo producen se convierte en una tarea fundamental para cualquier DBA. ¿El problema se encuentra en la parte más física (CPU, memória, disco) del servidor? O por el contrario, ¿se encuentra en el diseño más lógico (diseño de las bases de datos, consultas, índices, etc.)
Para detectar problemas a nivel de hardware podemos usar los contadores de rendimiento, que nos permitirán comprobar "la salud" de nuestro servidor y ver donde podemos tener el cuello de botella. Aun así, por muy potente que sea nuestro hardware, el servidor no responderá adecuadamente si el diseño de las bases de datos no es correcto o no está optimizado.
Centrándonos en este último punto, os quería presentar algunas consultas que nos pueden ser de utilidad para detectar qué consultas o procedimientos almacenados están "presionando" la CPU, el disco y la memoria:
Procedimientos almacenados más ejecutados frecuentemente

SELECT TOP 100 qt.text AS 'Nombre_SP',
qs.execution_count AS 'NumEjecuciones',
qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Llamadas/Segundo',
(qs.total_worker_time/qs.execution_count)/1000000.0 AS 'PromedioTiempoEjecucion(s)',
qs.total_worker_time/1000000.0 AS 'TiempoTotal(s)',
qs.max_logical_reads AS 'LecturasLogicas', 
qs.max_logical_writes AS 'EscriturasLogicas', 
qs.total_physical_reads AS 'LecturasFisicas',
DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'TiempoCache(min)'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = db_id()
ORDER BY qs.execution_count DESC

Procedimientos almacenados más costosos para la CPU

SELECT TOP 20 qt.text AS 'Nombre_SP', 
qs.total_worker_time/1000000.0 AS 'TiempoTotal(s)',
(qs.total_worker_time/qs.execution_count)/1000000.0 AS 'PromedioTiempoEjecucion(s)',
qs.execution_count AS 'NumEjecuciones',
qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Llamadas/Segundo',
qs.max_logical_reads AS 'LecturasLogicas', 
qs.max_logical_writes AS 'EscriturasLogicas', 
qs.total_physical_reads AS 'LecturasFisicas',
DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'TiempoCache(min)'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = db_id()
ORDER BY qs.total_worker_time DESC

Procedimientos almacenados más costosos para la memoria

SELECT TOP 20 qt.text AS 'Nombre_SP',
total_logical_reads AS 'TotalLecturasLogicas',
qs.execution_count AS 'NumEjecuciones',
(total_logical_reads/qs.execution_count)/1000000.0 AS 'PromedioLecturasLogicas',
qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Llamadas/Segundo',
(qs.total_worker_time/qs.execution_count)/1000000.0 AS 'PromedioTiempoEjecucion(s)',
qs.max_logical_reads AS 'LecturasLogicas', 
qs.max_logical_writes AS 'EscriturasLogicas', 
qs.total_physical_reads AS 'LecturasFisicas',
DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'TiempoCache(min)'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = db_id()
ORDER BY total_logical_reads DESC

Procedimientos almacenados más costosos para el disco (lecturas)

SELECT TOP 20 qt.text AS 'Nombre_SP',
qs.total_physical_reads AS 'TotalLecturasFisicas',
(qs.total_physical_reads/qs.execution_count)/1000000.0 AS 'PromedioLecturasFisicas',
qs.execution_count AS 'NumEjecuciones',
qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Llamadas/Segundo',
qs.max_logical_reads AS 'LecturasLogicas', 
qs.max_logical_writes AS 'EscriturasLogicas', 
DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'TiempoCache(min)'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = db_id()
ORDER BY qs.total_physical_reads DESC

Cabe destacar que las consultas están basadas en el uso de las funciones y vistas de administración dinámica (popularmente conocidas como DMV).
Antes de finalizar el artículo, me gustaría mencionar que parte de las consultas han sido obtenidas del blog sqlserverperformance, aunque las he adaptado y modificado para mayor comprensión de los lectores.
¡Enjoy T-SQL!

Reducir el tamaño del log de transacciones de todas las bases de datos en SQL Server.

Dándole vueltas a lo que escribí en el post anterior se me ocurrió que podía crear un script para reducir el log de transacciones de todas las bases de datos (en SQL Server 2005 y superior), exceptuando aquellas que son del propio sistema.

El algoritmo se basa en los pasos siguientes:

1. Recorrer todas las bases de datos que no son de sistema y que tienen el modelo de recuperación en completo o registro de copias masivas (quedan almacenadas en la variable de tabla @tblBD).
2. Poner las bases de datos que cumplen las condiciones anteriores en modelo de recuperación simple.
3. Buscar los ficheros que almacenan los registros de transacciones para cada base de datos (quedan almacenados en la tabla temporal #tblLog).
4. Reducir el espacio de cada uno de los ficheros detectados en el paso anterior (mediante la instrucción DBCC_SHRINKFILE)
5. Poner el modelo de recuperación de la base de datos al estado original (completo o registro de copias masivas).

Así que me puse manos a la obra, y aquí os lo dejo:

DECLARE @tblBD TABLE
(
  IDAux SMALLINT IDENTITY(1,1), 
  BDID INT,
  BDName VARCHAR(4000),
  RecoveryModel SMALLINT
)

CREATE TABLE #tblLog 
(
  IDRowLog SMALLINT IDENTITY (1,1), 
  LogFilename VARCHAR(4000)
)

DECLARE @intRecoveryModel SMALLINT
DECLARE @intNumTotalBD SMALLINT
DECLARE @intContBD SMALLINT=1
DECLARE @intBDID INT
DECLARE @strBDName VARCHAR(4000)
DECLARE @strSQL AS NVARCHAR(4000)

INSERT INTO @tblBD
SELECT database_id,
  name,
  recovery_model 
FROM sys.databases
WHERE NAME NOT IN ('master','tempdb','model','msdb')
AND recovery_model IN (1,2)

SELECT @intNumTotalBD=COUNT(*) FROM @tblBD

WHILE @intContBD<=@intNumTotalBD
  BEGIN
 SELECT @intBDID=BDID,
   @strBDName=BDName,
   @intRecoveryModel=RecoveryModel
 FROM @tblBD WHERE IDAux=@intContBD
 
 SET @strSQL='ALTER DATABASE '+@strBDName+' SET RECOVERY SIMPLE'
 exec sp_executesql @strSQL
 --PRINT @strsQL
 
 DECLARE @intLogTotal AS SMALLINT
 DECLARE @intPos AS SMALLINT=1
 DECLARE @strLogFile AS VARCHAR(4000)

 INSERT INTO #tblLog (LogFilename)
 SELECT name FROM sys.master_files 
 WHERE database_id=@intBDID AND type=1

 SELECT @intLogTotal=COUNT(*) 
 FROM #tblLog

 WHILE @intPos<=@intLogTotal
   BEGIN
  SELECT @strLogFile=LogFileName 
  FROM #tblLog WHERE IDRowLog=@intPos
  
  SET @strSQL='USE '+@strBDName+' DBCC SHRINKFILE('+@strLogFile+')'
  EXEC sp_executesql @strSQL
  --PRINT @strSQL
  SET @intPos+=1
   END
   TRUNCATE TABLE #tblLog
 IF @intRecoveryModel=1 
   SET @strSQL='ALTER DATABASE '+@strBDName+' SET RECOVERY FULL'
 ELSE 
   SET @strSQL='ALTER DATABASE '+@strBDName+ ' SET RECOVERY BULK_LOGGED'
 
 exec sp_executesql @strSQL
 --PRINT @strSQL
 SET @intContBD+=1
  END
  DROP TABLE #tblLog

Aun así, también existen otras formas de lograr el mismo objetivo, con lo que podéis evaluar la opción que más os guste o entendáis mejor.

¡Enjoy T-SQL!

Reducir el tamaño del log de transacciones en SQL Server

Últimamente me he encontrado unas cuantas situaciones en las que me han preguntado cómo reducir el tamaño del log de transacciones en SQL Server 2008 y/o superior, ya que las instrucciones usadas en SQL Server 2000 y SQL Server 2005 usadas para este fin dejaron de estar soportadas.

El script a usar sería el siguiente (en este caso usaremos la base de datos AdventureWorks):

USE AdventureWorks
GO

ALTER DATABASE AdventureWorks 
SET RECOVERY SIMPLE
GO

DBCC SHRINKFILE (AdventureWorks_Log)
GO

ALTER DATABASE AdventureWorks 
SET RECOVERY FULL
GO

Cabe tener en cuenta que en este ejemplo la instrucción DBCC SHRINKFILE reduce el fichero de log al máximo (en el parámetro se le debe indicar el nombre lógico del fichero).
En otro post ya hablaremos el porqué del crecimiento del fichero de log y como se podría evitar.
¡Enjoy T-SQL!

Transfiriendo inicios de sesión y contraseñas de SQL Server entre servidores

En cualquier proyecto de migración o de traspaso de bases de datos entre servidores de SQL Server nos encontramos con la problemática de los usuarios huérfanos. Un primer paso para resolver este tipo de incidencia es la transferencia de los inicios de sesión de un servidor hacía otro mediante el uso de la función sp_help_revlogin, el script de la cuál podéis encontrar aquí, si vuesto servidor es un SQL Server 2000, o bien aquí si es un SQL Server 2005.
Mediante la ejecución de este script se generará la función sp_help_revlogin en la base de datos master, que posteriormente deberemos ejecutar (exec sp_help_revlogin) y gracias a la cuál obtendremos en la ventana de resultados el script de generación de todos los inicios de sesión del servidor donde hayamos ejecutado la función. Útil, ¿verdad?

El siguiente paso ya será la asociación entre inicios de sesión y usuarios de las distintas bases de datos, de lo que ya hablaremos en otro post.
¡Enjoy T-SQL!

Threading y programación en paralelo con C#

Recientemente leí en un foro de linkedin algunas dudas referentes a la programación con threads, y sobretodo, la programación en paralelo, con la nueva versión del NET Framework (4.0). En una de la respuestas una persona recomendó el sitio Threading in C#, de Joseph Albahari. Después de darle un vistazo, puedo afirmar que se trata de un sitio muy recomendable para explorar las distintas opciones en cuanto a la programación con threads y en paralelo.
Las explicaciones son muy claras y concisas, entrando en en nivel de detalle justo e imprescindible. El tutorial está dividido en 5 partes: introducción, sincronización y bloqueos, uso básico de los threads, uso avanzado y programación en paralelo).
Finalmente, comentaros que el tutorial también se puede descargar en pdf.

¡Enjoy threading!

Incidencia con Visual Studio 2010 y Crystal Reports

En un proyecto en el que estoy trabajando actualmente necesito mostrar algunos datos en formato report, de forma que se puedan imprimir fácilmente. Como ya tengo experiencia previa en el desarrollo de informes mediante Crystal Reports, he decidido utilizar esta herramienta (en concreto, la versión SAP Crystal Reports for Visual Studio 2010).


Al crear el report e intentar crear una conexión a la base de datos, resulta que se producía un error con el siguiente mensaje: Un componente externo produjo una excepción en Visual Studio 2010. Después de buscar por la web e instalar Visual Studio 2010 SP1, y Crystal Reports for Visual Studio 2010 SP1 (con el consiguiente tiempo necesario) , ¡no resolví el error!

Buscando de nuevo, encontré está nota en la web de SAP, en la que se indica que el error se produce sólo si tenemos una carpeta en la unidad C:\ con el nombre temp. En definitiva, que renombré esta carpeta, y problema resuelto. Sinceramente, ¡ver para creer!


Espero que con esta artículo os pueda ahorrar un poco de tiempo si os encontráis con el mismo problema.


¡Enjoy Visual Studio 2010 y Crystal Reports!

Backup y restore de elementos en MOSS 2007 (II)

Herramientas de terceros

Como comentamos en el anterior post, existen diferentes herramientas para intentar recuperar la información que podamos haber perdido en cualquier sitio de Sharepoint. Aún así, estas herramientas tienen algunas limitaciones, y por este motivo aparecen herramientas de terceros, gracias a las cuáles podemos superarlas. Veamos cuáles son estas herramientas:

• Data Protection Manager 2007: Mediante esta herramienta de Microsoft es posible hacer copias de seguridad y restauraciones de colecciones de sitios, de sitios específicos, listas y bibliotecas de documentos, e incluso del contenido de éstas (e aquí la diferencia con los productos presentados anteriormente). El único inconveniente es que requiere de hardware adicional (básicamente una máquina dedicada a hacer las tareas de backup), a parte de ser un poco complejo de configurar.

• DocAve Backup and Recovery: Con esta herramienta es posible hacer copias de seguridad de colecciones de sitios, sitios, ficheros, listas y elementos, además de su contenido. Con estas copias también se incluyen los metadatos, histórico de versiones, flujos de trabajo, etc… Un producto ciertamente interesante, aunque el precio puede ser un poco elevado (2.880$ por colección de sitios, si queremos recuperación a nivel de elemento).

• CommVault: Herramienta similar a la anterior (copias y restauraciones a nivel de servidor, colecciones de sitios, sitios, listas y bibliotecas, elementos, y ficheros), con un precio un poco menor (1.300$ por servidor).

Las herramientas presentadas tienen como característica común el hecho de que nos permiten recuperar elementos a nivel de lista o biblioteca de documentos. Existen otras herramientas, como Neverfail for Sharepoint o Quest Software Sharepoint Tools for the Enterprise que no tienen esta característica, aunque sí son buenas soluciones para mantener entornos con alta disponibilidad.

Seguramente existirán otros productos, pero los aquí presentados son los más significativos. Si tenéis alguna experiencia en su uso y/o implementación, ¡no dudéis en comentarmelas! AvePoint se posiciona como un muy buen producto, aunque creo que en el futuro habrá una fuerte integración de MOSS con Data Protection Manager. ¿Qué pensáis?

¡Espero vuestros comentarios!