My Favorite Blogs

Saturday, February 22, 2014

Most useful MDW queries I've written but didn't get chance to share long time but now here they are...

Hmm... after a long gap writing this post on my blog... not sounds right... but I was little engaged with both personal and official work which blocked me to spend more time on my writing..

In this write up, I've provided couple of MDW queries which helps DBAs very much when they need to investigate some issues. You can tune up these queries depends on your requirement.

This morning reported an issue that every day at 6PM top of the hour there is a performance degradation on the application, the issue came to me for investigation with a set of given sprocs, I've checked the sproc performance with the collected data and don’t see any performance bottleneck on both db side or SAN side. I've been using MDW for a while and written couple of queries as well, with some additional tune up in the existing one came up with the below query which helped to find the hourly connections on the specific database. This query need to be run on the specific MDW database where you are collecting data for the SQL instance. I've passed the DBName, SQLInstanceName and the collection_time value to get the data for last 5 days and found that every day 6PM the call factor on the db is between 4X to 5X. Then realized that this is not a DB issue but it is volume issue. What else now app team is breaking their heads.... :)

 You can tune this query as needed and MDW has lot more details for your investigations.

-- To find Connections hourly
select  sii.instance_name ServerName,DATEPART(Month, acs.collection_time) CMonth,DATEPART(Day, acs.collection_time)CDay,DATEPART(hour, acs.collection_time) CHour ,COUNT(*) ConsCount
from snapshots.active_sessions_and_requests acs
INNER JOIN core.snapshots_internal si ON acs.snapshot_id=si.snapshot_id
INNER JOIN core.source_info_internal sii ON sii.source_id=si.source_id
where acs.database_name='DBName'
AND sii.instance_name = 'SQLInstanceName'
and collection_time BETWEEN  'From DateTime' AND 'To DateTime'
GROUP BY  sii.instance_name,DATEPART(Month, acs.collection_time),DATEPART(Day, acs.collection_time),DATEPART(hour, acs.collection_time)
Order BY 1,2

Below formatted result set got from the above query output and copied in excel sheet and made a graph out of it give a better understanding to the application teams.



The following query will have with hostname added, I mean the hourly connection list grouped by connection hosts, this will help you to nail down which host is making more calls during the issue time.

-- To find Connections hourly with Hostwise
select  sii.instance_name ServerName,acs.host_name,
DATEPART(Month, acs.collection_time) CMonth,DATEPART(Day, acs.collection_time)CDay,DATEPART(hour, acs.collection_time) CHour ,COUNT(*) ConsCount
from snapshots.active_sessions_and_requests acs
INNER JOIN core.snapshots_internal si ON acs.snapshot_id=si.snapshot_id
INNER JOIN core.source_info_internal sii ON sii.source_id=si.source_id
where acs.database_name='DBName'
AND sii.instance_name = 'SQL InstanceName'
and collection_time BETWEEN  '2014-02-15 08:00:00.9230000 -08:00' AND '2014-02-21 15:00:00.9230000 -08:00'
GROUP BY  sii.instance_name,host_name, DATEPART(Month, acs.collection_time),DATEPART(Day, acs.collection_time),DATEPART(hour, acs.collection_time)
Order BY 6 desc


The following query may be need little more tuning, but this one I've written to find blocking history on a specific database on specific time. The is_blocking column can help you to find the culprit or the head blocker if the value is 1. The blocking_session_id is the column will list all the victims. From this result set you can find the object from sql_text and check wait_type and description to deep dive into the issue give recommendation to the devs or fix.

-- Find Blocking Culprits and Victims

SELECT
collection_time ,Host_name
--, program_name, database_name, login_name
, session_last_request_start_time,session_last_request_end_time, command, object_id, object_name
--, substring(sql_text, 1, 50) -- Sproc or statement details.
,wait_type, wait_duration_ms,wait_resource,resource_description,request_logical_reads,request_total_elapsed_time
,session_id
,blocking_session_id,is_blocking,blocking_exec_context_id
--,*
from snapshots.active_sessions_and_requests acs
INNER JOIN core.snapshots_internal si ON acs.snapshot_id=si.snapshot_id
INNER JOIN core.source_info_internal sii ON sii.source_id=si.source_id
INNER JOIN snapshots.notable_query_text snq ON snq.sql_handle=acs.sql_handle
where acs.database_name='DBName'
AND sii.instance_name = 'SQL InstanceName'
and collection_time BETWEEN  '2014-02-20 17:00:00.9230000 -08:00' AND '2014-02-20 18:00:00.9230000 -08:00'
AND acs.host_name like 'hostname%'
--AND is_blocking = 1 -- Head blocker/ Culprit
AND object_name='Sproc/objectname'
Order by collection_time

The following query is a really helpful one to find a database growth status for a period, you can run the query on the MDW database where you are collecting data for a specific database.

--To find DB growth trend...

SELECT
    c.instance_name
  , a.dbsize / 128 AS 'DBSizeMB'
  , a.logsize / 128 AS 'LogSizeMB'
  , a.ftsize
  , a.database_name
  , convert(datetime,a.collection_time,102)
  ,a.reservedpages / 128 AS 'RpageMB'
  ,a.usedpages / 128 AS 'UsedpagesMB'
  ,a.pages / 128 AS 'PagesMB'
FROM
    MDW.snapshots.disk_usage a INNER JOIN
    MDW.core.snapshots_internal b
    ON a.snapshot_id = b.snapshot_id INNER JOIN
    MDW.core.source_info_internal c
    ON b.source_id = c.source_id
WHERE
      a.database_name='DBName'
ORDER BY
  --  c.instance_name
  --, a.database_name
   a.collection_time DESC

Hoping this is helpful for DBA community... 

1 comment: