a humdinger of a performance problem

Hi all,
Generally i'm helping to answer questions here, but i have strange problem
that I haven't seen before, or at least such a difference in performance of
a query.
Scenario:
I have my laptop (terrible spec) that has WinXP SP2, SQL2000 DEV EDITION +
SP4.
I have a datawarehouse that currently contains tiny amounts of data, like
3000 rows in 1 table.
All 3 tables in use are 'perfectly' indexed and I have a simple view sitting
on top of them.
If i run a select query against the DB it returns me 3000 rows in under a
second... Now.. here's the killer:
I have EXACTLY the same DB installed on the DWH server which boasts 4GB RAM,
4 3.08Ghz Processers and Win2003 and SQL 2000 STANDARD Edition.
The server is again 'perfectly' tuned with the exact same database ready for
rocking and rolling!
Now - the same query runs in 3 minutes!!!!!!!
If a perform execution plans and traces to my hearts content they ALWAYS
return the same output - but simply a difference in the time taken to return
the data.
I haven't come across a difference like this in 5 years of DBA'ing and am
grateful for any pointers.
A stupid question, would Dev version of SQL perform better indexing of views
(even though no indexed views are in play) than Standard edition which
obviously doesnt support Ind.Views!!'
Baffled! and TIA
ImmyImmy
Check out all SQL Server's settings on both machines
Add "noexpand" hint to the indexed view like
select sum(col) from IndexView with (noexpand)
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:OWPs5alfGHA.2208@.TK2MSFTNGP05.phx.gbl...
> Hi all,
> Generally i'm helping to answer questions here, but i have strange problem
> that I haven't seen before, or at least such a difference in performance
> of a query.
> Scenario:
> I have my laptop (terrible spec) that has WinXP SP2, SQL2000 DEV EDITION +
> SP4.
> I have a datawarehouse that currently contains tiny amounts of data, like
> 3000 rows in 1 table.
> All 3 tables in use are 'perfectly' indexed and I have a simple view
> sitting on top of them.
> If i run a select query against the DB it returns me 3000 rows in under a
> second... Now.. here's the killer:
> I have EXACTLY the same DB installed on the DWH server which boasts 4GB
> RAM, 4 3.08Ghz Processers and Win2003 and SQL 2000 STANDARD Edition.
> The server is again 'perfectly' tuned with the exact same database ready
> for rocking and rolling!
> Now - the same query runs in 3 minutes!!!!!!!
> If a perform execution plans and traces to my hearts content they ALWAYS
> return the same output - but simply a difference in the time taken to
> return the data.
> I haven't come across a difference like this in 5 years of DBA'ing and am
> grateful for any pointers.
> A stupid question, would Dev version of SQL perform better indexing of
> views (even though no indexed views are in play) than Standard edition
> which obviously doesnt support Ind.Views!!'
> Baffled! and TIA
> Immy
>|||Why don't you run a perfmon on the server and keep a check on the buffer
cache, I/O, processesor and memory when the query is run and see which is
being used more? that info will help in address the issue faster.
"Immy" wrote:

> Hi all,
> Generally i'm helping to answer questions here, but i have strange problem
> that I haven't seen before, or at least such a difference in performance o
f
> a query.
> Scenario:
> I have my laptop (terrible spec) that has WinXP SP2, SQL2000 DEV EDITION +
> SP4.
> I have a datawarehouse that currently contains tiny amounts of data, like
> 3000 rows in 1 table.
> All 3 tables in use are 'perfectly' indexed and I have a simple view sitti
ng
> on top of them.
> If i run a select query against the DB it returns me 3000 rows in under a
> second... Now.. here's the killer:
> I have EXACTLY the same DB installed on the DWH server which boasts 4GB RA
M,
> 4 3.08Ghz Processers and Win2003 and SQL 2000 STANDARD Edition.
> The server is again 'perfectly' tuned with the exact same database ready f
or
> rocking and rolling!
> Now - the same query runs in 3 minutes!!!!!!!
> If a perform execution plans and traces to my hearts content they ALWAYS
> return the same output - but simply a difference in the time taken to retu
rn
> the data.
> I haven't come across a difference like this in 5 years of DBA'ing and am
> grateful for any pointers.
> A stupid question, would Dev version of SQL perform better indexing of vie
ws
> (even though no indexed views are in play) than Standard edition which
> obviously doesnt support Ind.Views!!'
> Baffled! and TIA
> Immy
>
>|||Hi Uri,
I mentioned that both my machines are setup exactly the same way. I
installed them both personally.
I don't have indexed views setup as my live server uses Standard Edition of
SQL Server.
Immy
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OsqQcelfGHA.4864@.TK2MSFTNGP05.phx.gbl...
> Immy
> Check out all SQL Server's settings on both machines
> Add "noexpand" hint to the indexed view like
> select sum(col) from IndexView with (noexpand)
>
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:OWPs5alfGHA.2208@.TK2MSFTNGP05.phx.gbl...
>|||> If a perform execution plans and traces to my hearts content they ALWAYS
> return the same output - but simply a difference in the time taken to
> return the data.
I would expect different execution plans to explain such a big difference.
Take a close look at each plan node to ensure each is the same in both
plans.
Is there blocking or other processes running on the DWH server that might
explain the difference?

> A stupid question, would Dev version of SQL perform better indexing of
> views (even though no indexed views are in play) than Standard edition
> which obviously doesnt support Ind.Views!!'
Developer Edition has the same features as Enterprise Edition.
Consequently, DE will automatically consider view indexes. However, you
would see different execution plans if indexed views was the reason for the
difference in performance. DE/EE also has a merry-go-round scan feature
that can improve concurrent scan performance but that shouldn't be an issue
here.
Hope this helps.
Dan Guzman
SQL Server MVP
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:OWPs5alfGHA.2208@.TK2MSFTNGP05.phx.gbl...
> Hi all,
> Generally i'm helping to answer questions here, but i have strange problem
> that I haven't seen before, or at least such a difference in performance
> of a query.
> Scenario:
> I have my laptop (terrible spec) that has WinXP SP2, SQL2000 DEV EDITION +
> SP4.
> I have a datawarehouse that currently contains tiny amounts of data, like
> 3000 rows in 1 table.
> All 3 tables in use are 'perfectly' indexed and I have a simple view
> sitting on top of them.
> If i run a select query against the DB it returns me 3000 rows in under a
> second... Now.. here's the killer:
> I have EXACTLY the same DB installed on the DWH server which boasts 4GB
> RAM, 4 3.08Ghz Processers and Win2003 and SQL 2000 STANDARD Edition.
> The server is again 'perfectly' tuned with the exact same database ready
> for rocking and rolling!
> Now - the same query runs in 3 minutes!!!!!!!
> If a perform execution plans and traces to my hearts content they ALWAYS
> return the same output - but simply a difference in the time taken to
> return the data.
> I haven't come across a difference like this in 5 years of DBA'ing and am
> grateful for any pointers.
> A stupid question, would Dev version of SQL perform better indexing of
> views (even though no indexed views are in play) than Standard edition
> which obviously doesnt support Ind.Views!!'
> Baffled! and TIA
> Immy
>|||Immy
> A stupid question, would Dev version of SQL perform better indexing of
> views (even though no indexed views are in play) than Standard edition
> which obviously doesnt support Ind.Views!!'
Sorry I was referencing to the above statement somehow
Run DBCC FREEPROCCACHE on both machines and see what is going on
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:%23c130jlfGHA.3996@.TK2MSFTNGP04.phx.gbl...
> Hi Uri,
> I mentioned that both my machines are setup exactly the same way. I
> installed them both personally.
> I don't have indexed views setup as my live server uses Standard Edition
> of SQL Server.
> Immy
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OsqQcelfGHA.4864@.TK2MSFTNGP05.phx.gbl...
>|||thanks for the responses guys and i've either already tried most of your
suggestions or tried the rest offered but getting nowehere very fast.
I'm currently on Japanese time zone rolling out a live system so there comes
a time where i need to re-group and take a look after some sleep!
I'm beginning to think my conclusion as well as Dans comments re: auto
indexing is the key here as i've swapped db's around from server to server
and always see the issue on the Standard version of sql.
I'll have to just try alternate methods of providing the data to the users;
however, feel free to post more suggestions that i can try in a few hours
time.
thanks guys
Immy
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:OWPs5alfGHA.2208@.TK2MSFTNGP05.phx.gbl...
> Hi all,
> Generally i'm helping to answer questions here, but i have strange problem
> that I haven't seen before, or at least such a difference in performance
> of a query.
> Scenario:
> I have my laptop (terrible spec) that has WinXP SP2, SQL2000 DEV EDITION +
> SP4.
> I have a datawarehouse that currently contains tiny amounts of data, like
> 3000 rows in 1 table.
> All 3 tables in use are 'perfectly' indexed and I have a simple view
> sitting on top of them.
> If i run a select query against the DB it returns me 3000 rows in under a
> second... Now.. here's the killer:
> I have EXACTLY the same DB installed on the DWH server which boasts 4GB
> RAM, 4 3.08Ghz Processers and Win2003 and SQL 2000 STANDARD Edition.
> The server is again 'perfectly' tuned with the exact same database ready
> for rocking and rolling!
> Now - the same query runs in 3 minutes!!!!!!!
> If a perform execution plans and traces to my hearts content they ALWAYS
> return the same output - but simply a difference in the time taken to
> return the data.
> I haven't come across a difference like this in 5 years of DBA'ing and am
> grateful for any pointers.
> A stupid question, would Dev version of SQL perform better indexing of
> views (even though no indexed views are in play) than Standard edition
> which obviously doesnt support Ind.Views!!'
> Baffled! and TIA
> Immy
>|||How long has the database been set up on the production server?
Do any other queries take this long to run?
Is it possible that you have a bad processor / memory?
Or, maybe you have a corrupt index.
Lets figure out if one table is causing you the trouble. Take each table in
your query, and create a seperate query, joining the table to itself several
times, on the same columns used for the join in the original query. Run
each of these and see if one of them is slow. I would expect that one of
these would be very slow and the other two would be quite fast. Drop the
indexes on the one that is slow and recreate them.
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:%23Y8B0ylfGHA.4304@.TK2MSFTNGP05.phx.gbl...
> thanks for the responses guys and i've either already tried most of your
> suggestions or tried the rest offered but getting nowehere very fast.
> I'm currently on Japanese time zone rolling out a live system so there
comes
> a time where i need to re-group and take a look after some sleep!
> I'm beginning to think my conclusion as well as Dans comments re: auto
> indexing is the key here as i've swapped db's around from server to server
> and always see the issue on the Standard version of sql.
> I'll have to just try alternate methods of providing the data to the
users;
> however, feel free to post more suggestions that i can try in a few hours
> time.
> thanks guys
> Immy
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:OWPs5alfGHA.2208@.TK2MSFTNGP05.phx.gbl...
problem
+
like
a
am
>|||Jim
the DB is new as of yesterday as is the installation and the query was
running fine during the first few hours of yesterday.
I have also broken down the queries and joined onto 1 table it's fine.
If i add 1 of any other 2 tables to the query it slows down dramatically and
i had also recreated the indexes a few times throughout the day.
Could be right about bad processor as 1 of the processors always peaks for
the duration of the run.
Once again, thanks for your responses. Good to know we're all singing from
the same hymn sheet and i'm sure i'll get to the end of it soon.
Immy
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:%23n4XommfGHA.2068@.TK2MSFTNGP02.phx.gbl...
> How long has the database been set up on the production server?
> Do any other queries take this long to run?
> Is it possible that you have a bad processor / memory?
> Or, maybe you have a corrupt index.
> Lets figure out if one table is causing you the trouble. Take each table
> in
> your query, and create a seperate query, joining the table to itself
> several
> times, on the same columns used for the join in the original query. Run
> each of these and see if one of them is slow. I would expect that one of
> these would be very slow and the other two would be quite fast. Drop the
> indexes on the one that is slow and recreate them.
>
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:%23Y8B0ylfGHA.4304@.TK2MSFTNGP05.phx.gbl...
> comes
> users;
> problem
> +
> like
> a
> am
>|||How is the memory and IO (from perfmon) during the run? This really has my
curiosity peaked now. With only 3000 rows, I would expect very fast
response on anything better than a 386. With identical execution plans, I
am even more puzzled.
Just to confirm, all 3 tables have the same number of rows on both
databases?
Could you post the SQL query and the DDL, just to satisfy our curiosity?
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:uzTPfDnfGHA.2068@.TK2MSFTNGP02.phx.gbl...
> Jim
> the DB is new as of yesterday as is the installation and the query was
> running fine during the first few hours of yesterday.
> I have also broken down the queries and joined onto 1 table it's fine.
> If i add 1 of any other 2 tables to the query it slows down dramatically
and
> i had also recreated the indexes a few times throughout the day.
> Could be right about bad processor as 1 of the processors always peaks for
> the duration of the run.
> Once again, thanks for your responses. Good to know we're all singing from
> the same hymn sheet and i'm sure i'll get to the end of it soon.
> Immy
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:%23n4XommfGHA.2068@.TK2MSFTNGP02.phx.gbl...
table
of
the
your
hours
under
4GB
and
of
edition
>