Solved

SQL Server is sluggish.

Posted on 2014-01-23
11
178 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Let’s list some of the technologies that enable smooth teleworking. 
This tutorial will walk an individual through the steps necessary to install and configure the Windows Server Backup Utility. Directly connect an external storage device such as a USB drive, or CD\DVD burner: If the device is a USB drive, ensure i…
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…

895 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

13 Experts available now in Live!

Get 1:1 Help Now