SQL Server Service Broker example on how to configure, send and receive messages

SQL Server Service Broker example on how to configure, send and receive messages

Comparing performance for the MERGE statement to SELECT, INSERT, UPDATE or DELETE

Comparing performance for the MERGE statement to SELECT, INSERT, UPDATE or DELETE

New columnstore index feature in SQL Server 2012

New columnstore index feature in SQL Server 2012

SQL Jobs with Multiple schedules

use msdb
go
/*
select * from sysjobs
select * from sysjobschedules
*/

select a.job_id , a.name,  COUNT(*)
from sysjobs a
inner join sysjobschedules b on a.job_id = b.job_id
group by a.job_id , a.name
having COUNT(*) > 1



select c.name, b.*, a.*
from sysschedules a
inner join sysjobschedules b on a.schedule_id = b.schedule_id
inner join sysjobs c on c.job_id = b.job_id
where a.schedule_id in
(
select schedule_id from sysjobschedules
where job_id in
(
select a.job_id
from sysjobs a
inner join sysjobschedules b on a.job_id = b.job_id
group by a.job_id , a.name
having COUNT(*) > 1
)
)

Verify SQL Server Stored Procedures are the same on multiple servers

Verify SQL Server Stored Procedures are the same on multiple servers

Long Running Queries Alert

use master
go

/*
exec sp_long_running_queries @longduration = 15 , @sendalertemail = 1
*/
ALTER procedure sp_long_running_queries
(
@longduration int = 15 -- minutes
, @sendalertemail int = 0
)
as
BEGIN

CREATE TABLE #_tmp_whoisactive
( [dd hh:mm:ss.mss] varchar(8000) NULL,[session_id] smallint NOT NULL,[sql_text] xml NULL,[login_name] nvarchar(128) NOT NULL,[wait_info] nvarchar(4000) NULL,[CPU] varchar(30) NULL,[tempdb_allocations] varchar(30) NULL,[tempdb_current] varchar(30) NULL,[blocking_session_id] smallint NULL,[reads] varchar(30) NULL,[writes] varchar(30) NULL,[physical_reads] varchar(30) NULL,[used_memory] varchar(30) NULL,[status] varchar(30) NOT NULL,[open_tran_count] varchar(30) NULL,[percent_complete] varchar(30) NULL,[host_name] nvarchar(128) NULL,[database_name] nvarchar(128) NULL,[program_name] nvarchar(128) NULL,[start_time] datetime NOT NULL,[login_time] datetime NULL,[request_id] int NULL,[collection_time] datetime NOT NULL)

exec sp_WhoIsActive @destination_table = '#_tmp_whoisactive'

--select datediff(mi,start_time,collection_time) , * from #_tmp_whoisactive where datediff(mi,start_time,collection_time) > @longduration

if @sendalertemail = 1 and exists ( select * from #_tmp_whoisactive where datediff(mi,start_time,collection_time) > @longduration)
begin

DECLARE @tableHTML  NVARCHAR(MAX) , @subjectstring nvarchar(200);

SET @tableHTML =
N'

Long Runing Queries on '+@@SERVERNAME+' executing over '+convert(nvarchar(10),@longduration)+' minutes

' +
N'
' +
N'
' +
N'
' +
N'
' +
CAST ( ( SELECT td = [dd hh:mm:ss.mss],       '',
td = session_id, '',
td = login_name, '',
td = database_name, '',
td = host_name, '',
td = Status
 FROM #_tmp_whoisactive
 where datediff(mi,start_time,collection_time) > @longduration
 FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'

Time Elapsed	Session	Login Name	Database	Executing Host	Status

' ;

--print @tableHTML
 
set @subjectstring = 'Long Running Queries over '+convert(nvarchar(10),@longduration)+' min on SQL Server '+@@SERVERNAME
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'default',
@recipients = 'DLProductionDBA@cornerstoneondemand.com;jferris@csod.com',
--@recipients ='palexander@csod.com',
@body = @tableHTML,
@body_format = 'HTML' ,
@subject = @subjectstring;
 
end

drop table #_tmp_whoisactive

END
GO

If Primary Key Exists

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE  CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'user_career_pref_date' AND TABLE_SCHEMA ='dbo'

if not exists(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE  CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'user_career_pref_date' AND TABLE_SCHEMA ='dbo' )

begin

ALTER TABLE [dbo].[user_career_pref_date]

    ADD CONSTRAINT [PK_user_career_pref_date] PRIMARY KEY CLUSTERED ([user_id] ASC, [question_id] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);

end

Proc to show expensive queries : sp_expensive_queries

USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[sp_expensive_queries]
as
BEGIN


SELECT TOP 10
[Average IO] = (total_logical_reads + total_logical_writes) / qs.execution_count
,[Total IO] = (total_logical_reads + total_logical_writes)
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average IO] DESC;

SELECT TOP 10
[Average CPU used] = total_worker_time / qs.execution_count
,[Total CPU used] = total_worker_time
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
 ELSE qs.statement_end_offset END -
qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average CPU used] DESC;

SELECT TOP 10
[Total Logical Reads] = qs.total_logical_reads,
[Last Logical Reads] = qs.last_logical_reads,
[Total Logical Writes] = qs.total_logical_writes,
[Last Logical Writes] = qs.last_logical_writes,
[Execution count] = qs.execution_count,
[Individual Query] = 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),
[Parent Query] = qt.text,
DatabaseName = DB_NAME(qt.dbid),
[Total Worker Time] = qs.total_worker_time,
[Last Worker Time] = qs.last_worker_time,
[Total Elapsed Time Seconds] = qs.total_elapsed_time/1000000 ,
[Last Elapsed Time Seconds] = qs.last_elapsed_time/1000000 ,
[Last Execution Time] = qs.last_execution_time,
[Query Plan] = 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

SELECT TOP 10
[Total Reads] = SUM(total_logical_reads)
,[Execution count] = SUM(qs.execution_count)
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY DB_NAME(qt.dbid)
ORDER BY [Total Reads] DESC;

END

Automate Monitoring SQL Server Error Logs with Email Alerts

Automate Monitoring SQL Server Error Logs with Email Alerts

Get Alerts for Specific SQL Server Login Failed Events

Get Alerts for Specific SQL Server Login Failed Events