SQL常用优化脚本，优化SQL语句

### SQL优化脚本详解 在数据库管理与维护中，SQL语句的性能优化至关重要，它直接影响到系统的响应速度和资源利用率。以下是从给定文件中提取并解析的关键SQL优化脚本，涵盖数据库缓存管理、资源状态监控、空间使用情况统计、索引优化以及数据库检查与修复等方面。 #### 1. 缓存对象查询与管理 - **查询数据库ID**：`select db_id('web')`用于获取指定数据库的ID。 - **查询缓存对象**： - `Select SQL AS EXEC_SQL, OBJTYPE AS EXEC_TYPE, * FROM MASTER..SYSCACHEOBJECTS where dbid = '8'` - `select sql, cacheobjtype, OBJTYPE from MASTER..SYSCACHEOBJECTS where dbid = '8' group by cacheobjtype, sql, objtype` 这两段脚本分别用于获取数据库中所有缓存对象的详细信息，包括执行的SQL语句、对象类型等，按对象类型分组显示。 - **统计缓存对象数量**：`select count(*) from MASTER..SYSCACHEOBJECTS where dbid = '8'`统计特定数据库中的缓存对象总数。 - **清除过程缓存**：`dbcc freeproccache`命令用于释放SQL Server中存储的过程缓存，有助于减少内存消耗，但可能增加重新编译的成本。 #### 2. 内存与资源状态监控 - **查询内存状态**：`dbcc memorystatus`提供关于当前服务器上内存使用的详细信息，包括可用内存、总内存、已分配给SQL Server的内存等。 - **查看执行计数**：`SELECT UseCounts, RefCounts, CacheObjtype, ObjType, DB_NAME(8) as DatabaseName, SQL FROM syscacheobjects ORDER BY dbid, usecounts DESC, objtype`此脚本展示了数据库中缓存对象的使用次数和引用计数，便于分析哪些对象最常被访问。 #### 3. 数据库空间管理与统计 - **当前数据库占用信息**：`DBCC SQLPERF(LOGSPACE)`显示数据库的日志空间使用情况，包括日志空间总量、已用空间、剩余空间等。 - **数据库信息**：`exec sp_helpdb dbname`提供数据库的整体信息，如状态、大小、日志文件信息等。 - **表信息**：`sp_helptb_name`和`sp_MShelpcolumns tb_name`分别用于获取表结构信息和列详情，帮助了解数据分布和存储格式。 - **空间使用统计**：`exec sp_spaceused tb_name`和`exec sp_MSforeachtable "exec sp_spaceused '?'"`用于统计表或整个数据库的空间使用情况，包括已用空间、未分配空间和数据文件大小等。 #### 4. 索引与查询优化 - **创建索引**：`create index i_tbname_fieldname on tb_name(fieldName)`和`create clustered index pk_id on tb_name(id)`用于在表上创建非聚集和聚集索引，加速查询处理。 - **检查数据库完整性**：`DBCC CHECKDB`命令用于检查数据库的物理和逻辑一致性，是确保数据完整性的关键步骤。 #### 5. 数据库检查与修复 - **设置单用户模式**：`EXEC sp_dboption 'db_Name', 'singleuser', 'TRUE'`将数据库设置为只允许一个用户访问，通常在进行数据库检查或维护时使用。 - **检查并修复数据库**： - `dbcc checkdb('db_Name', repair_allow_data_loss)`、`dbcc checkdb('db_Name', REPAIR_REBUILD)`和`dbcc checkdb('db_Name', repair_fast)`分别用于不同级别上的数据库修复，可能涉及数据丢失的风险评估。 - **恢复多用户模式**：`EXEC sp_dboption 'db_Name', 'singleuser', 'FALSE'`将数据库恢复为多用户访问模式。 #### 6. SQL查询优化分析 - **查询高负载SQL**：`SELECT hash_value, executions, buffer_gets, disk_reads, parse_calls FROM V$SQLAREA WHERE buffer_gets > 10000000 OR disk_reads > 1000000 ORDER BY buffer_gets + 100*disk_reads DESC`此脚本用于识别数据库中资源消耗最大的SQL语句，通过`buffer_gets`和`disk_reads`指标来判断。 - **分析具体SQL执行计划**：`SELECT hash_value, buffer_gets, disk_reads, executions, parse_calls FROM V$SQLAREA WHERE hash_Value = 228801498 AND address = hextoraw('CBD8E4B0')`用于深入分析特定SQL语句的执行效率，包括磁盘读取次数和执行次数等。 这些脚本不仅提供了数据库性能优化的基础工具，也揭示了SQL Server内部的工作机制，对于提高数据库效率和稳定性具有重要作用。通过定期执行这些脚本，可以及时发现并解决潜在的性能瓶颈，确保数据库系统的健康运行。