January 3, 2008 at 9:19 am
Tracey,
You mention an MS IO tool and that you use it to see the status of your IOs.
What MS IO Tool do you use, and when do you use it (ie, during normal operations, at initial build of the server, benchmarking,...)?
As for Quest Spotlight on SQL Server, I've found it to be quite resource-hungry and only suitable for running for short periods. SQL Litespeed is generally good, though at my last place we had intermittent issues with Virtual Shadow Copy (VSS) and VDI (for which Quest said that we were the only one with this problem, plus their dev team said they didn't even use VSS :w00t:). We asked them to come in for a technical review of the issue, which seemed to occur most often on our bigger DBs (+200 GB), and we got a sales pitch :doze:.
Presently where I'm working I've found that they have the same intermittent issue and were also told it was an isolated incident - version was > 4.7. So, my experience with Quest (UK) is not good.
Also, prices seem to vary greatly, so bargain hard with them (especially with active/passive clusters).
January 3, 2008 at 10:18 am
Here are some here
Click on the SQLIO i used this one.
I done this on Production and Test but at hours when no one around. You will need the disk space for dat file which grew to 12 gig for me.
January 3, 2008 at 10:22 am
Quest Spotlight the first one they brought out was advised only run for small periods of time.
The latest one is the enterprise which is for 24 hours.
SQL Litespeed is generally good not used.
I have to figure out back tool soon but still use good old SQL as i can rely on this.
January 3, 2008 at 12:52 pm
Understand envirionmental\infrastrusture challenges
Plan for availability, growth, performance and security
Think distributed and federated
Expect the Unexpected
Follow Best practices
Network with other teams (e.g. network, san, security etc...)
Enjoy
Here's a good reference point for more information:
http://www.microsoft.com/sql/prodinfo/previousversions/scalability.mspx
Cheers,
Phillip Cox
January 3, 2008 at 3:09 pm
sqlio and sqlstress and other such tools only apply theoretical tests onto the disk subsystem - they have little real relation to application use, and if you don't understand the patteren and sizes of io that your applications generate then totally a waste of time!
If you want to degrade server performance run spotlight for sure, but again with these tools if you don't understand what is produced then the cost is wasted.
None of these will help you work with particular size databases. I'd suggest you get the sql 2000 performance tuning handbook - it's a really good place to start and will walk you through basic principles ( still apply for 2005/2008 )
I've only worked with databases up to 1.5TB and I can assure you the principles are the same as a 10mb database - some things just take longer!!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 4, 2008 at 2:18 am
Thanks Colin, this was my concern and exactly what I was leading to...but wanted to clarify first.
Tracey,
I tend to only use such MS tools as SQLIO, SQLIOStress, and the latest one, SQLIOSim, when benchmarking a new box (or one that a sales person is try to sell as a supa-dupa IO perf box 😉 ) just to get a feel of what its doing - also, another useful tool is IOMeter (BTW, I never mix results from different tools, ie I never compare results from SQLIO from Server A with results from IOMeter from Server B). This goes for SAN setups too, where, with SQLIO I get an idea of the raw throughput of the setup I have. Once a system is in though, unless I change it in a big way, then running it any other time is not going to give me any more info than what i already know. With regard to the dat files, I set these as per the guidance to ensure that, with the SAN for example, the cache is flooded so that it does not distort the values (can't remember exactly now, but something like twice the size of the cache and multiple dat files based on processors), plus I use multiple threads based on number of processors and cores. Also, I run with different block sizes and read/write patterns, all automated for me.
What I'm getting at here, and what I think Colin was trying to say was, you need to understand the tools you will use, how to use it, what they are going to tell you, and be able to interpret the results in a useful way that will help you and you systems (and you company of course).
With regard to Spotlight, I have used the old versions and tested the latest one in a 'live' system, and we still found it to be intrusive in our environment. Interestingly, my boss at the time wanted to buy it based on the fact that he liked the look of it and thought it would be a good tool for the NetOps team to monitor. The NetOps team would then pick up on any issues and alert the DBA team. My 'then' boss was a competent guy with letters after his name and a high IQ (well, he kept telling us it was 😀 ) so I had the trial version running a while and asked him to tell me what all the 'pretty' lights meant. He hadn't a clue. Now, the NetOps team were a great team, but even they admit that Database stuff was a black art to them. So, when I did the same test (very unfair I know, but it almost got the point across) they said they wouldn't know when to call and when not to. They said it looked good, but what the hell did it all mean? The final nail in the cofin for Spotlight was when my boss, who still thought it would look good when visitors and potential partners/investors came to see our operations, was when I presented him with Quest's quote :w00t: .
net,
there is some good advice here, but there is also some flaky bits too. I'm sure you'll be fine, though. A couple of other bits I'v found useful include, the importance of the DB to the business, having fast near-line backup facilities for your backups (going direct to tape is IMHE a killer) and keeping it there on a rollover basis. Why, because you want to get a backup completed as fast as possible, but, also, if you are asked to recover a DB that is critical to the business, you want to be able to recover it as quickly as possible too. Another area often neglected is archiving and data life-cycle management. I second Chirag's comment re: Paul Randall, and there are a number of others to look at too, Kimberly Tripp, Kalen Delaney...the usual suspects, as well as some MS guys (can't remember them off the top of my head, but some very good stuff...ahhh think one was Mike RuthRuff :hehe: ).
Good luck with it, anyway.
January 4, 2008 at 3:16 am
And, as if by magic...there's a link in today's SQLServerCentral Headlines - Predeployment I/O Best Practices, by Mike Ruthruff et al 🙂
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliobp.mspx
which covers the usefulness of IO test/benchmark tools...
January 17, 2008 at 3:59 am
Hi net,
Guess this is a bit late, but came across an old post in my documentation by Paul Randal (and his good lady, Kimberly) that covers some good practice stuff for VLDBs and remembered your question post at SSC...here's the link:
Hope its useful
January 17, 2008 at 8:37 am
Hello,
late or not, each contribution is welcome, also for other people needs for info.
Thank
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply