.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

Improper execution plan with scalar function

Posted By:      Posted Date: August 29, 2010    Points: 0   Category :Sql Server
 
Run the following sql in an empty db(in my case, sql 2008): create table t (id int) go declare @i int set @i = 1 while @i <= 1000 begin insert t values (@i) set @i = @i + 1 end go create function f(@i int) returns int as begin return (select count(*) - 10000+@i from t) end go Then run the following sql a, it's slow, used 6 seconds, yet sql b is very fast. Their execution plans are totally different. Can sql a run as fast as sql b? If not, why? -- sql a declare @i int set @i = 500 select * from t where id = (select dbo.f(@i)) -- sql b select * from t where id = (select dbo.f(500)) Thanks!


View Complete Post


More Related Resource Links

CPU killer when showing execution plan when having a missing index

  
Hi you all! Do you have problems with flickering and CPU getting high when you show the execution plan for a SQL Statement AND there is a missing index suggestion? See related connect id: https://connect.microsoft.com/SQLServer/feedback/details/360457/hovering-mouse-over-execution-plans-query-line-is-cpu-killer I really hope someone from Microsoft can take a look at this a give us a status update, over TWO years since I reported it and still an issue. Have you experienced the same? Please vote the bug up to "raise" the flag.Best regards Arild Røksund, Norwegian developer (SQL & .net), Omega AS (please Vote as helpful if helpful)

Execution Plan in Activity Monitor (Recent Expensive queries)

  
If we see the execution plan of a query in Activity Monitor > Recent Expensive Queries is it Estimated Execution Plan or Actual Execution Plan ?

Is the saved execution plan for a stored procedure really relevant?

  
The execution plan of a stored proc is saved for future use in the compilation process - but sometime in the future it could be irrelevant... for example: my assumption is that the heuristics of the optimizer are for example available indices and table size. So: 1. If an index is dropped then the execution plan can't be done... BUT when I checked this case, I created a simple stored proc with a simple select and a where clause, and ran it - the execution plan (which i assume is the saved one) contained an index seek. Now I dropped the index and got a table scan instead - so what happened? the saved execution plan (which was there from the time I run "create proc") was NOT performed unless the actual execution plan checks for existence of index (obviously 2. If an index is added then we'd have a better execution plan than the compiled one. BUT - when checking this.... before adding the index I had a table scan... after adding the index i still expected a table scan (because of the saved execution plan)... BUT i got an index scan - THIS LEADS ME TO THE CONCLUSION THAT after adding the index the proc was automatically recompiled by the sql server (I never read such a thing anywhere - so either i misundertand the entire theory or my conclusion is right). 3. Table size... first - is table size really  the optimizer heuristics? meaning for exa

Is the saved execution plan for a stored procedure really relevant?

  
The execution plan of a stored proc is saved for future use in the compilation process - but sometime in the future it could be irrelevant... for example: my assumption is that the heuristics of the optimizer are for example available indices and table size. So: 1. If an index is dropped then the execution plan can't be done... BUT when I checked this case, I created a simple stored proc with a simple select and a where clause, and ran it - the execution plan (which i assume is the saved one) contained an index seek. Now I dropped the index and got a table scan instead - so what happened? the saved execution plan (which was there from the time I run "create proc") was NOT performed unless the actual execution plan checks for existence of index (obviously 2. If an index is added then we'd have a better execution plan than the compiled one. BUT - when checking this.... before adding the index I had a table scan... after adding the index i still expected a table scan (because of the saved execution plan)... BUT i got an index scan - THIS LEADS ME TO THE CONCLUSION THAT after adding the index the proc was automatically recompiled by the sql server (I never read such a thing anywhere - so either i misundertand the entire theory or my conclusion is right). 3. Table size... first - is table size really  the optimizer heuristics? meaning for exa

Scalar Function Variable length return value

  
Would like to write a Scalar Function that returns a variable length string of spaces. Length being determined by an input parameter (int) Maybe I am missing the obvious but it seems impossible because you have to declare the size  of the varchar return variable I know there is a performance hit using scalar functions but we are generating a view that emulates an old fixed file format with lots of unused fields. So it will make the resultant view readable. So I am looking to do select goodfield1,fnSpace(5) as Unusedfield1,goodfield2,fnSpace(10)... from MyView thanks Bob

Query execution plan problem

  
Hi, I have encountered a problem with a query execution plan on MS SQL Server 2008. It is a simple query on a single table. The table has a primary key RNUM (number(10)) with a clustered index. The query is executed via ODBC using fast forward cursors and is constructed like this: select [field_list_here] from table_name where RNUM>@P and TYPE='A' order by RNUM. The field TYPE has 2 possible values and is not indexed. The table has about 2 000 000 rows of static data (only reads, no inserts and updates). For some time my query executes using the efficient query execution plan. Below a copy from Management Studio from an ad-hoc query: SELECT (0%) <- Clustered Index Seek  (100%) But after 2 days of executing other type of queries SQL Server starts to use other execution plan (live copy):                             Fetch query (0%) <- Clustered Index Seek [CWT_PrimaryKey] (0%)                            |                           \/ Fast forward (0%) <- Population quer

Execution plan help

  
When doing a query execution plan, I see the following: Clustered Index Scan Predicate [FinanceDW].[dbo].[fact_temp] [segment_key =2] which is Cost at 55% This is what im doing: where segment_key = 2 Any ideas what this means? an how I can improve it?

SQl 2008 execution plan problem

  
Hi We are in the middle of upgrading a PeopleSoft database from SQL 2000 to SQL 2008. After the upgrade a select from a view takes 8 min compared to the 1 second it took in SQL 2000. I can see that the execution plan is the problem. What is causing 2008 to be worse than 2000. Is there a patch to fix this 2008 problem or should we stick with 2000 for now. Fixing the system with new indexes and trouble shooting the query is out off the quetion as the people lost there faith in SQL as a system and is saying if this happens now it will keep on happening with other dev and to much time is spent on trouble shooting the SQL 2008 shortcomings.

SQl 2008 execution plan problem

  
Hi We are in the middle of upgrading a PeopleSoft database from SQL 2000 to SQL 2008. After the upgrade a select from a view takes 8 min compared to the 1 second it took in SQL 2000. I can see that the execution plan is the problem. What is causing 2008 to be worse than 2000. Is there a patch to fix this 2008 problem or should we stick with 2000 for now. Fixing the system with new indexes and trouble shooting the query is out off the quetion as the people lost there faith in SQL as a system and is saying if this happens now it will keep on happening with other dev and to much time is spent on trouble shooting the SQL 2008 shortcomings.

Save Execution plan option disabled

  
You cannot vote on your own post
0

Hi,

 

I am using Management studio. I want to save the Execution plan on a stored procedure, but the Save Execution plan option is disabled. Even on the File menu the Save Execution plan option is disabled. Is this some security issue or Is it because of Express addition. Is there any way to get around this problem.

Thanks in advance.

-Jaya


Save Execution plan option disabled

  

Hi,

 

I am using Management studio. I want to save the Execution plan on a stored procedure, but the Save Execution plan option is disabled. Even on the File menu the Save Execution plan option is disabled. Is this some security issue or Is it because of Express addition. Is there any way to get around this problem.

Thanks in advance.

-Jaya

Save Execution plan option disabled

  

Hi,

 

I am using Management studio. I want to save the Execution plan on a stored procedure, but the Save Execution plan option is disabled. Even on the File menu the Save Execution plan option is disabled. Is this some security issue or Is it because of Express addition. Is there any way to get around this problem.

Thanks in advance.

-Jaya


Table Spool takes up too much processing in execution plan

  

I have a query that is taking over 2 hours to run. When I look at the execution plan for a major segment of it, 75% of the cost is on three instances of "Table Spool" (25% each). What is "Table Spool" and how do you address it? The execution plan lists this as

 Table Spool
(Eager Spool)

There are actually 2 queries involved. One loops through a series of dates and calls the second one. All joining fields have indexes in their corresponding tables:

Calling Query:

    -- Process all quarters
        declare @lastQtrStart datetime
        declare @currentQtrStart datetime
        declare @currentQtrEnd datetime

        set @currentQtrStart = '1/1/2008'
        set @lastQtrStart = '1/1/2020'

        while @currentQtrStart < @LastQtrStart
            begin
                set @currentQtrEnd = dateadd(d , -1 , dateadd(MM , 3 , @currentQtrStart))
              

How to estimate "Minimum query plan threshold for considering queries for parallel execution(cost es

  

Hi,

We have 4 QUAD core processors and we have set max degree of parellelism to 4(as suggested by Microsoft). As of now we are using the default value for "Minimum query plan threshold for considering queries for parallel execution(cost estimate)".

Please suggest how to estimate the best value for for "Minimum query plan threshold for considering queries for parallel execution(cost estimate)"?

Thanks in advance.

 

 


How to Open Xml at Graphical Execution Plan?

  

This was working until a few days ago. I dont know if I change some config.

Exectuing:

SELECT *
FROM sys.dm_exec_query_stats as EQS
CROSS APPLY sys.dm_exec_sql_text(EQS.sql_handle)
CROSS APPLY sys.dm_exec_query_plan(EQS.plan_handle)
GO

Return a Xml "<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver......"

When I click at cell, I want to see Graphical Execution Plan, not the XML.


http://blog.fujiy.net/ - MCPD em .NET 2.0 MCTS em .NET 4

Calling scalar valued Function from SSIS OleDB Command Transformation

  

Hi There,

I need to call a function to calculate a value. This function accepts a varchar parameter and returns a boolean value. I need to call this function for each row in the dataflow task. I thought I would use an oledb command transformation and for some reason if I say..

'select functioname(?)' as the sqlcommand, it gives me an error message at the design time. In the input/output properties, I have mapped Param_0(external column) to an input column.

I get this erro.."syntax error, ermission violation or other non specific error". Can somebiody please suggest me what's wrong with this and how should I deal this.

Thanks a lot!! 


How to Open Xml at Graphical Execution Plan?

  

This was working until a few days ago. I dont know if I change some config.

Exectuing:

SELECT *
FROM sys.dm_exec_query_stats as EQS
CROSS APPLY sys.dm_exec_sql_text(EQS.sql_handle)
CROSS APPLY sys.dm_exec_query_plan(EQS.plan_handle)
GO

Return a Xml "<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver......"

When I click at cell, I want to see Graphical Execution Plan, not the XML.


http://blog.fujiy.net/ - MCPD em .NET 2.0 MCTS em .NET 4
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend