1. Grant necessory permission to user account, so it can use SQL profiler.

USE master

GRANT ALTER TRACE TO TEST;

GRANT VIEW SERVER STATE TO TEST;

USE testDB

GRANT VIEW DATABASE STATE TO TEST;

GRANT SHOWPLAN TO TEST;

 

2. SQL Server Computed Column Example

CREATE TABLE dbo.Products 

(

ProductID int IDENTITY (1,1) NOT NULL

, QtyAvailable smallint

, UnitPrice money

, InventoryValue AS QtyAvailable * UnitPrice persisted

);

-- Insert values into the table.

INSERT INTO dbo.Products (QtyAvailable, UnitPrice)

VALUES (25, 2.00), (10, 1.5);

-- Display the rows in the table.

SELECT ProductID, QtyAvailable, UnitPrice, InventoryValue

FROM dbo.Products;

ALTER TABLE dbo.Products ADD RetailValue AS (QtyAvailable * UnitPrice * 1.35);

ALTER TABLE dbo.Products DROP COLUMN RetailValue;

GO

ALTER TABLE dbo.Products ADD RetailValue AS (QtyAvailable * UnitPrice * 1.5) persisted;

 

3. Useful SQL statement using DMV

------------------------find the most cost missing index---------------------

set transaction isolation level read uncommitted

select top 20

round(s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks+s.user_scans),0) as [Total Cost]

, s.avg_user_impact

, d.statement as tableName

, d.equality_columns

, d.inequality_columns

, d.included_columns

from sys.dm_db_missing_index_groups g

inner join sys.dm_db_missing_index_group_stats s

on s.group_handle=g.index_group_handle

inner join sys.dm_db_missing_index_details d

on d.index_handle = g.index_handle

order by [Total Cost] DESC

------Populate tables with sp_executesql---------

set nocount on;

go

DECLARE @val INT

SELECT @val=1

WHILE @val < 100000

BEGIN

exec sp_executesql N'INSERT INTO dbo.Customer(CustomerID, CustomerName) VALUES(@v1,@v2)', N'@v1 int,@v2 int',@v1=@val,@v2=@val;

SELECT @val=@val+1

END

GO

-----identify the top 20 slowest sql -----

select top 20

cast(qs.total_elapsed_time/1000000 as decimal(28,2)) as [Total Elaspsed Duration(s)],

qs.execution_count,

SUBSTRING

(

qt.text,

qs.statement_start_offset/2+1,

(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)+ 1

)

as [Individual Query]

,

qt.text as [Parent Query],

DB_NAME(qt.dbid) as [Database Name],

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 total_elapsed_time

----------identify what SQL is running now---------

select

er.session_id AS [SPID]

, sp.ecid

, db_name(sp.dbid) as [Database]

, sp.nt_username

, er.status

, er.wait_type

, SUBSTRING

(

qt.text,

er.statement_start_offset/2+1,

(case when er.statement_end_offset = -1 then len(convert(NVARCHAR(MAX),qt.text)) * 2 else er.statement_end_offset end - er.statement_start_offset/2)+ 1

)

as [Individual Query]

, qt.text as [Parent Query]

, sp.program_name

, sp.hostname

, sp.nt_domain

, er.start_time

from sys.dm_exec_requests er

inner join sys.sysprocesses sp on er.session_id = sp.spid

cross apply sys.dm_exec_sql_text(er.sql_handle) as qt

where er.session_id>50

and er.session_id NOT in(@@spid)

order by er.session_id, ecid

------------------ if xxx is not null-----------------

IF (select 1 from sys.indexes where name='PK_CUSTOMERID' and object_id=object_id('dbo.customer')) is not null

BEGIN

DROP INDEX DBO.CUSTOMER.PK_CUSTOMERID;

END

create index [PK_CUSTOMERID] ON DBO.CUSTOMER(CUSTOMERID) include(customername)

-----------------------------fn_get_sql  and sys.dm_exec_sql_text function ------------------------------------

select * from fn_get_sql(0x0300FF7F8FB46906B80B0F01D49A00000100000000000000);

select * from sys.dm_exec_query_stats cross apply fn_get_sql(sql_handle);

select * from sys.dm_exec_query_stats cross apply sys.dm_exec_sql_text(sql_handle);

fn_get_sql and sys.dm_exec_sql_text are the same

 

-----------------------------Query deadlock xml value------------------------------------

declare @deadlock xml

set @deadlock = 'put your deadlock graph here'

select

[PagelockObject] = @deadlock.value('/deadlock-list[1]/deadlock[1]/resource-list[1]/pagelock[1]/@objectname', 'varchar(200)'),

[DeadlockObject] = @deadlock.value('/deadlock-list[1]/deadlock[1]/resource-list[1]/objectlock[1]/@objectname', 'varchar(200)'),

[Victim] = case when Deadlock.Process.value('@id', 'varchar(50)') = @deadlock.value('/deadlock-list[1]/deadlock[1]/@victim', 'varchar(50)') then 1 else 0 end,

[Procedure] = Deadlock.Process.value('executionStack[1]/frame[1]/@procname[1]', 'varchar(200)'),

[LockMode] = Deadlock.Process.value('@lockMode', 'char(1)'),

[Code] = Deadlock.Process.value('executionStack[1]/frame[1]', 'varchar(1000)'),

[ClientApp] = Deadlock.Process.value('@clientapp', 'varchar(100)'),

[HostName] = Deadlock.Process.value('@hostname', 'varchar(20)'),

[LoginName] = Deadlock.Process.value('@loginname', 'varchar(20)'),

[TransactionTime] = Deadlock.Process.value('@lasttranstarted', 'datetime'),

[InputBuffer] = Deadlock.Process.value('inputbuf[1]', 'varchar(1000)')

from @deadlock.nodes('/deadlock-list/deadlock/process-list/process') as Deadlock(Process)