Need some help with some "XML Hell" please.

Question

Need some help with some "XML Hell" please.

Viewing 5 posts - 31 through 34 (of 34 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 1

I haven't. I'll give that a try. Thank you

mister.magoo - Tuesday, May 30, 2017 6:05 PM
Nice to see you having to do XML, Jeff!
Have you tested this with an XML index? Something like this?
DBCC TRACEON(8666) ; WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as p) SELECT --TOP 1000 ROWID = IDENTITY(int,1,1), XMLFragment = db.DbInfo.query('.') INTO StatsPreProcess FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp CROSS APPLY qp.query_plan.nodes('//p:Recompile') db (DbInfo) OPTION (MAXDOP 1,recompile) ; DBCC TRACEOFF(8666) ;
ALTER TABLE StatsPreProcess ADD PRIMARY KEY (ROWID);
CREATE PRIMARY XML INDEX ix_xml ON StatsPreprocess(XMLFragment);

:blush: I haven't because I made a really poor assumption (heh... must be the fumes from the XML 😉 ). My assumption was that XML indexes would be like any NonClustered index in that, unless they're a covering index to reduce the number of pages read from a wider table, they're not that useful and sometimes a bit harmful to performance if you actually do need to scan the whole table. Since we had isolated the XML to only those items that contained the RECOMPILE tag, I made the poor assumption that such indexes wouldn't help. The real proof of the pudding would be a test, which may help here and will certainly continue my education in the world of XML.

Thank you, good Sir, for the reminder that if you haven't tested it insitu, you really don't know. Like Sergiy would say, "A Developer must not guess... a Developer must KNOW"!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 2

Ok... here's what I've found out about the XML index that Magoo suggested. Regardless of the outcome, his suggestion was a great idea that needed to be tested. Thanks again, Magoo!

I first did the unindexed thing using the current optimization of copying only what is between the RECOMPILE tags (the current method) and then tried it with the XML index.
1. It took 00:08:04 to do the initial copy of only the RECOMPILEs on ~75,000 execution plans.
2. It took 00:00:34 to do the distinct parsing using the current method (no indexes).
3. It took 00:00:14 to do the same thing as #2 above for a savings of 20 seconds but that benefit was serious outweighed by the fact that it took 00:00:44 to build the XML index. Conclusion... the index wasn't worth it in this case.

Thinking there may be a different method to take advantage, I modified the code for #1 above to just copy the entire plan instead of pre-shredding for RECOMPILE.
1. It took 00:07:11 to copy the whole plans. That's a savings of 53 seconds.
2. It took more than 16 minutes to add the same index to the whole plans. Conclusion... the index wasn't worth it in this case.

So far, the most incredible advantage goes like this and is what I have in production right now...
1. About once a week, clear the "plan history" and the "statistics used" tables that I've created.
2. Run the first run, which includes the optimization of pre-shredding and copying only the XML between RECOMPILE tags. The whole shebang on about 75,000 execution plans takes something in the area of 10 minutes. That includes the final DISTINCT "shrefening" and population of the "statistics used" table.
3. The first day, additional runs sometimes take something less than 3 minutes simply because there are new plans that show up thanks to some heavy-lifting batch runs that run both overnight and during the day. Usually, though, the optimization of not copying any plan that has already appeared in the "plan history" table keeps things down around 6 seconds for each run. That has been allowing me to easily run the job once every 20 minutes with virtually no load on the system.

Next steps are...
1. Create a job to clear out all of the column stats that don't appear in the "statistics used table". Not sure what the schedule will be here but it could be nightly with as well as things appear to be running.
2. Add to that job, a rebuild of the stats that remain. I'm considering continued reliance on the sys.sysindexes compatibility view (which MS still uses, as well) to get the RowModCntr because it works correctly when using 3 part naming (makes dynamic SQL an easy option) as opposed to using the statistics properties function, which doesn't work when using 3 part naming to include the database. Sure, it runs, but it returns whatever is in the current database rather than what is in the target database. Including a "USE" statement in the dynamic sql may work so solve that problem to make the code more bulletproof for the eventual demise of the deprecated sys.sysindexes table but I haven't made that decision yet. I'm still playing with "POP code" (Proof-of-Principle) to see what happens when and how.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 3

Eirikur Eiriksson - Monday, May 29, 2017 2:59 PM
Ed Wagner - Monday, May 29, 2017 2:40 PM
Do you deliver? If you come all the way here, the pasta and vegetable courses are on me and the beer is on Jeff. 😀😀😀
Ed, to you I'll deliver any day 😉
😎
The oak is giving me 350 C using two third of the chamber, not too bad.
And we need to have a chat about the pasta, done it for many years cucina povera 😉
Of course the beer popsicles are on Jeff!!!

That finally happened several months ago. What an honor and a pleasure.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 4

Jeff Moden - Wednesday, December 12, 2018 10:54 PM
Eirikur Eiriksson - Monday, May 29, 2017 2:59 PM
Ed Wagner - Monday, May 29, 2017 2:40 PM
Do you deliver? If you come all the way here, the pasta and vegetable courses are on me and the beer is on Jeff. 😀😀😀
Ed, to you I'll deliver any day 😉
😎
The oak is giving me 350 C using two third of the chamber, not too bad.
And we need to have a chat about the pasta, done it for many years cucina povera 😉
Of course the beer popsicles are on Jeff!!!
That finally happened several months ago. What an honor and a pleasure.

The honor and pleasure was mutual and this was one 4000 miles "delivery" I do not regret!
😎

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

Eirikur Eiriksson - Thursday, December 13, 2018 4:43 AM
Jeff Moden - Wednesday, December 12, 2018 10:54 PM
Eirikur Eiriksson - Monday, May 29, 2017 2:59 PM
Ed Wagner - Monday, May 29, 2017 2:40 PM
Do you deliver? If you come all the way here, the pasta and vegetable courses are on me and the beer is on Jeff. 😀😀😀
Ed, to you I'll deliver any day 😉
😎
The oak is giving me 350 C using two third of the chamber, not too bad.
And we need to have a chat about the pasta, done it for many years cucina povera 😉
Of course the beer popsicles are on Jeff!!!
That finally happened several months ago. What an honor and a pleasure.
The honor and pleasure was mutual and this was one 4000 miles "delivery" I do not regret!
😎

Yes, the honor and pleasure was mine as well. It was great to meet you. It was also quite humbling that you traveled 4000 miles.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions