搜尋本站文章

2016-03-11

認識 資料類型:rowversion,在資料庫內自動產生唯一的二進位數字(4)


結論

在既有的資料表上,新增加資料行,使用資料類型:rowversion,這會當下耗用磁碟I/O資源。

解決方向:

(1) 請安排於 系統離峰時間 建置。
(2) 搭配 高速儲存設備(例如:Flash)來縮短所需的建置時間!




-- P01_查詢目前資料庫內,每一個資料表的使用空間資訊



-- P02_新增 資料行 rowversion,觀察耗費的時間



-- P03_觀察 資料表



-- P04_已經新增資料行rowversion_SSMS







範例程式碼

-- 01_查詢目前資料庫內,每一個資料表的使用空間資訊
USE TSIO
GO
SELECT a3.name AS N'結構描述', a2.name AS N'資料表', a1.rows AS N'資料列筆數', (a1.reserved + ISNULL(a4.reserved,0))* 8.0/1024 AS N'配置的空間總量(MB)', a1.data * 8.0/1024 AS '資料(MB)', 
(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8.0/1024 AS N'索引(MB)', 
(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8.0/1024 AS N'尚未使用(MB)',
a2.create_date N'建立日期', a2.modify_date N'修改日期'
FROM (SELECT ps.object_id, SUM ( CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END ) AS [rows], 
SUM (ps.reserved_page_count) AS reserved, SUM ( CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count) 
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END ) AS data, 
SUM (ps.used_page_count) AS used 
FROM sys.dm_db_partition_stats ps 
GROUP BY ps.object_id) AS a1 LEFT OUTER JOIN
(SELECT it.parent_id, SUM(ps.reserved_page_count) AS reserved, SUM(ps.used_page_count) AS used 
FROM sys.dm_db_partition_stats ps INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id) 
WHERE it.internal_type IN (202,204) 
GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id) 
INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id ) 
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id) 
WHERE a2.type <> N'S' and a2.type <> N'IT'
ORDER BY a1.data DESC
GO

-- 02_新增 資料行 rowversion,觀察耗費的時間
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SET STATISTICS TIME ON
GO

ALTER TABLE TS_PK_2GB
 ADD vercol rowversion;
GO

-- 03_觀察 資料表
SELECT TOP 100 * FROM TS_PK_2GB
GO




參考資料

認識 資料類型:rowversion,在資料庫內自動產生唯一的二進位數字(1)
http://sharedderrick.blogspot.tw/2016/03/rowversion1.html

免費授權(Migrate from Oracle with free licenses):將 Oracle 移轉到 SQL Server


Microsoft CEO Satya Nadella 在 New York 與客戶分享「Data Driven

其中一項:免費授權(Migrate from Oracle with free licenses):將 Oracle 移轉到 SQL Server

注意事項
Software Assurance subscription required. Some restrictions may apply.
Limited time offer—free training and subsidized deployment services available until June 30 2016.

1. 需要簽署「軟體保證(Software Assurance)」,與部分限制。
2. 在2016/06/30之前,提供免費教育訓練並且補助部署服務。



就來這裡登記,獲得免費授權來移轉到 SQL Server!

I’m ready to break free from Oracle and claim my SQL Server offer
https://info.microsoft.com/Migrate_your_oracle_apps_to_SQL_Server-Contact_Me.html



參考資料

Follow the leader and migrate from Oracle to SQL Server—with free licenses*
https://www.microsoft.com/en-us/server-cloud/sql-license-migration.aspx

I’m ready to break free from Oracle and claim my SQL Server offer
https://info.microsoft.com/Migrate_your_oracle_apps_to_SQL_Server-Contact_Me.html

Put The Business Back In Your Data Management Business Case
https://info.microsoft.com/Put_the_business_back_in_your_business_case-Register.html?ls=Website

Microsoft Courting Oracle Users with SQL Server 2016
https://redmondmag.com/Articles/2016/03/10/SQL-Server-2016-Oracle.aspx?Page=1

2016-03-09

認識 資料類型:rowversion,在資料庫內自動產生唯一的二進位數字(3)

結論

資料類型:rowversion,不適合做為索引鍵 (尤其是主索引鍵) 的候選項。

可以在資料類型:rowversion上建立索引,但在異動時會產生更大磁碟 IO 負載。

說明

每次修改或插入含 rowversion 資料行的資料列時,都會在 rowversion 資料行中插入累加的資料庫資料列版本值。
這個屬性會使 rowversion 資料行不適合做為索引鍵 (尤其是主索引鍵) 的候選項。

資料列的任何更新都會變更資料列版本值,因而會變更索引鍵值。
如果資料行在主索引鍵中,舊的索引鍵值便不再有效,參考舊值的外部索引鍵也不再有效。

如果動態資料指標參考資料表,所有更新都會變更資料列在資料指標中的位置。
如果資料行在索引鍵中,資料列的所有更新也會產生索引的更新。



-- P01_檢視資料表的索引類型


-- P02_快速_檢視指定資料庫內的每一個索引之片段(fragmentation)資訊,使用 SAMPLED 模式




大量的I/D/U後,觀察:

-- P03_快速_檢視指定資料庫內的每一個索引之片段(fragmentation)資訊,使用 SAMPLED 模式



-- P04_觀察:rowversion資料行,有建立索引的使用特性 -- Index Seek



-- P05_觀察:rowversion資料行,沒有建立索引的使用特性 - Clustered index Scan




範例程式碼


-- 01_建立範例資料表:rowversion_Index1 與 rowversion_NoIndex1
USE DBRV01
GO
CREATE TABLE rowversion_Index1
(sid int IDENTITY PRIMARY KEY, pid varchar(50), rowrev rowversion)
GO

CREATE TABLE rowversion_NoIndex1
(sid int IDENTITY PRIMARY KEY, pid varchar(50), rowrev rowversion)
GO

-- 02_在範例資料表:rowversion_Index1 的 rowversion資料行上,建立索引
CREATE NONCLUSTERED INDEX [NCL01_rowversion] ON [dbo].[rowversion_Index1]
(
 [rowrev] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

GO

--03 檢視資料表的索引類型
SELECT t.name N'資料表', i.name N'索引名稱', 
 i.type_desc N'索引類型的描述',
 i.is_unique N'索引是否唯一'
FROM sys.tables t INNER JOIN sys.indexes i
ON t.object_id = i.object_id
GO

-- 04_快速_檢視指定資料庫內的每一個索引之片段(fragmentation)資訊,使用 SAMPLED 模式
SELECT sch.name N'結構描述', obj.name N'資料表',
 inx.name AS N'索引名稱', 
 index_type_desc N'索引類型',
 avg_fragmentation_in_percent '片段(%)',
 avg_page_space_used_in_percent N'頁面飽和度(%)',
 fragment_count,
 avg_fragment_size_in_pages,
 page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL,NULL, NULL, 'SAMPLED') AS phy
 INNER JOIN sys.indexes AS inx ON phy.object_id = inx.object_id AND phy.index_id = inx.index_id
 INNER JOIN sys.objects AS obj ON phy.object_id = obj.object_id
 INNER JOIN sys.schemas AS sch ON obj.schema_id = sch.schema_id
WHERE index_type_desc <> 'HEAP'  AND fragment_count IS NOT NULL AND avg_fragment_size_in_pages IS NOT NULL
ORDER BY 2
GO

-- 05_Insert/Update/Delete 大量資料列,四萬筆
SET NOCOUNT ON

DECLARE @cnt INT =1

WHILE @cnt < 10001
BEGIN
 INSERT rowversion_Index1(pid) VALUES('A_'+CAST(SYSDATETIME() AS varchar(50)))
 INSERT rowversion_Index1(pid) VALUES('B_'+CAST(SYSDATETIME() AS varchar(50)))
 INSERT rowversion_Index1(pid) VALUES('C_'+CAST(SYSDATETIME() AS varchar(50)))
 INSERT rowversion_Index1(pid) VALUES('D_'+CAST(SYSDATETIME() AS varchar(50)))

 INSERT rowversion_NoIndex1(pid) VALUES('A_'+CAST(SYSDATETIME() AS varchar(50)))
 INSERT rowversion_NoIndex1(pid) VALUES('B_'+CAST(SYSDATETIME() AS varchar(50)))
 INSERT rowversion_NoIndex1(pid) VALUES('C_'+CAST(SYSDATETIME() AS varchar(50)))
 INSERT rowversion_NoIndex1(pid) VALUES('D_'+CAST(SYSDATETIME() AS varchar(50)))
 
 SET @cnt +=1
END
GO

-- DELETE:1萬筆
DELETE FROM rowversion_Index1
WHERE pid LIKE 'B_%'
GO
DELETE FROM rowversion_NoIndex1
WHERE pid LIKE 'B_%'
GO

-- UPDATE:1萬筆
UPDATE rowversion_Index1
SET pid = ('E_'+CAST(SYSDATETIME() AS varchar(50)))
WHERE pid LIKE 'C_%'
GO
UPDATE rowversion_NoIndex1
SET pid = ('E_'+CAST(SYSDATETIME() AS varchar(50)))
WHERE pid LIKE 'C_%'
GO

-- 05_快速_檢視指定資料庫內的每一個索引之片段(fragmentation)資訊,使用 SAMPLED 模式
SELECT sch.name N'結構描述', obj.name N'資料表',
 inx.name AS N'索引名稱', 
 index_type_desc N'索引類型',
 avg_fragmentation_in_percent '片段(%)',
 avg_page_space_used_in_percent N'頁面飽和度(%)',
 fragment_count,
 avg_fragment_size_in_pages,
 page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL,NULL, NULL, 'SAMPLED') AS phy
 INNER JOIN sys.indexes AS inx ON phy.object_id = inx.object_id AND phy.index_id = inx.index_id
 INNER JOIN sys.objects AS obj ON phy.object_id = obj.object_id
 INNER JOIN sys.schemas AS sch ON obj.schema_id = sch.schema_id
WHERE index_type_desc <> 'HEAP'  AND fragment_count IS NOT NULL AND avg_fragment_size_in_pages IS NOT NULL
ORDER BY 2
GO

-- 06_觀察:rowversion資料行,有建立索引的使用特性 -- Index Seek
SELECT * FROM rowversion_Index1
WHERE rowrev = 0x000000000003AD11
GO

-- 07_觀察:rowversion資料行,沒有建立索引的使用特性 - Clustered index Scan
SELECT * FROM rowversion_NoIndex1
WHERE rowrev = 0x0000000000024C20
GO




參考資料

認識 資料類型:rowversion,在資料庫內自動產生唯一的二進位數字(1)
http://sharedderrick.blogspot.tw/2016/03/rowversion1.html

2016-03-07

認識 資料類型:rowversion,在資料庫內自動產生唯一的二進位數字(2)

在Microsoft官方文件說明:

timestamp 語法已被rowversion 取代。

未來的 Microsoft SQL Server 版本將移除這項功能。
請避免在新的開發工作中使用這項功能,並規劃修改目前使用這項功能的應用程式。

timestamp 是 rowversion 資料類型的同義字,遵照資料類型同義字的行為。
在 DDL 陳述式中,請盡可能利用 rowversion 來取代 timestamp。

Transact-SQL timestamp 資料類型不同於 ISO 標準中所定義的 timestamp 資料類型。


截至目前為止,管理工具 SSMS - SQL Server 2014 版本無法識別 rowversion,仍自動轉為:timestamp。

-- 00_timestamp 語法已被取代


-- 01_rowversion 資料類型值,但使用觀察卻是timestamp_SSMS


-- 02_找不到rowversion資料類型_SSMS


-- 03_有timestamp資料類型_SSMS


-- 04_錯誤_直接輸入rowversion資料類型_SSMS







參考資料
rowversion (Transact-SQL)
https://msdn.microsoft.com/zh-tw/library/ms182776(v=sql.120).aspx

資料類型同義字 (Transact-SQL)
https://msdn.microsoft.com/zh-tw/library/ms177566(v=sql.120).aspx

2016-03-04

認識 資料類型:rowversion,在資料庫內自動產生唯一的二進位數字(1)

應用場合,例如:

轉檔作業:利用資料類型:rowversion,找出此資料列是否有被更新過,以及是否為新增的資料列。
用於實作「開放式並行存取控制(optimistic concurrency control)」。
由前台程式可用來偵測更新衝突(Update Collisions)。


您可以利用資料列的 rowversion 資料行來輕易判斷上次讀取資料列之後,資料列中的任何值是否有任何改變。
如果資料列有了任何改變,就會更新資料列版本值。

如果資料列沒有任何改變,資料列版本值就與先前讀取時相同。
若要傳回資料庫目前的資料列版本值,請使用 @@DBTS。

您可以將 rowversion 資料行加入至資料表,以確保能在多個使用者同時更新資料列時維護資料庫的完整性。
您可能也想在不必重新查詢資料表的情況下,知道更新了多少資料列以及更新了哪些資料列。



認識 資料類型:rowversion的基礎功能

-- P01_查詢-目前資料庫的目前 rowversion 資料類型值



-- P02_資料行rowversion,自動產生的唯一的二進位數字



-- P03_加入對資料類型 rowversion 的排序



-- P04_資料行 pid 值為 C,,其 rowrev 值是 0x00000000000007D3



-- P05_rowversion值,自動更新



-- P06_WHERE 查詢方式,使用 大於



-- P07_WHERE 查詢方式,使用 等於



-- P08_pid值有重複,觀察資料類型 rowversion 值的特性



-- P09_更動多筆資料列,資料類型 rowversion 的值,已經自動變更



-- P10_不得直接對 rowversion 資料行新增資料



-- P11_不得直接對 rowversion 資料行執行UPDATE





認識 資料類型:rowversion

不可為 Null 的rowversion 資料行,語意等於 binary(8) 資料行。
可為 Null 的 rowversion 資料行,語意等於 varbinary(8) 資料行。

此資料類型會公開在資料庫中自動產生的唯一二進位數字。
rowversion 通常用來做為版本戳記資料表資料列的機制。

儲存體大小是 8 位元組。
rowversion 資料類型只是會遞增的數字,因此不會保留日期或時間。
若要記錄日期或時間,請使用 datetime2 資料類型。

每個資料庫都有一個計數器,會針對在資料庫內包含 rowversion 資料行的資料表所執行的每個插入或更新作業而累加。
這個計數器是資料庫資料列版本。

這會追蹤資料庫內的相對時間,而不是可關聯於時鐘的實際時間。
資料表只能有一個 rowversion 資料行。

每次修改或插入含 rowversion 資料行的資料列時,都會在 rowversion 資料行中插入累加的資料庫資料列版本值。
這個屬性會使 rowversion 資料行不適合做為索引鍵 (尤其是主索引鍵) 的候選項。

資料列的任何更新都會變更資料列版本值,因而會變更索引鍵值。
如果資料行在主索引鍵中,舊的索引鍵值便不再有效,參考舊值的外部索引鍵也不再有效。

如果動態資料指標參考資料表,所有更新都會變更資料列在資料指標中的位置。
如果資料行在索引鍵中,資料列的所有更新也會產生索引的更新。

timestamp 是 rowversion 資料類型的同義字,遵照資料類型同義字的行為。
在 DDL 陳述式中,請盡可能利用 rowversion 來取代 timestamp。

Transact-SQL timestamp 資料類型不同於 ISO 標準中所定義的 timestamp 資料類型。

timestamp 語法已被取代。
未來的 Microsoft SQL Server 版本將移除這項功能。
請避免在新的開發工作中使用這項功能,並規劃修改目前使用這項功能的應用程式。



範例程式碼



-- 01_建立新資料庫
USE master
GO
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'DBRV01')
 DROP DATABASE [DBRV01]
GO
CREATE DATABASE DBRV01
GO

-- 02_查詢-目前資料庫的目前 rowversion 資料類型值
USE DBRV01
GO
SELECT @@DBTS N'傳回目前資料庫的目前 rowversion 資料類型值'
GO

-- P01_查詢-目前資料庫的目前 rowversion 資料類型值

-- 03_建立範例資料表,使用資料類型:rowversion
CREATE TABLE rowversion01
(sid int IDENTITY, pid varchar(10), rowrev rowversion)
GO

-- 04_新增資料列,請明確要新增的資料行,
INSERT rowversion01(pid) VALUES('A')
INSERT rowversion01(pid) VALUES('B')
INSERT rowversion01(pid) VALUES('C')
INSERT rowversion01(pid) VALUES('D')
GO

-- 05_查詢資料
SELECT * FROM rowversion01
GO

-- P02_資料行rowversion,自動產生的唯一的二進位數字

-- 06_加入對資料類型 rowversion 的排序
SELECT * FROM rowversion01
ORDER BY rowrev DESC -- 可以排序
GO

/*
觀察
資料行 pid 值為 C,,其 rowrev 值是 0x00000000000007D3
*/

-- P03_加入對資料類型 rowversion 的排序

-- 07_修改pid資料行,由 C 改為 S。
UPDATE rowversion01
SET pid = 'S'
WHERE pid = 'C'
GO

-- P04_資料行 pid 值為 C,,其 rowrev 值是 0x00000000000007D3

-- 08_查詢資料表
SELECT * FROM dbo.rowversion01
ORDER BY rowrev DESC
GO

/*
觀察
資料行 pid 值為 C,,資料類型 rowversion 的值,已經自動變更為 0x00000000000007D5
*/

-- P05_rowversion值,自動更新

-- 09_加入篩選條件式:WHERE 查詢方式,使用 大於(>)
SELECT * FROM dbo.rowversion01
WHERE rowrev > 0x00000000000007D2
GO

-- P06_WHERE 查詢方式,使用 大於

-- 10_加入篩選條件式:WHERE 查詢方式,使用 等於(=)
SELECT * FROM dbo.rowversion01
WHERE rowrev = 0x00000000000007D2
GO

-- P07_WHERE 查詢方式,使用 等於

-- 11_新增資料列,pid值有重複,測試若 UPDATE更新多筆資料列時,資料類型 rowversion 值的特性
INSERT rowversion01(pid) VALUES('B')
INSERT rowversion01(pid) VALUES('B')
INSERT rowversion01(pid) VALUES('B')
GO

-- 12_查詢資料表
SELECT * FROM dbo.rowversion01
GO
/*
觀察
資料行 pid 值為 C,,資料類型 rowversion 是:
0x00000000000007D2、0x00000000000007D6、0x00000000000007D7 與 0x00000000000007D8
*/

-- P08_pid值有重複,觀察資料類型 rowversion 值的特性

-- 13__修改pid資料行,由 B 改為 T,預估有四筆資料列會受到影響
UPDATE rowversion01
SET pid = 'T'
WHERE pid = 'B'
GO

-- 14_查詢資料表
SELECT * FROM dbo.rowversion01
GO
/*
觀察
資料行 pid 值為 C,,資料類型 rowversion 的值,已經自動變更為:
0x00000000000007D9、0x00000000000007DA、0x00000000000007DB 與 0x00000000000007DC

-- 原本
資料行 pid 值為 C,,資料類型 rowversion 的值:
0x00000000000007D2、0x00000000000007D6、0x00000000000007D7 與 0x00000000000007D8
*/

-- P09_更動多筆資料列,資料類型 rowversion 的值,已經自動變更

-- 15_不得直接對 rowversion 資料行做任何異動,例如:不得 INSERT、UPDATE。

INSERT dbo.rowversion01(pid, rowrev) VALUES('E', 0x00000000000007DE) 
GO

/*
錯誤
訊息 273,層級 16,狀態 1,行 118
無法將明確值插入時間戳記資料行。請使用 INSERT 配合資料行清單排除時間戳記資料行,或者將 DEFAULT 插入時間戳記資料行。
*/

-- P10_不得直接對 rowversion 資料行新增資料

-- 16_不得直接對 rowversion 資料行做任何異動,例如:不得 INSERT、UPDATE。

UPDATE rowversion01
SET rowrev = 0x00000000000007E2
WHERE pid = 'D'
GO

/*
錯誤
訊息 272,層級 16,狀態 1,行 129
無法更新時間戳記資料行。
*/

-- P11_不得直接對 rowversion 資料行執行UPDATE





參考資料

rowversion (Transact-SQL)
https://msdn.microsoft.com/zh-tw/library/ms182776(v=sql.120).aspx

HOW TO: 使用時間戳記資料行中使用視覺化 C#.NET 的 ADO.NET 偵測更新衝突
https://support.microsoft.com/zh-tw/kb/317095

Optimistic Concurrency in Entity Framework Code First
http://www.codeproject.com/Articles/817432/Optimistic-Concurrency-in-Entity-Framework-Code-Fi

Database Concurrency Conflicts in the Real World
http://www.codemag.com/article/0607081

2016-03-03

SQL 效能調教:規劃記憶體(RAM Capacity)到比資料表、資料庫還要大的容量

結論

(1) 配置足夠的記憶體,讓 SQL Server 緩衝集區大到足以存放資料表


擴充記憶體(RAM Capacity)到比資料庫 還要大的容量。
若未配置足夠的記憶體(RAM)給 SQL Server 使用,將導致耗費時間在等待慢速的Disk I/O上!


(2) 建立合適的索引,避免發生存取大量Disk I/O


沒有使用到索引(Index),就會耗用Disk I/O。
Table Scan、Index Scan,是耗用大量的Disk I/O。


索引的建立需要考量更多的事情,筆者將在後續文章討論。

(3) 使用高速SSD或PCIe介面的Flash儲存裝置等來處理慢速Disk I/O的問題

(4) 規劃良好的資料表設計結構、資料歸檔機制、資料存取策略(例如:push、pull、讀寫分流)等,避免發生存取大量Disk I/O


使用合適的資料庫架構規劃、軟體架構規劃、適合的演算法等,是我們軟體人員一致都認同的解決方案。

但每個資訊系統或多或少都有「技術債(Technical Debt)」,當年抄捷徑的技術債,遲早要還的!


理由

所有資料庫軟體的主要設計目的之一,便是將磁碟 I/O 最小化,因為磁碟的讀取和寫入,是電腦上最需要用到大量資源的作業之一。
SQL Server 會在記憶體中建立緩衝集區,以保存從資料庫讀取的頁面。
SQL Server 的大部分程式碼,主要是用來最小化磁碟和緩衝集區之間實體讀取和寫入數目。


延伸閱讀

對回應速度且重視服務水準的系統,需要更快速的執行效能,資料庫廠商推出了:In-Memory Database。

Oracle Database In-Memory
http://www.oracle.com/us/products/database/options/database-in-memory/overview/index.html?ssSourceSiteId=opn

SQL Server In-Memory OLTP (記憶體中最佳化)
https://msdn.microsoft.com/zh-tw/library/dn133186(v=sql.120).aspx



實測數據

情境 1:記憶體配置足以存放資料表

環境:記憶體配置 1 GB,但資料表使用空間僅約 500 MB。

資料表使用空間
SQL Server配置的記憶體
執行次數
耗時()

476.64(MB)

1024(MB)
1次執行
9.155
2次執行
0.017
3次執行
0.019

分析:

第 1 次執行,會慢的原因是:由硬碟將資料讀取到記憶體(RAM)內,需要耗費時間在等待慢速的Disk I/O上!

第 2 次之後的執行,因資料表都在 SQL Server 緩衝集區內,無須存取存取慢速的Disk I/O,達成秒殺報表的需求!


情境 2:記憶體配置不足

環境:記憶體僅配置 1024 MB,而資料表使用空間大於此配置(例如:約 2031 MB)

資料表使用空間
SQL Server配置的記憶體
執行次數
耗時()

2031.57(MB)

1024(MB)
1次執行
38.887
2次執行
39.123
3次執行
39.109

分析:

由於記憶體配置不足,使得 SQL Server 緩衝集區沒有足夠的記憶體可以存放資料表,導致每次存取資料表都是執行慢速的 Disk I/O 作業!



實測的截圖資料

01_實測數據



02_資料表的使用空間




03_觀察:各個資料庫所使用的緩衝集區_2GB資料表




21_資料表500MB,記憶體1GB_第1次查詢




22_資料表500MB,記憶體1GB_第2次查詢




23_資料表500MB,記憶體1GB_第3次查詢



24_資料表2GB,記憶體1GB_第1次查詢


25_資料表2GB,記憶體1GB_第2次查詢



26_資料表2GB,記憶體1GB_第3次查詢



31_查詢目前_最大可用記憶體的組態選項值_SSMS


32_查詢目前_最大可用記憶體的組態選項值_TSQL





範例程式碼


 
-- 01_建立資料庫
SET NOCOUNT ON
USE master
GO
CREATE DATABASE [TSIO] ON  PRIMARY
( NAME = N'TSIO', FILENAME = N'C:\TSQLDB\TSIO.mdf' , SIZE = 8192MB , FILEGROWTH = 102400KB )
 LOG ON
( NAME = N'TSIO_log', FILENAME = N'C:\TSQLDB\TSIO_log.ldf' , SIZE = 102400KB , FILEGROWTH = 102400KB )
GO
 
-- 02_建立兩個範例資料表,一個有索引,一個沒有
USE TSIO
GO
CREATE TABLE TS_NOPK_2GB
(sid INT IDENTITY, wdatetime datetime2 ,cdata char(8000))
GO
CREATE TABLE TS_PK_2GB
(sid INT IDENTITY PRIMARY KEY, wdatetime datetime2 ,cdata char(8000))
GO

CREATE TABLE TS_NOPK_500MB
(sid INT IDENTITY, wdatetime datetime2 ,cdata char(8000))
GO
CREATE TABLE TS_PK_500MB
(sid INT IDENTITY PRIMARY KEY, wdatetime datetime2 ,cdata char(8000))
GO

 
-- 03_新增資料列
DECLARE @cnt INT=1
WHILE @cnt < 61000
BEGIN
 INSERT TS_PK_500MB VALUES (SYSDATETIME(),CONVERT(varchar(100),GETDATE(), 113)+' '+ APP_NAME())
 INSERT TS_NOPK_500MB VALUES (SYSDATETIME(),CONVERT(varchar(100),GETDATE(), 113)+' '+ APP_NAME())
 SET @cnt +=1
END
GO
 
-- 04_查詢目前資料庫內,每一個資料表的使用空間資訊
SELECT a3.name AS N'結構描述', a2.name AS N'資料表', a1.rows AS N'資料列筆數', (a1.reserved + ISNULL(a4.reserved,0))* 8.0/1024 AS N'配置的空間總量(MB)', a1.data * 8.0/1024 AS '資料(MB)', 
(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8.0/1024 AS N'索引(MB)', 
(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8.0/1024 AS N'尚未使用(MB)',
a2.create_date N'建立日期', a2.modify_date N'修改日期'
FROM (SELECT ps.object_id, SUM ( CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END ) AS [rows], 
SUM (ps.reserved_page_count) AS reserved, SUM ( CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count) 
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END ) AS data, 
SUM (ps.used_page_count) AS used 
FROM sys.dm_db_partition_stats ps 
GROUP BY ps.object_id) AS a1 LEFT OUTER JOIN
(SELECT it.parent_id, SUM(ps.reserved_page_count) AS reserved, SUM(ps.used_page_count) AS used 
FROM sys.dm_db_partition_stats ps INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id) 
WHERE it.internal_type IN (202,204) 
GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id) 
INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id ) 
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id) 
WHERE a2.type <> N'S' and a2.type <> N'IT'
ORDER BY a1.data DESC
GO



 
-- 查詢:伺服器所組態的最大可用記憶體是:1024 MB
SELECT
cfg.name AS N'組態選項', cfg.minimum AS N'最小值', cfg.maximum AS N'最大值', cfg.value AS N'設定的組態選項值', cfg.value_in_use AS N'執行中的值', cfg.description AS N'描述'
FROM sys.configurations AS cfg
WHERE name ='max server memory (MB)'

-- 原本:2147483647
USE master
GO
EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max server memory (MB)', N'1024'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE
GO

/*=========================================================*/

-- 05_查詢_沒有使用到索引,回傳一筆
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SET STATISTICS TIME ON
GO

-- 第 1 次:查詢此資料表的使用空間是:500MB
/*
目前組態的最大可用記憶體是:1GB,
*/

SELECT * FROM TS_NOPK_500MB WHERE sid=1

-- 第 2 次:查詢此資料表的使用空間是:500MB

SELECT * FROM TS_NOPK_500MB WHERE sid=1

-- 第 3 次:查詢此資料表的使用空間是:500MB

SELECT * FROM TS_NOPK_500MB WHERE sid=17


-- 05_查詢_沒有使用到索引,回傳一筆
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SET STATISTICS TIME ON
GO

/*=========================================================*/

-- 第 1 次:查詢此資料表的使用空間是:2GB
/*
目前組態的最大可用記憶體是:1GB,
*/

SELECT * FROM TS_NOPK_2GB WHERE sid=1

-- 第 2 次:查詢此資料表的使用空間是:2GB

SELECT * FROM TS_NOPK_2GB WHERE sid=1

-- 第 3 次:查詢此資料表的使用空間是:2GB

SELECT * FROM TS_NOPK_2GB WHERE sid=1

/*=========================================================*/

-- 觀察:各個資料庫所使用的緩衝集區 - SQL Server 緩衝集區(buffer pool)中的使用資訊
SELECT db.name, (COUNT(*)*8.0)/1024 AS N'耗用緩衝集區(MB)'
FROM sys.dm_os_buffer_descriptors ob INNER JOIN sys.sysdatabases db
ON ob.database_id =db.dbid
GROUP BY db.name
ORDER BY 2 DESC
GO





參考資料

SQL 效能調教:沒有使用到索引,可改用SSD或PCIe介面的Flash儲存裝置來達到 秒殺,提供更佳的服務水準給客戶!
http://sharedderrick.blogspot.tw/2016/02/disk-iossdpcieflash.html

如何降低在 64 位元版本的 SQL Server 的緩衝區集區記憶體的分頁
https://support.microsoft.com/zh-tw/kb/918483

如何使用 SQL Server 中的組態選項來調整記憶體使用量
https://support.microsoft.com/zh-tw/kb/321363

緩衝區管理
https://technet.microsoft.com/zh-tw/library/aa337525(v=sql.105).aspx

記憶體架構
https://technet.microsoft.com/zh-tw/library/ms187499(v=sql.105).aspx

技術債觀念及實務
http://www.ithome.com.tw/voice/100462

抄捷徑的技術債,遲早要還的
http://www.ithome.com.tw/node/71807

In-Memory OLTP (記憶體中最佳化)
https://msdn.microsoft.com/zh-tw/library/dn133186(v=sql.120).aspx