SQL大数据优化下

1、审核大数据表的索引、存储过程、sql语句

     此方式是基础性的，重点通过数据表的逻辑分析和性能工具，执行计划查看是否缺少索引或sql语句书写的消耗性能进行优化，对于存在IO瓶颈的问题，可以尝试   使用翻页存储过程等方法，在底层上实现数据优化，之前也有文章说明了一些常用sql语句的性能对比，尽量修改之。

2、数据库日志文件压缩

    一个数据库包含Data文件和Log文件，对于一个大库，细心的你有时候会发现，日志文件如此之大，我使用的日志文件达到18G，日志文件对于分析数据库操作和例外情况下的数据恢复具有关键的作用，但这么的文件最好的方法就是定期备份，然后清空日志。在不影响数据库正常使用的情况下清空日志方法如下：

 a、执行如下语句：DUMP TRANSACTION DBName WITH NOLOG

 b、右键数据库名，选择：任务-->收缩--->文件，选择文件类型：日志，在收缩操作中，选择释放...,输入0，点击确定，则日志文件则被清空

    日志文件的太大一方面会大量占用文件磁盘，另外在对应的数据操作中，频繁的日志读写也一定程度上影响磁道的检索速度，影响性能。

注：如果需要备份日志的，实现应该先备份日志。

3、查看数据库对应元数据，分析索引碎片，整理索引碎片

    索引就是一个字典目录，保存着快捷访问记录的方式。但由于数据是动态变化的，不停的修改，删除，插入可能导致索引动态变化，日积月累就会存在索引碎片，这将导致系统在执行对应查询检索过程中，要执行一些额外的操作能定位到指定的索引，最好的方法就是一次性定位到索引，因此动态的整理索引，清楚索引碎片也很关键。下文代码系统自动清理当前库中，索引碎片大于12%的索引，并重建对应索引。

先看如何查看索引碎片：

     DBCC SHOWCONTIG

 

接下来自动清理索引碎片大于12%的索引并自动重建，12%可以自设定。

use DBName --对指定的整个数据库所有表进行重新组织索引
set nocount on--使用游标重新组织指定库中的索引,消除索引碎片--R_T层游标取出当前数据库所有表
declare R_T CURSOR
for select name from sys.tables
declare @T varchar(50)
open r_t
fetch NEXT from r_t into @t
 while @@fetch_status=0
 begin--R_index游标判断指定表索引碎片情况并优化
 declare R_Index CURSOR
 for select t.name,i.name,s.avg_fragmentation_in_percent 
 from sys.tables t   join sys.indexes i on i.object_id=t.object_id  
  join sys.dm_db_index_physical_stats(db_id(),object_id(@T),null,null,'limited') s  
    on s.object_id=i.object_id and s.index_id=i.index_id 
    declare @TName varchar(50),@IName varchar(100),@avg int,@str varchar(500)
    open r_index
    fetch next 
    from r_index into @TName,@Iname,@avg
    while @@fetch_status=0 begin  if @avg>=12  --如果碎片大于12,重建索引
      begin   set @str='alter index '+rtrim(@Iname)+' on dbo.'+rtrim(@tname)+' rebuild'  end  else   --如果碎片小于30,重新组织索引  
      begin   set @STR='alter index '+rtrim(@Iname)+' on dbo.'+rtrim(@tname)+' reorganize'  end  print @str  exec (@str)  --执行 
       fetch next from r_index into @TName,@Iname,@avg
       end--结束r_index游标
       close r_index
       deallocate r_index
       fetch next from r_t into @t
       end--结束R_T游标
       close r_t
       deallocate r_t
       set nocount off

附带的清理统计信息，适情况也可以清理一下，如果统计信息有效则清理会自动跳过

USE DBName
GO
 EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?',' ',90)"
 GO
 EXEC sp_updatestats
 Go

注:上文中的Use DBName，是使用对应的数据库名，使用时注意修改。
完成如上操作后可以重启数据库服务，看看效果，以上优化可以一定程度上提升性能。

4、建立分区表和分区库

    这个在强度和力度上都是效果显著的，之前老觉得建立分区表是不是需要很多复杂的操作，需要建立对程序业务熟悉等等，其实sql已经给出了完善的方案。

磁盘分区表就是实现表的水平分区，将一个数据分布在多个数据实体文件中，即.mdf 文件中，考虑到性能每个数据文件最好在不同的物理磁盘上。具体操作步骤罗列如下：

a、创建分区函数，主要使用CREATE PARTITION FUNCTION XXX(parms)

b、查看分区函数是否创建成功

　　SELECT * FROM sys.partition_functions

c、创建分区策略，CREATE PARTITION SCHEME SchemaForPartition

d、查看分区策略是否创建成功

　　SELECT * FROM sys.partition_schemes

e、把分区策略和函数定义到数据表上，实现表关联。

d、测试一下分区情况和要查询数据所在的分区

原文转自：http://www.cnblogs.com/Wendy_Yu/archive/2012/06/17