This is a great DMV if you don’t want to leave SSMS to see the CPU usage or if you don’t have access. What’s good about this method is it also shows you the history of the cpu usage. [cc lang=”sql”] declare @ts_now bigint select @ts_now = cpu_ticks / (cpu_ticks/ms_ticks) from sys.dm_os_sys_info; select record_id, dateadd(ms, -1 * (@ts_now – [timestamp]), GetDate()) as EventTime, SQLProcessUtilization, SystemIdle, 100 – SystemIdle – SQLProcessUtilization as OtherProcessUtilization from ( select record.value(‘(./Record/@id)[1]’, ‘int’) as record_id, record.value(‘(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]’, ‘int’) as SystemIdle, record.value(‘(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]’, ‘int’) as SQLProcessUtilization, timestamp from ( select timestamp, convert(xml, record) as record from sys.dm_os_ring_buffers where ring_buffer_type = N’RING_BUFFER_SCHEDULER_MONITOR’ and record like ‘%%’) as x ) as y order by record_id desc [/cc]
Continue reading ...
This gem of a query has been indispensable in determining IO latency for our servers. The benefit of this is not having to rely on perfmon to interpret the latency (which can be a crapshoot). Here, SQL itself using SQL OS (I believe) to measure latency and usage statistics. In the past, perfmon has been sketchy in monitoring physical disk stats because it is not reliable when measuring a SAN. The old trick was to make sure perfmon’s refresh interval was below 2 second refresh in order to get good statistics. Now with this, we can see the exact latency of read / writes against the actual files. Thanks to my partner Chris for providing this. [cc lang=”sql”] SELECT DB_NAME(fs.database_id) AS [Database Name], mf.physical_name, io_stall_read_ms, num_of_reads, CAST(io_stall_read_ms/(1.0 + num_of_reads) AS NUMERIC(10,1)) AS [avg_read_stall_ms],io_stall_write_ms, num_of_writes, CAST(io_stall_write_ms/(1.0+num_of_writes) AS NUMERIC(10,1)) AS [avg_write_stall_ms], io_stall_read_ms + io_stall_write_ms AS [io_stalls], num_of_reads + num_of_writes AS [total_io], CAST((io_stall_read_ms + io_stall_write_ms)/(1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1)) AS [avg_io_stall_ms] FROM sys.dm_io_virtual_file_stats(null,null) AS fs INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id AND fs.[file_id] = mf.[file_id] ORDER BY avg_io_stall_ms DESC; [/cc]
Continue reading ...
I recently had a need to monitor inserts against a particular table in order to determine what kind of locks they were acquiring. Being that we could not run traces on the system, I had to resort to a roll-your-own approach to monitoring the table. The goal was to determine what kind of locking was occurring, and it would also be nice to be able to associate it to the executing query. The following code uses DMVs in order to trap a sample of the queries running against it. Please note that this will not trap all queries, however it will work in getting a good number of samples. This script will run in an endless loop so be sure to hit cancel at some point. Also, for some reason the SQL Statement will not always be trapped. It was not that important for me, as I mostly needed the locks, however if someone figures it out, please post. [cc lang=”sql”] — Capture query activity against a table using DMVs DECLARE @TableName varchar(255); — Specify the table you want to monitor SET @TableName = ‘Sales.SalesOrderDetail’; DECLARE @ObjectID int; SET @ObjectID = (SELECT OBJECT_ID(@TableName)); IF OBJECT_ID(‘tempdb..##Activity’) IS NOT NULL BEGIN DROP TABLE ##Activity; END; — Create table SELECT TOP 0 * INTO ##Activity FROM sys.dm_tran_locks WITH (NOLOCK); — Add additional columns ALTER TABLE ##Activity ADD SQLStatement VARCHAR(MAX), SQLText VARCHAR(MAX), LoginName VARCHAR(200), HostName VARCHAR(50), Transaction_Isolation VARCHAR(100), DateTimeAdded DATETIME; DECLARE @Rowcount INT = 0; WHILE 1 = 1 BEGIN INSERT INTO ##Activity SELECT […]
Continue reading ...
Often times when troubleshooting, it is helpful to know how much resources a particular application is requesting from your SQL Server. The easiest way to get this information is by using the connections and sessions DMV. These DMV’s when combined are powerful. Here we will query the aggregate connection information. The values below represent an aggregate value of all connections that are currently established by that application. [cc lang=”sql”] SELECT CPU = SUM(cpu_time) ,WaitTime = SUM(total_scheduled_time) ,ElapsedTime = SUM(total_elapsed_time) ,Reads = SUM(num_reads) ,Writes = SUM(num_writes) ,Connections = COUNT(1) ,Program = program_name FROM sys.dm_exec_connections con LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = con.session_id GROUP BY program_name ORDER BY cpu DESC [/cc] Note that the above query does not break down the values per user. In order to do that, we need to create an additional grouping condition (login_name). [cc lang=”sql”] SELECT CPU = SUM(cpu_time) ,WaitTime = SUM(total_scheduled_time) ,ElapsedTime = SUM(total_elapsed_time) ,Reads = SUM(num_reads) ,Writes = SUM(num_writes) ,Connections = COUNT(1) ,Program = program_name ,LoginName = ses.login_name FROM sys.dm_exec_connections con LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = con.session_id GROUP BY program_name, ses.login_name ORDER BY cpu DESC [/cc]
Continue reading ...
An important step in optimizing a system is to take a holistic approach and look at stored procedures that are called very often. These procedures can often be the backbone of a system. Sometimes optimizing these procedures can have a dramatic effect on relieving the physical bottlenecks of the system and improving end-user experience. The following DMV query shows the execution count of each stored procedure, sorted by the most executed procedures first. [cc lang=”sql”] SELECT DatabaseName = DB_NAME(st.dbid) ,SchemaName = OBJECT_SCHEMA_NAME(st.objectid,dbid) ,StoredProcedure = OBJECT_NAME(st.objectid,dbid) ,ExecutionCount = MAX(cp.usecounts) FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st WHERE DB_NAME(st.dbid) IS NOT NULL AND cp.objtype = ‘proc’ GROUP BY cp.plan_handle ,DB_NAME(st.dbid) ,OBJECT_SCHEMA_NAME(objectid,st.dbid) ,OBJECT_NAME(objectid,st.dbid) ORDER BY MAX(cp.usecounts) DESC [/cc] These execution counts are an aggregate from the last time SQL Server has been restarted.
Continue reading ...
