Select Top 100 Slow Queries

SELECT DISTINCT TOP 100
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
WHERE s.execution_count > 1
ORDER BY AvgElapsedTime desc , s.max_elapsed_time DESC, ExecutionCount DESC;
GO

View SQL cached Plans and clean the execution cached plans

use Master
go
--clear cache
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
go

USE master
GO
--view cached plans
SELECT UseCounts,RefCounts, Cacheobjtype, Objtype,
ISNULL(DB_NAME(dbid),'ResourceDB') AS DatabaseName, TEXT AS SQL
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
ORDER BY dbid,usecounts DESC;
GO

sp_kill

use master
go

if exists (select name from sysobjects where name = 'sp_kill' and type = 'p' )
drop proc sp_kill
go

create proc sp_kill
@dbname varchar(100) = null, -- When specified, kills all spids inside of the database
@loginame varchar(50) = null, -- When specified, kills all spids under the login name
@hostname varchar(50) = null -- When specified, kills all spids originating from the host machine
as
begin
set nocount on
select spid, db_name(dbid) as 'db_name', loginame, hostname into #tb1_sysprocesses from master.dbo.sysprocesses (nolock)
declare @total_logins int, @csr_spid varchar(100)
set @total_logins = ( select count(distinct spid) from #tb1_sysprocesses )
if @dbname is null
begin
if @loginame is null
begin
if @hostname is null
begin
if @total_logins > 0
begin
declare csr_spid cursor fast_forward for select distinct spid from #tb1_sysprocesses where loginame <> 'sa' and spid <> @@spid
open csr_spid
fetch next from csr_spid into @csr_spid
while @@fetch_status = 0
begin
set nocount on
exec ('kill ' + @csr_spid)
fetch next from csr_spid into @csr_spid
end
close csr_spid
deallocate csr_spid
end
end
else
begin
if @total_logins > 0
begin
declare csr_spid cursor fast_forward for select distinct spid from #tb1_sysprocesses where hostname = @hostname and loginame <> 'sa' and spid <> @@spid
open csr_spid
fetch next from csr_spid into @csr_spid
while @@fetch_status = 0
begin
set nocount on
exec ('kill ' + @csr_spid)
fetch next from csr_spid into @csr_spid
end
close csr_spid
deallocate csr_spid
end
end
--------------------------------------------------
end
else
begin
if @total_logins > 0
begin
declare csr_spid cursor fast_forward for select distinct spid from #tb1_sysprocesses where loginame = @loginame and loginame <> 'sa' and spid <> @@spid
open csr_spid
fetch next from csr_spid into @csr_spid
while @@fetch_status = 0
begin
set nocount on
exec ('kill ' + @csr_spid)
fetch next from csr_spid into @csr_spid
end
close csr_spid
deallocate csr_spid
end

end
-----------------------
end
else
begin
if @total_logins > 0
begin
declare csr_spid cursor fast_forward for select distinct spid from #tb1_sysprocesses where db_name = @dbname and loginame <> 'sa' and spid <> @@spid
open csr_spid
fetch next from csr_spid into @csr_spid
while @@fetch_status = 0
begin
set nocount on
exec ('kill ' + @csr_spid)
fetch next from csr_spid into @csr_spid
end
close csr_spid
deallocate csr_spid
end

end
drop table #tb1_sysprocesses
end

Tracking Changes in Your Enterprise Database

Tracking Changes in Your Enterprise Database

ASCII Character Codes

ASCII Character Codes

Is ISOLATION LEVEL READ UNCOMMITTED same as WITH (NOLOCK)

In one word, YES they are the same.

Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions. When this option is set, it is possible to read uncommitted modifications, which are called dirty reads. Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the isolation levels.

Check here for more detailed information


Also here is another document :
Read Committed Isolation Level

Find out all FK CONSTRAINT that exist on a table

declare @tableName varchar(200)
set @tableName = 'ssis_tasks'

select * from sys.foreign_keys k
inner join sys.tables t on t.[object_id] = k.referenced_object_id
where t.[name] = @tableName

SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where CONSTRAINT_NAME in
(
select k.name from sys.foreign_keys k
inner join sys.tables t on t.[object_id] = k.referenced_object_id
where t.[name] = @tableName
)

Run SP automatically when SQL Server starts

EXECUTE sp_procoption
@procname = 'usp_StartTrace',
@optionname = 'startup',
@optionvalue = 'on'

Identify Primary Keys and PK column name

SELECT KU.*
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME
ORDER BY KU.TABLE_NAME, KU.ORDINAL_POSITION

Change Tracking with SQL 2008

USE [Test]
GO

DROP TABLE [ChangeTrack]
GO

CREATE TABLE [dbo].[ChangeTrack](
[LineID] [int] IDENTITY(1000,1) NOT NULL,
[Fname] [nvarchar](100) NULL,
[Lname] [nvarchar](100) NULL,
[InsertDate] [datetime] NULL,
CONSTRAINT [PK_ChangeTrack] PRIMARY KEY CLUSTERED
(
[LineID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ChangeTrack] ADD CONSTRAINT [DF_ChangeTrack_InsertDate] DEFAULT (getdate()) FOR [InsertDate]
GO
ALTER TABLE [dbo].[ChangeTrack] ENABLE CHANGE_TRACKING WITH(TRACK_COLUMNS_UPDATED = ON)
GO

declare @synchronization_version bigint , @last_synchronization_version bigint

set @last_synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()
waitfor delay '00:00:05'

--load table
insert into [ChangeTrack] (Fname, Lname ) values ('Pat','Rick'), ('Pat','Rick'),('Pat','Rick'),('Pat','Rick'),('Pat','Rick'),('Pat','Rick'),('Pat','Rick'),('Pat','Rick'),('Pat','Rick'),('Pat','Rick')
waitfor delay '00:00:05'

--Obtain initial data set.
--SELECT * FROM [ChangeTrack]


--Using the Change Tracking Functions to Obtain Changes
--declare @last_synchronization_version bigint
--set @last_synchronization_version = 1302
set transaction isolation level snapshot
begin transaction
SELECT
Getdate(), CT.[LineID], CT.SYS_CHANGE_OPERATION,
CT.SYS_CHANGE_COLUMNS, CT.SYS_CHANGE_CONTEXT
FROM
CHANGETABLE(CHANGES [ChangeTrack], @last_synchronization_version) AS CT

set @last_synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()
commit
/*
SELECT
CT.[LineID], P.*,
CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS,
CT.SYS_CHANGE_CONTEXT
FROM
[ChangeTrack] AS P
RIGHT OUTER JOIN
CHANGETABLE(CHANGES [ChangeTrack], @last_synchronization_version) AS CT
ON
P.[LineID] = CT.[LineID]
*/
--select CHANGE_TRACKING_CURRENT_VERSION();
waitfor delay '00:00:05'

--some updates
update [ChangeTrack] set Fname = Fname + '_CHANGED' where LineID % 10 = 4
waitfor delay '00:00:05'

--Using the Change Tracking Functions to Obtain Changes
--declare @last_synchronization_version bigint
--set @last_synchronization_version = 1312
set transaction isolation level snapshot
begin transaction
SELECT
Getdate(), CT.[LineID], CT.SYS_CHANGE_OPERATION,
CT.SYS_CHANGE_COLUMNS, CT.SYS_CHANGE_CONTEXT
FROM
CHANGETABLE(CHANGES [ChangeTrack], @last_synchronization_version) AS CT

set @last_synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()
commit
/*
SELECT
CT.[LineID], P.*,
CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS,
CT.SYS_CHANGE_CONTEXT
FROM
[ChangeTrack] AS P
RIGHT OUTER JOIN
CHANGETABLE(CHANGES [ChangeTrack], @last_synchronization_version) AS CT
ON
P.[LineID] = CT.[LineID]
*/
--select CHANGE_TRACKING_CURRENT_VERSION();
waitfor delay '00:00:05'

--some insert/update/delete
insert into [ChangeTrack] (Fname, Lname ) values ('Pat111','Rick111'),('Pat111','Rick111'),('Pat111','Rick111'),('Pat111','Rick111'),('Pat111','Rick111')
update [ChangeTrack] set Fname = '123' where LineID % 10 = 5
delete from [ChangeTrack] where LineID % 10 = 6
waitfor delay '00:00:05'

--select * from [ChangeTrack]

--Using the Change Tracking Functions to Obtain Changes
--declare @last_synchronization_version bigint
--set @last_synchronization_version = 1313
set transaction isolation level snapshot
begin transaction
SELECT
Getdate(), CT.[LineID], CT.SYS_CHANGE_OPERATION,
CT.SYS_CHANGE_COLUMNS, CT.SYS_CHANGE_CONTEXT
FROM
CHANGETABLE(CHANGES [ChangeTrack], @last_synchronization_version) AS CT

set @last_synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()
commit
/*
SELECT
CT.[LineID], P.*,
CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS,
CT.SYS_CHANGE_CONTEXT
FROM
[ChangeTrack] AS P
RIGHT OUTER JOIN
CHANGETABLE(CHANGES [ChangeTrack], @last_synchronization_version) AS CT
ON
P.[LineID] = CT.[LineID]
*/
--select CHANGE_TRACKING_CURRENT_VERSION()