How can I tell if my SQL Server DB performance is hardware-limited?

Testing an app currently under single-user load - as the test data has increased to production sizes (400k-2M rows per table), some SELECT sp's are not quite fast enough anymore (with limited test data, used to be <30ms each, now it's 100-200ms, but there are several, so the delay is becoming apparent in the UI).

You can use DBCC SQLPERF("waitstats"). This will return the wait times of what tasks your SQL server was waiting on. Detailed explanations of each counter can be found online. You can use this information to find out your bottlenecks.

Also, turn on the client statistics in query analyzer to see the wait times on the client side.

I am assuming you hardware has not changed since your initial test, so since they are constant, I wouldn't doubt them.

StackExchange Database Administrators Q#3271, answer score: 8