Conjunto de consultas Administrativas SQL Server: Consultas Administrativas SQL para

JuanCastillo 29 marzo, 20179 noviembre, 2017 2 comentarios DBA

QUERYS SQLISTO

Consultas para administrar una base de datos SQL Server

En la actualidad muchas personas, que no son DBA, requieren de Consultas Administrativas SQL, para lograr obtener metricas de informacion, por lo cual crearemos un conjunto de informacion con las mas relevantes para mi, mas adelante colocare muchas mas

Consultas Administrativas SQL

Numero 1:

Top N de consultas ejecutadas en una base de datos en Particular, esta genial para realizar auditorias en nuestra base de datos

SELECT TOP 10 execquery.last_execution_time AS [Date Time] ,execsql.TEXT AS [Script]

FROM sys.dm_exec_query_stats AS execquery

CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql WHERE execsql.dbid = DB_ID()

ORDER BY execquery.last_execution_time DESC

Numero 2:

Fechas de objetos modificados en nuestra base de datos

SELECT *

FROM sys.objects

ORDER BY modify_date DESC

Numero 3:

Inspeccionar usuarios que estan ejecutando una consulta en tiempo real, genial para saber quien esta usando mas la base de datos y cual es su proceso activo

SELECT s.session_id AS SessionID
 ,s.login_time AS LoginTime
 ,s.[host_name] AS HostName
 ,s.[program_name] AS ProgramName
 ,s.login_name AS LoginName
 ,s.[status] AS SessionStatus
 ,st.TEXT AS SQLText
 ,(s.cpu_time / 1000) AS CPUTimeInSec
 ,(s.memory_usage * 8) AS MemoryUsageKB
 ,(CAST(s.total_scheduled_time AS FLOAT) / 1000) AS TotalScheduledTimeInSec
 ,(CAST(s.total_elapsed_time AS FLOAT) / 1000) AS ElapsedTimeInSec
 ,s.reads AS ReadsThisSession
 ,s.writes AS WritesThisSession
 ,s.logical_reads AS LogicalReads
 ,CASE s.transaction_isolation_level
  WHEN 0
   THEN 'Unspecified'
  WHEN 1
   THEN 'ReadUncommitted'
  WHEN 2
   THEN 'ReadCommitted'
  WHEN 3
   THEN 'Repeatable'
  WHEN 4
   THEN 'Serializable'
  WHEN 5
   THEN 'Snapshot'
  END AS TransactionIsolationLevel
 ,s.row_count AS RowsReturnedSoFar
 ,c.net_transport AS ConnectionProtocol
 ,c.num_reads AS PacketReadsThisConnection
 ,c.num_writes AS PacketWritesThisConnection
 ,c.client_net_address AS RemoteHostIP
 ,c.local_net_address AS LocalConnectionIP
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS st
WHERE s.is_user_process = 1
 AND [status] = 'running'
ORDER BY ElapsedTimeInSec
 ,LoginTime DESC

SELECT s.session_id AS SessionID

,s.login_time AS LoginTime

,s.[host_name] AS HostName

,s.[program_name] AS ProgramName

,s.login_name AS LoginName

,s.[status] AS SessionStatus

,st.TEXT AS SQLText

,(s.cpu_time / 1000) AS CPUTimeInSec

,(s.memory_usage * 8) AS MemoryUsageKB

,(CAST(s.total_scheduled_time AS FLOAT) / 1000) AS TotalScheduledTimeInSec

,(CAST(s.total_elapsed_time AS FLOAT) / 1000) AS ElapsedTimeInSec

,s.reads AS ReadsThisSession

,s.writes AS WritesThisSession

,s.logical_reads AS LogicalReads

,CASE s.transaction_isolation_level

WHEN 0

THEN 'Unspecified'

WHEN 1

THEN 'ReadUncommitted'

WHEN 2

THEN 'ReadCommitted'

WHEN 3

THEN 'Repeatable'

WHEN 4

THEN 'Serializable'

WHEN 5

THEN 'Snapshot'

END AS TransactionIsolationLevel

,s.row_count AS RowsReturnedSoFar

,c.net_transport AS ConnectionProtocol

,c.num_reads AS PacketReadsThisConnection

,c.num_writes AS PacketWritesThisConnection

,c.client_net_address AS RemoteHostIP

,c.local_net_address AS LocalConnectionIP

FROM sys.dm_exec_sessions s

INNER JOIN sys.dm_exec_connections c ON s.session_id = c.session_id

CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS st

WHERE s.is_user_process = 1

AND [status] = 'running'

ORDER BY ElapsedTimeInSec

,LoginTime DESC

Numero 4:

Detectar bloqueos en la base de datos, (Imaginen que tienen una consulta y no responde, quiza existe otro proceso usando las mismas tablas y tienen bloqueada la tabla, con esto podras saber si te estan bloquendo tu consulta)

SELECT db.NAME DBName

,tl.request_session_id

,wt.blocking_session_id

,OBJECT_NAME(p.OBJECT_ID) BlockedObjectName

,tl.resource_type

,h1.TEXT AS RequestingText

,h2.TEXT AS BlockingTest

,tl.request_mode

FROM sys.dm_tran_locks AS tl

INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id

INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address

INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id

INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id

INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id

CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1

CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2

Numero 5:

Cuando fue la ultima vez que se hizo un respaldo de mi base de datos, te lo tengo

SELECT db.NAME

,CASE

WHEN MAX(b.backup_finish_date) IS NULL

THEN 'No Backup'

ELSE convert(VARCHAR(100), MAX(b.backup_finish_date))

END AS last_backup_finish_date

FROM sys.databases db

LEFT JOIN msdb.dbo.backupset b ON db.NAME = b.database_name

AND b.type = 'D'

WHERE db.database_id NOT IN (2)

GROUP BY db.NAME

ORDER BY 2 DESC

Numero 6:

Cual es la version de este servidor SQL server, en SQListo te lo tenemos

SELECT SERVERPROPERTY('MachineName') AS Host

,SERVERPROPERTY('InstanceName') AS Instance

,SERVERPROPERTY('Edition') AS Edition

,/*shows 32 bit or 64 bit*/

SERVERPROPERTY('ProductLevel') AS ProductLevel

,/* RTM or SP1 etc*/

CASE SERVERPROPERTY('IsClustered')

WHEN 1

THEN 'CLUSTERED'

ELSE 'STANDALONE'

END AS ServerType

,@@VERSION AS VersionNumber

Numero 7:

Un DBA quiere saber que configuración tiene el server procesadores memoria timeout

SELECT *

FROM sys.configurations

ORDER BY NAME

Numero 8:

Quieres saber quien tiene permisos administrativos en una base de datos?

SELECT l.NAME

,l.denylogin

,l.isntname

,l.isntgroup

,l.isntuser

FROM master.dbo.syslogins l

WHERE l.sysadmin = 1

OR l.securityadmin = 1

Numero 9:

Indices faltantes y si ordenados por nivel de importancia para nuestra base de datos SQL

SELECT TOP 20 ROUND(s.avg_total_user_cost * s.avg_user_impact *

(s.user_seeks + s.user_scans), 0) AS [Total Cost]

,d.[statement] AS [Table Name]

,equality_columns

,inequality_columns

,included_columns

FROM sys.dm_db_missing_index_groups g

INNER JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle =

g.index_group_handle

INNER JOIN sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle

ORDER BY [Total Cost] DESC

Numero 10:

Indices fragmentados, recordar que ellos son importantes para el correcto funcionamiento de nuestras consulta

SELECT object_name(IPS.object_id) AS [TableName]

,SI.NAME AS [IndexName]

,IPS.Index_type_desc

,IPS.avg_fragmentation_in_percent

,IPS.fragment_count

,IPS.avg_fragment_size_in_pages

,alloc_unit_type_desc

FROM sys.dm_db_index_physical_stats(db_id(N'AdventureWorks2012'), NULL, NULL, NULL,

'LIMITED') IPS

INNER JOIN sys.tables ST WITH (NOLOCK) ON IPS.object_id = ST.object_id

INNER JOIN sys.indexes SI WITH (NOLOCK) ON IPS.object_id = SI.object_id

AND IPS.index_id = SI.index_id

WHERE ST.is_ms_shipped = 0

AND index_type_desc IN (

'NONCLUSTERED INDEX'

,'CLUSTERED INDEX'

)

AND (fragment_count * avg_fragment_size_in_pages) > 20

ORDER BY avg_fragmentation_in_percent DESC

Numero 11:

Detalle de la fragmentacion de nuestros indices

SELECT object_name(IPS.object_id) AS [TableName]

,SI.NAME AS [IndexName]

,IPS.Index_type_desc

,IPS.avg_fragmentation_in_percent

,IPS.avg_page_space_used_in_percent

,IPS.record_count

,IPS.ghost_record_count

,IPS.fragment_count

,IPS.avg_fragment_size_in_pages

,alloc_unit_type_desc

,index_level

FROM sys.dm_db_index_physical_stats(db_id(N'AdventureWorks2012'), NULL, NULL, NULL,

'DETAILED') IPS

INNER JOIN sys.tables ST WITH (NOLOCK) ON IPS.object_id = ST.object_id

INNER JOIN sys.indexes SI WITH (NOLOCK) ON IPS.object_id = SI.object_id

AND IPS.index_id = SI.index_id

WHERE ST.is_ms_shipped = 0

AND (fragment_count * avg_fragment_size_in_pages) > 20

AND index_type_desc IN (

'NONCLUSTERED INDEX'

,'CLUSTERED INDEX'

)

AND index_level <> 0

ORDER BY avg_fragmentation_in_percent DESC

Numero 12:

Detalle del histórico de los JOBs del servidor

SELECT object_name(IPS.object_id) AS [TableName]

,SI.NAME AS [IndexName]

,IPS.Index_type_desc

,IPS.avg_fragmentation_in_percent

,IPS.avg_page_space_used_in_percent

,IPS.record_count

,IPS.ghost_record_count

,IPS.fragment_count

,IPS.avg_fragment_size_in_pages

,alloc_unit_type_desc

,index_level

FROM sys.dm_db_index_physical_stats(db_id(N'AdventureWorks2012'), NULL, NULL, NULL,

'DETAILED') IPS

INNER JOIN sys.tables ST WITH (NOLOCK) ON IPS.object_id = ST.object_id

INNER JOIN sys.indexes SI WITH (NOLOCK) ON IPS.object_id = SI.object_id

AND IPS.index_id = SI.index_id

WHERE ST.is_ms_shipped = 0

AND (fragment_count * avg_fragment_size_in_pages) > 20

AND index_type_desc IN (

'NONCLUSTERED INDEX'

,'CLUSTERED INDEX'

)

AND index_level <> 0

ORDER BY avg_fragmentation_in_percent DESC

Numero 13:

Quieres mirar el estado del buffer del SQL server, te lo tengo

SELECT object_name

,counter_name

,cntr_value

FROM sys.dm_os_performance_counters

WHERE [object_name] LIKE '%Buffer Manager%'

AND [counter_name] IN (

'Page life expectancy'

,'Free list stalls/sec'

,'Page reads/sec'

)

Numero 14:

Consultas en ejecución con usuario incluido

SELECT r.session_id

,r.command

,CONVERT(NUMERIC(6, 2), r.percent_complete) AS [Percent Complete]

,CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time, GetDate()), 20)

AS [ETA Completion Time]

,CONVERT(NUMERIC(10, 2), r.total_elapsed_time / 1000.0 / 60.0)

AS [Elapsed Min]

,CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0)

AS [ETA Min]

,CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0 / 60.0)

AS [ETA Hours]

,CONVERT(VARCHAR(1000), (

SELECT SUBSTRING(TEXT, r.statement_start_offset / 2,

CASE

WHEN r.statement_end_offset = - 1

THEN 1000

ELSE (r.statement_end_offset - r.statement_start_offset) / 2

END)

FROM sys.dm_exec_sql_text(sql_handle)

))

FROM sys.dm_exec_requests r

WHERE command IN (

'RESTORE DATABASE'

,'BACKUP DATABASE'

)

Numero 15:

Indices faltantes

SELECT TOP 25 dm_mid.database_id AS DatabaseID
 ,dm_migs.avg_user_impact * (dm_migs.user_seeks + dm_migs.user_scans)
Avg_Estimated_Impact
 ,dm_migs.last_user_seek AS Last_User_Seek
 ,OBJECT_NAME(dm_mid.OBJECT_ID, dm_mid.database_id) AS [TableName]
 ,'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID, dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns, ''), ', ', '_'),
'[', ''), ']', '') + CASE 
  WHEN dm_mid.equality_columns IS NOT NULL
   AND dm_mid.inequality_columns IS NOT NULL
   THEN '_'
  ELSE ''
END + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns, '')
, ', ', '_'), '[', ''), ']', '') + ']' + ' ON ' + dm_mid.statement +
' (' + ISNULL(dm_mid.equality_columns, '') + CASE 
WHEN dm_mid.equality_columns IS NOT NULL
 AND dm_mid.inequality_columns IS NOT NULL
   THEN ','
  ELSE ''
  END + ISNULL(dm_mid.inequality_columns, '') + ')' +
 ISNULL(' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO

SELECT TOP 25 dm_mid.database_id AS DatabaseID

,dm_migs.avg_user_impact * (dm_migs.user_seeks + dm_migs.user_scans)

Avg_Estimated_Impact

,dm_migs.last_user_seek AS Last_User_Seek

,OBJECT_NAME(dm_mid.OBJECT_ID, dm_mid.database_id) AS [TableName]

,'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID, dm_mid.database_id) + '_'

+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns, ''), ', ', '_'),

'[', ''), ']', '') + CASE

WHEN dm_mid.equality_columns IS NOT NULL

AND dm_mid.inequality_columns IS NOT NULL

THEN '_'

ELSE ''

END + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns, '')

, ', ', '_'), '[', ''), ']', '') + ']' + ' ON ' + dm_mid.statement +

' (' + ISNULL(dm_mid.equality_columns, '') + CASE

WHEN dm_mid.equality_columns IS NOT NULL

AND dm_mid.inequality_columns IS NOT NULL

THEN ','

ELSE ''

END + ISNULL(dm_mid.inequality_columns, '') + ')' +

ISNULL(' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement

FROM sys.dm_db_missing_index_groups dm_mig

INNER JOIN sys.dm_db_missing_index_group_stats dm_migs

ON dm_migs.group_handle = dm_mig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details dm_mid

ON dm_mig.index_handle = dm_mid.index_handle

WHERE dm_mid.database_ID = DB_ID()

ORDER BY Avg_Estimated_Impact DESC

PREGUNTEN SI FALTO ALGUNA QUE REQUIERAN

2 comentarios en «Conjunto de consultas Administrativas SQL Server: Consultas Administrativas SQL para»

Erik Javier Olave

el 29 septiembre, 2017 a las 5:22 pm
Enlace permanente

Muy buen blog mi estimado, tendrá a la mano el query para obtener los usuarios de una BD roles asociados y privilegios del rol en una sola instrucción. Muchas gracias
Accede para responder
- JuanCastillo
  
  el 4 octubre, 2017 a las 6:45 pm
  Enlace permanente
  
  Actualmenten o lo tengo, dejame validar entre los que tengo en mi PC personal y te ayudo
  Accede para responder