Execution plan for view
in [Programming SQL]
|
Prev: SQLCMD NUMERIC FORMAT ERROR
Next: DBMail Problems
From: OceanDeep via SQLMonster.com on 24 Mar 2010 11:39 We are using SQL 2005/2008 std. I have a view that joins and self joins between two tables with various join criteria and the criteria is static. Once the view is created, we have a query that will join the view with another big table and the where clause has two parameters which are based on the big table. When this query runs with various values in the two parameters, I believe the execution plan will be cached based on the new feature, parameter sniffing in SQL 2005/2008. Is this correct? Furthermore, say a third parameter is introduced in this query, obviously the optimizer will need to redo the execution plan. My another question is that would SQL keeps two execution plans, one for the two parameter one and one for the three parameter one assuming memory resouce is plenty and these two queries are run often? Keep in mind that the query mentioned above is not a stored procedure. Our web application calls the query whenever a user uses that page on our web application. od -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/201003/1
From: Uri Dimant on 24 Mar 2010 11:53 Hi You can check it out? DBCC FREEPROCCACHE GO SELECT * FROM View WHERE col= <value> GO SELECT * FROM View WHERE col= <another value> GO DECLARE @i int SER @i = 56004 SELECT * FROM View WHERE col= @i GO select stats.execution_count AS exec_count, p.size_in_bytes as [size], [sql].[text] as [plan_text] from sys.dm_exec_cached_plans p outer apply sys.dm_exec_sql_text (p.plan_handle) sql join sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handle GO "OceanDeep via SQLMonster.com" <u46587(a)uwe> wrote in message news:a57e274bb4cea(a)uwe... > We are using SQL 2005/2008 std. I have a view that joins and self joins > between two tables with various join criteria and the criteria is static. > Once the view is created, we have a query that will join the view with > another big table and the where clause has two parameters which are based > on > the big table. When this query runs with various values in the two > parameters, I believe the execution plan will be cached based on the new > feature, parameter sniffing in SQL 2005/2008. Is this correct? > > Furthermore, say a third parameter is introduced in this query, obviously > the > optimizer will need to redo the execution plan. My another question is > that > would SQL keeps two execution plans, one for the two parameter one and one > for the three parameter one assuming memory resouce is plenty and these > two > queries are run often? > > Keep in mind that the query mentioned above is not a stored procedure. > Our > web application calls the query whenever a user uses that page on our web > application. > > od > > -- > Message posted via SQLMonster.com > http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/201003/1 >
From: Tibor Karaszi on 24 Mar 2010 12:40 A view doesn't take parameters. So, we need to know more about exactly it is you are doing. Are you for instance using ADO.-NET and parameterize your queries? Or something else? Seen from SQL Server's viewpoint, there exist only three things: Literals, as in .... WHERE col = 23 Variables, as in DECLARE @v int = 23 ....WHERE col = @v .... And parameters to stored procedures and functions. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "OceanDeep via SQLMonster.com" <u46587(a)uwe> wrote in message news:a57e274bb4cea(a)uwe... We are using SQL 2005/2008 std. I have a view that joins and self joins between two tables with various join criteria and the criteria is static. Once the view is created, we have a query that will join the view with another big table and the where clause has two parameters which are based on the big table. When this query runs with various values in the two parameters, I believe the execution plan will be cached based on the new feature, parameter sniffing in SQL 2005/2008. Is this correct? Furthermore, say a third parameter is introduced in this query, obviously the optimizer will need to redo the execution plan. My another question is that would SQL keeps two execution plans, one for the two parameter one and one for the three parameter one assuming memory resouce is plenty and these two queries are run often? Keep in mind that the query mentioned above is not a stored procedure. Our web application calls the query whenever a user uses that page on our web application. od -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/201003/1
From: OceanDeep via SQLMonster.com on 24 Mar 2010 13:06 Tibor, Thank for the reply. My wording may be a bit confusing but as mentioned in my post, the view is static. Our web application uses that view and joins another big tables so parameterizing is done in the code in the web application. When SQL receives the query, the parameter values (literals) should be in place already as in your first example. It is that values will vary when the query is run. od Tibor Karaszi wrote: >A view doesn't take parameters. So, we need to know more about exactly it is you are doing. Are you for instance using ADO.-NET and parameterize your queries? Or something else? Seen from SQL Server's viewpoint, there exist only three things: > >Literals, as in >... WHERE col = 23 > >Variables, as in >DECLARE @v int = 23 >...WHERE col = @v > >... And parameters to stored procedures and functions. > > We are using SQL 2005/2008 std. I have a view that joins and self joins > between two tables with various join criteria and the criteria is static. > Once the view is created, we have a query that will join the view with > another big table and the where clause has two parameters which are based on > the big table. When this query runs with various values in the two > parameters, I believe the execution plan will be cached based on the new > feature, parameter sniffing in SQL 2005/2008. Is this correct? > > Furthermore, say a third parameter is introduced in this query, obviously the > optimizer will need to redo the execution plan. My another question is that > would SQL keeps two execution plans, one for the two parameter one and one > for the three parameter one assuming memory resouce is plenty and these two > queries are run often? > > Keep in mind that the query mentioned above is not a stored procedure. Our > web application calls the query whenever a user uses that page on our web > application. > > od > > -- > Message posted via SQLMonster.com > http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/201003/1 -- Message posted via http://www.sqlmonster.com
From: Tibor Karaszi on 24 Mar 2010 15:11
OK, that clear up things a bit. Next question would be if you program in a way so that the API parameterize the parameters, or if you concatenate the values in your code. I.e., are you using parameter objects or not? If you are, you will see RPC_Completed using sp_executesql events in a profiler trace. If not, then you see just the SQL submitted as SQL:BatchCompleted for instance. Below is just a bit related, but might shed some light on what I mean: http://sqlblog.com/blogs/tibor_karaszi/archive/2010/01/11/is-there-and-overhead-to-rpc-events.aspx -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "OceanDeep via SQLMonster.com" <u46587(a)uwe> wrote in message news:a57ee81484dd1(a)uwe... Tibor, Thank for the reply. My wording may be a bit confusing but as mentioned in my post, the view is static. Our web application uses that view and joins another big tables so parameterizing is done in the code in the web application. When SQL receives the query, the parameter values (literals) should be in place already as in your first example. It is that values will vary when the query is run. od Tibor Karaszi wrote: >A view doesn't take parameters. So, we need to know more about exactly it is you are doing. Are you for instance using ADO.-NET and parameterize your queries? Or something else? Seen from SQL Server's viewpoint, there exist only three things: > >Literals, as in >... WHERE col = 23 > >Variables, as in >DECLARE @v int = 23 >...WHERE col = @v > >... And parameters to stored procedures and functions. > > We are using SQL 2005/2008 std. I have a view that joins and self joins > between two tables with various join criteria and the criteria is static. > Once the view is created, we have a query that will join the view with > another big table and the where clause has two parameters which are based on > the big table. When this query runs with various values in the two > parameters, I believe the execution plan will be cached based on the new > feature, parameter sniffing in SQL 2005/2008. Is this correct? > > Furthermore, say a third parameter is introduced in this query, obviously the > optimizer will need to redo the execution plan. My another question is that > would SQL keeps two execution plans, one for the two parameter one and one > for the three parameter one assuming memory resouce is plenty and these two > queries are run often? > > Keep in mind that the query mentioned above is not a stored procedure. Our > web application calls the query whenever a user uses that page on our web > application. > > od > > -- > Message posted via SQLMonster.com > http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/201003/1 -- Message posted via http://www.sqlmonster.com |