Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server is sluggish.

Posted on 2014-01-23
11
Medium Priority
?
194 Views
Last Modified: 2014-03-13
We have two Terminal Servers running 2008 Server off of a Xen Server. They use remote app to run one application. The Databases are on a completely separate server. It is also Server 2008 and the Virtual Server is running on Xen as well. Both Terminal Servers have about 50 users each. The DB Server has 70 Databases on it. What are the recommended server specs for the Database server with that many databases on it. The DB's are not very big. Most of them are less than 2gig in size. It is mostly 1 user hitting each. Some have 2 or 3 but not most. We have 8 drives in a Raid 10 with 300gig 10k rpm. However we are having some performance issues. Have not nailed it down. We don't want the Microsoft patented answer. We want real world.
0
Comment
Question by:aando
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
11 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39804948
please check what is the actual physical ram on the sql box, and how much ram is used there by the sql server instance. if the RAM used is "too high", you need to reduce the max memory setting on the instance level. usually, it's set on the default "unlimited". if your sql server box has actually 8GB of RAM, set that value of 6000 (MB), unless there are other applications running on the sql box (note: please make sure that sql server management studio is NOT run on the sql server box itself...

next point is : are the databases set to auto-close? if yes, untick that database property.
0
 

Author Comment

by:aando
ID: 39804981
Almost forgot. They have 32gig on DB Server and are using about 3.88gig of its memory. So it isn't using hardly any memory.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39807919
Hi,

Have you done any index maintenance recently/at all? That would help performance.

For the database server, if you have all 8 drives in one raid10, how are they partitioned?

I'd be interested if the results of this:
select
	db_name( mf.database_id) as database_name
	, mf.name
	, mf.physical_name
	, fs.io_stall_read_ms
	, fs.num_of_reads
	, ( fs.io_stall_read_ms / ( 1.0 + fs.num_of_reads ) ) as Avg_ms_read -- recommendation is that this is less than 100
	, fs.io_stall_write_ms
	, fs.num_of_writes
	, ( fs.io_stall_write_ms / ( 1.0 + fs.num_of_writes ) ) as Avg_ms_write -- recommendation is that this is less than 20
from sys.dm_io_virtual_file_stats( null, null ) as fs
inner join sys.master_files as mf 
	on fs.database_id = mf.database_id
	and fs.[file_id] = mf.[file_id]
where   
	1 = 1
;

Open in new window


HTH
  David
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:aando
ID: 39808181
David, I haven't done any index maintenance. The 8 drives in a Raid 10 have a C drive that is 82gig with 35gig free. The D drive is 511gig with 404gig free. The P drive which is for the PageFile is 37gig with 4gig free. Also I don't know much about SQL so I really don't understand what you listed above.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39808207
Hi

The above code is T-SQL that you can run as a query in SQL Server Management Studio (SSMS).

It is a good idea to keep at least 20% free on all disks. Properly different disks should different disk subsystems. Not just different partitions on the one raid volume.

Maintaining the indexes is important.

When you figure out running SQL scripts, look at Ola Hallengren's script at www.hallengren.com.

Regards
  David
0
 

Author Comment

by:aando
ID: 39840900
They all have more than 20%. Around 50%.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39840974
Hi,

Are you running a 64bit edition of SQL? How much RAM vs the total size of the databases?

Is the sluggish just happened, or has it be gradually getting this way?

Regards
  David
0
 

Author Comment

by:aando
ID: 39900846
Yes it is a 64bit edition of SQL. Very little ram is used. There are many Databases though. There are about 40 databases of 1-5gig in size.

can't tell on sluggishness.
0
 
LVL 35

Accepted Solution

by:
David Todd earned 1500 total points
ID: 39901481
Hi,

One rule of thumb I heard years ago is that the server ram should be equal to the size of the database.

This suggests that you should have 40-60GB of ram.

But you have 32GB of ram on the server and it isn't being used. Do check the edition of SQL and Windows, and check the servers max and minimum memory settings.

Regards
  David

PS Also run Brent Ozar's sp_Blitz to see if there's anything else that you should quickly look at.
http://www.brentozar.com/blitz/
0
 

Author Comment

by:aando
ID: 39914096
So I should make the memory equal to the total amount of gigs in Data or more?
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39914213
Hi,

Try increasing the max memory setting and see what happens. You'll run into a point of diminishing returns, ie the advantage of extra memory starts to decrease.

HTH
  David
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
Resolving an irritating Remote Desktop connection that stops your saved credentials from being used.
This tutorial will give a an overview on how to deploy remote agents in Backup Exec 2012 to new servers. Click on the Backup Exec button in the upper left corner. From here, are global settings for the application such as connecting to a remote Back…
This tutorial will walk an individual through locating and launching the BEUtility application and how to execute it on the appropriate database. Log onto the server running the Backup Exec database. In a larger environment, this would generally be …

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question