I have a couple databases on a shared SQL Server 2005 cluster instance, that I would like performance metrics on. I have some processes that run for a very long time and suspect that code inefficiencies, rather than insufficient hardware are to blame.
I would like some way to get these performance metrics so that I can rule out the database hardware as the culprit.
That's tricky... you can use performance monitor to track hardware and OS factors - like CPU usage, memory; and also various SQL Server counters like queries per second. Obviously memory usage would tell you if you need more RAM, but its not so easy to tell if (say) high CPU usage is due to the inefficient code, or just intensive code.
Some of the counters are more helpful to drilling down ino performance issues - things like locks in the DB can be counted, the problem is you cannot tell how many is too many because all code works differently. You can tell if you're experiencing far too many, or if periods of slowness equate to large counts. This applies to various of the other counters too - go and have a look what there is to view.
The other thing to do is run a trace (sql server tools) to get a list of the queries that are run. Take a few of the slowest/biggest and see what executions plans come out when you run them - this would suggest you might optimise the queries, though its down to you to decide if the code is inefficient or just intensive as before.
Lastly, get a tool like Spotlight that rolls a lot of database stats up and displays them to you in detail.
I just read a great article on using windows built in typeperf.exe for just this issue. http://www.mssqltips.com/tip.asp?tip=1575
I'm just trying to make sure the bottleneck currently is not (and will not likely in the future) be a bottleneck for some of the queries I am running.
The amount of data that will be going through this application is going to quadruple in the next release. I want to be able to check to make sure I'm using less than 25% of the SQL Server resources when I run my jobs. The actual total CPU/RAM don't ever hit 100% but I'm trying to be prepared for what may happen with the increased data load. Any ideas how to get this?
Ah, sounds like a job for SQL Profiler. http://msdn.microsoft.com/en-us/library/ms181091(SQL.90).aspx