July 29, 2013 at 8:47 pm
I have been asked a question today that I am having a hard time providing an answer for and would like some assistance to understand the impacts on performance and get some guidance of how this works in 2008R2
There are 2 databases on one sql server. One database has a view that query’s the second database on the same sql server the view is a 'select * from table’ query and returns a large record set of around 20 million rows
My questions
1) How do the mechanics of this work in the sql engine level?
2) What impact is there on the destination database and on the originating database?
3) What happens when the data being referenced in the view changes in the originating db?
4) Are there any other considerations?
Any help to understand how this works at the engine level would be much appreciated
July 29, 2013 at 10:53 pm
There are 2 databases on one sql server. One database has a view that query’s the second database on the same sql server the view is a 'select * from table’ query and returns a large record set of around 20 million rows
My questions
1) How do the mechanics of this work in the sql engine level?
2) What impact is there on the destination database and on the originating database?
3) What happens when the data being referenced in the view changes in the originating db?
4) Are there any other considerations?
1. As long as cross database permissions are in place, and since this is on the same server, there is little difference than if the view was referencing a table within the same database. You would want to reference the objects by 3 part names.
2. 20 million records in the select statement will have a bigger impact on tempdb
3. Data changes, then your results change - just the same as if the view were to reference a table within the same database.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 30, 2013 at 12:19 am
As it is selecting 20 million records, its obvious that it effects the performance of other transactions on the server as the tempdb will be heavily loaded.
1. Make sure nolocks are maintained on the tables from where the data is getting selected
2. check the where clause is having the columns which are indexed
3. Check the query execution plan in the dev server for confirming there are no table scans(if table scans are there, then add indexes on the required columns)
July 30, 2013 at 2:05 am
Kalyan Boddupalli (7/30/2013)
1. Make sure nolocks are maintained on the tables from where the data is getting selected
Do you mean that the NOLOCK hint should be used? Have ye no shame!
The NOLOCK hint can be very important when running diagnostic queries in a live system, but it rarely is a good thing to put in application code, and absolutely not in a view definition!
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 30, 2013 at 2:09 am
My biggest concern is the use of SELECT *. This is a bad thing to put in a view definition (or in application code in general).
Else, there is not very many difference between an inter-database view and an intra-database view. The impact depends on indexes and how the view is used. The view definition may qualify 20 million rows, but that does not matter if the view is always queried with WHERE clauses which filter out a small subset by indexed columns.
Well, there is one difference to an intra-database view: you can't index it. Then again, you can't index a view with SELECT * anyway.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 30, 2013 at 2:42 pm
Erland Sommarskog (7/30/2013)
Kalyan Boddupalli (7/30/2013)
1. Make sure nolocks are maintained on the tables from where the data is getting selectedDo you mean that the NOLOCK hint should be used? Have ye no shame!
The NOLOCK hint can be very important when running diagnostic queries in a live system, but it rarely is a good thing to put in application code, and absolutely not in a view definition!
I agree here. There is little to no reason to make that recommendation.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 30, 2013 at 3:03 pm
Thanks for your assistance it is very helpful , you say that the view is created in tempdb and it can have a heavy impact ,so each time the view is accessed how is the view "rebuilt" does it leave the 20 million records in the tempdb and add extra records to the view or keep it in memory or is it recreated from scratch each time or maybe left in buffer if sufficient memory is available (where is the information contained in the view held and how is it processed?) , what I am trying to understand is the process that sql uses to retrieve the information/update the information /and return the information . Thanks again for your help in this
July 30, 2013 at 3:10 pm
DesmoShane (7/30/2013)
You say that the view is created in tempdb and it can have a heavy impact ,so each time the view is accessed how is the view "rebuilt" does it leave the 20 million records in the tempdb and add extra records to the view or keep it in memory or is it recreated from scratch each time
The person who said that was flat wrong.
A view is nothing but a macro. When SQL Server processes a query with a view, SQL Server expands the query in the view into the query, and then optimizes the resulting query. The view is never materialised as part of the process. However, depending on the resulting query, the query plan may include operators (spools, hashing, sorting etc) that use space in tempdb. But that has nothing to do with the fact that the query includes a view or that it spans multiple databases.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 30, 2013 at 3:21 pm
Thanks Erland excellent response thank you , so my understanding is ... the view contains the sql and because the view can be accessed so as to cause a very large operation to occur on it it "potentially" could have an impact if the resulting query on the view is poorly used .The actual view itself does not have any requirements for resources until called
E.G. select top 1 no problem at all , select * from is going to take a while and have heavy usage of tempdb .
July 30, 2013 at 3:30 pm
DesmoShane (7/30/2013)
E.G. select top 1 no problem at all , select * from is going to take a while and have heavy usage of tempdb .
Maybe. All depending on the view definition, SELECT TOP 1 without ORDER BY could be an expensive operation, although it is less likely. SELECT * without a WHERE clause will take more resources, but again, not necessarily in tempdb. If the query can be implemented with only streaming operators, that will not happen.
And, again, the fact that this is a view or that it spans multiple databases, have zero importance here. It's a query, plain and simple.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 30, 2013 at 3:36 pm
Thank you for your assistance you have been very helpful
July 30, 2013 at 3:45 pm
Thank you for your help it is much appreciated
July 30, 2013 at 4:02 pm
Erland Sommarskog (7/30/2013)
DesmoShane (7/30/2013)
You say that the view is created in tempdb and it can have a heavy impact ,so each time the view is accessed how is the view "rebuilt" does it leave the 20 million records in the tempdb and add extra records to the view or keep it in memory or is it recreated from scratch each timeThe person who said that was flat wrong.
A view is nothing but a macro. When SQL Server processes a query with a view, SQL Server expands the query in the view into the query, and then optimizes the resulting query. The view is never materialised as part of the process. However, depending on the resulting query, the query plan may include operators (spools, hashing, sorting etc) that use space in tempdb. But that has nothing to do with the fact that the query includes a view or that it spans multiple databases.
Nobody ever said the view is materialized nor that the full 20 million records are stored in tempdb. Selecting 20 million records and a select * however can have an impact on tempdb due to spooling, sorting, hash tables and spillover - far more than the impact on the user databases.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply