August 1, 2013 at 2:32 pm
Donnie Carvajal (8/1/2013)
Thanks again.Is the goal to get the actual number of rows and estimated number of rows to be equal?
Donnie
within the same order of magnitude or so, doesn't have to be exact.
the stats are used for deciding the best execution plan, and the optimizer uses them to produce the best plan it can in the microseconds it's allotted to do that in.
inserts/updates/deletes modify the real data, but not the statistics about the data.
Auto update of statistics requires 20% of the rows of the table + 500 more rows before SQL does it for you;
but on a big table a much smaller percentage of rows modified can adversely affect query plans;
20% of a MillionBillion Row table might not occur for a long time.
Lowell
August 1, 2013 at 2:32 pm
Donnie Carvajal (8/1/2013)
Thanks again.Is the goal to get the actual number of rows and estimated number of rows to be equal?
Donnie
An exact match is not necessary. In fact, it is probably difficult to get them exact across the board. The target is to get them close so that the optimizer can compile an efficient plan. The estimates for rows is done by using statistics and when they are out of synch the optimizer will not create a plan that is optimal for the actual data.
--edit--
fixed some grammar issues...probably created new ones in the process.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 1, 2013 at 2:34 pm
Looks like Lowell beat me to a response again. At least his more detailed and earlier than mine. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 1, 2013 at 2:43 pm
I've updated the statistics on all of the underlying tables and the estimated rows are at 207077. Any other suggestions? I've attached the new .sqlplan.
BTW, all of these views are made up of joins on other views. Is this bad practice?
Thanks again,
Donnie
August 1, 2013 at 2:47 pm
Donnie Carvajal (8/1/2013)
I've updated the statistics on all of the underlying tables and the estimated rows are at 207077. Any other suggestions? I've attached the new .sqlplan.BTW, all of these views are made up of joins on other views. Is this bad practice?
Thanks again,
Donnie
Generally it is not a great idea to nest views on top of views. You can end up with really bad performance issues. I would recommend using Lowell's example of updating stats on all the tables in your db.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 1, 2013 at 2:58 pm
Would you suggest adding the Update Statistics Task to my maintenance plan that backs up the full database every night?
August 1, 2013 at 3:07 pm
Donnie Carvajal (8/1/2013)
Would you suggest adding the Update Statistics Task to my maintenance plan that backs up the full database every night?
That may be a waste of effort to do nightly. Especially since you are on a non-production instance. Generally speaking you need to find what kind of frequency works for your system. If you do lots and lots of insert/deletes daily may not be frequent enough. If on the other hand your data is fairly static updating stats daily may be more frequent than needed. There is no magic right answer here.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 1, 2013 at 3:41 pm
I checked the execution plan on the production server as well and I am seeing close to the same values for this query.
The full database statistics updates have finished and I am still seeing estimated rows at 206975. Any other suggestions? See attached for latest plan.
Donnie
August 1, 2013 at 3:49 pm
Donnie Carvajal (8/1/2013)
I checked the execution plan on the production server as well and I am seeing close to the same values for this query.The full database statistics updates have finished and I am still seeing estimated rows at 206975. Any other suggestions? See attached for latest plan.
Donnie
"Un-nest" your views. Nested views are generally horrible for performance. I suspect this is what you are running into. Try putting together the same query by un-nesting your views and see what the plan looks like at that point.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply