May 6, 2008 at 10:30 am
Hope someone has done this beofre.
I am looking for a way to validate an MS SQL 2005 installation so that is does not present "performance bottlenecks" when an application and/or data is introduced to an environment. Basically, are there any microsoft tools that can be used to ensure that the installation of SQL is performing optimally.
Your help is much appreciated
May 6, 2008 at 11:42 am
SQL Server performance is primarily the result of two things: Enough hardware set up correctly, and a well-built database.
The hardware part is easy. Don't try to run an enterprise system on a 286 chip with 16k of RAM (okay, so I'm showing my age, but I think the point makes itself). The right RAID set up can make a difference, but that's getting into small details at this point.
The main point for performance is a well-built database. That means the right tables with the right indexes, constraints, data types, primary and foreign keys, normalization, etc., as well as the right procs on top of that.
How can anyone, Microsoft or otherwise, tell you if your database is going to have performance bottlenecks, when so much of it is subjective? Don't rely on someone else, test it yourself. Test each piece, and when you're happy with them, test the whole thing. Then test it as if it were under a serious load (depends on your business; "serious load" could be ten connections, could be 10-million).
I don't know of any tool that can do that for you. Not sure I'd rely on one even if it did exist. How would such a tool know that "well, yeah, that proc takes 2 hours to run, but that's okay, since it's loading up a massive data warehouse, and any run time that's less than a full month is okay", vs "that proc takes 3 seconds? What the heck is it doing that for? It's a simple 1-column X 1-row query from one table with a simple Where clause!"?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 6, 2008 at 12:34 pm
Everything already stated is very true. Code, tables and indexes are going to be your worst problems. However, you can be sure that you've identified the really simple stuff if you use Microsofts SQL Server Best Practices Analyzer. It's free and catches all the silly and stupid stuff that is easy to forget about on the server. It won't help your code, tables or indexes, but I'd run it against any new system and then occasionally against existing systems, just as a check.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 6, 2008 at 12:45 pm
Both from microsoft -
theres sqlio which will tell you the maximum io throughput you can expect
and
sqliosim -which will simulate generic Sql activity and give you an idea of io rates you might get out of SQL
---------------------------------------------------------------------
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply