Performance tuning MS SQL 2005

I work for a small to mid-size business, and am a jack-of-all-trades: some application development, database design, technical support, etc.  I have been working a lot lately with SQL server 2005.  We have some databases that contain a large (for us) amount of info... anywhere between 5-20 million rows in the largest tables.  I'm interested in purchasing any type of software that will assist with performance tuning on these databases.  I doubt I manage updates/inserts/deletes the most effectively, but am not sure how to identify and analyze bottlenecks.  Outside of getting a consultant (which we're open to, but that's a different question), do you have any recommendations on software to help us with this?
christopher_perryAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jogosCommented:
The first tools are for free
- sql*profiler: tells you which statement is heavy or many times called , heavy in the way of cpu,duration or read/writes
  + from a (representative) profiler trace you can call a optimasation analysis -> will give you indexes or statistics you can benifit with
- query window -> execution plans can learn you where full table scans occur (previous step is this on the whole load)
- server monitoring : cpu, memory, disk I/O
 
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lundnakCommented:
I haven't used this product but Quest's "Workload Analysis for Oracle and SQL Server" looks good.  I ahve used other products from Quest and have been very pleased.

I am also a big fan of SQL Profiler.  In my experience most DB problems are the result of poorly tuned queries, and/or bad indexing methodology.
0
jogosCommented:
addition to my "The first tools are for free".
They are already on your server
- SQL*profiler is a part of your SQL*Server so you don't even have to install it.
- performance monitor for your server monitoring
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.