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.

I have a SQL server with about 150 tables and the 5 tables that are used the most consist of over 10 million rows each. I'm trying to see why the performance of the server has been diminishing day by day.

This is a corporate SQL server and we have never had a DBA to help with the server and it has gotten to the point where pulling 1000 records from one table can take 20 min.

The cpu is not pegging above 50% nor is the ram going above 2 gig of usage.

The I/O reads however are at 1.9 trillion reads in the last 48hrs.

I need some direction on how to attack this issue.

I heard something about having rowguid's causing issues with tables with millions of rows is this accurate?

share|improve this question

migrated from stackoverflow.com Jan 9 '13 at 21:22

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

    
SQL Server 2000? SERIOUSLY? That is - hm - 4 Generations behind? Well, seriously, what do you ask here. You have a DBA locally. He should be able to analyze your Problem. Otherwise I would start with some query Analysis of the heavy queries. But that is analytics - a Consulting Job. Your DBA should know how to help you. –  TomTom Jan 9 '13 at 16:06
1  
Are the GUID's used for the Primary key and if so are they set up for NewsequentialID? Also Agree with the above you guys might want to think about upgrading to a version that is less than 10 years old. –  Zane Jan 9 '13 at 16:13
    
We are using some old software that integrates with SQL server 2000 and i'm not sure of the possible problems with upgrading as the company who developed the software is no longer in business. And the GUID is not used for the PK. –  user1922240 Jan 9 '13 at 16:19
2  
Sounds like you are probably missing some useful indexes. –  Martin Smith Jan 9 '13 at 21:28
1  
BTW: You don't tell us anything about the server. If it is 32 bit then the RAM as shown in taskmgr won't go above 2GB except if you are using the 3GB switch. Also how big is the database in GB and what is the page life expectancy on the server? –  Martin Smith Jan 10 '13 at 13:20
show 1 more comment

3 Answers

sql 2000 does have some performance tools built into them. Run these on your queries to see if they give you any help.

  • Query Analyzer
  • Profiler
  • Index Wizard
  • Performance Monitor
share|improve this answer
    
"Query Analyzer" is the name of the tool that was the precursor to management studio. It isn't something that you can just run on your queries. –  Martin Smith Jan 9 '13 at 21:31
add comment

I work with DB that have millions of rows every day and I'm not having issues.

Try the performance tools with SQL. Try running Profiler to see the query and check if some of them need to be improved. Look at the indexes. Recalculate the statistics.

share|improve this answer
    
At TomTom I'm sorry i work for a company that uses old software and hardware it is not up to me and the software that uses the server does not work with any other version of SQL and is no longer in production. I will try to run the profiler but would that many I/O reads cause any issues or help point in the right direction of the indexes? –  user1922240 Jan 9 '13 at 16:14
    
You can see wich queries take long to run. Then, you can take those queries and copy-paste them in SQL Server, execute them with the execution plan. That way, you can see if there seems to be an index missing. –  Danielle Paquette-Harvey Jan 9 '13 at 16:15
    
Also, if you know there is an index on the table but SQL is not taking it, you might want to try to recreate the statistics. SQL uses statistics to know what index to use to read a table. –  Danielle Paquette-Harvey Jan 9 '13 at 16:16
    
I will try to rebuild the indexes next, I can't run that anytime soon until i can run it when the server is not being used thank you for all of your suggestions and i appreciate the time in helping me to find a possible solution. –  user1922240 Jan 9 '13 at 19:01
add comment

First thing I would look at is updating statistics (not the same thing as indexing, look in BOL for more information, look up UPDATE STATISTICS) and possibly rebuilding indexes. Statistics should be updated regularly in amaintenance job and if you had a dba you would be doing that. Likely you are now getting a less useful execution plan for many things. Next thing I would look at is hiring a dba, there is no excuse for not having a dba when you havea system with millions of records.

share|improve this answer
add comment

Your Answer

 
discard

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