Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question
    
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? –  Bart De Vos Mar 15 '13 at 13:46
    
1024 MB that is, not GB, sorry. –  Bart De Vos Mar 17 '13 at 23:52
add comment

1 Answer

up vote 1 down vote accepted

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?
  • ...
share|improve this answer
1  
Thanks, I have added the info to the question. –  Stuart Mar 15 '13 at 13:01
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 '13 at 17:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.