T-SQL Tuesday #66: Monitoring

This month's T-SQL Tuesday is being hosted by Cathrine Wilhelmsen (b|t), and the topic is as follows: Monitoring.

I thought I'd finally give it a go by joining the tradition of T-SQL Tuesday. The last months or so I kept saying to myself: this topic is not particularly suited for me, or I don't have the time right now.. As most people say about blogging: not starting is usually not blogging at all, so here it goes!

Since I'm a SQL/BI developer I'm not overly into monitoring servers or SQL-instances, but after thinking about it I do have some other things to monitor during my day-to-day tasks.

SSIS
I tend to execute quite a few packages and jobs during the day. I monitor them via the Standard Report option of the SSISDB Catalog in SSMS as that's the easiest way. Following naming conventions for my packages, tasks and components I can quickly address issues and see where things go bad.

SQL
I also regularly take a look at the servers that are running my solutions to keep everything going smoothly. Until now it's mainly been reactive, e.g. when users start complaining about slow reports or dashboards.
I use the below query for finding the top slow queries that have run lately.

SELECT TOP 20 SUBSTRING(qt.TEXT, (qs.statement_start_offset / 2) + 1, (
   (
    CASE qs.statement_end_offset
     WHEN - 1
      THEN DATALENGTH(qt.TEXT)
     ELSE qs.statement_end_offset
     END - qs.statement_start_offset
    ) / 2
   ) + 1) AS SQLStatement
 ,qs.execution_count
 ,qs.total_logical_reads
 ,qs.last_logical_reads
 ,qs.total_logical_writes
 ,qs.last_logical_writes
 ,qs.total_worker_time
 ,qs.last_worker_time
 ,qs.total_elapsed_time / 1000000 total_elapsed_time_in_Sec
 ,qs.last_elapsed_time / 1000000 last_elapsed_time_in_Sec
 ,qs.last_execution_time
 ,qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

Besides getting slow queries that have already run, it sometimes happens that a DWH-job is still running in the morning when it should've been completed looong ago :). Running sp_who2 or sp_WhoIsActive by Adam Machanic (b|t) usually gets me to find the reason why it is still running and what the problem is.

Hopefully I can continue to add more posts to the T-SQL Tuesday tradition. Have a nice day and keep sharing!