【基本优化实践】【1.1】IO优化——把文件迁移到不同物理磁盘

【1】概念

　　把不同数据文件移动到不同的物理磁盘，无疑是一个提高IO的有效办法

　　在资源可以的情况下，尽量把 temp 、数据库的主数据文件（mdf）、数据库的从数据数据（ndf）、数据库的事务日志文件（ldf）存放在不同磁盘

 　　如果碍于资源有限，尽可能的把数据文件存放在不同磁盘（即mdf与ndf不同盘，或者说是同文件组中的不同文件不同盘），没有ndf的情况下mdf与ldf不同盘

【2】如何只管查看物理磁盘与逻辑盘符的关系？

右击我的电脑-》管理-》磁盘管理，如下图，很明显C/E是同一个物理盘，而D盘是另外一个物理盘。

　　

 

简单案例演示：当目前只有MDF与LDF，且在同一个逻辑盘符时

--0、目的：把数据库文件分部在不同物理磁盘
--1、前提：知道除系统盘外其他逻辑盘符均为不同物理硬盘
--2、问题：（1）权限    （2）没有通用化

-- 允许配置高级选项  
EXEC  sp_configure 'show advanced options',1;
EXEC sp_configure 'xp_cmdshell',1;
RECONFIGURE WITH OVERRIDE;


use master
go
--查看逻辑盘符数量
DECLARE @temp_disk_unused TABLE (
    diskNum varchar(2),
    unUsed_space bigint
    )
insert into @temp_disk_unused
exec  xp_fixeddrives


--如果逻辑盘符数量》3（表明至少有2个物理盘，我这里只用D,E），且数据库的（单数据文件）数据文件与日志文件均在同一个磁盘下
if exists( select 1 from @temp_disk_unused having count(*)>=3)
    and
    exists(
        select 1 where
        (select left(filename,1) from db_tank_1..sysfiles where fileid=1)=
        (select left(filename,1) from db_tank_1..sysfiles where fileid=2)
    )
begin
    
    declare @db_name varchar(100),@logic_name varchar(100)
    declare @new_path varchar(500),@filename varchar(100),@old_path varchar(500),@new_dir varchar(100)
    declare @old_disk_num varchar(2),@new_disk_num varchar(2)
    declare @sql varchar(4000),@sql1 varchar(4000)
--判断当前是在D盘还是E盘，如果是D则把日志文件迁移到E，E迁移到D    
    select @old_disk_num=left(filename,1) from db_tank_1..sysfiles where fileid=1
    if @old_disk_num='D' 
        set @new_disk_num='E'
    else
        set @new_disk_num='D'

--找出旧文件全路径与新位置路径，方便迁移文件过去        
    select @old_path=filename,@logic_name=name from db_tank_1..sysfiles where fileid=2
    set @filename=right(@old_path,charindex('\',reverse(@old_path))-1)
    set @new_dir=stuff(left(@old_path,len(@old_path)-len(@filename)),1,1,@new_disk_num)
    set @new_path=stuff(@old_path,1,1,@new_disk_num)
    print @filename+','+@new_dir+','+@new_path+','+@logic_name
--------------------------------------------------------    
 --建立一个虚拟表 用来判断文件夹是否存在
    DECLARE @TEMP TABLE(A INT,B INT,C INT)
    insert into @TEMP
    EXEC [MASTER]..XP_FILEEXIST @new_dir
    IF NOT EXISTS(SELECT 1 FROM @TEMP WHERE B=1)
    BEGIN
--不存在则创建
        SET @SQL='EXEC XP_CMDSHELL ''MKDIR '+@new_dir+''''
        exec(@sql)
        print @sql
    END
--------------------------------------------------------
--修改日志文件（1）修改逻辑文件名对应的物理路径    （2）脱机该数据库    （3）迁移    （4）重新联机该数据库
    set @sql1='alter database db_tank_1 modify file (name = '+@logic_name+',filename='''+@new_path+''');'
    set @sql1=@sql1+'ALTER DATABASE db_tank_1 SET OFFLINE WITH ROLLBACK IMMEDIATE;'
    print @sql1
    exec(@sql1)
    SET @SQL='EXEC XP_CMDSHELL ''move '+@old_path+' '+@new_path+''''
    print @sql
    exec(@sql)    
    set @sql1='alter database db_tank_1 set online;'
    print @sql1
    exec(@sql1)

end
--核验
 select * from db_tank_1..sysfiles 
 if exists(
        select 1 where
        (select left(filename,1) from db_tank_1..sysfiles where fileid=1)=
        (select left(filename,1) from db_tank_1..sysfiles where fileid=2)
    )
    print 'have problem,please check the info by sysfiles..'
else
    print 'operator success!'
        

--------------------------------------------------------
-- 允许配置高级选项  

EXEC sp_configure 'xp_cmdshell',0;
EXEC  sp_configure 'show advanced options',1;

RECONFIGURE WITH OVERRIDE;