SQLServer DBA 六十问

1.  char、varchar、nvarchar之间的区别（包括用途和空间占用）；xml类型查找某个节点的数据有哪些方法，哪个效率高；使用存储过程和使用T-SQL查询数据有啥不一样；

① char、varchar、nvarchar之间的区别

• 用途区别：CHAR适合存储定长数据，存储及查询效率更高。字符长度不足的自动用空格填充，所以在读取的时候可能要多次用到trim()。VARCHAR适合长度不固定的字段，自由灵活，但存储效率没有CHAR高（需要多一个字节保存其实际长度）。NVARCHAR表示存储的是可变长度的Unicode类型字符。
• 空间占用区别：char、varchar长度是在1到8000之间，其中英文占1个字节，中文占2个字节，即最多能存储8000个英文，4000个汉字。这种差异导致英文与汉字同时存在时容易造成混乱，为了解决这种不兼容的问题，Unicode字符集所有的字符（包括英文）都用两个字节表示。nchar、nvarchar的长度是在1到4000之间，最多存储4000个字符，不论是英文还是汉字。
• 相同点：均用于存储字符类型，括号中的长度均表示可存储字节数而非字符数。

② xml类型查找某个节点的数据有哪些方法，哪个效率高

xml的数据类型，在存储序列化数据的时候比较有用，还有就是配置文件。

@xml.query()得到的结果也是xml

@xml.value()得到的结果可以转换成int/varchar等

@xml.exist()检查是否包含某个节点

@xml.modify()可以修改里面的内容

效率：exist>query>value          --这只是我的理解，exist只是检查路径，query没有转换，value需要转换。

XML类型查找数据的方法：query方法用于从XML数据类型提取XML；value方法用于从XML文档中返回单个值；exist方法用于确定指定节点是否存在于XML文档中。
这几个查找数据的方法谈不上哪个效率更高，每个都有它的用途；

③ 使用存储过程和使用T-SQL查询数据有啥不一样

存储过程优点：

• 存储过程允许模块化设计组件式编程。存储过程一旦被创建在程序中可多次使用，不必每次都重写相应的t-sql代码，可重用性强，大大提高了程序的可维护性和可移植性。
• 存储过程一次编译多次运行，速度相对更快。
• 减少网络流量。由于存储过程的代码是存储在数据库中，当客户端向服务器端发出调用存储过程的命令时，通过网络传输的只是存储过程的调用，不会有大量的代码在网络中传输，极大的减少了网络流量，降低了网络负载。
• 存储过程增强数据代码安全性。因为对存储过程中引用的对象用户不可以直接操作，必须通过sql server为用户指定某一具体存储过程的执行权限，由于对存储过程执行权限的限制，所以对存储过程所涉及的数据访问权限同样受到一定的限制，非授权用户是不可以访问相关的数据信息的。存储过程的使用既增加了代码安全性，又保证了数据安全性。
• 存储过程屏蔽数据库复杂的细节操作。用户不必访问数据库的底层信息和数据库内部的对象，可以将复杂的数据库操作进行封装，以便简化操作与设计流程。

存储过程缺点：

• 存储过程依赖于特定数据库类型，在存储过程中封装的t-sql代码通常难以迁移到其他类型数据库
• 嵌套过于复杂时，存储过程代码不易阅读，维护难度大，定位问题更复杂

2. 系统DB有哪些，都有什么作用，需不需要做备份，为什么；损坏了如何做还原（主要是master库）；

①系统DB有哪些，都有什么作用，需不需要做备份，为什么

1. Master库：

• 记录着SqlServer系统所有的系统信息（如用户信息、系统配置设置、端点和凭据及其他访问数据库所需信息）
• 记录启动服务器实例所需的初始化信息
• 记录其他数据库的主文件位置
• 如果master数据库有问题，整个SqlServer都无法正常启动，建议经常备份。
• 如果没有备份，损坏后必须重建。重建master库将使所有系统数据库恢复到原始状态（像被重装过一样）。所有用户记录丢失、用户数据库需再次附件、任务计划需要重建…

2. Model库：

• 创建用户数据库的模板，其中所有内容都会被复制到新建库
• 在SqlServer启动时，要使用model数据库的某些设置创建新的tempdb，因此model数据库必须始终存在于SqlServer中，所以它也要有备份。
• 还原方法与用户数据库相同。

3. Msdb数据库：

• 存放计划信息、定时任务、备份与还原历史记录等信息，需要备份
• 还原方法与用户数据库相同。

4. Tempdb数据库：

• 存放临时对象，排序、group by操作等可能会用到，快照及行版本控制隔离级别会用到。
• 不支持备份，每次启动SqlServer时都会重建。保护好model数据库，就能保证下次启动时得到一个好的tempdb数据库。

5. Resource数据库：

• 只读数据库，包含SqlServer中的所有系统对象。
• 其依赖于master数据库的位置（如果移动了master数据库，则必须将resource库也移到相同位置）
• SqlServer不提供直接备份恢复的操作。一般直接备份其物理文件，还原时停止SqlServer服务，将物理文件还原至指定位置（冷备份及还原）

② 如何在一台新服务器上还原数据库

• 确认备用服务器的SqlServer版本与原服务器一致或高于原版本，否则可能会遇到五花八门的报错(select @@version)
• 备用服务器以单用户模式启动SqlServer服务

net start MSSQKSERVER /m

• 在命令行窗口使用sqlcmd连接SqlServer

sqlcmd -E -S sql2005pc

• 恢复master库（还原后sqlserver服务自动停止）

restore database master from disk=’备份文件路径’

• 用跟踪标志3608来启动SqlServer

由于恢复的master数据库里记载的其他数据库的路径和现在的路径不一致，直接重启SqlServer会失败，必须要用跟踪标志3608来启动。

net start MSSQLSERVER /f /m /T3608

• 用sqlcmd连接修改各个系统数据库的文件路径到现有的正确路径

sqlcmd -E -S sql2005pc

alter database msdb modify file(name=data,filename=’正确路径\xxx.mdf’);

alter database msdb modify file(name=log,filename=’正确路径\xxx.ldf’);

alter database mssqlsystemresource modify file(name=data,filename=’正确路径\xxx.mdf’);

alter database mssqlsystemresource modify file(name=log,filename=’正确路径\xxx.ldf’);

alter database model modify file(name=data,filename=’正确路径\xxx.mdf’);

alter database model modify file(name=log,filename=’正确路径\xxx.ldf’);

alter database tempdb modify file(name=data,filename=’正确路径\xxx.mdf’);

alter database tempdb modify file(name=log,filename=’正确路径\xxx.ldf’);

• 关闭SqlServer

net stop MSSQLSERVER

• 以正常模式启动

net start MSSQLSERVER

此时，SqlServer可以正常启动，但它使用的数据库除了master以外都是备用服务器自己的，我们需要用生产库的备份来替换他们。

• 恢复msdb库

关闭SqlServer agent服务，利用restore命令恢复，将其指向新的文件路径

Restore database msdb from disk ‘C:\lab\msdb.bak’ with move ‘MSDBData’ to ‘备用服务器数据msdb数据文件’,move ‘MSDBlog’ to ‘备用服务器数据msdb日志文件’,replace

• 恢复model数据库

Restore database model from disk ‘C:\lab\ model.bak’ with move ‘modeldev’ to ‘备用服务器数据model数据文件’,move ‘modellog’ to ‘备用服务器数据model日志文件’,replace

• 修改服务器名称

sp_dropserver 备用服务器名

go

sp_addserver ‘SQL2005PC’,’local’

go

此时，原先SqlServer所有配置都能恢复到新服务器上，接下来将用户数据库逐个恢复即可。

3. 有哪些操作会使用到TempDB；如果TempDB异常变大，可能的原因是什么，该如何处理；

① 哪些操作会使用到TempDB

1. 存储临时对象（如临时变量、临时表、游标等）
2. 使用order by、group by相关语句时
3. 执行计划中使用了merge join或hash join
4. 隔离级别使用快照或行版本控制
5. 指定Sort_in_tempdb时创建或重建索引操作时产生临时排序结果

② 如果TempDB异常变大，可能的原因是什么，该如何处理

可能原因：

• Sql语句执行计划中使用了merge join或hash join使用了大量排序
• 语句使用临时表过大、语句排序过多、CheckDB等
• 执行insert into 临时表语句

如何处理：

• 语句优化；控制临时表大小；适当增加tempdb大小；可以考虑在同一台服务器上安装多个实例每个实例都有它自己的tempdb，可以有效地分散tempdb的使用

4. Index有哪些类型，它们的区别和实现原理是什么，索引有何优点和缺点；如何为SQL语句创建合适的索引，索引创建时有哪些需要注意的项，如何查看你创建的索引是否被使用；如何维护索引；索引损坏如何检查，怎么修复；T-SQL有更好的索引存在，但是运行时并没有使用该索引，原因可能是什么；

•  索引类型和实现原理

1. 聚集索引: 聚集索引基于聚集索引键按顺序排序和存储表或视图中的数据行，是一种B-树结构。
2. 非聚集索引：索引上顺序存储的键与堆表行定位符或聚集索引的键组成的数据结构。
3. 唯一索引：该索引确保每一个键值都是唯一的，会自动在该索引生成一个唯一约束
4. 包含列索引：索引有字段长度（900），字段数（16）的限制，包含列仅存储与子叶节点
5. xml索引：顾名思义，xml列上创建索引，必须拥有一个主xml索引，才能创建辅助xml索引，一般存储的是xml的路径、标记名、节点类型、基表的主键。
6. 全文索引：对某个字段做全文索引的时候，需要定时的更新索引的内容。

•  优缺点

7. 优点： 提高查询效率，减少锁资源占用
8. 缺点： 需要额外的空间和维护操作，不良的索引可能降低语句性能

索引的好处还是大于坏处的，堆表的性能在高并发的随机读写远落后与有聚集索引的表

• 如何为SQL语句创建合适的索引，索引创建时有哪些需要注意的项

建议的索引设计策略：

1. 了解数据库本身的特征（OLAP还是OLTP）
2. 了解最常用的查询的特征
3. 了解查询中使用的列的特征，在列中检查数据分布情况
4. 确定哪些索引选项可在创建或维护索引时提高性能（比如ONLINE选项）
5. 确定索引的最佳存储位置。 非聚集索引可以与基础表存储在同一个文件组中，也可以存储在不同的文件组中。 索引的存储位置可通过提高磁盘 I/O 性能来提高查询性能

注意事项：

1. 对表创建大量索引会影响 INSERT、UPDATE、DELETE 和 MERGE 语句的性能，因为当表中的数据更改时，所有索引都须进行适当的调整。
2. 避免对经常更新的表进行过多的索引，并且索引应保持较窄（列要尽可能少）
3. 使用多个索引可以提高更新少而数据量大的查询的性能。
4. 对小表进行索引可能不会产生优化效果，因为查询优化器在遍历用于搜索数据的索引时，花费的时间可能比执行简单的表扫描还长。
5. 如果索引包含多个列，则应考虑列的顺序
6. 可以将非键列包含在非聚集索引中，以避免超过当前索引大小的限制（最大键列数为 16，最大索引键大小为 900 字节）

参考：

https://docs.microsoft.com/zh-cn/sql/2014-toc/sql-server-index-design-guide?view=sql-server-2014

• 如何查看你创建的索引是否被使用

sys.dm_db_index_usage_stats

select * from sys.dm_db_index_usage_stats where database_id=DB_ID('DBNAME');

关注user_seek/user_scan/user_lookup，这三个值，如果你的表很忙，但这些值都偏小，可能你的sql没有很好的利用索引。

• 如何维护索引

定期的检查索引的碎片情况，根据碎片进行索引的重建或者重新组织

select * from sys.dm_db_index_physical_stats(DB_ID('tempdb'),null,null,null,null);

-- 关注：avg_fragmentation_in_percent字段
-- 一般在10%以内算优，30%考虑reorgnize,70%rebuild...

对从未使用过的索引，考虑与业务方沟通确认后删除

• 索引损坏如何检查，怎么修复

一般会在业务低峰的时候检查表数据检查dbcc checktable，检查其结果，如果有错误则尝试使用dbcc checktable的repair选项，或者还原数据库表。

• T-SQL有更好的索引存在，但是运行时并没有使用该索引，原因可能是什么

变量窥探

统计信息不准确

Sql中指定了hint或执行计划

查询返回的数据量过大

5. 视图上我们能建索引吗，如果能建的话，会啥好处和坏处；视图上建索引和表上建索引有啥区别

1. 能创建，视图+聚集索引=索引视图。 

索引视图可以看作是一个和表(Table)等效的对象，会将数据物理存在数据库中，所存的数据和索引视图中所涉及的底层表保持同步（类似Oracle中的Materialized View）

1. 好处：

1. 可以缩减索引的大小，更加精确定位到数据
2. 加速查询

对于涉及对大量的行进行复杂处理的视图，由于结果集已经保存为一张带有聚集索引的表，因此无需重新计算，索引视图有明显的速度优势。

1. 查询优化器引用

即使未在 FROM 子句中指定使用视图，查询优化器也可以使用索引视图。这样一来，可从索引视图检索数据而无需重新编码，设计良好的索引视图可以加快许多查询的处理速度　

1. 坏处：
2. DML性能可能会显著降低，在某些情况下，甚至无法生成查询计划
3. 与基表上的索引相比，对索引视图的维护可能更复杂，开销更大
4. 区别：
5. 视图索引基于视图，对视图和索引有诸多条件的限制
6. 对视图创建的第一个索引必须是唯一聚焦索引
7. 视图必须是使用schemabinding选项定义的。架构绑定将视图与底层基表的架构进行绑定。
8. 视图所引用的基表必须与该视图位于同一db中，并且与该视图有着相同的所有
9. 视图所引用的表和用户定义函数在视图中必须通过两段式名称引用，不允许单段式，三段，和四段式名称
10. 索引视图是一个已被物化或保存在数据库中的视图。当基本表更新时，给视图建立的索引由数据库引擎保存和更新。当你的结果集返回许多行并且需要求总数时，索 引视图十分有用

6. Job信息我们可以通过哪些表获取；系统正在运行的语句可以通过哪些视图获取；如何获取某个T-SQL语句的IO、Time等信息

① Job信息我们可以通过哪些表获取

所有的作业定义，日志，历史等，都存储在msdb的sysjob* 系统表中

msdb.dbo.sysjobactivity:代理作业的活动和状态

msdb.dbo.sysjobhistory:作业历史

msdb.dbo.sysjobs: 作业定义

msdb.dbo.sysjobschedules：作业计划

msdb.dbo.sysjobservers: 作业与特定的server关联或关系msdb.dbo.sysjobsteps: 作业步骤

msdb.dbo.sysjobstepslogs: 所有SQL Server 代理作业步骤的作业步骤日志

②系统正在运行的语句可以通过哪些视图获取

sys.dm_exec_requests：返回有关在SQL Server中正在执行的每个请求的信息sys.sysprocesses：包含有关在SQL Server实例上运行的进程的信息。这些过程可以是客户端进程或系统进程。类似于v$session

sys.dm_exec_sql_text：返回由指定的sql_handle标识的SQL批处理的文本

③如何获取某个T-SQL语句的IO、Time等信息

set statistics io  
set statistics time 

或

sys.dm_exec_query_stats

7. 在线系统，一个表有五千万记录，现在要你将其中的两千万条记录导入到另一台服务器的某个表中，导完后，需要将这两千万数据删除，你预备如何处理，优缺点是什么；

8. 数据库服务器报磁盘空间不足，你将如何应对，要求尽快恢复

1. 查找磁盘空间不足原因，是正常增长还是突然暴增
2. 寻找可清理文件进行清理
3. 若是数据库中执行sql导致空间暴增，联系业务方暂停该语句执行，后续进行优化
4. 若是日志文件太大，备份日志，sqlserver会自动截断日志
5. 若是正常增长，对服务器扩容

9. 临时表、表变量、CTE(通用表表达式)有啥区别和联系，保存位置有啥不一样，使用时如何决定选哪种

10. SQLServer有哪些隔离级别，默认级别是哪个；数据库有哪些主要的锁类型；行版本控制是如何实现的；

① SQLServer有哪些隔离级别，默认级别是哪个

附：ACID特性

原子性：事务中的语句要么全部执行，要么全不执行
一致性：确保系统不会出现不正确的逻辑状态。例如转账时，加减钱数必须是相同的
隔离性：事务之间无法相互看到中间状态，只能看到提交或回滚后的数据
持久性：保证事务效果持久存在，即使系统故障亦是如此

②数据库有哪些主要的锁类型

https://docs.microsoft.com/zh-cn/sql/2014-toc/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-2014

③ 行版本控制是如何实现的

当要更新表或索引上某行时，更新后的值会被标识上XSN的值，代表事务准备更新该数据。当开始更新时，该行的前一个版本会存储在version store中，新版本中则会包含一个指针来指向version store中的旧行。行的新旧版本通过指针关联，这个版本会持续到事务结束。

开启行版本控制后，sqlserver会把行版本存放在tempdb中。修改的数据越多，需要存的信息越多，对sqlserver额外的负载就越大。

SQL Server 还支持使用行版本控制的其他两个事务隔离级别。 一个是已提交读隔离的实现，另一个是事务隔离级别（快照）。

《sqlserver 企业平台管理实战》

https://docs.microsoft.com/zh-cn/sql/2014-toc/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-2014#Row_versioning

11. 死锁如何跟踪；阻塞如何跟踪和查找；发现有问题的语句后，如何进行处理；

• 死锁如何跟踪

扩展事件，选择lock_deadlock和lock_deadlock_chain事件

DBCC TRACEON跟踪 1204及1222事件

利用sql profile中的死锁图形事件

• 阻塞如何跟踪和查找

通过连接sys.sysprocesses视图里waittype,open_tran,status字段值的组合来分析阻塞源头

（查询waittype对应等待类型select waittype,lastwaittype from sys.sysprocesses）

5种常见的阻塞类型

• 发现有问题的语句后，如何进行处理

1. 由于语句运行时间太长而导致的阻塞，语句本身在正常运行中，只须等待某些系统资源。

解决方法：

要解决这一类阻塞，数据库管理员需要和数据库应用设计人员合作，共同解决以下问题。

1. 语句本身有没有可优化的空间？

这里包括修改语句本身降低复杂度、修改表格设计、调整索引等。

1. SQL Server整体性能如何？是不是有资源瓶颈影响了语句执行速度？

当SQL Server 遇到诸如内存、硬盘读写、CPU等资源瓶颈时，原来能很快完成的语句有可能会花很长时间。

1. 如果语句天生就很复杂，无法调优（很多处理报表的语句就是这样），就须考虑怎样把这一类应用从OLTP系统中隔离出来。
2. 未按预期提交的事务导致的阻塞

这一类阻塞的特征，就是问题连接早就进入了空闲状态（sysprocesses.status=’sleeping’和sysprocesses.cmd=’AWAITING COMMAND’），但是，如果检查sysprocesses.open_tran，就会发现它不为0，以及事务没有提交。

这类问题很多都是因为应用端遇到一个执行超时，或者其他原因，当时执行的语句被提前终止了，但是连接还保留着。应用没有跟随发来的事务提交或回滚指令，导致一个事务被遗留在SQL Server里。

解决办法：

1. 应用程序做好错误处理工作。这些工作包括

1. 在做SQL Server调用的时候，必须加上错误捕捉和处理语句
2. 设置连接属性“SET SACT_ABORT ON”
3. 考虑是否要关闭连接池

2. 联系业务方即使提交或回滚事务，若业务方同意可以考虑kill掉阻塞源
3. 分析为什么连接会遇到异常终止

1. 客户端没有及时把结果集取出而导致语句长时间运行

解决方法：

1. 慎重返回大结果集。这种行为不仅会对SQL Server和网络带来很大负担，对应用程序本身来讲，也要花很多资源去处理结果集。
2. 如果应用程序的确须返回大结果集，则要考虑报表数据库和生产数据库分开
3. 阻塞的源头连接一直处于rollback状态

不管是在客户端退出，还是在服务器端KILL，为了维护数据库事务的一致性，SQL Server都会对连接没来得及提交的事务做回滚动作。SQL Server要找到所有当前事务修改过的记录，把它们改回原来的状态。所以，如果一个DELETE、INSERT或UPDATE已经运行了一个小时，可能回滚也需要一个小时。回滚过程中，阻塞还会延续，我们只能等待。

有些用户可能等不及，直接重启SQL Server。当SQL Server关闭的时候，回滚动作会被中断，SQL Server会被很快关掉，但是这个回滚动作在下次SQL Server重启的时候会重新开始（数据库做恢复的时候）。重启的时候如果回滚不能很快结束，整个数据库都不可用，可能会带来更严重的后果。

解决方法：

最好的方法是在工作时间尽量不要做这种大的修改操作。这些操作尽量安排在半夜或者周末的时间完成。如果操作已经做了很久，最好耐心等它做完。如果一定要在有工作负荷的时候做，最好把一个大操作分成若干小操作分步完成。

1. 应用程序运行中产生死锁，在SQL Server中以阻塞形式体现。
2. 下列方法有助于将死锁减至最少：
3. 按同一顺序访问对象。
4. 避免事务中的用户交互。
5. 保持事务简短并处于一个批处理中。
6. 使用较低的隔离级别。
7. 使用绑定连接。

12. Windows日志主要有哪几种，SQLServer错误日志一般保留几个，什么情况下会产生新的SQL日志；数据库日志恢复模式有哪几种，区别是什么；数据库日志突然变得很大，而且你无法收缩，可能的原因是什么，怎么查找原因，分别将如何处理；

• Windows日志主要有哪几种

应用程序日志、安全日志、setup日志、系统日志、Forwarded Events（事件查看器中可以看到）

• SQLServer错误日志一般保留几个

SQL Server错误日志文件数量默认为7个：1个正在用的(ERRORLOG)和6个归档的(ERRORLOG.1 – ERRORLOG.6)，可以配置以保留更多(最多99个)

SQL Server Agent错误日志文件数量共为10个：1个正在用的(SQLAGENT.OUT)，9个归档的(SQLAGENT.1 - SQLAGENT.9)，个数不可以修改，但可以配置日志所记载的信息类型：信息、警告、错误

• 数据库日志恢复模式有哪几种，区别是什么

1. Full模式

Full模式的旧称叫”Checkpoint without truncate log“，也就是SQL Server不主动截断log，只有备份log之后，才可以截断log，否则log文件会一直增大，直到撑爆硬盘，因此需要部署一个job定时备份log。Full的好处是可以做point-in-time恢复，最大限度的保证数据不丢失，一般用于critical的业务环境里。缺点就是DBA需要维护log，增加人员成本（其实也就是多了定时备份log这项工作而已）。

1. Sample模式

Simple模式的旧称叫”Checkpoint with truncate log“，其实这个名字更形象，在Simple模式下，SQL Server会在每次checkpoint或backup之后自动截断log，也就是丢弃所有的inactive log records，仅保留用于实例启动时自动发生的instance recovery所需的少量log，这样做的好处是log文件非常小，不需要DBA去维护，但坏处也是显而易见的，一旦数据库出现异常，需要恢复时，最多只能恢复到上一次的备份，无法恢复到最近可用状态，因为log丢失了。

1. 大容量日志模式

Bulk-logged模式和full模式类似，唯一的不同是针对以下Bulk操作，会产生尽量少的log：

1) Bulk load operations (bcp and BULK INSERT).

2) SELECT INTO.

3) Create/drop/rebuild index

众所周知，通常bulk操作会产生大量的log，对SQL Server的性能有较大影响，bulk-logged模式的作用就在于降低这种性能影响，并防止log文件过分增长，但是它的问题是无法point-in-time恢复到包含bulk-logged record的这段时间。 Bulk-logged模式的最佳实践方案是在做bulk操作之前切换到bulk-logged，在bulk操作结束之后马上切换回full模式

• 数据库日志突然变得很大，而且你无法收缩，可能的原因是什么，怎么查找原因，分别将如何处理

查找日志空间不重用原因：

SELECT name ,recovery_model_desc ,log_reuse_wait_desc FROM sys.databases WHERE name = '库名'

查询日志的使用空间：

DBCC SQLPERF(LOGSPACE);

可能原因及对应解决方法

13. 分区表和分区视图是什么概念，一般是在什么情况下使用，有啥好处；

分区表

分区表是在SQL SERVER2005之后的版本引入的特性。它把逻辑上的一个表在物理上分为很多部分。

而对于SQL SERVER2005之前版本，所谓的分区表类似分区视图,也就是多个表做union操作。

适用情况及优点：

1. 可以快速、高效地传输或访问数据的子集，同时又能维护数据收集的完整性
2. 可以更快地对一个或多个分区执行维护操作
3. 方便将旧数据归档到慢速磁盘驱动器，并使用快速数据来存储经常访问的数据

分区视图

该视图将所有成员表的SELECT组合为一个使用UNION ALL运算符的结果集。这些成员表的结构相同，作为多个表存储在同一个 SQL Server实例或存储在称为联合数据库服务器的自主 SQL Server 服务器实例组中。

适用情况及优点：

最大的好处是提升性能及免费。表分区仅在Enterprise SQL Server Edition中可用，许可成本昂贵。幸运的是，SQL Server允许您设计自己的分区解决方案，而无需将当前的SQL Server实例升级到Enterprise Edition。这个新选项称为分区视图。虽然这个新解决方案不如表分区灵活，但如果您正确设计，分区视图将为您提供良好的结果。您可以手动设计将用作分区的表，并使用分区视图中的UNION ALL运算符将其组合在一起，该运算符将像表分区一样工作。

14. 如何比较两个同结构的表数据的差异；如果表损坏了，如何修复；如何在备份文件有问题的情况下尽量还原数据；如何将一个表的Identity属性归零；

① 如何比较两个同结构的表数据的差异

1. 求差集

EXCEPT是指在第一个集合中存在，但是不存在于第二个集合中的数据

select * from t1 EXCEPT select * from t2

select * from t2 EXCEPT select * from t1

1. checksum校验（能发现不同，但无法发现哪里不同）

比较两张表里的内容的checksum值是否一致，但是这种方法只局限于两表结构一摸一样

SELECT SUM(CHECKSUM(*)) AS checksumvalue FROM [dbo].[t1_old]

SELECT SUM(CHECKSUM(*)) AS checksumvalue FROM [dbo].[t1_new]

② 如果表损坏了，如何修复

dbcc checkdb(‘dbname’)

checktable

③ 如何在备份文件有问题的情况下尽量还原数据

RESTORE DATABASE TESTDB FROM DISK='D:\temp\TESTDB_BAD.bak'

 WITH CONTINUE_AFTER_ERROR

加CONTINUE_AFTER_ERROR, 虽然备份文件受损，但是会尽最大可能来继续恢复，显示恢复完成

④ 如何将一个表的Identity属性归零；

1. 如果曾经的数据都不需要的话，可以直接清空所有数据，并将自增字段恢复从1开始计数

truncate table 表名

1. dbcc checkident('table_name',reseed,new_reseed_value) 

当前值设置为new_reseed_value。如果自创建表后没有将行插入该表，则在执行DBCC CHECKIDENT后插入的第一行将使用new_reseed_value作为标识。否则，下一个插入的行将使用new_reseed_value+1。如果new_reseed_value的值小于标识列中的最大值，以后引用该表时将产生2627号错误信息。

执行语句DBCC CHECKIDENT (''dbo.Person'',RESEED,10)后再添加数据时，ID就会自动从11开始，也就是new_reseed_value+1开始。

该方法不会清空已有数据，操作比较灵活，不仅可以将自增值归零，也适用于删除大量连续行后，重新设置自增值并插入新的数据；或从新的值开始，当然不能和已有的冲突。

15. CheckPoint、LazyWriter和Eager Write的区别和作用；DDL Trigger 和 DML Trigger的区别和作用；

• CheckPoint、LazyWriter和Eager Write的区别和作用

它们都是脏页写入磁盘的方式，但除此之外基本没有什么相同之处

1. CheckPoint存在的意义是减少服务器的恢复时间。

CheckPoint也就意味着在这个点之前的所有修改都已经保存到了磁盘。CheckPoint会定期发生，将所有缓冲区的脏页写入磁盘，不管脏页中的数据是否已经Commit。CheckPoint不会从缓存中移出脏页，CheckPoint进程的工作只是保证脏页被写入磁盘，并且在页头将缓存中的这个页面标记为干净的页面。

1. Lazy Writer存在的目的是对缓冲区进行管理。

A.    管理SQLServer空闲内存：

1. 定期检查空闲缓冲列表的大小，当这个值过低的时候，它会扫描整个数据缓存， 将一段时间没有使用的页面老化（通过LRU算法），释放内存空间；
2. 如果找到一段时间没有被使用的脏页，它会将其写入磁盘，然后将这个页面的内存空间标记为空闲空间；

B.    协调Windows与SQLServer内存：

1. 监视服务器内存，如果Windows物理内存很少，它会从空闲缓冲列表中释放内存给Windows；
2. 在SQLServer负载很重时，它还会在分配给SQLServer的内存没有达到最大服务器内存阀值时，增加空闲缓冲列表的大小，以适应负载的需要。
3. EagerWrite

　　通常在BCP、SELECT INTO, WRITETEXT,UPDATETEXT,BULK INSERT等nonlogged操作时发生，为了加快这些操作的速度，EagerWrite会管理数据页的创建和分配，这些操作不会等待所有的页面创建完成后才将内存中的数据写入到页面中，而是以更高的优先级边创建边将缓存中的数据写入页面中，以保证缓冲池有足够的空间来完成这些操作。

② DDL Trigger 和 DML Trigger的区别和作用

DDL触发器会响应各种DDL事件，某些执行类似DDL操作的系统存储过程也可以触发DDL触发器。

适用场景：

1. 防止对数据库架构进行某些更改。
2. 数据库中发生了某些事情以响应数据库模式的更改。
3. 记录数据库模式中的更改或事件。

DML触发器是一种特殊类型的存储过程，当发生影响触发器中定义的表或视图的DML事件时，它会自动生效。

DML触发器可用于实施业务规则和保障数据完整性，查询其他表以及包含复杂的Transact-SQL语句。触发器和触发它的语句被视为单个事务，可以从触发器中回滚。如果检测到严重错误（例如，磁盘空间不足），整个事务将自动回滚。

DML Triggers - SQL Server | Microsoft Docs

16. Mirroring 和Logshipping 的区别和使用场景；SQLServer的Mirroring与Oracle的哪像技术比较接近，它们的区别是啥；

• Mirroring 和Logshipping 的区别和使用场景

1. Mirroring
2. Logshipping

日志传送主要是通过最基本的数据库事务日志备份和还原任务，来保持两台或多台机器间数据库同步。以此来实现数据冗余，灾难恢复的目的。但主从库间一定会有时间差，而且可能较长，故障切换也较麻烦。

• Mirroring与Oracle的哪像技术比较接近，它们的区别是啥；

17. Mirroring的搭建步骤，Mirroring三种模式的区别，Mirroring 中同步和异步的原理和要求，搭建Mirroring后，需要对数据库日志做什么处理；

18. Replication配置和使用场景；Replication有哪几种模式；PUSH和PULL有啥区别；搭建Replication后会产生一个什么库；报错时用什么来查看报错的具体语句，清理掉某个库的Replication使用什么语句，查看同步链信息主要通过哪些表；

19. Replication发布端的表能truncate吗，为什么；Replication Identity列如何处理、缺失字段错误如何处理、主键冲突错误如何处理、如何跳过指定的错误、订阅端表被删除了如何处理、大规模改动数据如何处理；某条同步链因为其中的某个表一次性改动数据很大造成同步链的严重延时，要求尽快恢复同步链，如何处理。

20. SSB（Service Broker）使用场景，如何创建，都会创建些什么对象，有啥优缺点，主要通过什么方式实现不同服务器之间的消息传递；可以通过哪些方式排错；

• 使用场景

Service Broker是SQL Server自带的消息队列机制，通过Service Broker可以实现数据实例与实例间的通讯，同时也可以作为数据库实例与应用程序的消息传递机制。同时，Service Broker是队列机制实现的，可以保证消息的执行顺序，对于具有事务性要求的数据同步，Service Broker将是很理想的一个数据同步实现。对于需要异步执行处理的应用程序或需要跨多个计算机分发处理的应用程序，Service Broker 会非常有用。

Service Broker 的典型使用包括：

1. 异步触发器
2. 可靠的查询处理
3. 可靠的数据收集
4. 用于客户端应用程序的分布式服务器端处理
5. 用于客户端应用程序的数据合并
6. 大规模批处理

• 如何创建

7. 创建信息类型
8. 创建合约 
9. 创建队列与服务
10. 发送消息到消息队列中
11. 从 inventory queue 中获取信息并更新数据
12. 做自动监听功能

• 会创建些什么对象

创建消息类型（Message Type），创建消息规则（Contract），队列（Queue）以及服务（Server）

• 优点

SQL Server Service Broker为SQL Server数据库引擎中的消息传递和排队应用程序提供本机支持。这使开发人员可以更轻松地创建使用数据库引擎组件在不同数据库之间进行通信的复杂应用程序；构建分布式且可靠的应用程序；可以跨多个数据库分发数据工作负载，这减少了开发和测试工作还可以提高性能。Service Broker确保在事务环境中管理所有任务，以确保可靠性和技术一致性。

• 缺点
• 消息传递实现方式
• 排错方法

21. 跟踪数据库数据的变更有哪些方法，它们使用上有何优缺点；

SQL Server记录数据变更有四种方法：触发器、Output子句、变更数据捕获（CDC）功能、同步更改跟踪。其中后两个为SQL Server 2008所新增。

SQL Server 2008 引入了两项跟踪功能，以使应用程序能够确定对数据库中的用户表所做的 DML 更改（插入、更新和删除操作）。 在这些功能出现之前，必须在应用程序中实现自定义跟踪机制。 这些自定义机制通常要求对跟踪的表进行架构更改，或者使用触发器。 变更数据捕获和更改跟踪均不要求在源中进行任何架构更改或使用触发器。

22. SQL调优步骤，如何来判断SQL语句存在问题，怎么定位问题，如何解决这些问题；Nested Join、Merge Join和Hash Join的区别；

23. 数据库故障排查步骤，如何处理紧急数据库问题；

24. 如何考虑和制定数据库备份计划；公司要求对一个非常大的数据库或者表做备份，而且要求数据量尽可能少丢失，你可能会采用什么方法；

25. 如果要你做数据库监控，你会关注那些指标（包括SQLServer和Windows），如何制定性能基线，你使用过哪些监控软件；

26. 数据库迁移步骤；重建一套比较大的测试系统(最少10个数据库实例)，如果原来DB数据量都不大，但DB比较多，新搭建的系统数据都不需要，如何快速实现；

27. 创建Cluster 简要步骤，最少需要几个IP，需要安装些什么服务,需要哪些固定的磁盘，Raid如何设置，磁盘如何划分；SQLServer Cluster与Mysql Cluster 和 Oracle RAC的区别等；

28. 如果遇到一个性能不理想、代码复杂的存储过程，很难通过数据库方面的调优来解决问题，你如何说服开发人员修改它（可能开发人员并不愿意修改）；

首先附上性能截图，例如CPU、执行时间，对于数据库的影响等。说明不修改可能导致的问题，造成的影响，DB层面无法调优的原因。如果DBA知道或者有思路如何从代码层面优化，一并告知。

询问开发存储过程对应的功能，及不愿修改的理由，大致可以分为几类：

• 外购的系统，存储过程是底层代码，轻易不敢改动。这种情况确实也不建议随意改动，如果有条件，可以给原厂开case或者联系供应商寻求解决方案。如果没有，是否可以通过将对应功能移到从库或者拆分到新库等workaround实现
• 开发部门自己（或者前同事）写的代码，实现过于复杂且涉及业务影响面广，一时难以改动。如果涉及业务影响面广，该性能问题对业务影响其实应该比较大，更加建议修改。一时难以改动可以用前面分库的workaround降低对数据库的影响，但需要开发给一个预计能排期修改的时间而不是就一直放着。
• 功能很少使用。如果该功能一运行就明显影响数据库整体性能，看是否能放到业务低峰期运行，分库workaround也适用。同样需要开发给一个预计能排期修改的时间。
• 没什么理由单纯不愿意改。一般来说基本不会出现这种情况，将性能问题反馈给本部门及开发leader，如果他们沟通确定能承担风险可以不改，愿意承担风险或者更高的资源预算，不改就不改吧

29. 你有没有遇到过因为你的误操作造成系统故障发生的情况，你当时是如何处理的；如果没有，假定你误删了一个重要的表，你该如何处理；

主库drop索引，从库恰好在执行相关表大查询，导致从库alwayson应用进程被阻塞，主从同步出现延迟。当时系统只有主从延迟超过30分钟的告警，而慢查询执行特别久，导致从库出现长时间延迟，影响了部分从从库获取数据的业务。

当时登录从库发现有阻塞，沟通紧急kill了慢sql，很快延迟问题恢复。后续与开发优化了该慢sql，添加了从库阻塞监控（因为从库有阻塞的话基本都是alwayson应用进程被阻塞），执行DDL前在主从库都确认没有相关大查询。

假定你误删了一个重要的表。

首先先冷静一下。想想是怎么删的，delete了几条？delete了全部？truncate了还是drop了？数据量有多大？

是不是已经影响到了业务？影响面有多大？例如有些配置表很重要但是只要业务不重启，暂时是不会有什么影响。

如果操作前有备份，赶紧对应恢复数据。

如果没有手动备份，数据量也不大，可以通过apexsql等工具闪回数据。

如果确实影响了业务，数据量又很大，联系业务方及备份管理员预备好数据恢复操作，建议两边同时进行。

30. 你准备成为一名什么样的DBA，为此你将如何准备（或者你有怎样的职业规划，准备怎样度过你的DBA生涯）；如果你进入公司，你最想获得的是什么。

加强版

1. 谈谈聚集索引、非聚集索引、Hash索引的区别和各自的优劣，Include覆盖索引的作用，相对于组合索引的优势；

2. 日志文件是什么结构，数据写入日志文件与数据文件区别是什么，如何优化日志文件；

• 日志文件是什么结构
• 数据写入日志文件与数据文件区别是什么

Sqlserver对于日志记录是按照严格的顺序写入的，即使有多个日志文件，sqlserver在同一时间点也只写其中一个。只有这个写满了，sqlserver才会写入另外一个

Sqlserver会按照同一个文件组里所有文件现有空闲空间的大小，按这个比例把新的数据分布到所有有空间的数据文件里。如果某个文件写满了，sqlserver会写到其他有空间的文件里

• 如何优化日志文件

1. 不需要多个日志文件（在数据库中有多个日志文件不会以任何方式提升性能，因为事务日志文件不会像同一文件组中的数据文件一样使用比例填充。）
2. LDF的增长设置不要使用百分比设置，应该使用以MB为单位的增长方式
3. 自动增长 (autogrow) 增量必须足够大，以领先于工作负载事务的需求。
4. 日志文件可以设为自动收缩。 但是，不建议这样做，auto_shrink 数据库属性默认设为 FALSE

3. SQLServer有哪些情况会读或者写日志文件；

4. 描述下CheckPoint、DBCC CheckDB、CheckSum的过程和作用；

5. 数据库文件的组织结构，主要包含哪些页，各自的作用是什么；

6. 如何寻找热点库、热点表和执行最频繁的SQL，以及资源消耗最多的SQL；

7. 内存为什么对SQLServer很重要，SQLServer缓存有哪些类型和各自的作用是什么，内存数据通过哪些方式写入磁盘，一般有哪些调整和控制内存的方法；

8. TempDB容易遇到哪些问题，你是如何配置和使用TempDB使其保持高效和可控的；

9. 对跨服务器访问数据，你有哪些方式，各自有优劣是什么；

10. 谈谈一条Update语句从应用端到数据库端返回结果的整个过程；

11.以SQLServer 2016为例，企业版和标准版的主要区别，企业版的数据库能还原到标准版吗，2016能还原到2012吗，32位机器上的数据库能直接附加或者还原到64位数据库上吗，说说原因；

• 以SQLServer 2016为例，企业版和标准版的主要区别

https://docs.microsoft.com/zh-cn/sql/sql-server/editions-and-components-of-sql-server-2016?view=sql-server-2017

• 2016能还原到2012吗

不能直接还原，微软的软件设计方案基本上都是新版本兼容旧版本，旧版本不兼容新版本。

可以设置兼容性使用脚本迁移

• 企业版的数据库能还原到标准版吗

可以

• 32位机器上的数据库能直接附加或者还原到64位数据库上吗，说说原因

可以使用 sp_attach_db 系统存储过程或 sp_attach_single_file_db 系统存储过程，或者使用SQL Server Management Studio中的备份和还原功能以及 SQL Server 导入和导出向导。您可以在 SQL Server 的 32 位和 64 位两种版本之间来回移动数据库。

12. 一台SQLServer服务器，你最常调整的配置有哪些，说说它们各自的作用；

排序规则：类似于实例级字符集

最大并行度 (MAXDOP) ：控制用于在并行计划中执行查询的的处理器数。

并行的开销阈值(cost threshold for parallelism)：仅当运行同一查询的串行计划的估计开销高于在“并行的开销阈值”中设置的值时， SQL Server 才创建和运行该查询的并行计划。 成本指的是在特定硬件配置中运行串行计划估计需要花费的成本。 可设置为 0 到 32767 之间的任何值。 默认值为 5。

USE AdventureWorks2012 ; 

GO 

EXEC sp_configure 'show advanced options', 1 ; 

GO 

RECONFIGURE 

GO 

EXEC sp_configure 'cost threshold for parallelism', 10 ; 

GO 

RECONFIGURE 

GO 

最大内存使用量

13. 说说你对SQLServer突然出现CPU很高情况可能的紧急处理措施和后续的排查步骤；

检查数据库用户连接数是否正常，是否有阻塞，是否是语句执行导致cpu高

若有阻塞，按前面阻塞处理方法操作；若是单条语句执行慢，看能否进行简单优化；如果不能，联系开发暂停执行该语句，后续排期优化；

若是并行度太高导致cpu高，联系开发降低并行度，调整最大并行度。

14. windows日志报告数据库文件自动增长时间超过15s的错误，你怎么来分析这个问题，有哪些应对方案；

可能每次需要增长的空间过大，此时需要查看数据文件增量，改为按固定大小增长而不要按百分比

可能存储系统存在故障或者不稳定造成的，联系存储工程师共同排查

15. 谈谈你遇到过的Mirror/replication问题，怎么解决的，哪些因素会影响他们的性能；

16. 如果你想说明一台数据库服务器存在内存瓶颈，哪些指标和哪些方式是你判断的依据；

17. 数据库检测出现损坏，谈谈你可能的修复方式和步骤；

18. 某天你收到一台平时运行很正常的数据库报警，发现很多指标都升高了一倍以上，你怎么快速分析和定位可能的问题；

19. 你遇到过哪些典型的SQL性能问题，怎么发现和处理的；

20. 谈谈你在应对数据量很大、访问频率很高的表时，可能采取的方案，实际做过哪些方案，比较有价值的地方是；

21. 谈谈你对分区表、分表、分库的适用场景、解决的问题和可能带来的问题的看法；

22. 描述下你现在采取的备份、还原方案，RPO、RTO情况，分析下可能存在的问题；

23. 谈谈一台数据库服务器上的所有数据（包括帐号、JOB等）迁移到另一台新服务器所有可能的方法和步骤，描述各自的优缺点；

24. 你会为数据库做哪些基础性的维护工作，说说你做这些的作用；

25. 如果现有的SQLServer服务器数量和访问量翻10倍，你有哪些应对措施；

26. 在维护数据库时，你经常使用的工具有哪些，它们各自的作用，还有哪些操作你觉得做成工具能大幅度提高你的工作效率，你为数据库运维自动化做了哪些工作；

27. 就Windows和SQLServer本身而言，你认为那些方面会影响数据库的性能，有哪些应对措施；

28. 你怎么来评估数据库的环境，怎么预测目前数据库可以提供多长时间的使用，基于那些情况来制定采购计划；

29. 你通过哪些方式来不断的提高自己的数据库运维能力，哪些方面是使你不断向前的动力；

30. 作为一名资深的DBA，你如何带领和培养新人，使ta能快速成长。