martes, 6 de agosto de 2013

SQL SERVER: CONSULTAS ÚTILES

CONSULTAS PARA AUDITORIA, OPTIMIZACIÓN, SIMPLE CURIOSIDAD O LO QUE SE TE OCURRA...

Les presento un listado de consultas que pueden resultar útiles (lo han sido para mí en distintas ocasiones). Algunas de ellas ofrecen salidas rápidas a problemas comunes, otras nos ayudaran a tener un panorama general de la base de datos con la que estamos tratando, ya sea por el simple hecho de querer tener un contexto, o que lo requiramos para propósitos de optimización general. La mayoría de ellas ya son conocidas, en su respectivo caso cito la fuente de donde las tome, por si gustan darse una vuelta por el site y ver que les puede servir. Con algunas me tome la molestia de parametrizarlas a fin de que puedan resultar más flexibles o por si a alguno le da por convertirlas en store procedure.

BUSCAR REGISTROS DUPLICADOS

USE <DB>
SELECT <Campo>, COUNT(*) AS Registros
FROM <TABLA>(NOLOCK)
GROUP BY <Campo>
HAVING COUNT(*) > 1


BUSCAR REFERENCIAS DE TEXTO EN TRIGGERS, FUNCIONES Y STORE PROCEDURES

Este es por lo general de mi particular uso frecuente al momento de  analizar alguna inconsistencia de datos, me dan un panorama de que store procedure, triggers o functions. pueden estar afectando que tablas, buscando, por ejemplo un "UPDATE <Tabla>" dentro de los mismos.

SET NOCOUNT ON
DECLARE @Texto VARCHAR(50)=NULL -->Texto a buscar. Normalmente lo uso para buscar                                            -->referencias a tablas o otros SP

       SELECT
       ROUTINE_TYPE AS TIPO,
       ROUTINE_SCHEMA AS ESQUEMA,
       ROUTINE_NAME AS NOMBRE,
       ROUTINE_DEFINITION AS DETALLE
       FROM INFORMATION_SCHEMA.ROUTINES(NOLOCK)
    WHERE ROUTINE_DEFINITION LIKE '%'+@Texto+'%'
SET NOCOUNT OFF


REVISAR CAMPOS EN ESTRUCTURAS

-http://www.mistrucos.net/truco-sql-server-buscar-un-campo-una-base-datos-501.htm
DECLARE @Columna NVARCHAR(25)= -->Columna a buscar

SELECT TABLE_NAME,*
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%'+@Columna+'%'


LISTA DE PROCEDIMIENTOS ALMACENADOS MODIFICADOS EN LOS ÚLTIMOS
“N” 
DÍAS

--http://www.sqlservercentral.com/scripts/Administration/98329/
DECLARE @DIAS INT=500 -->Numero de dias de antiguedad

SET NOCOUNT ON
       SELECT *
       FROM sys.objects (NOLOCK)
       WHERE TYPE = 'P'
       AND DATEDIFF(D,modify_date, GETDATE()) < @DIAS
SET NOCOUNT OFF

SACAR ESQUEMA BASICO DE TABLA (SIMILAR A ALT+F1)

Esto puede resultar redundante, es decir, ¿Para que quiero una consulta si ya lo puedo traer con ALT+F1?, pero, se le puede dar usos interesantes para revisiones automáticas usando Jobs y otras herramientas (:P), les dejo de tarea el uso creativo que se le puede dar a este tipo de consultas.

DECLARE @table VARCHAR(50)=NULL --> Tabla. 

SELECT  
c.table_name As Tabla,  
c.column_name As Columna,  
c.data_type As Tipo,  
c.character_maximum_length As Longitud, 
  COALESCE ( 
  ( SELECT  
    CASE cu.column_name 
      WHEN NULL THEN
      ELSE
    END 
  FROM information_schema.constraint_column_usage cu 
  INNER JOIN information_schema.table_constraints ct 
  ON ct.constraint_name = cu.constraint_name 
  WHERE  
  ct.constraint_type = 'PRIMARY KEY'  
  AND ct.table_name = c.table_name 
  AND cu.column_name = c.column_name  
  ),0) AS PK
FROM information_schema.columns c 
INNER JOIN information_schema.tables t 
ON c.table_name = t.table_name 
WHERE @table = t.table_name AND  
  (t.table_type = 'BASE TABLE' AND NOT  
  (t.table_name = 'dtproperties') AND NOT  
  (t.table_name = 'sysdiagrams')) 
ORDER BY c.table_name, c.ordinal_position


NUMERO DE REGISTROS POR TABLA

Este puede resultar particularmente útil para saber que campo vamos a usar como indice, en caso de que la tabla no lo tenga, o, en su defecto, si vamos a redefinirlo.

SET NOCOUNT ON
       SELECT sysobjects.Name as TABLA, sysindexes.Rows as REGISTROS
       FROM sysobjects(NOLOCK)
       INNER JOIN sysindexes(NOLOCK) ON sysobjects.id = sysindexes.id
       WHERE type = 'U'
       AND sysindexes.IndId < 2
       ORDER BY sysindexes.Rows desc
SET NOCOUNT OFF

REGRESAR TABLA EN FORMATO XML

 SELECT * FROM <Tabla> FOR XML AUTO

IDENTIFICA LOS QUERYS QUE ESTAN GENERANDO ALTOS INDICES DE I/O

/*
Troubleshooting I/O bottleneck.
Applies to: SQL Server 2005/2008.
www.SqlCoffee.com
http://www.sqlcoffee.com/Tuning01.htm
*/
SET NOCOUNT ON
       USE MASTER
       SELECT TOP 20 (qs.total_logical_reads/qs.execution_count) AS LogicalReads,
       (qs.total_logical_writes/qs.execution_count) AS LogicalWrites,
       (qs.total_physical_reads/qs.execution_count) AS PhysicalReads,
       qs.Execution_count, qs.sql_handle, qs.plan_handle, qt.Text
       FROM sys.dm_exec_query_stats qs  cross apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
       INNER JOIN sys.dm_exec_cached_plans AS cp ON qs.plan_handle=cp.plan_handle
       ORDER BY (qs.total_logical_reads + qs.total_logical_writes) DESC
SET NOCOUNT OFF

--Use the following query to examine the query plan / Use el siguiente query para examinar el plan
SELECT * FROM sys.dm_exec_query_plan (<plan_handle>)

RETORNAR TODAS LAS TABLAS Y SU INFORMACION

--http://www.sqlservercentral.com/scripts/tables/97194/
SET NOCOUNT ON
SELECT
sys.tables.name AS [TABLE],
sys.tables.create_date AS CREATE_DATE,
sys.tables.modify_date AS MODIFY_DATE,
CASE
WHEN sys.database_principals.name IS NULL THEN SCHEMA_NAME(sys.tables.schema_id)
ELSE sys.database_principals.name
END AS OWNER,
SUM(ISNULL(CASE INDEXES.TYPE WHEN 0 THEN COUNT_TYPE END, 0)) AS COUNT_HEAP_INDEX,
SUM(ISNULL(CASE INDEXES.TYPE WHEN 1 THEN COUNT_TYPE END, 0)) AS COUNT_CLUSTERED_INDEX,
SUM(ISNULL(CASE INDEXES.TYPE WHEN 2 THEN COUNT_TYPE END, 0)) AS COUNT_NONCLUSTERED_INDEX,
SUM(ISNULL(CASE INDEXES.TYPE WHEN 3 THEN COUNT_TYPE END, 0)) AS COUNT_XML_INDEX,
SUM(ISNULL(CASE INDEXES.TYPE WHEN 4 THEN COUNT_TYPE END, 0)) AS COUNT_SPATIAL_INDEX,
sys.tables.max_column_id_used AS COUNT_COLUMNS,
sys.partitions.rows AS COUNT_ROWS,
SUM(ISNULL(CASE
           WHEN sys.allocation_units.type <> 1 THEN USED_PAGES
           WHEN SYS.partitions.INDEX_ID < 2 THEN DATA_PAGES
           ELSE 0 END, 0)) *
               (SELECT low / 1024 AS VALUE
                FROM master.dbo.spt_values
                WHERE (number = 1) AND (type = N'E')) AS SIZE_DATA_KB,
SUM(ISNULL(sys.allocation_units.used_pages - 
           CASE
           WHEN sys.allocation_units.type <> 1 THEN USED_PAGES
           WHEN SYS.partitions.INDEX_ID < 2 THEN DATA_PAGES 
           ELSE 0 END, 0))*
                (SELECT low / 1024 AS VALUE
                 FROM master.dbo.spt_values AS spt_values_2
                 WHERE (number = 1) AND (type = N'E')) AS SIZE_INDEX_KB
FROM sys.allocation_units(NOLOCK)
INNER JOIN sys.partitions(NOLOCK
ON sys.allocation_units.container_id = sys.partitions.partition_id
INNER JOIN (SELECT TOP (100) PERCENT object_id, index_id, type AS TYPE, 
COUNT(*) AS COUNT_TYPE
FROM sys.indexes(NOLOCK)  AS indexes_1 GROUP BY object_id, type, index_id                    ORDER BY object_id) AS INDEXES 
ON sys.partitions.object_id = INDEXES.object_id 
AND sys.partitions.index_id = INDEXES.index_id 
RIGHT OUTER JOIN sys.database_principals 
RIGHT OUTER JOIN sys.tables 
ON sys.database_principals.principal_id = sys.tables.principal_id 
ON INDEXES.object_id = sys.tables.object_id 
GROUP BY sys.tables.name, sys.tables.create_date, sys.tables.modify_date,
CASE WHEN sys.database_principals.name IS NULL 
THEN SCHEMA_NAME(sys.tables.schema_id) ELSE sys.database_principals.name END,
sys.tables.max_column_id_used, sys.partitions.rows
ORDER BY COUNT_ROWS DESC
SET NOCOUNT OFF

BUSCAR COLUMNAS IDENTIDAD

--http://www.areatic.net/2012/11/sql-server-como-saber-si-hay-columnas.html
SET NOCOUNT ON
       SELECT SCHEMA_NAME(OBJECTPROPERTY(OBJECT_ID,'SchemaId')) AS SchemaName,
                OBJECT_NAME(OBJECT_ID) AS TableName,
                name AS ColumnName
       FROM  SYS.COLUMNS(NOLOCK)
       WHERE is_identity = 1
       ORDER BY SchemaName, TableName, ColumnName
SET NOCOUNT OFF

MONITOREO DE EL ESTADO DE LOS JOBS FALLADOS EN LA ULTIMA EJECUCION

--http://sqldata.blogspot.mx/2009/01/algunos-scripts-para-monitorear-sql.html
SET NOCOUNT ON
       SELECT name AS [JOB's FALLIDOS]
       FROM msdb.dbo.sysjobs A(NOLOCK), msdb.dbo.sysjobservers B(NOLOCK)
       WHERE A.job_id = B.job_id AND B.last_run_outcome = 0
SET NOCOUNT OFF
                          
ESPACIO EN CADA HD PARA INSTANCIAS DE SQL

EXEC master..xp_fixeddrives

VISUALIZAR LISTADO DE JOBS DESHABILITADOS

SET NOCOUNT ON
       SELECT name AS [JOB´s DESHABILITADOS]
       FROM msdb.dbo.sysjobs(NOLOCK)    
WHERE enabled = 0 ORDER BY name
SET NOCOUNT OFF

VISUALIZAR LISTADO DE JOBS EN EJECUCION ACTUALMENTE

msdb.dbo.sp_get_composite_job_info NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL

REVISAR FECHA DE ÚLTIMO BACKUP EFECTUADO A LAS BD

SET NOCOUNT ON
       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(NOLOCK)
       LEFT OUTER JOIN msdb.dbo.backupset A(NOLOCK) ON A.database_name = B.name AND A.type = 'D'
       GROUP BY B.Name
       ORDER BY B.name
SET NOCOUNT OFF

REVISAR LAS ÚLTIMAS ENTRADAS DEL ARCHIVO LOG (NO DEL LOG TRANSACCTION)

 SET NOCOUNT ON
       CREATE TABLE #Errors (LogDate datetime, ProcessInfo varchar(100), [Text] text)
       INSERT #Errors EXEC xp_readerrorlog
       SELECT LogDate,ProcessInfo,[Text] --SELECT * FROM #Errors
       FROM #Errors
       WHERE [Text]
       NOT LIKE '%Log backed up%' AND [Text]
       NOT LIKE '%.TRN%' AND [Text]
       NOT LIKE '%Database backed up%' AND [Text]
       NOT LIKE '%.BAK%' AND [Text]
       NOT LIKE '%Run the RECONFIGURE%' AND
       [Text] NOT LIKE '%Copyright (c)%'
       ORDER BY LogDate asc

       DROP TABLE #Errors
SET NOCOUNT OFF

RECOMPILAR SP
/*
http://www.sqlcoffee.com/Tuning03.htm
System Stored Procedure sp_recompile
By: Alberto Morillo - www.sqlcoffee.com

Usar cuando se añadan nuevos indices a tablas o se modifique la estructura de una tabla.
El ejemplo siguiente es usado en la tabla  Employees de la Base de datos AdventureWorks.
*/
USE AdventureWorks;
GO
EXEC sp_recompile N'HumanResources.Employee';
GO

LISTAR BASES DE DATOS

EXEC sp_helpdb
SELECT * FROM master.dbo.sysdatabases


REFERENCIAS:

http://www.sqlcoffee.com/
http://sqldata.blogspot.mx
http://www.areatic.net
http://www.mistrucos.net
http://www.sqlservercentral.com

No hay comentarios:

Publicar un comentario

Nota: solo los miembros de este blog pueden publicar comentarios.