Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
It's almost guaranteed to be code and design, not hardware... –  gbn Jun 11 '11 at 7:52
    
If your queries could take advantage of Analytical Functions, sql server 2000 is definitely software-limited. –  bernd_k Jun 11 '11 at 15:21
    
Well, it's not entirely hardware at least - I put an instance of 2008 Express side-by-side on the test machine. 2000 times are still in the same range, 2008 times are all < 20ms. The waitstats don't seem to clarify the difference. After clearing stats and on an identical app test, 2000 had PAGEIOLATCH_SH wait time of 2.48s, avg wait 4ms. 2008 had 2.14s, avg wait 2ms. Which is better, but doesn't explain the 10-fold improvement in actual response times - is that just in generic engine enhancements from 2000 to 2008 that aren't reflected in stats? –  Pastymage Jun 13 '11 at 17:46
add comment

migrated from stackoverflow.com Jun 11 '11 at 11:32

This question came from our site for professional and enthusiast programmers.

4 Answers

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

share|improve this answer
    
Upgrading to 2008 seems to be a quick fix, because even 2008 Express with its 1 CPU 1 GB RAM limits has completely solved the problem. Still, I'd like to understand why/how... –  Pastymage Jun 13 '11 at 18:02
add comment

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)

share|improve this answer
    
Those DMVs are great for 2005+ but don't work on 2000. –  SqlSandwiches Jun 13 '11 at 1:07
    
@SqlSandwiches: oops, missed that. –  gbn Jun 13 '11 at 9:29
    
Tried this in the 2008 Express instance I set up - dm_exec_query_stats doesn't seem to be present? –  Pastymage Jun 13 '11 at 18:00
add comment

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.

share|improve this answer
    
I grabbed a couple of queries to order and filter the relevant info from waitstats, and it didn't tell me much (see my comment on the original item). –  Pastymage Jun 13 '11 at 18:54
    
@Pastymage also try running sp_updatestats in your SQL 2000 DB. They try the query again and see if there is any performance improvement. –  StanleyJohns Jun 14 '11 at 0:00
    
Nope, exactly the same. Ditto for DBCC updateusage. –  Pastymage Jun 15 '11 at 20:45
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.