搜索
1管理数据库和文件1.1选择哪些组件安装组件包括：①SQLServerDatabaseEngine②AnalysisServices③ReportingServices----用在传统的报表中（EXCEL）；SQLServer2008中，装这个组件，不用装IIS，以前的版本需要装IIS④IntegrationServices⑤NotificationServices⑥ManagementTools其中，②③④是给商业智能（BI）来用的，以写为主注：1）装单机版时，要装④IntegrationServices，如果不装，不能做维护计划，不能导到EXCEL中2）SQLServer2005打上SP2补丁，可以不装④IntegrationServices1.2数据如何存储1.2.1日志文件和数据文件1.2.1.1理论日志文件：记录对表（库等）操作的过程。数据文件：记录对表操作的结果。日志文件的作用：恢复数据文件。前滚：当往表中写数据时，先写入日志文件，再写入数据文件。当数据写入失败后，则删除日志文件中的数据，并不写入数据文件。后滚：当数据写入成功，则将日志文件中的数据写入数据文件。1.2.1.2例子例要在表中写入如下数据idname1a2b1）往库里写（1，a）的过程是什么？过程如下：(1,a)先写内存，满足如下条件：①内存不足②事务提交----事务结束才写日志文件，满足如下条件：①发生检查点②事务提交----事务结束存放数据的空间不够才写数据文件。2）机器关机后，如果日志文件中有（2，b）,数据文件中没有（2，b），开机后，（2，b）怎么办？如果（2，b）没有提交，做前滚----将（2，b）从日志文件中的删除，并不写入数据文件如果（2，b）提交了，做后滚----将（2，b）写入数据文件1.2.2事务日志1)最容易出问题的是哪几项？锁，I/O，磁盘，内存2）案例：执行10万多条记录，要花费几分钟的时间，跟踪后发现花几分钟的原因是一天记录循环一次，循环中有事务，事务都要写日志，写日志要进行I/O操作，使得I/O操作太频繁了。如何解决？建议，声明一个变量，变量的值除以5000余0的提交一次（10万多条数据一起提交，如中途关机，数据会丢失。这样做虽然也会丢失数据，但丢失的数据少）3）如何看I/O是否出问题？步骤：点击开始菜单----AdministrativeTools----performance，如下图：建议：写代码时，一定不要在循环中加事务；在循环处理中不进行次次提交，反之，则会频繁的写入日志文件，会有大量io等待。；；弹出如下框其中，PysicalDisk为磁盘指标，当指标值“avg.diskqueuelength（磁盘等待队列）”的值大于磁盘数量*2时，则会出问题。读队列的值+写队列的值=磁盘队列的值磁盘等待队列比较高时，要关注（一般读容易出问题），看到底是读出问题还是写出问题的方法：点“+”号，Preformanceobject：选“LogicalDisk”，selectcountersfromlist：“%DiskTime”—不要看，值一般不准确，看“avg.diskwritequeuelength”和““avg.diskReadqueuelength””4）读出问题的解决方法？①消除数据库中的碎片（一般一个月左右，DBA要主动消除碎片，碎片主要指页中的碎片）②调数据库里的索引③改一些命令1.2.3数据库恢复模型数据库恢复模型包括Full和Simple两个选项（简单模型和完整模型），恢复模型可在以下位置进行选择：右键单击数据库—单击属性—单击选项—可以看到恢复模型完整模型：可以回到故障点；数据会丢的少一些；该模型用的多一些。简单模型：数据会丢的多一些。简单模型表示：数据写入数据文件后，日志文件中该数据占的空间可以被重用；只能回到上次做备份的数据状态，不能回到故障点。例子：一个数据库，其它表的数据都是只读，只有一个文件频繁更新，这个数据库应建立什么模型？建议方法：更新特别频繁的，特别容易产生大量日志的，把这样的表放入单独的新数据库中，新数据库设置成完整模型，该数据库设置成简单模型1.2.4数据页（page）1.2.4.1理论1）页是数据处理的最小单位,读/写数据对应的最小单位是整个页。2）sqlserver中页是8K，Oracle中可以改页的大小，比如改为2K，4K。3）以读为主，希望页大一点儿----加快数据处理的时间。4）以页为单位操作，带来的问题是：碎片（页内的碎片----页没有填满,空间又不能重用得,叫做页中的碎片）。5）Sqlserver2005中，一行中，定长数据类型占用空间加起来不能超过8k。1.2.4.2例子例要在表中写入如下数据1）写（1，a）时，需要分配新的空间，需要分配多大的空间？分配8K的空间，即一页的空间，因为页是数据处理的最小单位----（1，a）至少要消耗8K的空间。2）写（2，b）时，怎么存？idname1a2b（1，a）所在的页，如果还有足够的空间，写在（1，a）所在的页。3）如果要显示（1，a），会将什么读入内存？读入内存时，将（1，a）所在的页读入内存中，但是，显示的时候只显示（1，a），不显示其它的数据。1.2.5扩展（Extent）1）扩展：8个页组成一个扩展。2）数据库理论中，为什么要有扩展？表一创建，会有初始空间，以扩展为单位，给表分配空间，以页为单位来使用空间。3）为什么说SQLServer中，扩展有缺陷？连续的8个页为一个扩展，不能控制扩展大小4）有两种扩展类型：单一扩展：一个扩展中，有一个表的数据----查询快混合扩展：一个扩展中，有多个表的数据----节约磁盘空间5）SQLServer中，I/O的最小单位是扩展，不是数据页。6）页和扩展都是数据文件的概念，日志文件没有这些概念。1.3文件组1）注意：Primary文件组，应该只放系统表。2）文件组在企业中，用的不多，是因为历史原因----SqlServer2000中，文件组吧独立。3）Sqlserver2005及其之后版本，建议使用文件组，因为Sqlserver2005及其之后版本，单个文件组出问题，不会影响整个数据库1.3.1文件组的作用①文件组是对表的分类----以便对表进行管理。当数据表比较多，且很多表具有大数据量时，建议使用文件组。②文件组比多个database好（企业多用database，不用文件组），有一个两个原因：场景1：如果建立多个数据库，而多个数据库之间是有复杂依赖关系，问题1：无法跨库进行groupby操作，问题2：一旦有数据库出问题，必须手动迁移数据容灾等。在此场景下，使用文件组是比较方便的。场景2：如下图中，一个表中有大对象，也有其它列（只写第一列和大对象，中间的列省略）时，可采用文件组。因为如果不采用文件组，大对象和其他普通列存放在同一个的物理文件中；如果采用文件组，大对象和其他普通列存放在不同的物理文件中—提高检索速度（至少建立三个文件组，一个放表的，放索引的（此处指的是非聚集索引，聚集索引应与表放在一起），放大对象的，也可以再细分，例如将大对象组再分组）。50M的图片210M的图片1大对象……id50M的图片210M的图片1大对象……id○○10M的图片○○50M的图片…○○○○…10M的图片50M的图片将大对象和其他普通列存放在不同的物理文件中将大对象和其他普通列存放在同一个的物理文件中按小对象索引时，需要移动磁臂（移过大对象），速度慢1.3.2可以在建表时设计文件组与列关联createtablet2(idint,namevarchar(100),notevarchar(max))onsaletextimage_onlob1.3.3修改文件组对于已建好的表，可在表设计界面中，点击f4，在出现的属性框中修改（看红方框的地方），如下图所示。但需要注意以下两个问题：①不要在数据高峰期进行修改。②修改完后，点击上面的“×”（看红圈标注的地方）时会提示错误，这个错误是环境的原因。修改环境的方法是：单击Tools---点击options----单击Designers----在TablesanddatabseDesigners中，把“presentsavingchangesthatrequiretablere-creation”前的“√”去掉。1.3.4扩展文件组的方式右键单击某一个“数据库”---单击“属性”---单击“文件”，则出现下图：按上图方式中，默认（unestrictedgrowth）每次加1M，会造成数据不连贯，若太大，则分配时间加长也会影响性能。建议每次增加200M----600M之间。例：文件每个是10G，每次增长10G，分配磁盘空间时，如果硬件性能不好，慢的要用5----6秒时间，如何解决？注意：1）数据表中的null值，定长类型数据是占用存储空间的，而变长类型（varchar，nvarchar等）是不消耗存储空间的。2）Sqlserver2008中有稀疏列（sparse），用以解决null值。如果本列80%以上都是null值，则建议将此列定义为稀疏列；70%以下都是null值，定义为稀疏列反而会浪费空间。3）定义为稀疏列的方法：createtablet2(idintsparse)；；每个文件5---10G；给文件组加文件，不建议自动增长。1.3.5给文件组加文件给文件组加文件的方法如下图(点击“Add”按钮，可以给文件组加文件，也可以把文件加到一个新的文件组中)：1.3.6预留空间的问题DBA应该注意随时观察分配空间,进行空间预留，不要当文件空间完全用完的时候,再去分配空间。查看空间的使用情况（预留空间）的方法是：右键单击一个“数据库”----左键单击“Reports”----左键单击“StandardReports”----左键单击“DiskUsage”，如下图单击后，出现下图：主要关注“红色椭圆”标注的地方：“1”表示未分配的，“2”表示以分配的。1.4Schemas1.4.1理论1）Schemas的作用：把表分类。分类的目的是方便管理（在Sqlserver中，schemas没太多用处，只有分类的作用）。例如给dbo开头的表（如dbo.test），分配查询权限，给person开头的表分配添加权限。2）Schemas和文件组在分类上的区别：文件组相当于部门,schema相当于owner.3)当表的数量比较大时,才需要利用schema进行分类.4)在Sqlserver2000及以前的版本和Oracle中，用户（user）和schema是绑定的(用户删了，schemas也删了)；Sqlserver2005及以后的版本，用户和schema是分离的。1.4.2创建schemas的语句createschematraingocreatetabletrain.stu(idint)gocreatesynonyms2fortrain.stugo1.4.3查询带Schemas的表Select*fromtrain.stu当使用Select语句查询时，需要加上所属schema，否则会默认为dbo的。用schemas既要记表，又要记schemas，为了方便记忆和维护，我们用同义词（相当于别名），使用方法如下：createsynonyms2fromtrain.stugoselect*froms21.5数据库列顺序建数据库的表时，表中列的顺序不影响性能的，因为都会先存定长数据类型的列1.6Sqlserver怎样组织大对象的值1.6.1大对象类型及存储特点大对象包括如下4种类型：Text，Image，varchar(max)，varbinary(max)Textvarbinary(max)varchar(max)Image存大文本存大二进制存的值比较小，默认存在数据页的外面，即和所在行的其它数据不存到同一个数据也中存的值比较小，默认存在数据页的里面，即和所在行的其它数据不存到同一个数据也中1.6.2将大对象列的所有值都存在数据页的外面将大对象列的所有值都存在数据页的外面的方法：用sp_tableop