April 28, 2009 at 6:37 am
Welcome back, Roy. It's good to see you again.
Paul, a few of the articles out there may be weak, but sometimes the discussion posts turn out to be real seminars.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 28, 2009 at 6:50 am
Paul, a few of the articles out there may be weak, but sometimes the discussion posts turn out to be real seminars.
Understood! I will look out for them.
/P
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 28, 2009 at 7:11 am
Paul White (4/28/2009)
On a separate subject, can I ask: Are articles checked or reviewed before publication on SSC?
There's no technical review done, if that's what you're asking. Firstly Steve's vision for this site is for the articles to be people's first step into technical writing and hence he doesn't want to make it too hard or intimidating for people to submit.
Second problem is that technical editing takes time. I did a 2 parter recently that got a full tech edit and the second part went back and forward 4 times before we were both happy with it, I think the first edit took almost a full day to do. Considering that Steve also write an editorial per day, record a podcast every day and handle the moderation functions of the site, doing a full tech edit of every article it not really practical.
Some people voluntarily ask others to check their articles. I just did so for one person and I'm waiting for feedback on one that I wrote, but it's purely voluntary.
Oh, and if you think that one's bad you should see the article from a week or so ago where someone seriously suggested truncating the transaction log and shrinking the database in order to backup and restore a database to a smaller drive. My comments were slightly on the blunt side.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 28, 2009 at 7:19 am
I wrote an article and it will be published soon. I would love it if you all read it and give me a feed back. I dont think I covered it all but I tried.
I would like some negative reviews on it so that I can learn how to write the articles better.
And Thanks Bob. It is good to be back.
-Roy
April 28, 2009 at 7:29 am
Gail,
Thanks for that detailed explanation, it is appreciated.
GilaMonster (4/28/2009)
Oh, and if you think that one's bad you should see the article from a week or so ago where someone seriously suggested truncating the transaction log and shrinking the database in order to backup and restore a database to a smaller drive. My comments were slightly on the blunt side.
Would that not work then? :laugh:
The latest suggestion in the date-time to date precision discussion, is to store the date part in a separate column as VARCHAR(8) in YYYYMMDD format (yes I did say VAR-char!), to maintain its value using triggers (ow!), and to create an index on it 'to optimize performance'.
I kinda see where the poster is coming from, and the suggestion's heart is in the right place, sure.
But if only lazy Microsoft would get around to producing some kind of computed column or something...! I feel a Connect entry coming on.
:sigh:
And so to bed.
Paul
edit: P.S. In your reply to Tao Klerks in that thread, is it not true that many plans can be in cache for the same proc? One set for serial execution and one for parallel? Within that, I seem to recall that different user SET options require additional plans. Or am I thinking of execution contexts now? Or do I just need sleep?!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 28, 2009 at 7:34 am
Paul White (4/28/2009)
Would that not work then? :laugh:
It'll work, that ain't the point
The latest suggestion in the date-time to date precision discussion, is to store the date part in a separate column as VARCHAR(8) in YYYYMMDD format (yes I did say VAR-char!), to maintain its value using triggers (ow!), and to create an index on it 'to optimize performance'.
What's wrong with a between statement?
Oh, and btw, some functions can be collapsed so that the optimiser gets correct values to do it's estimates if those functions are on parameters or constants. I have no recollection offhand what the criteria are for that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 28, 2009 at 7:51 am
GilaMonster (4/28/2009)
...some functions can be collapsed so that the optimiser gets correct values to do it's estimates if those functions are on parameters or constants. I have no recollection offhand what the criteria are for that.
Yes it's listed in BOL under Constant Folding in the index, or: http://msdn.microsoft.com/en-us/library/ms175933(SQL.90).aspx]
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 28, 2009 at 7:58 am
I posted this before as an afterthough edit, but I guess you missed it:
In your reply to Tao Klerks in that thread, is it not true that many plans can be in cache for the same proc? One set for serial execution and one for parallel? Within that, I seem to recall that different user SET options require additional plans. Or am I thinking of execution contexts now? Or do I just need sleep?!
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 28, 2009 at 8:02 am
Paul White (4/28/2009)
edit: P.S. In your reply to Tao Klerks in that thread, is it not true that many plans can be in cache for the same proc? One set for serial execution and one for parallel? Within that, I seem to recall that different user SET options require additional plans. Or am I thinking of execution contexts now? Or do I just need sleep?!
Set options and users, yes, different plans, however not reusable without exactly the same userID and/or set options. He was asking about different plans for different parameters, which is definitely not done.
Serial and parallel, I've never seen a case where there was both a serial and a parallel in cache. I did investigate is a while back. I know both are compiled, but I think only the one that's actually picked to execute is cached.
I just did a test on my quad core machine. Emptied the plan cache, ran a query that paralleled and then checked the plan cache. Only one plan there and it was the parallel version. There was no serial plan cached for it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 28, 2009 at 8:16 am
GilaMonster (4/28/2009)
Set options and users, yes, different plans, however not reusable without exactly the same userID and/or set options.
I think it is OR there, rather than AND. I don't think the connection_id is important.
GilaMonster (4/28/2009)
Serial and parallel, I've never seen a case where there was both a serial and a parallel in cache. I did investigate is a while back. I know both are compiled, but I think only the one that's actually picked to execute is cached.I just did a test on my quad core machine. Emptied the plan cache, ran a query that paralleled and then checked the plan cache. Only one plan there and it was the parallel version. There was no serial plan cached for it.
You may need to try different parameter values for the procedure (it's tougher to do with an ad-hoc query) to get the QO to choose a parallel plan once and then a serial plan. Assuming this is done before the plans age out of cache, there should be two plans for the proc.
I found the answer re: execution contexts, and it also covers the above:
http://msdn.microsoft.com/en-us/library/ms181055(SQL.90).aspx
Those are details I find hard to keep in my head completely, so it's well worth a skim over to refresh the brain cells.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 28, 2009 at 8:34 am
Bob Hovious (4/25/2009)
How about this one? Fresh off the presses...1. Create temporary table "A"
2. Populate it with base ids
3. Query table "B" and update one column in temp "A"
4. Query table "B" AGAIN and update another column in temp "A"
5. Repeat step 4 a couple more times for good measure.
This is actually a "recommended practice" at my current employer. "Because it's easier to maintain than a bunch of left joins."
Just finished the dev version of a process that should have consisted of one proc that simply selects from a couple of tables in one database. Instead, I have to create three tables, insert one set of data into each, and then go through a series of 18 update statements. That loads the data into the "holding tables". Then the final proc is a select from that holding table.
Whole thing could be a single select statement, with a couple of CTEs/Sub-queries, and instead it's 1163 lines of code. The select version that I wrote first ran in about 3 seconds (which is fine for the purpose this has), and the final version that's going to QA runs in 2 minutes (which is also fine for the final purpose, but why load the server up that way?).
But, the system architect, who's in charge of coding standards, insists on this. I get paid either way, but it hurts to write this way.
Ironically, he didn't want me to wrap all the ETL processes in a transaction, "because it might cause the tran log to bloat". Note that selects don't even get logged in there, and that a single insert with left joins would get logged once, but the process he insists on, requires all 18 updates to get logged, as well as the insert. Oh well.
- 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
April 28, 2009 at 8:35 am
Paul White (4/28/2009)
GilaMonster (4/28/2009)
Set options and users, yes, different plans, however not reusable without exactly the same userID and/or set options.I think it is OR there, rather than AND. I don't think the connection_id is important.
I never said the connection_id was important. It's not.
Set options affect the plan as, sometimes, does the userid if objects aren't qualified correctly.
Imagine a case where there are two users, Johan and Mary. Each has their own default schema and each has a table named Table1 in that schema (with the same structure).
Imagine that they both run the query SELECT col1, col2 FROM Table1 WHERE col1 > 137
How many plans are there in cache that textually match "SELECT col1, col2 FROM Table1 WHERE col1 > 137"?
This is what the UserID attribute (in sys.dm_exec_plan_attributes) is for. -2 if the plan is safe for reuse among different users, userid if it's not.
Not something I've ever seen with stored procs, have seen it with adhoc SQL from time to time.
As for the serial and parallel, I know that it's theoretically possible but, as I said, I played with it extensively sometime ago and I couldn't get both serial and parallel plans to appear in cache together. Never saw it on my prod system either.
They're definitely not both cached on first compile, or there should be two plans visible in the cached plans dmv.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 28, 2009 at 8:46 am
This is actually a "recommended practice" at my current employer
Gus, I have such mixed emotions on reading that....
On the one hand I sympathize and empathize. God love you, brother.
On the other hand, misery loves company. 😉
See line 1 of my signature quotes.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 28, 2009 at 10:05 am
GilaMonster (4/28/2009)
Paul White (4/28/2009)
edit: P.S. In your reply to Tao Klerks in that thread, is it not true that many plans can be in cache for the same proc? One set for serial execution and one for parallel? Within that, I seem to recall that different user SET options require additional plans. Or am I thinking of execution contexts now? Or do I just need sleep?!Set options and users, yes, different plans, however not reusable without exactly the same userID and/or set options. He was asking about different plans for different parameters, which is definitely not done.
Serial and parallel, I've never seen a case where there was both a serial and a parallel in cache. I did investigate is a while back. I know both are compiled, but I think only the one that's actually picked to execute is cached.
I just did a test on my quad core machine. Emptied the plan cache, ran a query that paralleled and then checked the plan cache. Only one plan there and it was the parallel version. There was no serial plan cached for it.
You can get two plans into the cache through parallelism. I've done it and seen it.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 28, 2009 at 11:52 am
The desert is looking good again...
The White House window washer is back...
Viewing 15 posts - 3,721 through 3,735 (of 66,000 total)
You must be logged in to reply to this topic. Login to reply