Solved

SQL Server is sluggish.

Posted on 2014-01-23
11
176 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
  • 5
  • 5
11 Comments
 
LVL 142

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
 

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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

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 500 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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 the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now