New Server slow - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

New Server slow

Author

Topic

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-01-23 : 10:53:50

Our new development sandbox is running very slowly.
Prod Box:
Microsoft SQL Server 2005 - 9.00.1399.06 (X64)
Oct 14 2005 00:35:21
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

Dev Box:
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
Feb 9 2007 22:47:07
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790:
Service Pack 2)

Prod Box:
select count(*) from coverage = 511,000 records, less than 1 sec

Dev Box:
select count(*) from coverage = 510,000 records, 40 secs

I've had the statistics updated , but not the indexes. But could that account for a 40 second difference?

Thanks,

Jim

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-23 : 11:00:01

Any difference in Execution plan. Also your Prod box is in RTM(Release to Market). You should apply SP3 and hotfixes after that to make it upto date.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-01-23 : 11:06:16

Thanks for the quick response sodeep and for noticing we're out-of-date! I've already asked the sa to check the execution plans and will let you know what the results are as sonn as I get them.

Jim

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-02-16 : 12:05:49

Finally an answer!
Query = select count(*) from coverage
On old server = less than 1 second
On new server = 44 seconds

Old server execution plan
<--



Select <-- Compute Scalar <-- Stream Aggregate (cost:1%) <-- Clustered index scan (cost : 99%)

New server execution plan
<--



Select <-- Compute Scalar <-- Stream Aggregate <-- Parallelism <-- Stream Aggregate <-- Clustered index scan (cost: 100%)

The new databse is in Full Recovery Mode and the old in Simple

Thanks,

Jim

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-16 : 12:23:08

rebuild index and try.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-02-16 : 13:03:10

I also updated the statistics and changed the log file growth from 10% to 1 mb, no change in execution plan but the query ran in 28 secs.
This may be irrelevant, but the 2 databases also differ here:

sp_configure

old
name minimum maximum config_value run_value
max text repl size (B) 0 2147483647 65536 65536

New
name minimum maximum config_value run_value
max text repl size (B) 0 2147483647 2147483647 2147483647

We have some Text datatypes, all unnecessary. I am working with the vendor (who wrote the shred schema) to get these changed.

Thanks,

Jim

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-02-17 : 07:00:30

nudge

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-02-17 : 13:35:37

nudge nudge?

mfemenel
Professor Frink

1421 Posts

Posted - 2009-02-17 : 14:00:26

I'm not sure you'd ever see your dev box perform the same, you have a 64 bit prod box and a 32 bit dev box. That's like a yugo racing a corvette. I would guess your dev box has a different layout for disks and and filegroups than you do in prod as well.

Mike
"oh, that monkey is going to pay"

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-02-17 : 14:15:45

Thanks Mike,

I'll check on the disk layouts and file groups. Other database on the same server seems to be running fine, though. It is not part of any replication.

Jim

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-17 : 14:18:04

I don't see that you've rebuilt the indexes yet. When you updated statistics, did you use full scan? How does fragmentation look? Does the performance dashboard report indicate any issues?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-02-17 : 14:32:15

Tara,

I used your ISP_Alter_index proc to rebuild the indexes. I did update the stats with full scan. That's what brought the query down from 50 secs to 30 secs. The difference in the execution plans can be seen in my post yesterday. The only difference is that the new server execution plan has an extra step of parallelism added. Right now I am the only user.

Jim

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-17 : 14:35:42

What are the hardware specs of both systems?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-02-17 : 14:39:20

I'm not even sure how to go about doing that, other than asking the admin.

I'll check with him and then get back. Thanks for all your help.

Jim

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-17 : 14:45:08

Can you run and see whether it makes any effect?

Select count(*) from coverage
OPTION (MAXDOP 1)

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-02-17 : 14:49:28

quote:

Can you run and see whether it makes any effect?

Select count(*) from coverage
OPTION (MAXDOP 1)

No difference, still at 30 secs.

Jim

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-02-18 : 12:12:58

Here's what I got back from my sysadmin, I may need to ask a more specific question?

"The hardware is better than 71 & 245, 69 is a monster server - quad processor. We are not running raid on any of these servers except 69."

Jim

tripodal
Constraint Violating Yak Guru

259 Posts

Posted - 2009-02-18 : 12:34:21

"We are not running raid on any of these servers except 69."

69 is your production server?
If disk access is important, a raid will create the difference you are seeing.
Also, does the entire database / table fit in memory? That will account for a big difference.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-18 : 12:35:12

How much RAM is on each server, how many CPUs, how many cores, are they hyper-threaded, how fast are the CPUs, what about IO?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

mfemenel
Professor Frink

1421 Posts

Posted - 2009-02-18 : 13:17:39

I'm starting to get that feeling like when I drive by a car accident, I'm morbidly curious and can't look away. The sysadmin's response bugs me on several levels.

Mike
"oh, that monkey is going to pay"

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-02-18 : 14:31:30

quote:
Originally posted by mfemenel

I'm starting to get that feeling like when I drive by a car accident, I'm morbidly curious and can't look away. The sysadmin's response bugs me on several levels.

Mike
"oh, that monkey is going to pay"

It ain't that bad yet. The sa is competent, but has no experience with SQL Server -- just mainframes and some db2. This whole project was done backwards and now he and I are left to pick up the pieces. I call this "Project Lightbulb" because you can at least unscrew a lightbulb!

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources