數據文件移庫之後，SQL 查詢變慢
CSDN 上碰到網友提了這麼個問題：
sqlserver2008 ， 資料庫有2個 數據文件；原來都在 普通雲盤上；
現在移動其中的1個到了SSD上；
結果是：1個數據文件在普通雲盤上，1個數據文件在SSD上；
移動後，發現 資料庫性能下降很多；（很多SQL都耗時增大了）
1，什麼原因呢？
2，是否需要重建索引？如果需要，為什麼需要？
很多時候， SQL 查詢變慢，並不僅僅是我們的 SQL 寫的不夠好。SQL 在我們看來也就簡單的幾個表連接，同樣的條件語句，但是某一段時間就是慢了。當從 SQL 本身無法下手去判斷問題癥結的時候，我們別忘了，資料庫軟體是個系統，有很多組件限制了 SQL 查詢引擎的發揮。
比如這裡就存在這種問題，當數據文件遷移了之後，是不是索引需要重建呢？我們看到 SQL 寫得溜，不僅僅是業務邏輯了解的通透，能將業務邏輯清晰的寫出來固然重要，但是得跑上很長時間得到的結果，那麼成本本身就是巨大的，別提商機這麼宏偉的詞兒了，就是自己也受不了。
很多開發工程師都會認為 SQL 嘛，隨用隨查不必深究。我敢保票，這種態度永遠也只是一知半解，不知其所以然，是對知識的褻瀆，倒霉的還是自己。除非你特別幸運。
上面的問題就考察了對資料庫系統的了解功底。回答出這個問題的本質，需要我們至少對這些方面的了解：
1 數據文件是什麼，存儲了哪些對象
2 數據表和索引，到底是存儲在哪裡
我這裡有段腳本專門是用來辨認物理數據文件上，存儲的資料庫對象的（如果是生產環境，請不要在高峰期間使用）。運行完這段腳本，你可以知道表和索引分別存儲在哪個物理文件中了，也就完美的解決了，是不是需要重建索引的問題。
修改紅色字體部分為你的庫
DECLARE @tablename VARCHAR(200)
DECLARE @index_Id INT
DECLARE @sqlstatement NVARCHAR(max)
DECLARE @databasename VARCHAR(200) ="home"
DECLARE cur_tables CURSOR
FOR
(
SELECT schema_name(schema_id) + "." + NAME AS tableName
FROM sys.tables
)
OPEN cur_tables
FETCH NEXT
FROM cur_tables
INTO @tablename
IF EXISTS (
SELECT 1
WHERE upper(NAME) LIKE upper("%tempTabIndall%")
)
DROP TABLE #tempTabIndall;
CREATE TABLE #tempTabIndall (
PageFID BIGINT
,PagePID BIGINT
,IAMFID BIGINT
,IAMPID BIGINT
,ObjectID BIGINT
,IndexId BIGINT
,PartitionNumber BIGINT
,PartitionID BIGINT
,iam_chain_type VARCHAR(500)
,PageType BIGINT
,IndexLevel BIGINT
,NextPageFID BIGINT
,NextPagePID BIGINT
,PrevPageFID BIGINT
,PrevPagePID BIGINT
)
CREATE INDEX idx_pagefid ON #tempTabIndall (PageFID);
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE cur_indexes CURSOR
FOR
(
SELECT index_id
FROM sys.indexes
WHERE object_id = object_id(@tablename)
)
OPEN cur_indexes
FETCH NEXT
FROM cur_indexes
INTO @index_Id
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlstatement = N"insert into #tempTabIndall
exec sp_executesql N""DBCC IND(" + @databasename + ",""""" + @tablename + """""," + convert(VARCHAR(max), @index_Id) + ")""";
PRINT @sqlstatement
EXEC sp_executesql @sqlstatement
FETCH NEXT
FROM cur_indexes
INTO @index_Id
END
CLOSE cur_indexes
DEALLOCATE cur_indexes
FETCH NEXT
FROM cur_tables
INTO @tablename
END
CLOSE cur_tables
DEALLOCATE cur_tables
SELECT DISTINCT object_name(t.ObjectID) AS tablename
,t.IndexId
,ti.NAME AS IndexName
,f.FileGroupName
,f.Filegroup_type_description
,f.DefaultFileGroup
,f.datafile_type_description
,f.fileName
,f.file_physical_name
FROM #tempTabIndall t
INNER JOIN (
SELECT DISTINCT object_id
,index_id
,NAME
FROM sys.indexes
) ti ON t.ObjectID = ti.object_id
AND t.IndexId = ti.index_id
LEFT JOIN (
SELECT isnull(data_file_id, 0) AS data_file_id
,isnull(g.FileGroupName, "LOG File Group") AS FileGroupName
,isnull(g.type_desc, "LOG FILE GROUP") AS Filegroup_type_description
,isnull(g.is_default, 0) AS DefaultFileGroup
,f.type_desc AS datafile_type_description
,f.NAME AS fileName
,f.physical_name AS file_physical_name
,f.state_desc AS datafilestatus
,f.size_mb AS datafile_size_mb
,f.max_size_mb AS datafile_max_size_mb
FROM (
SELECT NAME AS FileGroupName
,data_space_id
,type_desc
,is_default
FROM sys.filegroups
) g
RIGHT OUTER JOIN (
SELECT file_id AS data_file_id
,type_desc
,data_space_id
,NAME
,physical_name
,state_desc
,size * 8 / 1024 AS size_mb
,max_size * 8 / 1024 AS max_size_mb
FROM sys.database_files
) f ON g.data_space_id = f.data_space_id
) f ON f.data_file_id = t.PageFID
ORDER BY f.file_physical_name ASC
,object_name(t.ObjectID) ASC
,t.IndexId ASC
如果我們這樣提出問題問自己，或許會對存儲本質了解的更透徹一些：
1） 數據文件中存儲的是什麼
2）一張表所佔用的數據頁，怎麼表示存儲地址
3）索引的數據頁，怎麼表示存儲地址
4）索引的數據頁，除了索引值，是不是還有指向表記錄的指針，這個指針怎麼表示
我嘗試回答上面的問題，你可以先不看，自己琢磨，回頭看看我說的是不是對你有幫助：
1） 數據文件中存儲的是什麼
資料庫對象，包括表，索引等。
當我們創建一個表的時候，是在這份文件中得到一個邏輯上的劃分空間，在這份空間中，表被存儲為數據頁
索引同理！
2）一張表所佔用的數據頁，怎麼表示存儲地址
表空間是一系列的數據頁組成的，每個數據頁標註了數據文件的邏輯編碼（sys.database_files中的file_Id, file_id 是個邏輯數字，由 SQL Server 分配，關聯到物理路徑地址，即操作系統中實際的存儲文件地址）和數據頁碼
表的數據頁用（nFileId,nPage）來表示，比如Page(3,4) 表示的是 sys.database_files 中 file_id 等於 3 所在的數據文件上，第三個數據頁。此時表數據頁的地址隨著數據文件已經固定了。
3）索引的數據頁，怎麼表示存儲地址
索引某種意義上的表，索引數據頁的結構信息同表
4）索引的數據頁，除了索引值，是不是還有指向表記錄的指針，這個指針怎麼表示
表記錄本身，就是表數據頁上的一個地址，叫做slot.
根據2）中所說的，那麼這個指針對應的就是 Page(3,4) 上的第n個 slot 中存儲的數據。
所以指針存儲的是表記錄的相對地址。
綜上，一切都已經在數據文件中用邏輯方式確定了表及索引的存儲地址，數據文件再怎麼遷移，表及索引就像是你的紋身一樣，跟著你。並不是你走到哪裡都要重新紋一遍。
TAG:全球大搜羅 |