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;
**********************************************************************

Todas las consultas de SQL Server que necesitabas

TRICKS AND TIPS SQL SERVER--Conocer los detalles de un S

SELECT *

FROM sys.objects

WHERE type = 'P'

AND name = 'SP_NAME'

USE AdventureWorks2012;

GO

EXEC sp_helptext 'HumanResources.dEmployee';

GO

***********************************

--Cambiar el tipo de dato de un campo en una tabla

ALTER TABLE NombreTabla

ALTER COLUMN CampoTabla int

***********************************

--Agregar un campo a una tabla

ALTER TABLE TABLA ADD CAMPO1 VARCHAR(20) NULL, CAMPO2 INT NULL ;

********************************

--Eliminar Columnas a tabla

ALTER TABLE TABLA DROP COLUMN columna_eliminar ;

********************************

--ELIMINAR LLAVE FORANEA

ALTER TABLE TABLA 

DROP CONSTRAINT FK_Column_B; 

GO

******************************

--Crear llave foranea

ALTER TABLE Tabla_inicial 

ADD CONSTRAINT FK_Nombre_Llave FOREIGN KEY (Campo_Tabla_inicial)   

    REFERENCES Tabla_final

(Campo_Tabla Final)   

    ON DELETE CASCADE 

    ON UPDATE CASCADE;

************************************

--Consultar detalles de un SP

--Buscar un Objeto en la base de datos

SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Nombre_obj]')

***********************************

--TrustWorthy--

ALTER DATABASE NOMBREDB SET TRUSTWORTHY ON;

**********************************

--Respaldar una base de datos

use AdventureWorks2012 

go

BACKUP DATABASE AdventureWorks2012 

 TO DISK = 'Z:\SQLServerBackups\AdvWorksData.bak'

   WITH FORMAT;

GO

*******************************

--CONOCER EL NUMERO DE FILAS DE CADA TABLA DE UNA BASE DE DATOS

use [AdventureWorks2014]

go

select si.rows as 'filas', SO.Name as Tabla, SI.name as 'Index', SFG.groupname as 'Filegroup'

from sysobjects as SO

    join sysindexes as SI on SO.Id = SI.id

    join sysfilegroups as SFG on SI.GroupId = SFG.GroupId

order by si.rows desc, SO.Name , SI.name, SFG.GroupName

***************************

--cARGAR CSV A TABLAS

use [AdventureWorks2014]

go

BULK

INSERT [HumanResources].[JobCandidate]

   FROM 'C:\Users\jonathanm\Desktop\ADwOR\JobCandidate.csv'

   WITH (

    FIELDTERMINATOR= ',',

    ROWTERMINATOR = '\n'

);

go

select * from [HumanResources].[JobCandidate]

**********************************

-- Encontrar un campo en una base de datos

SELECT TABLE_NAME,*

FROM INFORMATION_SCHEMA.COLUMNS

WHERE COLUMN_NAME LIKE '%meta%'

*****************************************************

--Renombrar una base de datos

USE master;

GO

ALTER DATABASE AdventureWorks2014

Modify Name = AdventureWorks ;

GO

****************************************************

----FORZAR INSERCIÓN DE IDENTITY

SET IDENTITY_INSERT -TABLA- ON

insert into -TABLA- values (20, value', getdate(),’value' ,NULL, 1)

SET IDENTITY_INSERT -TABLA- OFF

*****************************************************

--PONER EN OFF LINE Y ONLINE UNA BASE DE DATOS

 ALTER DATABASE YourDatabaseName  SET OFFLINE WITH ROLLBACK IMMEDIATE

 GO

USE master

 GO

 ALTER DATABASE YourDatabaseName SET ONLINE

GO

*******************************************************

--Comprabar existencia de una tabla

IF NOT EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID('[dbo].[tabla]'))

*******************************************************

--Comprobar si existe un campo en una tabla

IF NOT EXISTS (SELECT * FROM SYS.columns  WHERE name='campo' AND object_id = OBJECT_ID(N'[dbo].[tabla]'))

*******************************************************

--Comprobar si existe un SP

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Nombre_SP]'))

*******************************************************

--Comprobar permisos de un usuario

EXEC sp_helprotect NULL,'Usuario';

********************************************************

--reducir log de una BD

USE AdventureWorks2012;

GO

-- Truncate the log by changing the database recovery model to SIMPLE.

ALTER DATABASE AdventureWorks2012

SET RECOVERY SIMPLE;

GO

-- Shrink the truncated log file to 1 MB.

DBCC SHRINKFILE (AdventureWorks2012_Log, 1);

GO

-- Reset the database recovery model.

ALTER DATABASE AdventureWorks2012

SET RECOVERY FULL;

GO

************************************************************

--BUSCAR UN DATO EN TODA LA BASE DE DATOS

CREATE PROCEDURE BuscaValorEnBBDD

(

@StrValorBusqueda nvarchar(100)

)

AS

BEGIN

CREATE TABLE #Resultado (NombreColumna nvarchar(370), ValorColumna nvarchar(3630))

SET NOCOUNT ON DECLARE @NombreTabla nvarchar(256),

@NombreColumna nvarchar(128),

@StrValorBusqueda2 nvarchar(110)

SET @NombreTabla = ''

SET @StrValorBusqueda2 = QUOTENAME('%' + @StrValorBusqueda + '%','''')

WHILE @NombreTabla IS NOT NULL

BEGIN

SET @NombreColumna = ''

SET @NombreTabla =

(SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE'

AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @NombreTabla

AND OBJECTPROPERTY(

OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0)

WHILE (@NombreTabla IS NOT NULL) AND (@NombreColumna IS NOT NULL)

BEGIN

SET @NombreColumna =

(SELECT MIN(QUOTENAME(COLUMN_NAME))

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA = PARSENAME(@NombreTabla, 2)

AND TABLE_NAME = PARSENAME(@NombreTabla, 1)

AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')

AND QUOTENAME(COLUMN_NAME) > @NombreColumna)

IF @NombreColumna IS NOT NULL

BEGIN

INSERT INTO #Resultado

EXEC

('SELECT ''' + @NombreTabla + '.' + @NombreColumna + ''', LEFT(' + @NombreColumna + ', 3630)

FROM ' + @NombreTabla + ' (NOLOCK) ' + ' WHERE ' + @NombreColumna + ' LIKE ' + @StrValorBusqueda2)

END

END

END

SELECT NombreColumna, ValorColumna FROM #Resultado

END

Exec BuscaValorEnBBDD ‘PALABRA O VALOR A BUSCAR’

*****************************************************************************************

--BUSCAR TODAS LAS llaves primarias DE TODAS LAS TABLAS DE UNA BASE DE DATOS

SELECT i.name AS NombreIndice,

OBJECT_NAME(ic.OBJECT_ID) AS NombreTabla,

COL_NAME(ic.OBJECT_ID,ic.column_id) AS NombreColumna

FROM sys.indexes AS i

INNER JOIN sys.index_columns AS ic ON i.OBJECT_ID = ic.OBJECT_ID

AND i.index_id = ic.index_id and i.is_primary_key = 1

*****************************************************************************************

-- Buscar TODAS LAS columnas identity en base de datos

SELECT SCHEMA_NAME(OBJECTPROPERTY(OBJECT_ID,'SchemaId')) AS SchemaName,

       OBJECT_NAME(OBJECT_ID) AS TableName,

       name AS ColumnName

FROM  SYS.COLUMNS

WHERE is_identity = 1

ORDER BY SchemaName, TableName, ColumnName

*********************************************************************************
--CONOCER EL NÚMERO TOTAL DE REGISTROS DE TU BASE DE DATOS:

USE [MI_BASE]
GO

SELECT
           QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS                [TABLA], SUM(sPTN.Rows) AS [REGISTROS]
     INTO #TMPCount
FROM
      sys.objects AS sOBJ
      INNER JOIN sys.partitions AS sPTN
            ON sOBJ.object_id = sPTN.object_id
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
      AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY
      sOBJ.schema_id
      , sOBJ.name
ORDER BY registros desc
GO

select sum ([REGISTROS]) [TOTAL_REG]from #TMPCount
drop table #TMPCount


**********************************************************************
---CÓDIGO TRANSACCIONAL CON MANEJO DE ERRORES Y ROLLBACK EN CASO DE ERROR

BEGIN TRANSACTION;

BEGIN TRY

             --------------------------------------------------
             -------COLOCAR AQUÍ EL SCRIPT---------------------
             --------NO USAR "GO"------------------------------
             --------------------------------------------------

END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

**********************************************************************