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
Solved

SQL Server is sluggish.

Posted on 2014-01-23
11
183 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

In this article I will describe the Backup & Restore 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.
A procedure for exporting installed hotfix details of remote computers using powershell
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 setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…

809 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