Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

production server slower than development server Expand / Collapse
Author
Message
Posted Tuesday, February 24, 2009 11:54 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 5:26 AM
Points: 37, Visits: 116
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
Posted Wednesday, February 25, 2009 12:19 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:19 PM
Points: 42,332, Visits: 35,386
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
Posted Wednesday, February 25, 2009 1:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 5:26 AM
Points: 37, Visits: 116
The same case is with the QA environment also ? which wont have difference in load when compared with development.
Post #664070
Posted Wednesday, February 25, 2009 2:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 5:26 AM
Points: 37, Visits: 116
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
Posted Wednesday, February 25, 2009 2:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:34 AM
Points: 6,999, Visits: 8,436
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


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

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


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #664087
Posted Wednesday, February 25, 2009 5:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 5:26 AM
Points: 37, Visits: 116
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
Posted Wednesday, February 25, 2009 5:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:34 AM
Points: 6,999, Visits: 8,436
- 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


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

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


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #664150
Posted Wednesday, February 25, 2009 6:38 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 5:26 AM
Points: 37, Visits: 116
primary key is there. so it has created clustered index. Rebuild doesnt give any improvement.
Post #664193
Posted Wednesday, February 25, 2009 6:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:34 AM
Points: 6,999, Visits: 8,436
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


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

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


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #664197
Posted Wednesday, February 25, 2009 7:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 5:26 AM
Points: 37, Visits: 116
both instances match the records

Version : sql server 2000 sp 2
Post #664222
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse