SQL SERVER DBA 名企面试问题及答案.pdf

某名企数据库面试问题答案 答案仅供参考! 1.磁盘柜上有 14 块 73G 的磁盘，数据库为 200G 大小包括日志文件， 如何设置磁盘(要 说明这 14 磁盘是怎么用的)? 这个问题应该是考察硬件知识和数据库物理部署。 首先需要知道这些磁盘是否要用于存放数据库备份文件和数据库性能(读/写)要求。 来决 定 raid 的级别。 1)、如果偏重于性能考虑，而且不用存放数据库备份文件的话，考虑使用 raid0+1，这 样可使用的磁盘容量为：14*73*50%=511G。 2)、如果读/写性能要求不高，而且还比较抠门的话，可以考虑 raid5，这样可使用的磁 盘容量为：13*73=949G。 至于如何使用应该是说数据库物理文件的部署。注意说出将 tempdb,data file,log file 分 开存放以减少 I/O 竞争即可。其实现在的条带化磁盘一般都会自动将文件分存，人为的分布 已经越来越不重要了。 2.有两服务器群集，分别为 node1 和 node2 现在要打 win200 系统补丁，打完后，要重 新启动，如何打补丁，不能影响用户使用(要用群集的术语详细说明)。 这个具体操作有点忘了。大致是：首先看哪个节点正在使用，通过节点 IP(私有)访问另 一个空闲节点，为其打上补丁，然后在群集管理器中停止该节点(也可以用命令行方式)，重 新启动。等到启动完毕，将切换使用节点，为另一个节点打补丁。然后重新启动。 3.有一个 A 数据库，分别复制到 B 和 C B 要求 每次数据更新 也同时更新，C 每天更 新一次就行，如何制定复制策略! 这个应该考察的是复制知识。 a-b 1)、如果使用 SQL Server 复制功能，那么让 a-b 使用事务性复制方式(同步复制)。 2)、如果表不多，也可以自己写触发器，利用 linkserver+distribute transaction。 a-c 1)、如果使用 SQL Server 复制功能，那么让 a-b 使用快照复制方式，在某一时间点进 行一次性复制。 2)、也可以自己写 bat，将 a 备份后，通过 ftp 传输备份介质，恢复 c。(比较麻烦，不推 荐) 4.有一个 order 表，有 90 个字段，20 个索引，15 个复合索引，其中有 3 个索引字段超 过 10 个，如何进行优化 这个问题问的比较没水平。你不详细说明这个表的使用方式(读写类的，还是几乎是静 态表)，就问人家怎么优化?!还不如问问索引的分布访问原理更好。 看得出他就想让你说：那三个索引超过 10 个，B 树遍例效率很低，适当减少字段数目。 如果是 SQL2005，可以将选择性不好的字段放在“索引附加字段”中，以保证索引覆盖。 而且 SQL Server 由于有锁升级的毛病，可以考虑拆开表。 5.有一个数据库 200G 大小，每天增加 50M 允许用户随时访问，制定备份策略(详细说 明)。 这种情况可以采用增量备份方式。每周日做一次全备份，周一到周六作增量备份(由于 数据量较少，可以考虑每 30 分钟增量备份一次)。这样可以尽量减少性能消耗，而且如果 transaction log 丢失的情况下，可以保证最多丢失 30 分钟数据。 6.管理 50 台数据库，日常工作是检查数据库作业是否完成，你该如何完成这项检查工 作? 这个比较简单。在每台机器上建立 linkserver，然后在 DBA 管理服务器上做个分布式视 图，每次查询该视图，各个机器上的作业情况一目了然。分布式视图写法： create view vw_job as select 机器一 as MName,* from linkserver1.sysjobactivity union all select 机器二 as MName,* from linkserver2.sysjobactivity union all select 机器三 as MName,* from linkserver3.sysjobactivity 。 。 。 7.自定义函数和存储过程的区别是什么，什么情况下只能用自定义函数，什么情况下只能用 存储过程 这个应该是考察存储过程编写经验。一般自定义函数主要用于其他 sql 中的调用，如： select yourfunc(.) from table 这种情况下，一般只能通过函数实现。 存储过程的功能要远远强于函数，例如动态执行 sql(sp_executesql)的使用和一些特殊的 功能，自定义函数中是不支持的，只能用存储过程实现。 8.SQL 2005 的新特性是什么 ? 与 oracle 有什么区别? SQL 2005 的新特性一般都是和 Oracle 学的。 下面是当时被 leimin 逼着写的，你可以做个参考： 一、数据库设计方面 1、字段类型。 varchar(max)nvarchar(max)类型的引入大大的提高了编程的效率，可以使用字符串函数 对 CLOB 类型进行操作，这是一个亮点。但是这就引发了对 varchar 和 char 效率讨论的老问 题。到底如何分配 varchar 的数据，是否会出现大规模的碎片?是否碎片会引发效率问题?这 都是需要进一步探讨的东西。 varbinary(max)代替 image 也让 SQL Server 的字段类型更加简洁统一。 XML 字段类型更好的解决了 XML 数据的操作。XQuery 确实不错，但是个人对其没好 感。(CSDN 的开发者应该是相当的熟了!) 2、外键的级联更能扩展 可能大部分的同行在设计 OLTP 系统的时候都不愿意建立外键， 都是通过程序来控制父 子数据的完整性。但是再开发调试阶段和 OLAP 环境中，外键是可以建立的。新版本中加 入了 SET NULL 和 SET DEFAULT 属性，能够提供能好的级联设置。 3、索引附加字段 这是一个不错的新特性。 虽然索引的附加字段没有索引键值效率高， 但是相对映射到数 据表中效率还是提高了很多。我做过试验，在我的实验环境中会比映射到表中提高 30%左 右的效率。 4、计算字段的持久化 原来的计算字段其实和虚拟字段很像。只是管理方面好了而已，性能方面提高不多。但 是 SQL2005 提供了计算字段的持久化， 这就提高了查询的性能， 但是会加重 insert 和 update 的负担。OLTP 慎用。OLAP 可以大规模使用。 5、分区表 分区表是个亮点!从分区表也能看出微软要做大作强 SQL Server 的信心。资料很多，这 里不详细说。但是重点了解的是：现在的 SQL Server2005 的表，都是默认为分区表的。因 为它要支持滑动窗口的这个特性。这种特性对历史数据和实时数据的处理是很有帮助的。 但 是 需 要 注 意 的 一 点 ， 也 是 我 使 用 过 程 中 发 现 的 一 个 问 题 。 在 建 立 function-schema-table 后，如果在现有的分区表上建立没有显式声明的聚集索引时，分区 表会自动变为非分区表。这一点很让我纳闷。如果你觉得我的非分区索引无法对起子分区， 你可以提醒我一下呀!没有任何的提醒，直接就变成了非分区表。不知道这算不算一个 bug。大家也可以试试。 分区表效率问题肯定是大家关心的问题。 在我的试验中， 如果按照分区字段进行的查询 (过滤)效率会高于未分区表的相同语句。但是如果按照非分区字段进行查询，效率会低于未 分区表的相同语句。但是随着数据量的增大，这种成本差距会逐渐减小，趋于相等。(500 万数量级只相差 10%左右) 6、CLR 类型 微软对 CLR 作了大篇幅的宣传，这是因为数据库产品终于融入.net 体系中。最开始我 们也是狂喜，感觉对象数据库的一些概念可以实现了。但是作了些试验，发现使用 CLR 的 存储过程或函数在达到一定的阀值的时候，系统性能会呈指数级下滑!这是非常危险的!只使 用几个可能没有问题，当一旦大规模使用会造成严重的系统性能问题! 其实可以做一下类比，Oracle 等数据库产品老早就支持了 java 编程，而且提供了 java 池参数作为用户配置接口。但是现在有哪些系统大批使用了 java 存储过程?!连 Oracle 自己 的应用都不用为什么?!还不是性能有问题!否则面向对象的数据库早就实现了! 建议使用 CLR 的地方一般是和应用的复杂程度或操作系统环境有很高的耦合度的场 景。如你想构建复杂的算法，并且用到了大量的指针和高级数据模型。或者是要和操作系统 进行 Socket 通讯的场景。否则建议慎重! 7、索引视图 索引视图 2k 就有。 但是 2005 对其效率作了一些改进但是 schema.viewname 的作用域真 是太限制了它的应用面。还有一大堆的环境参数和种种限制都让人对它有点却步。 8、语句和事务快照 语句级快照和事务级快照终于为 SQL Server 的并发性能带来了突破。个人感觉语句级 快照大家应该应用。事务级快照，如果是高并发系统还要慎用。如果一个用户总是被提示修 改不成功要求重试时，会杀人的! 9、数据库快照 原理很简单， 对要求长时间计算某一时间点的报表生成和防用户操作错误很有帮助。 但 是比起 Oracle10g 的闪回技术还是细粒度不够。可惜! 10、Mirror Mirror 可以算是 SQL Server 的 Data guard 了。但是能不能被大伙用起来就不知道了。 二、开发方面 1、Ranking 函数集 其中最有名的应该是 row_number 了。这个终于解决了用临时表生成序列号的历史，而 且 SQL Server2005 的 row_number 比 Oracle 的更先进。因为它把 Order by 集成到了一起， 不用像 Oracle 那样还要用子查询进行封装。但是大家注意一点。如下面的例子： select ROW_NUMBER() OVER (order by aa) from tbl order by bb 会先执行 aa 的排序，然后再进行 bb 的排序。 可能有的朋友会抱怨集成的 order by，其实如果使用 ranking 函数,Order by 是少不了的。 如果担心 Order by 会影响效率， 可以为 order by 的字段建立聚集索引， 查询计划会忽略 order by 操作(因为本来就是排序的嘛)。 2、top 可以动态传入参数，省却了动态 SQL 的拼写。 3、Apply 对递归类的树遍历很有帮助。 4、CTE 个人感觉这个真是太棒了!阅读清晰，非常有时代感。 5、try/catch 代替了原来 VB 式的错误判断。比 Oracle 高级不少。 6、pivot/unpivot 个人感觉没有 case 直观。 而且默认的第三字段(还可能更多)作为 group by 字段很容易造 成新手的错误。 三、DBA 管理方面 1、数据库级触发器 记得在最开始使用 2k 的时候就要用到这个功能， 可惜 2k 没有， 现在有了作解决方案的 朋友会很高兴吧。 2、多加的系统视图和实时系统信息 这些东西对 DBA 挑优非常有帮助，但是感觉粒度还是不太细。 3、优化器的改进 一直以来个人感觉 SQL Server 的优化器要比 Oracle 的聪明。 SQL2005 的更是比 2k 聪明 了不少。(有次作试验发现有的语句在 200 万级时还比 50 万级的相同语句要快 show_text 的 一些提示没有找到解释。一直在奇怪。) 论坛例子： 4、profiler 的新事件观察 这一点很好的加强了 profiler 的功能。 但是提到 profiler 提醒大家注意一点。 windows2003 要安装 sp1 补丁才能启动 profiler。否则点击没有反应。 5、sqlcmd 习惯敲命令行的朋友可能会爽一些。但是功能有限。适合机器跑不动 SQL Server Management Studio 的朋友使用。 四、遗憾 1、登陆的控制 始终遗憾 SQL Server 的登陆无法分配 CPU/内存占用等指标数。如果你的 SQL Server 给别人分配了一个只可以读几个表的权限， 而这个家伙疯狂的死循环进行连接查询， 会给你 的系统带来很大的负担。而 SQL Server 如果能像 Oracle 一样可以为登陆分配如：5%的 cpu， 10%的内存。就可以解决这个漏洞。 2、数据库物理框架没有变动 undo 和 redo 都放在数据库得 transaction 中，个人感觉是个败笔。如果说我们在设计数 据库的时候考虑分多个数据库，可能能在一定程度上避免 I/O 效率问题。但是同样会为索引 视图等应用带来麻烦。看看行级和事务级的快照数据放在 tempdb 中，就能感觉到目前架构 的尴尬。 3、还是没有逻辑备份 备份方面可能还是一个老大难的问题。 不能单独备份几个表总是感觉不爽。 灵活备份的 问题不知道什么时候才能解决。 4、SSIS(DTS)太复杂了 SQL Server 的异构移植功能个人感觉最好了。(如果对比过 SQL Server 的链接服务器和 Oracle的透明网关的朋友会发现SQL Server的sp_addlinkedserver(openqu