June 17, 2009 at 9:18 am
We have a few indexed views in our reporting database and have had no problems creating them...until now. I've created a new view and am attempting to create a clustered index on it, but when I do the process chalks up a bit of cpu time and then it just goes into a perpetual (more than 4 hours) of wait on CXPackets. When I try throttling the maxdop down to 1, the index creation does begin to accumulate cpu cycles as well as reads and writes, but it still takes way too long (more than 12 hours and still not complete).
The process is not being blocked, it just enters this perpetual wait state and stays there.
I've looked at the execution plan for the view, and while expensive (the view returns just under 60 million records) it is not unreasonable. I can get results from querying the view just fine.
We have indexes on bigger views that take about 40 minutes to build.
Has anyone seen this behavior before, or have any suggestions?
Thanks.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
June 17, 2009 at 10:58 pm
Hey,
Did you happen to save the actual execution plan? That would make it easy to rule in or out some of the more usual causes (perhaps involving order-preserving exchanges).
Otherwise, an estimated execution plan would be interesting - though not as informative obviously.
Cheers,
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 19, 2009 at 1:04 pm
Here's the estimated execution plan for the view (sans index of course...)
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
June 19, 2009 at 1:57 pm
DCPeterson (6/17/2009)
We have a few indexed views in our reporting database and have had no problems creating them...until now. I've created a new view and am attempting to create a clustered index on it, but when I do the process chalks up a bit of cpu time and then it just goes into a perpetual (more than 4 hours) of wait on CXPackets. When I try throttling the maxdop down to 1, the index creation does begin to accumulate cpu cycles as well as reads and writes, but it still takes way too long (more than 12 hours and still not complete).The process is not being blocked, it just enters this perpetual wait state and stays there.
I've looked at the execution plan for the view, and while expensive (the view returns just under 60 million records) it is not unreasonable. I can get results from querying the view just fine.
We have indexes on bigger views that take about 40 minutes to build.
Has anyone seen this behavior before, or have any suggestions?
Thanks.
Are the auto-update-stats/auto-create-stats options turned on for your database?
Also, can you run DBCC SHOW_STATISTICS on some of your larger tables involved in the view?
This will give you the last time the stats was updated and the sampling size of the stats-update.
If the sampling size for the stats update is much smaller than the overall size of your table, try running UPDATE STATISTICS tblName WITH FULLSCAN on each of the tables. Once that is done, try repeating the process of creating the indexed view and see what happens.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 19, 2009 at 2:52 pm
I've checked the stats and they all seemed to be up to date (we have auto create and update stats on) But just to be sure, I forced an update stats, but no change... The execution plan seems reasonable for what the query is. I.e. the estimated number of rows are all pretty darn close and the optimizer appears to be making the best use of whatever indexes exist.
I also checked for fragmentation and none of the tables/indexes has more than 1% fragmentation.
At your suggestion, I forced an update to all the stats using fullscan, but the problem persists.
This is just not making any sense to me.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
June 19, 2009 at 3:39 pm
Would you mind showing the CREATE INDEX statement? As Gail says in this thread, CXPacket waits may stem from lagging parallel operations. Have you tried to set MAXDOP to lower value?
Regards
Piotr
...and your only reply is slàinte mhath
June 19, 2009 at 4:00 pm
Just for good measure, I'll include the view creation statement:
create view dbo.iv_SystemTransactionCardFund
with schemabinding
as
select
SystemTransactionID = st.ID,
c.CardNumber,
c.CardTypeID,
st.SystemTransactionTypeId,
c.ProgramID,
ci.BIN,
st.ContactID,
st.SoftwareClientInstanceID,
st.SystemReceiptTime,
st.LocalTransactionTime,
st.TimeZoneStandardNameId,
b.CurrencyNumericCode,
cf.Amount
from dbo.SystemTransactionCard stc
join dbo.SystemTransaction st
on stc.SystemTransactionId = st.[id]
join dbo.Card c
on stc.CardNumber = c.CardNumber
join dbo.CardInfo ci
on c.CardNumber = ci.CardNumber
join dbo.BIN b
on ci.BIN = b.BIN
join dbo.CardFund cf
on stc.SystemTransactionId = cf.SystemTransactionId
and stc.CardNumber = cf.CardNumber;
go
create unique clustered index cx_iv_SystemTransactionCardFund on dbo.iv_SystemTransactionCardFund
(
SystemReceiptTime asc,
ProgramID asc,
BIN asc,
SystemTransactionID asc,
CardNumber asc
);
I also tried creating the index with just CardNumber and SystemTransactionId (those two columns being the true unique key) thinking that I'd just have to create a covering nonclustered index to support querying, but making the clustered index key smaller didn't help either.
The data types are as follows:
SystemReceiptTime - datetime
ProgramId - int
BIN - char(6)
SystemTransactionId - int
CardNumber - char(16)
I did try using the with (maxdop = x) option. When it's set to anything larger than 1, I just get the CXPacket waits. When I set it to 1, I see cpu, reads, writes, and the working set grow, but I don't know how long it will take to complete as I canceled it after nearly 12 hours.
Like I said, we have other indexed views (some larger and more complex than this one, and several of them use the same base tables) that build just fine. This one is consistently causing problems and I can't spot the difference. There must be one though...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
June 19, 2009 at 4:08 pm
I forgot to say, that I know what CXPacket waits are. I would expect a large parallel query to experience some CXPacket waits, I just don't know why in this case the index creation never does anything BUT wait for CXPackets.
BTW this is on a quad dual-core processor box with 24Gb of RAM, attached to a raid-10 SAN array.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
June 19, 2009 at 4:39 pm
Ok, there was an old issue in SQL 2000 with undetected deadlocks, and though it was fixed, I saw 'hanging' (CXPacket locks) SELECT.. INTO query not longer than 2 weeks ago..
I noticed that you select card number and system transaction id columns from different tables in your view. Since these are inner joins, maybe you could try to change the definition of the view to return both SystemTransactionId and CardNumber column from the same table, as in
select
stc.SystemTransactionID,
stc.CardNumber,
c.CardTypeID,
(...)
This might change the CREATE INDEX execution plan for better.
Also, if the true unique key is (SystemTransactionId, CardNumber), why include additional columns?
Regards
Piotr
...and your only reply is slàinte mhath
June 19, 2009 at 4:45 pm
I didn't notice that, but it's worth a try. Thanks
The extra columns are there and in that particular order because those are the filters (from most selective to least) which will be used when querying against it.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
June 19, 2009 at 8:40 pm
Just taken a quick look at the estimated plan - would it be possible to try running:
select * from dbo.iv_SystemTransactionCardFund (option hash join)
to see if that reduces the CXPACKET waits?
edit: The reason I ask is that one of the repartition streams operations includes an order by, which can be the cause of intra-query parallelism deadlocks, or 'lagging' threads. Forcing a hash join plan (instead of the merges which require pre-sorted inputs) will remove this possibility. Don't ask me how to force hash joins in a create index statement (to materialize the view) because I don't know! We'll cross that bridge if we come to it...
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply