Query to keep CPU busy

 

-- set for 3 minutes below, do not run on production
-- ideal for testing CPU usage alerts

Declare @T DateTime,
@F Bigint;
Set @T=GetDate();
While DATEADD(Second,180,@T)>GETDATE()
Set @F=POWER(2,30);

cpuUsageAlert

/***** threshold is currently set to 20% checks recent CPU activity in the last minute *****/



CREATE PROC cpuUsageAlert



AS
 SET NOCOUNT ON



/***** CPU Utilization history *****/

-- Get CPU Utilization History (SQL Server 2008 and above)
 DECLARE @ts BIGINT



--DROP Table #tempCPURecords
 SELECT @ts =(SELECT cpu_ticks/(cpu_ticks/ms_ticks)

FROM sys.dm_os_sys_info);

SELECT TOP(1)SQLProcessUtilization AS [SQLServer_Process_CPU_Utilization],

SystemIdle AS [System_Idle_Process],

100 - SystemIdle - SQLProcessUtilization AS [Other_Process_CPU_Utilization],

DATEADD(ms,-1 *(@ts - [timestamp]),GETDATE())AS [Event_Time]

into #tempCPURecords

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;

select * from #tempCPURecords

if exists(select 1 from #tempCPURecords

where SQLServer_Process_CPU_Utilization > 20

and Event_Time>dateadd(mm,-1,getdate()))



begin
 print 'CPU Alert Condition Ture, Sending Email..'

DECLARE @tableHTML NVARCHAR(MAX) ;

SET @tableHTML =

N'<H1>High CPU Utilization Reported</H1>' +

N'<H2>SQL Server Session Details</H2>' +

N'<table border="1">' +

N'<tr><th>SPID</th><th>Status</th><th>Login</th><th>Host</th><th>BlkBy</th>'+

N'<th>DatabaseID</th><th>CommandType</th><th>SQLStatement</th><th>ElapsedMS</th>'+

N'<th>CPUTime</th><th>IOReads</th><th>IOWrites</th><th>LastWaitType</th>'+

N'<th>StartTime</th><th>Protocol</th><th>ConnectionWrites</th>'+

N'<th>ConnectionReads</th><th>ClientAddress</th><th>Authentication</th></tr>'+

CAST ( ( SELECT TOP 50 -- or all by using *

td= er.session_id,'',

td= ses.status,'',

td= ses.login_name,'',

td= ses.host_name,'',

td= er.blocking_session_id,'',

td= er.database_id,'',

td= er.command,'',

td= st.text,'',

td= er.total_elapsed_time,'',

td= er.cpu_time,'',

td= er.reads,'',

td= er.writes,'',

td= er.last_wait_type,'',

td= er.start_time,'',

td= con.net_transport,'',

td= con.num_writes,'',

td= con.num_reads,'',

td= con.client_net_address,'',

td= con.auth_scheme,''

FROM sys.dm_exec_requests er

OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st

LEFT JOIN sys.dm_exec_sessions ses

ON ses.session_id = er.session_id LEFT JOIN sys.dm_exec_connections con

ON con.session_id = ses.session_id

WHERE er.session_id > 50

ORDER BY er.cpu_time DESC ,er.blocking_session_id

FOR XML PATH('tr'), TYPE

)AS NVARCHAR(MAX))+

N'</table>'



-- Change SQL Server Email notification code here
 EXEC msdb.dbo.sp_send_dbmail

@recipients='shane.ryan@email.com,

@profile_name = 'ServiceDesk',

@subject = 'ServerName:Last 5 Minutes Avg CPU Utilization Over 80%',

@body = @tableHTML,

@body_format = 'HTML';



/** testing code

-- drop table temp_table

create table temp_table

(

cola varchar(max)

)

INSERT INTO [dba].[dbo].[temp_table] (cola)

select @tableHTML

select * from temp_table

**/
 END
 -- Drop the Temporary Table
 DROP Table #tempCPURecords

 

WhatIsGoingOn?

# joined DMV view useful for looking at current blocks  # step 1 create view CREATE VIEW WhatIsGoingOn

AS

SELECT

OBJECT_NAME(objectid) as ObjectName

,SUBSTRING(stateText.text, (statement_start_offset/2)+1,

((CASE statement_end_offset

WHEN -1 THEN DATALENGTH(stateText.text)

ELSE statement_end_offset

END - statement_start_offset)/2) + 1) AS statement_text

,DB_Name(database_id) as DatabaseName

,req.cpu_time AS CPU_Time

,DATEDIFF(minute, last_request_start_time, getdate()) AS RunningMinutes

,req.Percent_Complete

,sess.HOST_NAME as RunningFrom

,LEFT(CLIENT_INTERFACE_NAME, 25) AS RunningBy

,sess.session_id AS SessionID

,req.blocking_session_id AS BlockingWith

,req.reads

,req.writes

,sess.[program_name]

,sess.login_name

,sess.status

,sess.last_request_start_time

,req.logical_reads

FROM

sys.dm_exec_requests req

INNER JOIN sys.dm_exec_sessions sess ON sess.session_id = req.session_id

AND sess.is_user_process = 1

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS stateText

# step 2 create logging table

CREATE TABLE tblBlockingDetails
(
ID INT IDENTITY(1,1),
ObjectName SYSNAME NULL,
BlockingObjectName SYSNAME NULL,
StatementText VARCHAR(MAX),
BlockingStatementText VARCHAR(MAX),
SessionID INT,
BlockingSessionID INT,
Status VARCHAR(50),
BlockingStatus VARCHAR(50),
DateCreated DATETIME
)


# step3 create procedure to insert rows into the logging table
#After creating tblBlockingDetails, create the Stored Procedure uspInsertBlockingRecords below which will capture #details of any session being locked and insert that detail in “tblBlockingDetails” table.

CREATE PROC uspInsertBlockingRecords
AS
INSERT INTO tblBlockingDetails
SELECT
W1.ObjectName AS ObjectName,
W2.ObjectName AS BlockingObjectName,
W1.Statement_Text AS StatementText,
W2.Statement_Text AS BlockingStatementText,
W1.sessionid AS SessionID,
W2.sessionid AS BlockingSessionID,
W1.status AS Status,
W2.status AS BlockingStatus,
GETDATE() AS DateCreated
FROM
WhatIsGoingOn W1 INNER JOIN WhatIsGoingOn W2
ON
W1.BlockingWith > 0
AND W1.BlockingWith = W2.sessionid

Now, you can schedule SP uspInsertBlockingRecords every few minutes during business hours in SQL Server Job Agent so you can view the details of blocking processes anytime from table tblBlockingDetails. Its for example also possible to create an email alert to notify a dba group if blocking processes are active (e.g. RunningMinutes > 10 minutes). In this way its possible for a DBA to be proactive in resolving blocking issues. Also useful for performance tuning with some minor tweaks.

@DBAreactions Guide to In-Memory OLTP (Hekaton)

Hekaton (also known as SQL Server In-Memory OLTP) is an in-memory database for OLTP workloads built into Microsoft SQL Server Hekaton was designed in collaboration with Microsoft Research and was released in SQL Server 2014.

Brent Ozar says:

At SQLbits last month, I presented a new session: Cool Story, Bro – The DBAreactions Guide to SQL Server 2014. I wanted to have some fun while educating folks about the surprise gotchas of the newest features.
Here’s the In-Memory OLTP (Hekaton) section of the session:

Their sp_Blitz® has long warned you if Hekaton is in use, and its Hekaton detail page shows some of the limitations.

Exploring an ASP .NET Web Application

1. Creating a Basic Web Form and Connecting to a Database

2. Executing a Stored Procedure

 

3. Passing Parameters

 

4. Getting Return Values