驗證Partition Table是否改善記憶體快取使用

驗證結果的確是index和partition table都有節省記憶體快取
以後再研究index seek和index scan細部運作，和它還不太熟@@

最後解法還是把歷史資料拆成歷史檔，確認程式不會查太久的資料
用SqlAgent每月把歷史資料轉入歷史檔
但是建NonClustered Index也會有幫助，只是ldf會比較大
PK是交易日期+交易編號+序號
交易日期是會最常被搜尋的column，所以用它建NonClustered Index
這題應該用不到Partition Table

SQL筆記：Index Scan vs Index Seek
Sql Server中的表访问方式Table Scan, Index Scan, Index Seek
SQL Server中SCAN 和SEEK的区别
Buffer Management

	-- 參考 https://dotblogs.com.tw/jamesfu/archive/2012/12/25/partitiontable.aspx?fid=77758
	-- 驗證結果 https://1drv.ms/w/s!AmQ3SaTA10NQiVjYibUSqYphB1Xq
	---- 建立 FILEGROUP
	declare @i int = 1;
	declare @SQL VARCHAR(1000)
	while @i <= 9
	begin
	set @SQL = 'alter database [RexBigDb] add filegroup [PartitionFG'+LTRIM(STR(@i,2))+'0million];'
	exec( @SQL )
	--PRINT(@SQL) --不執行
	set @i += 1
	end
	set @SQL = 'alter database [RexBigDb] add filegroup [PartitionFGOther];'
	exec( @SQL )
	GO
	---- 每個 FileGroup 至少要指定一個檔案
	declare @i int = 1;;
	declare @SQL VARCHAR(1000)
	while @i <= 9
	begin
	set @SQL = 'alter database RexBigDb add file ( name=RexBigDbPartitionFile'+LTRIM(STR(@i,2))+'0million, filename="D:\MSSQLDATA\RexBigDbPartitionFile'+LTRIM(STR(@i,2))+'0million.ndf", size=1GB ) to filegroup PartitionFG'+LTRIM(STR(@i,2))+'0million' ;
	exec( @SQL )
	--PRINT(@SQL) --不執行
	set @i += 1
	end
	set @SQL = 'alter database RexBigDb add file ( name=RexBigDbPartitionFileOther, filename="D:\MSSQLDATA\RexBigDbPartitionFileOther.ndf", size=1GB ) to filegroup PartitionFGOther' ;
	exec( @SQL )
	GO
	---- 建立partition function(按照id區分)
	-- range right:邊界值屬於右邊的partition。所以20000000這筆資料會存在RexBigDbPartitionFile20million.ndf
	create partition function pfByID(int)
	as range right for values (10000000, 20000000, 30000000, 40000000, 50000000, 60000000, 70000000, 80000000, 90000000);
	---- 建立 Partition Schema(依據 Partition Function)
	create partition scheme psByID
	as partition pfByID
	TO ([PartitionFGOther], [PartitionFG10million], [PartitionFG20million], [PartitionFG30million], [PartitionFG40million], [PartitionFG50million], [PartitionFG60million], [PartitionFG70million], [PartitionFG80million], [PartitionFG90million]);
	---- 建立一個檢查用的 Function
	create function PartitionInfo( @tablename sysname ) returns table
	as return
	select
	OBJECT_NAME(p.object_id) as TableName
	,p.partition_number as PartitionNumber
	,prv_left.value as LowerBoundary
	,prv_right.value as UpperBoundary
	,ps.name as PartitionScheme
	,pf.name as PartitionFunction
	,fg.name as FileGroupName
	,CAST(p.used_page_count * 8.0 / 1024 AS NUMERIC(18,2)) AS UsedPages_MB
	,p.row_count as Rows
	from sys.dm_db_partition_stats p
	inner join sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id
	inner join sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
	inner join sys.partition_functions pf ON ps.function_id = pf.function_id
	inner join sys.destination_data_spaces dds ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number
	inner join sys.filegroups fg ON fg.data_space_id = dds.data_space_id
	left join sys.partition_range_values prv_right ON prv_right.function_id = ps.function_id AND prv_right.boundary_id = p.partition_number
	left join sys.partition_range_values prv_left ON prv_left.function_id = ps.function_id AND prv_left.boundary_id = p.partition_number - 1
	where
	p.object_id = OBJECT_ID(@tablename) and p.index_id < 2
	----建立測試用的Table
	--建立測試Table (沒有PRIMARY KEY)
	CREATE TABLE [dbo].[tenmillionData](
	[id] [int] NOT NULL,
	[ids] [char](8) NOT NULL,
	[idsn] [nchar](8) NOT NULL,
	[idsvar] [varchar](8) NOT NULL,
	[idsnvar] [nvarchar](8) NOT NULL,
	[s1] [nvarchar](20) NULL,
	[s2] [nvarchar](20) NULL
	) ON [PRIMARY] ----ON PRIMARY filegroup
	GO
	--建立測試Table (有PRIMARY KEY)
	CREATE TABLE [dbo].[tenmillionDataWithPK](
	[id] [int] NOT NULL,
	[ids] [char](8) NOT NULL,
	[idsn] [nchar](8) NOT NULL,
	[idsvar] [varchar](8) NOT NULL,
	[idsnvar] [nvarchar](8) NOT NULL,
	[s1] [nvarchar](20) NULL,
	[s2] [nvarchar](20) NULL,
	CONSTRAINT PK_tenmillionDataWithPK_id PRIMARY KEY CLUSTERED ([id])
	) ON [PRIMARY] ----ON PRIMARY filegroup
	GO
	--建立測試Table (使用partition scheme·沒有PRIMARY KEY)
	CREATE TABLE [dbo].[tenmillionDataPT](
	[id] [int] NOT NULL,
	[ids] [char](8) NOT NULL,
	[idsn] [nchar](8) NOT NULL,
	[idsvar] [varchar](8) NOT NULL,
	[idsnvar] [nvarchar](8) NOT NULL,
	[s1] [nvarchar](20) NULL,
	[s2] [nvarchar](20) NULL
	) ON psByID(id) ----改成ON partition scheme
	GO
	--建立測試Table (使用partition scheme·有PRIMARY KEY)
	CREATE TABLE [dbo].[tenmillionDataPTwithPK](
	[id] [int] NOT NULL,
	[ids] [char](8) NOT NULL,
	[idsn] [nchar](8) NOT NULL,
	[idsvar] [varchar](8) NOT NULL,
	[idsnvar] [nvarchar](8) NOT NULL,
	[s1] [nvarchar](20) NULL,
	[s2] [nvarchar](20) NULL,
	CONSTRAINT PK_tenmillionDataPTwithPK_id PRIMARY KEY CLUSTERED ([id])
	) ON psByID(id) ----改成ON partition scheme
	GO
	----建立99999999筆測試資料(需要執行很久很久)
	SET NOCOUNT ON;
	GO
	declare @i int,@rows int
	set @i = 1
	set @rows = 99999999
	WHILE(@i<=@rows)
	BEGIN
	INSERT INTO tenmillionData (id , ids, idsn, idsvar, idsnvar , s1 , s2)
	VALUES (@i, CAST(@i AS char(8)) , CAST(@i AS nchar(8)) , CAST(@i AS varchar(8)) , CAST(@i AS nvarchar(8)) , 'TEST'+CAST(@i AS nvarchar(8)) , CAST(@i AS nvarchar(8))+'TEST');
	SET @i = @i+1;
	END
	SET NOCOUNT OFF;
	GO
	--
	INSERT INTO tenmillionDataWithPK
	SELECT *
	FROM tenmillionData
	GO
	--
	INSERT INTO tenmillionDataPT
	SELECT *
	FROM tenmillionData
	GO
	--
	INSERT INTO tenmillionDataPTwithPK
	SELECT *
	FROM tenmillionData
	GO
	----Rebuild Index
	--需要20分鐘以上
	ALTER INDEX PK_tenmillionDataPTwithPK_id ON dbo.tenmillionDataPTwithPK REBUILD;
	ALTER INDEX PK_tenmillionDataWithPK_id ON dbo.tenmillionDataWithPK REBUILD;
	---- 利用 UDF 來檢查 Partition 資料存放狀況，確認資料分散在各ndf
	select * from dbo.PartitionInfo('dbo.tenmillionDataPT')
	---- 確認四個Table的筆數都是99999999筆
	-- 指令來源 https://dotblogs.com.tw/rainmaker/archive/2012/02/02/67498.aspx
	SET NOCOUNT ON
	DBCC UPDATEUSAGE(0)
	EXEC sp_spaceused
	CREATE TABLE #t
	(
	[name] NVARCHAR(128),
	[rows] CHAR(11),
	reserved VARCHAR(18),
	data VARCHAR(18),
	index_size VARCHAR(18),
	unused VARCHAR(18)
	)
	INSERT #t EXEC sys.sp_MSforeachtable 'EXEC sp_spaceused ''?'''
	select * from #t where name in ('[dbo].[tenmillionData]','[dbo].[tenmillionDataWithPK]','[dbo].[tenmillionDataPTwithPK]','[dbo].[tenmillionDataPT]')
	----將資料載入cache
	CHECKPOINT;
	DBCC DROPCLEANBUFFERS;
	select count(1) from tenmillionData where id > 30000000 AND id < 50000000
	--執行『指令find-memory-usage-by-table』，查看記憶體快取使用情形
	--請先REBUILD INDEX((where條件有使用PK))
	CHECKPOINT;
	DBCC DROPCLEANBUFFERS;
	select count(1) from tenmillionDataWithPK where id > 30000000 AND id < 50000000
	--執行『指令find-memory-usage-by-table』，查看記憶體快取使用情形
	--有建PK，但where條件沒使用PK
	CHECKPOINT;
	DBCC DROPCLEANBUFFERS;
	select count(1) from tenmillionDataWithPK where ids > 30000000 AND ids < 50000000
	--執行『指令find-memory-usage-by-table』，查看記憶體快取使用情形
	CHECKPOINT;
	DBCC DROPCLEANBUFFERS;
	select count(1) from tenmillionDataPT where id > 30000000 AND id < 50000000
	--執行『指令find-memory-usage-by-table』，查看記憶體快取使用情形
	CHECKPOINT;
	DBCC DROPCLEANBUFFERS;
	select count(1) from tenmillionDataPTwithPK where id > 30000000 AND id < 50000000
	--執行『指令find-memory-usage-by-table』，查看記憶體快取使用情形
	--有建PK，但where條件沒使用PK
	CHECKPOINT;
	DBCC DROPCLEANBUFFERS;
	select count(1) from tenmillionDataPTwithPK where ids > 30000000 AND ids < 50000000
	--執行『指令find-memory-usage-by-table』，查看記憶體快取使用情形
	----指令find-memory-usage-by-table
	-- 指令來源 https://dba.stackexchange.com/questions/44541/how-to-find-memory-usage-by-table
	set nocount on;
	set transaction isolation level read uncommitted;
	select
	count(*)as cached_pages_count,
	(COUNT(*) * 8.0) / 1024 AS Total_MB_Occupied, -- convert pages into MB - the page size is 8 KB for sql server
	obj.name as objectname,
	ind.name as indexname,
	obj.index_id as indexid
	from sys.dm_os_buffer_descriptors as bd
	inner join
	(
	select object_id as objectid,
	object_name(object_id) as name,
	index_id,allocation_unit_id
	from sys.allocation_units as au
	inner join sys.partitions as p
	on au.container_id = p.hobt_id
	and (au.type = 1 or au.type = 3)
	union all
	select object_id as objectid,
	object_name(object_id) as name,
	index_id,allocation_unit_id
	from sys.allocation_units as au
	inner join sys.partitions as p
	on au.container_id = p.partition_id
	and au.type = 2
	) as obj
	on bd.allocation_unit_id = obj.allocation_unit_id
	left outer join sys.indexes ind
	on obj.objectid = ind.object_id
	and obj.index_id = ind.index_id
	where bd.database_id = db_id()
	and bd.page_type in ('data_page', 'index_page')
	and obj.name in ('tenmillionData','tenmillionDataPT','tenmillionDataWithPK','tenmillionDataPTWithPK')
	group by obj.name, ind.name, obj.index_id
	order by cached_pages_count desc

view raw 驗證Partition Table是否改善記憶體快取使用 hosted with ❤ by GitHub

動態磁碟轉換基本磁碟(Convert Dynamic Disk to Basic Disk)

舊硬碟是500GB，原本打算用Acronis的Clone Disk把磁碟複製到新的2TB硬碟
被動態磁碟搞了一下午@@ 
Acronis在動態磁碟無法用Clone Disk
只能用備份再還原，但無法開機(no boot disk detected or the disk has failed)
DISKPART指令要先delete volume才能轉，硬碟裡面有很多資料不能刪
最後用『分区助手』先把來源磁碟轉成基本磁碟(轉換只要幾秒)
終於順利使用Acronis的Clone Disk完成

在ptt找到有人的分享
https://www.ptt.cc/bbs/Storage_Zone/M.1344084490.A.B8B.html 

AOMEI Partition Assistant和AOMEI Backupper付費的專業版似乎也可以 
都是傲梅科技的產品，『分区助手』是免費的，但只有簡體中文介面

備註:
EaseUS Partition Master專業版(付費)應該也可以，沒試過