Monday, April 23, 2012

View / Grant / Revoke Security permissions

--view security definitions
select sys.schemas.name 'Schema', sys.objects.name Object, sys.database_principals.name username, sys.database_permissions.type permissions_type,
sys.database_permissions.permission_name,
sys.database_permissions.state permission_state,
sys.database_permissions.state_desc,
state_desc + ' ' + permission_name + ' on ['+ sys.schemas.name + '].[' + sys.objects.name + '] to [' + sys.database_principals.name + ']' COLLATE LATIN1_General_CI_AS
from sys.database_permissions
join sys.objects on sys.database_permissions.major_id =
sys.objects.object_id
join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
join sys.database_principals on sys.database_permissions.grantee_principal_id =
sys.database_principals.principal_id
order by 1, 2, 3, 5

--revoke permission
select
'USE ['+DB_NAME()+']' + ' REVOKE VIEW DEFINITION ON [dbo].['+sys.objects.name+'] FROM ['+sys.database_principals.name+']'
,*
from sys.database_permissions
join sys.objects on sys.database_permissions.major_id = sys.objects.object_id
join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
join sys.database_principals on sys.database_permissions.grantee_principal_id = sys.database_principals.principal_id
where permission_name = 'VIEW DEFINITION'
and sys.database_principals.name = 'CSODMGMT\SQLAllDBAccess'

Thursday, April 19, 2012

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

Monday, April 02, 2012

New columnstore index feature in SQL Server 2012

Thursday, March 29, 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
)
)

Monday, March 26, 2012

Verify SQL Server Stored Procedures are the same on multiple servers

Monday, March 19, 2012

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

Friday, March 16, 2012

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

Wednesday, March 14, 2012

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

Useful SQL Server scripts