NONEMPTYCROSSJOIN : how does it work with linked measure groups ?

Hi,

I am experiencing a strange behaviour with both NONEMPTYCROSSJOIN() and NON(CROSSJOIN()) Mdx statements when working with linked measure groups.

Bascially, I have created a very simple "regular" MOLAP cube on SSAS2005 with one fact table and 3 dimensions. I have one single Measure Group for this cube.

Now, if I create an empty cube (no data source view) and add the measure group of my first cube as a Linked Measure Group, it seems like the Mdx function NonEmptyCrossJoin() never returns anything whereas it does on the underlying cube.

Same problem occurs when using NonEmpty(CrossJoin()).

Is there anything I need to parameter to have the NonEmpty baheviour work correclty on the linked measure group ? I've read there's a "NonEmpty behaviour" parameter for calculated members, but I'm not even using calculated measures.

Is such a behaviour a known issue ?

Do you need a more detailed example? if so, on which database ?

To give some backgroung, I have first noticed this problem after migrating a virtual cube from SSAS 2000.

Many thanks in advance

Rgds,

Francois

I eventually found an answer to this.

There was obviously some type of confusion in the migration of the virtual cube (which is based in SSAS2000 on 3 physical cubes) because not all of the sub cubes' dimensions where used.

I have recreated by linked measure groups manually, removed the unused dimension and the nonemptycrossjoin works fine now.

Hope that helps anyone having the same type of problem.

- F.

Nonconvergence issue after upgrading MSDE to SQL 2005 Express

I'm experiencing an intermittent issue with merge replication after
upgrading one of the subscribers to SQL 2005 Express from MSDE 2000.
Here are a few details about the environment before I describe the
issue
1. Originally was SQL 2000 Standard Publisher with MSDE 2000
Subscribers all SP4
2. Upgraded (in place) Publisher to SQL 2005 Standard SP1 - no issues
3. Upgraded 1 of 3 subscribers to SQL 2005 Express SP1 encountered the
following issue only on the upgraded Express installation
4. Merge replication is running continuously as a pull subscription
with column-level tracking
5. Replication is running as a scheduled task on the subscriber (pull)
by executing replmerg.exe
Issue:
Background: A contacts table has addresses stored in a separate table
with the address_id in the contacts table referencing the address_id
in the addresses table
1. Contact created on subscriber without an address (null address_id)
2. Replication runs (contact uploaded to publisher)
3. Contact updated at subscriber to have an address (address_id
populated)
4. Replication uploads changes to publisher
4a. The address is uploaded to the publisher
4b. The address_id update to the contact record appears to be uploaded
to the publisher (confirmed by running SQL profiler)
4c. After the replication session is complete the address_id update to
the contact record appears to have not been made or is rolled back
5. Replication downloads changes to the subscriber and determines that
the address is not part of the partition for the subscriber (because
the address_id is not present in the contact record) and removes it
from the subscriber (the address is still present on publisher)
If I were to bet on a theory about why the address_id update to the
contact record is getting lost I would put my money on a deadlock (or
similar issue) and rollback but I would be interested to hear if
anyone has run into similar issues. I'd also be interested to know why
SQL 2005 Express would behave differently when replicating than MSDE
2000 (this setup has been running for almost 2 years on the MSDE with
no issues). If it is a deadlock/rollback then does the MSDE subscriber
handle the rollback differently than the Express subscriber? Is there
a re-try step that is not running in Express that does run in MSDE?
Have you logged to an output file or run profiler to determine exactly what
is happening?
If it is a deadlock the merge agent is transactional in nature and will put
the error in a retry loop at the end of each batch.
So this doesn't really explain what you are seeing.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Brian Fell" <brianfell@.gmail.com> wrote in message
news:1182807685.623799.115710@.u2g2000hsc.googlegro ups.com...
> I'm experiencing an intermittent issue with merge replication after
> upgrading one of the subscribers to SQL 2005 Express from MSDE 2000.
> Here are a few details about the environment before I describe the
> issue
> 1. Originally was SQL 2000 Standard Publisher with MSDE 2000
> Subscribers all SP4
> 2. Upgraded (in place) Publisher to SQL 2005 Standard SP1 - no issues
> 3. Upgraded 1 of 3 subscribers to SQL 2005 Express SP1 encountered the
> following issue only on the upgraded Express installation
> 4. Merge replication is running continuously as a pull subscription
> with column-level tracking
> 5. Replication is running as a scheduled task on the subscriber (pull)
> by executing replmerg.exe
>
> Issue:
> Background: A contacts table has addresses stored in a separate table
> with the address_id in the contacts table referencing the address_id
> in the addresses table
> 1. Contact created on subscriber without an address (null address_id)
> 2. Replication runs (contact uploaded to publisher)
> 3. Contact updated at subscriber to have an address (address_id
> populated)
> 4. Replication uploads changes to publisher
> 4a. The address is uploaded to the publisher
> 4b. The address_id update to the contact record appears to be uploaded
> to the publisher (confirmed by running SQL profiler)
> 4c. After the replication session is complete the address_id update to
> the contact record appears to have not been made or is rolled back
> 5. Replication downloads changes to the subscriber and determines that
> the address is not part of the partition for the subscriber (because
> the address_id is not present in the contact record) and removes it
> from the subscriber (the address is still present on publisher)
>
> If I were to bet on a theory about why the address_id update to the
> contact record is getting lost I would put my money on a deadlock (or
> similar issue) and rollback but I would be interested to hear if
> anyone has run into similar issues. I'd also be interested to know why
> SQL 2005 Express would behave differently when replicating than MSDE
> 2000 (this setup has been running for almost 2 years on the MSDE with
> no issues). If it is a deadlock/rollback then does the MSDE subscriber
> handle the rollback differently than the Express subscriber? Is there
> a re-try step that is not running in Express that does run in MSDE?
>
|||On Jun 25, 10:06 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> Have you logged to an output file or run profiler to determine exactly what
> is happening?
> If it is a deadlock the merge agent is transactional in nature and will put
> the error in a retry loop at the end of each batch.
> So this doesn't really explain what you are seeing.
> --
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com"Brian Fell" <brianf...@.gmail.com> wrote in message
> news:1182807685.623799.115710@.u2g2000hsc.googlegro ups.com...
>
>
>
>
> - Show quoted text -
I ran profiler and saw no evidence of any problem however I do see the
update with the address_id in it (and an entry that indicated that
replication thought the upload was successful (Uploaded 2 change(s) in
''contacts'' (1 insert, 1 update) - one of those for the address entry
as well). Unfortunately, it took me three weeks to catch this thing
with profiler and I didn't have the replication logging to a file when
I started it up that day (was switching back and forth between
watching the console and reviewing a log). Any ideas about something
to look at would be greatly appreciated.
Thanks,
Brian Fell
|||You will probably have to try to repro the problem using the specific set of
commands you outline in your original post.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Brian Fell" <brianfell@.gmail.com> wrote in message
news:1182824624.031896.140300@.o61g2000hsh.googlegr oups.com...
> On Jun 25, 10:06 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> I ran profiler and saw no evidence of any problem however I do see the
> update with the address_id in it (and an entry that indicated that
> replication thought the upload was successful (Uploaded 2 change(s) in
> ''contacts'' (1 insert, 1 update) - one of those for the address entry
> as well). Unfortunately, it took me three weeks to catch this thing
> with profiler and I didn't have the replication logging to a file when
> I started it up that day (was switching back and forth between
> watching the console and reviewing a log). Any ideas about something
> to look at would be greatly appreciated.
> Thanks,
> Brian Fell
>

NoLock vs ReadPast

I have been experiencing deadlock errors with two stored procs that I am using.

SP1 is a read query that gets all unprocessed orders (status = 0)

SP2 is an insert query that inserts new orders (Status = 0) uses a transaction.

I have a multithreaded application and once in a while the read query (SP1) will try to read a new row that has just been inserted but not committed yet hence the deadlock arises.

If i use a hint "With(NoLocks)" this will be a dirty read and still read the uncommitted insert from SP2 - is this correct?

Where as if I use hint "With(ReadPast)" this will now only read committed rows and hence the deadlock should not arise - it will not read any uncommitted rows - Correct?

So I think that it is better to use READPAST than NOLOCK. Any orders that have status = 0 not picked up will get picked up on the next round when SP1 is executed again.

Any thougths or suggestions are always appreciated.

Jawahar

I have a lot of experience with NOLOCK. The basic different in NOLOCK and READPAST is that NOLOCK will read the uncommitted data, preventing blocking because of that specific read. However, READPAST skips those rows. If you are updating a row, then NOLOCK reads the uncommitted data if the transaction is still open. If you use READPAST, it doesn't read the row at all, as I understand it.

Since I have a lot of experience with NOLOCK, I would suggest NOLOCK because I understand it a lot better.

I'm not all that convinced that your reads are causing DEADLOCKS however. I wonder if you are getting multiple inserts or updates that are blocking each other. The read locks (READ COMMITTED), by default, will just wait until the insert is done and then read the new inserted row. NOLOCK just tells it not to wait and reads UNCOMMITTED.

To test this, I would use NOLOCK and see if you still have the same problem.

|||

I would almost never suggest you use NOLOCK in a production environment. You don't want to have any chance that you have two users fetching the same row to process. So I would suggest you use readpast to read only committed rows, but skip those that are being inserted, in case that row fails.

As for deadlocks, I can't exactly fathom why this is occurring from the limited information. It is possible that you have indexing problems, causing full table scans to occur on selects... Can you post the table structure and queries.

|||I disagree about the production comment. If you are pulling reports, you want to be able to pull the same record quickly--regardless of locks. As for selecting the same record by two users at the same time, that would occur anyway whether you use NOLOCK or not--assuming that there is not an open transaction modifying the situation.|||There is a possibility for rows to show up twice when using NOLOCK because of page splitting so beware of the hint unless you know exactly how your data is used. This does not mean you should never use NOLOCK of course, there are many situations in which it is completely sane to use it but just remember the consequences.|||

Chris:

I think you have misunderstood what Louis means. I think here when he says "production" he means as part of a record-updating process -- in which case if you are reading a record for update you would never want a dirty read -- using a NOLOCK hint -- but always want a "clean" read of the data.

Dave

|||

Dave,

I think you are correct. I agree with the statement in that case :)

|||

All thank you for your insight

Here are my two sp that cause the Deadlock to occur sometims - I have poseted the queries ony not the whole SP with input paramters etc but the queries are the guts of the SP. I did tunr on Deadlock tracing and these SP were identified as the cause of the deadlock situtation - Many thanks -Jawahar

SP 1 - Read (currently there are no hints) - PLEASE note the queries are not optimized and do not follow good coding standards)

BEGIN
set rowcount @.nMaxMessages
select A.* from MsgRequest A
where ProcessingCode = 0
and RequestID = ( select Min(RequestID) from MsgRequest B where A.ClientID= B.ClientID and B.ProcessingCode = 0 )
and A.ClientID NOT IN (select ClientID from MsgRequest where ProcessingCode = 1)
order by ReceiveDT ASC
END

SP 2 Insert (currently there are no hints) - PLEASE note the queries are not optimized and do not follow good coding standards)

BEGIN
BEGIN TRAN
insert into MsgRequest ( ReceiveDT, ClientID, CommandCode, ProcessingCode, MessageLen, QueryParameterString,
MessageData, ServiceOrderID, NewServiceOrderFlag, ClientVersionNumber)
Values ( getdate(), @.ClientID, @.CommandCode, @.ProcessingCode, @.MessageLen, @.QueryParameterString,
@.MessageData, @.ServiceOrderID, @.NewServiceOrderFlag, @.ClientVersionNumber)
if (@.@.error != 0)
BEGIN
RAISERROR 20001 'Error in csp_MsgRequestInsert'
ROLLBACK TRAN
RETURN(1)
END
COMMIT
return (0)
END

|||

Jawahar:

Is the RequestID column unique in the MsgRequest table? That is, is the RequestID a key to this table? Also, is the data returned by this procedure used as a select list or is this potentially used as the basis for a record update?

Dave

|||

Dave,

Yes the RequestID is the Primary key

The data from the select is processed row by row using a multithreaded system. We run a windows service (multithreaded) to process the data (new orders).

Jawahar

|||

Jawahar:

There are a few other things that I need to know:

How many rows are in the MsgRequest table ?|||

You need to first determine the cause of the deadlock before trying to use locking hints. What version of SQL Server are you using? Did you enable trace flag # 1204? In SQL Server 2005, there is a new trace flag# 1222 and trace events that will help identify the cause. See link below for troubleshooting steps for SQL Server 2000:

http://support.microsoft.com/default.aspx/kb/832524/

|||

Jawahar:

Umachandar is right about the need to fully understand your deadlock. But I went ahead and attempted to mock up your select query.

First, I created an index on the MsgRequest table based on (1) ProcessingCode, (2) ClientID and (3) requestID. I figured that the requestID component was not completely necessary but I wanted to avoid some bookmark lookups so I included. When I got all done testing I tried recreating the index without the requestID component and found that when I removed the requestID component from the index my logical reads increased from 38 to 4007 so I left it in.

I was able to improve on the performance of your select query by eliminating one of the scans through the MsgRequest table. The query I used was:

select A.*
from msgRequest A
inner join
( select clientId,
max (processingCode) as max_processingCode,
min (requestID) as min_requestID
from msgRequest p
where processingCode between 0 and 1
group by clientId
) b
on a.requestID = b.min_requestID
and max_processingCode = 0
order by ReceiveDT ASC

This query returned for me the same rows as your original query was returning and ran with far less logical IO. I don't like the A.* syntax, but like you said ... Sometimes you got what you you got. Hopefully, this query will provide (1) a smaller lock profile and (2) a much smaller IO profile.

Dave

|||

Jawahar:

One thing that I failed to point out is that my test was based on a relatively low cardinality of the '0' and '1' processing codes compared to all other processing codes. I used a 1000000 row test table with 8000 rows in the 1 process state and 800 rows in the 0 process state because it looks to me like we are talking about a "new record" process.

Dave

|||

Dave

Thanks for your new suggestion on the Selection query - sorry for the late reply

I am using SQL Sever 2000

I did run the trace DBCC Trace 1204 and 3605 and found that the two SP I have listed are the ones that are involved in the Deadlock. The Select SP being the victim each time. Currently I only have an index on the RequestID column, but adding the ClientID and Processing code is a good Idea.

Out table at present has 600, 000 records. 99.33 % of the records have already been processed and have a Processing code of = 2 and we never touch those records. At any given time we should not have more than 15 to 20 records of the Processing code = 0 and 15 to 20 records Processing code = 1.

would the DBCC trace be helpful to you?

Thanks again for your suggestions I will try yor revised query. Another process that I might add is to archive Rows that have already been processd to an archive table to help reduce the size of the request table.

-jawahar