Recently when I was working on optimization of a store procedure. I tried to see the execution plan of that procedure using DMV sys.dm_exec_query_plan by passing the Plan_handle of that store procedure. When I clicked on the query_plan hyperlink to display the graphical execution plan I got below error window saying that:
"Unable to show XML The following error happened.
There is an unclosed literal string. Line 1, position 2097154."
I have faced this problem earlier on my system so I was aware that how to fix it. Actually this error comes in picture because of limitation set by default of 2 MB for XML data. The XML Data for my procedure was more than 8 MB. We can check the size of our cached plan by using DMV sys.dm_exec_cached_plans. So, To see the graphical execution plan we have to increase the size of XML Data. We can follow below steps to increase the XML data size:
From SSMS Go to Tools > Options >Query Results> SQL Server > Results to Grid
Here we can see that only 2 MB data size is specified for XML data. Just increase it to 5 MB or unlimited according to your situation. Click OK.
Go back and execute the query. BINGO!!!!! Problem Solved. :)
"Unable to show XML The following error happened.
There is an unclosed literal string. Line 1, position 2097154."
I have faced this problem earlier on my system so I was aware that how to fix it. Actually this error comes in picture because of limitation set by default of 2 MB for XML data. The XML Data for my procedure was more than 8 MB. We can check the size of our cached plan by using DMV sys.dm_exec_cached_plans. So, To see the graphical execution plan we have to increase the size of XML Data. We can follow below steps to increase the XML data size:
From SSMS Go to Tools > Options >Query Results> SQL Server > Results to Grid
Here we can see that only 2 MB data size is specified for XML data. Just increase it to 5 MB or unlimited according to your situation. Click OK.
Go back and execute the query. BINGO!!!!! Problem Solved. :)
Thanks!!
No comments:
Post a Comment