Advertisements

Archive

Posts Tagged ‘DBA Queries’

Listing blocking chain by tasks

May 25, 2010 Leave a comment
WITH TaskChain (waiting_task_address, blocking_task_address, ChainId, LEVEL)
AS
(
   -- Anchor member definition: use self join so that we output
   -- Only tasks that blocking others and remove dupliates

   SELECT   DISTINCT A.waiting_task_address
          , A.blocking_task_address
          , A.waiting_task_address   AS ChainId
          , 0 AS LEVEL
     FROM sys.dm_os_waiting_tasks AS A
     JOIN sys.dm_os_waiting_tasks AS B
       ON A.waiting_task_address = B.blocking_task_address
    WHERE A.blocking_task_address IS NULL

UNION ALL

	-- Recursive member definition: Get to the next level waiting tasks

   SELECT   A.waiting_task_address
	  , A.blocking_task_address
	  , B.ChainId
	  , LEVEL + 1
     FROM sys.dm_os_waiting_tasks AS A
     JOIN TaskChain AS B
       ON B.waiting_task_address = A.blocking_task_address
)
  SELECT   waiting_task_address
         , blocking_task_address
 	 , ChainId
         , LEVEL
    FROM TaskChain
ORDER BY ChainId

References: #20

Advertisements

Getting file usage stats

May 4, 2010 Leave a comment
SELECT db_name(database_id) AS DBName,
       FILE_ID, -- File ID 2 = Log, File Id = 1 Data
       CASE WHEN num_of_reads = 0 THEN
          0
       ELSE
          io_stall_read_ms/num_of_reads
       END AS AvgIOStallRead_ms,
       CASE WHEN num_of_writes = 0 THEN
          0
       ELSE
          io_stall_write_ms/num_of_writes
       END AS AvgIOStallWrites_ms
  FROM sys.dm_io_virtual_file_stats(null,null)

You can use this DMV to see what SQL Server thinks about the I/O stall times. I don’t rely on it 100% but it helpful to see which file is generating the most I/O or is performing the worst. However if you have Avg IO Stall time for read or write over 100 ms, I think it requires investigation. Correlate these with the Disk KPIs such as Avg Disk Sec/Read and Avg. Disk Sec/Write.

References: #9, #10, #11

Granting Execute Permissions

November 10, 2008 Leave a comment

Following code can be used to Grant Execute Permissions to a Database Role, in a database. This is for SQL Server 2000; because in 2000 you can’t just Grant Execute on a Schema or Database like we can in SQL Server 2005/2008.

EXEC dbo.sp_addrole @rolename = N'udbr_SPPermissions'
GO

EXEC dbo.sp_addrolemember N'udbr_SPPermissions', N'DOMAIN\user'
GO

 SELECT 'GRANT EXECUTE ON ' + name + ' TO udbr_SPPermissions' AS SQLStatement
   INTO #NewPermissions
   FROM sysobjects
  WHERE xtype = 'P'
    AND ( name LIKE 'sp[_]%' OR
          name LIKE 'up[_]%')
ORDER BY name
GO

DECLARE @SQLStatement VARCHAR(512)

DECLARE SPPermissions CURSOR FOR
SELECT SQLStatement
  FROM #NewPermissions

OPEN SPPermissions

  FETCH NEXT
   FROM SPPermissions
   INTO @SQLStatement

  WHILE @@FETCH_STATUS = 0
  BEGIN

     PRINT 'Executing: ' + @SQLStatement
     EXEC (@SQLStatement)

     FETCH NEXT
      FROM SPPermissions
      INTO @SQLStatement

  END

CLOSE SPPermissions
DEALLOCATE SPPermissions
GO 
%d bloggers like this: