《微软SQL Server数据库管理》课件

《微软数据库管SQL Server理》欢迎参加《微软SQL Server数据库管理》课程本课程将全面介绍SQLServer数据库系统的管理技术和最佳实践，从基础安装配置到高级性能优化和故障排除无论您是数据库管理员、开发人员还是IT专业人士，本课程都将帮助您掌握管理SQL Server所需的关键技能通过系统学习，您将能够设计、实施、维护和优化SQL Server数据库环境，确保数据的安全性、可用性和完整性让我们一起探索SQL Server丰富的功能和工具，提升您的数据库管理能力课程概述课程目标学习要点先决条件本课程旨在培养学员全面掌握SQL课程涵盖SQL Server安装配置、数据学员应具备基本的SQL语法知识和数Server数据库管理技能，从基础配置库对象管理、安全性设置、备份恢复据库概念理解熟悉Windows服务器到高级维护完成课程后，学员将能策略、性能监控与优化、高可用性解操作系统环境和基本网络知识将有助够独立设计、实施和维护SQL Server决方案以及故障排除方法等核心内容于更好地理解课程内容无需高级编数据库系统，解决常见问题并优化性，注重理论与实践相结合程技能，但了解T-SQL基础会很有帮能助简介SQL Server什么是1SQL ServerSQL Server是微软公司开发的关系型数据库管理系统，提供全面的数据存储、处理和分析解决方案它不仅支持传统的事务处理，还具备高级分析功能，可满足从小型应用到大型企业级系统的各种需求的发展历史2SQL ServerSQL Server起源于1989年，由微软与Sybase公司合作开发从SQLServer

4.2到如今的SQL Server2022，产品经历了显著演变，不断增强性能、安全性和功能，逐步成为市场领先的数据库管理系统之一的主要版本3SQL ServerSQL Server提供多种版本以满足不同需求，包括企业版、标准版、开发版、Web版和免费的Express版各版本在功能、容量限制和授权模式上存在差异，用户可根据实际需求选择合适版本架构SQL Server数据库引擎1核心组件，负责数据存储和处理分析服务2提供OLAP和数据挖掘功能报表服务3创建和分发企业报表集成服务4ETL工具，数据迁移与转换SQL Server采用模块化架构，由多个相互关联但功能独立的组件构成数据库引擎是核心组件，负责数据存储、查询处理和事务管理，它实现了关系数据库的基本功能并提供高效的数据访问分析服务SSAS提供在线分析处理OLAP和数据挖掘功能，帮助用户从大量数据中获取有价值的见解报表服务SSRS则专注于创建各类企业报表，支持从简单列表到复杂交互式仪表板的各种报表类型集成服务SSIS作为强大的ETL工具，可以实现复杂的数据提取、转换和加载操作安装SQL Server系统要求安装SQL Server前，确保系统满足最低硬件要求64位处理器、至少2GB内存建议8GB以上、磁盘空间根据安装组件决定通常需要至少6GB支持的操作系统包括Windows Server2016/2019/2022或Windows10/11建议使用NTFS文件系统以支持全部安全功能安装步骤启动SQL Server安装中心，运行新SQL Server独立安装向导依次选择版本、接受许可条款、选择要安装的功能、配置实例名称、服务账户和排序规则等特别注意身份验证模式选择，可选择Windows身份验证或混合模式确保分配足够资源，尤其是内存配置安装后配置安装完成后，需要进行一系列配置以优化性能和安全性包括配置网络协议启用TCP/IP、设置防火墙规则、启用远程连接、配置内存限制和并行度设置等还应执行初始备份并验证所有服务是否正常启动和运行管理工具SQL ServerSQL Server ManagementStudio SQL Server ConfigurationManager SQL Server ProfilerSSMSConfigurationManager专注于服务器配置管Profiler是一个强大的跟踪和分析工具，可捕SSMS是管理SQL Server的主要图形界面工具理，允许管理员控制SQL Server服务的启动获并分析SQL Server的事件流管理员可以，提供全面的管理功能通过SSMS，管理员模式、运行状态和网络配置它提供了管理监控查询执行、错误、死锁和性能问题等可以执行查询、设计数据库结构、管理安全网络协议、配置客户端连接和服务账户的界它常用于故障排除、性能调优和审计，能够性、备份和恢复数据库、监控性能等它集面，是解决连接问题和配置服务器网络属性记录SQL活动并重放以复现问题，是诊断复杂成了对象资源管理器、查询编辑器和模板等的重要工具问题的有效手段功能，大大简化了日常管理任务创建数据库使用T-SQL创建数据库通过T-SQL创建数据库提供更精细的控制和自动化能力基本语法为CREATEDATABASE database_name，可附加多种参数如文件位置、初始大小、增长设置等例如CREATE DATABASETestDB ONPRIMARY NAME=TestDB,FILENAME=C:\Data\TestDB.mdf,SIZE=100MB,FILEGROWTH=10%LOG ONNAME=TestDB_log,FILENAME=C:\Data\TestDB_log.ldf,SIZE=50MB,FILEGROWTH=5MB使用SSMS创建数据库通过SSMS图形界面创建数据库更为直观在对象资源管理器中，右键点击数据库文件夹，选择新建数据库在弹出的对话框中，可以设置数据库名称、所有者、文件位置、初始大小和自动增长属性等图形界面还提供高级选项如排序规则、恢复模式和兼容性级别的设置数据库文件和文件组SQL Server数据库由至少两个物理文件组成数据文件.mdf/.ndf和日志文件.ldf主数据文件.mdf包含启动信息和系统表，次要数据文件.ndf用于存储用户数据文件组是文件的逻辑分组，用于数据分配、备份和恢复操作合理规划文件和文件组可提高性能和管理效率数据库对象SQL Server数据库中包含多种对象类型，各自承担不同功能表是存储数据的基本单元，由行和列组成，定义了数据的结构视图是基于一个或多个表的虚拟表，可简化复杂查询并提供数据安全层存储过程是预编译的T-SQL语句集合，可接受参数并执行操作，提高代码重用性和执行效率函数返回单一值或表，可用于计算和数据转换触发器是特殊类型的存储过程，在表上的特定事件插入、更新、删除发生时自动执行，用于实现业务规则和数据完整性表设计数据类型主键和外键12SQL Server提供多种数据类型，选择合适的数据类型对性能和存储效率至主键唯一标识表中的每一行，通常使用聚集索引实现，提供数据访问路径关重要常用类型包括整数类型int,bigint、字符串类型char,varchar,外键建立表间关系，确保引用完整性，防止孤立记录定义外键时，可nvarchar、日期时间类型date,datetime

2、数值类型decimal,float以设置级联规则控制当引用行更改时的行为良好的键设计是数据库性能和及特殊类型如XML和空间数据类型建议使用最小满足需求的数据类型，完整性的基础如电话号码使用varchar而非nvarchar约束索引34约束用于确保数据完整性，包括主键约束、外键约束、唯一约束、检查约索引加速数据检索，但会影响写入操作常见类型包括聚集索引每表一个束和默认约束等检查约束验证数据符合特定条件，如年龄范围默认约和非聚集索引可多个索引设计应考虑查询模式、更新频率和数据分布束为列提供默认值适当的约束设计可在数据库层而非应用层保障数据质有效的索引策略对查询性能有显著影响，但过多索引也会降低写入性能量和增加维护成本数据操作语言DML语句语句语句语句SELECT INSERTUPDATE DELETESELECT是最常用的DML语INSERT用于向表中添加新UPDATE修改表中现有数据DELETE从表中移除行语句，用于从一个或多个表中记录可使用INSERT INTO语法为UPDATE法为DELETE FROM检索数据基本语法为table_name column1,table_name SETcolumn1=table_name WHERESELECTcolumn1,column2VALUES value1,value1,column2=value2condition不使用WHEREcolumn2FROM value2插入单行，或WHERE condition不使用子句将删除所有行对于大table_name WHEREINSERT INTOtable_name WHERE子句将更新所有行表，考虑使用TRUNCATEconditionWHERE子句筛SELECT...从其他表插入多，可能导致意外结果复杂TABLE无法筛选但效率更选行，ORDER BY排序结果行插入大量数据时，考虑更新可结合子查询或连接，高或分批DELETE以减少锁，GROUP BY分组数据可使用批量插入方法如BULK但应注意锁定和并发影响定删除操作可能触发级联使用通配符*选择所有列，INSERT或bcp工具以提高性大型更新建议分批处理以减删除，应谨慎使用并考虑事但生产环境中建议明确指定能少锁定时间务回滚机制所需列以提高性能高级查询操作子查询JOIN连接操作用于组合多个表的数据内连子查询是嵌套在另一查询中的SELECT接INNER JOIN返回匹配行，外连接语句，可用于WHERE、FROM或LEFT/RIGHT/FULL JOIN保留一侧或SELECT子句相关子查询引用外部查1两侧的非匹配行交叉连接CROSS询的列，非相关子查询独立执行使用2JOIN产生笛卡尔积，自连接则是表与EXISTS和NOT EXISTS可高效检查存在自身连接性分组和排序聚合函数GROUP BY对数据分组以便应用聚合函聚合函数对一组值执行计算并返回单一4数ORDER BY对结果排序，可指定值，包括SUM、AVG、COUNT、MIN3ASC或DESCOFFSET-FETCH提供分、MAX等它们通常与GROUP BY一起页功能，而TOP限制返回的行数合理使用，可以通过HAVING子句筛选分组使用这些子句可提高查询可读性和性能结果窗口函数扩展了聚合能力索引管理索引类型创建索引优化索引SQL Server支持多种索引类型聚集索引决索引可通过T-SQL或SSMS创建基本语法索引优化涉及选择合适的列、顺序和类型定表中数据的物理存储顺序，每表只能有一为CREATE[UNIQUE][CLUSTERED|使用查询计划分析工具识别缺失索引避免个非聚集索引创建单独的结构，包含键值NONCLUSTERED]INDEX index_name ON过度索引，特别是在频繁更新的表上考虑和指向数据行的指针特殊索引类型包括唯table_name column1,column2创建索查询模式、数据分布和基数等因素定期检一索引、筛选索引、覆盖索引、全文索引、引时可设置填充因子、排序顺序和筛选条件查索引使用情况，移除未使用的索引以减少空间索引和XML索引，每种类型适用于不同等选项对于生产环境，应考虑使用WITH维护成本场景选项如ONLINE=ON以减少对用户的影响事务管理事务的属性ACID1原子性Atomicity保证事务作为一个整体完成或失败事务隔离级别2定义事务之间的可见性和锁定行为显式和隐式事务3控制事务边界的不同方式事务是作为单一工作单元执行的一系列操作SQL Server事务遵循ACID属性原子性确保事务中的所有操作要么全部完成，要么全部失败；一致性保证数据从一个有效状态转变为另一个有效状态；隔离性确保并发事务的执行如同顺序执行；持久性保证一旦事务提交，其结果将永久保存，即使系统崩溃也不会丢失SQL Server提供四种标准隔离级别读未提交最低隔离，允许脏读、读已提交默认级别，防止脏读、可重复读防止不可重复读和可序列化最高隔离，防止幻读还支持快照隔离，提供基于版本的并发控制显式事务使用BEGIN TRANSACTION、COMMIT和ROLLBACK语句明确控制，而隐式事务则在每个语句后自动提交并发控制锁定机制死锁检测和预防SQL Server使用多级锁定机制控制并死锁是两个或多个事务互相等待对方发访问锁可应用于不同级别，从行释放锁的情况SQL Server自动检测到表常见锁模式包括共享锁S、独死锁并选择代价最小的事务作为牺牲占锁X、更新锁U、意向锁IS/IX品回滚可通过系统视图和追踪标记和架构锁Sch等锁定粒度和持续监控死锁事件预防措施包括一致的时间直接影响系统吞吐量和并发用户表访问顺序、减少事务范围、使用绑数可通过设置适当的隔离级别和使定连接以及适当的隔离级别选择用锁提示优化锁定行为乐观并发控制除传统的悲观锁定外，SQL Server还支持基于行版本的乐观并发控制，通过READ_COMMITTED_SNAPSHOT和SNAPSHOT隔离级别实现这种方式通过保存数据的多个版本来避免读写冲突，适用于读操作多于写操作的环境使用乐观并发控制可显著提高系统并发性，但会增加tempdb的使用量数据库备份完整备份差异备份事务日志备份完整备份是最基本的备份类型，包差异备份包含自上次完整备份以来事务日志备份记录所有交易，支持含数据库中的所有数据和足够的日发生更改的所有数据页T-SQL语时间点恢复语法为BACKUP LOG志以使数据库可恢复使用T-SQL法为BACKUP DATABASEdatabase_name TODISK=path语法为BACKUP DATABASEdatabase_name TODISK=path日志备份在完整和差异备份之间执database_name TODISK=path WITHDIFFERENTIAL差异备份通行，频率取决于可接受的数据丢失完整备份是所有恢复策略的基础，常小于完整备份，恢复速度较快，量日志备份链不应中断，否则可建议定期执行，频率取决于数据更是完整备份和日志备份之间的平衡能需要新的完整备份日志备份是改率和可接受的数据丢失量点，适合中等大小的数据库和中等实现最小数据丢失的关键数据变化率备份策略有效的备份策略结合多种备份类型并考虑恢复目标常见模式包括每周完整备份+每日差异备份+每小时日志备份策略应基于恢复点目标RPO和恢复时间目标RTO考虑使用压缩和加密增强安全性和减少存储需求备份应存储在多个位置，包括异地数据库恢复恢复模式还原过程时间点恢复SQL Server提供三种恢复模式，影响日志管理和恢基本还原语法为RESTORE DATABASE时间点恢复允许将数据库恢复到特定时间点，通常复能力完整恢复模式记录所有事务，支持时间点database_name FROMDISK=path WITH选项用于修复用户错误或逻辑损坏要使用此功能，数恢复，适合对数据安全要求高的环境大容量日志选项包括NORECOVERY用于连续还原、据库必须处于完整或大容量日志恢复模式使用恢复模式类似完整模式，但允许最小日志记录的批RECOVERY最终还原和STANDBY只读访问完STOPAT选项指定目标时间点RESTORE LOG量操作简单恢复模式自动截断事务日志，只能恢整恢复过程通常包括还原完整备份，然后是差异备database_name FROMDISK=path WITH复到最近备份点，适合开发或非关键数据份如有，最后是连续的日志备份复杂恢复场景RECOVERY,STOPAT=2023-01-0112:00:00可使用SSMS还原计划辅助实现此方法可精确恢复到事务边界安全管理身份验证模式1SQL Server支持两种身份验证模式Windows身份验证和混合模式Windows身份验证加SQL Server身份验证Windows身份验证利用Windows账户和活动目录，为用登录和用户户提供单点登录体验，通常更安全混合模式在需要支持非Windows客户端时有用，2但需特别注意SQL登录密码策略和安全性登录是服务器级别的安全主体，用于连接到SQL Server实例用户是数据库级别的安全主体，映射到登录Windows登录格式为DOMAIN\User，SQL登录需用户名和密码创建登录的T-SQL语法为CREATE LOGINlogin_name WITH角色3PASSWORD=password，创建用户语法为CREATE USERuser_name FORLOGIN角色是权限的容器，简化安全管理服务器角色如sysadmin、dbcreator控制服务器login_name级权限固定数据库角色如db_owner、db_datareader提供预定义数据库权限集自定义数据库角色可以根据业务需求创建，提供精细的权限控制应用最小权限原则权限管理，仅授予必要访问权限4权限可通过GRANT、DENY和REVOKE语句管理GRANT授予权限，DENY明确禁止优先于GRANT，REVOKE移除之前的GRANT或DENY权限可应用于多种安全范围，从服务器到列级别应建立权限审核流程，确保权限分配符合安全策略和合规要求数据加密列级加密列级加密提供细粒度控制，允许选择性加密敏感数据列如信用卡号或身份证号使用加透明数据加密密函数如EncryptByKey和EncryptByCert手TDE2动加密/解密数据虽实现较复杂且可能影TDE加密整个数据库，包括数据和日志文响索引使用，但提供比TDE更精确的控制，件，提供静态数据保护数据在磁盘上加适合部分数据需加密的场景1密，在内存中解密，对应用透明无需修改代码实现TDE需生成证书或非对称密钥加密密钥管理、创建数据库加密密钥，然后启用加密SQL Server提供分层密钥管理系统，包括服可显著减轻数据文件、备份或日志被盗风务主密钥SMK、数据库主密钥DMK、证险3书和对称/非对称密钥重要的是建立密钥备份和轮换策略，确保密钥安全不丢失扩展密钥管理EKM支持使用硬件安全模块HSM或密钥保管库进行外部密钥管理审核SQL Server审核配置服务器审核SQL Server审核提供跟踪和记录服务器服务器审核配置包括创建审核对象和服和数据库事件的能力，支持法规遵从和务器审核规范T-SQL语法为CREATE安全监控审核可捕获各种事件，包括SERVER AUDIT audit_name TOFILE...登录尝试、权限更改、数据访问和架构和CREATE SERVER AUDIT修改等审核记录可以存储在文件、SPECIFICATION specification_nameWindows事件日志或安全日志中，便于FOR SERVERAUDITaudit_name ADD集中管理和分析audit_action_group服务器级审核适用于登录事件、服务器配置更改和资源使用等方面的监控配置数据库审核数据库审核规范适用于特定数据库内的事件T-SQL语法为CREATE DATABASEAUDITSPECIFICATION specification_name FORSERVERAUDITaudit_name ADDaudit_action_type ONobject这支持更细粒度的审核，可针对特定对象如表或存储过程的操作数据库审核规范与服务器审核结合，提供全面的审核解决方案性能监控动态管理视图性能计数器扩展事件DMVDMV提供服务器内部状态信息，是诊断性Windows性能计数器提供SQL Server各方面扩展事件XEvents是轻量级、高度可配置能问题的重要工具常用DMV包括的实时监控指标关键计数器包括SQL的性能监控系统，逐渐替代SQL Profilersys.dm_exec_requests当前请求、Server:Buffer Manager缓冲池效率、SQL它提供细粒度的事件筛选和目标选项，显著sys.dm_os_wait_stats等待统计和Server:SQL Statistics编译和重用率以及减少监控开销可通过T-SQL或SSMS配置sys.dm_db_index_usage_stats索引使用情Physical Disk磁盘队列长度和响应时间会话，捕获查询执行、等待、错误和资源消况等通过组合多个DMV，可获取全面的可使用Performance MonitorPerfMon图耗等事件扩展事件特别适合长期运行的监性能视图，识别瓶颈如CPU压力、内存不足形化查看这些计数器，或通过PowerShell和控场景和故障排除或I/O延迟DMV编程访问查询优化查询执行计划统计信息查询提示查询执行计划显示SQL Server如何执统计信息是优化器选择执行计划的基查询提示允许开发人员影响优化器决行查询，是性能调优的关键工具可础，它包含列值分布的直方图和密度策表提示如WITH NOLOCK改变锁通过SSMS中的显示执行计划或使用信息SQL Server自动创建和更新统定行为；连接提示如HASH JOIN强制T-SQL命令如SET SHOWPLAN_XML计信息，但有时需要手动干预使用特定连接类型；优化器提示如OPTIONON获取执行计划显示操作顺序、估UPDATE STATISTICS命令更新统计信RECOMPILE控制计划缓存行为虽计的行数、成本比例和操作类型如表息，尤其是在大量数据变更后过时然提示可解决特定问题，但应谨慎使扫描、索引查找或排序分析计划可的统计信息可能导致次优的执行计划用，因为它们可能在数据或结构变化识别性能问题如缺失索引、过多的表和性能下降统计信息可通过DBCC时导致性能下降提示应视为临时解扫描或哈希匹配SHOW_STATISTICS命令检查决方案，不应替代良好的数据库设计资源管理资源调控器1整体资源管理功能工作负载组2用户请求分类资源池3CPU和内存资源分配SQL Server资源调控器Resource Governor是企业版的一项高级功能，允许管理员控制不同工作负载的资源分配资源调控器由三个主要组件组成资源池划分CPU和内存资源；工作负载组将用户请求分类到特定资源池；分类函数确定请求属于哪个工作负载组资源池定义最小和最大资源百分比，确保关键工作负载获得足够资源，同时限制资源密集型查询的影响工作负载组可设置重要度、请求最大内存授权、执行超时和查询资源消耗限制等参数典型用例包括隔离报表查询与事务处理、限制特定应用程序的资源使用，以及确保高优先级业务功能在高负载期间保持响应性高可用性解决方案AlwaysOn可用性组AlwaysOn可用性组是SQL Server企业版中最全面的高可用性和灾难恢复解决方案它支持多个辅助副本最多8个，包括同步和异步复制选项，可实现零数据丢失或近零数据丢失的故障转移辅助副本可用于只读操作和备份，减轻主副本负担可用性组支持多个数据库作为单一故障单元，提供集成的健康监控和自动故障转移数据库镜像数据库镜像是较旧的技术，已被可用性组逐渐取代，但在标准版中仍然可用它提供单数据库级别的保护，支持高安全模式同步和高性能模式异步可选的见证服务器可实现自动故障转移限制包括每个主体仅支持一个镜像，镜像不可读，且不支持多数据库的协调故障转移在未来版本中将被弃用日志传送日志传送是所有版本都支持的基本灾难恢复技术它通过定期备份、传输和还原事务日志实现，通常具有小时级的恢复点目标主要优势包括简单的配置、较低的许可成本和支持多个辅助服务器辅助数据库可以有限访问只读用于报表日志传送不提供自动故障转移，但可与其他技术如可用性组结合使用，增强灾难恢复能力SQL ServerAgent作业警报操作员SQL ServerAgent作业是警报监控SQL Server事件操作员是接收警报通知的自动执行任务的主要机制并在特定条件满足时触发联系人可为每个操作员作业包含一个或多个步响应警报可基于SQL配置电子邮件、寻呼机和骤，每个步骤可执行T-Server错误按严重性或网络消息等通知方式，以SQL脚本、SSIS包、特定错误号、性能条件及可用时间窗口多个操PowerShell命令或操作系如CPU使用率超过阈值作员可组成轮班团队，确统命令等作业可按计划或WMI事件警报触发时保全天候监控通知可通执行、响应特定事件或手可执行作业、通知操作员过数据库邮件发送，支持动启动作业步骤可配置或两者兼有适当的警报详细的错误信息和自定义成功/失败后的操作流，配置可实现主动监控，在格式每个警报可配置不支持分支逻辑作业历史问题严重影响用户前及时同的通知操作员，实现根记录提供执行状态和详细发现并解决据问题类型的差异化通知日志，方便故障排除维护计划创建维护计划维护计划是SQL ServerAgent作业的特殊类型，专注于数据库维护任务可通过SSMS维护计划向导创建，提供图形化界面配置常见维护操作向导支持创建单一任务计划或包含多个任务的综合计划维护计划可安排在非工作时间执行，配置电子邮件通知，并针对特定数据库或整个服务器实例运行常见维护任务标准维护任务包括备份数据库和事务日志，确保数据安全；检查数据库完整性，验证数据结构；重组或重建索引，提高查询性能；更新统计信息，优化查询计划；清理历史记录表，防止元数据膨胀；收缩数据库谨慎使用，回收空间；执行SQL ServerAgent作业，整合自定义维护脚本维护计划故障排除维护计划问题的常见原因包括权限不足、磁盘空间不足和任务间依赖性冲突故障排除步骤包括检查SQL ServerAgent日志和作业历史记录；验证执行账户权限；检查维护计划生成的SSIS包；以及测试单个任务以隔离问题复杂的维护需求可能需要自定义T-SQL脚本或SSIS包，超越维护计划向导的能力数据导入和导出导入和导出向导SQL Server导入/导出向导提供图形界面，简化数据移动操作可通过SSMS访问，支持多种数据源如Excel、文本文件和其他数据库向导允许基本转换和批量复制BCP2筛选，能保存操作为SSIS包以便重用适合中小BCP是命令行工具，用于在SQL Server和文本型数据传输和偶尔使用的场景，不需要SSIS许可文件之间高效传输数据它支持多种格式和选项，适合大量数据移动基本语法为bcp1database.schema.table in|out filename包SSISoptionsBCP优势包括最小开销、脚本支持和SQL Server集成服务SSIS提供全功能ETL平台无需SSIS许可限制包括有限的转换能力和错，用于复杂数据集成SSIS包可使用Visual误处理3Studio中的SSIS设计器创建，支持高级数据流转换、错误处理和工作流控制企业级功能包括增量加载、数据清洗、多源集成和复杂业务逻辑实现适合大规模、重复的数据集成需求数据复制发布者、分发者和订阅者复制架构包含三个主要角色发布者是源服务器，提供要复制的数据；分发者存储元数据和事务，充当中间人；订阅者接收复制的数据分发者可以是独立服务器或与发布者共存拓复制类型2扑可以是一对多单发布者多订阅者、多对一SQL Server提供三种主要复制类型事务复多发布者单订阅者或多对多复杂网络，取决制捕获更改并几乎实时应用，适合频繁更新于业务需求1的环境；合并复制允许分布式修改并解决冲突，适合断连接环境如销售团队；快照复制配置复制定期完全刷新数据，适合相对静态或可接受复制配置通过复制向导或T-SQL脚本实现步定期完全更新的数据每种类型有不同的延骤包括启用数据库进行复制；配置分发；创建迟、资源需求和复杂性3发布和文章表或视图；创建订阅关键配置考虑包括初始化方法、冲突解决策略、筛选条件、安全设置和监控计划应建立验证机制确保数据一致性，以及健壮的监控系统及时发现问题数据压缩行压缩页压缩12行压缩是SQL Server提供的基本压页压缩在行压缩基础上增加两个额缩形式，主要通过优化存储格式来外阶段前缀压缩和字典压缩前节省空间它使用变长存储格式，缀压缩识别并存储列中重复的前缀去除固定长度数据类型中的填充空值；字典压缩为页内重复值创建字间，并为空值和重复值使用压缩表典页压缩通常可实现40-60%的压示行压缩实现简单，CPU开销较缩率，但增加了CPU开销它最适小，通常可节约20-40%存储空间合数据重复性高的大型表，尤其是它适用于包含大量字符数据或有许读取频繁而更新较少的表多空值的表压缩的优点和缺点3压缩的主要优势包括减少存储空间和成本；提高I/O性能，因为每次I/O读取更多数据；提高缓冲池效率，内存中可容纳更多数据缺点包括增加CPU使用率，特别是在写入密集型工作负载中；可能延长某些批量操作时间；在某些情况下可能影响并发性应通过测试确定特定工作负载的最佳压缩策略分区表分区索引分区分区方案和函数表分区将大表横向划分为多个较小的存储单分区表的索引可以是对齐或非对齐的对齐分区函数定义边界值，确定数据行的分区位元，基于分区列的值分区边界定义数据存索引使用与表相同的分区方案，维护简单且置；分区方案将分区映射到文件组创建分储位置，数据移动或添加只影响相关分区支持分区切换非对齐索引使用不同的分区区表需先创建函数和方案，然后在CREATE适合场景包括管理大型历史表、快速加载批方案或完全不分区，可能优化特定查询但增TABLE或ALTER TABLE语句中引用方案量数据和实现滑动窗口策略分区对查询性加管理复杂性聚集索引分区自动应用于表分区维护包括拆分、合并、切换操作，允许能的影响取决于查询模式和分区消除能力，而非聚集索引分区需单独配置在最小停机时间内管理大量数据，特别适合数据仓库环境大型数据库管理大型数据库的挑战1管理TB级以上数据库的特殊考虑优化策略2最小化大型数据库的管理开销分布式分区视图3跨服务器水平分片的实现管理超大型数据库VLDB面临多种挑战备份和恢复时间延长，可能需要特殊策略如文件组备份和增量备份；维护操作如索引重建和统计更新需消耗大量资源和时间；资源需求显著增加，特别是I/O子系统和内存；查询性能优化变得更加复杂，可能需要细粒度分区和高级索引策略针对VLDB的关键优化策略包括实施表和索引分区，支持并行操作和部分维护；使用压缩减少存储需求和I/O负担；采用增量统计更新代替全表扫描；实施时间段维护，将大型操作分解为可管理的块；利用资源调控器限制维护操作资源使用；考虑历史数据归档策略，将不常访问数据移至成本较低的存储分布式分区视图提供另一种水平分片方法，将表分散到多个服务器，扩展超出单一服务器容量内存中OLTP内存优化表本机编译存储过程使用场景和限制内存优化表完全驻留在内存中，使用本机编译存储过程将T-SQL编译为本内存中OLTP最适合高并发、低延迟场无锁并发控制模型它们通过行版本机代码DLL而非解释执行，显著提高景如股票交易、点击流分析和高负载控制和多版本时间戳来管理冲突，消性能它们减少了CPU指令数、内存登录系统等主要限制包括某些T-除传统锁定带来的瓶颈表结构包括访问和解释开销编写本机存储过程SQL功能不支持如计算列、外键和触哈希索引点查找和非聚集索引范围需遵循特定规则，如避免不支持的T-发器的限制、表大小需合理规划以适扫描数据持久性通过事务日志和检SQL构造和使用原子块结合内存优应内存、事务复杂性限制以及模式变查点文件实现，支持崩溃恢复内存化表使用时，本机存储过程可实现数更的特殊考虑迁移到内存中OLTP需优化表特别适合高并发、低延迟OLTP倍性能提升，特别适合CPU密集型操仔细评估应用兼容性和内存需求工作负载作列存储索引列存储索引的工作原理创建和管理列存储索引列存储索引采用按列而非按行组织数据聚集列存储索引CCI替代表的主存储的存储格式数据按列分组，然后压缩结构，适合数据仓库表创建语法为，通常可实现10:1的压缩比列存储使CREATE CLUSTERED用行组通常为100万行和列段组织数COLUMNSTORE INDEXindex_name据，支持批处理模式处理，每次处理多ON table_name非聚集列存储索引行而非单行查询只需访问必要的列，NCCI是表的辅助结构，支持混合显著减少I/O并提高性能，特别是数据OLTP和分析工作负载列存储索引需仓库和分析工作负载要特殊维护考虑，如压缩延迟、行组合并和碎片整理，可通过维护作业自动执行性能优势列存储索引在分析查询中提供显著性能优势，包括数据压缩减少存储需求和I/O；批处理执行提高CPU效率；段消除快速跳过不相关数据；列存储仅读取查询所需列最佳实践包括最小化小批量更新、创建足够大的行组100K行、避免大量单条更新以及定期维护重组索引列存储特别适合星型模式数据集和大型聚合查询多语言支持排序规则支持全球化和本地化Unicode排序规则定义字符数据的SQL Server通过nchar、SQL Server支持多语言应存储、比较和排序规则，nvarchar和ntext数据类型用开发的全球化和本地化影响字符串比较、排序顺提供完整Unicode支持，全球化功能包括不同字序和大小写敏感性SQL能够存储任何语言的字符符集支持、日期和数字格Server使用Windows排序Unicode数据类型使用式处理以及区域特定排序规则，格式为语言_区域_UTF-16编码，每个字符占本地化涉及通过资源文变体_编码，如用2字节或4字节用于补充件和参数化查询适应特定Chinese_PRC_CI_AS中国字符对多语言数据，始语言和文化习惯开发多大陆，不区分大小写，区终应使用n前缀类型，即使语言应用时应考虑字符集分重音排序规则可在服存储空间增加Unicode转换、排序规则兼容性、务器、数据库或列级别设字符串常量需要N前缀，日期/时间/货币格式差异置，影响索引、连接和分如N你好Unicode支持以及右到左语言支持等因组操作选择合适的排序允许在单一数据库中存储素规则对多语言应用至关重和处理多种语言数据要和支持XML JSON1XML数据类型SQL Server提供原生XML数据类型，支持存储和处理结构化XML数据XML列可存储最大2GB的文档，支持类型化符合XML架构或非类型化XMLXML索引提高查询性能，包括主XML索引和三种辅助索引PATH路径定位、VALUE值检索和PROPERTY属性访问XML数据可以使用FOR XML子句从关系数据生成，或使用OPENXML从XML解析为关系数据2XQuery和XPathSQL Server支持标准XQuery和XPath语言查询XML数据XML数据类型提供方法如query、value、exist和nodes执行复杂XML操作query返回XML片段；value提取标量值；exist检查节点存在性；nodes将XML分解为关系形式复杂查询可组合这些方法，如提取特定XPath的所有匹配节点值XQuery支持丰富的函数库和表达式，用于XML处理和转换3JSON函数和索引SQL Server2016起提供内置JSON支持，不使用特殊数据类型而是通过字符串存储核心JSON功能包括FOR JSON生成JSON输出；OPENJSON将JSON转换为表格；JSON_VALUE提取标量值；JSON_QUERY提取对象或数组；ISJSON验证有效性虽然没有专用JSON索引，但可通过计算列和索引实现高效查询，如索引JSON_VALUE提取的属性JSON支持简化了与现代Web应用的集成空间数据SQL Server通过两种空间数据类型支持地理空间功能geometry平面或欧几里得数据和geography地球表面数据这些类型可表示点、线、多边形和更复杂的形状，支持存储位置信息、区域边界、路线和空间关系每种类型都有特定方法集用于创建、验证和分析空间数据空间索引通过网格化机制加速空间查询，将复杂形状分解成更小、易于索引的单元索引配置包括网格密度和细胞覆盖级别，影响性能和准确性空间查询使用专用方法如STDistance计算距离、STIntersects检查相交和STContains检查包含关系，支持查找最近点、确定区域内的对象等操作这些功能适用于GIS应用、位置智能和空间分析全文搜索全文索引全文索引支持高效的文本搜索，超越了LIKE操作符的能力创建全文索引需先定义目录，然后在表的文本列上创建索引索引生成过程包括分词将文本分解为单词、停用词过滤、词干提取识别词根和存储倒排索引全文索引可自动或手动维护，支持多种语言和文档格式，包括通过IFilter提取器处理的外部文档全文查询SQL Server提供多种全文搜索功能CONTAINS支持精确词语、前缀和近似匹配；CONTAINSTABLE返回相关性排名；FREETEXT支持同义词和自然语言查询；FREETEXTTABLE提供相关性排名全文查询支持布尔运算符AND,OR,NOT、邻近搜索NEAR、加权搜索ISABOUT和通配符这些功能可创建复杂的搜索表达式，如特定主题相关文档的加权排名停用词和同义词停用词是常见词如的、是等，从索引中排除以节省空间并提高相关性SQL Server提供默认停用词列表，可通过自定义停用词列表修改同义词扩展搜索能力，允许查询一个词时匹配其同义词同义词词典是XML格式文件，定义词语的等价组适当配置停用词和同义词可显著提高搜索质量和相关性，特别是针对特定领域术语服务器级配置服务器属性内存配置12SQL Server实例级配置通过内存配置是SQL Server性能的关键因sp_configure系统存储过程或SSMS服素最小服务器内存和最大服务器内务器属性页进行管理重要设置包括存设置控制缓冲池大小限制建议为最大并行度MAXDOP控制单一查询操作系统和其他进程保留足够内存至使用的处理器数量；成本阈值并行确少4-10GB大页支持可提高内存密定查询何时使用并行计划；网络配置集型实例性能锁定页内存防止SQL如启用TCP/IP和命名管道；防火墙设Server内存被分页到磁盘，需特殊权置确保所需端口开放；服务账户安全限内存配置应根据工作负载特性、配置符合最小权限原则服务器角色和并发用户数调整并行度设置3并行度配置影响SQL Server如何使用多核处理器最大并行度MAXDOP限制单一查询使用的核心数，通常设置为处理器组中的CPU数量，最大8核成本阈值并行确定查询估计成本何时达到使用并行计划的门槛，默认值5对OLTP系统可能过低并行配置应平衡复杂查询的性能与线程管理开销，OLTP和OLAP系统推荐不同设置数据库级配置数据库级配置通过ALTER DATABASE语句或SSMS中的数据库属性对话框设置，影响单一数据库的行为自动增长设置决定数据和日志文件空间不足时的扩展方式，可配置为固定MB值或百分比增长建议避免使用小增量值如1MB或过大百分比，这可能导致频繁增长或突发大量分配恢复模式对数据保护和性能有重大影响完整模式记录所有事务并支持时间点恢复；大容量日志模式类似但允许最小日志记录的批量操作；简单模式自动截断日志减少维护但限制恢复选项兼容性级别控制数据库的T-SQL行为和新功能可用性，可设置为特定SQL Server版本值升级旧系统时，保持较低兼容性级别可能暂时需要，但应尽快升级以利用新功能和性能改进日志管理错误日志SQL Server错误日志记录服务器事件、系统消息和用户定义错误默认保留7个历史文件，可通过事务日志追踪标记sp_cycle_errorlog手动循环错误日志是故障排除事务日志记录所有数据库修改，支持事务完整性和的关键资源，记录启动问题、资源不足、死锁和安追踪标记启用SQL Server的特定诊断行为或功能变恢复日志采用逻辑日志序列号LSN组织的连续全事件等可通过sp_readerrorlog或SSMS查询错更它们通过DBCC TRACEON命令全局或会话级记录管理事务日志需关注大小、增长、截断和性误日志应建立定期检查错误日志的机制，及时发启用，如DBCC TRACEON1222,-1全局启用死锁能日志应位于专用高性能磁盘上，配置适当初始现潜在问题信息常用标记包括1222详细死锁信息、3605大小和增长设置日志满常见原因包括长时间运行错误输出到错误日志和4199查询优化器修复事务、未执行日志备份和失败备份链中断追踪标记通常用于高级故障排除或在微软支持指导下解决特定问题213统计信息管理自动更新统计信息手动更新统计信息统计信息故障排除SQL Server默认启用自动统计信息创手动更新统计信息对关键表或特定场统计信息问题常表现为查询性能突然建和更新，当查询优化器遇到缺失或景很重要，如大批量数据加载后或自下降或执行计划变更可使用DBCC过时统计信息时触发统计信息更新动更新不足时使用UPDATE SHOW_STATISTICS查看统计信息细阈值基于表大小动态计算小表STATISTICS命令或sp_updatestats存节和更新时间；=500行20%数据变化；中表501-储过程手动更新可以指定sys.dm_db_stats_properties提供统计10,000行500行+表大小10%；大表FULLSCAN获取完整精确的统计信息信息元数据；SET STATISTICSXML10,000行500行+表大小20%自动，或SAMPLE指定抽样率控制资源使ON生成包含基数估计的详细计划常更新有助于维持查询性能，但可能在用统计信息更新通常应安排在非高见修复方法包括手动更新统计信息、高峰期导致意外的优化器编译工作峰时段运行，作为定期维护计划的一调整自动更新阈值、对关键查询使用部分查询提示或在特定情况下使用计划指南固定良好计划链接服务器配置链接服务器链接服务器允许SQL Server访问其他数据源，包括其他SQL Server实例、Oracle、MySQL和Excel等配置通过sp_addlinkedserver存储过程或SSMS服务器对象中的链接服务器节点完成配置参数包括服务器名称、产品名称SQL Server、Oracle等、数据源和目录安全配置至关重要，可使用映射本地登录到远程凭据或设置固定登录，同时考虑委托和Kerberos认证的要求分布式查询链接服务器支持使用四部分名称语法服务器.数据库.架构.对象执行分布式查询例如SELECT*FROM LinkedServer.Database.dbo.TableOPENQUERY函数提供替代语法，允许在远程服务器上执行原生SQL SELECT*FROM OPENQUERYLinkedServer,SELECT*FROM Database.dbo.Table分布式查询性能考虑包括减少数据传输、推送处理到源服务器和避免在连接中使用函数管理链接服务器链接服务器管理包括监控性能、故障排除连接问题和维护安全配置系统视图如sys.servers和sys.linked_logins提供配置信息；可使用sp_testlinkedserver测试连接；sp_serveroption设置行为选项如查询超时和排序规则兼容性常见问题包括防火墙限制、权限不足、网络延迟和数据类型转换错误应记录所有链接服务器配置并定期审核安全设置版本控制和源代码管理数据库版本控制数据库版本控制将架构和参考数据变更纳入可控流程，类似应用程序代码版本控制数据库对象如表、存储过程、函数和视图以脚本形式存储在源代码管理系统中提供变更历史、回滚能力、开发者协作支持和审核跟踪版本控制模式包括状态模型完整架构脚本和迁移模型增量变更脚本，各有优劣适用于不同场景源代码管理集成SQL Server可与多种源代码管理系统集成，包括Git、Azure DevOps和SVN等SSMS提供基本源代码管理集成；SQL ServerData ToolsSSDT和Visual Studio提供更完整的数据库项目支持，包括验证、重构和比较工具集成流程包括签入/签出、变更跟踪、分支管理和合并冲突解决等有效集成需要工作流程规范和团队工具使用标准部署脚本生成自动部署脚本生成是有效数据库DevOps的关键工具如SSDT、RedGate SQLCompare和ApexSQL可比较源与目标数据库，生成精确的部署脚本部署考虑包括幂等性可重复执行、数据保留、回滚脚本创建和环境特定配置脚本应处理边缘情况如外键约束、依赖对象顺序和潜在数据转换自动化部署通过CI/CD管道简化发布流程，提高可靠性数据库快照创建数据库快照使用数据库快照快照的限制数据库快照是数据库在特定数据库快照主要用途包括数据库快照存在多种限制时间点的只读静态视图，使提供一致的报表数据源，避仅适用于读取工作负载，不用写时复制技术实现创建免长时间运行报表锁定生产支持写入；随着源数据库变语法为CREATE DATABASE表；数据比较和审计，分析化增长，快照可能显著增大snapshot_name ON特定时间段的数据变化；测；快照不可备份，依赖源数NAME=logical_name,试环境中快速重置数据，通据库的完整性；源数据库还FILENAME=path AS过从快照恢复；备份前数据原操作需先删除所有相关快SNAPSHOT OF库验证，确保逻辑一致性；照；不支持某些功能如全文source_database快照只作为修改前的安全网，允许索引和文件流数据；要求企存储自创建以来原始数据库在复杂更新出错时快速恢复业版SQL Server2016SP1更改的页面，初始创建几乎访问快照与普通数据库相前快照不能替代常规备份瞬时快照必须位于与源数同，但仅支持只读操作策略，因为它们与源数据库据库相同的服务器实例上共享相同的物理风险多个快照可表示同一数据库在不同时间点的状态资源管理器管理文件和文件组收缩数据库管理日志文件123SQL Server使用文件和文件组组织物理存数据库收缩通过DBCC事务日志文件管理影响性能和可靠性日储主数据文件.mdf包含启动信息和系SHRINKDATABASE或DBCC SHRINKFILE志文件应放置在专用高性能磁盘上，与数统表；次要数据文件.ndf存储用户数据；减小数据库文件大小虽然可回收磁盘空据文件分离初始大小应足够容纳正常工日志文件.ldf记录事务通过ALTER间，但应谨慎使用，因为收缩会导致严重作负载，避免频繁增长日志文件增长是DATABASE命令或SSMS文件属性页可添的索引碎片，可能显著影响性能更好的串行操作，可能导致性能暂停监控日志加文件、调整大小或修改属性文件组是方法是正确预估和分配空间，设置适当的空间使用和虚拟日志文件VLF数量很重要文件的逻辑分组，用于数据分配、备份和自动增长参数，避免频繁收缩如必须收，过多VLF会影响恢复性能当日志文件恢复合理的文件和文件组策略考虑性能缩，应在低活动时段执行，并跟进重建受过大时，可考虑备份日志、重设大小然后I/O分布、可管理性和可用性需求影响的索引重建更合理的日志文件策略管理创建策略评估策略强制执行策略SQL Server策略管理Policy-Based策略评估可以是按需、按计划或基于更改事策略执行模式决定发现违规时的行为强制Management提供自动化数据库环境管理机件触发按需评估通过SSMS手动执行，验模式自动防止不合规操作；审计模式仅记录制创建策略首先需要定义条件如服务器属证当前合规性；按计划评估通过SQL Server违规但允许操作继续；现场时强制模式在对性规范，然后创建基于该条件的策略，设置Agent作业定期运行；事件触发评估在相关象创建或修改时检查；计划时强制通过作业评估模式和目标SSMS提供图形界面进行更改尝试时自动执行评估结果可以记录到检查并可自动修复最佳实践包括从审计模策略定义，或可使用系统存储过程编程实现表中用于历史审计，或导出为XML或HTML式开始，验证策略准确性后再转为强制模式策略范围可从服务器到特定对象，条件可报告policy_health表存储所有策略评估的；使用策略类别组织相关策略；为不同环境包括属性比较、命名规则和配置标准等详细历史记录，便于趋势分析维护单独的策略集数据库邮件故障排除发送电子邮件数据库邮件问题常见原因包括网络连接困难、配置数据库邮件SQL Server提供多种发送邮件的方式SMTP服务器限制和权限问题故障排除步骤包括数据库邮件是SQL Server的内置电子邮件系统，用sp_send_dbmail存储过程是主要方法，支持纯文本检查msdb数据库中的sysmail_event_log和于从数据库引擎发送消息配置步骤包括启用数或HTML格式，可包含查询结果和附件；SQL sysmail_failed_items表获取详细错误信息；验证据库邮件功能；创建邮件配置文件和账户；设置Agent提供作业通知，可在作业成功、失败或完成SMTP设置是否正确，包括服务器、端口和认证方SMTP服务器信息如服务器名称、端口和身份验证时发送通知；数据库监视器可配置发送特定事件警式；确认服务器防火墙允许SQL Server访问SMTP；配置安全性和访问权限可通过SSMS配置向导报邮件常见应用场景包括任务完成通知、错误报端口；测试SMTP服务器是否接受SQL Server使用或使用sysmail_xxx存储过程进行设置企业应考告、阈值警报、自动报表分发和管理员通知等的账户和发送接收限制复杂环境可考虑使用中继虑建立专用邮件账户用于数据库通知，确保足够权服务器限控制和审计服务代理对话和消息服务代理通信基于对话，是应用程序之间基于消息的持久双向会话对话使用BEGIN DIALOG命令启动，并通过SEND ONCONVERSATION发送消息消息保证按发送顺序交付，且系统能处理网络中断和服务器服务代理架构2故障服务代理自动维护对话状态，跟踪已发送和确SQL Server服务代理是可靠的异步消息和队列处理认的消息，支持各种消息类型如对话控制、定时器和框架它支持分布式应用程序之间的松散耦合通信自定义应用程序消息，保证消息传递即使在系统故障期间也能可靠完成1核心组件包括消息类型定义消息格式；对话通信安全性考虑是应用程序间的双向会话；队列存储消息直到处理服务代理安全基于多层模型对话安全使用证书或加；服务定义消息处理规则；激活器自动启动存储过密密钥保护消息；传输安全通过网络协议加密保护数程处理新消息3据传输；访问控制管理队列和服务的用户权限最佳实践包括使用证书而非密码验证远程服务；为服务代理对象创建专用角色；使用加密保护敏感数据；详细记录远程服务设置；定期更新证书；以及实施最小权限原则减少攻击面动态数据屏蔽配置数据屏蔽屏蔽函数权限和限制动态数据屏蔽DDM在查询结果中隐藏敏感数SQL Server提供四种预定义屏蔽函数屏蔽权限通过UNMASK权限控制，具有此权据，而不修改底层数据配置通过ALTER default对字符串显示xxxx，数字显示零，日限的用户可查看完整原始数据系统管理员TABLE语句添加屏蔽规则，如ALTER TABLE期使用

01.

01.1900；email显示格式如sysadmin和数据库所有者db_owner自动Customers ALTERCOLUMN aXXX@XXXX.com保留首字母；拥有UNMASK权限授予方式如GRANTCreditCardNumber ADDMASKED WITHpartialprefix,padding,suffix保留前缀和后缀UNMASK TOUserNameDDM存在一些限FUNCTION=partial0,XXXX-XXXX-XXXX-，中间替换为填充字符；random对数字列显制不适用于高安全性要求，因为有多种技,4新表创建可在列定义中直接包含示随机值屏蔽规则可根据数据类型和安全术可能绕过屏蔽；不影响WHERE子句的过滤MASKED WITH子句DDM影响SELECT查询需求选择，可自定义屏蔽格式控制信息显示，可能通过尝试不同值推断数据；列复制会结果，但不影响数据写入或物理存储程度复制原始数据而非屏蔽值行级安全性启用行级安全性创建安全策略性能影响行级安全性RLS允许基于执行查询的创建函数后，使用CREATE SECURITYRLS在查询处理中添加额外筛选条件用户身份限制数据行的访问实现POLICY语句将其应用于表语法包括，可能影响性能影响程度取决于安RLS的第一步是创建表值函数，定义指定目标表、筛选函数和安全状态启全函数复杂性、表大小和查询模式行访问的筛选逻辑函数参数通常包用/禁用例如CREATE SECURITY优化技巧包括保持筛选函数简单，括SESSION_CONTEXT中的用户信息POLICY SalesFilterADD FILTER避免复杂计算或连接；确保安全函数或USER_NAME系统函数，返回满足PREDICATE中引用的列有适当索引；监控执行计条件的行标识符可为同一表定义多dbo.fn_SalesFilterSalesRepID ON划识别RLS引起的性能问题；考虑使个安全函数，适应不同的安全要求Sales.Orders WITHSTATE=ON用索引视图或分区作为替代方法；在启用RLS时表现有数据无需修改，安策略可以包含FILTER PREDICATE限批量导入等特殊操作期间临时禁用安全逻辑对应用程序透明制读取和BLOCK PREDICATE限制写全策略以提高性能入可以将多个谓词应用于同一表，实现复杂的安全规则组合临时表和表变量本地和全局临时表1临时表是会话期间存在的临时对象，存储在tempdb数据库中本地临时表名以单井号#开头，仅在创建会话可见，会话结束自动删除全局临时表名以双井号##开头，对所有会话可见，在所有引用会话关闭后删除临时表支持索引、约束和统计信息，行为类似永久表优势包括减少锁争用、隔离会话数据和避免日志开销，常用于存储查询中间结果表变量2表变量使用DECLARE语句在T-SQL代码中定义的内存表结构语法为DECLARE@TableName TABLEcolumn definitions表变量作用域限于定义它们的批处理、存储过程或函数，执行结束自动释放它们支持索引、主键和唯一约束，但不支持外键或CHECK约束表变量不维护完整统计信息，优化器通常假设它们只包含少量行使用场景和性能考虑3临时表适合存储大量中间数据100行；需要复杂索引支持；需要在多个存储过程间传递数据；需要在创建后动态修改结构表变量适合存储少量数据；降低事务日志负担；减少锁和锁定争用；需要在用户定义函数中使用表结构SQL Server2019后，表变量可利用延迟编译功能，根据实际行数生成更优执行计划，减少了两者性能差异查询存储分析查询性能查询存储提供多个内置报表分析性能监管查询显示资源消耗最高的查询；执行计划比较识别计划变更；强制计划用例识别回归；等待统计显示主要瓶颈系统视图如sys.query_store_query和sys.query_store_plan提供启用查询存储编程访问，支持自定义分析常见使用场景包括确定资2查询存储是SQL Server内置的查询性能监控和分析工源密集型查询，诊断性能波动，以及跟踪查询参数化和具，收集查询执行统计信息和执行计划通过ALTER计划选择随时间变化DATABASE语句启用ALTER DATABASE1DatabaseName SETQUERY_STORE=ON，可配置多个参数如数据刷新间隔、保留策略、采集模式和存储大强制执行计划小限制建议采用读写模式确保持续收集，设置足够查询存储的关键功能是强制执行特定计划，解决计划选存储空间通常1-10GB，并配置适当清理策略以平衡择不稳定问题使用sp_query_store_force_plan指定查历史数据可用性和存储需求3询ID和计划ID，或通过SSMS图形界面从历史计划中选择强制计划尤其适用于参数嗅探问题，不同参数值导致不同计划质量的情况它比传统的查询提示或计划指南更易于管理，可随时启用或禁用，并提供计划强制效果监控，便于评估干预的有效性延迟持久性配置延迟持久性性能影响延迟持久性允许SQL Server在确认事务提交延迟持久性通过批处理和异步化日志IO显著前不立即将事务日志写入磁盘，提高性能提高事务吞吐量性能改进在以下场景最明配置选项包括数据库级别设置ALTER显大量小型事务；频繁提交；IO子系统瓶DATABASE SETDELAYED_DURABILITY=颈；日志写入延迟较高测试表明某些工作{DISABLED|ALLOWED|FORCED}；事务级负载可实现5-10倍吞吐量提升，尤其是有大别控制BEGIN TRANSACTIONWITH量单行OLTP事务的应用程序性能改进程度DELAYED_DURABILITY={ON|OFF}；或实取决于工作负载特性、硬件配置和并发级别例级别配置sp_configure delayed_durability ALLOWED模式最灵活，允许按事务决定，适合混合工作负载环境数据丢失风险延迟持久性的主要权衡是增加了数据丢失风险服务器崩溃可能导致已确认但未写入磁盘的事务丢失这种风险可通过几种方式管理限制使用于可接受一定数据丢失的应用；配置合理的COMMIT_DELAY和COMMIT_SIZE参数控制延迟时间；使用事务复制或AlwaysOn提供额外保护；以及为关键事务保留完全持久性选项业务需求应决定是否适合使用此功能缓冲池扩展配置缓冲池扩展监控和管理性能优化123缓冲池扩展BPE允许SQL Server使用快速固BPE性能可通过多个动态管理视图监控BPE最适合内存压力大但I/O子系统成为瓶颈态硬盘SSD扩展内存缓冲池，为数据页提供sys.dm_os_buffer_pool_extension_configura的工作负载性能改进在以下场景最明显处二级缓存配置通过sp_configure启用，然后tion显示配置信息；理超过内存容量的工作集；读密集型查询混合使用ALTER SERVERCONFIGURATION设置文sys.dm_os_buffer_descriptors包含缓冲页位随机访问模式；内存中能容纳80-90%高频访件路径和大小ALTER SERVER置和使用情况；性能计数器如Buffer问数据，剩余部分可存储在扩展区不适合的CONFIGURATION SETBUFFER POOLManager:Buffer PoolExtension page场景包括完全内存容纳的工作集或已CPU绑定EXTENSION FILENAME=E:\BPE\bpe.bpe,reads/sec跟踪扩展活动BPE文件创建后不的工作负载应监控页面交换率和读取延迟以SIZE=32GB文件应位于低延迟SSD上，能调整大小，需要禁用然后使用新大小重新配评估BPE有效性大小通常设置为物理内存的1-4倍，但不超过置数据库维护活动如备份和CHECKDB可能32倍，最大限制为32TB导致大量页面读入内存，应考虑调整维护计划避免缓冲池污染资源健康SQL Server资源健康监控提供服务器的实时和历史健康状态信息系统健康会话是SQL Server自动创建和启动的扩展事件会话，收集对故障排除和诊断有价值的信息它捕获严重错误、内存不足事件、死锁、长时间运行的查询和等待统计信息等重要数据，无需额外配置即可使用资源健康视图如sys.dm_os_sys_info、sys.dm_os_process_memory和sys.dm_os_ring_buffers提供系统资源使用情况和健康状态这些视图可识别内存压力、CPU瓶颈、IO延迟和资源争用等问题结合扩展事件和动态管理视图的数据可构建全面的监控解决方案，从物理资源到SQL Server内部组件，提供多维度健康视图定期检查资源健康指标对预防问题和维持最佳性能至关重要智能查询处理1批处理模式内存授予反馈动态调整内存分配以改进后续执行2批处理模式自适应连接在执行期间切换连接算法3交错执行并行处理多表引用以减少内存需求4表变量延迟编译基于实际行数而非默认估计生成计划SQL Server的智能查询处理IQP是一组自适应查询处理技术，使查询执行更加智能和高效这些功能在SQL Server2017开始引入，在2019和更高版本中进一步扩展IQP的核心理念是使执行计划在运行时能够根据实际数据特征和系统状况进行自适应调整，而不是仅依赖编译时的静态估计启用IQP需将数据库兼容性级别设置为150SQL Server2019或更高无需修改应用程序代码，现有查询自动受益这些功能解决了传统优化器的多项限制，如不准确的基数估计、静态执行策略和次优的内存分配，显著提高了复杂查询的性能和稳定性，减少异常值查询的影响对于管理混合工作负载的数据库管理员来说，IQP是提升整体系统性能的强大工具数据库范围设置MAXDOP设置最大并行度MAXDOP控制单一查询可使用的处理器数量现代SQL Server支持三级MAXDOP配置实例级sp_configure设置全局默认值；数据库级ALTER DATABASESCOPEDCONFIGURATION覆盖实例设置；查询级OPTION MAXDOPn针对特定查询设置OLTP工作负载推荐MAXDOP设置为2-4；数据仓库工作负载可使用更高值如8-16；混合工作负载需要平衡设置，通常4-8成本阈值并行成本阈值并行是决定查询是否使用并行计划的估计成本界限默认值5较低，导致许多小型查询使用并行计划，可能造成线程和资源争用OLTP系统通常应提高此值到50-100，减少不必要并行；数据仓库可保持较低设置5-25，鼓励并行执行此设置现仅可在实例级配置，应根据系统CPU资源、并发用户数和工作负载类型调整查询优化器热修复QUERY_OPTIMIZER_HOTFIXES也称为追踪标记4199控制是否启用查询优化器行为更改SQL Server2016引入数据库范围配置，允许在数据库级别控制此设置ALTERDATABASE SCOPEDCONFIGURATION SETQUERY_OPTIMIZER_HOTFIXES={ON|OFF}启用后，数据库将使用最新的优化器改进，可能提高查询性能，但也可能改变执行计划建议在测试环境验证后再在生产环境启用数据库生命周期管理开发环境管理1支持开发流程的环境配置与管理测试和质量保证2验证变更的系统化过程生产部署策略3最小化风险的发布管理方法数据库生命周期管理DLM是一套实践和流程，用于管理数据库从开发到部署再到废弃的整个生命周期开发环境管理包括建立标准化的开发环境，使用源代码控制管理架构变更，实施开发者沙箱环境，以及使用自动化工具生成测试数据开发环境应模拟生产环境主要特性，但可以放宽某些限制以提高开发效率测试和质量保证流程涉及多个环境级别集成测试验证各组件协同工作；性能测试确认系统满足性能要求；用户验收测试确保功能符合业务需求应建立自动化测试套件验证数据完整性、安全控制和业务规则生产部署策略应包括详细的部署计划、回滚流程、停机窗口规划和事后分析蓝绿部署和金丝雀发布等技术可用于降低风险完整的DLM实践将大幅提高数据库变更的可靠性和效率性能基准测试执行基准测试基准测试执行需要系统化方法和适当工具常用工具包括SQL ServerDistributed Replay、ostress、HammerDB和自定义脚本测试应在隔离环境中进行，避免外部因素干扰执行步骤包括系统准备清设计基准测试2除缓存、重建索引、预热阶段填充缓存和测量阶有效的基准测试始于明确目标，如验证系统是否段收集指标应进行多次迭代以获得统计显著性满足SLA、比较不同硬件配置或评估软件升级影响，并测试不同负载级别以确定容量限制设计应包括工作负载特征分析、关键场景识别1和测试指标定义工作负载模型应反映实际使用分析结果模式，包括查询类型分布、并发用户数、数据量结果分析应超越简单的吞吐量和响应时间比较应和访问模式测试数据应具有代表性，涵盖各种收集详细指标如CPU使用率、内存消耗、磁盘IO、边缘情况和数据分布特征3等待统计和执行计划统计使用基线比较识别偏差和性能退化分析应关注瓶颈识别、资源限制点和可扩展性特征结果文档应包括测试条件、配置详情、原始数据和建议这些信息为优化决策和容量规划提供基础故障排除方法论系统性故障排除步骤常见问题和解决方案使用系统动态管理视图有效的SQL Server故障排除遵循结构化方法1性能问题通常源于几个核心领域CPU瓶颈优化动态管理视图DMV是SQL Server故障排除的核定义问题，收集症状描述、时间点和影响范围；查询、调整并行度、检查过度编译；内存压力心工具关键DMV类别包括sys.dm_exec_*执2收集证据，包括错误消息、事件日志和性能计增加内存、优化查询内存使用、检查内存泄漏行相关提供查询执行和计划信息；数器；3分析数据，识别模式和潜在原因；4制；IO延迟优化存储配置、调整索引、实施数据压sys.dm_os_*操作系统相关显示内存、调度器定假设，解释观察到的症状；5测试假设，通过缩；锁定和阻塞调整事务范围、检查死锁、优和等待统计；sys.dm_io_*IO相关监控文件和虚配置更改或实验验证；6实施解决方案，监控效化隔离级别；编译和重编译使用参数化查询、拟文件活动；sys.dm_db_*数据库相关监控索果；7记录发现和解决过程，防止问题重现调整计划缓存、检查自动参数化引使用和统计信息；sys.dm_tran_*事务相关跟踪锁和事务状态组合多个DMV的查询可提供强大的诊断能力课程总结关键要点回顾最佳实践进一步学习资源123本课程全面介绍了SQL Server数据库管理成功的SQL Server管理基于几项关键最佳SQLServer技术不断发展，持续学习至关的核心概念和技术我们从基础架构和安实践定期备份和测试恢复流程确保数据重要推荐资源包括微软官方文档和学装开始，依次探讨了数据库对象管理、索安全；实施多层安全策略保护敏感数据；习路径；SQLServer技术博客如引优化、事务控制、备份恢复策略、安全建立性能基线和监控系统及时发现问题；SQLServerCentral和SQLBlog；专业书籍管理、高可用性方案和性能调优等关键领遵循结构化变更管理流程减少风险；定期如《SQLServer内部原理》和《高性能域尤其强调了SQLServer的高级功能如维护任务如索引和统计信息更新；以及持SQLServer》；在线培训平台如内存中OLTP、列存储索引、查询存储和智续学习与评估新功能这些实践共同构成Pluralsight和Microsoft Learn；以及社区能查询处理，它们为现代数据库应用提供了高效、可靠数据库环境的基础活动如PASS Summit和SQL Saturday加了卓越性能和可靠性入SQLServer专业社区，参与讨论组和论坛，可获取实时支持和经验分享。