October 14, 2010 at 9:14 am
Configuration:
8 CPU (quad core)
256 GB mem (up to 100GB given to SQL Server)
Windows Enterprise 2008 r2 (64bit)
SQL Server Enterprise 2005 SP3CU5
I have a stored procedure that is being called 70,000+ times in an hour (20+ times per second), non peak.
This procedure has 3 result sets being returned all with 'FOR XML RAW'.
These result sets can return anywhere from one row to 100's of rows.
Last year I isolated this procedure to consuming 5% of the total CPU utilization on the system, I think it is higher now.
I have tried to retrieve the execution plan with the following script:
I have tried to analyze the SPR's query plan
select * from sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
where st.dbid = #
and st.objectid = ####
For some reason, the query_plan is never valued.
WHY?
But to my main question....
Other than absorbing precious CPU cycles, how does this impact other parts of the system when called in such an aggressive manner?
Memory?...can someone explain how FOR XML RAW impacts memory? The XML field I would guess is nothing but a formatted string field (varchar max in some way or another), and I am aware of the impacts of VARCHAR MAX when you start doing large string manipulations.
Any insight on this would be appreciative.
Could this procedure and the frequent use degrade a system to a point of non-recovery where a SQL Server re-start is needed?
I am trying to make a case to have this code redesigned in a way that FOR XML is not needed.
October 14, 2010 at 10:29 am
Eric1/2aB (10/14/2010)
I am trying to make a case to have this code redesigned in a way that FOR XML is not needed.
Do you have a personal vendetta against the FOR XML clause? You haven't provided the business need for the sproc. You haven't supplied the definition of the sproc. You haven't supplied an execution plan or estimate. All you've given us to go on is that the sproc contains a FOR XML clause. We can't tell you if FOR XML is the best tool to use in your case, because you haven't given us any details about your case.
Based on the information provided, a CURSOR is definitely the way to go. :sick:
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 14, 2010 at 11:00 am
Are you even sure it's the For XML part that's causing the problem? Have you run side-by-side tests with and without that clause on the same data+query+parameters?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 14, 2010 at 12:54 pm
Do you have a personal vendetta against the FOR XML clause? You haven't provided the business need for the sproc. You haven't supplied the definition of the sproc. You haven't supplied an execution plan or estimate. All you've given us to go on is that the sproc contains a FOR XML clause. We can't tell you if FOR XML is the best tool to use in your case, because you haven't given us any details about your case.
Drew...wow, I feel I need to put my shields up.
Vendatta no, cautious yes. As I stated, this is a frequently used stored procedure and the CPU utilization warrants my caution. Yes it is called a lot but it is not the most called SPR yet it is the highest in CPU Utilization.
The business need pertains to a developers requirement to assist in rendering data on a screen/form.
For licensing reasons, I can not post the SPR...please understand.
The SPR is three selects with the FOR XML RAW appended at the bottom. The only think out of the ordinary (other than the XML) is a large GROUP BY, I am investigating that now.
The logical reads are realatively low (avg 1700) with some outliers, writes are 0.
The execution plan for the proc is not recoverable, see the original statement. Do you know why I would not be able to recover the QPlan?
I'm really not asking for a better way, I'm asking if anyone has had experience with the FOR XML causing performance issues when used this frequently?
You could be right, I may be completely off base but that wouldn't be the first time.
October 14, 2010 at 3:18 pm
I don't see that how often it is executed has any bearing on the issue unless it starts blocking itself. I wouldn't immediately target the FOR XML as being the main culprit. I would look at missing indices before looking at the FOR XML clause.
Can't you run an execution plan on the definition portion of the stored proc? I would think that would be reasonably close to the actual execution plan unless you are using dynamic SQL.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 15, 2010 at 11:27 am
Break the problem down into component parts.
Try the query without the Group By.
Try the query without the For XML.
And so on.
Pull execution plans in SSMS for each version, including the full query.
You'll find the problem fast enough.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply