April 11, 2011 at 12:29 pm
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.
--------------------------------------
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 11, 2011 at 12:30 pm
GilaMonster (4/11/2011)
Stefan Krzywicki (4/11/2011)
GilaMonster (4/11/2011)
Ok, had enough complaints about the lizard. Let's see if this is better.People complained about the lizard?
Yes, I have a bunch of PMs on the topic. Not all nice.
I'm honestly shocked by this. What does it matter what avatar someone else uses to you? And to actually go send a PM complaining about it? I think that I'd leave it just for spite at that point!
Edit: I would expect people to object to mine more than that lizard.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 11, 2011 at 12:30 pm
GilaMonster (4/11/2011)
Stefan Krzywicki (4/11/2011)
GilaMonster (4/11/2011)
Ok, had enough complaints about the lizard. Let's see if this is better.People complained about the lizard?
Yes, I have a bunch of PMs on the topic. Not all nice.
Really! I liked the lizard. I'm surprised people would care enough to PM you. Not surprised they wouldn't be nice though. I frequently say this'd be a great world if it weren't for the people.
--------------------------------------
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 11, 2011 at 12:31 pm
Executions of the query after the first are faster because the pages are in memory and the plan is in cache. I generally just run queries more than once and ignore the first execution for timing and execution statistics.
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 11, 2011 at 12:35 pm
GilaMonster (4/11/2011)
Executions of the query after the first are faster because the pages are in memory and the plan is in cache. I generally just run queries more than once and ignore the first execution for timing and execution statistics.
Oh good, that's what I've been doing. Thanks again for setting me straight, now I can stop looking for how to fix something that doesn't exist. : -)
--------------------------------------
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 11, 2011 at 1:00 pm
Stefan Krzywicki (4/11/2011)
Can someone point me to one of the posts where the keywords to make a cursor run as fast as possible are listed? I searched, but couldn't find what I was looking for. Something about Fast_Forward and some others, but after reading BOL I can't figure out what the others should be.
I can't remember which posts it's in, but it hasn't changed for donkey's years:
-- ISO Syntax
declare K insensitive cursor for <select statement> for READ_ONLY
-- T-SQL Syntax (2005 onward)
declare K cursor LOCAL STATIC FORWARD_ONLY FAST_FORWARD for <select statement>
-- T-SQL Syntax (SQL 2000)
declare K cursor LOCAL STATIC FAST FORWARD for <select statement>
/* all of these are read only cursors, all of them operate off a local copy of the data (so are not
sensitive to changes made to the data after the cursor was opened), all of them are forward only,
all of them have scope local to the batch (except when passed out from a stored procedure); if you
want something different different keyword sets will apply.
A cursor that allows writing, scrolling backwards and forwards and jumping to beginning or end, and
which does see changes to the data that happen after it is opened, is declared like this */
-- T-SQL Syntax dynamic scrolling cursor for writing,
-- using optimistic locking (so writes may fail)
-- and YES, optimistic locking with cursors WAS available in SQL 2000
declare K cursor LOCAL SCROLL DYNAMIC OPTIMISTIC for <select statement> FOR UPDATE
--T-SQL Syntax dynamic scrolling cursor for writing,
-- take write locks on all rows read (writes don't fail)
declare K cursor LOCAL SCROLL DYNAMIC SCROLL_LOCKS for <select stmt> FOR UPDATE
-- neither of these last two is remotely efficient, they are very heavyweight
edit: layout - get rid of left to right scrolling
Tom
April 11, 2011 at 1:03 pm
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?
- 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 11, 2011 at 1:05 pm
GilaMonster (4/11/2011)
Executions of the query after the first are faster because the pages are in memory and the plan is in cache. I generally just run queries more than once and ignore the first execution for timing and execution statistics.
I do the same. Discard the first result unless you're on a system where the cache is under-resourced and has to be flushed too often because of memory pressure.
- 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 11, 2011 at 1:06 pm
Tom.Thomson (4/11/2011)
Stefan Krzywicki (4/11/2011)
Can someone point me to one of the posts where the keywords to make a cursor run as fast as possible are listed? I searched, but couldn't find what I was looking for. Something about Fast_Forward and some others, but after reading BOL I can't figure out what the others should be.I can't remember which posts it's in, but it hasn't changed for donkey's years:
-- ISO Syntaxdeclare K insensitive cursor for <select statement> for READ_ONLY
-- T-SQL Syntax (2005 onward)
declare K cursor LOCAL STATIC FORWARD_ONLY FAST_FORWARD for <select statement>
-- T-SQL Syntax (SQL 2000)
declare K cursor LOCAL STATIC FAST FORWARD for <select statement>
/* all of these are read only cursors, all of them operate off a local copy of the data (so are not sensitive to changes made to the data after the cursor was opened), all of them are forward only, all of them have scope local to the batch (except when passed out from a stored procedure); if you want something different different keyword sets will apply.
A cursor that allows writing, scrolling backwards and forwards and jumping to beginning or end, and which does see changes to the data that happen after it is opened, is declared like this */
-- T-SQL Syntax dynamic scrolling cursor for writing, using optimistic locking (so writes may fail)
-- and YES, optimistic locking with cursors WAS available in SQL 2000
declare K cursor LOCAL SCROLL DYNAMIC OPTIMISTIC for <select statement> FOR UPDATE
--T-SQL Syntax dynamic scrolling cursor for writing, take write locks on all rows read (writes don't fail)
declare K cursor LOCAL SCROLL DYNAMIC SCROLL_LOCKS for <select stmt> FOR UPDATE
-- neither of these last two is remotely efficient, they are very heavyweight
Thanks Tom. I'm using LOCAL FAST_FORWARD now. FAST_FORWARD implied FORWARD_ONLY and READ_ONLY. I'll have to see if adding STATIC to the mix makes a difference.
--------------------------------------
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 11, 2011 at 1:12 pm
Tom.Thomson (4/11/2011)
Stefan Krzywicki (4/11/2011)
Can someone point me to one of the posts where the keywords to make a cursor run as fast as possible are listed? I searched, but couldn't find what I was looking for. Something about Fast_Forward and some others, but after reading BOL I can't figure out what the others should be.I can't remember which posts it's in, but it hasn't changed for donkey's years:
-- ISO Syntaxdeclare K insensitive cursor for <select statement> for READ_ONLY
-- T-SQL Syntax (2005 onward)
declare K cursor LOCAL STATIC FORWARD_ONLY FAST_FORWARD for <select statement>
-- T-SQL Syntax (SQL 2000)
declare K cursor LOCAL STATIC FAST FORWARD for <select statement>
/* all of these are read only cursors, all of them operate off a local copy of the data (so are not sensitive to changes made to the data after the cursor was opened), all of them are forward only, all of them have scope local to the batch (except when passed out from a stored procedure); if you want something different different keyword sets will apply.
A cursor that allows writing, scrolling backwards and forwards and jumping to beginning or end, and which does see changes to the data that happen after it is opened, is declared like this */
-- T-SQL Syntax dynamic scrolling cursor for writing, using optimistic locking (so writes may fail)
-- and YES, optimistic locking with cursors WAS available in SQL 2000
declare K cursor LOCAL SCROLL DYNAMIC OPTIMISTIC for <select statement> FOR UPDATE
--T-SQL Syntax dynamic scrolling cursor for writing, take write locks on all rows read (writes don't fail)
declare K cursor LOCAL SCROLL DYNAMIC SCROLL_LOCKS for <select stmt> FOR UPDATE
-- neither of these last two is remotely efficient, they are very heavyweight
Just as a note, you don't need to use all of these at once. Fast_Forward automatically makes it Forward_Only, so you can skip that one, for example. (You can declare a Forward_Only, Fast_Forward in 2005 and beyond, you just don't need to.)
I've done tests, and Static and Fast_Forward are the two that make the biggest difference. Local vs Global matters for concurrency more than single-shot speed, but it can make a difference nonetheless.
- 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 11, 2011 at 1:14 pm
GSquared (4/11/2011)
Tom.Thomson (4/11/2011)
Stefan Krzywicki (4/11/2011)
Can someone point me to one of the posts where the keywords to make a cursor run as fast as possible are listed? I searched, but couldn't find what I was looking for. Something about Fast_Forward and some others, but after reading BOL I can't figure out what the others should be.I can't remember which posts it's in, but it hasn't changed for donkey's years:
-- ISO Syntaxdeclare K insensitive cursor for <select statement> for READ_ONLY
-- T-SQL Syntax (2005 onward)
declare K cursor LOCAL STATIC FORWARD_ONLY FAST_FORWARD for <select statement>
-- T-SQL Syntax (SQL 2000)
declare K cursor LOCAL STATIC FAST FORWARD for <select statement>
/* all of these are read only cursors, all of them operate off a local copy of the data (so are not sensitive to changes made to the data after the cursor was opened), all of them are forward only, all of them have scope local to the batch (except when passed out from a stored procedure); if you want something different different keyword sets will apply.
A cursor that allows writing, scrolling backwards and forwards and jumping to beginning or end, and which does see changes to the data that happen after it is opened, is declared like this */
-- T-SQL Syntax dynamic scrolling cursor for writing, using optimistic locking (so writes may fail)
-- and YES, optimistic locking with cursors WAS available in SQL 2000
declare K cursor LOCAL SCROLL DYNAMIC OPTIMISTIC for <select statement> FOR UPDATE
--T-SQL Syntax dynamic scrolling cursor for writing, take write locks on all rows read (writes don't fail)
declare K cursor LOCAL SCROLL DYNAMIC SCROLL_LOCKS for <select stmt> FOR UPDATE
-- neither of these last two is remotely efficient, they are very heavyweight
Just as a note, you don't need to use all of these at once. Fast_Forward automatically makes it Forward_Only, so you can skip that one, for example. (You can declare a Forward_Only, Fast_Forward in 2005 and beyond, you just don't need to.)
I've done tests, and Static and Fast_Forward are the two that make the biggest difference. Local vs Global matters for concurrency more than single-shot speed, but it can make a difference nonetheless.
So you'd suggest LOCAL STATIC FAST_FORWARD?
EDIT: Never Mind, Can't do FAST_FORWARD and STATIC on the same Cursor.
--------------------------------------
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 11, 2011 at 1:23 pm
Stefan Krzywicki (4/11/2011)
EDIT: Never Mind, Can't do FAST_FORWARD and STATIC on the same Cursor.
However LOCAL STATIC FORWARD_ONLY READ_ONLY works.
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 11, 2011 at 1:27 pm
GilaMonster (4/11/2011)
Ok, had enough complaints about the lizard. Let's see if this is better.
:blink: Whaaa?
Good to see the dead hopeless romantic virgin isn't forgotten. I never could decide if killing him off made the show better or worse.
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
April 11, 2011 at 1:27 pm
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.
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 11, 2011 at 1:30 pm
Craig Farrell (4/11/2011)
GilaMonster (4/11/2011)
Ok, had enough complaints about the lizard. Let's see if this is better.:blink: Whaaa?
Good to see the dead hopeless romantic virgin again. I never could decide if killing him off made the show better or worse.
I liked Marcus. One of my favourite characters. His irrelevant sense of humour was awesome.
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
Viewing 15 posts - 25,576 through 25,590 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply