2

We have a virtualized environment using VMware vSphere 5.0 U1 which hosts our Microsoft SQL Server and have very poor performance. the host server is a HP DL360 G5 with xeon X5450's, and the disk space comes from a SAN on HP P4300 G2's using SAS2 and Iscsi.

The server itself has 3 cores at 3.00ghz, 12gb memory and 1gbps nic, running Server 2008 R2 Enterprise and SQL Server 2008 R2.

There are three databases, one is 180GB used for Microsoft Dynamics CRM, one is 50gb used as a data warehouse and the other is 500mb used by Document Logistics (a document management system)

There is also an analysis services cube and reporting services running on the same server.

Network and CPU usage are both low, except when the SSIS packages update the data warehouse when CPU sometimes reaches 100%.

Memory is always maxed out and the system reports excessive paging.

I am uncertain if just throwing more memory at the problem will help - and if so how much should I use - or if it's more due to being virtualized and having a separate server with dedicated local disks would be the best option.

UPDATE --- databases are set to limited memory and the total limits are kept withing the 12gb total all of the files are on one disk and the OS/Programs on another Autogrowth is set to 1mb, unrestricted Indexes are left to host program defaults but seem ok server is generally slow, assume due to memory no mantinence plans set up Logs are all aroung 100mb except the one for CRM which is 900mb

2
  • How are your IOPS on those disks? Set your autogrow to 1024 GB on databases that size. Check your autogrow report to see it doesn't constantly expand. Any latency towards the LUNs? Mar 15, 2013 at 13:46
  • 1024 MB that is, not GB, sorry. Mar 17, 2013 at 23:52

2 Answers 2

1

When you say memory is maxed out, did you account for the fact SQL will do this (pretty much) by default? Have you tried limiting the amount of RAM SQL can take?

enter image description here

How is your disk layout set up? Different disks for temp/log/data/backup? You can run this script to find out:

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
  • How are your autogrows set up?
  • How are your indexes?
  • Do you have any other slowness on the server (other then SQL)?
  • Do you have maintenance plans? How are they configured?
  • How big are your logs?
  • ...
1
  • 1
    Thanks for this, now i know where to look I'm finding loads of issues, might have to get a proper DBA in.
    – Stuart
    Mar 15, 2013 at 17:28
0

I agree with the first answer, but in general, with a Microsoft CRM Database that is over 100 GB in size, I'd recommend having more RAM than 12 GB on the SQL Server, and would prefer seeing at least 32 GB of RAM for a Microsoft CRM database of this size. To be more specific about finding out the cause of what's contributing to your SQL Server performance issues, you need to identify the queries that are causing the greatest load to the server. These are not always the single query, but are sometimes a set of parameterized queries that may be executing thousands of times that are contributing to the performance degradation. You can identify these using the SQL Server Perf Stats collection scripts to collect data during peak load times for about 5 minutes of time, http://sqlnexus.codeplex.com/wikipage?title=Sql2005PerfStatsScript&referringTitle=Home , (just use the StartSQLDiagTrace20082.cmd and not the detailed trace collections). You can use the SQLNexus tool available at http://sqlnexus.codeplex.com/ to help analyze the data and view reports that can help show you which query or queries are causing the most load to the server by Total Duration, Total Reads, Total CPU, or Total Writes. I usually focus on the top queries by Total Reads or Total Duration and get examples of those queries to run through SQL Server Database tuning advisor to find if there's any missing indexes or statistics that can be applied to improve performance.

With your SQL Server having 3 cores, it will also get better performance with a Microsoft Dynamics CRM database if you set the Max Degree of Parallelism to 1. See below for those commands to set that.

sp_configure 'Show Advanced Options', 1 Reconfigure; GO sp_configure 'max degree of parallelism',1 Reconfigure with Override; GO sp_configure 'Show Advanced Options', 0 Reconfigure; GO

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .