February 24, 2009 at 11:54 pm
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".
February 25, 2009 at 12:19 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 25, 2009 at 1:49 am
The same case is with the QA environment also ? which wont have difference in load when compared with development.
February 25, 2009 at 2:01 am
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 ?
February 25, 2009 at 2:53 am
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
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- 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
February 25, 2009 at 5:28 am
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.
February 25, 2009 at 5:47 am
- 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
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- 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
February 25, 2009 at 6:38 am
primary key is there. so it has created clustered index. Rebuild doesnt give any improvement.
February 25, 2009 at 6:45 am
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
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- 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
February 25, 2009 at 7:20 am
both instances match the records
Version : sql server 2000 sp 2
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply