October 2, 2011 at 7:24 pm
I have a small client who was running on 1 disk for the SQL Server server and the server wasn't even dedicated to SQL Server. After a year of performance tuning queries and indexes, they finally upgraded to a 64bit server and version (R2). But when I logged in there were only 2 logical disks - the hosting provider says they are raid 1 so (2x2=4 disks). I don't think it's a winning battle to get more information that that, so i'm stuck with configuring the server as is.
Would it be recommended to put anything on the disk drive with the SQL Server binaries (C)
or could the log files or tempdb go on it, let's assume the 2 disks are separate spindles.
thanks
October 2, 2011 at 11:15 pm
you dont seem to have much options. Generally it's not the best ides to put database files on the drive used by OS. But in this scenario, you may keep the files there and check the disk usage and any possible latency.
October 3, 2011 at 12:18 am
SQL running on single disk is not advised especially if its business critical. RAID is acceptable to some extent. If possible try adding an external disk for backups and transaction logs for that server. Though not advised, necessity beckons it.
SQL binaries / logs / backups - keeping them seperate from each other is the best safe bet.
October 3, 2011 at 3:15 am
SQL Server reads / executes binaries when it starts. After that it uses memory to refer SQL Server objects. Thus there would be no (or less) read / write operations on the disk that hosts SQL Server binaries.
You can take performance advantage if you put your tempdb with SQL binaries. The data & log should go to separate disk.
The ideal solution would be keeping binaries, data files, log files & tempdb on separate disks.
October 3, 2011 at 7:42 am
Thanks all for the advice. I have been saying to the client the 'best' practices for over a year, but it is a really really small startup and just getting this upgrade is like a big win. I've asked for separate disks for all three components but it just isn't going to happen now.
So my plan is to put the data(including tempdb) on it's on drive and the log file on the C drive with the binaries (including tempdb log).
Anyone got any better ideas? Again even if I could get more disks, it would not happen before I need to get this current situation up and running, which is a lot better than 1 disk that is currently running.
October 3, 2011 at 7:57 am
seriously how busy are the disks on avg?
The day all the queries are tuned correctly and you're still getting IO problem because you have too much sales (and losing some of them because of that) is the day you'll get a new server. Or a few weeks prior to that.
That's fine for a start up. And I like a client that nickles his way around (while not being a scrooge), he seems to know how to manage the cie at this point.
October 3, 2011 at 8:06 am
Hi Again,
I don't have my disk stats in front of me. When I first started helping out, and i'm not getting paid a lot to help, I did all the diagnostics, and IO was the biggest issue. And believe me I've said it over and over again that we need separate/faster disks, we need a dedicated server, we need... the CIO knows what's going and he's feeling the pain cause he runs/control most of the software at this point.
So on the top of my head, all the issues that occur show high IO wait times.
And it gets better the SQL Server serves up both transactional and OLAP processing.
I know it is a 'mess', but I was able to tune what was running over the last year to get acceptable results, but then the server started crashing, hence the new 64 bit server, with 12GB ram vs old 32 bit with 4GB ram. But the hosting provider says hey you are paying for 4 disks, what they don't say is they are Raid1 so 2 logical drives.
By the time I get involved (low paid PT) It was already set up and done.
thanks for the help.
October 3, 2011 at 8:17 am
There's still an easy / cheap way out for now. Just overload the server with ram (as much as it can take, or the client is willing to pay for).
Low end ram is like 5-10$ / GB now, so even an extra 50 GB should be relatively cheap (compared to a full time consultant trying to help).
It won't solve the disk issue, but it'll relieve a whole lot of pressure.
Also I'm 99% sure that you already went down that path but this might give you more tuning opportunities to relieve the disks =>
Instead of simply duration, I'd concentrate to group on reads / writes.
Don't underestimate the cost of a query running "decent" 100 times / hour. That adds up really fast.
October 3, 2011 at 9:11 am
thanks Ninja's_RGR'us
I scanned those links and I will read them more in depth along with some others i've seen.
I will definitely use your advice on the RAM .
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply