i have a stored procedure which accepts a xml ( the code is in sql 2000 so the data type is nvarchar(2000) ). The sp parses the xml and retrieves the value into a table variable which is used in subsequent joins to return data. A sample xml input is as follows
though i have shown only one item for discussion, it can be multiple values like 8538,8539 etc.
The sp was working fine initially. a couple of days back, the execution time went upto 3 minutes with the xml parsing ( sp is using OPENXML and sp_xml_preparedocument and sp_xml_removedocument) showing 69% of the overall execution cost.
The remote scan in execution plan shows the estimated number of records as 10000. but my input has only one item, 8538. I am not sure why this happens and why such a simple xml parsing takes 69% of overall execution cost.
could you please let me know what is wrong with this