Performance issue with SQL 2000

[sergeiY]

Hi

I have a performance issue with a stored proc on SQL 2000

I run a dynamic search query across 3 DBs with couple thousands of records in one DB (about 19,000). When I run this query I am getting SQL Server CPU spike to 100% and average of 60/70% and it's for one user only. The box I am running this query is production strength dual CPU hips of memory good hardware etc (I work in a bank) so I don't think there is a problem with hardware.

I reviewed the query and made sure it uses indexes etc and my suspicion is that I am getting high CPU usage just because of high volume of data.

As I said before it is a dynamic query and if I don't select certain criteria I am not crossing over to DB with 19,000 records and I am getting respectable CPU usage of 3-5%

So my question is, is 19,000 records too much for SQL 2000 ? I thought it should handle much more without any performance problems.

 

[SemperFiDownUnda]

Where are the the other databases? Are they on the same machine? When you say you've checked the query how did you check it? Is there any reason you can't put this into a stored proceedure?

Its hard to say not knowing more but 19,000 doesn't sound to much. How large is 1 row? If 1 row is around the max size you are talking ~150 meg. If the databases are on different boxes you could be having some network issues.

I'd suggest using profiler and maybe pumping that throught the tuning wizard to see if it comes up with anything.

I'm just throwing ideas out based on the info you've given.

try SET SHOWPLAN and SET STATISTICS to try to hunt down the problem too.

I'd also break your 3 db query into their individual parts and see how they perform individually.

 

[sergeiY]

all the DBs are on the same cluster so there should be no network issues...

1 row has about 10-15 fields

I run the stored proc with and without joing other DBs it works fine with one DB but exceptionly high CPU usage with other...

I never worked with large DBs is 19,000 recrods too much for SQL 2000 ?

 

[SQLSister]

SQL Server handles tables with terrabytes of data and millions of records. 19000 is very few records. Why don't you post your query and we'll see whether if it's efficiency can be improved.

 

[sergeiY]

I just got confirmation that this DB has 7.5 million records, I recon thats why it's performing so badly. I was told that it had 19,000 ... I am going to clean up that DB and re-run my tests hopfully it will be OK

Thank you to everybody who participated in this discussion.

 

[nathanstevenson]

I am in a similar position as you are - the DB I have just become responsible for has 16.5 mil records just in the one table, it's 21gigs, but that one table alone is 2.4gigs.

It's at this point that you have to look very closely at your stored procedures, particularly the joins you are using, what order they are joined in, and exactly how you structure your queries.

Post your code and we'll have a look.

 

[SQLSister]

SOme things to consider with tables that size are:
first the indexes and transaction logs should each be on their own separate drive with their own drive controller. Second, you should look at splitting the database itself across differnt drives. WIth ENterprise Edition you can do distributed partitioned views which might help.
Your indexes should be periodically reindexed.
YOur queries and stored procedures must be optimized. Consider limiting the number of records returned. Perform mass updates and inserts and deltes in batches.
Consider if all those records are needed, if some are old data that would rarely be needed you can move it to an archive database or table so that you are searching through few records. This greatly depends onthe what the data is being used for so do not do this without extensive consultation with your users.

The following FAQ has some resources on performance tuning. This is complex subject which is why I suggest you get some books on it.

Useful Reference Books for SQL Server Professionals
faq183-3324