• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 518
  • Last Modified:

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?
  • 2
2 Solutions
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
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.
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now