SQL Server Love Linux:效能篇

/*

TRIVIAL Plan:針對簡單的查詢可能只有一種執行方法，好比一個Table沒有任何Index，

當要查詢該Table資料只有執行Full Table Scan方法

*/

select adress.City

from dbo.Address as adress

where adress.AddressID=1

SET NOCOUNT ON

SET STATISTICS IO

,TIME ON

--@query plan

select sales.UnitPrice

,trans.ReferenceOrderID

,'Total'=sum(sales.OrderQty)

from dbo.SalesOrderDetail sales join

dbo.TransactionHistory trans on

sales.SalesOrderID=trans.ReferenceOrderID

where trans.TransactionDate>='2007-05-11'

and trans.TransactionDate<='2007-10-10'

group by sales.UnitPrice,trans.ReferenceOrderID

--@scan count

SELECT *

FROM Person

WHERE BusinessEntityID = 1

SELECT FirstName

,Title

,EmailPromotion

FROM Person

WHERE FirstName = 'A. Scott'

SELECT *

FROM Person

WHERE BusinessEntityID IN (

1

,2

,3

,12

,13

,20

,31

,32

,33

,34

)

--how many scan counts?

SELECT *

FROM Person

WHERE BusinessEntityID >= 1

AND BusinessEntityID <= 3

OR BusinessEntityID = 12

SELECT *

FROM Person

WHERE BusinessEntityID > 0

AND BusinessEntityID < 10

SELECT *

FROM Person

WHERE BusinessEntityID > 155

AND BusinessEntityID < 500

SELECT *

FROM Person

WHERE BusinessEntityID < 50

OR BusinessEntityID = 400

SELECT *

FROM Person

WHERE BusinessEntityID < 10

AND BusinessEntityID <> 4

/*

不良運算子

Scan

Lookup

Hash

Nested Loops

*/

--@Scan

SELECT [AddressLine1]

,[City]

,[AddressLine2]

FROM dbo.Address

select max(BusinessEntityID)

from Person

select min(BusinessEntityID)

from Person

--seek or scan?

SELECT [AddressLine1]

,[City]

,[AddressLine2]

FROM dbo.Address

WHERE City = N'Paris'

-- AND  AddressLine1=N'1, rue de Maubeuge'

sp_helpindex 'dbo.Address'

SELECT

OBJECT_NAME(i.[object_id]) TableName ,

i.[name] IndexName ,

c.[name] ColumnName ,

ic.is_included_column ,

i.index_id ,

i.type_desc ,

i.is_unique ,

i.data_space_id ,

i.ignore_dup_key ,

i.is_primary_key ,

i.is_unique_constraint

FROM

sys.indexes i

JOIN sys.index_columns ic ON ic.object_id = i.object_id and i.index_id = ic.index_id

JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id

WHERE OBJECT_NAME(i.[object_id])='Address'

and i.[name] in ('idx1','idx3')

ORDER BY

tableName ,

ic.index_id ,

ic.index_column_id

DROP INDEX IF EXISTS uix_num ON dbo.[rsa241TestA]

DROP INDEX IF EXISTS ix_ModifiedDate ON dbo.[TransactionHistoryTestA]

SELECT b.TransactionID

,ProductID

,ReferenceOrderID

,ActualCost

,ModifiedDate

FROM rsa241TestA a

JOIN TransactionHistoryTestA b ON a.num = b.ProductID

WHERE ModifiedDate = '2008-02-01 00:00:00.000'

CREATE INDEX ix_ModifiedDate ON TransactionHistoryTestA (ModifiedDate) include (

ProductID

,TransactionID

,ReferenceOrderID

,ActualCost

)

CREATE UNIQUE INDEX uix_num ON rsa241TestA (num)

--@lookup

DROP INDEX IF EXISTS [idx_ModifiedDate_ProductID] ON [dbo].[TransactionHistoryTestA]

CREATE INDEX [idx_ModifiedDate_ProductID] ON [dbo].[TransactionHistoryTestA] (

[ModifiedDate] ASC,

[ProductID] ASC

) INCLUDE ([TransactionID]) ON [PRIMARY]

GO

SELECT [TransactionID]

,[ReferenceOrderID]

,Quantity

,[ModifiedDate]

FROM TransactionHistoryTestA WITH (INDEX (idx_ModifiedDate_ProductID))

WHERE [ProductID] > 500

AND [ProductID] < 1000

AND [ModifiedDate] > '2008-01-1'

AND [ModifiedDate] < '2008-03-01'

--@hash

DROP TABLE IF EXISTS #myrico

SELECT TOP(1000000)

SalesOrderID,

OrderDate,

Status,

TaxAmt

INTO #myrico

FROM dbo.BigSalesOrderHeader

WHERE

SalesOrderID BETWEEN 1 AND 4000000

GO

SELECT sum(TaxAmt)

FROM #myrico

GROUP BY OrderDate

CREATE INDEX idx_OrderDate ON #myrico (OrderDate) include (TaxAmt)

SELECT

COUNT(*)

FROM #myrico AS b

WHERE

EXISTS

(

SELECT

1

FROM dbo.BigSalesOrderHeader AS bso

WHERE

bso.OrderDate = b.OrderDate

AND bso.SalesOrderID = b.SalesOrderID

AND bso.SalesOrderID BETWEEN 1501 AND 2201

)

CREATE CLUSTERED INDEX cix_SalesOrderID_OrderDate

ON #myrico

(

OrderDate,

SalesOrderID

)

GO

--@Nested Loops

CREATE TABLE #t1 (

c1 INT

,name VARCHAR(100)

,storeon DATETIME

)

CREATE TABLE #t2 (

c1 INT

,name VARCHAR(100)

,storeon DATETIME

)

CREATE TABLE #t3 (

c1 INT

,name VARCHAR(100)

,storeon DATETIME

)

SET NOCOUNT ON

DECLARE @i INT = 0

WHILE (@i < 10000)

BEGIN

INSERT INTO #t1

VALUES (

1

,'rico' + cast(newid() AS VARCHAR(50))

,getdate()

)

INSERT INTO #t2

VALUES (

@i

,'rico' + cast(newid() AS VARCHAR(50))

,getdate()

)

INSERT INTO #t3

VALUES (

1

,'rico' + cast(newid() AS VARCHAR(50))

,getdate()

)

SET @i += 1

END

declare @mytest table

(

c1 INT

,name VARCHAR(100)

,storeon DATETIME

)

insert into @mytest

select * from #t2

select b.* from BigTestLIKE a join @mytest b

on a.Value=b.c1

where b.c1>=1 and b.c1<=10

select b.* from BigTestLIKE a join @mytest b

on a.Value=b.c1

where b.c1>=1 and b.c1<=10

option(recompile)

--large outer table

SELECT a.c1

,a.name

FROM #t1 a

JOIN #t3 b ON a.c1 = b.c1

WHERE a.c1 = 1

AND a.name = 'rico87ABA74A-A202-4D3D-9174-A48ED3543864'

SELECT DISTINCT a.c1

,a.name

FROM #t1 a

JOIN #t3 b ON a.c1 = b.c1

WHERE a.c1 = 1

AND a.name = 'rico87ABA74A-A202-4D3D-9174-A48ED3543864'

SELECT a.c1

,a.name

FROM #t1 a

JOIN #t3 b ON a.c1 = b.c1

WHERE a.c1 = 1

AND a.name = 'rico87ABA74A-A202-4D3D-9174-A48ED3543864'

option(merge join)

/*

RiCo's Note

叢集索引-資料非連續性，過多的索引碎片，影響查詢效能

*/

--過多的索引碎片

drop  table if exists myguidnew

create table myguidnew

(

id uniqueidentifier not null default newid(),

c2 tinyint,

c3 char(4)

)

create clustered index cidx_1 on myguidnew(id)

truncate table myguidnew

alter table myguidnew rebuild

dbcc showcontig('myguidnew') with tableresults

--新增資料

insert into myguidnew(c2,c3)

select 4,'rico'

go 500

dbcc showcontig('myguidnew') with tableresults

--再次新增5000筆資料

insert into myguidnew(c2,c3)

select 4,'rico'

go 5000

--使用 NEWSEQUENTIALID 連續解決碎片問題

create table myguidnewseq

(

id uniqueidentifier not null default NEWSEQUENTIALID(),

c2 tinyint,

c3 char(4)

)

create clustered index cidx_A on myguidnewseq(id)

truncate table myguidnewseq

alter table myguidnewseq rebuild

dbcc showcontig('myguidnewseq') with tableresults

--新增資料

set nocount on

insert into myguidnewseq(c2,c3)

select 4,'rico'

go 500

--顯示資料表索引碎片資訊

dbcc showcontig('myguidnewseq') with tableresults

--再次新增5000筆資料

set nocount on

insert into myguidnewseq(c2,c3)

select 5,'rico'

go 5000

/*

RiCo's Note

統計資料

*/

set nocount on;

UPDATE STATISTICS dbo.SalesOrderDetail;

DBCC SHOW_STATISTICS ('dbo.SalesOrderDetail', IX_SalesOrderDetail_ProductID)

--QO如何使用histogram進行估計資料筆數

select * from dbo.SalesOrderDetail

where ProductID = 709

select * from dbo.SalesOrderDetail

where ProductID = 743

select * from dbo.SalesOrderDetail

where ProductID = 742

--QO如何使用密度進行估計資料筆數

select 1 / CAST(COUNT(DISTINCT ProductID) as numeric(18, 2)) from dbo.SalesOrderDetail

select ProductID from dbo.SalesOrderDetail

group by ProductID

select 1/0.01219512

declare @i int = 707 

select * from dbo.SalesOrderDetail

where ProductID = @i --Local variable

select count(*) * 0.01219512 from  dbo.SalesOrderDetail

declare @i int = 707 

select * from dbo.SalesOrderDetail

where ProductID < @i --Local variable with inequal

select count(*) * 0.3 from  dbo.SalesOrderDetail

/*

RiCo's Note

實戰索引優化

*/

set nocount on;

set statistics io,time on;

--@善用約束

--drop constraint

alter table dbo.address drop constraint CK1_stateprovinceid

select top 1000 * from Address

where stateprovinceid=0

--add constraint

alter table dbo.address with check add constraint CK1_stateprovinceid check(stateprovinceid>0)

--@善用計算資料行

select c4

from dbo.testhashkey

where c2 = N'點部落'  --nvarchar(2000)

and c3='陳泓翔'  --varchar(8000)

create index idx1 on testhashkey(c2,c3)

include(c4)

--透過 checksum (決定性)函示，但 CHECKSUM(*) 除外，建立 hashkey (儲存大小4 bytes)

alter table testhashkey add Hashid as checksum(c2,c3) PERSISTED 

create unique clustered index cuidx on testhashkey (Hashid, c1);

--改寫查詢

select c4

from dbo.testhashkey

where hashid=checksum(N'點部落','陳泓翔')

and c2 = N'點部落'

and c3='陳泓翔'

--@善用涵蓋索引

drop index if exists [idx3] on [dbo].[Address]

CREATE INDEX [idx3] ON [dbo].[Address]

(

[City] ASC

)

INCLUDE ( [ModifiedDate])

select [ModifiedDate],StateProvinceID

from address

where City=N'Bothell'

--logical reads 54

select [ModifiedDate]

from address

where City=N'Bothell'

--logical reads 2

--透過涵蓋索引改善I/O

drop index if exists [idx3] on [dbo].[Address]

CREATE INDEX [idx3] ON [dbo].[Address]

(

[City] ASC

)

INCLUDE ( [ModifiedDate],StateProvinceID)

--@善用篩選索引

--查詢必須符合條件才有效

--適用最常查詢的條件

drop index if exists ix_ModifiedDate on SalesOrderDetail

create index ix_ModifiedDate on SalesOrderDetail(ModifiedDate)

where ModifiedDate>'2008-01-01 00:00:00'

select modifieddate,salesorderid

from SalesOrderDetail

where ModifiedDate>'2008-02-01 00:00:00'

option(recompile)

select modifieddate,salesorderid

from SalesOrderDetail

where ModifiedDate='2008-04-01 00:00:00'

option(recompile)

select modifieddate,salesorderid

from SalesOrderDetail

where ModifiedDate>'2007-04-01 00:00:00'

option(recompile)

--@善用索引檢視

drop view if exists dbo.uv_mytest

select a.AddressID,b.ip

from dbo.Address a join dbo.rsa241 b

on a.AddressID=b.num

--rsa241 logical reads 60079

--Address logical reads 108

--索引檢視

create view uv_mytest

with schemabinding

as

select a.AddressID,b.ip

from dbo.Address a join dbo.rsa241 b

on a.AddressID=b.num

go

--建立唯一叢集索引

create unique clustered index ix_uv_mytest

on uv_mytest(AddressID)

go

/*

RiCo's Note

實戰索引維護

*/

set nocount on;

set statistics io,time off;

drop table if exists CARS

create table CARS

(

Serial int identity(1,1) not null,

Name varchar(20) null,

Rowid uniqueidentifier  default(newid()) not null 

)

-- Insert Data

declare @i int

set @i=1

begin tran

while @i<=100000

begin

insert into CARS

select 'Ferrari'+ cast(458+@i as varchar),NEWID()

set @i=@i+1

end

commit

--建立叢集和非叢集索引

create clustered index cidx_s

on cars(Rowid)

with

(

fillfactor=80

)

create nonclustered index nidx_name

on cars(name)

with

(

fillfactor =80

)

--更新

update CARS

set Rowid=NEWID()

--新增

declare @i int

set @i=1

begin tran

while @i<=10000

begin

insert into CARS

select 'prosche'+ cast(911+@i as varchar),NEWID()

set @i=@i+1

end

commit

--查詢

select Serial,Name

   from CARS

   where Serial=100

--重建所有索引

ALTER INDEX all on dbo.cars

rebuild with(FILLFACTOR =80 , SORT_IN_TEMPDB = ON,

              STATISTICS_NORECOMPUTE = ON)

--重建(大於等於35)索引

ALTER INDEX nidx_name ON dbo.cars

REBUILD

--重新組織所有索引

ALTER INDEX ALL ON dbo.cars

   REORGANIZE;

--重新組織索引(> 5% and <35%)   

ALTER INDEX nidx_name

   ON dbo.cars

   REORGANIZE;

--查詢索引碎片

select  DB_NAME(DATABASE_ID) AS DatabaseName,

OBJECT_NAME(dips.object_id) as TableName,

idxs.name as IndexName,

dips.index_type_desc as IndexType,

dips.avg_fragmentation_in_percent as AvgFragmentation,

dips.page_count 

from sys.dm_db_index_physical_stats (db_id(),null,null,null,N'limited') dips

inner join sysindexes idxs

on dips.index_id  =idxs.indid

and  dips.object_id=idxs.id 

and OBJECT_NAME(dips.object_id)='CARS'

and dips.avg_fragmentation_in_percent >=35.0

--查詢遺漏索引

SELECT statement as [database.scheme.table],

column_id , column_name, column_usage,

migs.avg_user_impact,migs.avg_total_user_cost,

migs.user_seeks, migs.user_scans,

migs.last_user_seek

FROM sys.dm_db_missing_index_details as mid

CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle)

INNER JOIN sys.dm_db_missing_index_groups AS mig

ON mig.index_handle = mid.index_handle

INNER JOIN sys.dm_db_missing_index_group_stats  AS migs

ON mig.index_group_handle=migs.group_handle

ORDER BY mig.index_group_handle, mig.index_handle, column_id

/*

RiCo's Note

名詞定義

*/

--session

select *

from sys.dm_exec_sessions

where session_id=62

--locking

declare @RC INT

begin tran

exec @RC =sp_getapplock @Resource='MyLock', @LockMode='Exclusive'

            , @LockOwner='Transaction', @LockTimeout = 10000

select @@SPID as [session_id], @RC as [return code], GETDATE() as 'begin time'

waitfor delay '00:00:05' 

select GETDATE() as 'end time'     

commit

select *

from sys.dm_tran_locks

where resource_type<>'DATABASE'   

--blocking:注意，SQL Server並不會自動kill被blocking的session

--session A

begin tran

update Address set AddressLine1='test'

where AddressID=1

rollback 

--session B

select *

from Address

where AddressID=1 

--deadlock

--session A

begin tran

update Address set AddressLine1='test'

where AddressID=1

waitfor delay '00:00:03'

select *

from Person

where BusinessEntityID=1

rollback 

--session B

begin tran

update Person set FirstName='test'

where BusinessEntityID=1

waitfor delay '00:00:03'

select *

from Address

where AddressID=1

rollback

/*

RiCo's Note

深入淺出鎖定機制

*/

begin tran

update dbo.DatabaseLog set DatabaseUser='newrico'

where DatabaseLogID=1

rollback

set transaction isolation level read uncommitted

select *

from sys.dm_tran_locks

where 

--resource_type <> 'DATABASE'

--and

resource_database_id = 5

--and

--request_session_id in (68,67)

order by request_session_id

--Lock time exception

create or alter proc usp_GetAddress(@AddressID int)

as

begin

    set nocount on;

set LOCK_TIMEOUT 2000 --2000 ms

begin tran

select AddressLine1

from dbo.Address

where AddressID=@AddressID

    --waitfor delay '00:00:03'

commit

end

select @@lock_timeout

select @@TRANCOUNT

begin tran

update dbo.Address set AddressLine1='newrico'

where AddressID=1

rollback

/*

一句常見Select所要申請的鎖

SQL Server會自動在需要查詢的資料上申請S Lock(無論資料表或索引的資料列)，

大部分情況，SQL Server會讀取完資料後馬上釋放S Lock，無需等待交易結束，

但如果設定repeatable read/serializable 就會持續到交易結束

*/

--session A

set transaction isolation level repeatable read;

begin tran

select * from dbo.bookmark

where c1=1

rollback

--session B

begin tran

delete dbo.bookmark

where c1=1

rollback

/*

一句常見Insert所要申請的鎖

SQL Server會對新增資料申請一個X Lock，也會再發生變化的page和table個申請一個IX Lock，

因為是新資料，所以會被參考的機會較小，相對發生Blocking也小很多

*/

-- session A

begin tran

insert into  dbo.bookmark  values(11,'test11',GETDATE(),1)

rollback

-- session B

select * from dbo.bookmark

where c1=1

/*

一句常見Update所要申請的鎖

SQL Server處理Update，必須先查詢到該筆資料後，才能針對該筆資料進行修改，

查詢過程需要申請S Lock，找到資料後再申請U Lock，當要進行資料修改時，才會把U Lock升級為 X Lock。

note:如果update有透過那些索引，就會在這些索引鍵值上申請U Lock，沒用到的索引不申請鎖。

*/

-- session A

begin tran

update dbo.bookmark set c2='newtest'

where c1=1

rollback

-- session B

select * from dbo.bookmark

where c1=1

--鎖定擴大

dbcc tracestatus(1224)

ALTER TABLE TransactionHistoryTest SET (LOCK_ESCALATION = table); --default

ALTER TABLE TransactionHistoryTest SET (LOCK_ESCALATION = disable);

ALTER TABLE TransactionHistoryTest SET (LOCK_ESCALATION = auto);

--session 1

set transaction isolation level repeatable read

begin tran

select *

from TransactionHistoryTest

where TransactionID <1800 -- 1800 43800 55800

select resource_type,request_mode, count(*)

from sys.dm_tran_locks

where resource_database_id = 5

and request_session_id=@@SPID

group by resource_type,request_mode

commit

--session 2

begin tran

update  TransactionHistoryTest

set ActualCost+=10

where TransactionID=999963

rollback

/*

RiCo's Note

閱讀死結報表和減少死結

*/

set nocount on

set transaction isolation level read uncommitted

select *

from sys.dm_tran_locks

where 

--resource_type <> 'DATABASE'

--and

resource_database_id = 5

--and

--request_session_id in (68,67)

order by request_session_id

set transaction isolation level read uncommitted

--Returning the deadlock graph from the system_health event session

SELECT XEvent.query('(event/data/value/deadlock)[1]') AS DeadlockGraph

, XEvent.value('(/event/@timestamp)[1]','DATETIMEOFFSET') AS BeginTime

FROM ( SELECT XEvent.query('.') AS XEvent

       FROM ( SELECT CAST(target_data AS XML) AS TargetData

              FROM sys.dm_xe_session_targets st

                   JOIN sys.dm_xe_sessions s

                   ON s.address = st.event_session_address

              WHERE s.name = 'system_health'

                    AND st.target_name = 'ring_buffer'

              ) AS Data

              CROSS APPLY

                 TargetData.nodes

                    ('RingBufferTarget/event[@name="xml_deadlock_report"]')

              AS XEventData ( XEvent )

      ) AS src;

--同時讀寫叢集和非叢集索引的死鎖

drop index ix_c3 on deadlockc

create index ix_c3 on deadlockc(c3)

--include(c2)

--session A

while(1=1)

select * from deadlockc with(index(ix_c3)) where c3=1

--session B

while(1=1)

update deadlockc set c1+=1

where c3=1

/*

解決方法:

1.設計正確索引，避免查詢使用lookup

2.盡量避免更新叢集索引

*/

--Full Tabe Scan的死鎖

--session A

begin tran

update deadlockA set c2 = 'newrico' where c1 = 1

waitfor delay '00:00:05'

select * from deadlockB (FORCESCAN)  where c1 = 3

rollback --(FORCESCAN)

--session B

begin tran

update deadlockB set c2 = 'newpcdou' where c1 = 4

waitfor delay '00:00:05'

select * from deadlockA (FORCESCAN) where  c1 = 2

rollback

/*

解決方法:

設計正確索引，避免full table scan

*/

--不同順序存取資源的死鎖

--session A

begin tran

update deadlockA set c2 = 'newrico' where c1 = 1

waitfor delay '00:00:05'

select * from deadlockA  where c1 = 2

rollback

--session B

begin tran

update deadlockA set c2 = 'newpcdou' where c1 = 2

waitfor delay '00:00:05'

select * from deadlockA where  c1 = 1

rollback

/*

解決方法:

1.select 如非必要無須參與交易

2.按照相同順序存取資源

3.使用RCSI或Read Uncommitted(小心副作用)

*/

/*

RiCo's Note

等待簡介

*/

set nocount on;

set transaction isolation level read uncommitted;

--查詢等待資訊

;WITH Waits AS

(

SELECT 

   wait_type, 

   wait_time_ms / 1000. AS wait_time_s,

   100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,

   ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn

FROM sys.dm_os_wait_stats

WHERE wait_time_ms >0 and wait_type 

   NOT IN

    (

N'CLR_SEMAPHORE', N'LAZYWRITER_SLEEP',

N'RESOURCE_QUEUE', N'SQLTRACE_BUFFER_FLUSH',

N'SLEEP_TASK', N'SLEEP_SYSTEMTASK',

N'WAITFOR', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',

N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',

N'XE_TIMER_EVENT', N'XE_DISPATCHER_JOIN',

N'LOGMGR_QUEUE', N'FT_IFTS_SCHEDULER_IDLE_WAIT',

N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',

N'CLR_AUTO_EVENT', N'DISPATCHER_QUEUE_SEMAPHORE',

N'TRACEWRITE', N'XE_DISPATCHER_WAIT',

N'BROKER_TO_FLUSH', N'BROKER_EVENTHANDLER',

N'FT_IFTSHC_MUTEX', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',

N'DIRTY_PAGE_POLL', N'SP_SERVER_DIAGNOSTICS_SLEEP') -- filter out additional irrelevant waits

)   

SELECT W1.wait_type,

CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,

CAST(W1.pct AS DECIMAL(12, 2)) AS pct,

CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct

FROM Waits AS W1

INNER JOIN Waits AS W2 ON W2.rn <= W1.rn

GROUP BY W1.rn, 

W1.wait_type, 

W1.wait_time_s, 

W1.pct

HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold;

--清空等待資料,無須重新啟動sql server,也不會影響sql Instance,因為該資料用於外部

DBCC SQLPERF('sys.dm_os_wait_stats',CLEAR);

--可透過Agent Job 每小時收集等待資訊

drop table if exists dbo.WaitStat

CREATE TABLE dbo.WaitStat

(

[WaitType] [nvarchar](60) NOT NULL,

[WaitingTasksCount] [bigint] NOT NULL,

[WaitTimeMs] [bigint] NOT NULL,

[MaxWaitTimeMs] [bigint] NOT NULL,

[SignalWaitTimeMs] [bigint] NOT NULL,

[CaptureTime] [datetime] NOT NULL default(getdate()),

[IncrementId] [int] NOT NULL

);

--Insert wait stats info in a datestamped format for later querying:

DECLARE @currentTime DATETIME ;

SET @currentTime = GETDATE() ;

DECLARE @incrementid INT;

SELECT @incrementid = MAX(IncrementId) + 1 FROM dbo.WaitStat

SELECT @incrementid = ISNULL(@incrementid, 1)

INSERT INTO dbo.WaitStat

([WaitType], [WaitingTasksCount], [WaitTimeMs], [MaxWaitTimeMs],

[SignalWaitTimeMs], [CaptureTime], [IncrementId])

SELECT [wait_type], [waiting_tasks_count], [wait_time_ms], [max_wait_time_ms], 

[signal_wait_time_ms], @currentTime, @incrementid

FROM sys.dm_os_wait_stats;

--query

DECLARE @max_increment_id INT

SELECT @max_increment_id = MAX([IncrementId])

FROM dbo.WaitStat

SELECT top 8 DOWS1.[WaitType], 

(DOWS1.[WaitingTasksCount] - DOWS2.[WaitingTasksCount]) AS [waiting_tasks_count],

(DOWS1.[WaitTimeMs] - DOWS2.[WaitTimeMs]) AS [wait_time_ms],

DOWS1.[MaxWaitTimeMs], 

(DOWS1.[SignalWaitTimeMs] - DOWS2.[SignalWaitTimeMs]) AS [signal_wait_time_ms],

DATEDIFF(ms, DOWS2.[CaptureTime], DOWS1.[CaptureTime]) AS [elapsed_time_ms],

DOWS1.[CaptureTime] AS [last_time_stamp], DOWS2.[CaptureTime] AS [previous_time_stamp]

FROM 

(

SELECT  [WaitType], [WaitingTasksCount], [WaitTimeMs], [MaxWaitTimeMs],

         [SignalWaitTimeMs], [CaptureTime], [IncrementId]

FROM dbo.WaitStat

WHERE [IncrementId] = @max_increment_id

)AS DOWS1 

JOIN 

(

SELECT  [WaitType], [WaitingTasksCount], [WaitTimeMs], [MaxWaitTimeMs],

         [SignalWaitTimeMs], [CaptureTime], [IncrementId]

FROM dbo.WaitStat

WHERE [IncrementId] = (@max_increment_id - 1)

)AS DOWS2 ON DOWS1.[WaitType] = DOWS2.[WaitType]

WHERE (DOWS1.[WaitTimeMs] - DOWS2.[WaitTimeMs]) > 0 

--This can technically be eliminated because we're not persisting these waits:

AND DOWS1.WaitType NOT IN 

   (N'CLR_SEMAPHORE', N'LAZYWRITER_SLEEP',

N'RESOURCE_QUEUE', N'SQLTRACE_BUFFER_FLUSH',

N'SLEEP_TASK', N'SLEEP_SYSTEMTASK',

N'WAITFOR', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',

N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',

N'XE_TIMER_EVENT', N'XE_DISPATCHER_JOIN',

N'LOGMGR_QUEUE', N'FT_IFTS_SCHEDULER_IDLE_WAIT',

N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',

N'CLR_AUTO_EVENT', N'DISPATCHER_QUEUE_SEMAPHORE',

N'TRACEWRITE', N'XE_DISPATCHER_WAIT',

N'BROKER_TO_FLUSH', N'BROKER_EVENTHANDLER',

N'FT_IFTSHC_MUTEX', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',

N'DIRTY_PAGE_POLL', N'SP_SERVER_DIAGNOSTICS_SLEEP')

ORDER BY (DOWS1.[WaitTimeMs] - DOWS2.[WaitTimeMs]) DESC;

;WITH Waits AS

(

SELECT DOWS1.[WaitType], 

   ((DOWS1.[WaitTimeMs] - DOWS2.[WaitTimeMs])/1000) AS [wait_time_s],

   100. * (DOWS1.[WaitTimeMs] - DOWS2.[WaitTimeMs]) / SUM(DOWS1.[WaitTimeMs] - DOWS2.[WaitTimeMs]) OVER() AS pct,

     ROW_NUMBER() OVER(ORDER BY (DOWS1.[WaitTimeMs] - DOWS2.[WaitTimeMs]) DESC) AS rn

FROM 

   (

  SELECT  [WaitType], [WaitingTasksCount], [WaitTimeMs], [MaxWaitTimeMs],

         [SignalWaitTimeMs], [CaptureTime], [IncrementId]

   FROM dbo.WaitStat

   WHERE [IncrementId] = @max_increment_id

   )AS DOWS1 

   INNER JOIN 

   (

SELECT  [WaitType], [WaitingTasksCount], [WaitTimeMs], [MaxWaitTimeMs],

         [SignalWaitTimeMs], [CaptureTime], [IncrementId]

   FROM dbo.WaitStat

   WHERE [IncrementId] = (@max_increment_id - 1)

   )AS DOWS2 ON DOWS1.[WaitType] = DOWS2.[WaitType]

WHERE (DOWS1.[WaitTimeMs] - DOWS2.[WaitTimeMs]) > 0

AND DOWS1.[WaitType] NOT IN 

   (N'CLR_SEMAPHORE', N'LAZYWRITER_SLEEP',

N'RESOURCE_QUEUE', N'SQLTRACE_BUFFER_FLUSH',

N'SLEEP_TASK', N'SLEEP_SYSTEMTASK',

N'WAITFOR', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',

N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',

N'XE_TIMER_EVENT', N'XE_DISPATCHER_JOIN',

N'LOGMGR_QUEUE', N'FT_IFTS_SCHEDULER_IDLE_WAIT',

N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',

N'CLR_AUTO_EVENT', N'DISPATCHER_QUEUE_SEMAPHORE',

N'TRACEWRITE', N'XE_DISPATCHER_WAIT',

N'BROKER_TO_FLUSH', N'BROKER_EVENTHANDLER',

N'FT_IFTSHC_MUTEX', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',

N'DIRTY_PAGE_POLL', N'SP_SERVER_DIAGNOSTICS_SLEEP')

)

SELECT W1.[WaitType],

CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,

CAST(W1.pct AS DECIMAL(12, 2)) AS pct,

CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct

FROM Waits AS W1

INNER JOIN Waits AS W2 ON W2.rn <= W1.rn

GROUP BY W1.rn, 

W1.[WaitType], 

W1.wait_time_s, 

W1.pct

HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold;

/*

RiCo's Note

鎖定等待

*/

--download : RML Utilities for SQL Server (x64) CU4

--https://www.microsoft.com/en-us/download/details.aspx?id=4511

/*

--模擬多人同時進行更新操作

cd /d "C:\Program Files\Microsoft Corporation\RMLUtils"

ostress.exe -E -dAdventureWorks2012 -i"C:\LabSqlFiles\LCK_M_U.sql" -n100 -r3 –oC:\myoutput -q

ostress.exe -E -dAdventureWorks2012 -i"C:\LabSqlFiles\LCK_M_U_Batch.sql" -n100 -r3 –oC:\myoutput -q

*/

set nocount on;

select * from sys.dm_os_waiting_tasks

where resource_description like N'%dbid=11%'

select AddressLine1,City from dbo.Address

where StateProvinceID=9

--rollback data

update  a set a.AddressLine1=b.AddressLine1,a.City=b.City

from dbo.Address a join Person.Address b on a.AddressID=b.AddressID

where b.StateProvinceID=9

drop index if exists ix_StateProvinceID on dbo.Address

create index ix_StateProvinceID on dbo.Address(StateProvinceID)

include(AddressLine1,City)

/*

RiCo's Note

平行等待

*/

SET NOCOUNT ON

SET STATISTICS io,time ON

USE AdventureWorks2012 ; 

GO   

EXEC sp_configure 'show advanced options', 1; 

GO 

RECONFIGURE WITH OVERRIDE; 

GO 

EXEC sp_configure 'max degree of parallelism', 8;   

EXEC sp_configure 'cost threshold for parallelism', 25 ; 

GO 

RECONFIGURE WITH OVERRIDE; 

GO 

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 8;

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = 4 ;

--get information of cpu

SELECT 'PhysicalCPUs'=(cpu_count / hyperthread_ratio),   

'logicalCPUs'=cpu_count   

FROM sys.dm_os_sys_info 

SELECT * FROM sys.dm_os_wait_stats

WHERE wait_type = 'CXPACKET'

DBCC sqlperf('sys.dm_os_wait_stats', clear)

CREATE TABLE #myid (id INT)

INSERT INTO #myid VALUES(0),(1),(2),(3)

DECLARE @maxC3 int

SELECT @maxC3 = max(a.c3)

FROM dbo.cxpacketTest a

where a.c1 in (0,1,2,3)

OPTION (maxdop 0)

DECLARE @maxC3 int

SELECT @maxC3 = max(a.c3)

FROM dbo.cxpacketTest a

where a.c1 in (select id from #myid)

OPTION (maxdop 0)

DECLARE @maxC3 int

SELECT @maxC3 = max(a.c3)

FROM dbo.cxpacketTest a JOIN #myid b ON (a.c1 = b.id)

OPTION (maxdop 0)

drop table #myid

--Find Queries using Parallelism from Cached Plan

SELECT TOP 10

p.*,

q.*,

qs.*,

cp.plan_handle

FROM

sys.dm_exec_cached_plans cp

CROSS apply sys.dm_exec_query_plan(cp.plan_handle) p

CROSS apply sys.dm_exec_sql_text(cp.plan_handle) AS q

JOIN sys.dm_exec_query_stats qs

ON qs.plan_handle = cp.plan_handle

WHERE

cp.cacheobjtype = 'Compiled Plan' AND

p.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";

max(//p:RelOp/@Parallel)', 'float') > 0

OPTION (MAXDOP 1)

/*

RiCo's Note

I/O等待

*/

SET NOCOUNT ON

SET STATISTICS io,time ON

SELECT * FROM sys.dm_os_wait_stats

WHERE  wait_type like 'PAGEIOLATCH_%'

-- or  wait_type in ('ASYNC_IO_COMPLETION','IO_COMPLETION')

DBCC sqlperf('sys.dm_os_wait_stats', clear)

drop index if exists ix_EMP_DEP on dbo.DOORDATA

create index ix_EMP_DEP on dbo.DOORDATA(EMP_DEP)

--include(EMP_ID,SDATE)

--write buffer into disk

CHECKPOINT;

GO

--clear buffer cache

DBCC DROPCLEANBUFFERS;

GO

select EMP_ID,SDATE,EMP_DEP

from dbo.DOORDATA

where EMP_DEP in ('2210','2225','2519')

order by EMP_DEP

/*

RiCo's Note

暫存資料表的進化

*/

set nocount on;

create or alter proc usp_testRecomile

as

begin

create table #abc(c1 int);

insert into #abc select 1;

exec usp_testInner --要了解相同session 範圍皆可存取temp table，並非SP範圍

end

create or alter proc usp_testInner

as

begin

delete top (1) from  #abc;

drop table #abc;

end

declare @j int=1;

while(@j<=100)

begin

exec usp_testRecomile

set @j+=1;

end