none
La Querys que simpre utilizo. RRS feed

  • Debate general

  • Hola amigos:

    Les dejo una serie de query que siempre son de ayuda para determinar ciertos estado de SQL Server.

    Version de SQL Server

    select @@version
    select serverproperty('productversion'),
    serverproperty('productlevel'),
    serverproperty('edition')

    Tamaño de la Base de datos.

    select
    		'FileID: ' + convert(varchar(10),a.FILEID),
    		[FILE_SIZE_MB] = 
    			'FILE SIZE MB: ' + convert(varchar(10), convert(decimal(12,2),round(a.size/128.000,2))),
    		[SPACE_USED_MB] =
    			'SPACE USED MB: ' + convert(varchar(10), convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2))),
    		[FREE_SPACE_MB] =
    			'FREE SPACE MB:' + convert(varchar(10),convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2))) ,
    		NAME = 'File Name: ' + left(a.NAME,15),
    		FILENAME = 'Path: ' + left(a.FILENAME,30)
    from
    		dbo.sysfiles a

    Estado de la ejecucion de un backup:

    SELECT command,
                s.text,
                start_time,
                percent_complete,
                CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
                      + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
                      + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
                CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
                      + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
                      + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
                dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
    FROM sys.dm_exec_requests r
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
    WHERE 
    r.command LIKE '%RESTORE%' OR r.command LIKE '%BACKUP%'
    --WHERE r.command IN 'RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG', 'RESTORE HEADERON')


    Blokeos

    sp_who2
    sp_who2 'active'

    Para ver las concutas en el Plan de Cache

    SELECT SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
                              ((CASE statement_end_offset
                                WHEN -1 THEN DATALENGTH(ST.text)
                                ELSE QS.statement_end_offset 
                                END - QS.statement_start_offset)/2) + 1) AS "Statement Text",
           total_worker_time/execution_count/1000 AS "Average Worker Time (ms)",
           execution_count AS "Execution Count", 
           total_worker_time/1000 AS "Total Worker Time (ms)", 
    		   total_logical_reads AS "Total Logical Reads", 
           total_logical_reads/execution_count AS "Average Logical Reads",
    		   total_elapsed_time/1000 AS "Total Elapsed Time (ms)", 
           total_elapsed_time/execution_count/1000 AS "Average Elapsed Time (ms)",
    		   QP.query_plan AS "Query Plan (double click to open)"
    FROM sys.dm_exec_query_stats QS
    		CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) ST
    		CROSS APPLY sys.dm_exec_query_plan(QS.plan_handle) QP
    ORDER BY total_elapsed_time/execution_count DESC

    Bueno esperpo que les sirva

    Saludos a todos

    lunes, 26 de marzo de 2012 15:17

Todas las respuestas

  • Me uno a la propuesta:

    Este es un conjunto de consultas a las que llamo quick queries:

    -- Last time the Instance was restarted
    SELECT create_date as Last_Restarted_Date FROM sys.databases WHERE name = 'tempdb'
    
    -- View in which node is the Instance
    SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as Node
    
    -- View SQL Instance version
    SELECT @@version
    
    -- View with which SQL version was performed the backup (Check SoftwareVersionMajor, SoftwareVersionMinor and SoftwareVersionBuild)
    RESTORE HEADERONLY FROM DISK = 'PATH'
    
    -- Shrink all DB Log Files
    sp_MSforeachdb "use [?]; dbcc shrinkfile(2,1)"
    
    -- View DB size
    sp_spaceused
    
    -- View table size
    sp_spaceused 'database.schema.table'
    
    -- View all tables sizes
    EXEC sp_MSforeachtable "EXEC sp_spaceused '?'"
    
    -- Change DB owner
    EXEC SP_CHANGEDBOWNER 'USER'

    Para consultar los procesos de la instancia, utilizo una versión modificada del procedimiento sp_who2

    -- Informacion General sobre Procesos de la Instancia 
    SELECT 
           [Process ID]    = p.spid
          ,[HostName]	   = p.hostname 
          ,[User]          = p.loginame   
          ,[Database]      = ISNULL(db_name(p.dbid),N'')
          ,[Query]		   = sql1.text
          ,[Status]        = p.status 
          ,[Open Transactions] = p.open_tran 
          ,[Command]       = p.cmd 
          ,[ApplicatiON]   = p.program_name 
          ,[Wait Time]     = p.waittime  
          ,[Wait Type]     = CASE 
    							WHEN p.waittype = 0 THEN N'' 
    						 ELSE p.lastwaittype 
    						 END 
          ,[CPU]           = p.cpu 
          ,[Physical IO]   = p.physical_io 
          ,[Memory Usage]  = p.memusage
          ,[Login Time]    = p.login_time 
          ,[Last Batch]    = p.last_batch 
          ,[Blocked By]    = p.blocked
          ,[Blocked By Query] = sp.text
          ,[Blocking]      = CASE
    							WHEN (SELECT count(*) FROM master.dbo.sysprocesses pp WHERE p.spid=pp.blocked)>0 THEN 1
                             ELSE 0
    						 END
    FROM master.dbo.sysprocesses p
    INNER JOIN master.sys.dm_exec_sessiONs s WITH (NOLOCK) ON p.spid = s.session_id
    CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) sql1
    LEFT JOIN (SELECT mp.spid,sql2.text FROM master.dbo.sysprocesses mp
    		   CROSS APPLY sys.dm_exec_sql_text(mp.sql_handle) sql2 ) sp ON p.blocked=sp.spid
    ORDER BY p.spid 

    Para bloqueos, utilizo el script anterior, pero con algunos campos menos, y realizo un filtro en el where dependiendo la ocasión:

    --Ver únicamente bloqueos (editar WHERE)
    SELECT * FROM (
    	SELECT 
    	 [Process ID]	  = p.spid
    	,[HostName]	      = p.hostname
    	,[User]           = p.loginame   
        ,[Database]       = ISNULL(db_name(p.dbid),N'')
    	,[Query]		  = sql1.text
    	,[Command]        = p.cmd 
    	,[Blocked By]	  = p.blocked
    	,[Blocked By Query] = sp.text
    	,[Blocking]		  = CASE 
    							WHEN (SELECT count(*) FROM master.dbo.sysprocesses pp WHERE p.spid=pp.blocked)>0 THEN 1
    					    ELSE 0
    					    END 
    	,[Wait Time]      = p.waittime 
        ,[Wait Type]      = CASE 
    							WHEN p.waittype = 0 THEN N'' 
    					    ELSE p.lastwaittype 
    					    END
        ,[Kill Command] = 'kill ' + CONVERT(varchar(10), p.spid)
    	FROM master.dbo.sysprocesses p
    	CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) sql1
    	LEFT JOIN (SELECT mp.spid,sql2.text FROM master.dbo.sysprocesses mp
    			   CROSS APPLY sys.dm_exec_sql_text(mp.sql_handle) sql2 ) sp ON p.blocked=sp.spid
    ) AS tabla
    --WHERE [Blocking] <> 0 or [Blocked By] <> 0	-- Ver Procesos que están siendo bloqueados o que están bloqueando
    --WHERE [Blocking] <> 0 and [Blocked By] = 0	-- Ver Procesos que no están siendo bloqueados y que están bloqueando
    ORDER BY [Process ID]

    Para el tema índices tengo unas cuantos scripts, pero recomiendo revisar este link de Brent Ozar con unos scripts muy útiles:

    Blitz Result: Slow Storage Reads or Writes

    Y como no, hablando de Brent Ozar no puede faltar su gran procedimiento almacenado sp_blitz, que en un momento te analiza la BBDD en busca de fallos o posibles problemas:

    sp_BLITZ – SQL Server Takeover Script

    Y por último, para Jobs, uso un script parecido al resultado del Job Activity Monitor, con la diferencia que este se puede filtrar mejor y rápidamente:

    Scripts: Jobs no ejecutados en el último año / Actividad de Jobs Detallada

    Al final, todos estos scripts se pueden encontrar rápidamente con una búsqueda en google, pero bueno, mejor tenerlos agrupados.


    Fran Lens http://es.linkedin.com/in/franlens
    My Blog: http://www.lensql.net

    Please remember click the Mark as Answer button if a post helps you to solve the problem



    • Editado Fran Lens lunes, 26 de marzo de 2012 16:52
    lunes, 26 de marzo de 2012 16:51
  • Deberíamos tener un repositorio de scripts con este tipo de datos.

    La sección de code la veo muy orientada a proyectos .net.


    MVP MCT MCTS Daniel Calbimonte

    http://elpaladintecnologico.blogspot.com

    lunes, 26 de marzo de 2012 17:51