【网学网提醒】：网学会员为广大网友收集整理了，著名物流外企SQLServer2000实战问题解决技术文档，希望对大家有所帮助！
SQL实战问题(ForSQL2000Server)Version1.0/DateDecember2005
SQL实战问题
(ForSQL2000ServerinXSSC-CHN)
AReferenceGuide
DATE:FEBRUARY2006
Confidential
?CopyrightXSSCGlobal
SQL实战问题(ForSQL2000Server)Version1.0/DateDecember2003
问题一：问题一：数据正确性检验问题二：问题二：索引优化问题三：问题三：修改用户密码需要注意问题四：问题四：备份策略设置问题五：死锁问题五：问题六：问题六：DTS和JOB命名
Confidential
Page2
问题一：问题一：数据正确性检验XSSC数据库服务器上有几个非常重要的数据库，如ODS_CHN，ODS_Wofe，其中的数据定时从新加坡导入上海，然后还要将正确的数据导入北京、厦门等地区，是各地业务作业的基础。保证每天数据的正确，是数据库管理员的一个最重大的职责，同时也非常繁琐。每天有几十个表的数据要准确及时，检查量比较大。经过几个月的摸索，现在有了一个比较快速、高效率的查数据的方法，同大家分享。建立检查数据的DTS，设定JOB，让他每天自动运行，替代原先的较为缓慢的手工查询作业。这样会提高检验的速度和减少人工误差。DTS是根据XSSC的实际业务制作的，将近两天新加坡数据库中的相关表的的记录笔数，和本地数据库中的相关表中的记录笔数分别查出，然后写入一个资料表中（图一）。只需查询一个结果资料表，比较两者数值是否一致，就可以判断数据是否有问题（图二）。如果当天数据没有大问题，每天可以在9：30AM之前发Email通知大家。
图一
图二
问题二：问题二：索引优化XSSC数据库中的主要数据库，ODS_CHN，从2001年9月就开始就有了数据，现在数据库数据文件就有将近8G大小，数据量非常大，一般的交易表，数据量都超过10万笔，收款表JOB_Cost记录更是超过了500万笔。在查询时会比较慢，因此，索引的建立和维护就尤为重要。索引类型一般分为两种，聚集索引和非聚集索引。
?
聚集索引只能有一个，资料表中的数据行顺序是按该索引排列，在创建主键时会自动将主键创建为聚集索引，聚集索引能大大提高查询速度。非聚集索引，在同一个表中可以有多个，表中数据行不会按该索引排列。非聚集索引有独立的索引页可以准确指向需要查找的数据行。
?
索引会在数据的不断删除、修改和增加中产生断裂和碎片，随着时间的推移，反而会影响数据的查询效率。有两种方法可以减少索引碎片：
?
除去并重新创建聚集索引。这种方法的缺点是索引在除去/重新创建周期内为脱机状态，并且操作属原子级。使用DBCCINDEXDEFRAG按逻辑顺序重新排序索引的叶级
页。能联机操作。缺点是在重新组织数据方面没有聚集索引的除去/重新创建操作有效。
?
索引创建：一般XSSC数据库中比较常用的Where条件为单据编号和日期两个栏位，于是，将日期的降序和单据编号的升序作为聚集索引，对数据根据日期查询和有条件删除，比较有利。还可以根据表的具体情况，将其他需要经常查询的栏位作为非聚集索引保存。索引维护：建立了DTS和JOB，在每个礼拜比较空闲的时候整理一次。先用DBCCSHOWCONTIG方法查选出逻辑扫描碎片大于30％的资料表，再用DBCCINDEXDEFRAG的方法整理，这种方法不会使表脱机工作（图三）。如果碎片量比较大，首次整理会花比较多的时间（图四）。
图三
图四
问题三：问题三：修改用户密码需要注意数据库权限管理，是保证数据库安全的最重要部分。以给出满足用户需求的最小权限为好。比如只需要查看数据库的数据，没有写入的需求，则仅给出该数据库的public和db_Reader权限即可。密码的设置，最好使用强密码，不要用1等简单的数字，也尽量不要与用户名相同，以免被人一猜即中。一般建议有大小写间隔，再加入符号或数字。Sa用户，它是指派给固定服务器角色sysadmin，并不能进行更改或删除。虽然sa是SQLServer内置的管理员登录，但不应在平时的工作中使用它。如果有必要，可以创建新的系统管理员，只要赋予用户成为sysadmin固定服务器角色的成员，就可以拥有数据库最高操作权限。只有当没有其它方法登录到SQLServer实例（例如，当其它系统管理员不可用或忘记了密码）时才使用sa。而且不要使用空密码，请使用强密码。如果需要修改用户密码，需要注意一些事项：
?????
在应用程序中是否有使用该用户名，密码是否写死在程序中。在DTS中是否有使用该用户，如果有，其中的密码也需要相应改变。修改用户的密码，该用户创建的JOB有可能运行不了，需要重新创建一下。如果是服务器正在使用的权限，请查询服务器设置。如SharePoint服务器等。在ODBC连接中也有可能写如密码，需要及时更改。
应用程序更改登录数据库的用户，在改前需要进行严格的测试，以免出现程序不能正常运行，或者出现影响其他程序或网络的事件。
问题四：问题四：备份策略设置根据数据库的不同情况，设置不同的备份策略。
?
恢复模式：对数据及时性要求比较高的数据，可以保全日志文件，将恢复模式设置为Full，其余的设置为Simple。备份间隔：对数据要求较严格的数据库，可以每天备份，甚至每小时备份。要求宽松或者数据能方便获取的数据库
，可以每周备份或每月备份。
?
在备份前，对恢复模式为Full的数据库，先进行日志截断，数据库收缩，然后再备份，对恢复模式为Simgle的数据库也要先收缩，再备份。如此，数据库文件会缩小一些，数据库备份的速度也会加快。还能提高以后数据库使用的性能。可以创建DTS，JOB，自动并且定期备份，只要检查是否备份成功即可（图五）。当然也要定时对备份完成后的数据库文件进行测试，是否可以正常恢复。其他备份相关信息，请参考文档《DatabasebackupStrategy》，其中有详细说明。
图五
问题五：问题五：死锁5.5服务器出现过死锁问题，当时点击CurrentAcivity会出现提示1222错误，解释为“已超过了锁请求超时时段”，点击对应的数据库表，整个企业管理器会Down掉。SQLServer使用以下资源锁模式。锁模式共享(S)更新(U)排它(X)意向架构大容量更新(BU)描述用于不更改或不更新数据的操作（只读操作），如SELECT语句。用于可更新的资源中。防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。用于数据修改操作，例如INSERT、UPDATE或DELETE。确保不会同时对同一资源进行多重更新。用于建立锁的层次结构。意向锁的类型为：意向共享(IS)、意向排它(IX)以及与意向排它共享(SIX)。在执行依赖于表架构的操作时使用。架构锁的类型为：架构修改(Sch-M)和架构稳定性(Sch-S)。向表中大容量复制数据并指定了TABLOCK提示时使用。
两个线程都锁定数据，并且等待其它进程占有的资源，释放自己的锁时，他才能释放自己的锁，此时会发生死锁。即当某组资源的两个或多个线程之间有循环相关性。处理方法，可以在查询分析器中执行execsp_lock，这是一个系统存储过程，专门用来查看数据库中锁的情况。找到锁住的进程号spid，然后用Kill命令将他杀掉，就可以解决这个问题。KILL命令可能需要一些时间来完成，特别是在涉及回滚长事务时。
问题六：问题六：DTS和JOB命名DTS和JOB的命名，是为了方便DBA的维护。XSSC数据库中的DTS和JOB命名都有一定的规律。名称一般分为四段，第一个段为JOB执行间隔，如Daily、Weekly、Hourly、Monthly。第二段为数据来源，第三段数据目的地，再后面一段就是该DTS或JOB的详细信息了，如表名、创建日期等。例如：Daily_SIN400_SHA1_ODSWOFE_Replication_F004P05、Dily_FAMS_ODS_XMN_RPT_F001P01_20041013，如此可以从名称就可以看出很多信息。
