sqlserver2008，移动一个数据文件到SSD上，发现性能下降

[samyp1234]

sqlserver2008 ， 数据库有2个 数据文件；原来都在 普通云盘上；
现在移动其中的1个到了SSD上；
结果是：1个数据文件在普通云盘上，1个数据文件在SSD上；
移动后，发现 数据库性能下降很多；（很多SQL都耗时增大了）
1，什么原因呢？
2，是否需要重建索引？如果需要，为什么需要？

[吉普赛的歌]

不需要重建索引。 箱子里的东西， 不会因为搬运就动了位置。 当然， 你觉得有必要重建索引， 为什么不试一下呢？ 重建索引生产环境一般是每周一次， 不需要为这点小事情争论。 另外， 磁盘的IO性能到底有没有下降， 你可以实际测试一下， 原旧盘和新的SSD盘都测试一下： https://download.csdn.net/download/yenange/9540422

[samyp1234]

是不是因为：数据的物理位置移动了，所以需要重建索引 ？

[OwenZeng_DBA]

引用 3 楼 samyp1234 的回复:

是不是因为：数据的物理位置移动了，所以需要重建索引 ？

是否需要重建索引主要取决于索引碎片，你可以检查下碎片率是多少

[samyp1234]

引用 3 楼 samyp1234 的回复:

是不是因为：数据的物理位置移动了，所以需要重建索引 ？

聚集索引包含了 该行数据的具体位置信息，非聚集索引有指向聚集索引的指针； 所以我的理解：如果数据的物理位置移动了， 聚集索引、和 非聚集索引就必须重建了， 我的理解对吗 ？

[OwenZeng_DBA]

引用 6 楼 samyp1234 的回复:

[quote=引用 3 楼 samyp1234 的回复:] 是不是因为：数据的物理位置移动了，所以需要重建索引 ？

聚集索引包含了 该行数据的具体位置信息，非聚集索引有指向聚集索引的指针； 所以我的理解：如果数据的物理位置移动了， 聚集索引、和 非聚集索引就必须重建了， 我的理解对吗 ？ [/quote] b不对，参考我上一条回复。

[samyp1234]

引用 7 楼 z10843087 的回复:

[quote=引用 6 楼 samyp1234 的回复:] [quote=引用 3 楼 samyp1234 的回复:] 是不是因为：数据的物理位置移动了，所以需要重建索引 ？

聚集索引包含了 该行数据的具体位置信息，非聚集索引有指向聚集索引的指针； 所以我的理解：如果数据的物理位置移动了， 聚集索引、和 非聚集索引就必须重建了， 我的理解对吗 ？ [/quote] b不对，参考我上一条回复。[/quote] 我想问一下，我的理解， （对于 聚集索引、非聚集索引） 为什么不对呢 ？

[吉普赛的歌]

不需要想象，不需要理论，你做一次实验就好。 无非先测一次碎片率 移动数据文件 再测一次碎片率。 15分钟就能搞定

[samyp1234]

没人知道吗 ？

[dbLenis]

引用 8 楼 samyp1234 的回复:

[quote=引用 7 楼 z10843087 的回复:]
[quote=引用 6 楼 samyp1234 的回复:]
[quote=引用 3 楼 samyp1234 的回复:]
是不是因为：数据的物理位置移动了，所以需要重建索引 ？

聚集索引包含了 该行数据的具体位置信息，非聚集索引有指向聚集索引的指针；
所以我的理解：如果数据的物理位置移动了， 聚集索引、和 非聚集索引就必须重建了， 我的理解对吗 ？

[/quote]
b不对，参考我上一条回复。[/quote]

我想问一下，我的理解， （对于 聚集索引、非聚集索引） 为什么不对呢 ？ [/quote]


题主： 你说的非常对！

数据库文件，分配的是系统IO资源。 资源的物理地址变了，那么索引叶节点的地址肯定需要重编。
除了索引问题，查询变慢的原因是多方面的，比如数据迁移之后， 缓存统统失效，因此解析和取数本就是慢的。

先解决第一个问题，即索引是不是需要重建？

如果不重新建立索引，原先的索引是不是就无效了呢？
这取决于你是如何操作，将原本的数据文件，放到 SSD 新盘上去的。
1） 如果是在停掉 SQL Server 服务的情况下，将数据文件硬剪贴过去，然后重启服务，添加SSD数据文件，那么索引无效；
2） 如果是在SQL Server 运行的情况下，添加了 SSD 新盘的数据文件，然后往里头导入数据的，那么索引已经重建

我不知道你在移动数据文件的过程中，是如何操作的，根据你目前的状况，我们要做的事情，就是辨认，新的SSD 盘上的数据文件，到底存储了哪些表：

我这里有段脚本专门是用来辨认物理数据文件上，存储的数据库对象的（如果是生产环境，请不要在高峰期间使用）
修改红色字体部分为你的库

效果用下图來示意：


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 from tempdb.sys.tables 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



如果你怀疑是 SSD 上的数据文件中存储的表，因为缺乏索引而变慢，那么只要筛选 file_physical_name 是你所用 SSD 盘路径，取其中一表，查看其或者依赖它的存储过程 的执行计划就可以。

我曾经写过一篇比较啰嗦的文章 ， 用来分析 SQL Server 的内部存储结构，你有兴趣可以参考 ：
https://blog.csdn.net/wujiandao/article/details/51501522

第二个问题，是你的查询变慢了

这就需要你来配合 ：
1 慢的 SQL ，其 SQL 语句及执行计划是不是可以贴一下 ？
2 在并发没有明显加大的情况 下 ，SQL 变慢可以通过 set profile time/IO 等方式 查询具体指标 ，比如解析慢了，还是 IO 慢了
3 如果是查询多次之后 ，查询区域稳定，那么就是执行计划以及数据缓存重新抽取造成的，不必惊慌

我同样也写过 性能分析的文章 ，不怕啰嗦的话也可以读一读
https://blog.csdn.net/wujiandao/article/details/51307741

其实，前面几位答主说的很对了，最快的方式就是直接重建索引，看看是否有效 ！

[samyp1234]

引用 11 楼 wujiandao 的回复:

[quote=引用 8 楼 samyp1234 的回复:] [quote=引用 7 楼 z10843087 的回复:] [quote=引用 6 楼 samyp1234 的回复:] [quote=引用 3 楼 samyp1234 的回复:] 是不是因为：数据的物理位置移动了，所以需要重建索引 ？

聚集索引包含了 该行数据的具体位置信息，非聚集索引有指向聚集索引的指针； 所以我的理解：如果数据的物理位置移动了， 聚集索引、和 非聚集索引就必须重建了， 我的理解对吗 ？ [/quote] b不对，参考我上一条回复。[/quote] 我想问一下，我的理解， （对于 聚集索引、非聚集索引） 为什么不对呢 ？ [/quote] 题主： 你说的非常对！ 数据库文件，分配的是系统IO资源。 资源的物理地址变了，那么索引叶节点的地址肯定需要重编。 除了索引问题，查询变慢的原因是多方面的，比如数据迁移之后， 缓存统统失效，因此解析和取数本就是慢的。 先解决第一个问题，即索引是不是需要重建？ 如果不重新建立索引，原先的索引是不是就无效了呢？ 这取决于你是如何操作，将原本的数据文件，放到 SSD 新盘上去的。 1） 如果是在停掉 SQL Server 服务的情况下，将数据文件硬剪贴过去，然后重启服务，添加SSD数据文件，那么索引无效； 2） 如果是在SQL Server 运行的情况下，添加了 SSD 新盘的数据文件，然后往里头导入数据的，那么索引已经重建 我不知道你在移动数据文件的过程中，是如何操作的，根据你目前的状况，我们要做的事情，就是辨认，新的SSD 盘上的数据文件，到底存储了哪些表： 我这里有段脚本专门是用来辨认物理数据文件上，存储的数据库对象的（如果是生产环境，请不要在高峰期间使用） 修改红色字体部分为你的库 效果用下图來示意： 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 from tempdb.sys.tables 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 如果你怀疑是 SSD 上的数据文件中存储的表，因为缺乏索引而变慢，那么只要筛选 file_physical_name 是你所用 SSD 盘路径，取其中一表，查看其或者依赖它的存储过程 的执行计划就可以。 我曾经写过一篇比较啰嗦的文章 ， 用来分析 SQL Server 的内部存储结构，你有兴趣可以参考 ： https://blog.csdn.net/wujiandao/article/details/51501522 第二个问题，是你的查询变慢了 这就需要你来配合 ： 1 慢的 SQL ，其 SQL 语句及执行计划是不是可以贴一下 ？ 2 在并发没有明显加大的情况 下 ，SQL 变慢可以通过 set profile time/IO 等方式 查询具体指标 ，比如解析慢了，还是 IO 慢了 3 如果是查询多次之后 ，查询区域稳定，那么就是执行计划以及数据缓存重新抽取造成的，不必惊慌 我同样也写过 性能分析的文章 ，不怕啰嗦的话也可以读一读 https://blog.csdn.net/wujiandao/article/details/51307741 其实，前面几位答主说的很对了，最快的方式就是直接重建索引，看看是否有效 ！ [/quote] 多谢多谢； 我的方式是： 先分离数据库；然后移动数据文件到 SSD； 然后 附加数据库； 这样看来， 索引是 应该重建的吧 ？

[dbLenis]

先找到 SSD 上的表，任选其一，查看其索引对应的数据页(DBCC IND) 是否已经是 SSD 文件路径。 按照你的操作，索引不需要重建。因为你把索引一起迁移了。 可能的情况是， 重建索引之后， SQL 性能未必马上就能提升。

[薛定谔的DBA]

是同一台云服务器上，将其中的一个数据文件移到ssd盘吧？另一个数据文件和日志文件还是在普通云盘？ 先从大到小的范围进行排查， 内存、cpu、IO 都有什么变化。 数据库内的堵塞情况、找出几个语句执行看看是否正常。

[samyp1234]

引用 15 楼 kk185800961 的回复:

是同一台云服务器上，将其中的一个数据文件移到ssd盘吧？另一个数据文件和日志文件还是在普通云盘？ 先从大到小的范围进行排查， 内存、cpu、IO 都有什么变化。 数据库内的堵塞情况、找出几个语句执行看看是否正常。

是这种情况：是同一台云服务器上，将其中的一个数据文件移到ssd盘吧，另一个数据文件和日志文件还是在普通云盘。

[dbLenis]

引用 14 楼 samyp1234 的回复:

[quote=引用 13 楼 wujiandao 的回复:]
先找到 SSD 上的表，任选其一，查看其索引对应的数据页(DBCC IND) 是否已经是 SSD 文件路径。
按照你的操作，索引不需要重建。因为你把索引一起迁移了。
可能的情况是， 重建索引之后， SQL 性能未必马上就能提升。

‘’

我没有把索引一起迁移啊，因为我本来有2个数据文件，我只通过分离、附加的方式移动了其中的1个数据文件到SSD上；
这样的操作后，索引不需要重建吗 ？


[/quote]

你可以问你自己这样几个问题：

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 中存储的数据。
所以指针存储的是表记录的相对地址。

综上，一切都已经在数据文件中用逻辑方式确定了表及索引的存储地址，数据文件再怎么迁移，表及索引就像是你的纹身一样，跟着你。并不是你走到哪里都要重新纹一遍。

[删库到跑路]

文件更换了位置，性能可能会变得， 数据库不只是依靠自身的优化提神性能，也需要主机的配置 如果你更换的主机配置比原来的差，那么性能肯定会下降，分离附加，备份还原是不要重建索引

[samyp1234]

引用 13 楼 wujiandao 的回复:

先找到 SSD 上的表，任选其一，查看其索引对应的数据页(DBCC IND) 是否已经是 SSD 文件路径。 按照你的操作，索引不需要重建。因为你把索引一起迁移了。 可能的情况是， 重建索引之后， SQL 性能未必马上就能提升。

‘’ 我没有把索引一起迁移啊，因为我本来有2个数据文件，我只通过分离、附加的方式移动了其中的1个数据文件到SSD上； 这样的操作后，索引不需要重建吗 ？

[line_us]

好深奥的，路过看看先

[qq_37401311]

java怎么读取sql资源

[zjcxc]

先自己简单的测试一下吧，建两个库，一个只在云盘，一个只在 SSD
在两个库中做同样的测试
如果仍然发现 SSD 上的更慢，那么显然跟你的移动没有关系，是 SSD 方面的问题

如果 SSD 明显不慢，那么是你移动文件的问题，根据你的描述，你做的是移动，不是添加，那么是否可以理解为文件脱机后复制过去再联机的方式？在这种方式下，表+索引的组织方式不变，也就是并没有改变其在数据文件中的逻辑对应，改变的仅仅是物理存储位置，所以跟逻辑结构没有什么关系，我觉得可能性最大的是 SSD + 云盘混用，我不清楚你所谓的两个数据文件，是同一文件组，还是不同的文件组，如果是不同的文件组，那么是两个逻辑存储，也就是可以它们的读写是不关联的；而如果是同一个文件组，那么它们的读写是关联的，简单地说，表的存储只能指定文件组，不能指定文件，所以是同一文件组的话，你往表中写数据的时候，实际上是组中的文件都可能涉及写入的，这个时候两种存储设备之间的协调可能会导致一睦问题吧，毕竟你的两种设备理论上性能差异是比较大的，而且云盘通常是机械的吧？这个是要求顺序读写，而SSD则是随机的

[xuxiangege]

看你们在这说了 一大堆， 简单看下 查询的时候 是不是使用了一样的查询计划 ，如果是 等着吧，数据还在硬盘上 当然比你之前的慢。 什么 顺序读 随机读 都是看 文件本身的状况，索引碎片少 ，文件组织紧密 合理 一般都是 顺序读的。。别听这些人 瞎说。