April 22, 2015 at 10:14 am
Hi
I have been looking at a SQL agent job that runs.
The issue is that it joins a lot of tables as well as a lot of views. If I then try to look into the views, these views also reference views.
Does anyone have any advice on how best to break it all down?
Using SET STATISTICS IO ON and SET STATISTICS TIME ON I can see that one of the tables shows Scan count 3169, logical reads 212116.
Is there a limit to the amount of scans or reads a table should be doing or is it all relative?
Thanks
Sam
April 22, 2015 at 10:48 am
Sam Garth (4/22/2015)
HiI have been looking at a SQL agent job that runs.
The issue is that it joins a lot of tables as well as a lot of views. If I then try to look into the views, these views also reference views.
Does anyone have any advice on how best to break it all down?
Using SET STATISTICS IO ON and SET STATISTICS TIME ON I can see that one of the tables shows Scan count 3169, logical reads 212116.
Is there a limit to the amount of scans or reads a table should be doing or is it all relative?
Thanks
Sam
Nested views is a recipe for horrible performance. It seems like it shouldn't be a big deal but the optimizer has a hard time finding a good execution plan with nested views.
For more detailed help please take a look at this article for what to post for performance problems. http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]
_______________________________________________________________
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/
April 22, 2015 at 2:14 pm
Nesting views is a really common code smell and should be avoided. It's best to write each query as a stand-alone entity and not attempt code reuse through views or other objects. It seems counter-intuitive, but that's how SQL Server works best.
As for the number of reads from any given table, it just depends on how much data there is and how it's being accessed. There isn't a formula that says X is bad and Y is good. Instead you need to explore the execution plan.
Basically, you need to read the books in my signature line down below.
----------------------------------------------------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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply