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