一、简介
在MSSQL中,性能调优是实际案例中常见的问题之一,一些简单的步骤可以显著提高MSSQL的性能。MSSQL的“简单”模式是最基础的恢复模式,但它可以通过优化性能来获得最佳性能。
二、简单模式下的性能调优
1. 动态管理视图(Dynamic Management Views - DMVs)
DMVs是用于获取有关服务器和数据库状态的信息的视图,可以帮助识别性能瓶颈。以下是一些有用的DMVs:
--用于识别CPU负载
SELECT TOP 10
total_worker_time/execution_count AS Avg_CPU_Time
,execution_count AS Execution_Count
,total_elapsed_time/execution_count as AVG_Run_Time
,(SELECT
SUBSTRING(text,statement_start_offset/2,((CASE WHEN statement_end_offset = -1
THEN (LEN(CONVERT(nvarchar(max),text)) * 2)
ELSE statement_end_offset END
) -statement_start_offset)/2
)
FROM sys.dm_exec_sql_text(sql_handle)
) AS Query_Text
FROM sys.dm_exec_query_stats
ORDER BY Avg_CPU_Time DESC
以上查询可以显示CPU时间最高的查询,可以通过对代码进行优化来提高性能。
2. 索引维护
每当进行更改时,索引必须维护以保持性能。以下是一些有用的查询:
--查找索引碎片率
SELECT OBJECT_NAME(T.[OBJECT_ID]) AS [TableName],
I.[name] AS [IndexName],
T.[Name] AS [IndexDescription],
T.[avg_fragmentation_in_percent],
T.[page_count]
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS T
INNER JOIN sys.indexes AS I ON T.[object_id] = I.[object_id]
AND T.[index_id] = I.[index_id]
WHERE T.avg_fragmentation_in_percent > 10.0
AND i.[name] is not null;
--重建索引
ALTER INDEX ALL ON TableName REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, ONLINE = ON);
以上代码用于查找索引碎片率,并根据需要重建索引。
3. 服务器设置
可以通过更改服务器设置来优化MSSQL性能。以下是一些有用的设置:
--检查多个实例是否在同一服务器上,这可能会影响性能
SELECT
@@SERVERNAME AS HostName,
SERVERPROPERTY('InstanceName') AS InstanceName,
@@MAX_PRECISION AS MaxPrecision,
@@MAX_CONNECTIONS AS MaxConnections,
@@LANGUAGE AS Language,
@@DATEFIRST AS Datefirst,
@@DBTS AS DBTimeStamp
GO
--设置max degree of parallelism (M应小写),用于限制单个查询使用的CPU个数
EXEC sys.sp_configure N'max degree of parallelism', N'4'
GO
以上代码将max degree of parallelism设置为4个CPU。
三、结论
简单模式下进行的性能调优可以大大提高MSSQL的性能。使用DMVs,索引维护和服务器设置之类的技术可以帮助识别性能瓶颈,通过优化代码,重建索引和更改服务器设置等措施来改进性能。