SQL server 2008 数据库优化常用脚本

让我们来详细讲解“SQL server 2008 数据库优化常用脚本”的完整攻略。

1. 为什么需要数据库优化？

提高数据库的性能，缩短数据保存和数据检索过程的时间，是我们进行数据库优化的主要目的。在处理海量数据的过程中，数据库优化至关重要，可以让你的系统运行更加快捷、高效。

2. 数据库优化的方法

• 数据库物理设计优化
• SQL语句优化
• 数据库参数优化
• 系统与应用架构优化

在以上几个方面中，SQL语句优化是我们优化数据库性能的重点。常见的SQL语句优化技术主要是查询优化和索引优化。

3. SQL server 2008 数据库优化常用脚本

3.1 查询优化

3.1.1 开启或关闭查询执行计划

查询执行计划是一个重要的查询优化工具，可以启用它来查看查询优化器如何执行SQL语句和选择执行计划。我们可以通过以下脚本开启或关闭查询执行计划：

-- 启用查询执行计划
SET SHOWPLAN_ALL ON

-- 关闭查询执行计划
SET SHOWPLAN_ALL OFF

3.1.2 查看缓存中的执行计划

查询执行计划可能会被缓存，这可以帮助提高查询的性能。我们可以使用以下脚本来查看SQL server缓存中的执行计划：

SELECT DB_NAME(st.dbid) DBName,
 OBJECT_NAME(st.objectid, DB_id) ObjName,
 qs.execution_count,
 qs.total_worker_time / 1000000 total_worker_time_inSeconds,
 qs.total_physical_reads
FROM sys.dm_exec_query_stats AS qs
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) st
WHERE st.dbid = DB_ID(); -- 查询当前数据库中的缓存

3.1.3 查看最慢的查询

对于数据库的性能问题而言，最慢的查询是性能瓶颈之一。我们可以使用以下脚本来查看最慢的SQL语句：

SELECT TOP 10 
 execution_count, 
 total_worker_time/execution_count AS avg_cpu_time, 
 total_worker_time, 
 total_elapsed_time/execution_count AS avg_elapsed_time, 
 total_logical_reads/execution_count AS avg_logical_reads, 
 creation_time, 
 last_execution_time,
 cp.objtype,
 OBJECT_NAME(qt.objectid, DB_ID()) AS object_name,
 SUBSTRING(qt.text, (qs.statement_start_offset/2) + 1,
 (( CASE qs.statement_end_offset
 WHEN -1 THEN DATALENGTH(qt.text)
 ELSE qs.statement_end_offset 
 END - qs.statement_start_offset)/2) + 1) as query_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
LEFT JOIN sys.dm_exec_cached_plans cp
 ON cp.plan_handle = qs.plan_handle
WHERE qt.dbid = DB_ID() -- 查询当前数据库中的缓存
ORDER BY avg_elapsed_time DESC

3.2 索引优化

3.2.1 查找缺失索引

缺失索引可能会导致查询的性能下降。我们可以使用以下脚本来查找缺失的索引：

SELECT name AS TableName, 
 mid, 
 max_record_size_in_bytes, 
 max_dhoff_in_bytes, 
 max_rows, 
 distribution_policy_desc,
 index_depth, 
 index_level_count, 
 avg_fragmentation_in_percent 
FROM sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, 'SAMPLED') dps
INNER JOIN sys.tables st ON dps.object_id = st.object_id
LEFT JOIN sys.indexes si ON dps.object_id = si.object_id AND si.index_id = dps.index_id
WHERE dps.database_id = DB_ID()
AND index_id = 0 -- 0 表示是堆表
ORDER BY avg_fragmentation_in_percent DESC

3.2.2 查找碎片化的索引

索引的碎片化也会导致查询性能下降。我们可以使用以下脚本来查找碎片化的索引：

SELECT dbschemas.[name] as 'Schema',
 dbtables.[name] as 'Table',
 dbindexes.[name] as 'Index',
 indexstats.avg_fragmentation_in_percent,
 indexstats.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = dbtables.[object_id]
 AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
AND indexstats.avg_fragmentation_in_percent > 30
ORDER BY indexstats.avg_fragmentation_in_percent DESC

4. 总结

通过以上SQL server 2008 数据库优化常用脚本，可以帮助我们优化数据库的性能、提高系统运行效率。在具体操作中，需要结合实际情况来选择和使用优化脚本。同时，数据库优化是一个长期工作，我们需要持续关注，并不断地进行调整和优化。