☰

如何改善 SQL Server 性能不佳

tag-icon • tag-icon virtualization hardware sql-server-2008-r2
如何改善 SQL Server 性能不佳

我们有一个使用 VMware vSphere 5.0 U1 的虚拟化环境，它托管我们的 Microsoft SQL Server，并且性能非常差。主机服务器是带有 xeon X5450 的 HP DL360 G5，磁盘空间来自使用 SAS2 和 Iscsi 的 HP P4300 G2 上的 SAN。

服务器本身有 3 个核心，频率为 3.00ghz，12gb 内存和 1gbps 网卡，运行 Server 2008 R2 Enterprise 和 SQL Server 2008 R2。

有三个数据库，一个是 180GB，用于 Microsoft Dynamics CRM，一个是 50gb，用作数据仓库，另一个是 500mb，由 Document Logistics（文档管理系统）使用

同一台服务器上还运行着分析服务多维数据集和报告服务。

网络和 CPU 使用率都很低，但 SSIS 包更新数据仓库时 CPU 有时会达到 100%。

内存总是达到最大容量并且系统报告分页过多。

我不确定是否只需投入更多内存即可解决问题 - 如果是的话我应该使用多少内存 - 或者是否由于虚拟化和拥有带有专用本地磁盘的单独服务器是最好的选择。

更新 --- 数据库设置为有限内存，总限制保持在 12gb 以内，所有文件都在一个磁盘上，操作系统/程序在另一个磁盘上，自动增长设置为 1mb，不受限制的索引保留为主机程序默认值，但似乎服务器通常很慢，假设由于内存没有设置维护计划，除了 CRM 的日志为 900mb 之外，其他日志都在 100mb 左右

答案1

当您说内存已用完时，您是否考虑到 SQL 默认会这样做（基本上）？您是否尝试过限制 SQL 可占用的 RAM 量？

在此处输入图片描述

您的磁盘布局是如何设置的？临时/日志/数据/备份使用不同的磁盘？您可以运行此脚本来找出答案：

DECLARE @database_id int 
DECLARE @database_name sysname 
DECLARE @sql_string nvarchar(2000) 
DECLARE @file_size TABLE 
    ( 
    [database_name] [sysname] NULL, 
    [groupid] [smallint] NULL, 
    [groupname] sysname NULL, 
    [fileid] [smallint] NULL, 
    [file_size] [decimal](12, 2) NULL, 
    [space_used] [decimal](12, 2) NULL, 
    [free_space] [decimal](12, 2) NULL, 
    [name] [sysname] NOT NULL, 
    [filename] [nvarchar](260) NOT NULL 
    )

SELECT TOP 1 @database_id = database_id 
    ,@database_name = name 
FROM sys.databases 
WHERE database_id > 0 
ORDER BY database_id

WHILE @database_name IS NOT NULL 
BEGIN

    SET @sql_string = 'USE ' + QUOTENAME(@database_name) + CHAR(10) 
    SET @sql_string = @sql_string + 'SELECT 
                                        DB_NAME() 
                                        ,sysfilegroups.groupid 
                                        ,sysfilegroups.groupname 
                                        ,fileid 
                                        ,convert(decimal(12,2),round(sysfiles.size/128.000,2)) as file_size 
                                        ,convert(decimal(12,2),round(fileproperty(sysfiles.name,''SpaceUsed'')/128.000,2)) as space_used 
                                        ,convert(decimal(12,2),round((sysfiles.size-fileproperty(sysfiles.name,''SpaceUsed''))/128.000,2)) as free_space 
                                        ,sysfiles.name 
                                        ,sysfiles.filename 
                                    FROM sys.sysfiles 
                                    LEFT OUTER JOIN sys.sysfilegroups 
                                        ON sysfiles.groupid = sysfilegroups.groupid'

    INSERT INTO @file_size 
        EXEC sp_executesql @sql_string   

    --Grab next database 
    SET @database_name = NULL 
    SELECT TOP 1 @database_id = database_id 
        ,@database_name = name 
    FROM sys.databases 
    WHERE database_id > @database_id 
    ORDER BY database_id 
END

--File Sizes 
SELECT database_name, groupid, ISNULL(groupname,'TLOG') groupname, fileid, name, file_size, space_used, free_space, filename 
FROM @file_size

--File Group Sizes 
SELECT database_name, groupid, ISNULL(groupname,'TLOG') groupname, SUM(file_size) as file_size, SUM(space_used) as space_used, SUM(free_space) as free_space 
FROM @file_size 
GROUP BY database_name, groupid, groupname
  • 你的自动增长是如何设置的？
  • 你的指标怎么样？
  • 服务器上还有其他缓慢的情况吗（除 SQL 外）？
  • 您有维护计划吗？它们是如何配置的？
  • 你的日志有多大？
  • ...

答案2

我同意第一个答案，但一般来说，对于大小超过 100 GB 的 Microsoft CRM 数据库，我建议在 SQL Server 上拥有超过 12 GB 的 RAM，并且最好看到这种大小的 Microsoft CRM 数据库至少有 32 GB 的 RAM。要更具体地找出导致 SQL Server 性能问题的原因，您需要确定导致服务器负载最大的查询。这些查询并不总是单个查询，但有时是一组参数化查询，这些查询可能执行了数千次，导致性能下降。您可以使用 SQL Server Perf Stats 收集脚本在峰值负载时间收集大约 5 分钟的数据来识别这些查询，http://sqlnexus.codeplex.com/wikipage?title=Sql2005PerfStatsScript&referringTitle=Home，（只需使用 StartSQLDiagTrace20082.cmd，而不是详细的跟踪集合）。您可以使用 SQLNexus 工具，网址为http://sqlnexus.codeplex.com/帮助分析数据并查看报告，这些报告可以帮助您显示哪些查询或哪些查询对服务器造成的负载最大（按总持续时间、总读取次数、总 CPU 或总写入次数）。我通常关注按总读取次数或总持续时间排序的热门查询，并获取这些查询的示例，通过 SQL Server 数据库调优顾问运行，以查找是否有任何缺失的索引或统计数据可用于提高性能。

如果您将最大并行度设置为 1，并且 SQL Server 具有 3 个核心，那么它与 Microsoft Dynamics CRM 数据库配合使用时也将获得更好的性能。请参阅下文以了解设置命令。

sp_configure '显示高级选项',1 重新配置；GO sp_configure '最大并行度',1 使用覆盖重新配置；GO sp_configure '显示高级选项',0 重新配置；GO

相关内容