Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.
SQL server Memory Grants and High Row Estimates
Dear all,
reading this :
http://www.brentozar.com/archive/2013/08/query-plans-what-happens-when-row-estimates-get-high/
she said :
"Here’s the general shape of our plan. Just note that the bars connecting operators are narrow– SQL Server has pretty low (and accurate) estimates of how much data will be flowing through these pipes:""
"The plan estimated cost is 1.1 and it estimates it’ll bring back 229 rows. In my test environment it takes 16 ms to run and does several thousand logical reads across the many tables it joins. This means the query optimizer didn’t consider every possible plan, but hey, our runtime was pretty darn good anyway."
how can she know "This means the query optimizer didn’t consider every possible plan, but hey, our runtime was pretty darn good anyway" ?
reading this :
http://www.brentozar.com/archive/2013/08/query-plans-what-happens-when-row-estimates-get-high/
she said :
"Here’s the general shape of our plan. Just note that the bars connecting operators are narrow– SQL Server has pretty low (and accurate) estimates of how much data will be flowing through these pipes:""
"The plan estimated cost is 1.1 and it estimates it’ll bring back 229 rows. In my test environment it takes 16 ms to run and does several thousand logical reads across the many tables it joins. This means the query optimizer didn’t consider every possible plan, but hey, our runtime was pretty darn good anyway."
how can she know "This means the query optimizer didn’t consider every possible plan, but hey, our runtime was pretty darn good anyway" ?
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
yeah, got it
"Now, this query is already a little troubled. It has a lot of joins, including outer joins. It shows an optimization timeout in the plan."
can you tell which operator told her the timeout problem ? which the optimizer give up the planning ?
"It loaded the the temp tables up in memory turn by turn and then had to go through a long, painful process of reading from them. It did this three times, in sequence."
why 3 times? I don't get it !
if I recall correctly, if we see spool operator we need to add index of it or increase memory for the SQL server ?
basically can't see why just add a function to the join will make the query START to do a lot of row by row operation but not the one without function, why ?
"TSQL anti-patterns."
what is that ? no RBAR programming and use set-base operation ? anything else ?
"Now, this query is already a little troubled. It has a lot of joins, including outer joins. It shows an optimization timeout in the plan."
can you tell which operator told her the timeout problem ? which the optimizer give up the planning ?
"It loaded the the temp tables up in memory turn by turn and then had to go through a long, painful process of reading from them. It did this three times, in sequence."
why 3 times? I don't get it !
if I recall correctly, if we see spool operator we need to add index of it or increase memory for the SQL server ?
basically can't see why just add a function to the join will make the query START to do a lot of row by row operation but not the one without function, why ?
"TSQL anti-patterns."
what is that ? no RBAR programming and use set-base operation ? anything else ?
so in the link, the second one make the optimizer overestimated is because the join column has function (RTRIM()) on it? so it is not because of the RBAR problem, right?
so this RTRIM: Returns a character string after truncating all trailing blanks, can't apply to join column and just because of this ? not because the join column with function can't be index?
so this RTRIM: Returns a character string after truncating all trailing blanks, can't apply to join column and just because of this ? not because the join column with function can't be index?
I think that is because some certain SQL own functions (like CAST CONVERT and others) used in UDF's or directly in SP queries in WHERE or JOINs for that matter can cause costly scans instead of index seeks and lot of details about that can be found at links below:
http://blogs.msdn.com/b/turgays/archive/2013/09/11/applying-functions-on-where-clause-columns-might-cause-serious-performance-problems.aspx
https://www.mssqltips.com/sqlservertip/1236/avoid-sql-server-functions-in-the-where-clause-for-performance/
http://blog.sqlauthority.com/2013/03/12/sql-server-avoid-using-function-in-where-clause-scan-to-seek/
https://www.sqlskills.com/blogs/jonathan/implicit-conversions-that-cause-index-scans/
http://blogs.msdn.com/b/turgays/archive/2013/09/11/applying-functions-on-where-clause-columns-might-cause-serious-performance-problems.aspx
https://www.mssqltips.com/sqlservertip/1236/avoid-sql-server-functions-in-the-where-clause-for-performance/
http://blog.sqlauthority.com/2013/03/12/sql-server-avoid-using-function-in-where-clause-scan-to-seek/
https://www.sqlskills.com/blogs/jonathan/implicit-conversions-that-cause-index-scans/
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
"WHERE or JOINs for that matter can cause costly scans instead of index seeks and lot of details about that can be found at links below:"
so you don't do this and it is 100% sure that we can't include any function (any exception ? ) can't be use on ANY column in the join and where clause ?
so you don't do this and it is 100% sure that we can't include any function (any exception ? ) can't be use on ANY column in the join and where clause ?
"The plan estimated cost is 1.1 and it estimates it’ll bring back 229 rows. In my test environment it takes 16 ms to run and does several thousand logical reads across the many tables it joins."
She was talking about the Display Estimated Execution Plan against the Include Actual Execution Plan. Like the name says, the first option is only an estimation so we can't always trust on that.
She was talking about the Display Estimated Execution Plan against the Include Actual Execution Plan. Like the name says, the first option is only an estimation so we can't always trust on that.
what I worrying is :
"This means the query optimizer didn’t consider every possible plan"
how can she make this conclude that because of that SQL sever don't consider every possible plan ! this is the real thing.
or here just demo the function on any of the join column can cause SQL server OVER estimated the number of ROW it process and also it will force RBAR operation, so do not use function on join and where column?
"Because SQL Server had to do a lot of heavy row-by-row comparisons and wasn’t sure exactly how many might feed out of each of them, it decided to build “spool” temporary tables behind the scenes. It loaded the the temp tables up in memory turn by turn and then had to go through a long, painful process of reading from them. It did this three times, in sequence"
how can she know there are 3 times in sequence ?
"This means the query optimizer didn’t consider every possible plan"
how can she make this conclude that because of that SQL sever don't consider every possible plan ! this is the real thing.
or here just demo the function on any of the join column can cause SQL server OVER estimated the number of ROW it process and also it will force RBAR operation, so do not use function on join and where column?
"Because SQL Server had to do a lot of heavy row-by-row comparisons and wasn’t sure exactly how many might feed out of each of them, it decided to build “spool” temporary tables behind the scenes. It loaded the the temp tables up in memory turn by turn and then had to go through a long, painful process of reading from them. It did this three times, in sequence"
how can she know there are 3 times in sequence ?
how can she know there are 3 times in sequence ?Check her article again and in the figure just before she wrote those line you can see 3 nested loops. Is about that she was talking about.
I think you are talking about the 2 x picture above it, which above the table spool operator, right?
Well, the picture exactly before the nested loops are covered by the Table Pool information so yes, to be exact is the picture even before that one :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008
From novice to tech pro — start learning today.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with premium.
Start your 7-day free trial.
how can she know "This means the query optimizer didn’t consider every possible plan, but hey, our runtime was pretty darn good anyway" ?
>>
(my) answer to that would be (as well):
16ms (or milliseconds) runtime is pretty darn good no matter what plan and how many logical reads SQL choses - isn't it?
I think thats' what they refer to - I mean is fast regardless SQL consider or not each and any plan and choosing the best - maybe the fastes of all would be 12 (or lets say 9!!) milliseconds comparing to 16ms - would you "feel" that diference? I won't - and pleas don't consider percentage wise but AS IS in milliseconds.