rebuild index worsens query performance

Last Post 06 Dec 2012 11:24 PM by peter. 12 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Joe Genovese
New Member
New Member

--
26 Nov 2012 11:59 AM
We have a query which runs several times a day

Query reads from several tables in one database ("W"), and two tables in another database ("P") (both db's reside on the same server)

The two tables in database "P" never had their indexes rebuilt or statistics updated, and were significantly fragmented

I purged some old information from another table in database "P" (about 50% worth, not one of the two tables refrenced in the query), and shrank database "P" (to reclaim disk space which we were running out of)

I rebuilt the indexes (but NOT statistics) in "P" immediately afterward

From that point forward the aforementioned query takes twice as long to run

We need to know what happened (vs what changes to make to the query)

Thoughts?

FYI:

Starting two weeks later, on a weekly basis, I run a maintenance plan to rebuild all indexes on "P" (rebuild index task [original amount of free space]) and update statistics task ([column stats only, initially at 10%, then 50% weekly]) -- update statistics had no effect either good or bad

The two tables in the query, and the table I purged, are subscribed tables in replication, the publisher being on another server in the same domain

I dropped replication on the purged table, purged it on both the publisher and subscriber, and re-created "replication support only" option

The two tables in the query had replication active the whole time (purge of the third table, shrink, index rebuild, etc.) -- no information was flowing at that time

Database "P" has not expanded since the shrink, and a report on frgamentation for the associated disk drives (as of 11/20) shows at worst 1% fragmentation

A comparison (via RedGate Compare) between the database pre-purge/shrink/index rebuild and present show no structural differences between the two tables used by the query

Is it possible that the plan the query was using prior to the index rebuild was running faster despite the fragmentation? I would think that any reduction in index fragmentation would be cause for improvement in query performance, but the general consensus is that query optimizer has its imperfections and perhaps that is what we are running up against
Joe Genovese
New Member
New Member

--
26 Nov 2012 12:00 PM
On a related note, I am trying to pass as a query hint the XML from what I believe to be the plan that the query was using before purge

I can see the plan just fine as a sqplan, but when I try to retrive the XML, the "StatementText" gets truncated. If I try to paste into the hint the missing part of the statement from the original query, I get this at run time" "XML parsing: line 104, character 17, well formed check: no '<' in attribute value"

I suspect there is some limit to the length of "StatementText", on both the retrieval and the use in a query option -- is there a way to circumvent that limit? Or, can the complete XML live apart from the query option (for ex. a text file) and still be referenced by the query option?
gunneyk
New Member
New Member

--
26 Nov 2012 12:26 PM
Joe, just because a table has fragmentation does not mean queries against it will be slower. If you are doing large range scans or full index or table scans then fragmentation can affect read-ahead a great deal. However if you are doing individual seeks it is less likely to affect the overall performance. So a couple of comments or suggestions.
1. Never blindly rebuild all indexes. There really isn't a need to do so. If you want you can check the frag levels and rebuild ones above a certain % of fragmentation but what that % is can vary depending on several factors. I would start with 20 or 30% or ? to rebuild and see how that works.
2. If you rebuild an index (vs. defrag) there is no need to update the stats since the rebuild updates the stats 100%. By redoignthe stats at a lower % you may be making them less accurate.
3. Chances are you just had a different plan that although the optimizer may think the new one is better in real life it may not be. That has nothing to do with fragmentation. You need to look at the plan and see what it is doing and see how to make it better. That may be a hint or it may mean changing or adding indexes.
4. If you run the query in SSMS and choose to show the actual graphical showplan you can right click on it and save it. It has a .sqplan extension but it is XML.
Joe Genovese
New Member
New Member

--
26 Nov 2012 02:21 PM
I have the actual plan, do you want to see it?
gunneyk
New Member
New Member

--
26 Nov 2012 02:27 PM
I am not sure if there is anythhing I can do with it but sure you can try to post it.
Joe Genovese
New Member
New Member

--
28 Nov 2012 01:01 PM
is there a way to attach the .sqlplan file? otherwise I would have to post the XML
gunneyk
New Member
New Member

--
29 Nov 2012 09:30 AM
I am not sure if there is a way to add an attachment and the raw xml would probably be too large. Why don't you post the actual query along with how you call it.
Joe Genovese
New Member
New Member

--
30 Nov 2012 07:33 AM
query below

note:

Please bear in mind that the emphasis is on what happened Oct 13 such that this query's run time doubled

this is called from a stored proc, I extracted the query from that proc -- this query is where the sudden degradation of performance is most pronounced, but is not necessarily limited to this query

the "portfolio" database is the database that had a table purged (not in this query), was shrunk, and had all indexes rebuilt (but not stats, that came two weeks later and made no difference either way)

if you look at the est plan and the actual plan, they are the same but the counts between the est and actual are WAY off

when I run the query with trace flag 2301, I get a totally different plan and a missing index recommendation -- I would like to add the index and force that plan into the query, but I keep running into some upper limit on the length of the XML I can force (see previous post) -- is there a way I can get around that?

--------------------------------------------------------------------------------

declare
@LoginIdNmb int
, @AcctIdNmbList varchar(8000)
, @SecurityIdNmbList varchar(8000)
-- , @RefreshIdNmb int
-- , @LastRunDT datetime
, @FirmIdNmbList varchar(8000)

set @AcctIdNmbList = null
set @SecurityIdNmbList = null

declare @acct table (acctidnmb int not null primary key)

if @FirmIdNmbList is not null
insert into @acct
select AccountIdNmb
from repl_hierarchy_acct with (nolock)
where 1=1
and firmidnmb in (select idnmb from ParseIdListToTable(@FirmIdNmbList, ','))
else
if @AcctIdNmbList is not null
insert into @acct
select IdNmb
from acct_ety with (nolock)
where 1=1
and IdNmb in (select idnmb from ParseIdListToTable(@acctIdNmbList, ','))

select TrxnIdNmb = z.IdNmb
, AccountIdNmb = c.IdNmb
, SecurityIdNmb = d.IdNmb
, TechFiAssetIdNmb = a.AssetId
, TechFiDestinationAssetIdNmb = a.DestinationAssetID
, TCodeIdNmb = case
when e.DeleteDT is null then e.IdNmb
else e.ReMapTCodeIdNmb
end
, TradeDate = isnull(a.TradeDate, isnull(a.SettleDate, getdate()))
, SettleDate = isnull(a.SettleDate, isnull(a.TradeDate, getdate()))
, Dollars = isnull(abs(a.Dollars), isnull(d.PricePct, 1) * isnull(abs(a.Price), 0) * isnull(abs(a.Shares), 0))
, Price = isnull(abs(a.Price), 0)
, Shares = round(isnull(abs(a.Shares), 0), d.SharePrecNmb)
, Confirmed = a.Confirmed
, Reversed = a.Reversed
, Comment = a.Comment
, Lot = a.Lot
, OriginalCostDate = a.OriginalCostDate
, OriginalCost = a.OriginalCost
, CorporateActionID = a.CorporateActionId
, TechFiIdNmb = a.TransId
, UpdateLoginIdNmb = @LoginIdNmb
, UpdateDt = getdate()
-- Added for fee trxns
, OriginalFeeTrxnIdNmb = f.IdNmb
, ReorgIdNmb = case when IsNumeric(a.expand3) = 0 then NULL else a.expand3 end --AH
, transuser3 = a.transuser3 -- J GENOVESE 2011-02-22
into #UpdateTrxn
from trxn_log z with (nolock) -- J GENOVESE 2009-03-24 -- ADDED WITH NOLOCK
inner join portfolio.dbo.trans a WITH (NOLOCK) on a.TransId = z.TechFiIdNmb -- J GENOVESE 2009-04-01 -- ADDED NOLOCK
inner join portfolio.dbo.asset b WITH (NOLOCK) on b.assetid = a.assetid -- J GENOVESE 2009-04-01 -- ADDED NOLOCK
inner join acct_ety c with (nolock) on c.TechFiIdNmb = b.AccountId -- J GENOVESE 2009-03-24 -- ADDED WITH NOLOCK
and c.CopyAcctIdNmb is null
and c.DeleteDT is null
inner join security_ety d with (nolock) on d.TechFiIdNmb = b.SecurityId -- J GENOVESE 2009-03-24 -- ADDED WITH NOLOCK
and d.DeleteDT is null
inner join tcode_lkup e with (nolock) on e.TechFiIdNmb = a.TCodeId -- J GENOVESE 2009-03-24 -- ADDED WITH NOLOCK

-- Added for fee trxns
left outer join (
select IdNmb
, TechFiIdNmb
from trxn_log WITH (NOLOCK) -- J GENOVESE 2009-04-01 -- ADDED NOLOCK
where DeleteDT is null
and TechFiIdNmb is not null
) f on f.TechFiIdNmb = a.TransUser4
where z.DeleteDT is null
and (
-- J GENOVESE 2011-12-08
/*
@AcctIdNmbList is null
or exists (
select *
from dbo.ParseIdListToTable(@AcctIdNmbList, ',')
where IdNmb = z.AccountIdNmb
)
)
*/
((@AcctIdNmbList is null) and (@FirmIdNmbList is null))
or exists (
select top 1 1
from @acct acct
where acct.acctidnmb = z.AccountIdNmb
)
)
-- END J GENOVESE 2011-12-08
and (
@SecurityIdNmbList is null
or exists (
select IdNmb
from dbo.ParseIdListToTable(@SecurityIdNmbList, ',')
where IdNmb = z.SecurityIdNmb
)
)
and (
e.DeleteDT is null
or (
e.DeleteDT is not null
and e.ReMapTCodeIdNmb is not null
)
)

and (
z.AccountIdNmb <> c.IdNmb
or z.securityIdNmb <> d.IdNmb
or isnull(z.TechFiAssetIdNmb, 1) <> isnull(a.AssetId, 1)
or isnull(z.TechFiDestinationAssetIdNmb, 1) <> isnull(a.DestinationAssetID, 1)
or z.TCodeIdNmb <> case
when e.DeleteDT is null then e.IdNmb
else e.ReMapTCodeIdNmb
end
or z.TradeDate is null
or z.TradeDate <> isnull(a.TradeDate, isnull(a.SettleDate, getdate()))
or z.SettleDate is null
or z.SettleDate <> isnull(a.SettleDate, isnull(a.TradeDate, getdate()))
or z.Dollars is null
or z.Dollars <> isnull(abs(a.Dollars), isnull(d.PricePct, 1) * isnull(abs(a.Price), 0) * isnull(abs(a.Shares), 0))
or z.Price is null
or z.Price <> isnull(abs(a.Price), 0)
or z.Shares is null
-- J GENOVESE 2010-12-01
-- or z.Shares <> isnull(abs(a.Shares), 0)
or z.Shares <> round(isnull(abs(a.Shares), 0), d.SharePrecNmb)
-- END J GENOVESE 2010-12-01
or z.Confirmed <> a.Confirmed
or z.Reversed <> a.Reversed
or isnull(z.Comment, '') <> isnull(a.Comment, '')
or isnull(z.Lot, 1) <> isnull(a.Lot, 1)
or isnull(z.OriginalCostDate, '12/31/1999') <> isnull(a.OriginalCostDate, '12/31/1999')
or isnull(z.OriginalCost, 0) <> isnull(a.OriginalCost, 0)
or isnull(z.CorporateActionID, 1) <> isnull(a.CorporateActionId, 1)
-- Added for fee trxns
or isnull(z.OriginalFeeTrxnIdNmb, 1) <> isnull(f.IdNmb, 1)
-- J GENOVESE 2010-12-01
-- or isnull(z.ReorgIdNmb, 1) <> isnull(a.Expand3, 1) --AH
or isnull(z.ReorgIdNmb, 1) <> isnull(case when IsNumeric(a.expand3) = 0 then NULL else a.expand3 end, 1) --AH
-- END J GENOVESE 2010-12-01
or isnull(z.transuser3, '') <> isnull(a.transuser3, '') -- J GENOVESE 2011-02-22
)

drop table #UpdateTrxn
peter
New Member
New Member

--
03 Dec 2012 10:40 PM
index defragmentation shouldn't cause a performance degradation, but it won't necessarily speed things up either. It depends on how the index is being used.

Single-row seeks, for example, aren't significantly impacted by fragmentation.

Before I can suggest anything about the issue, I'd need to see the execution plan at the very least. Table definitions and the query are usually also needed, but the execution plan is the bare minimum.

I suppose that it's possible that an index rebuild could cause a slow down of this nature because of the stats rebuild that also occurs. That may have caused the execution plan to change. I've also see that adding an index (found this out in the code for one of my articles) can cause code to run much slower. The optimizer isn't magic... it was written by humans and it sometimes makes bad choices
Joe Genovese
New Member
New Member

--
04 Dec 2012 11:12 AM
was not able to post the plan's XML

is there a way I can send you the .sqlplan file?
peter
New Member
New Member

--
05 Dec 2012 01:25 AM
You should follow the same question which was asked earlier and I think
you were there in discussion

So why are you asking the same question here also

http://www.sqlservercentral.com/For...391-4.aspx
Joe Genovese
New Member
New Member

--
05 Dec 2012 08:37 AM
why am I asking the same question here, you ask?
#1 -- this is an urgent matter, and I was hoping for a wider audience on the issue
#2 -- much of the "answers" I have received on the other blog have not proven helpful
#3 -- I feel I am being ignored on the other blog

Are you telling me that every single blog on which I can post such an inquiry features the same exact crew of experts?
peter
New Member
New Member

--
06 Dec 2012 11:24 PM
Thats pretty much fine if you hoping a wider audience and also experts

i was just assuming that you must have found the solution as so many experts were participating there

Anyway I hope here we all together will find the solution of you query
You are not authorized to post a reply.

Acceptable Use Policy