How to list current activity - Active SPID’s

What's going on with my server?

If you ever need to know what SPID’s are currently active _and_ are doing something that takes more than 1 second, then try this:

SELECT  r.cpu_time,         r.logical_reads,         r.session_id INTO    #temp FROM    sys.dm_exec_requests AS r   WAITFOR DELAY '00:00:01'   SELECT  SUBSTRING(h.text, ( r.statement_start_offset / 2 ) + 1,                   ( ( CASE r.statement_end_offset                         WHEN -1 THEN DATALENGTH(h.text)                         ELSE r.statement_end_offset                       END - r.statement_start_offset ) / 2 ) + 1) AS text,         r.cpu_time - t.cpu_time AS CPUDiff,         r.logical_reads - t.logical_reads AS ReadDiff,         r.wait_type,         r.wait_time,         r.last_wait_type,         r.wait_resource,         r.command,         r.database_id,         r.blocking_session_id,         r.granted_query_memory,         r.session_id,         r.reads,         r.writes,         r.row_count,         s.[host_name],         s.program_name,         s.login_name FROM    sys.dm_exec_sessions AS s         INNER JOIN sys.dm_exec_requests AS r ON s.session_id = r.session_id                                                 AND s.last_request_start_time = r.start_time         LEFT JOIN #temp AS t ON t.session_id = s.session_id         CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) h   WHERE is_user_process = 1 ORDER BY 3 DESC     DROP TABLE #temp

PerfMon for Dummies

Quest Software has released a nice overview of how to use PerfMon when analyzing SQL Server performance and health.

Top 100 most expensive queries–Profiler and Excel

Sometimes you need to analyze how the queries are behaving and get an understanding of what queries is taking most of the CPU time.  The key thought here is to slice the used CPU time per query with the current load. Note that this is not an exact science, but rather a qualified estimate of what is causing the current server load.

There are several ways of doing this, but I prefer to run a trace, parse the trace and import the result set into Excel for further analysis.  A complicating factor is whether your load consist of ad-hoc SQL or stored procedures. If your system consists of the latter, then the analysis job is straight forward. However, if your system has ad-hoc sql, then you need to parse and cleanup each sql statement. Itzik Ben-Gan has made a great CLR function for this, but I won’t cover this scenario for now.

Now, simply run a trace where you collect RPC:Completed and SP:Completed. Trace it to file and ensure you collect enough data. You should make a note of the current system load and consider whether it’s a representative load compared with the rest of the day or week. I usually sample for 10 minutes and end up with a file between 100 and 300 Gb.

Note that this may put some extra load on your server, but I have never been able to document a noticeable performance hit while sampling. This may, however, vary from system to system, load to load.

Make sure that you collect the following counters: EventClass, DBName, AppName, NTUserName, CPU, Reads, Writes, Duration, ObjectName, TextData, StartTime and EndTime.

An important question is whether you want to factor in a cost when the reported CPU cost is 0. In my experience this is the right thing to do because you get nothing for free. If you have 1000 queries per second, each with a CPU cost of 0, the sum cost is _not_  zero.

Simply run the query below and copy the result into Excel for further drill down. – VERY EASY-

Note that you need to have the trace files located on a SQL instance.

DECLARE @FileName VARCHAR(255) SELECT  @Filename = 'C:\baseline traces\18082010_1427.trc' SELECT  DATEDIFF([ss], MIN([starttime]), MAX(starttime)) AS [Seconds],         COUNT(*) AS [Total],         COUNT(*) / DATEDIFF([ss], MIN(starttime),                             MAX(starttime)) AS s,         MIN(starttime) AS [FROM],         MAX(starttime) AS [TO] FROM    ::         FN_TRACE_GETTABLE(@Filename, DEFAULT) ;   WITH    a AS ( SELECT   CAST(COUNT(*) AS NUMERIC(38, 2))                         / ( SELECT COUNT (*) FROM    :: FN_TRACE_GETTABLE (@Filename, DEFAULT) )                         * 100 AS '%Calls',                         COUNT(*) AS '#Calls',                         AVG(duration) * COUNT(*) AS [Weighed AVG Duration],                         CASE AVG(cpu)                           WHEN 0 THEN 0.5 * COUNT(*)                           ELSE AVG(cpu) * COUNT(*)                         END AS [Weighed AVG CPU],                         AVG(cpu) AS AvgCPU,                         MIN(cpu) AS MinCPU,                         MAX(cpu) AS MaxCPU,                         ISNULL(AVG(duration) / 1000, 0) AS AvgDurationMS,                         ISNULL(MIN(duration) / 1000, 0) AS MinDurationMS,                         ISNULL(MAX(duration) / 1000, 0) AS MaxDurationMS,                         AVG(READS) AS AvgReads,                         MAX(READS) AS MaxReads,                         AVG(writes) AS AvgWrites,                         MAX(writes) AS MaxWrites,                         ObjectName                FROM     ::                         FN_TRACE_GETTABLE(@Filename, DEFAULT)                GROUP BY ObjectName)     SELECT  CAST([%Calls] AS NUMERIC(38, 2)) AS '%Calls',             [#Calls],             [Weighed AVG Duration],             [Weighed AVG CPU],             CAST([Weighed AVG CPU]             / ( SELECT SUM (a . [Weighed AVG CPU]) AS                         [%Weighed AVG CPU] FROM a ) * 100 AS NUMERIC(38, 2)) AS [%Weighed AVG CPU],             AvgCPU,             MinCPU,             MaxCPU,             AvgDurationMS,             MinDurationMS,             MaxDurationMS,             AvgReads,             MaxReads,             AvgWrites,             MaxWrites,             ObjectName     FROM    a     ORDER BY [%Weighed AVG CPU] DESC

Retrieving the execution plan by DMV

How can I retrieve the execution plan from a system without “EXECUTE” or “SELECT” rights on my database?

There are two ways of doing this. Either you can get the execution plan XML from SQL Profiler, or you can get it from the DMV dm_exec_query_plan.

If you want to use Profiler, you need the ALTE TRACE credential on the server. You simply set up a trace and use the ShowPlan XML event.

Another approach is to use the dm_exec_query_plan DMV:

SELECT  usecounts,         cacheobjtype,         objtype,         text,         OBJECT_NAME(st.objectid),         qp.query_plan FROM    sys.dm_exec_cached_plans cp         CROSS APPLY sys.dm_exec_sql_text(plan_handle) st         CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp WHERE   OBJECT_NAME(st.objectid) = 'my proc'

This approach requires that your login has the VIEW SERVER STATE credential.

When you execute it, the result set might look like this:

Then you simply need to click on the XML to study the execution plan

Script for database sizes per server

I don’t remember where I got this script, anyways, it’s a nice add-on to you toolbox. The scripts lists up the size of your databases. This info is also available through SSMS, but the formatting is different.

Please note that the script will fail if your databases has a name that requires brackets. Adding them should be a easy exercise.

SET Nocount ON     CREATE TABLE ##helpfile     (       ObsvDate DATETIME NULL,       ServerName VARCHAR(50) NULL,       DbName VARCHAR(100) NULL,       FileLogicalName VARCHAR(100) NULL,       FileID INT NULL,       FileGroupID INT NULL,       FilePath VARCHAR(100) NULL,       FileGroupName VARCHAR(50) NULL,       FileTotalSizeKB VARCHAR(20) NULL,       FileMaxSizeSetting VARCHAR(20) NULL,       FileGrowthSetting VARCHAR(20) NULL,       FileUsage VARCHAR(20) NULL,       FileTotalSizeMB dec(19, 4) NULL,       FileUsedSpaceMB dec(19, 4) NULL,       FileFreeSpaceMB dec(19, 4) NULL,            )   CREATE TABLE ##filestats     (       DbName VARCHAR(100) NULL,       FileID INT NULL,       FileGroupID INT NULL,       FileTotalSizeMB dec(19, 4) NULL,       FileUsedSpaceMB dec(19, 4) NULL,       FileFreeSpaceMB dec(19, 4) NULL,       FileLogicalName VARCHAR(100) NULL,       FilePath VARCHAR(100) NULL     )   CREATE TABLE ##sqlperf     (       DbName VARCHAR(100) NULL,       LogFileSizeMB dec(19, 4) NULL,       LogFileSpaceUsedpct dec(19, 4) NULL,       Status INT NULL     )   INSERT  ##sqlperf         (           DbName,           LogFileSizeMB,           LogFileSpaceUsedpct,           Status         )         EXEC ( 'DBCC SQLPERF ( LOGSPACE ) WITH NO_INFOMSGS '             )   EXEC sp_MSForeachDB        --@command1 = 'Use ?; DBCC UPDATEUSAGE(0)',     @command1 = 'Use ?;Insert ##helpfile (FileLogicalName, FileID, FilePath, FileGroupName, FileTotalSizeKB, FileMaxSizeSetting, FileGrowthSetting,FileUsage) Exec sp_helpfile; update ##helpfile set dbname = ''?'' where dbname is null',     @command2 = 'Use ?;Insert  ##filestats (FileID, FileGroupID, FileTotalSizeMB, FileUsedSpaceMB, FileLogicalName, FilePath) exec (''DBCC SHOWFILESTATS WITH NO_INFOMSGS ''); update ##filestats set dbname = ''?'' where dbname is null'   -- remove any db's that we don't care about monitoring DELETE  FROM ##filestats WHERE   CHARINDEX(dbname,                   'master-model-pubs-northwind-distribution-msdb') > 0 DELETE  FROM ##helpfile WHERE   CHARINDEX(dbname,                   'master-model-pubs-northwind-distribution-msdb') > 0 DELETE  FROM ##sqlperf WHERE   CHARINDEX(dbname,                   'master-model-pubs-northwind-distribution-msdb') > 0   UPDATE  ##filestats SET     FileTotalSizeMB = ROUND(FileTotalSizeMB * 64 / 1024,                                 2),         FileUsedSpaceMB = ROUND(FileUsedSpaceMB * 64 / 1024,                                 2) WHERE   FileFreeSpaceMB IS NULL   UPDATE  ##filestats SET     FileFreeSpaceMB = FileTotalSizeMB - FileUsedSpaceMB WHERE   FileFreeSpaceMB IS NULL   UPDATE  ##helpfile SET     FileGroupID = 0 WHERE   FileUsage = 'log only'   UPDATE  ##helpfile SET     FileGroupID = b.FileGroupID,         FileTotalSizeMB = b.FileTotalSizeMB,         FileUsedSpaceMB = b.FileUsedSpaceMB,         FileFreeSpaceMB = b.FileFreeSpaceMB FROM    ##helpfile a,         ##filestats b WHERE   a.FilePath = b.FilePath         AND a.FileUsage = 'data only'   UPDATE  ##helpfile SET     FileTotalSizeMB = ROUND(CAST(REPLACE(FileTotalSizeKB, ' KB', '') AS dec(19, 4))                                 / 1024, 2) WHERE   FileTotalSizeMB IS NULL   UPDATE  ##helpfile SET     FileUsedSpaceMB = ROUND(FileTotalSizeMB                                 * b.LogFileSpaceUsedpct                                 * 0.01, 2),         FileFreeSpaceMB = ROUND(FileTotalSizeMB * ( 100 - b.LogFileSpaceUsedpct )                                 * 0.01, 2) FROM    ##helpfile a,         ##sqlperf b WHERE   a.dbname = b.dbname         AND a.FileUsage = 'log only'   DECLARE @obsvdate DATETIME SET @obsvdate = GETDATE() UPDATE  ##helpfile SET     ObsvDate = @obsvdate WHERE   ObsvDate IS NULL     -- 97 : 122 = a to z -- 65 : 90  = A to Z UPDATE  ##helpfile SET     FilePath = STUFF(FilePath, 1, 1,                          UPPER(LEFT(FilePath, 1))) WHERE   UNICODE(LEFT(FilePath, 1)) BETWEEN 97 AND 122   UPDATE  ##helpfile SET     servername = @@servername WHERE   ServerName IS NULL   SELECT  ObsvDate,         ServerName,         DbName,         FileLogicalName,         FileID,         FilePath,         FileGroupID,         FileGroupName,         FileTotalSizeKB,         FileTotalSizeMB,         FileUsedSpaceMB,         FileFreeSpaceMB,         FileMaxSizeSetting,         FileGrowthSetting,         FileUsage FROM    ##helpfile ORDER BY FilePath   DROP TABLE ##helpfile DROP TABLE ##filestats DROP TABLE ##sqlperf     SET Nocount OFF