1

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?

Improve this question
5
  • 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
    Commented Jan 9, 2013 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
    Commented Jan 9, 2013 at 16:19
  • 2
    Sounds like you are probably missing some useful indexes.
    – Martin Smith
    Commented Jan 9, 2013 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
    Commented Jan 10, 2013 at 13:20
  • @MartinSmith: you're right, that was also my first thought :-). 2 GB of RAM and CPU at 50%? Is it a dual processor and you've disabled parallelism by any chance? How do you check the RAM usage? If it's by using Task Manager, it's not reliable at all. Let's go from this info and we'll go on further. So: what version of OS, what version of SQL Server, how many CPUs, how much RAM do you have?
    – Marian
    Commented Jan 10, 2013 at 14:37

3 Answers 3

Reset to default
1

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
Improve this answer
1
  • "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
    Commented Jan 9, 2013 at 21:31
0

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.

Improve this answer
3
  • 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
    Commented Jan 9, 2013 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
    Commented Jan 9, 2013 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
    Commented Jan 9, 2013 at 19:01
0

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.