拓冰网站定制 
1、角色和权限1.1、固定数据库角色权限对照表角色名称核心权限典型应用场景重要备注db_accessadmin可以添加或删除数据库用户的访问权限即管理数据库用户。负责管理数据库用户账号的管理员。不能授予服务器级别权限仅限当前数据库的用户管理。db_backupoperator可以执行数据库备份操作BACKUP DATABASE和BACKUP LOG。负责定期备份数据库的运维人员。无法读取数据或恢复数据库仅备份权限。db_datareader可以读取所有用户表中的数据SELECT权限。报表查询、只读分析、数据导出。不能修改数据也不能查看表结构定义。db_datawriter可以对所有用户表的数据执行增、删、改INSERT、UPDATE、DELETE。数据录入、内容管理、ETL 工具。通常需要与db_datareader组合使用否则无法查询数据。db_ddladmin可以执行所有数据定义语言DDL如创建、修改、删除表、视图、存储过程、架构等。数据库迁移、应用部署、架构变更。不自动拥有数据读取或修改权限也不能管理用户权限。db_denydatareader显式拒绝读取所有用户表数据的权限DENY SELECT。用于覆盖其他角色赋予的读取权限强制禁止读数据。该权限会覆盖db_datareader等授予的权限通常用于安全隔离。db_denydatawriter显式拒绝对任何用户表进行数据修改的权限DENY INSERT、UPDATE、DELETE。用于覆盖其他角色赋予的写入权限强制禁止修改数据。会覆盖db_datawriter的权限确保用户无法更改数据。db_owner拥有数据库的完全控制权CONTROL DATABASE可执行所有配置、维护和权限管理。数据库管理员DBA用于全面管理数据库。权限极大应严格限制授予范围。⚠️ 谨慎授予db_securityadmin可以管理数据库的权限包括授予、拒绝或撤销权限以及管理角色成员身份。权限管理员负责分配数据库访问权限。无法管理数据库架构或数据专注于安全性。public每个数据库用户默认所属的内置角色拥有最基础的权限通常为VIEW ANY DATABASE等。所有用户自动拥有该角色用于授予全局最低权限。不能被删除权限应最小化避免给public授予敏感权限。最佳实践总结原则说明最小权限只授予用户完成工作所需的最少权限优先使用角色固定数据库角色管理简单权限清晰便于维护谨慎使用db_owner完全控制权限极大仅授予少数核心管理员理解优先级DENYGRANT显式授予 角色授权定期审查定期检查用户权限移除不再需要的授权1.2 SQL Server 数据库级权限分类详解数据操作权限 (DML - 操作数据)权限用途使用场景关键说明SELECT读取一个或多个表中的数据。报表应用、数据查询、只读用户。最基础的权限之一。建议优先使用db_datareader角色批量授予。INSERT向表中添加新行。数据录入应用、日志记录。授予此权限意味着用户可以插入数据但无法读取或修改已有数据。UPDATE修改表中现有行的数据。数据更新、内容管理应用。常与SELECT权限一同授予。DELETE从表中删除行。数据清理、归档应用。风险较高需谨慎授予。REFERENCES创建外键约束时引用该表。需要创建表间关联关系的开发者。通常授予开发者或架构师。最佳实践组合对于大多数应用账号授予db_datareader和db_datawriter两个角色即可获得完整的“增删改查”权限结构管理权限 (DDL - 管理对象)权限用途使用场景关键说明CREATE TABLE在数据库中创建新表。应用部署、数据迁移脚本。风险较高应限制在开发或部署环境。CREATE VIEW创建新视图。为报表或应用创建专用数据视角。同CREATE TABLE需谨慎授予。CREATE PROCEDURE创建新的存储过程。封装业务逻辑、提高性能。风险较高可能被用于执行任意代码。CREATE FUNCTION创建新的用户定义函数。封装可复用的逻辑。风险同存储过程。ALTER修改任何安全对象的属性或定义。修改表结构、调整索引等维护任务。权限极大。在架构级别授予ALTER用户可创建/修改/删除该架构下任何对象。DROP删除安全对象。清理过期对象。风险极高可能导致数据丢失。CONTROL授予类似对象“所有者”的全部权限。将对象的管理权委托给其他人。权限极大。在数据库级别授予CONTROL用户拥有该库的所有权限。最佳实践通过db_ddladmin角色可授予用户管理数据库对象结构的全部权限适合需要执行部署或架构变更的账号。对于普通应用账号不应授予任何CREATE或ALTER权限安全与权限管理权限权限用途使用场景关键说明GRANT授予其他用户权限。需要协助管理权限的“二级管理员”。权限极大通常只授予数据库所有者dbo或db_securityadmin角色的成员。DENY拒绝其他用户的权限。强制执行安全策略覆盖其他授权。DENY的优先级高于GRANT即使用户通过角色获得了权限也会被拒绝。REVOKE移除之前授予或拒绝的权限。回收不再需要的权限。使权限回到未定义状态此时用户是否拥有权限取决于角色或其他授权。IMPERSONATE允许模拟其他用户的身份执行操作。高级故障排查、审计场景。风险极高应严格限制。最佳实践通过db_securityadmin角色可授予用户管理数据库权限的权限。普通用户不应拥有管理权限的能力。总结与实践建议将上述原则应用到你的场景常规应用账号授予db_datareaderdb_datawriter角色即可。部署/发布账号考虑授予db_ddladmin角色。数据库管理员根据职责考虑db_owner或自定义角色。如果固定角色无法满足需求可以创建自定义数据库角色将一组精细权限打包授予便于管理和复用。最后务必遵循以下最佳实践定期审查定期检查并回收不必要的权限。避免使用sa禁止应用使用sa等高特权账号。职责分离将数据操作DML和结构变更DDL权限分给不同账号2、用户角色管理2.1 核心方法概览方法适用场景特点通过固定数据库角色标准权限需求读写、只读等管理简单、权限清晰推荐优先使用通过GRANT语句精细授权需要对特定表、视图或存储过程单独授权权限粒度最细灵活但管理复杂通过DENY语句拒绝权限强制禁止特定操作覆盖其他授权用于安全隔离优先级最高方法一通过角色赋权标准方式常用角色组合及命令-- 切换到目标数据库 USE [DSCM_STD]; GO -- 1. 只读权限仅查询数据 ALTER ROLE [db_datareader] ADD MEMBER [user1]; GO -- 2. 读写权限查询 增删改 ALTER ROLE [db_datareader] ADD MEMBER [user2]; ALTER ROLE [db_datawriter] ADD MEMBER [user2]; GO -- 3. 结构修改权限可创建/修改/删除表等 ALTER ROLE [db_ddladmin] ADD MEMBER [user3]; GO -- 4. 完全控制权限数据库所有者 ALTER ROLE [db_owner] ADD MEMBER [user4]; GO方法二通过 GRANT 语句精细授权常用精细授权命令-- 切换到目标数据库 USE [DSCM_STD]; GO -- 1. 授予对特定表的查询权限 GRANT SELECT ON [dbo].[Employees] TO [user1]; GRANT SELECT, INSERT, UPDATE ON [dbo].[Orders] TO [user2]; -- 2. 授予对特定视图的查询权限 GRANT SELECT ON [dbo].[v_ActiveUsers] TO [user3]; -- 3. 授予对特定存储过程的执行权限 GRANT EXECUTE ON [dbo].[sp_GenerateReport] TO [user4]; -- 4. 授予对特定架构的所有权限 GRANT CONTROL ON SCHEMA::[Sales] TO [user5]; -- 5. 授予创建表的权限细粒度 GRANT CREATE TABLE TO [user6];方法三通过 DENY 拒绝权限覆盖所有授予-- 拒绝用户读取某张敏感表即使他是 db_datareader DENY SELECT ON [dbo].[Salaries] TO [user1]; -- 拒绝用户修改某张表的数据 DENY INSERT, UPDATE, DELETE ON [dbo].[Financials] TO [user2]; -- 拒绝用户执行某个存储过程 DENY EXECUTE ON [dbo].[sp_DeleteAllData] TO [user3];权限管理辅助命令-- 查看用户所属的数据库角色 SELECT r.name AS RoleName FROM sys.database_role_members rm JOIN sys.database_principals r ON rm.role_principal_id r.principal_id JOIN sys.database_principals m ON rm.member_principal_id m.principal_id WHERE m.name user1; -- 查看用户对特定对象的权限 SELECT * FROM fn_my_permissions(dbo.Employees, OBJECT); --移除权限 -- 从角色中移除用户 ALTER ROLE [db_datareader] DROP MEMBER [user1]; -- 撤销直接授予的权限移除 GRANT REVOKE SELECT ON [dbo].[Employees] FROM [user1]; -- 移除拒绝权限取消 DENY REVOKE DENY SELECT ON [dbo].[Salaries] FROM [user1];-- 查看当前数据库所有角色成员 EXEC sp_helpuser; -- 将用户添加到某个角色 ALTER ROLE [db_datareader] ADD MEMBER [YourUser]; -- 将用户从角色移除 ALTER ROLE [db_datareader] DROP MEMBER [YourUser];2.2实战案例对 DSCM_STD 库 映射多个用户user1user2对user1 赋于db_datareaderdb_datawriterdb_ddladmin对user2 赋于 db_owner-- -- 1. (可选) 在服务器级别创建登录名如果已存在可跳过 -- 请将 YourStrongPassword! 替换为强密码 -- USE [master]; GO -- 如果登录名 user1 不存在则创建建议先检查是否存在 IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name user1) CREATE LOGIN [user1] WITH PASSWORD YourStrongPassword!; GO IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name user2) CREATE LOGIN [user2] WITH PASSWORD YourStrongPassword!; GO -- -- 2. 切换到目标数据库 DSCM_STD -- USE [DSCM_STD]; GO -- -- 3. 创建数据库用户并映射到登录名 -- -- 如果用户已存在先删除谨慎操作生产环境需先确认 -- DROP USER IF EXISTS [user1]; -- DROP USER IF EXISTS [user2]; CREATE USER [user1] FOR LOGIN [user1]; CREATE USER [user2] FOR LOGIN [user2]; GO -- -- 4. 为 user1 授予角色只读、读写、DDL 管理员 -- ALTER ROLE [db_datareader] ADD MEMBER [user1]; ALTER ROLE [db_datawriter] ADD MEMBER [user1]; ALTER ROLE [db_ddladmin] ADD MEMBER [user1]; GO -- -- 5. 为 user2 授予 db_owner 角色拥有数据库完全控制权 -- ALTER ROLE [db_owner] ADD MEMBER [user2]; GO -- -- 6. 查看已授予的角色确认结果 -- SELECT u.name AS UserName, r.name AS RoleName FROM sys.database_role_members rm JOIN sys.database_principals r ON rm.role_principal_id r.principal_id JOIN sys.database_principals u ON rm.member_principal_id u.principal_id WHERE u.name IN (user1, user2) ORDER BY u.name;3、备份库# # SQL Server 数据库备份脚本 # 功能: 备份数据库 # # 请修改以下配置 $sqlServer localhost # SQL Server实例名 $sqlUser bakUser # SQL用户名 $sqlPassword ************ # SQL密码 $useWindowsAuth $true # trueWindows认证, falseSQL认证 $backupDir D:\DBBackup # 备份目录 $databases (DSCM_STD,DSCM_STD_Log,Mango-Server-DSCM) # 可以指定多个数据库名 # 日志函数 function Log { param([string]$message) $timestamp Get-Date -Format yyyy-MM-dd HH:mm:ss Write-Host [$timestamp] $message } # 主程序 # 创建备份目录 if (!(Test-Path $backupDir)) { New-Item -ItemType Directory -Path $backupDir -Force | Out-Null Log 创建备份目录: $backupDir } Log 备份开始 # 获取时间戳 $timestamp Get-Date -Format yyyyMMdd_HHmmss # 测试数据库连接 Log 测试数据库连接... try { if ($useWindowsAuth) { Invoke-Sqlcmd -ServerInstance $sqlServer -Query SELECT 1 -QueryTimeout 30 -ErrorAction Stop | Out-Null } else { Invoke-Sqlcmd -ServerInstance $sqlServer -Username $sqlUser -Password $sqlPassword -Query SELECT 1 -QueryTimeout 30 -ErrorAction Stop | Out-Null } Log 数据库连接成功 } catch { Log 数据库连接失败: $_ exit 1 } # 备份每个数据库 $successCount 0 $failCount 0 foreach ($db in $databases) { Log ---------------------------------------- Log 开始备份数据库: $db $bakFile $backupDir\$db_$timestamp.bak # 备份SQL $backupSql BACKUP DATABASE [$db] TO DISK N$bakFile WITH FORMAT, STATS10 # 执行备份 try { $startTime Get-Date if ($useWindowsAuth) { Invoke-Sqlcmd -ServerInstance $sqlServer -Query $backupSql -QueryTimeout 0 -ErrorAction Stop } else { Invoke-Sqlcmd -ServerInstance $sqlServer -Username $sqlUser -Password $sqlPassword -Query $backupSql -QueryTimeout 0 -ErrorAction Stop } $endTime Get-Date $duration [math]::Round(($endTime - $startTime).TotalSeconds, 2) # 检查备份文件 if (Test-Path $bakFile) { $bakSize [math]::Round((Get-Item $bakFile).Length / 1MB, 2) Log 备份成功: $db_$timestamp.bak (${bakSize}MB, 耗时 ${duration}秒) $successCount } else { Log 备份失败: 文件未生成 $failCount } } catch { Log 备份数据库 $db 失败: $_ $failCount } } Log 备份完成 Log 统计: 成功 $successCount 个, 失败 $failCount 个 if ($failCount -gt 0) { exit 1 } else { exit 0 }4、定时任务设置4.1 创建定时任务脚本使用 Transact-SQL (T-SQL) 脚本这种方法适合批量创建、自动化部署或版本控制。你需要在 msdb 系统数据库中执行一系列系统存储过程。以下是一个完整的脚本示例它创建了一个名为 Weekly Sales Data Backup 的作业USE msdb; GO -- 1. 创建作业 EXEC dbo.sp_add_job job_name NWeekly Sales Data Backup; -- 作业名称 GO -- 2. 为作业添加步骤 EXEC sp_add_jobstep job_name NWeekly Sales Data Backup, step_name NSet database to read only, -- 步骤名称 subsystem NTSQL, -- 步骤类型TSQL表示执行T-SQL command NALTER DATABASE SALES SET READ_ONLY;, -- 执行的命令 retry_attempts 5, -- 失败重试次数 retry_interval 5; -- 重试间隔分钟 GO -- 3. 创建并附加计划 EXEC dbo.sp_add_schedule schedule_name NRunOnce, -- 计划名称 freq_type 1, -- 1 表示只执行一次 active_start_time 233000; -- 开始时间 23:30:00 GO EXEC sp_attach_schedule job_name NWeekly Sales Data Backup, schedule_name NRunOnce; GO -- 4. 将作业添加到当前服务器目标服务器 EXEC dbo.sp_add_jobserver job_name NWeekly Sales Data Backup; GO -- USE msdb; GO -- -- 1. 删除作业如果存在 -- IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name NWeekly Sales Data Backup) BEGIN EXEC msdb.dbo.sp_delete_job job_name NWeekly Sales Data Backup; PRINT 作业 Weekly Sales Data Backup 已成功删除。; END ELSE BEGIN PRINT 作业 Weekly Sales Data Backup 不存在无需删除。; END GO -- -- 2. 删除计划 RunOnce如果存在且未被其他作业使用 -- DECLARE schedule_id INT; SELECT schedule_id schedule_id FROM msdb.dbo.sysschedules WHERE name NRunOnce; IF schedule_id IS NOT NULL BEGIN -- 检查该计划是否还被其他作业使用排除已删除的作业 IF NOT EXISTS ( SELECT 1 FROM msdb.dbo.sysjobschedules js INNER JOIN msdb.dbo.sysjobs j ON js.job_id j.job_id WHERE js.schedule_id schedule_id AND j.name NWeekly Sales Data Backup -- 排除我们刚删除的作业 ) BEGIN EXEC msdb.dbo.sp_delete_schedule schedule_id schedule_id; PRINT 计划 RunOnce 已成功删除。; END ELSE BEGIN PRINT 计划 RunOnce 仍被其他作业引用未被删除。如确需删除请先处理引用它的作业。; END END ELSE BEGIN PRINT 计划 RunOnce 不存在无需删除。; END GO核心参数说明参数说明常用值freq_type频率类型1一次,4每天,8每周,16每月,64当SQL Server代理服务启动时freq_interval具体执行日期与freq_type配合使用。例如freq_type8(每周)时1周日,2周一,4周二...freq_subday_type日内重复单位1在指定时间,4分钟,8小时freq_subday_interval日内重复间隔与freq_subday_type配合。如freq_subday_type4且此值为30表示每30分钟active_start_time作业开始时间格式HHmmss如233000表示 23:30:004.2 查看定时任务脚本方法一使用系统存储过程sp_help_job-- 查询所有作业在 msdb 数据库的查询窗口中执行以下命令 USE msdb; GO EXEC dbo.sp_help_job; GO -- 查询特定作业通过 job_name 参数指定作业名 USE msdb; GO EXEC dbo.sp_help_job job_name N你的作业名称; GO -- 只查看作业的步骤信息可以通过 job_aspect STEPS 参数实现。 USE msdb; GO EXEC dbo.sp_help_job job_name N你的作业名称, job_aspect STEPS; GO方法二直接查询系统表-- 查询所有作业的基本信息这是获取所有作业列表最直接的方法 USE msdb; GO SELECT * FROM msdb.dbo.sysjobs; GO -- 查询所有启用的作业及其计划通过关联 sysjobs 和 sysjobschedules 表可以获取作业的调度信息 USE msdb; GO SELECT sj.name AS JobName, sj.enabled AS IsEnabled, sjs.next_run_date, sjs.next_run_time FROM msdb.dbo.sysjobs sj INNER JOIN msdb.dbo.sysjobschedules sjs ON sj.job_id sjs.job_id; GO方法三查看作业历史如果需要查看作业的执行历史如成功、失败等可以使用sp_help_jobhistory存储过程。查看特定作业的历史记录USE msdb; GO EXEC dbo.sp_help_jobhistory job_name N你的作业名称; GO总结与建议• 日常查询对于大多数日常查询使用 sp_help_job 存储过程是最便捷、信息最全面的选择。• 定制化查询当你需要筛选特定字段如只查看作业名和启用状态或进行复杂关联时直接查询 msdb.dbo.sysjobs 表会更加灵活。• 故障排查当需要检查作业执行失败的原因时查看作业历史 (sp_help_jobhistory) 是首要步骤5、数据库订阅6、性能设置6.1 硬件与实例配置优化打好地基这是优化的第一步确保基础设施能提供足够的性能保障。•内存为 SQL Server 设置合理的最大内存上限确保为操作系统和其他服务预留足够内存。对关键生产环境可考虑启用“锁定内存页”权限防止操作系统回收SQL Server内存。(微软官方推荐的设置值是将“最大服务器内存”设置为服务器总物理内存的 75%)•存储使用具备高 IOPS 和吞吐量的存储子系统。建议将数据文件、日志文件和 tempdb 分开放置在不同物理磁盘上以避免I/O争用。同时为数据文件启用 “即时文件初始化” 功能可加速数据文件增长。•CPU合理设置 “最大并行度” 避免因并行度过高导致CPU资源耗尽并根据负载调整 “并行成本阈值” 。• tempdb 优化根据CPU核心数创建多个 tempdb 数据文件。一般建议从每个CPU核心一个文件开始最大不超过8个文件并确保所有数据文件大小一致启用自动增长。• 版本与更新使用最新的累积更新包并考虑将数据库兼容性级别提升至最新支持版本以启用更智能的查询优化特性。6.2 索引优化提速的关键索引是数据库性能的核心。索引设计不佳是导致性能问题的主要障碍。设计原则根据实际的查询模式来设计索引。为WHERE、JOIN和ORDER BY子句中频繁使用的列创建索引。对于复合查询考虑创建复合索引如(CustomerID, OrderDate)。选择类型聚集索引(Clustered) 决定数据物理顺序适合主键和范围查询非聚集索引(Non-Clustered) 是独立于数据行的索引结构一个表可建多个。避免过度索引索引会提高查询速度但会降低INSERT、UPDATE、DELETE的性能。需定期移除无用索引。维护策略定期检查索引碎片率。当碎片率在5%-30%时考虑重组 (REORGANIZE)索引超过30%时考虑重建 (REBUILD)索引6.3 SQL 查询优化关注细节即使硬件和索引配置良好低效的查询语句依然会成为瓶颈。只返回需要的数据避免使用SELECT *只选择必要的列。分析执行计划在 SSMS 中启用“包括实际的执行计划”找出查询中开销最大的部分并针对性优化。利用 Query Store启用Query Store功能。它可以记录查询的执行计划、运行时统计和等待统计信息是性能故障排查的利器。监控资源开销使用SET STATISTICS TIME ON和SET STATISTICS IO ON来评估查询的CPU和I/O开销。如果物理读取次数较多通常意味着需要索引优化。关注统计信息SQL Server 依赖统计信息来生成执行计划。确保数据库的“自动更新统计信息”选项是开启的或在数据大幅变更后手动更新6.4 监控与诊断持续观察优化不是一次性的需要持续监控来发现新问题。内置工具利用 Windows性能监视器 (PerfMon)监控 CPU、内存、磁盘等硬件指标。动态管理视图 (DMVs)通过查询 DMVs 来获取关于索引使用、查询等待统计、CPU和I/O消耗等方面的实时信息。数据库引擎优化顾问 (DTA)可以作为参考工具分析工作负载并提供索引或分区建议总结SQL Server 性能优化是一个持续迭代的过程可以遵循以下路径建立基线首先了解系统在正常情况下的性能指标。发现问题通过监控工具、Query Store 或用户反馈定位最耗时的查询。分析原因查看执行计划、等待统计等判断是索引缺失、统计信息过旧还是查询语句本身的问题。实施优化针对性地创建/修改索引、重写查询或调整配置。验证效果对比优化前后的性能指标确认问题是否解决。持续循环将监控纳入日常工作不断重复上述过程。7、查询表字段7.1、查询INFORMATION_SCHEMA.COLUMNS- 标准方式这是一个符合 SQL 标准的系统视图写法简单在不同数据库之间迁移性好-- 仅查询字段名 USE [你的数据库名]; GO SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME N你的表名 AND TABLE_SCHEMA Ndbo; -- dbo是默认架构请按需调整[reference:14] GO -- 查询字段名及关键属性如数据类型、是否可空 USE [你的数据库名]; GO SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME N你的表名; GO7.2、查询 sys.columns 系统视图 - 最灵活这是 SQL Server 专用的系统视图能提供最丰富的字段信息。可以配合 sys.tables 和 sys.types 等视图获取更多细节-- 仅查询字段名 USE [你的数据库名]; GO SELECT name AS ColumnName FROM sys.columns WHERE object_id OBJECT_ID(你的表名); -- OBJECT_ID 函数会根据表名返回其ID[reference:18] GO -- 查询字段名、数据类型及长度 USE [你的数据库名]; GO SELECT c.name AS ColumnName, t.name AS DataType, c.max_length AS MaxLength FROM sys.columns c INNER JOIN sys.types t ON c.system_type_id t.system_type_id WHERE c.object_id OBJECT_ID(你的表名); GO7.3 使用系统存储过程 - 信息最全如果希望一次性获取表的完整信息包括字段、索引、约束等可以使用系统存储过程。-- sp_help返回表的全面信息其中就包含一份详细的列列表 USE [你的数据库名]; GO EXEC sp_help 你的表名; GO -- 快捷操作在 SSMS 的查询编辑器中选中表名 你的表名然后按键盘快捷键 AltF1即可快速执行 sp_help -- sp_columns专门用于返回指定表或视图的列信息 USE [你的数据库名]; GO EXEC sp_columns table_name 你的表名, table_owner dbo; GO注意事项指定数据库在执行查询前请确保 USE [你的数据库名]; 语句已切换到你表所在的数据库否则可能查不到数据。指定架构如果表不属于默认的 dbo 架构需要在条件中明确指定架构名例如 TABLE_SCHEMA N你的架构名。大小写SQL Server 默认不区分大小写但为了清晰通常保持关键字大写。8、死锁问题查询-- 查看被阻塞的进程 SELECT spid, blocked, waittype, lastwaittype, waitresource FROM sys.sysprocesses WHERE blocked 0; -- spid当前被阻塞的会话ID。 -- blocked阻塞这个会话的头部阻塞者的会话ID --######################## --分析阻塞链谁阻塞了谁 -- 找到头部阻塞者后可以使用更详细的查询来分析整个阻塞链。这个查询能清晰地展示阻塞关系以及它们正在执行的 SQL 语句 SELECT wt.blocking_session_id AS [阻塞者], wt.session_id AS [被阻塞者], t.text AS [被阻塞查询], b.text AS [阻塞查询] FROM sys.dm_os_waiting_tasks wt JOIN sys.dm_exec_requests r ON wt.session_id r.session_id JOIN sys.dm_exec_requests br ON wt.blocking_session_id br.session_id CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t CROSS APPLY sys.dm_exec_sql_text(br.sql_handle) b WHERE wt.wait_type LIKE LCK%; -- 只关注与锁相关的等待深入分析死锁使用扩展事件 (Extended Events)这是微软官方推荐的、用于捕获和分析死锁的现代方法。你可以创建一个扩展事件会话来捕获xml_deadlock_report或lock_deadlock事件。当死锁发生时事件会记录一个包含完整死锁信息的 XML 报告。一个简化的创建脚本示例CREATE EVENT SESSION [CaptureDeadlocks] ON SERVER ADD EVENT sqlserver.xml_deadlock_report ADD TARGET package0.event_file(SET filename NC:\DeadlockEvents.xel) WITH (MAX_MEMORY4096 KB, EVENT_RETENTION_MODEALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY30 SECONDS, MAX_EVENT_SIZE0 KB, MEMORY_PARTITION_MODENONE, TRACK_CAUSALITYOFF,STARTUP_STATEOFF) GO -- 启动会话 ALTER EVENT SESSION [CaptureDeadlocks] ON SERVER STATE START;捕获到的.xel文件可以在 SSMS 中打开查看。死锁 XML 报告会详细列出死锁中涉及的两个或多个进程。每个进程正在执行的 SQL 语句。每个进程试图获取和已持有的锁资源。死锁发生后被选为牺牲品的事务会收到错误号1205。你的应用程序应该捕获这个错误并在短暂随机暂停后重试该事务。 处理建议对于一般阻塞使用上述查询找到头部阻塞者blocking_session_id为 0 的会话。分析它正在执行的操作判断是否可以优化或终止。对于死锁短期如果死锁频繁发生可以在查询中使用WITH (NOLOCK)或READ UNCOMMITTED提示来避免某些锁但这可能导致脏读。长期根本解决方案是优化应用代码。确保事务简短、快速并尽量以相同的顺序访问资源。同时考虑启用READ_COMMITTED_SNAPSHOT数据库选项使用行版本控制来减少阻塞和死锁 MSSQL 手册
1、角色和权限1.1、固定数据库角色权限对照表角色名称核心权限典型应用场景重要备注db_accessadmin可以添加或删除数据库用户的访问权限即管理数据库用户。负责管理数据库用户账号的管理员。不能授予服务器级别权限仅限当前数据库的用户管理。db_backupoperator可以执行数据库备份操作BACKUP DATABASE和BACKUP LOG。负责定期备份数据库的运维人员。无法读取数据或恢复数据库仅备份权限。db_datareader可以读取所有用户表中的数据SELECT权限。报表查询、只读分析、数据导出。不能修改数据也不能查看表结构定义。db_datawriter可以对所有用户表的数据执行增、删、改INSERT、UPDATE、DELETE。数据录入、内容管理、ETL 工具。通常需要与db_datareader组合使用否则无法查询数据。db_ddladmin可以执行所有数据定义语言DDL如创建、修改、删除表、视图、存储过程、架构等。数据库迁移、应用部署、架构变更。不自动拥有数据读取或修改权限也不能管理用户权限。db_denydatareader显式拒绝读取所有用户表数据的权限DENY SELECT。用于覆盖其他角色赋予的读取权限强制禁止读数据。该权限会覆盖db_datareader等授予的权限通常用于安全隔离。db_denydatawriter显式拒绝对任何用户表进行数据修改的权限DENY INSERT、UPDATE、DELETE。用于覆盖其他角色赋予的写入权限强制禁止修改数据。会覆盖db_datawriter的权限确保用户无法更改数据。db_owner拥有数据库的完全控制权CONTROL DATABASE可执行所有配置、维护和权限管理。数据库管理员DBA用于全面管理数据库。权限极大应严格限制授予范围。⚠️ 谨慎授予db_securityadmin可以管理数据库的权限包括授予、拒绝或撤销权限以及管理角色成员身份。权限管理员负责分配数据库访问权限。无法管理数据库架构或数据专注于安全性。public每个数据库用户默认所属的内置角色拥有最基础的权限通常为VIEW ANY DATABASE等。所有用户自动拥有该角色用于授予全局最低权限。不能被删除权限应最小化避免给public授予敏感权限。最佳实践总结原则说明最小权限只授予用户完成工作所需的最少权限优先使用角色固定数据库角色管理简单权限清晰便于维护谨慎使用db_owner完全控制权限极大仅授予少数核心管理员理解优先级DENYGRANT显式授予 角色授权定期审查定期检查用户权限移除不再需要的授权1.2 SQL Server 数据库级权限分类详解数据操作权限 (DML - 操作数据)权限用途使用场景关键说明SELECT读取一个或多个表中的数据。报表应用、数据查询、只读用户。最基础的权限之一。建议优先使用db_datareader角色批量授予。INSERT向表中添加新行。数据录入应用、日志记录。授予此权限意味着用户可以插入数据但无法读取或修改已有数据。UPDATE修改表中现有行的数据。数据更新、内容管理应用。常与SELECT权限一同授予。DELETE从表中删除行。数据清理、归档应用。风险较高需谨慎授予。REFERENCES创建外键约束时引用该表。需要创建表间关联关系的开发者。通常授予开发者或架构师。最佳实践组合对于大多数应用账号授予db_datareader和db_datawriter两个角色即可获得完整的“增删改查”权限结构管理权限 (DDL - 管理对象)权限用途使用场景关键说明CREATE TABLE在数据库中创建新表。应用部署、数据迁移脚本。风险较高应限制在开发或部署环境。CREATE VIEW创建新视图。为报表或应用创建专用数据视角。同CREATE TABLE需谨慎授予。CREATE PROCEDURE创建新的存储过程。封装业务逻辑、提高性能。风险较高可能被用于执行任意代码。CREATE FUNCTION创建新的用户定义函数。封装可复用的逻辑。风险同存储过程。ALTER修改任何安全对象的属性或定义。修改表结构、调整索引等维护任务。权限极大。在架构级别授予ALTER用户可创建/修改/删除该架构下任何对象。DROP删除安全对象。清理过期对象。风险极高可能导致数据丢失。CONTROL授予类似对象“所有者”的全部权限。将对象的管理权委托给其他人。权限极大。在数据库级别授予CONTROL用户拥有该库的所有权限。最佳实践通过db_ddladmin角色可授予用户管理数据库对象结构的全部权限适合需要执行部署或架构变更的账号。对于普通应用账号不应授予任何CREATE或ALTER权限安全与权限管理权限权限用途使用场景关键说明GRANT授予其他用户权限。需要协助管理权限的“二级管理员”。权限极大通常只授予数据库所有者dbo或db_securityadmin角色的成员。DENY拒绝其他用户的权限。强制执行安全策略覆盖其他授权。DENY的优先级高于GRANT即使用户通过角色获得了权限也会被拒绝。REVOKE移除之前授予或拒绝的权限。回收不再需要的权限。使权限回到未定义状态此时用户是否拥有权限取决于角色或其他授权。IMPERSONATE允许模拟其他用户的身份执行操作。高级故障排查、审计场景。风险极高应严格限制。最佳实践通过db_securityadmin角色可授予用户管理数据库权限的权限。普通用户不应拥有管理权限的能力。总结与实践建议将上述原则应用到你的场景常规应用账号授予db_datareaderdb_datawriter角色即可。部署/发布账号考虑授予db_ddladmin角色。数据库管理员根据职责考虑db_owner或自定义角色。如果固定角色无法满足需求可以创建自定义数据库角色将一组精细权限打包授予便于管理和复用。最后务必遵循以下最佳实践定期审查定期检查并回收不必要的权限。避免使用sa禁止应用使用sa等高特权账号。职责分离将数据操作DML和结构变更DDL权限分给不同账号2、用户角色管理2.1 核心方法概览方法适用场景特点通过固定数据库角色标准权限需求读写、只读等管理简单、权限清晰推荐优先使用通过GRANT语句精细授权需要对特定表、视图或存储过程单独授权权限粒度最细灵活但管理复杂通过DENY语句拒绝权限强制禁止特定操作覆盖其他授权用于安全隔离优先级最高方法一通过角色赋权标准方式常用角色组合及命令-- 切换到目标数据库 USE [DSCM_STD]; GO -- 1. 只读权限仅查询数据 ALTER ROLE [db_datareader] ADD MEMBER [user1]; GO -- 2. 读写权限查询 增删改 ALTER ROLE [db_datareader] ADD MEMBER [user2]; ALTER ROLE [db_datawriter] ADD MEMBER [user2]; GO -- 3. 结构修改权限可创建/修改/删除表等 ALTER ROLE [db_ddladmin] ADD MEMBER [user3]; GO -- 4. 完全控制权限数据库所有者 ALTER ROLE [db_owner] ADD MEMBER [user4]; GO方法二通过 GRANT 语句精细授权常用精细授权命令-- 切换到目标数据库 USE [DSCM_STD]; GO -- 1. 授予对特定表的查询权限 GRANT SELECT ON [dbo].[Employees] TO [user1]; GRANT SELECT, INSERT, UPDATE ON [dbo].[Orders] TO [user2]; -- 2. 授予对特定视图的查询权限 GRANT SELECT ON [dbo].[v_ActiveUsers] TO [user3]; -- 3. 授予对特定存储过程的执行权限 GRANT EXECUTE ON [dbo].[sp_GenerateReport] TO [user4]; -- 4. 授予对特定架构的所有权限 GRANT CONTROL ON SCHEMA::[Sales] TO [user5]; -- 5. 授予创建表的权限细粒度 GRANT CREATE TABLE TO [user6];方法三通过 DENY 拒绝权限覆盖所有授予-- 拒绝用户读取某张敏感表即使他是 db_datareader DENY SELECT ON [dbo].[Salaries] TO [user1]; -- 拒绝用户修改某张表的数据 DENY INSERT, UPDATE, DELETE ON [dbo].[Financials] TO [user2]; -- 拒绝用户执行某个存储过程 DENY EXECUTE ON [dbo].[sp_DeleteAllData] TO [user3];权限管理辅助命令-- 查看用户所属的数据库角色 SELECT r.name AS RoleName FROM sys.database_role_members rm JOIN sys.database_principals r ON rm.role_principal_id r.principal_id JOIN sys.database_principals m ON rm.member_principal_id m.principal_id WHERE m.name user1; -- 查看用户对特定对象的权限 SELECT * FROM fn_my_permissions(dbo.Employees, OBJECT); --移除权限 -- 从角色中移除用户 ALTER ROLE [db_datareader] DROP MEMBER [user1]; -- 撤销直接授予的权限移除 GRANT REVOKE SELECT ON [dbo].[Employees] FROM [user1]; -- 移除拒绝权限取消 DENY REVOKE DENY SELECT ON [dbo].[Salaries] FROM [user1];-- 查看当前数据库所有角色成员 EXEC sp_helpuser; -- 将用户添加到某个角色 ALTER ROLE [db_datareader] ADD MEMBER [YourUser]; -- 将用户从角色移除 ALTER ROLE [db_datareader] DROP MEMBER [YourUser];2.2实战案例对 DSCM_STD 库 映射多个用户user1user2对user1 赋于db_datareaderdb_datawriterdb_ddladmin对user2 赋于 db_owner-- -- 1. (可选) 在服务器级别创建登录名如果已存在可跳过 -- 请将 YourStrongPassword! 替换为强密码 -- USE [master]; GO -- 如果登录名 user1 不存在则创建建议先检查是否存在 IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name user1) CREATE LOGIN [user1] WITH PASSWORD YourStrongPassword!; GO IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name user2) CREATE LOGIN [user2] WITH PASSWORD YourStrongPassword!; GO -- -- 2. 切换到目标数据库 DSCM_STD -- USE [DSCM_STD]; GO -- -- 3. 创建数据库用户并映射到登录名 -- -- 如果用户已存在先删除谨慎操作生产环境需先确认 -- DROP USER IF EXISTS [user1]; -- DROP USER IF EXISTS [user2]; CREATE USER [user1] FOR LOGIN [user1]; CREATE USER [user2] FOR LOGIN [user2]; GO -- -- 4. 为 user1 授予角色只读、读写、DDL 管理员 -- ALTER ROLE [db_datareader] ADD MEMBER [user1]; ALTER ROLE [db_datawriter] ADD MEMBER [user1]; ALTER ROLE [db_ddladmin] ADD MEMBER [user1]; GO -- -- 5. 为 user2 授予 db_owner 角色拥有数据库完全控制权 -- ALTER ROLE [db_owner] ADD MEMBER [user2]; GO -- -- 6. 查看已授予的角色确认结果 -- SELECT u.name AS UserName, r.name AS RoleName FROM sys.database_role_members rm JOIN sys.database_principals r ON rm.role_principal_id r.principal_id JOIN sys.database_principals u ON rm.member_principal_id u.principal_id WHERE u.name IN (user1, user2) ORDER BY u.name;3、备份库# # SQL Server 数据库备份脚本 # 功能: 备份数据库 # # 请修改以下配置 $sqlServer localhost # SQL Server实例名 $sqlUser bakUser # SQL用户名 $sqlPassword ************ # SQL密码 $useWindowsAuth $true # trueWindows认证, falseSQL认证 $backupDir D:\DBBackup # 备份目录 $databases (DSCM_STD,DSCM_STD_Log,Mango-Server-DSCM) # 可以指定多个数据库名 # 日志函数 function Log { param([string]$message) $timestamp Get-Date -Format yyyy-MM-dd HH:mm:ss Write-Host [$timestamp] $message } # 主程序 # 创建备份目录 if (!(Test-Path $backupDir)) { New-Item -ItemType Directory -Path $backupDir -Force | Out-Null Log 创建备份目录: $backupDir } Log 备份开始 # 获取时间戳 $timestamp Get-Date -Format yyyyMMdd_HHmmss # 测试数据库连接 Log 测试数据库连接... try { if ($useWindowsAuth) { Invoke-Sqlcmd -ServerInstance $sqlServer -Query SELECT 1 -QueryTimeout 30 -ErrorAction Stop | Out-Null } else { Invoke-Sqlcmd -ServerInstance $sqlServer -Username $sqlUser -Password $sqlPassword -Query SELECT 1 -QueryTimeout 30 -ErrorAction Stop | Out-Null } Log 数据库连接成功 } catch { Log 数据库连接失败: $_ exit 1 } # 备份每个数据库 $successCount 0 $failCount 0 foreach ($db in $databases) { Log ---------------------------------------- Log 开始备份数据库: $db $bakFile $backupDir\$db_$timestamp.bak # 备份SQL $backupSql BACKUP DATABASE [$db] TO DISK N$bakFile WITH FORMAT, STATS10 # 执行备份 try { $startTime Get-Date if ($useWindowsAuth) { Invoke-Sqlcmd -ServerInstance $sqlServer -Query $backupSql -QueryTimeout 0 -ErrorAction Stop } else { Invoke-Sqlcmd -ServerInstance $sqlServer -Username $sqlUser -Password $sqlPassword -Query $backupSql -QueryTimeout 0 -ErrorAction Stop } $endTime Get-Date $duration [math]::Round(($endTime - $startTime).TotalSeconds, 2) # 检查备份文件 if (Test-Path $bakFile) { $bakSize [math]::Round((Get-Item $bakFile).Length / 1MB, 2) Log 备份成功: $db_$timestamp.bak (${bakSize}MB, 耗时 ${duration}秒) $successCount } else { Log 备份失败: 文件未生成 $failCount } } catch { Log 备份数据库 $db 失败: $_ $failCount } } Log 备份完成 Log 统计: 成功 $successCount 个, 失败 $failCount 个 if ($failCount -gt 0) { exit 1 } else { exit 0 }4、定时任务设置4.1 创建定时任务脚本使用 Transact-SQL (T-SQL) 脚本这种方法适合批量创建、自动化部署或版本控制。你需要在 msdb 系统数据库中执行一系列系统存储过程。以下是一个完整的脚本示例它创建了一个名为 Weekly Sales Data Backup 的作业USE msdb; GO -- 1. 创建作业 EXEC dbo.sp_add_job job_name NWeekly Sales Data Backup; -- 作业名称 GO -- 2. 为作业添加步骤 EXEC sp_add_jobstep job_name NWeekly Sales Data Backup, step_name NSet database to read only, -- 步骤名称 subsystem NTSQL, -- 步骤类型TSQL表示执行T-SQL command NALTER DATABASE SALES SET READ_ONLY;, -- 执行的命令 retry_attempts 5, -- 失败重试次数 retry_interval 5; -- 重试间隔分钟 GO -- 3. 创建并附加计划 EXEC dbo.sp_add_schedule schedule_name NRunOnce, -- 计划名称 freq_type 1, -- 1 表示只执行一次 active_start_time 233000; -- 开始时间 23:30:00 GO EXEC sp_attach_schedule job_name NWeekly Sales Data Backup, schedule_name NRunOnce; GO -- 4. 将作业添加到当前服务器目标服务器 EXEC dbo.sp_add_jobserver job_name NWeekly Sales Data Backup; GO -- USE msdb; GO -- -- 1. 删除作业如果存在 -- IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name NWeekly Sales Data Backup) BEGIN EXEC msdb.dbo.sp_delete_job job_name NWeekly Sales Data Backup; PRINT 作业 Weekly Sales Data Backup 已成功删除。; END ELSE BEGIN PRINT 作业 Weekly Sales Data Backup 不存在无需删除。; END GO -- -- 2. 删除计划 RunOnce如果存在且未被其他作业使用 -- DECLARE schedule_id INT; SELECT schedule_id schedule_id FROM msdb.dbo.sysschedules WHERE name NRunOnce; IF schedule_id IS NOT NULL BEGIN -- 检查该计划是否还被其他作业使用排除已删除的作业 IF NOT EXISTS ( SELECT 1 FROM msdb.dbo.sysjobschedules js INNER JOIN msdb.dbo.sysjobs j ON js.job_id j.job_id WHERE js.schedule_id schedule_id AND j.name NWeekly Sales Data Backup -- 排除我们刚删除的作业 ) BEGIN EXEC msdb.dbo.sp_delete_schedule schedule_id schedule_id; PRINT 计划 RunOnce 已成功删除。; END ELSE BEGIN PRINT 计划 RunOnce 仍被其他作业引用未被删除。如确需删除请先处理引用它的作业。; END END ELSE BEGIN PRINT 计划 RunOnce 不存在无需删除。; END GO核心参数说明参数说明常用值freq_type频率类型1一次,4每天,8每周,16每月,64当SQL Server代理服务启动时freq_interval具体执行日期与freq_type配合使用。例如freq_type8(每周)时1周日,2周一,4周二...freq_subday_type日内重复单位1在指定时间,4分钟,8小时freq_subday_interval日内重复间隔与freq_subday_type配合。如freq_subday_type4且此值为30表示每30分钟active_start_time作业开始时间格式HHmmss如233000表示 23:30:004.2 查看定时任务脚本方法一使用系统存储过程sp_help_job-- 查询所有作业在 msdb 数据库的查询窗口中执行以下命令 USE msdb; GO EXEC dbo.sp_help_job; GO -- 查询特定作业通过 job_name 参数指定作业名 USE msdb; GO EXEC dbo.sp_help_job job_name N你的作业名称; GO -- 只查看作业的步骤信息可以通过 job_aspect STEPS 参数实现。 USE msdb; GO EXEC dbo.sp_help_job job_name N你的作业名称, job_aspect STEPS; GO方法二直接查询系统表-- 查询所有作业的基本信息这是获取所有作业列表最直接的方法 USE msdb; GO SELECT * FROM msdb.dbo.sysjobs; GO -- 查询所有启用的作业及其计划通过关联 sysjobs 和 sysjobschedules 表可以获取作业的调度信息 USE msdb; GO SELECT sj.name AS JobName, sj.enabled AS IsEnabled, sjs.next_run_date, sjs.next_run_time FROM msdb.dbo.sysjobs sj INNER JOIN msdb.dbo.sysjobschedules sjs ON sj.job_id sjs.job_id; GO方法三查看作业历史如果需要查看作业的执行历史如成功、失败等可以使用sp_help_jobhistory存储过程。查看特定作业的历史记录USE msdb; GO EXEC dbo.sp_help_jobhistory job_name N你的作业名称; GO总结与建议• 日常查询对于大多数日常查询使用 sp_help_job 存储过程是最便捷、信息最全面的选择。• 定制化查询当你需要筛选特定字段如只查看作业名和启用状态或进行复杂关联时直接查询 msdb.dbo.sysjobs 表会更加灵活。• 故障排查当需要检查作业执行失败的原因时查看作业历史 (sp_help_jobhistory) 是首要步骤5、数据库订阅6、性能设置6.1 硬件与实例配置优化打好地基这是优化的第一步确保基础设施能提供足够的性能保障。•内存为 SQL Server 设置合理的最大内存上限确保为操作系统和其他服务预留足够内存。对关键生产环境可考虑启用“锁定内存页”权限防止操作系统回收SQL Server内存。(微软官方推荐的设置值是将“最大服务器内存”设置为服务器总物理内存的 75%)•存储使用具备高 IOPS 和吞吐量的存储子系统。建议将数据文件、日志文件和 tempdb 分开放置在不同物理磁盘上以避免I/O争用。同时为数据文件启用 “即时文件初始化” 功能可加速数据文件增长。•CPU合理设置 “最大并行度” 避免因并行度过高导致CPU资源耗尽并根据负载调整 “并行成本阈值” 。• tempdb 优化根据CPU核心数创建多个 tempdb 数据文件。一般建议从每个CPU核心一个文件开始最大不超过8个文件并确保所有数据文件大小一致启用自动增长。• 版本与更新使用最新的累积更新包并考虑将数据库兼容性级别提升至最新支持版本以启用更智能的查询优化特性。6.2 索引优化提速的关键索引是数据库性能的核心。索引设计不佳是导致性能问题的主要障碍。设计原则根据实际的查询模式来设计索引。为WHERE、JOIN和ORDER BY子句中频繁使用的列创建索引。对于复合查询考虑创建复合索引如(CustomerID, OrderDate)。选择类型聚集索引(Clustered) 决定数据物理顺序适合主键和范围查询非聚集索引(Non-Clustered) 是独立于数据行的索引结构一个表可建多个。避免过度索引索引会提高查询速度但会降低INSERT、UPDATE、DELETE的性能。需定期移除无用索引。维护策略定期检查索引碎片率。当碎片率在5%-30%时考虑重组 (REORGANIZE)索引超过30%时考虑重建 (REBUILD)索引6.3 SQL 查询优化关注细节即使硬件和索引配置良好低效的查询语句依然会成为瓶颈。只返回需要的数据避免使用SELECT *只选择必要的列。分析执行计划在 SSMS 中启用“包括实际的执行计划”找出查询中开销最大的部分并针对性优化。利用 Query Store启用Query Store功能。它可以记录查询的执行计划、运行时统计和等待统计信息是性能故障排查的利器。监控资源开销使用SET STATISTICS TIME ON和SET STATISTICS IO ON来评估查询的CPU和I/O开销。如果物理读取次数较多通常意味着需要索引优化。关注统计信息SQL Server 依赖统计信息来生成执行计划。确保数据库的“自动更新统计信息”选项是开启的或在数据大幅变更后手动更新6.4 监控与诊断持续观察优化不是一次性的需要持续监控来发现新问题。内置工具利用 Windows性能监视器 (PerfMon)监控 CPU、内存、磁盘等硬件指标。动态管理视图 (DMVs)通过查询 DMVs 来获取关于索引使用、查询等待统计、CPU和I/O消耗等方面的实时信息。数据库引擎优化顾问 (DTA)可以作为参考工具分析工作负载并提供索引或分区建议总结SQL Server 性能优化是一个持续迭代的过程可以遵循以下路径建立基线首先了解系统在正常情况下的性能指标。发现问题通过监控工具、Query Store 或用户反馈定位最耗时的查询。分析原因查看执行计划、等待统计等判断是索引缺失、统计信息过旧还是查询语句本身的问题。实施优化针对性地创建/修改索引、重写查询或调整配置。验证效果对比优化前后的性能指标确认问题是否解决。持续循环将监控纳入日常工作不断重复上述过程。7、查询表字段7.1、查询INFORMATION_SCHEMA.COLUMNS- 标准方式这是一个符合 SQL 标准的系统视图写法简单在不同数据库之间迁移性好-- 仅查询字段名 USE [你的数据库名]; GO SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME N你的表名 AND TABLE_SCHEMA Ndbo; -- dbo是默认架构请按需调整[reference:14] GO -- 查询字段名及关键属性如数据类型、是否可空 USE [你的数据库名]; GO SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME N你的表名; GO7.2、查询 sys.columns 系统视图 - 最灵活这是 SQL Server 专用的系统视图能提供最丰富的字段信息。可以配合 sys.tables 和 sys.types 等视图获取更多细节-- 仅查询字段名 USE [你的数据库名]; GO SELECT name AS ColumnName FROM sys.columns WHERE object_id OBJECT_ID(你的表名); -- OBJECT_ID 函数会根据表名返回其ID[reference:18] GO -- 查询字段名、数据类型及长度 USE [你的数据库名]; GO SELECT c.name AS ColumnName, t.name AS DataType, c.max_length AS MaxLength FROM sys.columns c INNER JOIN sys.types t ON c.system_type_id t.system_type_id WHERE c.object_id OBJECT_ID(你的表名); GO7.3 使用系统存储过程 - 信息最全如果希望一次性获取表的完整信息包括字段、索引、约束等可以使用系统存储过程。-- sp_help返回表的全面信息其中就包含一份详细的列列表 USE [你的数据库名]; GO EXEC sp_help 你的表名; GO -- 快捷操作在 SSMS 的查询编辑器中选中表名 你的表名然后按键盘快捷键 AltF1即可快速执行 sp_help -- sp_columns专门用于返回指定表或视图的列信息 USE [你的数据库名]; GO EXEC sp_columns table_name 你的表名, table_owner dbo; GO注意事项指定数据库在执行查询前请确保 USE [你的数据库名]; 语句已切换到你表所在的数据库否则可能查不到数据。指定架构如果表不属于默认的 dbo 架构需要在条件中明确指定架构名例如 TABLE_SCHEMA N你的架构名。大小写SQL Server 默认不区分大小写但为了清晰通常保持关键字大写。8、死锁问题查询-- 查看被阻塞的进程 SELECT spid, blocked, waittype, lastwaittype, waitresource FROM sys.sysprocesses WHERE blocked 0; -- spid当前被阻塞的会话ID。 -- blocked阻塞这个会话的头部阻塞者的会话ID --######################## --分析阻塞链谁阻塞了谁 -- 找到头部阻塞者后可以使用更详细的查询来分析整个阻塞链。这个查询能清晰地展示阻塞关系以及它们正在执行的 SQL 语句 SELECT wt.blocking_session_id AS [阻塞者], wt.session_id AS [被阻塞者], t.text AS [被阻塞查询], b.text AS [阻塞查询] FROM sys.dm_os_waiting_tasks wt JOIN sys.dm_exec_requests r ON wt.session_id r.session_id JOIN sys.dm_exec_requests br ON wt.blocking_session_id br.session_id CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t CROSS APPLY sys.dm_exec_sql_text(br.sql_handle) b WHERE wt.wait_type LIKE LCK%; -- 只关注与锁相关的等待深入分析死锁使用扩展事件 (Extended Events)这是微软官方推荐的、用于捕获和分析死锁的现代方法。你可以创建一个扩展事件会话来捕获xml_deadlock_report或lock_deadlock事件。当死锁发生时事件会记录一个包含完整死锁信息的 XML 报告。一个简化的创建脚本示例CREATE EVENT SESSION [CaptureDeadlocks] ON SERVER ADD EVENT sqlserver.xml_deadlock_report ADD TARGET package0.event_file(SET filename NC:\DeadlockEvents.xel) WITH (MAX_MEMORY4096 KB, EVENT_RETENTION_MODEALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY30 SECONDS, MAX_EVENT_SIZE0 KB, MEMORY_PARTITION_MODENONE, TRACK_CAUSALITYOFF,STARTUP_STATEOFF) GO -- 启动会话 ALTER EVENT SESSION [CaptureDeadlocks] ON SERVER STATE START;捕获到的.xel文件可以在 SSMS 中打开查看。死锁 XML 报告会详细列出死锁中涉及的两个或多个进程。每个进程正在执行的 SQL 语句。每个进程试图获取和已持有的锁资源。死锁发生后被选为牺牲品的事务会收到错误号1205。你的应用程序应该捕获这个错误并在短暂随机暂停后重试该事务。 处理建议对于一般阻塞使用上述查询找到头部阻塞者blocking_session_id为 0 的会话。分析它正在执行的操作判断是否可以优化或终止。对于死锁短期如果死锁频繁发生可以在查询中使用WITH (NOLOCK)或READ UNCOMMITTED提示来避免某些锁但这可能导致脏读。长期根本解决方案是优化应用代码。确保事务简短、快速并尽量以相同的顺序访问资源。同时考虑启用READ_COMMITTED_SNAPSHOT数据库选项使用行版本控制来减少阻塞和死锁