Query performace problem

We have a query, which accepts a number of parameters.
When the parameter @.activeAffiliateCode is set, the following statement
executes, and the query runs very slowly on our LIVE server.
update t
set t.activeAffiliateID = @.activeAffiliateCode,
t.Weight = t.Weight + power(2,6)
from @.results t, BMG_VFE_WORLD.dbo.tblSongTerritory st with (nolock)
where t.SongID = st.SongID
and st.SysTerritoryCode = @.activeAffiliateCode
In the Development environment, the query runs in just a couple of
seconds when this parameter is set.
The two queryplans are mostly identical, except in the DEV version,
when it does it's index s on tblSongTerritory the rowcount is 313,
where as in the LIVE version it does the index s against
tblSongTerritory and the rowcount is 717,248,519!
Although both queryplans are using the same index on tblSongTerritory
(which is an Index on columns SysTerritoryCode and SongID), the LIVE
version doesn't seem to be able to use the SongID part of the index
1) DEV (runs in a couple of seconds)
OBJECT:([bmg_vfe_world].[dbo].[tblSongTerritory].[IX_tblSongTerritory_STCSongID]
as [st], SEEK:([st]. [SysTerritoryCode]=[@.activeAffiliateCode
] AND
[st].[SongID]=[t].[SongID]) ORDERED FORWARD
2) LIVE (takes about 5 minutes)
OBJECT:([bmg_vfe_world].[dbo].[tblSongTerritory].[IX_tblSongTerritory_STCSongID]
as [st], SEEK:([st]. [SysTerritoryCode]=[@.activeAffiliateCode
] ORDERED
FORWARD
So, you would have thought a statistics or index problem then?
Well, the statistics get updated nightly, and the indexes are rebuilt
nightly, and the query plan still stays the same on live.
Any suggestions from the gurus out there?Hi
Compare both execution plans to see what is going on. Run DBCC UPDATEUSAGE
on LIVE server as well as check out your SQL Server's settings on DEV and
LIVE enviroments
<jamiesurman@.gmail.com> wrote in message
news:1150358041.460713.27250@.i40g2000cwc.googlegroups.com...
> We have a query, which accepts a number of parameters.
> When the parameter @.activeAffiliateCode is set, the following statement
> executes, and the query runs very slowly on our LIVE server.
>
> update t
> set t.activeAffiliateID = @.activeAffiliateCode,
> t.Weight = t.Weight + power(2,6)
> from @.results t, BMG_VFE_WORLD.dbo.tblSongTerritory st with (nolock)
> where t.SongID = st.SongID
> and st.SysTerritoryCode = @.activeAffiliateCode
>
> In the Development environment, the query runs in just a couple of
> seconds when this parameter is set.
> The two queryplans are mostly identical, except in the DEV version,
> when it does it's index s on tblSongTerritory the rowcount is 313,
> where as in the LIVE version it does the index s against
> tblSongTerritory and the rowcount is 717,248,519!
> Although both queryplans are using the same index on tblSongTerritory
> (which is an Index on columns SysTerritoryCode and SongID), the LIVE
> version doesn't seem to be able to use the SongID part of the index
> 1) DEV (runs in a couple of seconds)
> OBJECT:([bmg_vfe_world].[dbo].[tblSongTerritory].[IX_tblSongTerritory_STCSongID]
> as [st], SEEK:([st]. [SysTerritoryCode]=[@.activeAffiliateCode
] AND
> [st].[SongID]=[t].[SongID]) ORDERED FORWARD
>
> 2) LIVE (takes about 5 minutes)
> OBJECT:([bmg_vfe_world].[dbo].[tblSongTerritory].[IX_tblSongTerritory_STCSongID]
> as [st], SEEK:([st]. [SysTerritoryCode]=[@.activeAffiliateCode
] ORDERED
> FORWARD
>
> So, you would have thought a statistics or index problem then?
> Well, the statistics get updated nightly, and the indexes are rebuilt
> nightly, and the query plan still stays the same on live.
> Any suggestions from the gurus out there?
>|||Uri Dimant wrote:
> Hi
> Compare both execution plans to see what is going on. Run DBCC UPDATEUSAGE
> on LIVE server as well as check out your SQL Server's settings on DEV and
> LIVE enviroments
Hi Uri, it runs a DBCC DBREINDEX against all the tables in the database
every night. Surely this should mean the index is fine? (I can't really
run anything against LIVE at the moment, because there are millions of
rows, and I don't want to affect the users)|||jamiesurman@.gmail.com wrote:

> Uri Dimant wrote:
>
> Hi Uri, it runs a DBCC DBREINDEX against all the tables in the database
> every night. Surely this should mean the index is fine? (I can't really
> run anything against LIVE at the moment, because there are millions of
> rows, and I don't want to affect the users)
Are you using SQL Server 2005.
You can force query plan using USE PLAN hint.
Look in BOL for more details
Regards
Amish Shah|||jamiesurman@.gmail.com wrote:

> Uri Dimant wrote:
>
> Hi Uri, it runs a DBCC DBREINDEX against all the tables in the database
> every night. Surely this should mean the index is fine? (I can't really
> run anything against LIVE at the moment, because there are millions of
> rows, and I don't want to affect the users)
Are you using SQL Server 2005.
You can force query plan using USE PLAN hint.
Look in BOL for more details
Regards
Amish Shah|||
> Are you using SQL Server 2005.
> You can force query plan using USE PLAN hint.
> Look in BOL for more details
> Regards
> Amish Shah
Hi Amish, nope it's SQL 2000. I'm not sure if forcing the plan would
help though, because both plans use an Index s against the same
index...however one of them seems to try to only match on
SysTerritoryCode (there are only 30-odd out of millions of records),
and the other one matches on SysTerritoryCode AND SongID (which has a
much better selectivity)|||How much data is on the test side? Is it really equivalent to the
live side?
I would start with basic checks. Are the table definitions on both
sides EXACTLY the same? Are the index definitions? The @.variables?
Something as simple as slight data type difference between
ysTerritoryCode and @.activeAffiliateCode can cause the optimizer to
skip the index s you need.
Roy Harvey
Beacon Falls, CT
On 15 Jun 2006 00:54:01 -0700, jamiesurman@.gmail.com wrote:

>We have a query, which accepts a number of parameters.
>When the parameter @.activeAffiliateCode is set, the following statement
>executes, and the query runs very slowly on our LIVE server.
>
>update t
>set t.activeAffiliateID = @.activeAffiliateCode,
> t.Weight = t.Weight + power(2,6)
>from @.results t, BMG_VFE_WORLD.dbo.tblSongTerritory st with (nolock)
>where t.SongID = st.SongID
>and st.SysTerritoryCode = @.activeAffiliateCode
>
>In the Development environment, the query runs in just a couple of
>seconds when this parameter is set.
>The two queryplans are mostly identical, except in the DEV version,
>when it does it's index s on tblSongTerritory the rowcount is 313,
>where as in the LIVE version it does the index s against
>tblSongTerritory and the rowcount is 717,248,519!
>Although both queryplans are using the same index on tblSongTerritory
>(which is an Index on columns SysTerritoryCode and SongID), the LIVE
>version doesn't seem to be able to use the SongID part of the index
>1) DEV (runs in a couple of seconds)
>OBJECT:([bmg_vfe_world].[dbo].[tblSongTerritory].[IX_tblSongTerritory_STCSongID]
>as [st], SEEK:([st]. [SysTerritoryCode]=[@.activeAffiliateCode
] AND
>[st].[SongID]=[t].[SongID]) ORDERED FORWARD
>
>2) LIVE (takes about 5 minutes)
>OBJECT:([bmg_vfe_world].[dbo].[tblSongTerritory].[IX_tblSongTerritory_STCSongID]
>as [st], SEEK:([st]. [SysTerritoryCode]=[@.activeAffiliateCode
] ORDERED
>FORWARD
>
>So, you would have thought a statistics or index problem then?
>Well, the statistics get updated nightly, and the indexes are rebuilt
>nightly, and the query plan still stays the same on live.
>Any suggestions from the gurus out there?|||Roy Harvey wrote:
> How much data is on the test side? Is it really equivalent to the
> live side?
Yes, it's a copy of the live DB from 2 ws ago,

> I would start with basic checks. Are the table definitions on both
> sides EXACTLY the same? Are the index definitions? The @.variables?
> Something as simple as slight data type difference between
> ysTerritoryCode and @.activeAffiliateCode can cause the optimizer to
> skip the index s you need.
I'll investigate further along these lines...the index is definitely
exactly the same in both environments though.|||Are your Development and Production SQL Servers both at the same SQL Server
Service Pack level?
Chris
"jamiesurman@.gmail.com" wrote:

> Roy Harvey wrote:
> Yes, it's a copy of the live DB from 2 ws ago,
>
> I'll investigate further along these lines...the index is definitely
> exactly the same in both environments though.
>|||Chris Howarth wrote:
> Are your Development and Production SQL Servers both at the same SQL Serve
r
> Service Pack level?
Both servers are SP4 Chris