Hello Everyone,

I have a very complex performance issue with our production database. Here's the scenario. We have a production webserver server and a development web server. Both are running SQL Server 2000.

I encounted various performance issues with the production server with a particular query. It would take approximately 22 seconds to return 100 rows, thats about 0.22 seconds per row. Note: I ran the query in single user mode. So I tested the query on the Development server by taking a backup (.dmp) of the database and moving it onto the dev server. I ran the same query and found that it ran in less than a second.

I took a look at the query execution plan and I found that they we're the exact same in both cases.

Then I took a look at the various index's, and again I found no differences in the table indices.

If both databases are identical, I'm assumeing that the issue is related to some external hardware issue like: disk space, memory etc. Or could it be OS software related issues, like service packs, SQL Server configuations etc.

Here's what I've done to rule out some obvious hardware issues on the prod server:
1. Moved all extraneous files to a secondary harddrive to free up space on the primary harddrive. There is 55gb's of free space on the disk.
2. Applied SQL Server SP4 service packs
3. Defragmented the primary harddrive
4. Applied all Windows Server 2003 updates

Here is the prod servers system specs:
2x Intel Xeon 2.67GHZ
Total Physical Memory 2GB, Available Physical Memory 815MB
Windows Server 2003 SE /w SP1
Here is the dev serers system specs:
2x Intel Xeon 2.80GHz
2GB DDR2-SDRAM
Windows Server 2003 SE /w SP1

Sql server is set to dynamically configure its own memeory. It has access to all 2gb of memeory. Generally sqlsevr.exe takes about 700mb's, and at anytime, the system has about 800mb of available physical memory.

The server itself is not overloaded with processes. Generally it will the processors will operate at under 5% on a daily basis. When I execute a specific query, it will jump to about 25%. I tried to run perfmon, a while ago. I'm not very familar with it so I couldn't use the information to find any leads. Any ideas what counters I should use?

I've also checked table fragmentation using showcontig and DBREINDEX. What should I look for in the results?

I'm not sure what else to do, the query performance is an order of magnitude difference and I can't explain it. To me its is a hardware or operating system related issue.

Any Ideas would help me greatly!
Thanks,
Brian T

Are the versions of the software on both servers the same? Is all the software on both servers fully updated?

If I found myself in the same situation (and I won't explain why I wouldn't), I would do a few things and see if any of them helped.

Make sure all updates are applied. If the problem still exists, try adjusting your dev SQL server config so that it matches how your production server's should be. If using that config on your dev server does not change the performance, try that exact config (copy config files if possible) to the production server.

If that doesn't help anything, try some maintenance tasks on your SQL server if available. If nothing seems to help, just reinstall the SQL server. If, after that, there are still problems, you might try swapping hard drives between the dev and production server to see if the problem exists even with the dev set-up. If it does, then there's some hardware problem. (Perhaps you're running into one of the many pitfalls in one of the many Xeon variations.)

If the dev server runs the queries slow with the production server's hd and the production server runs the queries quickly with the dev server's hd, there are problems with the disk or the data on it -- probably the data on it if this is the only problem you have. If you got this far and still have a problem, it's time to back up important data (ignoring configurations -- they may be causing problems), reformat the disk, and reinstall and reconfigure everything.

If the problem still persists after that, use a new hd. If after that, there's still a problem, your case is infested by demons.

Hi mkp

We had reciently installed SQL Service Pack 4 to the prod system, so it has the lastest version. As for the config files, I looked through C:\Program Files\Microsoft SQL Server\ but I couldn't find any .config files. Can you give me the path for this file? So I looked at the server properties in SQL Enterprise manager. The only difference was that the dev server had SP3 installed, where as the Prod server had SP4 installed.

So I'm stuck.. I've done most of the easy steps now I'm looking at a reinstall of SQL server on the production server. I will also try to install sql server and that db on a new harddrive to see any improvments. I can't test swapping harddrive. We need our prod server to be online 24hours, with little downtime. SO the only thing that I can try is to install the db on a new hd.

At the moment I do not want to go through a reinstall of sql server or a format of the hd, or anything of that nature. I will first try to exhaust any immediate leads first.

Any more ideas?

Thanks for yoru help mkp,

Brian

What's the query look like and what size of tables is it working with? Perhaps it's fast on the dev server as it's in a query cache? If you stop sql server on dev, and start it up and run the query is it still fast?

Other things that might help is what is the system doing while the query is running on prod server? Is CPU pegged, or disk drives pegged?

Cheers,

Alex
--
Gossamer Threads Inc.

Hi Alex,

I tried what you have suggested and restarted the sql server on dev and ran the query, and it still ran very fast. So its not a cache issue. Would it be usefull to post the query? I don't think so, because both queries (and dbs) are the exact same on both servers.

I've been useing perfmon to monitor any unusual activity. But I've had no luck finding anything. Generally % Processor Time stays below 5%, when executing a query it only rises to about 25%. Avg Queue length also stays very low as well as pages/sec. Do you have any suggestions on what counters I should be listening to?

Thanks for ur help, any more ideas?

Brian

I could tell you a few places where configuration files are likely to be found, but knowing Microsoft, those are the last places they'd be. Everything might be hidden "safely" away in the registry. And as for the names of the configuration files (assuming there are any), my guess is as good as yours.

My thinking is that there are some extraneous factors causing your SQL server to run slowly. Those factors are probably hidden out of sight, and you probably would have had to write the software to know where to look or what you are looking for.

Do you have this same issue with other queries? Do simple SELECTs or UPDATEs take a lot longer than they should? If not, it's probably just the database. Try exporting the data from the database, removing it, and then importing it. (MSSQL doesn't allow you to rebuild the database or anything useful like that, does it?)


You could try some other things like defragmenting the drive or checking for errors on it, but that might result in a bit of downtime and the benefits are usually short-lived.

Hi mkp,

Thanks for your help, I have an update that looks very promissing.

I manged to look at the text based actual query execution plan on the different servers and I found some very minute differences in the plan. These differences are in the following format: Nested Loops(Inner Join, OURTER REFERENCES: (TABLE.COLUMN)). Now on both servers the table is the same, but the difference between both servers is in the column value. There is also a clustered index seek difference. Here is the difference: Clustered Index Seek(OBJECT(___), SEEK(____) ORDERED FORWARD) both of the ____ objects are different in prod rather then dev.

So I made sure that the index's on both servers we're the EXACT same, and that has been verified. So here are my questions:
1. What else affects query execution plans?
2. How can I find out if the difference in the execution plans, results in the difference in execution time?

Any help would be greatly appreciated!

Thanks,
Brian

Hello everyone,

After finding these differencees in the query execution plan. I ran update statistics and viewed the query execution plan. THey are not both identical, but I still have the bad performance on the prod server.

I'm running out of leads here, any other suggestions?

Thanks for your patience.
Brian

Hello everyone,

Here is a final update. We have resolved the performance problem by reinstalling SQL Server on the production machine. The server was reinstalled with SP3a. We ran the test query, and it performed exactly as it should returning results almost instantly 0.0035 seconds per row. Which is about 60x better then it had been performing before.

I know we didn't get to the bottom of the performance issue, we tried everything I could think of (with all of your help of course!). I know a reinstall does not give us the answers as clearly as we like, but now it’s a matter of documenting our current performance baseline, so that we have something to compare against if performance degrades again.

Again, thank you everyone for all of your help.
Brian T

It is good to see that it's been resolved. Often times (and this is especially the case with proprietary software), a program will begin misbehaving for no apparent reason and the only solution is to reinstall. Perhaps talking to the developers would have helped track down and resolve the issue, but that would have probably meant a new patch you'd have to install (and they might never find the problem -- not to mention that it's very difficult to get a message to the developers of proprietary software made by a large company). Sometimes there is no satisfactoy answer. But at least the problem no longer effects you.