Poor Performance - Identical DBs But Different Performance

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
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
*** Sent via Developersdex http://www.developersdex.com ***Brian Tabios wrote:
> Any Ideas would help me greatly!
> Thanks,
> Brian T
>
> *** Sent via Developersdex http://www.developersdex.com ***
Is SQL configured the same way on both servers, same memory settings,
parallelism, etc.?
Does the query do a lot of sorting? How is TEMPDB configured between
the two machines?
Fire up Perfmon on the production machine and look at CPU, Avg. Disk
Queue Length when the query is running. Compare that to the development
machine.|||Hi Brian
Have you defragmented the indexes and updated statistics on the slower box?
When you defragmented the disc was SQL server running or not?
Have you checked sp_configure and sp_dboption values?
How did you time/execute the queries?
John
"Brian Tabios" wrote:
> 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
> 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
>
> *** Sent via Developersdex http://www.developersdex.com ***
>|||Brain,
I had similar problem in the past. It turned out that one of my DBA's was
shrinking the tempdb and also he left it on autogrow @. 1mb. The problem was
solved by configuring initial bigger TEMPDB and ofcourse, shrink job was
disabled.
--
Venkat
sql server admirer
"John Bell" wrote:
> Hi Brian
> Have you defragmented the indexes and updated statistics on the slower box?
> When you defragmented the disc was SQL server running or not?
> Have you checked sp_configure and sp_dboption values?
> How did you time/execute the queries?
> John
> "Brian Tabios" wrote:
> > 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
> >
> > 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
> >
> >
> > *** Sent via Developersdex http://www.developersdex.com ***
> >