April 5, 2010 at 12:54 pm
Marts (4/5/2010)
One point I must note here, we have only sql server standard, and unless I'm very much mistaken it is limited to 2gb ram usage 🙁
Time to upgrade to Windows 2003 and SQL Server 2005! SQL Server 2005 Standard is limited to the OS maximum - 4GB for Windows 2003 Standard (32-bit), 32GB for the 64-bit version.
ramdrive for the pagefile
Heh...absolutely no!
So it may make sense to keep the data files with the OS, and just move out the logs and tempdb onto seperate disks.
Yes. Use something like SQLIOSim or IOMeter[/url] to validate your choice.
April 5, 2010 at 1:50 pm
Yeah pagefile was just some sillyness, a random thought.
On our current setup without any modifications, I could move the logs and/or the tempdb to the system drive (raid 1), away from the data files.
Would you think that could help at all and if so, should I move logs or tempdb or both?
Regards
Marts
April 5, 2010 at 2:51 pm
Marts, I HIGHLY recommend that you try to convince your company to hire a performance tuning professional for a day or two to give your system a review and recommend the optimal configuration for your stuff. Best for you could be everything from an 8 drive raid 5 to JBOD, with a bunch of possibilities in between.
Failing that, make DAMN SURE you do the following: a) sector align whatever you create, b) use 64K cluster size on the NTFS format, c) make just ONE DRIVE on each disk collection you create. You don't have nearly enough drives to go around and you need to get every ounce of performance out of them you can.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 5, 2010 at 7:38 pm
TheSQLGuru (4/5/2010)
Marts, I HIGHLY recommend that you try to convince your company to hire a performance tuning professional for a day or two to give your system a review and recommend the optimal configuration for your stuff. Best for you could be everything from an 8 drive raid 5 to JBOD, with a bunch of possibilities in between.Failing that, make DAMN SURE you do the following: a) sector align whatever you create, b) use 64K cluster size on the NTFS format, c) make just ONE DRIVE on each disk collection you create. You don't have nearly enough drives to go around and you need to get every ounce of performance out of them you can.
Ditto on that thought. Also have that PTP check for the most intensive disk I/O queries. Having great hardware is only half the equation. Having great code is the other half and most folks don't have that half. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
April 5, 2010 at 7:48 pm
Upgrading hardware has always been the easiet solution 🙂 I've worked at a place that purchased a 64 core (32 proc's) IA-64 cluster with 256GB of RAM active/passive cluster (with a major SAN upgrade to boot) rather than look at the code!
Developers leave, no one understand's what their original intention was, it's just easier to keep upgrading hardware.
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
April 5, 2010 at 8:37 pm
Well, I found something very wrong that I think I can fix realitively easily... I have no idea how the IT guy missed this, but I went into the dell openmanage tool and found the system mirror partion was "degraded", not being an IT guy I sent him an email saying does this matter...
He said, try a reboot, it may be a glitch, well I could see that there were 3 unused drives and a hot spare not being used, he went to the server room and confirmed that there were no red lights...
Turns out that chkdsk found errors, and according to some quick google research the mirror wont automatically rebuild if there are errors, looks like a first thing in the morning chkdsk /F is in order.
Since I had pagefile, windows and sever log files here, it might explain some of my issues.
As for the code, I know for a fact that there are a lot of things that I would change, but I cant get the system to respond to some of the bigger ones in a reasonable period of time, like changing a clustered index from a composite that uses device_id, date and something else to an existing rowid field, that table heeps getting fragmented and very inefficient (I wonder why...).
There were a bunch of triggers that used cursors eeep, I changed them to use while loops at least, and have been grinding through the ones I could get into set form as I can (since this is not my main job function, I have less time than desired).
Hopfully getting the system mirror fixed will alliveate some stress in the system, and some of your tips that I can integrate will get us moving in a more reasonable fassion 🙂
I now have a mirror setup for exclusive log file storage, so I hope to get those sorted tomorow.
Marts
April 5, 2010 at 9:42 pm
Marts (4/5/2010)
There were a bunch of triggers that used cursors eeep, I changed them to use while loops at least,
Stop. It's a waste of time to do that. A read only, forward only cursor will run just as good as any While Loop. Think about it... what's in a typical cursor? A cursor table and a While Loop. Same as a TempTable and a While Loop and the cursor table isn't the problem... the While Loop is the problem.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 5, 2010 at 10:23 pm
Jeff Moden (4/5/2010)
Marts (4/5/2010)
There were a bunch of triggers that used cursors eeep, I changed them to use while loops at least,Stop. It's a waste of time to do that. A read only, forward only cursor will run just as good as any While Loop. Think about it... what's in a typical cursor? A cursor table and a While Loop. Same as a TempTable and a While Loop and the cursor table isn't the problem... the While Loop is the problem.
Agreed. Lots more important things to do here I think.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 6, 2010 at 1:54 am
Marts (4/5/2010)
On our current setup without any modifications, I could move the logs and/or the tempdb to the system drive (raid 1), away from the data files. Would you think that could help at all and if so, should I move logs or tempdb or both?
Moving the logs to dedicated disk (i.e. not shared with anything else at all) is generally the best first move. That said, I do appreciate the restrictions you have here, so if the only immediate option is to move either tempdb or logs away from the data and onto the system drive, I would move the logs. You will not get the full benefit, but it might be a start, if it is easy to try.
As an aside, I have to say to the others suggesting that you hire a professional consultant: read back for the comments about how tight funds are. Any money available would probably be better spent on an upgrade to Windows 2003 and SQL Server 2005.
You appear to be using the software RAID provided by Windows, is that correct?
April 6, 2010 at 1:59 am
Marts (4/5/2010)
system mirror partion was "degraded"
It is important to fix this, from a recovery point of view, but I doubt it is affecting performance much.
As for the code, I know for a fact that there are a lot of things that I would change, but I cant get the system to respond to some of the bigger ones in a reasonable period of time, like changing a clustered index from a composite that uses device_id, date and something else to an existing rowid field, that table heeps getting fragmented and very inefficient (I wonder why...).
There were a bunch of triggers that used cursors eeep, I changed them to use while loops at least, and have been grinding through the ones I could get into set form as I can (since this is not my main job function, I have less time than desired).
I feel your pain here 🙂
I now have a mirror setup for exclusive log file storage, so I hope to get those sorted tomorow.
Good news - sorry I missed this comment on my initial sweep of the new replies. Having separate physical disks for logging is pretty much essential.
April 6, 2010 at 2:07 am
Jeff Moden (4/5/2010)
Marts (4/5/2010)
There were a bunch of triggers that used cursors eeep, I changed them to use while loops at least,Stop. It's a waste of time to do that. A read only, forward only cursor will run just as good as any While Loop. Think about it... what's in a typical cursor? A cursor table and a While Loop. Same as a TempTable and a While Loop and the cursor table isn't the problem... the While Loop is the problem.
A fully optimized cursor has generally comparable performance to a WHILE loop, true.
Default cursors (dynamic, update-able, supports all fetch options) can be much slower.
We do not have enough information to say how Marts time should be best spent, and Marts has already said that he is converting to set-based code as and when time allows.
Given the circumstances (no money, no time, ancient software and hardware) I think Marts is doing pretty well - and gets extra marks from me for just caring enough to want to improve stuff.
Too many responses in this thread have come off as critical and borderline patronising for my liking.
Given unlimited money, I'm sure Marts would love to order some shiny new hardware and get a team of consultants in to make the system fly...but let's be realistic 😛
April 6, 2010 at 5:25 am
I appriceate the feedback, quick comment here, the server has 8 physical disks and a hardware based raid controller with 2 channels.
Currently I have one system mirror (degraded and trying to fix today)
A new logs only mirror (mapped as a folder on the data drive is that ok?)
Main raid 5 with 3 disks
Leaving one global hot spare capable of hitting any of the virtual disks.
All the origional cursors I "fixed" were from a long time ago where I read lots of things that said cursors were the anti-christ, so the first thought was to make table variables with while loops, not outstanding, but better than a standard cursor. And as I said, when I get some time I try to determine the appropriate set bassed solution.
I am nervous about the degraged system drive, but I guess if that dosn't impact performance, at least I would be able to sleep better at night when its fixed.
Once that is done, do you think I should move the tempdb to the system mirror? without a major reconfigure, it is something I could easily achieve today.
Sorry for the rambling responses and thanks again 🙂
Marts
April 6, 2010 at 5:41 am
Marts (4/6/2010)
...the server has 8 physical disks and a hardware based raid controller with 2 channels.
Thanks for clarifying - much better than software RAID, then 🙂
Currently I have one system mirror (degraded and trying to fix today)
A new logs only mirror (mapped as a folder on the data drive is that ok?)
Main raid 5 with 3 disks
Leaving one global hot spare capable of hitting any of the virtual disks.
When you say 'mapped as a folder', do you mean a mount point? Sounds fine though - just curious about the terminology. Ideally, the log disks would be served by a separate channel from all the other I/O, but it isn't vital.
Once that is done, do you think I should move the tempdb to the system mirror? without a major reconfigure, it is something I could easily achieve today.
I do think it is worth trying - assuming the system mirror has a low level of I/O once the server reaches steady-state. Might be an idea to test throughput and average latency with something like IOMeter[/url] before and after making changes, for comparison.
April 6, 2010 at 5:49 am
One last question - does the server have no direct-attach storage that you could use for tempdb?
April 6, 2010 at 6:07 am
I can check with my IT guy to see if something can be arranged. Does the tempdb storage need to be quick, or just seperated from other stuff?
Hmm chkdsk /F did not fix everything in a single pass I hope it does this time.
Viewing 15 posts - 16 through 30 (of 51 total)
You must be logged in to reply to this topic. Login to reply