May 21, 2013 at 2:38 pm
I really need help with optimizing this code. The subtree cost is really high, but it may just be a bug? Plan attached...
DECLARE @filename1 VARCHAR(255);
DECLARE @filename2 VARCHAR(255);
SELECT @filename1 = REPLACE(CAST(esf.value AS VARCHAR(255)), '.xel', '*xel')
FROM sys.server_event_sessions es
INNER JOIN sys.server_event_session_fields esf
ON es.event_session_id = esf.event_session_id
WHERE es.name = 'Monitor_Deprecated_Discontinued_features'
AND esf.name = 'filename';
SELECT @filename2 = REPLACE(CAST(esf.value AS VARCHAR(255)), '.xem', '*xem')
FROM sys.server_event_sessions es
INNER JOIN sys.server_event_session_fields esf
ON es.event_session_id = esf.event_session_id
WHERE es.name = 'Monitor_Deprecated_Discontinued_features'
AND esf.name = 'metadatafile';
SET STATISTICS IO ON
SELECT DISTINCT FinalData.R.value ('@name', 'nvarchar(50)') AS EventName,
FinalData.R.value ('data(data/value)[1]', 'nvarchar(500)') AS Feature,
FinalData.R.value ('data(data/value)[2]', 'nvarchar(500)') AS MESSAGE,
FinalData.R.value ('(action/.)[1]', 'nvarchar(50)') AS DatabaseID,
FinalData.R.value ('(action/.)[2]', 'nvarchar(50)') AS SQLText
FROM ( SELECT CONVERT(XML, event_data) AS xmldata
FROM sys.fn_xe_file_target_read_file(@filename1, @filename2, null, null)) AsyncFileData
CROSS APPLY xmldata.nodes ('//event') AS FinalData (R)
WHERE FinalData.R.value ('data(data/value)[1]', 'nvarchar(500)') <> 'Deprecated encryption algorithm'
SET STATISTICS IO OFF
(16 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 87955, physical reads 0, read-ahead reads 0,
lob logical reads 122773, lob physical reads 0, lob read-ahead reads 0.
Jared
CE - Microsoft
May 21, 2013 at 4:30 pm
Hi,
The plan tells that everything is just normal.
What about MAXDOP and the Cost threshold for parallelism? Every one node in the sqlplan is marked to use parallelism.
Is it same every time you execute this query?
Regards,
IgorMi
Igor Micev,My blog: www.igormicev.com
May 21, 2013 at 4:59 pm
Costs are guidelines and SQL tends to overestimate XML breakouts in my experience. I don't see anything particularly strange here, however. Also, this is an estimated query plan.
Are you experiencing seriously high run times or anything? Costing is structured so deep in the system that it's both black box and a liar to boot.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 22, 2013 at 6:55 am
Not experiencing any extremely high times (under a min). I was able to bring the cost down by taking out the cross apply on the nodes. Cost threshold would be irrelevant for this since the cost is already so high. Also tried MAXDOP 1 and that didn't help anything; didn't expect it to really either since I am not seeing CXPACKET waits on the system.
The real goal here was to try to reduce the cost and understand why it is so high. Clearly has to do with xml parsing. I could also try to bring the size of the target files down to reduce the size of the xml file. Including new code and new plan.
DECLARE @filename1 VARCHAR(255);
DECLARE @filename2 VARCHAR(255);
SELECT @filename1 = REPLACE(CAST(esf.value AS VARCHAR(255)), '.xel', '*xel')
FROM sys.server_event_sessions es
INNER JOIN sys.server_event_session_fields esf
ON es.event_session_id = esf.event_session_id
WHERE es.name = 'Monitor_Deprecated_Discontinued_features'
AND esf.name = 'filename';
SELECT @filename2 = REPLACE(CAST(esf.value AS VARCHAR(255)), '.xem', '*xem')
FROM sys.server_event_sessions es
INNER JOIN sys.server_event_session_fields esf
ON es.event_session_id = esf.event_session_id
WHERE es.name = 'Monitor_Deprecated_Discontinued_features'
AND esf.name = 'metadatafile';
SET STATISTICS IO ON;
WITH cte(EventName, Feature, MESSAGE, DatabaseID, SQLText)
AS
(
SELECT DISTINCT xmldata.value ('(/event/@name)[1]', 'nvarchar(50)') AS EventName,
xmldata.value ('(/event/data/value)[1]', 'nvarchar(500)') AS Feature,
xmldata.value ('(/event/data/value)[2]', 'nvarchar(500)') AS MESSAGE,
xmldata.value ('(/event/action/value)[1]', 'nvarchar(50)') AS DatabaseID,
xmldata.value ('(/event/action/value)[2]', 'nvarchar(50)') AS SQLText
FROM
(SELECT CAST(event_data AS XML) AS xmldata
FROM sys.fn_xe_file_target_read_file(@filename1, @filename2, null, null)
) AsyncFileData
WHERE xmldata.value ('(/event/data/value)[1]', 'nvarchar(500)') <> 'Deprecated encryption algorithm'
)
SELECT * FROM cte
Table 'Worktable'. Scan count 14328, logical reads 1501850, physical reads 0,
read-ahead reads 0, lob logical reads 16, lob physical reads 0, lob read-ahead reads 0.
Jared
CE - Microsoft
May 22, 2013 at 12:26 pm
The only person I know around these parts who has gone deep enough into the internals to really understand costing and why a particular number would be x would be Paul White (SQLKiwi). I'd recommend dropping him a PM, but I haven't seen him around here much lately, so you might want to go to him and his blog.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 22, 2013 at 1:53 pm
Evil Kraig F (5/22/2013)
The only person I know around these parts who has gone deep enough into the internals to really understand costing and why a particular number would be x would be Paul White (SQLKiwi). I'd recommend dropping him a PM, but I haven't seen him around here much lately, so you might want to go to him and his blog.
Good call. Thanks, I'll see what he's got before I send him a mail.
Jared
CE - Microsoft
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply