Consultas-administrativas-SQL-Server-query-admi-select-admin

Consultas para administrar una base de datos SQL Server

En esta post mostraremos una serie de consultas para administrar una base de datos sql y sus motor, en las cuales podremos validar el estado de indices espacio en disco permisos y mucho mas, interesante si queremos aprender un poco la forma en que nuestro SQL esta operando, imaginen que quieren entender a su perro que ladra mucho y no entienden quiere o necesita, pero con el tiempo realiza el mismo comportamiento y van aprendiendo sus modos de hablar con nosotros, pues bien el SQL es muy parecido solo debemo saber interpretar sus señales, nada mejor que unas breves consultas que nos indiquen que realmente tenemos que hacer o mejorar

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

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

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
GO

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

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

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

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

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

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

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

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'
  )

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'
  )

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