September 2007 - Posts
Ron Talmage ha estado trabajando en el SQL Server Customer Lab, buscando las cosquillas a Full-Text, y aquí puedes leer un resultado de las conclusiones obtenidas:
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/ftslesld.mspx
ay Ron, que envidia!! J
Introducción
He estado impartiendo una serie de charlas para nuestros Summits en Latam (http://learning.solidq.com/la/CourseDetail.aspx?CourseScheduleId=211), y ayer tuve un webcast similar para CAPA8 en Argentina (http://www.microsoft.com/conosur/technet/realityit/).
Durante las sesiones hice referencia a conocer el uso que se hace de los ficheros del servidor, cual es la topología de uso de cada fichero, cuales son los más usados, etc.
En SQL Server 2005, aparece una DMV (dm_io_virtual_file_stats, http://msdn2.microsoft.com/es-es/library/ms190326.aspx), que nos reporta información tan interesante como:
Nombre de columna | Tipo de datos | Descripción |
database_id | smallint | Id. de la base de datos. |
file_id | smallint | Id. del archivo. |
sample_ms | int | Número de milisegundos desde que se inició la instancia de SQL Server. Esta columna se puede utilizar para comparar diferentes resultados de esta función. |
num_of_reads | bigint | Número de operaciones de lectura realizadas en el archivo. |
num_of_bytes_read | bigint | Número total de bytes leídos en el archivo. |
io_stall_read_ms | bigint | Tiempo total, en milisegundos, que los usuarios han esperado para que se realicen las lecturas en el archivo. |
num_of_writes | bigint | Número de operaciones de escritura realizadas en este archivo. |
num_of_bytes_written | bigint | Número total de bytes escritos en el archivo. |
io_stall_write_ms | bigint | Tiempo total, en milisegundos, que los usuarios han esperado para que se completen las escrituras en el archivo. |
io_stall | bigint | Tiempo total, en milisegundos, que los usuarios han esperado para que se completen las operaciones de E/S en el archivo. |
size_on_disk_bytes | bigint | Número de bytes utilizados en el disco para este archivo. En el caso de archivos dispersos, este número es el número real de bytes en el disco utilizados para las instantáneas de bases de datos. |
file_handle | varbinary | Identificador de archivo de Windows para este archivo. |
Obviamente es copy/paste de la url anterior, pero fíjate que la DMV reporta el número de lecturas, escrituras, incluso bytes, realizados en cada fichero de base de datos.
Esto es absolutamente indispensable para elegir cómo deben estar distribuidos los ficheros, en qué unidades físicas, incluso, qué nivel RAID es adecuado. La literatura dice que debes utilizar tal o cual nivel RAID para cada tipo de fichero, pero la realidad es que esa decisión la tomarás en función del número de discos disponibles, así que por ejemplo, si tienes 9 discos, ¿qué te conviene montar?
- Un RAID10 con 4 discos, y
- Un RAID5 con 5 discos.
O quizás:
- Un RAID 1 con 2 discos, y
- Un RAID10 con 6 discos.
O quizás:
- Un RAID 1 con 2 discos, y
- Un RAID5 con 7 discos.
Igual estás pensando: "Madre mia, cuanto lio, y yo que pensaba que había que montar topologías concretas para cada tipo de fichero; ¿no habíamos quedado que RAID10 para logs, y RAID5 para datos si no tenemos más discos?" Esa decisión la tendrás que tomar en función de uso que se haga de tus ficheros, y esa información la obtienes con el procedimiento que veremos a continuación J
Si claro, ¿pero quién justifica a los tíos de sistemas que en una instalación que ha estado funcionando "bien" durante un par de años, vamos a cambiar los niveles RAID porque lo ponga en este blog? Si lo necesitas en tu sistema, pues tendrás que justificarlo y proponer el cambio; piensa que depende del beneficio que tengas… ¿conviene montar toda esa historia para ganar una media de digamos 100 milisegundos por consulta? Pues depende… hace no mucho estuve con una empresa en Costa Rica que gestionaban créditos hipotecarios para miles de agencias… piensa un momento que esos 100 milisegundos que podrías ganar, supondrían mayor concurrencia en tu sistema (las transacciones son más cortas), y mayor tiempo de respuesta (sensación de usuario)… como siempre, depende la inversión que vas a hacer con el retorno de beneficios…
El procedimiento almacenado
El siguiente código sirve para identificar a nivel de fichero de base de datos, qué tipo de uso se hace. El procedimiento almacenado está basado en el procedimiento anterior de waitstats, así que esta vez me permitiré no explicarlo; recordar: se hacen dos capturas, y se calculan los deltas, ok?
IF
EXISTS
(SELECT
*
FROM
SYS.PROCEDURES
WHERE
name
=
'sproc_get_fileiostats_percentage')
DROP
PROC dbo.sproc_get_fileiostats_percentage
GO
CREATE
PROC dbo.sproc_get_fileiostats_percentage
@hours tinyint
=
null,
@minutes tinyint
=
null,
@seconds tinyint
=
null
AS
SET
NOCOUNT
ON
IF @hours IS
NULL
SET @hours = 0
IF @minutes IS
NULL
SET @minutes = 0
IF @seconds IS
NULL
SET @seconds = 0
--
-- Validaciones iniciales
--
IF @hours < 0 OR @hours > 24
BEGIN
RAISERROR
('Hours range is not valid.', 16, 1 )
RETURN
END
IF @minutes < 0 OR @minutes > 60
BEGIN
RAISERROR
('Minutes range is not valid.', 16, 1 )
RETURN
END
IF @seconds < 0 OR @seconds > 60
BEGIN
RAISERROR
('Seconds range is not valid.', 16, 1 )
RETURN
END
IF @hours = 0 and @minutes = 0 and @seconds = 0
BEGIN
RAISERROR
('The measure time must be greater than zero.', 16, 1 )
RETURN
END
DECLARE @t TABLE
(
id int
identity
, database_id smallint
,
file_id
smallint
, num_of_reads bigint
, num_of_bytes_read bigint
, io_stall_read_ms bigint
, num_of_writes bigint
, num_of_bytes_written bigint
, io_stall_write_ms bigint
)
--
-- Inserción de primera captura
--
INSERT @t
( database_id
,
file_id
, num_of_reads
, num_of_bytes_read
, io_stall_read_ms
, num_of_writes
, num_of_bytes_written
, io_stall_write_ms )
SELECT
database_id
,
file_id
, num_of_reads
, num_of_bytes_read
, io_stall_read_ms
, num_of_writes
, num_of_bytes_written
, io_stall_write_ms
FROM
sys.dm_io_virtual_file_stats(-1,-1)
--
-- A esperar n tiempo
--
DECLARE @s CHAR(8)
SET @s =
RIGHT
('00'
+
CAST
(@hours as
VARCHAR(2)), 2)
+
':'
+
RIGHT
('00'
+
CAST
(@minutes as
VARCHAR(2)), 2)
+
':'
+
RIGHT
('00'
+
CAST
(@seconds as
VARCHAR(2)), 2)
WAITFOR
DELAY @s
--
-- Inserción de segunda captura
--
INSERT @t
( database_id
,
file_id
, num_of_reads
, num_of_bytes_read
, io_stall_read_ms
, num_of_writes
, num_of_bytes_written
, io_stall_write_ms )
SELECT
database_id
,
file_id
, num_of_reads
, num_of_bytes_read
, io_stall_read_ms
, num_of_writes
, num_of_bytes_written
, io_stall_write_ms
FROM
sys.dm_io_virtual_file_stats(-1,-1)
SELECT s.name, v.*
FROM
(
SELECT
T1.database_id
, T1.file_id
,
(AVG(T2.num_of_reads - T1.num_of_reads)) num_of_reads
,
(AVG(T2.num_of_bytes_read - T1.num_of_bytes_read)) num_of_bytes_read
,
(AVG(T2.io_stall_read_ms - T1.io_stall_read_ms)) io_stall_read_ms
,
(AVG(T2.num_of_writes - T1.num_of_writes)) num_of_writes
,
(AVG(T2.num_of_bytes_written - T1.num_of_bytes_written)) num_of_bytes_written
,
(AVG(T2.io_stall_write_ms - T1.io_stall_write_ms)) io_stall_write_ms
FROM @T t1
JOIN @T t2
ON T1.database_id = T2.database_id
AND T1.file_id
= T2.file_id
AND t1.id < T2.id
GROUP
BY
T1.database_id
, T1.file_id
) v
JOIN
sys.databases s
ON v.database_id = s.database_id
WHERE num_of_reads <> 0 OR num_of_writes <> 0
GO
Y un ejemplo de uso sería el siguiente:
EXEC dbo.sproc_get_fileiostats_percentage
@hours =
1,
@minutes =
null,
@seconds =
null
- ¿Qué hará? Pues como habrás imaginado, tardará en finalizar su ejecución 1 hora, y te mostrará el uso que se hace de cada fichero durante ese periodo de tiempo.
- ¿Siguiente paso? Copiarlo a un documento Excel, y calcular porcentajes. Recuerda, será
tan sencillo
como contar habas, ¿verdad Carlos? J
Si bien la DMV no existe en SQL Server 2000, existe la función ::fn_virtualfilestats(-1, -1), que expone prácticamente lo mismo
Database Mirroring también está disponible para conexiones ODBC; recuerda que para que la aplicación cliente sepa a qué servidor intentar "re-conectar", en algún sitio en su configuración debe conocer los servidores miembros.
Puedes cambiar la cadena de conexión de tu aplicación usando la siguiente sintaxis:
- Server=instancia_servidor_principal;
- Failover Partner=instancia_servidor_mirror;
Cambiar la cadena de conexión, es posible que no requiera recompilar la aplicación si lo gestionas a través de ficheros INI, pero en las "viejas" aplicaciones VB6, no se utilizaba esta técnica con demasiada frecuencia por lo que con bastante probabilidad requeriría recompilación de la aplicación.
Como debes saber, las APIs de acceso a datos también implementan Mirroring para ODBC, por lo que si tienes DSNs de usuario también es muy fácil de cambiar; tan sólo tendrás que cambiar el Driver al nuevo Driver SQL Native Cliente (fíjate en esta primera pantalla el elemento seleccionado):
Y luego, en la configuración de la base de datos a la que deseas conectar, tienes la posibilidad de establecer cual es servidor mirror (en el caso de ejemplo sería DELL-ERH\INSTANCE2):
Otras consideraciones interesantes que comenté en la presentación de ayer de nuestro SQL Summit:
- Recuerda que cuando tu aplicación conecta al servidor principal, el servidor principal envía a la aplicación cliente quien es el servidor mirror: en este caso la información que tienes en la cadena de conexión, las APIs de acceso a datos no la necesitan porque tiene de primera mano (el servidor principal) quien es el servidor mirror.
- La información que tienes en la cadena de conexión del servidor mirror, solamente será utilizada cuando el primer intento de conexión no se puede satisfacer contra el servidor principal. SOLO en este caso, tirará de la información que tiene de la cadena de conexión.
En SQL Server 2008, (según comentan los whitepapers iniciales), no será necesario codificar quien el servidor mirror porque "milagrosamente" la aplicación cliente tendrá cacheada esa información. Digo "milagrosamente", porque no conozco todavía el mecanismo que utilizarán para cachear esa información. Recuerdo que en las primeras fases betas de SQL Server 2005, implementaban un mecanismo similar, pero con el paso del tiempo la implementación se quitó del producto por razones que desconozco.
Mi opinión personal es que el comportamiento que se promete para SQL Server 2008, es el comportamiento que "debería ser"
Extraordinaria entrevista de Itzik a David Campbell (General Manager of Strategy, Infrastructure and
Architecture of Microsoft SQL Server)
http://www.sqlmag.com/Article/ArticleID/96048/96048.html
Pasado, presente, y futuro de SQL Server...
Gracias Itzik :)
Introducción
En Solid Quality estamos realizando una serie de Summits por Latam (http://learning.solidq.com/la/CourseDetail.aspx?CourseScheduleId=211). El viernes pasado tuvimos el primero en Costa Rica, y próximamente estaremos en México, Colombia y Perú. Aunque no presencialmente, tengo la suerte de haber sido invitado a estos eventos como ponente. Me encuentro en España, y a través de Livemeeting, puedo contar a los asistentes a los Summits mis experiencias desde mi hogar en Torrevieja
Bueno, el tema es que este viernes, tuve la primera presentación, y aunque la presentación estaba focalizada en SQL Server 2005:
"SQL Server: Optimización y Afinamiento de servidores SQL Server: Al atender esta sesión, brindaremos las mejores prácticas para el análisis de rendimiento de servidores de bases de datos SQL Server 2005. Aprenderá a monitorear el uso de los recursos de su servidor (procesador, memoria, unidades IO, …) y mediante nuevas funciones de administración (DMVs), SQL Profiler y Performance Monitor mostraremos como identificar las consultas que más impacto tienen en su sistema para atacar los problemas de rendimiento desde su raíz. Muchas de las prácticas que presentaremos son aprendidas directamente del equipo que desarrolló el producto."
Estuve haciendo bastantes referencias a SQL Server 2000 porque:
Muchos de los asistentes trabajan con SQL Server 2000.
Nuestra experiencia en clientes dice que todavía hay muchos clientes con SQL Server 2000.
El mismo artículo lo tengo publicado para SQL Server 2005 en las siguientes URLs:
http://blogs.solidq.com/ES/erincon/Lists/Posts/Post.aspx?ID=28
http://solidqualitylearning.com/Blogs/eladio/archive/2007/02/22/3727.aspx
Me comprometí con los asistentes a publicar el mismo script para SQL Server 2000, así que ahí va:
El procedimiento almacenado
Para vuestra información he tenido que realizar los siguientes cambios:
Quitar referencia a CTE (obviamente en SQL Server 2000 no existe).
Quitar referencia a DMV y utilizar el correspondiente comando DBCC.
Cambiar tabla variable por tabla temporal porque un comando DBCC no puede insertar en una tabla variable.
Filtrar del resultado del comando DBCC el wait_type "Total" que es la suma total de los valores medidos.
Ejemplo de ejecución:
EXEC dbo.sproc_get_waitstats_percentage
@hours = null,
@minutes = 5,
@seconds = null
El procedimiento almacenado:
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sproc_get_waitstats_percentage')
DROP PROC dbo.sproc_get_waitstats_percentage
GO
CREATE PROC dbo.sproc_get_waitstats_percentage
@hours tinyint = null,
@minutes tinyint = null,
@seconds tinyint = null
AS
SET NOCOUNT ON
IF @hours IS NULL
SET @hours = 0
IF @minutes IS NULL
SET @minutes = 0
IF @seconds IS NULL
SET @seconds = 0
--
-- Validaciones iniciales
--
IF @hours < 0 OR @hours > 24
BEGIN
RAISERROR ('Hours range is not valid.', 16, 1 )
RETURN
END
IF @minutes < 0 OR @minutes > 60
BEGIN
RAISERROR ('Minutes range is not valid.', 16, 1 )
RETURN
END
IF @seconds < 0 OR @seconds > 60
BEGIN
RAISERROR ('Seconds range is not valid.', 16, 1 )
RETURN
END
IF @hours = 0 and @minutes = 0 and @seconds = 0
BEGIN
RAISERROR ('The measure time must be greater than zero.', 16, 1 )
RETURN
END
--
-- Definición de variable table
--
CREATE TABLE #T (
id int identity
, wait_type nvarchar(60)
, Requests bigint
, Wait_Time bigint
, signal_Wait_Time bigint)
--
-- Inserción de captura inicial
--
INSERT #T
EXEC ('DBCC SQLPERF(WAITSTATS)')
--
-- A esperar n tiempo
--
DECLARE @s CHAR(8)
SET @s =
RIGHT ('00' + CAST (@hours as VARCHAR(2)), 2) + ':'
+ RIGHT ('00' + CAST (@minutes as VARCHAR(2)), 2) + ':'
+ RIGHT ('00' + CAST (@seconds as VARCHAR(2)), 2)
WAITFOR DELAY @s
--
-- Inserción de segunda captura
--
INSERT #T
EXEC ('DBCC SQLPERF(WAITSTATS)')
--
-- calculos finales
--
SELECT
detalle.*
, detalle.Wait_Time * 1.00 / detalle.Requests as wait_per_request
, CASE WHEN suma.Requests = 0 THEN 0 ELSE detalle.Requests * 1.00 / suma.Requests END as porcen_Requests
, CASE WHEN suma.Wait_Time = 0 THEN 0 ELSE detalle.Wait_Time * 1.00 / suma.Wait_Time END as porcen_Wait_Time
, CASE WHEN suma.signal_Wait_Time = 0 THEN 0 ELSE detalle.signal_Wait_Time * 1.00 / suma.signal_Wait_Time END as porcen_signal_Wait_Time
FROM
(SELECT * FROM ( SELECT
T1.wait_type
, AVG(T2.Requests - T1.Requests) Requests
, AVG(T2.Wait_Time - T1.Wait_Time) Wait_Time
, AVG(T2.signal_Wait_Time - T1.signal_Wait_Time) signal_Wait_Time
FROM #T t1
JOIN #T t2
ON T1.wait_type = T2.wait_type
AND T1.id < T2.id
WHERE T1.wait_type <> 'Total'
GROUP BY T1.wait_type
) v WHERE
v.Wait_Time <> 0
) detalle,
( SELECT
SUM (T2.Requests - T1.Requests) Requests
, SUM (T2.Wait_Time - T1.Wait_Time) Wait_Time
, SUM (T2.signal_Wait_Time - T1.signal_Wait_Time) signal_Wait_Time
FROM #T t1
JOIN #T t2
ON T1.wait_type = T2.wait_type
AND T1.id < T2.id
WHERE T1.wait_type <> 'Total'
) suma
GO
Siéntete libre de publicar comentarios…
Cumulative hotfix package (build 3152) for SQL Server 2005 Service Pack 2 is available
Bueno, parece que han sacado un FIX para un fallo de la lista del artículo:
http://solidqualitylearning.com/Blogs/eladio/archive/2007/03/08/3909.aspx
50000939 | The Maintenance Cleanup task uses an incorrect age group for cleanup operations. |
Pero parece que el otro no está resuelto todavía… en fin, para mi gusto (y el de muchos de vosotros), demasiada frecuencia de SP2, refresh, cummulative…
El KB está en http://support.microsoft.com/kb/933097/en-us
Lo solucionado es:
SQL bug number | Description |
50000783 | When you define the value of the AdminTimeout property and the value of the ServerTimeout property, a profiler trace stops unexpectedly. |
50000784 | When you make a data manipulation language (DML) change to a published table, error 2812 occurs. |
50000785 | When you create a cursor by using an SQL statement that uses the OPTION (RECOMPILE) hint, error 16943 occurs. Error 16943 indicates that the table schema changed. This problem occurs when you retrieve information from the cursor location. |
50000786 | A memory leak exists in the MSOLAP provider (Msolap90.dll). |
50000787 | When you configure the Scheduler component to use a time-out value that is less than 15 milliseconds, you experience high CPU usage. |
50000809 | The LazyWriter process uses lots of CPU resources. This problem occurs on a non-uniform memory access (NUMA)-enabled x64-based server that is running SQL Server 2005 x64 Edition. |
50000810 | When you update a view through a cursor, you cannot make a CSEQUAL request or a TSEQUAL request for rows that are extended by using NULL values. |
50000873 | When you run an application in SQL Server 2005, error 8624 occurs. Error 8624 indicates that the cycle check failed. |
50000874 | When the stored procedure runs outside an explicit transaction, a temporary table in a stored procedure is not cleaned up correctly. |
50000889 | When you run a Microsoft Office Excel data mining operation, you receive the following error message:
The 'MINIMUM_DEPENDENCY_PROBABILITY' data mining parameter is not valid for the 'Table1_283833_NB_413946' model |
50000939 | The Maintenance Cleanup task uses an incorrect age group for cleanup operations. |
Cuidado con los prerequisites (HABILITAR SMO y DMO XPs):
• | SQL Server 2005 hotfixes are now multilanguage. There is only one cumulative hotfix package for all languages. |
• | One cumulative hotfix package includes all the component packages. The cumulative hotfix package is designed to update only those components that are installed on the system. |
• | You must enable the SQL Server Management Object (SMO) extended stored procedures and the SQL Server Distributed Management Object (SQL-DMO) extended stored procedures before you install the cumulative hotfix package. For more information about the SMO and DMO XPs option, see SQL Server 2005 Books Online.
Note SQL Server 2005 Books Online notes that the default setting of these stored procedures is 0 (off). However, this value is incorrect. By default, the setting is 1 (on). |
Como comenta mi colega de blog Dejan Sarka, hay una refresh del SP2 de SQL Server 2005 que soluciona un error incluido en SP2 que provoca que el histórico de tareas de SQL Agent se borre antes de lo previsto (básicamente en lugar de interpretar días, interpreta horas).
http://solidqualitylearning.com/Blogs/Dejan/archive/2007/03/07/3900.aspx
Además, tendrás que tener en cuentan el siguiente problema:
https://connect.microsoft.com:443/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=262122
Ayer tuvimos unos threads moviditos en los newsgroups de MVPs; en definitiva, si tienes instalado SP2, cuando ejecutas una tarea SSIS de tipo TSQL, y tienes operaciones GO, provoca que se cierre la conexión, y la reconexión se realice a master.
Además, utilizando el asistente de planes de mantenimiento, si utilizas operaciones de comprobación de consistencia de base de datos DBCC CHECKDB, siempre se ejecutarán sobre la base de datos master.
La solución por el momento, es ejecutar esas tareas desde SqlAgent con un Script TSQL en el que tendrías algo así como lo siguiente:
USE db1
GO
DBCC CHECKDB
GO
USE db2
GO
DBCC CHECKDB
Este problema es sólo aplicable al SP2, es decir, si tienes instalado RTM o SP1 no te verás afectado. Sin embargo, mi laptop tiene SP2, y RTM, y editando paquetes SSIS de RTM he conseguido reproducir el fallo también en RTM, así que parece claro que el problema está en los paquetes SSIS guardados/creados/ejecutados desde SP2.
De momento no hay fix para ese problema (puedes comprobarlo en tus máquinas de pruebas con profiler y ejecutando la traza)… se estima que el fix estará listo en unos días…
Ejem.... El comentario del otro día me sigue dando vueltas ... Microsoft está haciendo bien su trabajo (productos cada vez más estables, productivos y efectivos), pero ¿realmente hacemos nosotros el nuestro?.
Todos conocemos la penalización que sufren nuestros servidores SQL Server con el uso de cursores y funciones que provocan que se realicen procesos iterativos: Un firme defensor de la lucha contra los cursores es Miguel Egea: Miembro de la Brigada Anti-Cursores y blogger en GolemProject http://weblogs.golemproject.com/egea. ¿Habéis pensado también lo negativo que resulta el uso de "SELECT *"?
Seguro que si, es más, yo creo que todos lo sabemos y sin embarlo lo seguimos utilizando ... Ahora me viene a la cabeza (no recuerdo donde lo he leido, si era en las news o en alguna url) que alguien comentó algún día la NECESIDAD de quitar el asterisco (*) del lenguaje T-SQL. Por descabellado que parezca: nos cargarmos en ANSI-SQL (que ya falta muy poco para acabar con el), hacemos más costosos los desarrollos (vaya tela con tener que escribir siempre las columnas) y lo más importante la compatibilidad hacia atrás (backward compatibility) se destroza. Hombre, yo creo que poniendo una opción de configuración SET (eso sí por defecto activa, el que quiera que la desactive), más de un servidor lo iba a agradecer ... y más de un desarrollador se tiraría de los pelos para hacerla compatible con el nuevo estandard ANTI_SELECT_ASTERISCO.
Bueno que me enrollo, habeis visto dos ejemplos básicos que casi todos conocemos, y sin embargo un alto porcentaje de los problemas de rendimiento vienen derivados de esos ejemplos.
¿y qué pasa ahora con Yukon? pues que Microsoft nos pone mucho más fácil la posibilidad de crear cursores en la propia base de datos con .NET. Uhhmmmmm No quiero decir que esté en desacuerdo: NI POR ASOOOOOMO ! No nos equivoquemos ! SQL Server es un servidor de base de datos que trabaja muy eficientemente orientado a juegos de registros ( set-based programming) sin embargo la inclusión de CLR dentro de la base de datos va a hacer renacer viejos hábitos orientados a fila ...
Microsoft es consciente de ello y seguro que tiene preparada mucha documentación y ejemplos para no dejarnos caer en la tentación, pero es la pregunta de la primera frase: ¿haremos bien nuestro trabajo? ¿Pasará igual que con el asterisco?