You’re offline. This is a read only version of the page.
119
I have a SQL plan guide working for a DAX select and a delete with the help of Vilmos Kintera. Now I have SQL that DAX supplies the DataArea and Partition as values in place of parameters. The SQL also has parameters so it is a mix of a parametertized query and a query with values. I am attempting to apply a plan guide but so far all I have been able to do is create a plan guide for each value I have for dataArea. What I think can be done is to create a template that forces parameterization of the entire query and then a second plan guide against the parameterized query that forces the plan. Has anyone done this with a mixed query like I have ? One example is DELETE FROM INVENTSUMDELTA WHERE (((PARTITION=5637144576) AND (DATAAREAID=N'540')) AND (TTSID=@P1)). I am forcing a plan on this delete given SQL looses its mind now and then and does not use the index that matches the where clause despite or index maintenance efforts. Once this happens things goes south fast with inventory adjustments of any kind deadlocking / blocking all over the application.
*This post is locked for comments
I have the same question (0)
Just for the other forum readers' sake I will link the original topic, since this is related:
community.dynamics.com/.../214863
Regarding your current issue, since the statement executed must be a 100% match for the plan guide, I think you do need to create all possible versions if the partition id and dataarea id are within the query text. However, I might be wrong since I am not a database ninja and my SQL-fu is somewhat limited.
If someone has access to a DBA, I wouldn't mind if you could share more details for the topic as well, since I am interested too.
Share
Report
All responses (
Answers (
