Performance DEV vs PROD - Same Equipment??

DEV and PROD same exact box, PROD has 4GB and DEV 1GB. A particular
query takes over an hour in PROD and about 20 minutes in DEV? As far as
I can tell their SQLServer settings are the same, I do have limited access.
Read something about a /3GB switch, we are using SQLServer 2000 and the
semi-dba indicated that we did not have that setup but didn't think it
needed to be. The semi-dba type is mainly Sybase and can't seem to find
any spare cycles to look at my problem. I am a developer and of course
past writing some SQL am far from expert on the DB stuff.
I have googled and found various information about databases and
indexing, which BTW, did find that our FillFactor was set incorrectly
as this system is reporting, updated once a month from a feed from the
main system. So reindexed everything with a fillFactor of 100%. It was
previously set at 75%? Didn't see any improvement in DEV so don't expect
anyhing great in PROD.
Anything is helpful at this point. I will be running to HalfPrice
books tomorrow to see what I can find that can help me.
JimDOn Sat, 14 Apr 2007 23:29:12 GMT, Jim Douglas
<james.douglas@.genesis-software.com> wrote:

>DEV and PROD same exact box, PROD has 4GB and DEV 1GB. A particular
>query takes over an hour in PROD and about 20 minutes in DEV? As far as
>I can tell their SQLServer settings are the same, I do have limited access.
Not to overlook the obvious, are you certain the *data* is the same in
Dev?
Try running profiler on both to see what resources are getting used.
J.|||Hum., 20 minutes in DEV: with this kind of result, you shouldn't be
surprised to see the PROD to take much longer if its size is four times as
big.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Jim Douglas" <james.douglas@.genesis-software.com> wrote in message
news:ctdUh.1139$xP.1027@.trnddc04...
> DEV and PROD same exact box, PROD has 4GB and DEV 1GB. A particular query
> takes over an hour in PROD and about 20 minutes in DEV? As far as I can
> tell their SQLServer settings are the same, I do have limited access.
> Read something about a /3GB switch, we are using SQLServer 2000 and the
> semi-dba indicated that we did not have that setup but didn't think it
> needed to be. The semi-dba type is mainly Sybase and can't seem to find
> any spare cycles to look at my problem. I am a developer and of course
> past writing some SQL am far from expert on the DB stuff.
> I have googled and found various information about databases and indexing,
> which BTW, did find that our FillFactor was set incorrectly
> as this system is reporting, updated once a month from a feed from the
> main system. So reindexed everything with a fillFactor of 100%. It was
> previously set at 75%? Didn't see any improvement in DEV so don't expect
> anyhing great in PROD.
> Anything is helpful at this point. I will be running to HalfPrice
> books tomorrow to see what I can find that can help me.
> JimD
>|||"Jim Douglas" <james.douglas@.genesis-software.com> wrote in message
news:ctdUh.1139$xP.1027@.trnddc04...
> DEV and PROD same exact box, PROD has 4GB and DEV 1GB. A particular query
> takes over an hour in PROD and about 20 minutes in DEV? As far as I can
> tell their SQLServer settings are the same, I do have limited access.
What is the query?
And what is the schema for the tables involved?
A 20 minute query is a pretty long query in most cases (but not all.)

> Read something about a /3GB switch, we are using SQLServer 2000 and the
> semi-dba indicated that we did not have that setup but didn't think it
> needed to be. The semi-dba type is mainly Sybase and can't seem to find
> any spare cycles to look at my problem. I am a developer and of course
> past writing some SQL am far from expert on the DB stuff.
> I have googled and found various information about databases and indexing,
> which BTW, did find that our FillFactor was set incorrectly
> as this system is reporting, updated once a month from a feed from the
> main system. So reindexed everything with a fillFactor of 100%. It was
> previously set at 75%? Didn't see any improvement in DEV so don't expect
> anyhing great in PROD.
What made you decide to change this? Is this a datawarehouse or an OLTP.
In some cases 100% fill factor can make overall performance far worse.
(generally if yo're only selecting, it's great, you'll pack as much data
into the nodes of the b-tree as possible, but inserts will cost a lot more
overheard.)

> Anything is helpful at this point. I will be running to HalfPrice
> books tomorrow to see what I can find that can help me.
You running SQL 2000 or 2005?
> JimD
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Hi Jim
"Jim Douglas" wrote:

> DEV and PROD same exact box, PROD has 4GB and DEV 1GB. A particular
> query takes over an hour in PROD and about 20 minutes in DEV? As far as
> I can tell their SQLServer settings are the same, I do have limited access
.
> Read something about a /3GB switch, we are using SQLServer 2000 and the
> semi-dba indicated that we did not have that setup but didn't think it
> needed to be. The semi-dba type is mainly Sybase and can't seem to find
> any spare cycles to look at my problem. I am a developer and of course
> past writing some SQL am far from expert on the DB stuff.
> I have googled and found various information about databases and
> indexing, which BTW, did find that our FillFactor was set incorrectly
> as this system is reporting, updated once a month from a feed from the
> main system. So reindexed everything with a fillFactor of 100%. It was
> previously set at 75%? Didn't see any improvement in DEV so don't expect
> anyhing great in PROD.
> Anything is helpful at this point. I will be running to HalfPrice
> books tomorrow to see what I can find that can help me.
> JimD
To add my 2c...
The usage of the two systems is likely to be different if these are not
processes that set the database into single user mode!! You may have blockin
g
on live which if you are the only person testing on your own database will
not happen on DEV. Rather than testing on DEV you may want to consider a
separate test environment which you can run simulated loads on. You should
also look at the query plans on the two different environments and also chec
k
index fragmentations and the validity of the statistics.
John