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

Question

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).

Answer 1

Interesting thing that should be considered is the version of MSSQL 2000 running

There are four versions of the binaries

1. Express
2. Standard
3. Professional
4. Enterprise

Each of those versions have limits in terms of RAM and CPU.

It is worth exploring the possibility that the amount of data currently stored has simply outgrown the capabilities of the version of MSSQL 2000 due to queries needing more RAM to fulfuill queries/subqueries or inadequate CPU utilization. You may require upgrading the binary version to the MSSQL 2000 Entrprise version (probably a long shot becasue of how old your version of MSSQL is) or the best version your budget can afford.

You may even want to get out of MSSQL 2000 since 2008 is the latest and has current support available. Again, this could be a budget issue. If you are already using Enterprise, or your budget cannot allow for any major upgrade, now you can explore DB Statistics or DB Design.

Disclaimer : I'm not a SQL Server DBA

Answer 2

Thoughts:

• hardware is almost never an issue: it's poor design and code
• always test with near-production data quality and quantity

Some solutions:

Run the missing index DMV to see, well, missing indexes:

SELECT 
  migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure, 
  'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle) 
  + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
  + ' ON ' + mid.statement 
  + ' (' + ISNULL (mid.equality_columns,'') 
    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END 
    + ISNULL (mid.inequality_columns, '')
  + ')' 
  + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement, 
  migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

...and most expensive DMV queries

SELECT TOP 20
    qs.sql_handle,
    qs.execution_count,
    qs.total_worker_time AS Total_CPU,
    total_CPU_inSeconds = --Converted from microseconds
    qs.total_worker_time/1000000,
    average_CPU_inSeconds = --Converted from microseconds
    (qs.total_worker_time/1000000) / qs.execution_count,
    qs.total_elapsed_time,
    total_elapsed_time_inSeconds = --Converted from microseconds
    qs.total_elapsed_time/1000000,
    st.text,
    qp.query_plan
FROM
    sys.dm_exec_query_stats AS qs
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
        CROSS apply sys.dm_exec_query_plan (qs.plan_handle) AS qp
ORDER BY qs.total_worker_time DESC

Otherwise, this SO question has good tips from me and other SQL high rep types: http://stackoverflow.com/q/4118156/27535 (I won't copy/paste all 3 longish answers)

Answer 3

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.

Answer 4

Log system resources or look at task manager to see how many system resources are used by the processes.