找寻关于sqlserver2005建立分区视图的寻些这方面的文档和一些实践经验,up有分 200jiangchuandong]

发布时间:2014-1-1 0:09:53编辑:www.fx114.net 分享查询网我要评论
本篇文章主要介绍了"找寻关于sqlserver2005建立分区视图的寻些这方面的文档和一些实践经验,up有分 200jiangchuandong]",主要涉及到找寻关于sqlserver2005建立分区视图的寻些这方面的文档和一些实践经验,up有分 200jiangchuandong]方面的内容,对于找寻关于sqlserver2005建立分区视图的寻些这方面的文档和一些实践经验,up有分 200jiangchuandong]感兴趣的同学可以参考一下。

因为公司需要使用sqlserver2005的分区视图,我准备写公司的解决方案,希望能找寻些这方面的文档和一些实践经验,希望各位朋友能提供些建议和连接参考,谢谢; 附我准备写的文档概要 一 背景    1 分析公司的业务增长和数据库变化    2 计算数据增长量,和带来的变化    3 sql7.0/2000/2005 分区视图的变化及DB2和Oracle相对应的概念和比较 二 实施优点    1 解决查询带来的开销    2 方便维护数据      3 方便维护索引    4 方便于维护Check约束     5 对现有的开发模式影响很小    6 减少聚集数据的堵塞 三 参考技术文档    1 微软msdn关于2005建立方案     track: http://msdn2.microsoft.com/zh-cn/library/ms181036.aspx    2 chinabye  SQL Server 2005 中的分区表和索引     track http://server.chinabyte.com/496/2588996.shtml    3 sqlserver2005 中文帮助  四 实施方案    1 准备基础环境,包括操作系统,数据库,硬件,网络的准备    2 确定实施表和影响的业务功能模组    3 具体实施      a 数据的备份和准备      b 技术支持准备      c 考虑硬件优化和做负载平衡      d 记录创建前的查询开销      e 编写check函数和实施建立分区表      f 准备维护方案 五 测试方法    1 测试select ,update,insert,delete 直接更新数据     2 测试影响的业务功能模组是否受影响    3 记录新模组带来的  六 可能出现的问题       1 没有正确配置参数,引起视图无法更新     2 开放方面接口的变化    3 维护方案编写后由于环境的变化不能得到正常实施

学习ing

似乎2005更倾向于使用分区表, 而不是分区视图吧?

msdn SQL Server 2005 中的分区表和索引 http://www.microsoft.com/china/msdn/library/data/sqlserver/sql2k5partition.mspx?mfr=true WebCasts SQL Server2005中的表分区功能和索引 http://www.microsoft.com/china/msdn/events/webcasts/shared/webcast/episode.aspx?newsID=1242313

2005的不懂^^

是的,邹老大,应该是推荐用分区表了

orcale里有同样的概念,不过我很奇怪的是 在chinabyte里的 http://server.chinabyte.com/496/2588996.shtml 却推荐用分区视图,真是让我郁闷了

SQL2005才推出的分区表概念。在你看得文档中使用check和分区视图是当时没有办法的选择。check+分区视图实施起来比较麻烦,性能方面的提升并不是特别明显,还是一种人为拆分的方案。SQL2005分区表不需要使用check。 你可以用Oracle的分区表概念来理解SQL2005的分区表。而且他们的操作也几乎相同,不同的只是语法和内部实现而已。我自己总结了一个分区表操作的文档,如果你想要可以留下你的邮箱。其他的资料你可以直接上微软的网站上查阅,非常全面。

http://sqlserver2005.itpub.net/post/22359/228863 --创建分区表之前,请在新建数据前添加数据库文件和文件组(文件组数>=分区数) --创建分区函数(有三个范围会产生四个分区) CREATE PARTITION FUNCTION FiveYearDateRangePFN(datetime) AS RANGE LEFT FOR VALUES ( '20061031 23:59:59.997', '20061130 23:59:59.997', '20061231 23:59:59.997') --删除PARTITION FUNCTION --DROP PARTITION FUNCTION FiveYearDateRangePFN --分区映射到文件组的方案('200610'代表文件组,文件组的个数不得少于分区的个数,文件组包括数据文件) CREATE PARTITION SCHEME [FiveYearDateRangePScheme] AS PARTITION FiveYearDateRangePFN TO ('200610','200611','200612','200701') --删除SCHEME --DROP PARTITION SCHEME [FiveYearDateRangePScheme] --创建分区表 CREATE TABLE PARTITIONTABLE (P_NAME VARCHAR(10),BIRTHDAY DATETIME) ON FiveYearDateRangePScheme(BIRTHDAY) --插入测试数据 INSERt INTO PARTITIONTABLE values ('a','2006-5-1') INSERt INTO PARTITIONTABLE values ('b','2006-8-1') INSERt INTO PARTITIONTABLE values ('c','2006-10-1') INSERt INTO PARTITIONTABLE values ('d','2006-11-1') INSERt INTO PARTITIONTABLE values ('e','2006-12-1') INSERt INTO PARTITIONTABLE values ('f','2007-5-1') --查看数据是否写到相应的分区 select $partition.FiveYearDateRangePFN(BIRTHDAY) as PARTITIONT_ID,BIRTHDAY,* from PARTITIONTABLE --创建分区索引 create index PARTITION_INDEX ON PARTITIONTABLE(BIRTHDAY) ON FiveYearDateRangePScheme(BIRTHDAY)

非常感谢楼上的朋友,我会总结下,把自己做的结果告诉给大家

关注下

五 测试方法   可以做以下对比  使用分区表后对Disk I/O的影响 通过windos性能计数器 做I/O负载分析 计数器对象 计数器 Physical Disk %Disk Time                    Avg.Disk Queue Length                    Avg.Disk sec/Read                    Avg.Disk sec/Write                    Avg.Disk Reads/sec                    Avg.Disk Writes/sec 动态管理视图 Sys.dm_os_wait_stats Sys.dm_io_pending_io_requests Sys.dm_io_virtual_file_stats Sys.dm_exec_query_stats

有点疑问的是,如果按以前的思路,分月,分日建表的话,那不是每个月和每日都需要增加,修改函数和架构吗?

另外请教大家,能不能用先建立好的表,直接改为分区表?不然的话创建后还需要导入数据?

修改了下,方案编写如下: 一 背景    1 分析公司的业务增长和数据库变化    2 计算数据增长量,和带来的变化    3 名词解释      a sqlserver数据分区历史     b 对齐数据     c 范围分区     d 定义分区键     e 索引分区     f 分区的特殊情况:拆分、合并和移动 二 实施优点 1 简化了需要进行分区以改善性能或可管理性的大型表的设计和实现,方便于维护Check约束 和字段维护。    2 将数据加载到现有分区表的新分区中时,最大程度地减少了对其他分区中的数据访问的影响。    3 将数据加载到现有分区表的新分区中时,性能相当于将同样的数据加载到新的空表中。    4 在存档和/或删除分区表的一个分区时,最大程度地减少了对表中其他分区的访问的影响。    5 允许通过将分区移入和移出分区表来维护分区。    6 提供了更好的伸缩性和并行性,可以对多个相关表执行大量操作。    7 改善了所有分区的性能。    8 缩短了查询优化时间,因为不需要单独优化每个分区   9 方便维护索引 10 对现有的开发模式影响很小(不需要以前那样建立视图,程序也能正常使用)     11 减少聚集数据的堵塞 三 参考技术文档    1 微软msdn      http://www.microsoft.com/china/msdn/library/data/sqlserver/sql2k5partition.mspx?mfr=true      2 WebCasts      SQL Server2005中的表分区功能和索引      http://www.microsoft.com/china/msdn/events/webcasts/shared/webcast/episode.aspx?newsID=1242313    3 sqlserver2005 中文帮助  四 实施方案    1 准备基础环境,包括操作系统,数据库,硬件,网络的准备    2 确定实施表和影响的业务功能模组    3 具体实施      a 数据的备份和准备      b 技术支持准备      c 考虑增对分区表的硬件优化和性能参数的修改      d 记录创建前的查询开销      e 创建分区函数,创建分区架构,创建分区表,导入数据,建立索引和约束      f 执行完整数据库备份或差异备份      g 维护方案 五 测试方法    1 测试select ,update,insert,delete 直接更新数据     2 测试影响的业务功能模组是否受影响    3 记录新方案带来的影响      a:直接体现在业务模组上的影响      b:记录 对Disk I/O的影响,I/O负载分析   六 可能出现的问题       1 如果以DataTime类型做函数区分,可能带来3微秒的误差,解决方法见msdn    2 开发方面,创建大表加入了新流程    3 现在没有确定实施使用的范围

mark下,学习

mark!

學習一下.

我是来up的。

mark

学习

学习了

mark,学习

mark

mark,学习

又没我的事了

重写了下 一、 概要 1.1 实现目标 SQL Server 2005 中基于表的分区功能简化了分区表的创建和维护过程,给数据库提供了灵活性和更好的性能;如果能良好应用将改善数据库大型表性能,设计人员管理性的的设计和实现;并方便于DBA相关维护工作 1.2 参考资料 1 微软msdn http://www.microsoft.com/china/msdn/library/data/sqlserver/sql2k5partition.mspx?mfr=true   2 WebCasts SQL Server2005中的表分区功能和索引      http://www.microsoft.com/china/msdn/events/webcasts/shared/webcast/episode.aspx?newsID=1242313 3 sqlserver2005 中文帮助 ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.zh-CHS/tsqlref9/html/f1745145-182d-4301-a334-18f799d361d1.htm   二、 需求描述 2.1主要参与者 2.1.1数据库管理人员DBA 2.1.2 负责相关模块的SA 2.2系统相关人员及其兴趣 2.2.1 User ,希望系统能快速准确的新增,修改,删除,查询,到业务数据;同時因為業務需要和審計需要更多的數據 2.2.2 PM  使管理设计维护功能进一步分开,让数据库维护部分交给DBA进行性能调优;同时系统能更好的相应用户操作; 2.2.3 SA  简化对数据库表的设计,并把可以把数据库设计,可把优化的工作交给DBA处理 2.2.3 Develper 简化开发工作 2.2.4 DBA减少备份,聚合数据带来的用户响应等待;充分利用多硬盘,多CPU能并发处理数据相关工作;利用分區,减能少磁盘I/O读取;方便实现负载平衡,均衡I/O;建立起良好的数据库架构工作,方便数据库服务器性扩展,減少約束和表的結構的維護; 2.3前置条件 2.3.1了解基础环境,包括数据库,硬件的准备和基本配置情况 数据库是SQLServer2005 SQL Server Enterprise的版本 硬件  系统是多硬盘,多CPU为佳 2.3.2现在业务中存在需要性能优化和管理方便的地方 可以优化的有 A. 如何處理 transaction 數據多的 Table 1. 按定時抄去 _Arc 的 database,只留有用(e.g. report要用) 的 data 在 現行的DB  2. 按年份月份 開 Table 上面的方法存在的问题有 每年需要对该表进行定时清理数据,在清理数据的时候会使得该表的索引变得和/或索引变得支离破碎和/或被锁定,同时处理这些数据的时候将会使系统阻塞的情况,容易使得正常的操作无法正常运行,处理后还需要重修复索引;整个工作比较毫时和影响业务模组的正常运行(虽然可以把这个工作放在周末进行,但是随着业务数据的增加将会变得庞大) B 查询速度由于Data较多的原因,查询和修改数据比较慢,一次查詢會掃描整個表的數據 2.3.3 DBA需要充分了解分区技术每一个关键点; 包括创建,维护方法 2.3.4用SQL PROFILER找出性能存在的瓶颈的SQL 2.4成功后的保证 2.4.1建立分区函数,分区架构,和分区表; 2.4.2 DBA应知道如何维护其架构; 2.4.3 SA了解其技术; 2.5基本流程 [描述能够满足项目相关人员兴趣的典型的成功路径] 2.5.1确定系统存在的瓶颈问题 2.5.2 确定需要分区的表 2.5.3确定分区键和分区数目; 2.5.4建立分区函数,分区架构,分区表; 2.5.5维护分区函数,架构, 分区表 2.5.6在删除数据和备份中使用分区表 2.6扩展流程(替代流程) 扩展流程主要在,分区表设置前后的对比 2.6.1 查询成本的比例变化 2.6.2 windows性能检视器看看对磁盘i/o,和cpu内存变化(能在测试服务器里测试需要压力测试才能查看结果) 2.7特殊流程         2.8技术与数据的变化列表 2.9发生频率 经常 2.10待解决的问题. 2.10.1建立完分区表后,建立不指定分区的聚集索引,可能会导致分区表变成非分区表 2.10.2 如果以DataTime类型做函数区分,可能带来3微秒的误差,参考msdn提出 http://www.microsoft.com/china/msdn/library/data/sqlserver/sql2k5partition.mspx?mfr=true 必须更改日期范围。因为您要处理的是 datetime 数据,而在时间的存储方式方面又存在舍入问题,所以必须能够通过编程方式确定正确的毫秒值。要确定月末最后的 datetime 值,最容易的方法是将正在处理的月份加上 1 个月,然后再减去 2 或 3 毫秒。不能只减去 1 毫秒,因为 59.999 会上舍入为 .000,即下个月的第一天。可以减去 2 或 3 毫秒,因为 2 毫秒将向下舍入为 .997,而 3 毫秒等于 .997;.997 是可以存储的有效值。这样即可确定 datetime 范围的正确结束值: 三、 系统设计 3.1系统流程设计 建立分区函数,分区架构,分区表,维护分区表结构,数据备份SQL脚本 3.2数据库设计 先处理StLotLdg表 3.3模块功能详细设计 3.3.1建立多个文件组 该步骤是创建数据库文件分布到多个磁盘中,以获取更好的性能(也可以创建在一个磁盘中) 1 创建文件组 ALTER DATABASE DC_CECCS ADD FILEGROUP [DC_CECCS_FG1] GO ALTER DATABASE DC_CECCS ADD FILEGROUP [DC_CECCS_FG2] GO …. 2 把文件组加入到驱动器中 INSERT dbo.FilegroupInfo VALUES (1, 1, N'C:\SalesDB') INSERT dbo.FilegroupInfo VALUES (2, 2, N'D:\SalesDB') ….. 3查看分组信息 创建函数BaseDB..FnPartitionInfo或sql 4 更改文件组语句 ALTER DATABASE Dc_ceccs ADD FILE

5 查看文件组大小 exec sp_helpfile 3.3.2 创建分区表函数,架构,分区表,索引 1创建分区表函数 CREATE PARTITION FUNCTION RangeByMonth (datetime)       注:这里有 RANGE 和 Left 的方式  更改分区函数为 ALTER PARTITION FUNCTION 2创建分区表架构     CREATE PARTITION SCHEME [SchemeByMonth]     注:可更改分区架构  更改分区架构方式为:  ALTER PARTITION SCHEME 3 在表中使用分区架构 在sqlserver2000 里需要手工把数据分别插入不用时间段的表 在sql 2005里 只要把数据从原表一次插入分区表就可以了,系统自动根据分区函数分别插入数据 (暂不支持直接在原表上创建分区函数) 那么创建步骤如下: A先建立一个备份表 创建新表上使用分区表 CREATE TABLE dc_ceccs.[dbo].[ StLotLdgBak]  (….) on RangeByMonth B 再把旧表数据插入至新表中 use dc_ceccs SELECT *  INTO StLotLdgbak FROM StLotLdg C 删除旧表 Drop Table StLotLdg D 改新表的名称为旧表 sp_rename 'StLotLdgbak1' , 'StLotLdg' 4 查看分区表信息 SELECT $partition.TwoYearDateRangePFN(o.date)  AS [Partition Number] , min(o. date) AS [Min Order Date] , max(o. date) AS [Max Order Date] , count(*) AS [Rows In Partition] FROM dbo.StLotLdg AS o GROUP BY $partition.TwoYearDateRangePFN(o. date) ORDER BY [Partition Number] GO 5 添加索引 ALTER TABLE StLotLdg ADD CONSTRAINT StLotLdgPK PRIMARY KEY CLUSTERED (Date, nvrDocno) ON SchemeByMonth (Date) GO 6 使用SQL Server 为分区表提供的各种连接策略的查询 SELECT o. nvrDocno, o.Date FROM dbo.StLotLdg AS o INNER JOIN dbo.stocklot AS od ON o.lot_no = od. lot_no WHERE o. Date >= '20050701'  AND o. Date <= '20040930 11:59:59.997' 注,该查询已经使用到了分区表,不用做整个表的扫描 7 备份数据操作 A備份 exec xp_cmdshell 'bcp "SELECT * FROM StLotLdg WHERE date < ''2006/02/01'' AND date > ''2001/01/01'' "  queryout "StLotLdg.txt" -T -c"' 或 备份至本机 或备份至其他表 Create table StLotLdg200601 …  on [DC_CECCS_FG1] 在同一个分区里执行插入数据  INSERT into StLotLdg200601 SELECT  * from StLotLdg where date =  < '2006/02/01' AND date > '2001/01/01') 插入后建索引 ALTER TABLE [StLotLdg200601] ADD CONSTRAINT StLotLdg200601PK  PRIMARY KEY CLUSTERED (Date, nvrDocno) ON [DC_CECCS_FG1] GO 此时已经使用到分区表,查询只会进行在相应的历史分区进行查询,对现在进行的分区数据影响较小 B删除原表的数据 truncate table 你的表 或 建立一个作业指定操作时间在晚上进行  while exists (select 1 from StLotLdg where date =  < '2006/02/01' AND date > '2001/01/01') begin     set rowcount 10000     delete StLotLdg where here date =  < '2006/02/01' AND date > '2001/01/01'    set rowcount 0 end 注 时间可以取 getdate,判断时间为 datediff(Year,date,getdate())<=1 C恢复 exec xp_cmdshell ‘bcp dbname.dbo.table_name in d:\ StLotLdg.txt -c -q -S -Usa –P’ 或 insert into StLotLdg select * from StLotLdg200601 或 ALTER TABLE StLotLdg SWITCH PARTITION 1 TO OrdersOctober2002 GO 8修改分区表 A 添加一个文件组到数据库 Use master GO ALTER DATABASE Dc_ceccs ADD FILEGROUP DC_CECCS_FG13 GO ALTER DATABASE Dc_ceccs ADD FILE  ( NAME = N' DC_CECCS_FG13',FileName = 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Dc_ceccs13.mdf' ) TO FILEGROUP DC_CECCS_FG13 GO Use Dc_ceccs GO B 修改分区Scheme ALTER PARTITION SCHEME RangeByMonthScheme NEXT USED DC_CECCS_FG13; GO C 修改分区函数 ALTER PARTITION FUNCTION RangeByMonth() SPLIT RANGE ('T/N'); GO

歲月,你夠猛..

还没用过2005

学习

补充能量中.....

学习

学习

学习+接分+收藏+UP

up

写得不错。既然征求意见,给你提点建议: /**************************** 4 查看分区表信息 SELECT $partition.TwoYearDateRangePFN(o.date)  AS [Partition Number] , min(o. date) AS [Min Order Date] , max(o. date) AS [Max Order Date] , count(*) AS [Rows In Partition] FROM dbo.StLotLdg AS o GROUP BY $partition.TwoYearDateRangePFN(o. date) ORDER BY [Partition Number] GO **************************/ 这部分叫“查看分区表信息”有点不妥。这个应该叫作“计算表分区后记录分布情况”。因为你就算对一个未分区的表作这样的计算,也可以得到相应的信息。而且你的文档侧重于分区表的操作,没有对分区表实际存储情况的监控。这样分区表的存储情况就容易成为你优化时的一个盲点。建议使用sys.dm_db_partition_stats 作为察看分区情况的主要依据。

谢谢,版主的指正,我也是看msdn翻译过来的,这点翻译确实不是很准确

up

可以看一下WebCasts的教程,如果需要把邮件告诉我,我发给你。

up

继续帮lz顶哈

楼主好精神,值得学习,收了

没研究过 学习!

收藏了,咱数据库板块好多好心人都喜欢把总结的东西给大家分享,实在是太可爱了,对我们帮助很大~

mark

mk

呵呵  ,完成了。 感觉不错。

mark

MARK

不断的完善了,看着你的成长.大家很高兴.... 学习.

我收藏了.

mark

UP


上一篇:怎样让系统自动执行储存过程! 0
下一篇:用vs2005写了个网站,怎么发布?可以给别人用但是不让人看到代码吗??? 20CSDN]

相关文章

相关评论

本站评论功能暂时取消,后续此功能例行通知。

一、不得利用本站危害国家安全、泄露国家秘密,不得侵犯国家社会集体的和公民的合法权益,不得利用本站制作、复制和传播不法有害信息!

二、互相尊重,对自己的言论和行为负责。

好贷网好贷款