Question

Mystery query to pr4_rule_vw that does not show up on a global db trace

MSSQL DBA reports a query that runs periodically and takes a long time in the DB. The query takes very little time when run from MSSQL tools.

Checked SendStringParametersAsUnicode - that seems fine.

Went looking in alerts and could not find the query.

Enabled global db trace for a bit. Could not find it

What is this?   

SELECT pyClass AS "pyClass" , pxInsName AS "pxInsName" , pyRuleSet AS "pyRuleSet" , pyRuleSetVersion AS "pyRuleSetVersion" , pxUpdateDateTime AS "pxUpdateDateTime" , pyLabel AS "pyLabel" , pyRuleName AS "pyRuleName" , pyClassName AS "pyClassName" , pyRuleAvailable AS "pyRuleAvailable" , pyCircumstanceProp AS "pyCircumstanceProp" , pyCircumstanceVal AS "pyCircumstanceVal" , pyCircumstanceDateProp AS "pyCircumstanceDateProp" , pyCircumstanceDate AS "pyCircumstanceDate" , pyRuleStarts AS "pyRuleStarts" , pyRuleEnds AS "pyRuleEnds", pxInsName as "pxInsHandle" FROM Rules.pr4_rule_vw WHERE pxObjClass = 'Data-Rule-Summary' AND ( ( pxInsName LIKE '%LOCALREQUIREMENTS%' ) AND ( pyClass NOT LIKE 'Rule-AutoTest%' ) ) ORDER BY pyRuleName

**Moderation Team has archived post**

This post has been archived for educational purposes. Contents and links will no longer be updated. If you have the same/similar question, please write a new post.

Correct Answer
May 17, 2016 - 5:31am

Looks to be coming from QuickSearchRulesByName list view? I am not entirely sure though because there are no table aliases.

Comments

Keep up to date on this post and subscribe to comments

Pega
May 17, 2016 - 5:31am

Looks to be coming from QuickSearchRulesByName list view? I am not entirely sure though because there are no table aliases.

May 17, 2016 - 11:00am
Response to nistr

That's it. Thanks. Didnt think to try the obvious since this was a UAT system and was supposed to have working elastic search

lots of developers fixing things in UAT (no comment) and elastic search indexes were corrupt and they were running old: queries.

Reason i didnt catch it from global db trace is that developers stopped working in system while a large unit test was running

Pega
May 18, 2016 - 1:55am
Response to AndrewWerden

Ah, it would be good to know if the corruption of Elastic Search indices are now fixed. Which version of Pega 7 are they using?

May 19, 2016 - 10:38am
Response to nistr

Elastic Search problem was a training and operations issue - they had copied database to new environment and never set indexing node and built the index.

Pega
May 19, 2016 - 11:50am
Response to AndrewWerden

Cloning the DB is always an issue. Especially having the pr_sys_statusnodes table not truncated before startup of the new nodes. Thanks for the details.