473,836 Members | 1,593 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SQL Server disk sub-system (overhaul) performance questions

I 'inherited' a group of SQL Server server class machines. They are
true server technology but the disk sub-systems are lacking. There is
one hot-swap backplane that all the drives share (with one SCSI
channel) thusly even though there are three logical drives (composed
from 6 to 8 hard drives), they all go through one channel. This is
creating a performance issue that is noticable and can be seen in
various performance counters that Microsoft recommended one should
monitor in terms of disk I/O. For a cheaper 'fix', I can add a
seperate two drive bay (with its own SCSI channel) with mirrored
drives. I would then mostly likely place the transaction log files on
this new channel. Or I could place the indices filegroup files on
this new channel for DBs with mainly searching going on (not much
updating). If I went this route I would be leaning towards the
transaction log move since the second method would require me moving
DBs around quite a bit. Any input on this solution (besides spending
more money)?

What I would prefer to do is get a better server class machine or add
an external drive bay solution (not a SAN). I would try to get three
or four SCSI channels in the new hardware to split the different
file/filegroups out (i.e. transaction logs files, data filegroup,
indices filegroup, etc.). My only concern here is: would this more
expensive solution be worth the money? As far as replacing servers, I
have only two kinds of experience...re placing somewhat underpowered
servers with slightly less underpowered servers and replacing overkill
servers with even more overkill servers. In both cases, the disk
sub-systems were fairly equivalent from the old system to the new one.

Will going the three/four channel route really get data moving along?
We have one server in particular that hosts a database (one of many on
it) for a web application that gets decent traffic (it is a private
login based system for internal use and external use by our clients'
agents). Periodically throughout the day, there are 2-5 minute bursts
where performance slows to a crawl. I want to spend more time
profiling queries and such before recommending we spend more money,
but the folks I am working for want quick results and there is quite a
bit of stored procedure logic to profile and investigate. I know the
disk sub-system is definately in need of an overhaul, but I would like
to get an idea of peformance gains from adding either one additional
channel over the existing single channel as well as going the
three/four channel route over the existing single channel setup.

Any information would be greatly appreciated.

Regards,
Tony
Jul 20 '05 #1
2 1957
ajjvn (aj***@hotmail. com) writes:
Will going the three/four channel route really get data moving along?
We have one server in particular that hosts a database (one of many on
it) for a web application that gets decent traffic (it is a private
login based system for internal use and external use by our clients'
agents). Periodically throughout the day, there are 2-5 minute bursts
where performance slows to a crawl. I want to spend more time
profiling queries and such before recommending we spend more money,
but the folks I am working for want quick results and there is quite a
bit of stored procedure logic to profile and investigate. I know the
disk sub-system is definately in need of an overhaul, but I would like
to get an idea of peformance gains from adding either one additional
channel over the existing single channel as well as going the
three/four channel route over the existing single channel setup.


I don't have much experience of dimensioning servers, but from what you
say it seems that upgrading the hardware is going to improve the system,
but the performance problems with remain. Whlie better hardware can
make some performance issues negligible, there are many ways to make
a database engine so slow, that no hardware in the world can save it.

So I predict that even if you buy all those disks, you will eventually
be asked to analyse the application and look for ways to improve it.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
What you say is true. We are doing a lot of profiling and analyzing
but we know a server replacement will be in order down the road to
handle a known increase in load. What we are wondering is if we move
the hardware replacement sooner, how much will we benefit from it
(based on others observations of somewhat similar changes in
hardware).

Anyone?

TIA,
Tony

--------------

Erland Sommarskog <so****@algonet .se> wrote in message news:<Xn******* **************@ 127.0.0.1>...
ajjvn (aj***@hotmail. com) writes:
Will going the three/four channel route really get data moving along?
We have one server in particular that hosts a database (one of many on
it) for a web application that gets decent traffic (it is a private
login based system for internal use and external use by our clients'
agents). Periodically throughout the day, there are 2-5 minute bursts
where performance slows to a crawl. I want to spend more time
profiling queries and such before recommending we spend more money,
but the folks I am working for want quick results and there is quite a
bit of stored procedure logic to profile and investigate. I know the
disk sub-system is definately in need of an overhaul, but I would like
to get an idea of peformance gains from adding either one additional
channel over the existing single channel as well as going the
three/four channel route over the existing single channel setup.


I don't have much experience of dimensioning servers, but from what you
say it seems that upgrading the hardware is going to improve the system,
but the performance problems with remain. Whlie better hardware can
make some performance issues negligible, there are many ways to make
a database engine so slow, that no hardware in the world can save it.

So I predict that even if you buy all those disks, you will eventually
be asked to analyse the application and look for ways to improve it.

Jul 20 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
6313
by: SK | last post by:
I am looking for an API to flush all data in memory held by SQL Server to disk. Also, is there a tool for SQL Server like eseutil for Exchange that lets you correct a SQL database?
1
2863
by: Prashant Thakwani | last post by:
Consider a scenario, I am a DBA for the Database server where the size of the hard disk is 100 GB. On that database server, i have the Database whose size is 40 GB. Also, i restrict the size of the database file to 95 GB, by specifying the "maximum file size" to "Restrict file growth" option , under the "file properties". I then copied a DAT ( or some other file ) on the same database server that is having the size of 45 GB. Now...
1
1946
by: Joriveek | last post by:
Trying to install SQL Server 2005 beta version on partition disk drive say F:\ (Other than local disk drive c:\), Does anyone know how can I do this? During the installation, it never asks me on which drive it install to nor browse button is there to browse the disk drive for installation... Any help??? Thanks
2
1479
by: Bruce Ramsey | last post by:
We have a web application written in ASP.NET using C#. The application renders Maps using the SVG (scalable vector graphics) standard. Normally the SVG is sent direct to the user from the Aspx page. However we wish to save the output to a file on the server for processing i.e conversion to a raster file etc, any ideas how to do this?
6
3100
by: ad | last post by:
I have a compressed file in client. How could we send this compressed file (.zip) to server by web service?
1
1197
by: DraguVaso | last post by:
Hi, I want to create a Service that Reads and Write Files, and Insert and Select Data from a Sql Server Database. I did a little test with a small Service that I wanted to write something to a File when it starts and stops. I did it like this: Protected Overrides Sub OnStart(ByVal args() As String) ' Add code here to start your service. This method should set things ' in motion so your service can do its work.
0
3156
by: Takpol | last post by:
Hello, I have several archived filegroups that have data in them partitioned based on the date. These filegroups have been removed from database after archival. For example two months ago. Meantime my production database is populating everyday. Now I would like to restore one of my old archived filegroups. In order to do that I would like to backup and restore the current Primary filegroup to another server, and also restore the...
14
28218
by: Lauren Wilson | last post by:
Discovered this interesting comment on MSDN: "To programmatically obtain the hard disk's serial number that the manufacturer assigns, use the Windows Management Instrumentation (WMI) Win32_PhysicalMedia (a class) property SerialNumber." I'm sorry to admit it bit I am really undereducated on how to incorporate some of the Windows SDK stuff into VBA apps. Anyone know of some sample code that will allow us to read the C drive hardware...
0
2119
by: elias.farah | last post by:
Hello Everyone, I've been searching for a few hours on how to check the available disk space (on a Flash disk actually) from within MS-Access. Basically, I want to ensure there is adequate room on the Flash drive before a Backup is attempted. I've been trying to get Doug's code working within Access 2007 (and Vista x64), but it doesn't seem to work for me. I pasted this into a
10
6170
by: gary0gilbert | last post by:
An unusual spin to this recurring disk or network error in a Terminal Server environment. Access 2000, Terminal Server 2000, file server is windows 2000. All users have a separate copy of the front end db, everyone accesses the back-end db via a network share. To preface, non Terminal Server users (4 or 5 in office) never have this problem. There are two Terminal Servers running win 2000, both basically identical. This error affects...
0
9673
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10859
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10260
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7795
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6984
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5653
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5829
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4463
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4023
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.