sys.dm_os_performance_counters

La performance de un servidor SQL Server 2005 puede ser monitoreada utilizando los contadores de performance. Para lo cual podemos usar el System Monitor (perfmon) o, a partir de SQL Server 2005, utilizando la Dynamic Management View (DMV a partir de ahora) con sys.os_exec_performance_counters.

Algunos contadores útiles (en otro posteo indicaré como analizar estos contadores):
SQLServer:Buffer Partition
SQLServer:User Settable
SQLServer:Databases
SQLServer:CLR
SQLServer:Cursor Manager by Type
SQLServer:Exec Statistics
SQLServer:Transactions
SQLServer:Memory Manager
SQLServer:SQL Errors
SQLServer:Buffer Node
SQLServer:Plan Cache
SQLServer:Access Methods
SQLServer:Cursor Manager Total
SQLServer:Broker Activation
SQLServer:Latches
SQLServer:Wait Statistics
SQLServer:Broker/DBM Transport
SQLServer:General Statistics
SQLServer:SQL Statistics
SQLServer:Catalog Metadata
SQLServer:Broker Statistics
SQLServer:Locks
SQLServer:Buffer Manage
En SQL Server 2000 podíamos obtener la información desde la tabla master.dbo.sysperfinfo. En 2005 se nos provee con una vista que representa esta tabla, solo a los efectos de mantener compatibilidad con codificaciones previas, pero en 2005 usted debiera usar el DMV “sys.os_exec_performance_counters”.

Para Finalizar un ejemplo:
Vamos a crear un script para calcular el "buffer cache hit ratio", mientras mas cercano a 100% mejor el valor, ya que indicaría que el buffer cache está siendo utilizado de manera óptima y que las páginas permanecen en el cache de buffer. Y por ende la performance de su servidor será mejor.
Create Proc dbo.P_ContadorBufferCacheHit
as
SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 [BufferCacheHitRatio]
FROM (SELECT *, 1 x FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio'
AND object_name = 'SQLServer:Buffer Manager') a
JOIN
(SELECT *, 1 x FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio base'
AND object_name = 'SQLServer:Buffer Manager') b
GO

Hugo Román Bernachea
Mail de contacto: SQLServer777@gmail.com

Microsoft Certified DBA
Microsoft Certified Trainer
Twitter: @bernachea


Read More...

Un Stored para ejecutar en modo DAC (Conexión Administrativa Dedicada)

Como todos bien sabemos, en SQL Server 2005 disponemos de las DAC, Conexiones administrativas dedicadas, para ejecutar distintos tipos de tareas en caso de encontrarnos con fallos o problemas en nuestros servidores SQL Server 2005.

La pregunta es, que es lo que podríamos ejecutar para tener un vistazo general de los problemas de los servidores?

Googleando por ahí encontré el siguiente Stored que me parece muy util de tener creado como para poder ejecutar en modo DAC y obtener la información que estamos buscando.

Script---->

USE master
GO

-- Este Stored nos dará la info de los servidores en cuestión.
-- Conectar en modo DAC y ejecutar el Stored que YA existirá de ANTEMANO en la base

CREATE PROC dbo.p_Info_Servidor
AS

SELECT '*** comienzo de informe DAC ***'

SELECT '-- Mostrar SQL Server Info'
EXEC ('USE MASTER')

SELECT
CONVERT(char(20), SERVERPROPERTY('MachineName')) AS 'Nombre Maquina',
CONVERT(char(20), SERVERPROPERTY('ServerName')) AS 'Nombre SQL Server',

(CASE WHEN CONVERT(char(20), SERVERPROPERTY('InstanceName')) IS NULL
THEN 'Instancia Predeterminada'
ELSE CONVERT(char(20), SERVERPROPERTY('InstanceName'))
END) AS 'Nombre de Instancia',

CONVERT(char(20), SERVERPROPERTY('EDITION')) AS Edicion,
CONVERT(char(20), SERVERPROPERTY('ProductVersion')) AS 'Version',
CONVERT(char(20), SERVERPROPERTY('ProductLevel')) AS 'Level',

(CASE WHEN CONVERT(char(20), SERVERPROPERTY('ISClustered')) = 1
THEN 'Clustered'
WHEN CONVERT(char(20), SERVERPROPERTY('ISClustered')) = 0
THEN 'NOT Clustered'
ELSE 'INVALID INPUT/ERROR'
END) AS 'FAILOVER CLUSTERED',

(CASE WHEN CONVERT(char(20), SERVERPROPERTY('ISIntegratedSecurityOnly')) = 1
THEN 'Seguridad Integrada '
WHEN CONVERT(char(20), SERVERPROPERTY('ISIntegratedSecurityOnly')) = 0
THEN 'Seguridad SQL Server '
ELSE 'INVALID INPUT/ERROR'
END) AS 'SECURITY',

(CASE WHEN CONVERT(char(20), SERVERPROPERTY('ISSingleUser')) = 1
THEN 'Single User'
WHEN CONVERT(char(20), SERVERPROPERTY('ISSingleUser')) = 0
THEN 'Multi User'
ELSE 'INVALID INPUT/ERROR'
END) AS 'USER MODE',

CONVERT(char(30), SERVERPROPERTY('COLLATION')) AS COLLATION

SELECT '-- Mostrar las 5 sentencias mas consumidoras'
SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC;

SELECT '-- Mostrar quienes están logeados'
SELECT login_name ,COUNT(session_id) AS session_count
FROM sys.dm_exec_sessions
GROUP BY login_name;

SELECT '-- Mostrar cursores con tiempos extensos de ejecución'
EXEC ('USE master')

SELECT creation_time ,cursor_id
,name ,c.session_id ,login_name
FROM sys.dm_exec_cursors(0) AS c
JOIN sys.dm_exec_sessions AS s
ON c.session_id = s.session_id
WHERE DATEDIFF(mi, c.creation_time, GETDATE()) > 5;

SELECT '-- Mostrar sesiones con transacciones abiertas'
SELECT s.*
FROM sys.dm_exec_sessions AS s
WHERE EXISTS
(
SELECT *
FROM sys.dm_tran_session_transactions AS t
WHERE t.session_id = s.session_id
)
AND NOT EXISTS
(
SELECT *
FROM sys.dm_exec_requests AS r
WHERE r.session_id = s.session_id
);

SELECT '-- Mostrar espacio libre en tempdb '
SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;

SELECT '-- Mostrar espacio ocupado por tempdb'
SELECT SUM(size)*1.0/128 AS [size in MB]
FROM tempdb.sys.database_files

SELECT '-- Mostrar jobs activos'
SELECT DB_NAME(database_id) AS [Database], COUNT(*) AS [Active Async Jobs]
FROM sys.dm_exec_background_job_queue
WHERE in_progress = 1
GROUP BY database_id;

SELECT '--Mostrar clientes conectados'
SELECT session_id, client_net_address, client_tcp_port
FROM sys.dm_exec_connections;

SELECT '--Mostrar batchs en ejecución'
SELECT * FROM sys.dm_exec_requests;

SELECT '--Mostrar request actualmente bloqueados'
SELECT session_id ,status ,blocking_session_id
,wait_type ,wait_time ,wait_resource
,transaction_id
FROM sys.dm_exec_requests
WHERE status = N'suspended'

SELECT '--Mostrar fechas de ultimos backups ' as ' '
SELECT B.name as Database_Name,
ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
MAX(Backup_finish_date)))), 'NEVER')
as DaysSinceLastBackup,
ISNULL(Convert(char(10),
MAX(backup_finish_date), 101), 'NEVER')
as LastBackupDate

FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A
ON A.database_name = B.name AND A.type = 'D' GROUP BY B.Name ORDER BY B.name

SELECT '--Mostrar jobs que están todavía en ejecución' as ' '
exec msdb.dbo.sp_get_composite_job_info NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL

SELECT '--Mostrar informe de Jobs fallidos ' as ' '
SELECT name FROM msdb.dbo.sysjobs A, msdb.dbo.sysjobservers B WHERE A.job_id = B.job_id AND B.last_run_outcome = 0

SELECT '--Mostrar jobs deshabilitados ' as ' '
SELECT name FROM msdb.dbo.sysjobs WHERE enabled = 0 ORDER BY name

SELECT '--Mostrar espacio disponible de BD ' as ' '
exec sp_MSForEachDB 'Use ? SELECT name AS ''Name of File'', size/128.0 -CAST(FILEPROPERTY(name, ''SpaceUsed'' )
AS int)/128.0 AS ''Espacio disponible en MB'' FROM .SYSFILES'

SELECT '--Mostrar total DB size (.MDF+.LDF)' as ' '
set nocount on
declare @name sysname
declare @SQL nvarchar(600)
-- Use temporary table to sum up database size w/o using group by
create table #databases (
DATABASE_NAME sysname NOT NULL,
size int NOT NULL)
declare c1 cursor for
select name from master.dbo.sysdatabases
-- where has_dbaccess(name) = 1 -- Only look at databases to which we have access
open c1
fetch c1 into @name

while @@fetch_status >= 0
begin
select @SQL = 'insert into #databases
select N'''+ @name + ''', sum(size) from '
+ QuoteName(@name) + '.dbo.sysfiles'
-- Insert row for each database
execute (@SQL)
fetch c1 into @name
end
deallocate c1

select DATABASE_NAME, DATABASE_SIZE_MB = size*8/1000 -- Convert from 8192 byte pages to K and then convert to MB
from #databases order by 1

select SUM(size*8/1000)as '--Shows disk space used - ALL DBs - MB ' from #databases

drop table #databases

SELECT '--Mostrar espacio disponible en disco ' as ' '
EXEC master..xp_fixeddrives

SELECT '*** Fin de informes **** '

GO

A este stored le pueden agregar las sentencias que crean convenientes, pero pueden tomar este script como base.

Hugo Román Bernachea
Mail de contacto: SQLServer777@gmail.com

Microsoft Certified DBA
Microsoft Certified Trainer
Twitter: @bernachea

Read More...

Como determinar índices faltantes en SQL Server 2005??

En SQL Server 2005 existen unas nuevas vistas dinámicas que nos facilitan el proceso de determinar que índices optimizarían el rendimiento de nuestras consultas:

sys.dm_db_missing_index_group_stats Regresa información acerca de grupos de índices no existentes, por ejemplo, la performance que se podría obtener implementando un grupo específico de índices.
sys.dm_db_missing_index_groups Regresar información acerca de un grupo específico de indices no declarados, como el identificador de grupo y el identificador de todos los índices que están contenidos en dicho grupo.
sys.dm_db_missing_index_details Devuelve información detallada acerca de un posible índice a ser creado, por ejemplo nombre e identificador de la tabla donde el índice podría ser creado y las columnas y tipos que conformarían dicho índice.
sys.dm_db_missing_index_columns Devuelve info acerca de los campos que podrían conformar un índice.that are missing an index.

Cada vez que SQL ejecuta una consulta, internamente determina si esa consulta podía haber sido optimizada con el uso de algún índice inexistente al momento del query (por eso es missing index) y cuando ejecutemos algunas de estas vistas dinámicas nos dará dicha información.

Nada mas y nada menos.

Hugo Román Bernachea
Mail de contacto: SQLServer777@gmail.com

Microsoft Certified DBA
Microsoft Certified Trainer
Twitter: @bernachea

Read More...