Long running queries block TM1

[FallenCipher]

I have a problem with TM1 9.5.2 FP2 and long running queries initiated by Cognos BI 10.1.1.
Long running queries can block TM1 completely which means that no further API commands (or MDX commands initiated by Cognos BI) can be executed against TM1. Even TI Processes will not execute.
In TM1Top i can see that all these tasks are put on hold (Wait state). Cancellation of threads in TM1Top are ignored.
The only way to cancel the long running queries and gain access to TM1 again is by killing the tm1sd.exe process and restarting the TM1 services.
Is there any way to prevent such blocking in TM1 or set a timeout for long running tasks?

Below is my tm1s.cfg

Code: Select all

[TM1S]
ParallelInteraction=T
LockPagesInMemory=F
ServerLogging=F
SecurityPackageName=Kerberos
IntegratedSecurityMode=4
UseSSL=T
ServerName=OLAPDB
DataBaseDirectory=D:\Databases\TM1\OLAPDB
LoggingDirectory=D:\Logfiles\TM1\OLAPDB
AdminHost=10.5.60.20
PortNumber=22345
ReceiveProgressResponseTimeoutSecs=36000
Language=DEU
SaveTime=
DownTime=
ProgressMessage=True
AuditLogOn=F
AuditLogMaxFileSize= 100 MB
AuditLogUpdateInterval=60
IdleConnectionTimeOutSeconds=14400
AllowSeparateNandCRules=T
AllRuleCalcStargateOptimization=T
ServerCAMURI=http://10.5.60.20:9300/p2pd/servlet/dispatch
ClientCAMURI=http://10.5.60.20:80/ibmcognos/cgi-bin/cognosisapi.dll
ClientPingCAMPassport=900
MaximumViewSize=10000
PrivilegeGenerationOptimization=T

[George Regateiro]

You might want to drop a line to IBM. I did a quick search and cannot see it, but I know there was a defect in the 9.5 series where long running threads were causing locking. They might be able to point you to where the fix was implemented or a possible timeframe.

[FallenCipher]

I wonder if this problem still exists in TM1 10.1.0 FP1.
Anyway, thanks for the help George.

[Gabor]

Is there any dynamic subset, which is referenced by your calculation? This can cause the lock in older versions than 10.1.
As far as I know, for all versions 9.1 ... 9.5.2 the following is more or less valid (an extract of an old Applix bulletin):

"In the current release stream, when a public dynamic subset that is part of a read query is calculated, it will hold a lock that will block other users
of that same public dynamic subset. Once the dynamic subset is calculated and published, then all other users can use that object concurrently.
... In the 9.1+ servers, dynamic subset recalculation is now protected against concurrent modification. This means in practice that any public
dynamic subset may encounter lock contention with a long running calculation.
... Solutions: Replace the dynamic subsets with static subsets."

New Features Guide 10.1.0:
Tm1s.cfg UseLocalCopiesforPublicDynamicSubsets

Allows public dynamic subsets to improve performance and reduce locking by
using local copies of the subset when possible.

By default, or if the parameter is not present in the tm1s.cfg file, UseLocalCopiesforPublicDynamicSubsets is enabled. To restore the earlier
method of saving Dynamic Subsets, set this parameter to F.

[FallenCipher]

I'm not sure if Cognos BI creates dynamic subsets via MDX but it's highly possible.
Updating TM1 to 10.1.0 FP1 seems to be the first step in isolating the problem.

[David Usherwood]

As an alternative line of approach, is the source cube fully calculated before the query is run? Calculations are _still_ single threaded. Try running a ViewConstruct on the relevant area of the cube before you run the report.
But I share your suspicion about dynamic subsets.