Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Server is sluggish.

Posted on 2014-01-23
11
Medium Priority
?
195 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 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

A safe way to clean winsxs folder from your windows server 2008 R2 editions
This article provides a convenient collection of links to Microsoft provided Security Patches for operating systems that have reached their End of Life support cycle. Included operating systems covered by this article are Windows XP,  Windows Server…
This tutorial will give a short introduction and overview of Backup Exec 2012 and how to navigate and perform basic functions. Click on the Backup Exec button in the upper left corner. From here, are global settings for the application such as conne…
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…

879 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