production server slower than development server

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Performance Tuning » production server slower than development...

production server slower than development server

Rate Topic

Display Mode

Topic Options

Author

Message

aravind-305595

Posted Tuesday, February 24, 2009 11:54 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 24, 2012 10:07 AM
Points: 37, Visits: 114

Hi,

I have a select query from a table which has a column with huge data. The select query takes 14 secs in the development server but the same query takes 70 secs in the production server.

To simulate the issue, I created the following script and tested in both the environments

create table Testperf
(col1 varchar(5000) )

declare @i int
select @i = 1
while @i <= 1000
begin
insert into Testperf
select replicate('Bravo',600)
set @i = @i + 1
end

select * from Testperf

Normally any company will have production server highly configured than the development server. So in that case how this could happen?
"Production server slower than development server".

Post #664033

GilaMonster

Posted Wednesday, February 25, 2009 12:19 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:59 PM
Points: 37,640, Visits: 29,895

aravind (2/24/2009)

Normally any company will have production server highly configured than the development server. So in that case how this could happen?
"Production server slower than development server".

More users and hence more load on production. More data on production. Older statistics. More fragmented indexes. Different exec plan.
Lots of reasons

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #664043

aravind-305595

Posted Wednesday, February 25, 2009 1:49 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 24, 2012 10:07 AM
Points: 37, Visits: 114

The same case is with the QA environment also ? which wont have difference in load when compared with development.

Post #664070

aravind-305595

Posted Wednesday, February 25, 2009 2:01 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 24, 2012 10:07 AM
Points: 37, Visits: 114

Independent on the environment, can we make this query run faster by anyway ??
Our ultimate aim is to run the query faster. Is the performance lack due to the huge data size in that column ?? Can we overcome that ?

Post #664073

ALZDBA

Posted Wednesday, February 25, 2009 2:53 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:25 AM
Points: 6,861, Visits: 8,045

Get things organized !

create table Testperf (col1 varchar(5000) )

cannot be a reference because of ... table design.
No indexes, a table with a single column, ...

If you want to stress test io try SQLIOSim tool (download from MS)

After you migrated the db to prod, did you perform db maintenance ?
- rebuild indexes
- refresh statistics

What performance issues are you getting ?
- timeouts
- deadlocks
- ...

Johan

     Jul 13

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help

- How to prevent a sore throat after hours of presenting ppt ?

"press F1 for solution", "press shift+F1 for urgent solution" BigGrin

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #664087

aravind-305595

Posted Wednesday, February 25, 2009 5:28 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 24, 2012 10:07 AM
Points: 37, Visits: 114

The actual table has an id, name and its description. id is the primary key.
The description is huge which has length ranging from 3000 to 5000 characters.
I use simply query to get all the content from the table which has description not null.
The query takes in 3-4 mins to get that resultset. Because of which the user has to wait for that time in the front end. The requriement is to reduce the wait time.

Post #664140

ALZDBA

Posted Wednesday, February 25, 2009 5:47 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:25 AM
Points: 6,861, Visits: 8,045

- Is there a clustering index on that table ?

if not, provide one, unless you can prove it hurts performance.
Data pages may be scattered all over the place.

if you have a clustering index, rebuild it. and check again after.

Johan

     Jul 13

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #664150

aravind-305595

Posted Wednesday, February 25, 2009 6:38 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 24, 2012 10:07 AM
Points: 37, Visits: 114

primary key is there. so it has created clustered index. Rebuild doesnt give any improvement.

Post #664193

ALZDBA

Posted Wednesday, February 25, 2009 6:45 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:25 AM
Points: 6,861, Visits: 8,045

What's the number of rows that match the criteria ?

Is that number the same for both instances ?

How about concurrency ?

What software level are you on (@@version) ?

Johan

     Jul 13

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #664197

aravind-305595

Posted Wednesday, February 25, 2009 7:20 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 24, 2012 10:07 AM
Points: 37, Visits: 114

both instances match the records

Version : sql server 2000 sp 2

Post #664222

« Prev Topic | Next Topic »

Permissions