Luis Carlos Diaz

Contador Page life Expectancy

Ultimamente hemos observado uno que otro problema de memoria y hemos utilizado ese contador de MSSQL, que en castellano podria ser algo asi posible duracion de la pagina en memoria. Algunas veces ni siquiera traduzco los terminos tecnicos y creo que la mayoria lo hacemos de igual manera. Ahora bien este contador es mas menos eso; me indica que tanto va a estar alojada una pagina en memoria sin referencia alguna.

Microsoft hace referencia a que un PLE de 300 es un buen numero, el problema de este dato es que ese calculo nunca se actualizo cuando fue mejorando el hardware; por lo que ese numero puede ser bueno en equipos pequeños pero en máquinas poderosas puede estar muy lejos de la realidad.

Mi recomendación es mirar este contador con lupa y ver si de alguna manera podría tener problemas de memoria sin darse cuenta.

La siguiente consulta lista los numeros del PLE

SELECT  @@servername AS Instancia ,
        [object_name] Objeto ,
        [counter_name] Contador ,
        [cntr_value] AS PLE_SEGUNDOS ,
        [cntr_value] / 60 AS PLE_MINUTOS ,
        [cntr_value] / 3600 AS PLE_HORAS
FROM    sys.dm_os_performance_counters
WHERE   [object_name] LIKE '%Manager%'
        AND [counter_name] = 'Page life expectancy'

Leyendo varios foros encontré una formula que utilizan muchos de los MVPs de SQL Server para determinar un PLE apropiado para cada equipo:

PLE Recomendado = (Tamaño en GB del Datos en Cache/4GB *300) en ingles por aquello de la traduccion: (DataCacheSizeInGB/4GB*300), como lo dice don Jonathan Kehayias

Este query trae un valor bastante cercano a lo que deberíamos esperar o buscar en realidad:

DECLARE @PLE DECIMAL

SELECT @PLE = ( ( ( bpool_committed * 8 ) / ( 1024 * 1024 ) ) / 4 ) * 300
FROM   sys . dm_os_sys_info

SELECT 'Recommended PLE' = CASE
WHEN @PLE &lt; 300 THEN 300
ELSE @PLE
END

Como encontrar todos los triggers en mi base de datos SQL Server

Siguiendo con el post anterior, a continuacion se hace uso de las vistas de sistema para encontrar los triggers en una base de datos:\

SELECT 
    Object_name(so .parent_object_id) Parent_Name,
    so .name ObjectName,
    so .type_desc [Type Description],
    so .create_date [Create Date],
    sm.definition [Text]
FROM   sys .objects so
INNER JOIN sys. sql_modules sm
ON so.object_id = sm.object_id
WHERE  so .type = 'TR'

A partir de mi proximos posts empezare una revision de las vistas de sistema, y que cambio despues de 2005 y posteriores, que deberiamos sacar de los nuevos queries que se desarrollen tomando en cuenta que muchas vistas estan por desaparecer en futuras versions de MSSQL

Como encontrar todas las funciones de usuario (UDF) en mi base de datos SQL Server

Una de las grandes ventajas del Transact SQL es que podemos accesar la metadata para encontrar informacion relacionada a objectos que hemos creado en algun momento. El otro dia por cierto queria encontrar las funciones de una base de datos de unos de nuestros clientes. Escribi una consulta pequena que comparto a continuacion:

SELECT 
    name AS Nombre_Funcion,
    Schema_name(schema_id) AS Esquema,
    create_date            AS Fecha_Creacion,
    type_desc              Tipo_Funcion
FROM   sys.objects
WHERE  type_desc LIKE '%FUNCTION%';

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

<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

Como encontrar corrupcion en mi base de datos?

Muchas veces hacemos uso frecuente del CHECKDB como herrarmienta de verificacion de la base de datos cuando lo que tratamos de encontrar es corrupcion.

A partir de SQL Server 2005 microsoft puso a nuestra disposicion una tabla en la MSBD que nos indica que paginas estan en un estado «sospechoso» (la traduccion del ingles suspect). El motor de base de datos inserta datos en esta tabla cuando encuentra errores al leer una pagina de datos en particular.

Error 823: una prueba cíclica de redundancia (CRC) generada por el sistema operativo, por ejemplo un error de disco (ciertos errores de hardware)
Error 824: generalmente indica errores logicos

La informacion que se registra en esta tabla se guarda durante las siguientes operaciones:

Lectura rutinaria durante cualquier query (consulta)
Respaldo de base de datos (backup restore)
Operaciones de reparacion de base de datos (CHECKDB)

Es importante saber cuales son los tipos de eventos listados (event_type) dentro de la tabla:

Valor event_type	Descripción del error
1	Error 823 error de CRC del sistema operativo, o error 824 que no sea una suma de comprobación (checksum) no válida o una página con informacion incierta (por ejemplo, un Id. de página no válido)
2	Suma de comprobación errónea (checksum en Ingles)
3	Página rasgada (informacion incierta o Torn page en ingles)
4	Restaurada (la página se restauró después de marcarse como errónea)
5	Reparada (DBCC reparó la página)
7	Desasignada por DBCC

Para mi la autoridad en estos temas es Paul Randall, considero que los videos disponibles en este vinculo son de la informacion mas informativa disponible en linea.

Una consulta útil para buscar información en msdb..suspect_pages

SELECT 
    DB_NAME(database_id),
    [file_id],
    page_id,
    event_type,
    CASE event_type
        WHEN 1 THEN '823 or 824 or Torn Page'
        WHEN 2 THEN 'Bad Checksum'
        WHEN 3 THEN 'Torn Page'
        WHEN 4 THEN 'Restored'
        WHEN 5 THEN 'Repaired (DBCC)'
        WHEN 7 THEN 'Deallocated (DBCC)'
    END,
    error_count,
    last_update_date
FROM msdb..suspect_pages

Una nota importante es que la información en esta pagina se actualiza; los eventos 4, 5 y 7 son de eventos que han sido actualizados y corregidos.

Esta tabla no se limpia automáticamente por lo que una vez solucionado el problema deberíamos de borrar los datos que contenga

Opciones Ahorro de Energia; resulta que ahora Windows es ecologico

No se que tanta gente habra notado el hecho de que Microsoft nos hizo un Windows ecologico para trabajar con nuestros servidores; pues si las opciones de ahorro de energia son parte de la configuracion de nuestros servidores. Esta opcion no es del todo mala; se reduce el consumo de energia; pero debemos analizar un poco mas si queremos mantenerla en nuestros equipos cuya funcionalidad sea para aplicaciones con alto flujo de datos o bien con accesos muy frecuentes; windows automaticamente saldra del modo de ahorro de energia cuando el uso de procesador o memoria sea mayor al 45% por mas de un minuto; pero ese simple minuto puede ocacionarnos quejas de nuestros jefes.

Revisar las opciones de energia es algo que nos puede ayudar a evitar llamadas a media noche del gerente o bien quejas de nuestros clientes 😀

Esta opcion esta disponible a partir de Windows Server 2008

SQL Server WAIT STATISCTICS ()

Los contadores de SQL Server asociados o agrupados como WAIT STATS son muy útiles a la hora de diagnosticar problemas de desempeño. De su nombre en Ingles podemos sacar que esta es información de procesos que tienen que esperar por alguna razón.

Cuando alguna operación o proceso en SQL Server tiene que esperar su turno para ser ejecutado el SQLOS guarda la información referente como estas estadísticas. Las vistas que mas consultan para indagar en nuestros tiempos de espera son sys.dm_os_wait_stats y sys.dm_os_waiting_tasks. En la vista sys.dm_os_wait_stats encontramos un resumen de todas la espera que se dio durante la ejecución de cualquier tarea. En la vista sys.dm_os_waiting_tasks encontramos información de los procesos que estan esperando por un recurso en este momento. La información que esta detallada puede ser muy útil si sabemos que nos esta diciendo realmente.

Es importante saber cual son los tipos de espera (wait_type) y que nos estan diciendo, a continuación una breve explicación de los tipos mas importantes, profundizar en ellos llevaría muchísimo tiempo

CXPACKET: habitualmente indica que se esta dando paralelismo en la ejecución de alguna de nuestras consultas
LATCH_* y PAGELATCH_*: no estan relacionados con lectura/escritura en disco duro. Se pueden asociar a problemas de contención con recursos internos al SQLOS como TEMPDB especialmente cuando se usan tipos de datos tipo texto. Habitualmente cuando se trabaja y se mejora los escenarios con los otros tipos de espera se mejora la situacion los *LATCH*
PAGEIOLATCH_*,IO_COMPLETION, WRITELOG: estos tipos se asocian a cuellos de botella en el sistema de almacenamiento, un numero alto me indicaria que estamos ante un problema de contencion de lectura/escritura. El PAGEIOLATCH se asocia a problemas de lectura/escritura en los archivos de datos(data files) y el WRITELOG a problemas de escritura en el LOG de la base de datos.
LCK_*: este tipo de esperas indican que se esta dando bloqueos en el sistema, y que los procesos estan esperando para poder obtener uso exclusivo de un recurso.
THREADPOOL: Este es un tipo especial de tiempo de espera, se da a lo interno del SQLOS; indica que el proceso ha tenido que esperar para obtener un hilo para entrar en la cola de procesamiento. Puede darse en casos de muchos bloqueos en los cuales se da mucho paralelismo en las tareas que se estan ejecutando y el SQLOS se queda sin hilos (worker threads) o bien indica la necesidad de adicionar procesadores a nuestro sistema.
SOS_SCHEDULER_YIELD: indica que la consulta cumplió su tiempo de ejecución en el procesador y cedio el espacio al siguiente proceso en cola. Valores altos habitualmente indican problemas de CPU
ASYNC_NETWORK_IO: habitualmente se define como un cuello de botella en la red; pero esto no es realmente valido. Muchas veces se puede ver cuando un cliente esta procesando una llamada registro a registro; cambiar este comportamiento involucra cambiar el código de la aplicación.

Para tener mas informacion se puede acceder a la pagina de Microsoft o bien me pueden escribir y con gusto tratare de ayudarles

TECHNET WAIT STATISTICS

Monitorear el uso de Procedimientos Almacenados

Hace un tiempo habia encontrado estas consultas para monitorear el uso de procedimientos almacenados en SQL 2005 y posterior. Los comparto porque siempre es util conocer que tanto y que tan frecuentemente se ejecutan nuestros procedimientos almacenados.

1. [Conteo Total de Ejecuciones]

SELECT 
    Db_name(st.dbid) [Base de Datos],
    Object_schema_name(st.objectid, dbid) [Schema],
    Object_name(st.objectid, dbid)        [USP],
    Max(cp.usecounts)                     [Total Ejecuciones]
FROM   
    sys.dm_exec_cached_plans cp
    CROSS apply sys.Dm_exec_sql_text(cp.plan_handle) st
WHERE  
    Db_name(st.dbid) IS NOT NULL
    AND cp.objtype = 'proc'
GROUP  BY 
    cp.plan_handle,
    Db_name(st.dbid),
    Object_schema_name(objectid, st.dbid),
    Object_name(objectid, st.dbid)
ORDER  BY 
        Max(cp.usecounts)

2. [Procedimiento que Consume mas CPU]

 SELECT 
    Db_name(st.dbid)
    [Base de Datos, Object_schema_name(st.objectid, dbid) [Schema],
    Object_name(st.objectid, dbid) [USP],
    Max(cp.usecounts) [Total Ejecuciones],
    Sum(qs.total_worker_time) [Consumo Total CPU],
    Sum(qs.total_worker_time) / ( Max(cp.usecounts) * 1.0 )    [Consumo Promedio CPU]
FROM   
    sys.dm_exec_cached_plans cp
    INNER JOIN sys.dm_exec_query_stats qs
        ON cp.plan_handle = qs.plan_handle
    CROSS apply sys.Dm_exec_sql_text(cp.plan_handle) st
WHERE  
    Db_name(st.dbid) IS NOT NULL
    AND cp.objtype = 'proc'
GROUP  BY 
    Db_name(st.dbid),
    Object_schema_name(objectid, st.dbid),
    Object_name(objectid, st.dbid)
ORDER  BY 
    Sum(qs.total_worker_time) DESC

3. [Procedimientos con mayor tiempo de ejecucion]

SELECT 
    Db_name(st.dbid) [Base de Datos, 
    Object_schema_name(objectid, st.dbid) [Schema],
    Object_name(objectid, st.dbid) [USP],
    Max(cp.usecounts) [Total Ejecuciones],
    Sum(qs.total_elapsed_time) [Tiempo Total Ejecuciones],
    Sum(qs.total_elapsed_time) / Max(cp.usecounts) [Tiempo Promedio Ejecuciones]
FROM   
    sys.dm_exec_query_stats qs
    CROSS apply sys.Dm_exec_sql_text(qs.plan_handle) st
    INNER JOIN sys.dm_exec_cached_plans cp
        ON qs.plan_handle = cp.plan_handle
WHERE  
    Db_name(st.dbid) IS NOT NULL
    AND cp.objtype = 'proc'
GROUP  BY 
    Db_name(st.dbid),
    Object_schema_name(objectid,st.dbid),
    Object_name(objectid, st.dbid)
ORDER  BY 
    Sum(qs.total_elapsed_time) DESC

Fuente SQL Journal

Cual es el progreso de mi respaldo de base de datos (backup)?

Saber cual es el progreso o porcentaje de ejecución de un respaldo nativo en SQL Server es tan simple como ejecutar una consulta sobre una de las tan útiles vistas del sistema. En este caso particular sobre la vista sys.dm_exec_requests que devuelve información de lo que se esta ejecutando en SQL Server.

El código que cito a continuación lista el identificador de la sesión, la tarea que esta ejecutando, el porcentaje completado y un estimado de cuanto tiempo tendremos que esperar para que la tarea termine en minutos; nótese que este tiempo es un estimado

--Progreso de mis backups
SELECT 
    dm_er.session_id id_Sesion,
    dm_er.command Comando,
    CONVERT(NUMERIC(6, 2), dm_er.percent_complete) AS [Porcentaje Completedo],
    CONVERT(VARCHAR(20), Dateadd(ms, dm_er.estimated_completion_time, Getdate()),20) AS [Tiempo Estimado Finalizacion],
    CONVERT(NUMERIC(6, 2), dm_er.estimated_completion_time / 1000.0 / 60.0) AS [Minutos pendientes]
FROM
    sys.dm_exec_requests dm_er
WHERE  
    dm_er.command IN ( 'RESTORE VERIFYON', 'RESTORE DATABASE','BACKUP DATABASE','RESTORE LOG','BACKUP LOG', 'RESTORE HEADERON' )

Hoy tuve la suerte de que un buen amigo me recordara que no es bueno olvidar las fuentes, y este código no es de mi autoría, el mismo lo encontré por primera vez por el 2010; los cambios que le he hecho son pocos, hacer un tipo de dato mas grande para evitar desbordamientos, etiquetas, agregar procesos en ejecución. El mismo lo encontré por primera vez en esta pagina; siendo utilizado por mis compañeros de trabajo en general

http://www.sp-configure.com/estimate-backuprestore-times/