April 12, 2011 at 7:48 am
Brandie Tarvin (4/12/2011)
I think this cursor suits Stefan's needs best.
DECLARE OMG_NotAnotherCursor CURSOR GLOBAL_SCREAM BACKWARD_FACING
STATIC_THE_SUPERHERO PESSIMISTIC DB_LOCKDOWN
FOR
SELECT Superpower
FROM dbo.JusticeLeague
WHERE SupType = 'Lightning'
OPEN OMG_NotAnotherCursor
FETCH LAST FROM OMG_NotAnotherCursor
INTO @WhatsThisVariableForAgain
WHILE @@FETCH_STATUS = 'headdesk'
BEGIN
SET @YetAnotherVariable = DBCC Timewarp()
FETCH LAST FROM OMG_NotAnotherCursor
INTO @WhatsThisVariableForAgain
END
THROWOUT OMG_NotAnotherCursor
DEALLOCATE CursorAbilities
GO
Having had a little bit of fun with this (and probably making a horrible joke that no one thinks is funny), I will admit cursors do have their place. I've actually found, in some circumstances, cursors are faster than WHILE Loops. But, admittedly, that's part in due to database design and part in due to what I was trying to accomplish.
I thought DBCC TimeWarp() has 127 required parameters?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 12, 2011 at 7:49 am
Stefan Krzywicki (4/12/2011)
I see SanDroid has started digging again...
Where?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 12, 2011 at 7:53 am
WayneS (4/12/2011)
Brandie Tarvin (4/12/2011)
I think this cursor suits Stefan's needs best.
DECLARE OMG_NotAnotherCursor CURSOR GLOBAL_SCREAM BACKWARD_FACING
STATIC_THE_SUPERHERO PESSIMISTIC DB_LOCKDOWN
FOR
SELECT Superpower
FROM dbo.JusticeLeague
WHERE SupType = 'Lightning'
OPEN OMG_NotAnotherCursor
FETCH LAST FROM OMG_NotAnotherCursor
INTO @WhatsThisVariableForAgain
WHILE @@FETCH_STATUS = 'headdesk'
BEGIN
SET @YetAnotherVariable = DBCC Timewarp()
FETCH LAST FROM OMG_NotAnotherCursor
INTO @WhatsThisVariableForAgain
END
THROWOUT OMG_NotAnotherCursor
DEALLOCATE CursorAbilities
GO
Having had a little bit of fun with this (and probably making a horrible joke that no one thinks is funny), I will admit cursors do have their place. I've actually found, in some circumstances, cursors are faster than WHILE Loops. But, admittedly, that's part in due to database design and part in due to what I was trying to accomplish.
I thought DBCC TimeWarp() has 127 required parameters?
I'm being lazy typist today. I figured each individual could fill out those params on their own. @=)
April 12, 2011 at 8:03 am
WayneS (4/12/2011)
Stefan Krzywicki (4/12/2011)
I see SanDroid has started digging again...Where?
Still on your QOTD. bottom of the page and then on the next page.
I love when Gail is correcting someone about how the question is correct and he comes on to tell her "As I see you and others have noticed, there are several things wrong with the question and answer", part of the answer being her blog post...
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 12, 2011 at 8:20 am
Stefan Krzywicki (4/12/2011)
WayneS (4/12/2011)
Stefan Krzywicki (4/12/2011)
I see SanDroid has started digging again...Where?
Still on your QOTD. bottom of the page and then on the next page.
I love when Gail is correcting someone about how the question is correct and he comes on to tell her "As I see you and others have noticed, there are several things wrong with the question and answer", part of the answer being her blog post...
you cannot, and will not, win that argument. Best to let it go.
April 12, 2011 at 8:27 am
Steve Jones - SSC Editor (4/12/2011)
you cannot, and will not, win that argument. Best to let it go.
Non Illegitimi Carborundum 😀
Far away is close at hand in the images of elsewhere.
Anon.
April 12, 2011 at 8:40 am
Steve Jones - SSC Editor (4/12/2011)
Stefan Krzywicki (4/12/2011)
WayneS (4/12/2011)
Stefan Krzywicki (4/12/2011)
I see SanDroid has started digging again...Where?
Still on your QOTD. bottom of the page and then on the next page.
I love when Gail is correcting someone about how the question is correct and he comes on to tell her "As I see you and others have noticed, there are several things wrong with the question and answer", part of the answer being her blog post...
you cannot, and will not, win that argument. Best to let it go.
I've walked away at this point. When he sent me some insulting PMs and showed he's just a troll it drove home that he has no evidence to back up his position. He's just trying to drag others to his level.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 12, 2011 at 8:40 am
Steve Jones - SSC Editor (4/12/2011)
Stefan Krzywicki (4/12/2011)
WayneS (4/12/2011)
Stefan Krzywicki (4/12/2011)
I see SanDroid has started digging again...Where?
Still on your QOTD. bottom of the page and then on the next page.
I love when Gail is correcting someone about how the question is correct and he comes on to tell her "As I see you and others have noticed, there are several things wrong with the question and answer", part of the answer being her blog post...
you cannot, and will not, win that argument. Best to let it go.
Ah geez... I thought he was done over there. Yep, I'm letting his remarks go, and after several PMs, even have blocked him from sending me any more.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 12, 2011 at 8:42 am
Jack Corbett (4/11/2011)
GilaMonster (4/11/2011)
GSquared (4/11/2011)
GilaMonster (4/11/2011)
Ok, had enough complaints about the lizard. Let's see if this is better.What/who is it?
And what was wrong with the lizard?
Can't recall, I deleted the PMs. Complaining that it's ugly, that no sensible person would have a venomous lizard as an avatar, that I should delete the 'insect' picture.
Can you send me the picture? I'll make it my avatar.:-D
I'm in too! But since I don't get the chance to post often, it's more of a show of support for Gail than anything since no one will see it. It would be awesome to see an entire thread somewhere with Gila Monsters on it though. I'd die laughing. 😀
April 12, 2011 at 8:46 am
Stefan Krzywicki (4/11/2011)
GilaMonster (4/11/2011)
Stefan Krzywicki (4/11/2011)
I'm likely using the wrong word. the metrics that are gathered by SQL Server to improve performance on subsequent runnings of a query. I knew the word at one point, but can't think of it now. The stored plan?No such thing.
The 'metrics' that it gathers based on column distribution are the column/index statistics, they're stored in the system tables and are just based on the column distribution, not previous query executions.
The cached plan is based on the optimiser's estimate of the costs. It doesn't take into account previous executions, just the table structure, indexes, column/index statistics.
If you're thinking about what's in sys.dm_exec_query_stats, that's just cumulative statistics for reporting. They're not reused by SQL in any way.
Huh. I wonder where I got that idea then. I could have sworn I'd attended a lecture or read an article that said the subsequent runnings of a query were faster because SQL Server stores reuse information. Maybe it was wishful thinking. : -) Thanks for setting me straight. I'd been worried that my test comparison speeds were off and therefore invalid.
Stefan,
One method I use for levelling the playing field when doing performance testing is to clear the cache and buffers prior to running my code. Especially, if I'm running my proc over and over again (with little tweaks in between) on my dev box, where there aren't a lot of competing queries vying for resources, it can be hard to tell whether a query ran fast becuase I'm really that good a coder or whether the result set was already cached in memory from a prior run.
DBCC FREEPROCCACHE
This command will release any cached plan, forcing the proc to recompile.
DBCC DROPCLEANBUFFERS
This command will clean the data cache to ensure that your result set doesn't still live in memory. You can use the CHECKPOINT command to clean the dirty buffers first.
Again, these commands can be useful while testing in Dev; I would not recommend running them in production. As you point out in an earlier post, you'd be wiping out info that other processes could use for optimization.
April 12, 2011 at 8:48 am
Steve Thompson-454462 (4/12/2011)
Stefan Krzywicki (4/11/2011)
GilaMonster (4/11/2011)
Stefan Krzywicki (4/11/2011)
I'm likely using the wrong word. the metrics that are gathered by SQL Server to improve performance on subsequent runnings of a query. I knew the word at one point, but can't think of it now. The stored plan?No such thing.
The 'metrics' that it gathers based on column distribution are the column/index statistics, they're stored in the system tables and are just based on the column distribution, not previous query executions.
The cached plan is based on the optimiser's estimate of the costs. It doesn't take into account previous executions, just the table structure, indexes, column/index statistics.
If you're thinking about what's in sys.dm_exec_query_stats, that's just cumulative statistics for reporting. They're not reused by SQL in any way.
Huh. I wonder where I got that idea then. I could have sworn I'd attended a lecture or read an article that said the subsequent runnings of a query were faster because SQL Server stores reuse information. Maybe it was wishful thinking. : -) Thanks for setting me straight. I'd been worried that my test comparison speeds were off and therefore invalid.
Stefan,
One method I use for levelling the playing field when doing performance testing is to clear the cache and buffers prior to running my code. Especially, if I'm running my proc over and over again (with little tweaks in between) on my dev box, where there aren't a lot of competing queries vying for resources, it can be hard to tell whether a query ran fast becuase I'm really that good a coder or whether the result set was already cached in memory from a prior run.
DBCC FREEPROCCACHE
This command will release any cached plan, forcing the proc to recompile.
DBCC DROPCLEANBUFFERS
This command will clean the data cache to ensure that your result set doesn't still live in memory. You can use the CHECKPOINT command to clean the dirty buffers first.
Again, these commands can be useful while testing in Dev; I would not recommend running them in production. As you point out in an earlier post, you'd be wiping out info that other processes could use for optimization.
Thanks, if I ever get the chance to develop on a dev box I'll probably use these. As it is I'm developing on the Production machine.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 12, 2011 at 8:59 am
David Burrows (4/7/2011)
Dave Ballantyne (4/6/2011)
Any other threadzian going to be at sqlbits ?Yarp, Saturday.
Good to meet you on saturday, it was a fun weekend 🙂
April 12, 2011 at 9:33 am
Speaking of QotD, I thought up a nice followup for that question on page checksums. On when TDE is applied to a page.
Now, when am I going to find 5 minutes to write it up....
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 12, 2011 at 9:54 am
Dave Ballantyne (4/12/2011)
David Burrows (4/7/2011)
Dave Ballantyne (4/6/2011)
Any other threadzian going to be at sqlbits ?Yarp, Saturday.
Good to meet you on saturday, it was a fun weekend 🙂
And you too 🙂
It was good even learnt something :w00t:
Far away is close at hand in the images of elsewhere.
Anon.
April 12, 2011 at 10:21 am
WayneS (4/12/2011)
Steve Jones - SSC Editor (4/12/2011)
Stefan Krzywicki (4/12/2011)
WayneS (4/12/2011)
Stefan Krzywicki (4/12/2011)
I see SanDroid has started digging again...Where?
Still on your QOTD. bottom of the page and then on the next page.
I love when Gail is correcting someone about how the question is correct and he comes on to tell her "As I see you and others have noticed, there are several things wrong with the question and answer", part of the answer being her blog post...
you cannot, and will not, win that argument. Best to let it go.
Ah geez... I thought he was done over there. Yep, I'm letting his remarks go, and after several PMs, even have blocked him from sending me any more.
Wow, his latest post... He's either unhinged, has no idea what he's talking about whatsoever or is the trolliest type of troll. Maybe a combination of all 3. It is a train wreck and you just have to look as you go by.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Viewing 15 posts - 25,621 through 25,635 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply