SQLServer Note

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)