Monitoreo de Base de Datos

Con el fin de monitorear nuestra Base de Datos, para saber qué está ocurriendo en el preciso momento y tomar medidas para mejorar performance, seguridad o simplemente "auditar" a los usuarios de las bases de datos.



Script N1: Ayuda a saber qué se está ejecutando es este preciso momento en la BD.
NOTA: Cambiar el texto "DBNameaqui" dentro del código por el de la base de datos que deseamos monitorear
**********************************************************************
DECLARE @TABLA1 TABLE (
SPID varchar(max),ClientAddress varchar(max),Login varchar(max),Host varchar(max),SQLStatement varchar(max),Executions varchar(max),Blocking_By varchar(max),
StartTime varchar(max),ElapsedSEG varchar(max),ElapsedMIN varchar(max),CPU varchar(max),IOReads varchar(max),IOWrites varchar(max),ObjectName varchar(max),
objectid varchar(max),status varchar(max),CommandType varchar(max),Status_2 varchar(max),DBName varchar(max),LastWaitType varchar(max),ElapsedMS varchar(max),
Protocol varchar(max),transaction_isolation varchar(max),ConnectionWrites varchar(max),ConnectionReads varchar(max),ClientAddress_2 varchar(max),
Authentication varchar(max)
)

INSERT INTO @TABLA1
SELECT
SPID                = er.session_id
,ClientAddress      = con.client_net_address
,[Login]            = ses.login_name
,Host               = ses.host_name
,SQLStatement       =
SUBSTRING
(
qt.text,
er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
ELSE er.statement_end_offset
END - er.statement_start_offset)/2
) 
,Executions         = ec.execution_count
,Blocking_By              = er.blocking_session_id
,StartTime          = er.start_time
,ElapsedSEG          = ((CAST (er.total_elapsed_time AS FLOAT))/1000)
,ElapsedMIN          = ((CAST(er.total_elapsed_time AS FLOAT))/1000)/60
,CPU                = er.cpu_time
,IOReads            = er.logical_reads + er.reads
,IOWrites           = er.writes
,ObjectName         = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)
,objectid
,status                        = ses.status
,CommandType        = er.command
,Status             = ses.status
,DBName             = DB_Name(er.database_id)
,LastWaitType       = er.last_wait_type
,ElapsedMS          = er.total_elapsed_time
,Protocol           = con.net_transport
,transaction_isolation =
CASE ses.transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'Read Uncommitted'
WHEN 2 THEN 'Read Committed'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END
,ConnectionWrites   = con.num_writes
,ConnectionReads    = con.num_reads
,ClientAddress      = con.client_net_address
,Authentication     = con.auth_scheme
FROM DBNameaqui.sys.dm_exec_requests er
LEFT JOIN DBNameaqui.sys.dm_exec_sessions ses
ON ses.session_id = er.session_id
LEFT JOIN DBNameaqui.sys.dm_exec_connections con
ON con.session_id = ses.session_id
CROSS APPLY DBNameaqui.sys.dm_exec_sql_text(er.sql_handle) as qt
OUTER APPLY
(
SELECT execution_count = MAX(cp.usecounts)
FROM DBNameaqui.sys.dm_exec_cached_plans cp
WHERE cp.plan_handle = er.plan_handle
) ec
where ses.session_id != @@SPID
ORDER BY
8 desc,
--er.blocking_session_id DESC,
er.logical_reads + er.reads DESC,
er.session_id
**********************************************************************


Script N2: Consulta que muestra la cantidad de tablas temporales existentes, así como sus detalles de No. Registros, Tamaño, Nombre, etc.
**********************************************************************

SELECT DISTINCT replace(substring(REPLACE(TBL.name,'__','.'),1,50),'.','') as SmallName
,TBL.name AS ObjName
,TBL.object_id
  ,STAT.row_count AS StatRowCount
  ,STAT.used_page_count * 8 AS UsedSizeKB
  ,STAT.reserved_page_count * 8 AS RevervedSizeKB
  ,TBL.create_date
  ,TBL.modify_date
FROM tempdb.sys.partitions AS PART
INNER JOIN tempdb.sys.dm_db_partition_stats AS STAT
ON PART.partition_id = STAT.partition_id
AND PART.partition_number = STAT.partition_number
INNER JOIN tempdb.sys.tables AS TBL
ON STAT.object_id = TBL.object_id
where len(name) >9
ORDER BY 8 desc;
**********************************************************************