SQL Server Note

1. Grant necessory permission to the 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 (+) 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
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 ', @[email protected],@[email protected];
SELECT @[email protected]+1
END
GO

-----Identify the top 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 is 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 are 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 is 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 ')) are 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 '),
[Deadlockobject] = @deadlock. Value ('/deadlock-list[1]/deadlock[1]/resource-list[1]/objectlock[1]/@objectname ', ' varchar '),
[victim] = case when Deadlock.Process.value (' @id ', ' varchar ') = @deadlock. Value ('/deadlock-list[1]/deadlock[1 ]/@victim ', ' varchar ') then 1 else 0 end,
[Procedure] = Deadlock.Process.value (' executionstack[1]/frame[1]/@procname [1] ', ' varchar '),
[Lockmode] = Deadlock.Process.value (' @lockMode ', ' char (1) '),
[Code] = Deadlock.Process.value (' executionstack[1]/frame[1] ', ' varchar '),
[ClientApp] = Deadlock.Process.value (' @clientapp ', ' varchar '),
[HostName] = Deadlock.Process.value (' @hostname ', ' varchar '),
[LoginName] = Deadlock.Process.value (' @loginname ', ' varchar '),
[Transactiontime] = Deadlock.Process.value (' @lasttranstarted ', ' datetime '),
[InputBuffer] = Deadlock.Process.value (' inputbuf[1] ', ' varchar (+) ')
From @deadlock. Nodes ('/deadlock-list/deadlock/process-list/process ') as deadlock (process)