Diario de SQL Server

scripts

Monitorizar esperas

Escrito por qwalgrande 13-12-2010 en General. Comentarios (1)

Una de las bases de la monitorización de servidores de bases de datos es el seguimiento de las esperas para identificar cuellos de botella, problemas generales y problemas puntuales.

 

Estos scripts son para llevar un seguimiento de las esperas. Se crea una tabla (en una base de datos) y se van grabando fotos cada 15 minutos, porque las esperas tienen sentido como gradiente, no por el valor absoluto. Así que lo primero es crear dicha tabla:

 

--Base de datos de estadsticas

USE [master]

GO

CREATE DATABASE [Estadisticas] ON PRIMARY

( NAME = N'Estadisticas_Data', FILENAME = N'C:\DATA\Estadisticas_Data.MDF' , SIZE = 1048576KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10240KB )

LOG ON

( NAME = N'Estadisticas_Log', FILENAME = N'C:\LOGS\Estadisticas_Log.LDF' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10240KB )

GO

 

--Tabla de estadsticas:

USE [Estadisticas]

GO

CREATE TABLE [dbo].[WaitStats](

[wait_type] [sysname] NOT NULL,

[requests] [bigint] NULL,

[wait_time] [bigint] NULL,

[signal_wait_time] [bigint] NULL,

[FEC_Hora] [datetime] NOT NULL

) ON [PRIMARY]

GO

 

El siguiente paso es crear un job, que se lance cada 15 minutos, que ejecute lo siguiente:

 

--Para el job de carga

insert WaitStats (wait_type, requests, wait_time, signal_wait_time, FEC_Hora)

select wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms, getdate()

from sys.dm_os_wait_stats

 

Y por último, creamos un procedimiento almacenado que nos ayude a monitorizarlo. Si lo creamos como sp_WaitStats, podemos crear un atajo en Management Studio para que, por ejemplo, pulsando Ctrl + 9 podamos lanzarlo, sin preocuparnos de en qué base de datos estemos situados en ese momento. Su código sería este:

 

--Procedimientos almacenados para consultarlas

create proc sp_WaitStats as

declare @fecha datetime, @Total_Req bigint, @Total_Time bigint, @fecha_ant datetime, @Total_Req_ant bigint, @Total_Time_ant bigint

 

select @fecha = max(FEC_Hora) from Estadisticas.dbo.WaitStats with(nolock)

select @fecha_ant= max(FEC_Hora)

from Estadisticas.dbo.WaitStats with(nolock) where FEC_Hora < @fecha

select @Total_Req = sum(requests), @Total_Time = sum(wait_time)

from Estadisticas.dbo.WaitStats with(nolock) where FEC_Hora = @fecha

select @Total_Req_ant = sum(requests), @Total_Time_ant = sum(wait_time)

from Estadisticas.dbo.WaitStats with(nolock) where FEC_Hora = @fecha_ant

select @Total_Req_ant = isnull(@Total_Req_ant, 0), @Total_Time_ant = isnull(@Total_Time_ant, 0)

--Media General

select

wait_type,

cast(100.0*requests/ @Total_Req as numeric(19,2)) as [% Req],

cast(100.0*wait_time/ @Total_Time as numeric(19,2)) as [% Time],

cast(case when requests = 0 then 0 else 1.0*wait_time/requests end as numeric(19,2))as Avg_ms

from Estadisticas.dbo.WaitStats with(nolock)

where FEC_Hora = @fecha

order by [% Time] desc

--Entre dos momentos (los dos ltimos)

select

M2.wait_type,

cast(100.0*(M2.requests - isnull(M1.requests, 0))/ (@Total_Req - @Total_Req_ant) as numeric(19,2)) as [% Req],

cast(100.0*(M2.wait_time - isnull(M1.wait_time, 0))/ (@Total_Time - @Total_Time_ant) as numeric(19,2)) as [% Time],

cast(case when (M2.requests - isnull(M1.requests, 0)) <= 0 then 0 else 1.0*(M2.wait_time - isnull(M1.wait_time, 0))/(M2.requests - isnull(M1.requests, 0)) end as numeric(19,2))as Avg_ms

from

 (select wait_type, requests, wait_time from Estadisticas.dbo.WaitStats with(nolock) where FEC_Hora = @fecha) M2 left join

(select wait_type, requests, wait_time from Estadisticas.dbo.WaitStats with(nolock) where FEC_Hora = @fecha_ant) M1 on M2.wait_type = M1.wait_type

order by [% Time] desc

 

return 0

 

Y ya lo tenemos, cuando ejecutemos sp_WaitStats, obtendremos dos recordset, el primero con las esperas más significativas en general y el segundo con las esperas de los últimos 15 minutos. Hasta que no tengas 2 lecturas, no funcionará adecuadamente.

 

Lo que queda es saber interpretar estos resultados.

 

Para SQL Server 2005 (aunque aplicable al versiones siguientes): http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc

Para SQL Server 2008 R2 (Books Online): http://msdn.microsoft.com/es-es/library/ms179984(v=SQL.105).aspx

Un conjunto de links para mayor profundidad, del blog del MVP Brent Ozar: http://www.brentozar.com/sql/sql-server-performance-tuning/wait-stats/

 

Como dice Brent, demasiada información. El whitepaper de 2005 es más que suficiente para la gran mayoría de las situaciones. Y si encuentras dudas de interpretación, bueno, aquí seguiremos.

... y eliminar los índices que no se usan (2/2)

Escrito por qwalgrande 08-10-2010 en General. Comentarios (0)

Si en el anterior post hablábamos de la cómo crear los índices que se necesitan (http://qwalgrande.blogspot.es/1284932940/crear-los-indices-que-se-necesitan...-(1/2)/), aquí hablamos de lo contrario. Si no se usa un índice, lo mejor es eliminarlo.

 

Antes de SQL Server 2005, se tenía el miedo de eliminar un índice porque era complejo determinar cuándo un índice no se usaba. Sin embargo, de SQL Server 2005 en adelante, conservar un índice que no se va a usar carece de todo sentido, porque tenemos forma de saber si se usan o no. Para ello, se puede emplear un script similar a este:


select

  object_name(i.id) as Tabla,

  case i.indid

    when 0 then N'HEAP'

    else i.name

  end as Indice,

  i.Indid,

  i.rowcnt,

  i.dpages * 8/(1024.0) as Tamaño,

  i.*--i.type_desc as Tipo

  ,u.*

  , 'drop index [' + object_name(i.id) + '].[' +

  case i.indid when 0 then N'HEAP' else i.name end +']' as Borrado

from

  sysindexes i with(nolock) inner join

  sys.objects o with(nolock) on i.id = o.object_id left join

  sys.dm_db_index_usage_stats u with(nolock) 

     on i.id = u.object_id and i.indid = u.index_id and u.database_id = db_id()

where 

  o.type = 'u' and i.status & 64 = 0 and i.indid > 1 and

  --u.object_id is null --and i.type_desc = 'NONCLUSTERED'

  (u.user_seeks = 0 and u.user_scans = 0 or u.object_id is null)

order by i.dpages desc, Tabla, i.Indid

 

Y eso es todo, no se usa, se borra, para lo cual puede hacerse uso directamente de la última columna de la sentencia.

 

Crear los índices que se necesitan... (1/2)

Escrito por qwalgrande 19-09-2010 en General. Comentarios (1)

Es my difícil dar con la medida precisa de los índices que se deben crear, debido a que cuando se diseñan las aplicaciones, es raro dar con el comportamiento típico que tendrá luego el usuario en su día a día. Así, uno pronostica qué búsquedas se harán y prepara los índices para ello. Acertar es algo muy diferente.

 

Lo ideal sería que el usuario usara la aplicación y después pudiera optimizarse el conjunto de consultas más significativo. Justamente eso es lo que nos proporcionan las DMVs que dan acceso a estadísticas de uso. Es decir, se pueden crear la índices que serán usados con certeza, sabiendo además cuánto mejorarán esas consultas, y cómo de frecuente será su uso.

 

La información se puede consultar de muchas formas, esta es la consulta que yo empleo:

 

select

db_name(d.database_id) as DB,

object_name(d.object_id, d.database_id) tabla,

s.avg_user_impact, s.user_seeks,

d.equality_columns, d.inequality_columns, d.included_columns,

p.row_count, l.num_esperas, l.ms_esperas, s.last_user_seek,

create_index = replace('create nonclustered index IX_' +

object_name(d.object_id, d.database_id) +'_A# on ' +

object_name(d.object_id, d.database_id) + ' (' +

isnull(d.equality_columns + ',', '') + isnull(d.inequality_columns, '') + ') ' +

isnull('include (' + d.included_columns + ')', '') +

' with(online = on)', ',)', ')')

from

sys.dm_db_missing_index_details d left join

sys.dm_db_missing_index_groups g on d.index_handle =g.index_handle left join

sys.dm_db_missing_index_group_stats s on g.index_group_handle = s.group_handle left

oin

sys.dm_db_partition_stats p on d.object_id = p.object_id and p.index_id < 2 left join

(select

database_id,

object_id,

row_number() over (partition by database_id

order by sum(page_io_latch_wait_in_ms) desc) as row_number,

sum(page_io_latch_wait_count) as num_esperas,

sum(page_io_latch_wait_in_ms) as ms_esperas,

sum(range_scan_count) as range_scans,

sum(singleton_lookup_count) as index_lookups

from sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL)

where page_io_latch_wait_count > 0

group by database_id, object_id) l

on d.object_id = l.object_id and d.database_id = l.database_id

where

d.database_id = db_id()

and s.last_user_seek > dateadd(dd, -7, getdate())

order by --floor(s.avg_user_impact) desc,

s.user_seeks desc

 

Desde luego, no se pueden crear todos los índices que se recogen con esta consulta sin más. Hay que evaluarlos con los conocimientos que se poseen de la base de datos, del entorno, del tamaño de los campos, etc. Ojo, hay que asegurarse también de que no exista ya el índice sugerido.

 

Un par de detalles. El primero, la información estadística se elimina cuando se reinicia el servidor. Por lo que es muy importante dejar pasar un tiempo prudencial antes de ponerse a examinar y explotar estos datos. El segundo, cuando se crea un índice, se elimina toda la información relativa a la tabla en la que el índice se crea. Es mejor examinar todos los índices que se sugieren a la vez, puede que se puedan unificar en uno o crear dos, depende, pero si se crea uno, toda la información se elimina.

 

La pega puede ser pasarse. En ese caso, puedes recurrir al (2/2) cuando esté disponible y eliminar los índices que nunca fueron usados.

 

Concatenar valores en una misma consulta

Escrito por qwalgrande 05-09-2010 en General. Comentarios (3)

Es muy frecuente tener que construir un campo que concatener los valores de una columna para varios registros. Una forma clásica de realizar esta acción (es decir, que funciona también para versiones antes de SQL Server 2005) es concatenar en una variable:

 

declare @Cadena varchar(8000)

select @Cadena = ''

 

select @Cadena = @Cadena + ',' + COLUMN_NAME

from INFORMATION_SCHEMA.COLUMNS

where TABLE_NAME = 'Tabla1'

ORDER BY ORDINAL_POSITION

select STUFF(@Cadena, 1, 1, '') as CadenaCampos

Desde SQL Server 2005, se puede solventar ese problema en una sóla consulta, haciendo uso de FOR XML PATH, lo que permite una mejor integración en consultas más complejas:

 

select STUFF(

(SELECT CAST(',' AS varchar(MAX)) + COLUMN_NAME

FROM INFORMATION_SCHEMA.COLUMNS

where TABLE_NAME = 'Tabla1'

ORDER BY ORDINAL_POSITION

FOR XML PATH('')

), 1, 1, '') as CadenaCampos

 

Un ejemplo de esa integración sería el siguiente:

 

Select Table_Name, STUFF(

(SELECT CAST(',' AS varchar(MAX)) + C.COLUMN_NAME

FROM INFORMATION_SCHEMA.COLUMNS C

where C.TABLE_NAME = T.TABLE_NAME

ORDER BY ORDINAL_POSITION

FOR XML PATH('')

), 1, 1, '') as CadenaCampos

from INFORMATION_SCHEMA.TABLES T

 

En esta línea se pueden hacer otras cosas más elaboradas, como por ejemplo, construir una sentencia pivot dinámica:

 

http://social.msdn.microsoft.com/Forums/es-ES/sqlserveres/thread/d5544169-7068-4796-88ce-b6eafd0f0b8e

 

Calcular la moda y la mediana en una función

Escrito por qwalgrande 07-06-2010 en General. Comentarios (0)

A raíz de un post en el foro, me picó la curiosidad y me propuse preparar unas funciones, sino para resolver, sí al menos para servir de punto de inicio en la resolución de estos cálculos. Y existen algunos ejemplos por ahí, pero son difícilmente trasladables a ejemplos de la vida real. Para que esa traslación sea algo más sencillo, nada mejor que encapsular, en este caso, en funciones de SQL Server.

 

Antes, un poco de teoría, o al menos una de las fuentes en las que me he basado para este post.

 

http://www.vitutor.net/2/11/moda_media.html

 

Y bueno, aquí va el código. La primera es una función de apoyo. Separa una cadena de valores separados por comas y las guarda en una tabla. Práctica: 

 

CREATE function [dbo].[fdu_Desglose_Lista] (@pLista varchar(max))

returns @Detalle table (Lista varchar(max) not null) as

begin

;with csvtbl(ini_carac, fin_carac) as (

 select

    ini_carac = convert(bigint, 1),

    fin_carac = charindex(',',@pLista)

 union all

 select

    ini_carac = fin_carac + 1,

    fin_carac = charindex(',',@pLista, fin_carac + 1)

 from

  csvtbl

 where charindex(',',@pLista, fin_carac + 1) <> 0)

 

 insert @Detalle (Lista)

 select substring(@pLista, ini_carac, fin_carac - ini_carac) as Lista

 from csvtbl option(MAXRECURSION 0)

 

return

 

end;

 

Ahora el cálculo de la moda. Devuelve una tabla: 

 

create function dbo.fdu_Moda (@pLista varchar(max)) returns @Modas table (Moda numeric(12,2) null) as

begin

  insert @Modas

  select top 1 with ties

    Candidatos = cast(Lista as numeric(12,2))

  from dbo.fdu_Desglose_Lista(@pLista)

  group by cast(Lista as numeric(12,2))

  having COUNT(*) > 1 --> Si no hay valores repetidos, no hay mediana

  order by COUNT(*) desc

 

return

end;

 

El cálculo de la mediana:

 

create function dbo.fdu_Mediana (@pLista varchar(max)) returns numeric(12,2) as

begin

  declare @Median numeric(12,2)

  declare @mid int, @odd int, @mid1 int, @mid2 int, @NumRowsint

 

  select @NumRows = COUNT(*), @odd = COUNT(*) % 2, @mid = COUNT(*) / 2

  from dbo.fdu_Desglose_Lista(@pLista)

 

  if @NumRows = 1

    select @odd = 1, @mid = 1

  if @odd = 1

    begin

      select @Median = Lista

      from

        (select

           row_number() over(order by Lista) as ID,

           cast(Lista as Numeric(12,2)) as Lista

         from dbo.fdu_Desglose_Lista(@pLista)) P

      where ID = @mid

    end

  else

    begin

      select @Median = (min(Lista) + MAX(Lista)) / 2.0

      from

       (select

          ROW_NUMBER() over (order by Lista) as ID,

          cast(Lista as Numeric(12,2)) as Lista

        from dbo.fdu_Desglose_Lista(@pLista)) P

      where ID = @mid or ID = @mid + 1

    end

 

  return @Median

 

end

 

Por último, un ejemplo de llamada: 

 

declare @Lista varchar(100)

select @Lista = '1,1,2,3,4,5,6,6,7,8,9,'

select * from dbo.fdu_Moda(@Lista)

select dbo.fdu_Mediana(@Lista)