SQL Compilation And Execution Plan
Hi all,
I€™m having a test regarding to the image data type. The test program is written with sql native api and just update the image data type column, but I looked the SQL Compilations/sec and Batch Requests/sec counters in SQLServer:QL Statistics using Perfmon, both values are almost the same. It seemed whenever the stored procedure is called, SQLServer compiles it and makes execution plan again. But when I had a test without image data type, SQL Compilation/sec was 0. SQL version is Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) (Build 2600: Service Pack 2).
Is SQL server working the way expected or am I missing something?
View Complete Forum Thread with Replies
Related Forum Messages:
SSIS Package Compilation And Execution
I am wondering something, once we've created a job that executes a package at a given time interval, does that package get recompiled each time the job spins up and executes the package? Or is the package compiled once and then that compiled code is executed each run after the first run? What I'm seein is this; I have a package that reads data from flat text files and then dumps that data into the database. The package will take 3 minutes to execute when executing on a single file, but when it's looping through ~50 files, it will take ~30 minutes to execute, that is less than a minute per file. Why is this? Hopefully I'm just forgetting something and not setting a checkbox or radio button somewhere. The job is set up as an SSIS job, not as a command line job. Thanks in advance for any help you can give me. Wayne E. Pfeffer Sr. Systems Analyst Hutchinson Technolgy Inc.
View Replies !
Actual Execution Plan Vs Estimated Execution Plan
The benefit of the actual execution plan is that you can see the actual number of rows passing through each step - compared to the estimated number of rows.But what about the "cost percentages" ?I believe I've read somewhere that these percentages is still just an estimate and is not based on the real execution.Does anyone know this and preferable have a link to something that documents it?Thanks
View Replies !
Execution Plan
Which of the following does NOT cause the execution plan of a query to berecompiled ?- new column is added to a table accessed by a query OR- index used by a query has been dropped from the database OR- query perfoms a join to return data from multiple tables OR- significant amount of data in a table has been mofified
View Replies !
Execution Plan Of UDF
Hi,I have a table-valued user defined function (UDF) my_fnc.The execution of statement "select * from my_fnc" takes much longertime than runnig the code inside my_fnc (with necessary changes).What can be the reason?How can I see an execution plan used for UDF?Thanks a lotMartin
View Replies !
Bad Execution Plan
I'm new to SQL server but familiar enough with databases to know this doesn't seem right. Here's the situation: I have a table with real estate property information. There are about 650,000 rows in it. I have a nonclustered non-unique index on the city where the property is located. There are about 40 unique values in this index. I do a simple query like: SELECT city,address from propinfo where city= 'CARLSBAD'. The query will return about 4,000 rows. The problem is that the execution plan that it chooses is to do a full table scan. I.E. Even though there is an index on City, it chooses to look through 650,000 rows rather than do an index seek. Something sounds inefficient here. BTW, this happens in both SQL 7 and SQL 2000. Can anyone explain why this happens? I've got to think that SQL Server is more efficient here.
View Replies !
Execution Plan
Hello, I have been looking at the execution plan for a procedure call and the select, compute scalar, stream aggregates, constant scan, nested loops, asserts are all at 0% cost, the PK costs are 2% apart from a rogue 7% and a few 20%, tables scans are all at 23%. The query cost realtive to the batch is 100%. What does this all mean? I have put non-clustered indexes on all the table attributes that are involved in the select statements but this has made no difference, i am guessing this is because my tables are not heavily populated and i may have seen a difference if i had thousands of entries in the tables the select statements acted on, is this assumption correct? Does anyone else bother using the execution plan to tweak there DB or is it a negligible tool? Jill
View Replies !
Execution Plan
In sql server 2005 management studio where do I find the option to run the sql query in the query analyser and also show the execution plan? At present I see the option under Query menu which is "Display estimated Execution plan" which only shows the plan but does not execute the query. Thanks
View Replies !
Execution Plan
Does anyone know of a good way to copy the execution plan when using "Include Actual Execution Plan"? I often need to copy this and mail it. I know I can use PrintScreen button, but I need a more efficient way to do this. If I just could rightclick the execution plan and select "Copy" and get complete plan it would be great. Mladen? E 12°55'05.25" N 56°04'39.16"
View Replies !
Get Real XML Execution Plan
Hi,I want to access the real execution plan via my webapplication after I have executed an SQL statement. I know how to get the estimated execution plan:1 cmd.CommandText = "SET SHOWPLAN_XML ON";2 cmd.ExecuteNonQuery();3 4 cmd.CommandText = myStatement;5 SqlDataReader dataReader = cmd.ExecuteReader();6 7 String plan = String.Empty;8 9 while (dataReader.Read()) {10 plan += dataReader.GetSqlString(0).ToString();11 }12 13 cmd.CommandText = "SET SHOWPLAN_XML OFF";14 cmd.ExecuteNonQuery();I want do compare the estimated costs with the real costs of the same statement. If I change code line 1 an 13 to "SET STATISTICS XML [ON|OFF]" the string "plan" will contain the result of the submitted SELECT statement, but I just need to get the plan and not the result itself. Thanks in Advance,Dominik
View Replies !
Functions And Execution Plan
The cost of query with usage of functions is as same as that of withoutfunctionsIn the below code, the query cost of insert is 0.02% and two selectstatements costs same 0.04%Declare @t table(mydate datetime)Declare @i intset @i=1while @i<=5000Begininsert into @t values(getdate())set @i=@i+1EndSelect mydate from @tSelect convert(varchar,mydate,112) from @tBut I thought usage of convert function will take more query costWhat do you think of this?Madhivanan
View Replies !
Execution Plan Hinting
We've got as slightly unusual scenario happening whereby a statement ispassed to SQL which consists of two parts.BEGIN TRANSACTIONDELETE * FROM WhateverBULK INSERT INTO Whatever...(etc)COMMIT TRANSACTIONThe first is a deletion of the data and the second is the bulk insertof replacement data into that table. The error that we see is aviolation of the primary key (composite).The violation only happens if we run both processes together. If we runone, then the other, it works fine. If we set a line by line insert, itworks fine.My suspicion is that the execution plan that is being run is mostlikely working the two parts in parallel and that the records stillexist at the point that the insert is happening. Truncate is not anoption. The bulk insert was added for performance reasons. There is anoption of trying the bulk insert, and if that fails, do the line byline insert, but it's far from ideal.I think we can probably wrap this into two individual transactionswithin the one statement as follows :BEGIN TRANSACTIONDELETE * FROM WhateverCOMMIT TRANSACTIONBEGIN TRANSACTIONBULK INSERT INTO Whatever...(etc)COMMIT TRANSACTIONWill this give sufficient hint to SQL about the order it processes itso that it completes as we intend and not as it sees being the mostefficient method ?Or, is there a better approach to this ?I've seen that some hints can be passed to SQL for optimizing, but myunderstanding was that it was always better to trust the optimiser andre-work the query as needed.With the server having two processors, is it feasible that one is doingone part and the other processor the other part in parallel ? Willtelling it to use a single processor be worthwhile looking at ? MAXDOP1 ?Finally, I'd imagine that the insert is quicker to process than thedeletion. Is this correct ?ThanksRyan
View Replies !
Stuck Execution Plan?
Using SQL Server 2000 SP4.There is a relatively complex stored procedure that usually completes inless than 20 seconds. Occasionally it times out after 180 seconds. The SPis called via ADO 2.8, using adCmdStoredProc command type. If I useProfiler to capture the EXEC that ADO sends to run the procedure, and runthat from QA, the procedure completes in less than 20 seconds as it should.The procedure is created WITH RECOMPILE. One additional twist is thatsp_setapprole is called from the client before running the procedure inquestion. This may be irrelevant, because even if I include the samesp_setapprole call when running the procedure from QA, it still executesquickly, and even if I comment out the call to sp_setapprole in the clientcode, the proc still times out when run from the client.The only thing that fixes it, at least for a day or two, is DBCCFREEPROCCACHE. So it appears that a bad plan is somehow stuck in memory andis only used when the procedure is called from the client app, and is notflushed even though the procedure was created WITH RECOMPILE.Other than scheduling the DBCC call to run every night, is there anythingelse I could try to get this resolved? Thanks.--(remove a 9 to reply by email)
View Replies !
SQL 6.5 Query Execution Plan .
Hello , I wanted to know whether we have an execution plan enabled in SQL 6.5 as we have it in SQL 7.0 and SQL 2000 . I.e when we execute a query and if we enable ' show execution plan 'then it creates a map and shows the vital statistics . If that is available on SQL 6.5 then i am missing that tool . How can i have it installed on my SQL 6.5 server ?? Thanks.
View Replies !
Execution Plan Analysis
Hi, I want to know how to analyze query execution plan for complex queries and what information is useful from that for improving the performance. I have gone through details in some sites like www.like sql-performance.com (http://www.sql-server-performance.com/query_execution_plan_analysis.asp), where it was more generic. I want more info regarding this. Can any one tell about the resources for this or do you have any white papers or documents, which you can share with me. Thanks in advance, sekhar
View Replies !
Execution Plan Different For = And &<&> Operators
Hi , when operator = then index SEEK operator <> then index SCAN Is normal ? Example SELECT * FROM dbo.Batch WHERE (Status = 'Batch Completed') (1 row(s) affected) StmtText --------------------------------------------------------------------------------------------------------------------------------- |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([PriceAvisPr].[dbo].[Batch])) |--Index Seek(OBJECT:([PriceAvisPr].[dbo].[Batch].[IX_Batch]), SEEK:([Batch].[Status]='Batch Completed') ORDERED FORWARD) StmtText --------------------------------------------------------------------------------- SELECT * FROM dbo.Batch WHERE (Status <> 'Batch Completed') (1 row(s) affected) StmtText ------------------------------------------------------------------------------------------------------------------------ |--Clustered Index Scan(OBJECT:([PriceAvisPr].[dbo].[Batch].[PK_Batch]), WHERE:([Batch].[Status]<>'Batch Completed'))
View Replies !
Execution Plan Explanation
Can someone explain to me the difference between the thick lines vs. thin lines represented in the execution plans for various queries? Also, what is meant by "Estimated Cost"? Thank you, Michelle Turner turner_michelle@bah.com
View Replies !
Execution Plan Explanation
Can someone explain to me the difference between the thick lines vs. thin lines represented in the execution plans for various queries? Also, what is meant by "Estimated Cost"? Thank you, Michelle Turner turner_michelle@bah.com
View Replies !
Execution Plan - Confusing
Hello all. created a simple table test name char(10) no numeric(2) inserted some records. created one index test_name_idx for name created one index test_no_idx for no I executed a query say ------------------------ select * from test Execution plan shows - i/o cost - .0375 CPU cost - .0000086 Then.... I executed a query say ----------------------- select name from test Query analyser picked the right index. and the execution plan shows i/o cost - .0375 CPU cost - .0000086 The i/o cost is more and the cpu cost is more sometime and less or zero simetime. Will somebody explain how i/o cost and the cpu cost and the bookmark values should be calculated or take in to consideartion. or Is there any rules to follow like the cost should not exeed a certain limit or so..? or Is there any expalnation given by microsoft anywhere....? Advance thanks MAK
View Replies !
Forcing New Execution Plan
I have a query that selects data from a view using a couple of where conditions. The query is using a bad execution plan. If I restore the database to another server then run the query (on the new server) it generates a different execution plan and the query runs in about 1/20th of the original time even though the machine is of a lower spec. How to I force then query to generate a new execution plan?
View Replies !
Graphical Execution Plan
Hi all, What does an operation involving 'Parallelism/Repartition streams' mean? Should the cost on it be lower or higher? Where can we find more help on what exactly each of them mean and the presence of which one improves performance, like for eg. is a clustered index seek better than a clustered index scan? Thanks in advance -Praveena
View Replies !
Execution Plan Mystery
I was hoping someone could shed some light on wierd situation i'm experiencing. I have the following query: select count(*) LeadCount from auto_leads al where received > dbo.GetDay(GetDate()) dbo.GetDay simply returns a smalldatetime value of today's date. Now I recently got thrown into a data mess and for some reason this query takes 8 seconds to run. Now the first thing I did was update the stats on the Received column of this auto_leads table. I re-run the query and I'm still getting 8 seconds. I look at the execution plan I can make figure out why this is happening. I then change the above query so the filter received > dbo.GetDay(GetDate()) is now just received > '5/31/2006' and the query comes back immediately. This doesn't make sense to me because the GetDay function is really simple and comes back immediately. I then try the following query to confirm it isn't a problem with the GetDay function: declare @Today DateTime set @Today = dbo.getday(GetDate()) select count(*) leads from auto_leads al join type_lead_status tls on (tls.type_lead_status_id = al.type_lead_status_id) where received > @Today Sure enough, the query came back immediately. Next thing to go through my mind is that the query execution plan has been cached by SQL Server using the execution plan from before I updated the stats on the received column. So I executed sp_recompile 'auto_leads' and tryed the original query again. Still taking 8-10 seconds to come back. So my question, is why when I remove the GetDay function call in my query filter is the query slow, as opposed to me just passing a variable into the query? Thanks! - James
View Replies !
Change Of Execution Plan
Hi, We migrated our database from SQL Server 2000 to Yukon last week. Now, when we run our application it has slowed down. We analyzed some stored procedure and they seems to have degarded. The execution plan has changed. Now, this looks like there's lot of work if we have to tune each query w.r.t the new execution plan. Our application has around 4000 stored procs. Is anyone aware of some generic pattern or solution such that these exection plans problem can resolved? Also, does the new execution plan ensure that the once we tune stored procs will perform better than SQL Server 2000. Need help on this, otherwise it seems we might have to move back to 2000. Thanks in Advance Ritesh
View Replies !
Execution Plan Question
I'm new to sql server 2005 and was reviewing the execution plan on one of my queries. I have a query that selects about 62,000 rows from a table of about 20 million I see there is a index seek indicated but further down the execution plan I see that a large percent is being assigned to a RID LOOKUP on the same table. Should I be concerned with this and if so, what would you recommend I do to correct it?
View Replies !
Execution Plan Degradation
Hi all, I am experiencing performance problems with one of my stored procedures. When the stored procedure is first compiled an executed, it behaves as expected (it usually takes 1 or 2 seconds to complete). But its performace it is degradated, so in 1 day, it usually takes 120 seconds to complete !!!. Once the stored procedure is compiled, its performance it is then the expected. It is a complex stored procedure with two integer parameters with only one select, but composed by multiple views and sub-queries. We have been trying to break the query into small pieces using temporary tables but without success. The SQL Profiler shows an unusual number of reads when it goes wrong (more than a million reads). I think the problem is in the execution plan. I know than compiling the stored procedure, the problem is fixed, but I do not know exactly when and why it starts to happen. The stored procedure is running under the following configuration: - Microsoft SQL Server Standard Edition (64-bit). - Version: 9.00.1399.06 - RAM 16 MB - 8 CPUs Anyone has any ideas or possible solutions? Thanks in advance, Carlos.
View Replies !
Store Proc Execution Plan
Is there anyway to force sql server to use the same execution plan? One of the sp for web page takes about 2 minutes to execute. Once it's executed through query analyser, it takes relatively less time. Is there any explanation for this?
View Replies !
The Worst Execution Plan For A Function
Hi, I use a SQL Server 2000 and I am trying to use function instead of a ad hoc query because I REALLY have to do it, but some issues must be solved. SQL Server is using a optimal execution plan when I run query and the worst execution plan for function. Here are the informations you might need to help me: Table DDL Code SnippetCREATE TABLE [IRES] ( [CPF_CNPJ] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [TIPO] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [IMPEDIMENTO] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [GRAU] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [DT_ULT_OCOR] [datetime] NULL ) ON [PRIMARY] GO indexes sp_helpindex ires Code Snippet name description keys IRES_CPF clustered located on PRIMARY CPF_CNPJ IRES_IMPED nonclustered located on PRIMARY IMPEDIMENTO PERFORMANCE INFO QUERY - it takes 2s to run and server makes an index seek on IRES_CPMF index Code Snippet select 'query ad-hoc',* from IRES where CPF_CNPJ = '000002230' and TIPO = 'F' EXECUTION PLAN Clustered Index Seek(OBJECT:([bmcires].[dbo].[IRES].[IRES_CPF]), SEEK:([IRES].[CPF_CNPJ]='000002230'), WHERE:([IRES].[TIPO]='F') ORDERED FORWARD) FUNCTION - it takes 20s to run and server makes a index scan on IRES_IMPED index!!!! Code Snippet CREATE FUNCTION fn_ires_crivo ( @cpfcnpj nvarchar(9) ) RETURNS table AS RETURN ( SELECT CPF_CNPJ, TIPO, IMPEDIMENTO, GRAU, DT_ULT_OCOR FROM dbo.ires WITH (INDEX (IRES_CPF) NOLOCK) WHERE CPF_CNPJ = @cpfcnpj and TIPO = 'F' ) go select cpf_cnpj from fn_ires_crivo('000000029') EXECUTION PLAN |--Filter(WHERE:([IRES].[TIPO]='F')) |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([bmcires].[dbo].[IRES])) |--Parallelism(Gather Streams) |--Index Scan(OBJECT:([bmcires].[dbo].[IRES].[IRES_IMPED]), WHERE:(Convert([IRES].[CPF_CNPJ])='000000029'))
View Replies !
Tune Execution Plan Of Expensive CLR UDF
I've create a bunch of views to expose a logical model of the underlying database of an application server. To enforce the security control, I've also created a CLR UDF to call the application server's API for security check and audit log. For example, we have a table, tblSecret, and the view, vwSecret, is, SELECT Id, ParentId, Description, SecretData FROM tblSecret WHERE udfExpensiveApiCall(Id) = 1 The udfExpensiveApiCall will return 1 if the current user is allowed to access the SecretData else 0. The CLR UDF call is very expensive in terms of execution time and resources required. Currently, there are millions rows in the tblSecret. My objective is to tune the view such that when the view is JOINed, the udfExpensiveApiCall will be called the least number of time. SELECT ParentId, SecertData FROM vwParent LEFT JOIN vwSecret ON vwSecret.ParentId = vwParent.ParentId WHERE vwParent.StartDate > '1/1/2008' AND vwSecret.Description LIKE '%WHATEVER%' Is there any way to specify the execution cost of the CLR UDF, udfExpensiveApiCall, such that the execution plan will call the UDF while it is absolutely necessary? Is there any query hint will help? Any recommendation? Thanks, Simon Chan
View Replies !
Understanding Estimated Execution Plan
When I generate an estimated execution plan from Management Studio, one of the things I often see in the execution plan generated is an 'Index Scan'. When I put my mouse over the 'Index Scan' graphic, I will see a window display with something called 'Output List' at the bottom of the window. Do I understand correctly that SQL Server will scan my index looking for values in each of the fields included in this output list? Thanks, Amos.
View Replies !
Execution Plan In Query Analyzer
This is probably a very stupid question. I have been out of the SQL Server arena for awhile and am now getting re acclimated. It was my understanding that using execution plan in query analyzer does not really execute the query against the query's database tables. Is this right? Tom.
View Replies !
OPTION(RECOMPILE) And Execution Plan
Hi, I use recompile option in SQL query to dynamic pass variable to optimizer. I verify explain plan with SET STATISTICS PROFILE ON and optimizer chose nested lookup ,ok. But if use Display Estimated Execution Plan (CTR+L) I€™ve get merge join. It€™s very confusing, some suggestion €¦? Use AdventureWorks go declare @StartOrderDate datetime set @StartOrderDate = '20040731' SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d WHERE h.SalesOrderID = d.SalesOrderId AND h.OrderDate >= @StartOrderDate OPTION(RECOMPILE); SQL2005SP2 djedgar
View Replies !
View Query Execution Plan
Hi, I am developing an application (VB) that should present a query estimated execution plan. Using the SQL Server Management Studio, I should execute the following commands to see the query's estimated execution plan: SET SHOWPLAN_XML ON go MyQuery go SET SHOWPLAN_XML OFF go The query is not executed. The result is the query execution plan. In my application, I call Connection.Execute to execute the 'SET SHOWPLAN_XML ON'. Then, I use a Resultset submit the query. The query is executed and the execution plan is not returned. Does anyone have any ideas? Thanks
View Replies !
Clearing Execution Plan Cache
Hi, We have an application which fetches data from a table which has approximately 1 million records. 1. Nearly 25 users will be using this application concurrently. 2. frequent updations will be done to the records in the geographyrolecurriculum table. 3. This table has 1 clustered index and 4 nonclustered index bounded to it. Problem Statement: 1. Application runs smoothly for 15 - 20 days and after that all the screens throws timeout errors. When i clear the sys.syscacheobjects its working fine again and screens get loaded quickly. Please tell me how clearing the syscacheobjects makes the execution fast? and is this the correct way to solve the timeout issue or is there any other alternative? 2. Will the stored procs timeout if the tempdb is full ? Thanks, Arunprasad
View Replies !
Reading Sql Server Execution Plan
Can anyone explain me simple language and easy to understand query execution plan. I am a fresher assigned to read and evaluate execution plan. i do not understand where is the problem. what percentage is considered as good sql and what percentage is considered as bad sql. how do i understand whether there is a problem in sql or joins or index or anything else. Please explain me step by step what should be considered and what recomenendation should i give for each problem.
View Replies !
SQL Server Guru: Execution Plan Issue?
As a developer, we always say "using a stored procedure, instead of a cliet side SQL statement, provides performance benefits". However, it seems it has not been true anymore since SQL Server 7.0. See SQL online "Execution Plan Caching and Reuse" at http://msdn.microsoft.com/library/default.asp?url=/nhp/default.asp?contentid=28000409 I am quite confused with the following questions: 1. it seems since SQL 7.0, a SQL statement in client side uses the existing execution plan as a stored procedure does. That means SP doesn't has much advantage over SQL statement in terms of performance. 2. It seems, a stored procedure is not always compled ONLY once. If a stored procedure is not used for a long time, it could be kicked out from procedure cashe. 3. In order to use an existing execution plan, it seems that we have to use the fully qualified identifier, such as SELECT * FROM Northwind.dbo.Employees instead of SELECT * FROM Employees However, I rarely see anyone uses these kind of fully qualified references for objects both in SQL statements and SP. For example, in the sample database pubs and NorthWind, they don't use the fully qualified expression. I only see the use of it in master database. I guess I might miss something in the issues above. I would like to get any explanation from SQL guru or anybody. Thanks a lot.
View Replies !
Tuning The Sort Step Of Execution Plan
hii got a query that takes about 14 minshere it isselect BDProduct.ProductCode,BDProduct.ProductName,SALTer ritory.TerritoryID,SALTerritory.TerritoryName,SALAccount.AccountID,S ALAccount.AccountName,sum(SalesNetFact.Qty2) as Quantity,sum(SalesNetFact.bonus) as Bonusfrom SalesNetFactinner join BDProducton BDProduct.ProductID=SalesNetFact.ProductIDinner join SALAccounton SALAccount.AccountID=SalesNetFact.AccountIDand SALAccount.BranchID=SalesNetFact.branchidinner join SALTerritoryon dbo.SALAccount.TerritoryID = dbo.SALTerritory.TerritoryIDand dbo.SALAccount.BranchID = dbo.SALTerritory.BranchIDgroup by BDProduct.ProductCode,BDProduct.ProductName,SALTerritory.TerritoryID,SALTerritory.TerritoryNa me,SALAccount.AccountID,SALAccount.AccountNamethe SalesNetFact table has BranchID,TransactionLineID as primary keythe BDProduct table has ProductID as primary keythe SALAccount table has AccountID,BranchID as primary keythe SALTerritory table has TerritoryID,BranchID as primary keyi have no other indices in any of these tablesthe execution plan shows that the sort step takes 96% cost,that is themost expensive step,it is done after all the joining steps and beforethe group by stepfor the sort step:the estimated row count is 1552242,the argumentsare:ORDER BY [BDProduct].[ProductCode]asc,[SALTerritory].[TerritoryID] asc,[SALTerritory].[TerritoryName]asc,[SalesNetFact].[AccountID] asc,[SALAccount].[AccountID] asc)any ideas about how to improve this sort step
View Replies !
Uniqueidentifier Causing Strange Execution Plan
Can anyone help me with this strange problem please?I have a stored procedure, with a parameter defined as auniqueidentifier. The procedure does a select with a number of joins,and filters within the Where clause using this parameter.(@orderHeader_iduniqueidentifier)SELECT*FROM originalOrderHeader oohINNER JOIN originalOrderLine oolON ooh.id = ool.idFULL OUTER JOIN orderLine olon ool.id = ol.idAND ool.productCode = ol.productCodewhere (ooh.id = @orderHeader_id)There is a clustered index on the id column of originalOrderHeader,and on id and productCode of both originalOrderLine and orderLine.These indexes are regularly rebuilt. The execution plan shows a seekagainst these indexes, but the estimated row count values are huge,and should be single figures.If I change the SP to accept the parameter as a varchar, and thenexplictly cast back to a uniqueidentifier in the where clause, the SPruns much, much quicker, the execution plan is doing far less work,and crucially the estimated row counts on the clustered index seeksare correct (single figures).Does anyone have any ideas what might be causing this?
View Replies !
Execution Plan Caching And Inconsistent Results
Hello Friends, This problem is bothering us for almost two weeks. We have a query as follows: SELECT field_locator_id, copy_code, text_data FROM kpmg_FIELD_DATA WHERE tax_return_id = 10 and FIELD_LOCATOR_ID in (9002, 9003, 9004, 9005, 9006, 9007, 9008, 9009, 9010, 9011, 9012, 9013, 9014, 9015, 9016, 9017, 9018, 9019, 9020, 9021, 9022, 9023, 9024, 9025, 9026, 9027, 9028, 9029, 9030, 9031, 9032, 9033, 9034, 9035, 9036, 9037, 9038, 9039, 9040, 9041, 9042, 9043, 9044, 9045, 9046, 9047, 9048, 9049, 9050, 9051, 9052, 9053, 9054, 9055, 9056, 9057, 9058, 9059, 9060, 9061, 9062, 9063, 9064, 9065, 9066, 9067, 9068, 9069, 9070, 9071, 9072, 9073, 9074, 9075, 9076, 9077, 9078, 9079, 9080, 9081, 9082, 9083, 9084, 9085, 9086, 9087, 9088, 9089, 9090, 9091, 9092, 9093, 9094, 9095, 9096, 9097, 9098, 9099, 9100, 9101, 9102, 9103, 9104, 9105, 9106, 9107, 9108, 9109, 9110, 9111, 9112, 9113, 9114, 9115, 9116, 9117, 9118, 9119, 9120, 9121, 9122, 9123, 9124, 9125, 9126, 9127, 9128, 9129, 9130, 9131, 9132, 9133, 9134, 9135, 9136, 9137, 9138, 9139, 9140, 9141, 9142, 9143, 9144, 9145, 9146, 9147, 9148, 9149, 9150, 9151, 9152, 9153, 9154, 9155, 9156, 9157, 9158, 9159, 9160, 9161, 9162, 9163, 9164, 9165, 9166, 9167, 9168, 9169, 9170, 9171, 9172, 9173, 9174, 9175, 9176, 9177, 9178, 9179, 9180, 9181, 9182, 9183, 9184, 9185, 9186, 9187, 9188, 9189, 9190, 9191, 9192, 9193, 9194, 9195, 9196, 9197, 9198, 9199, 9200) In Query Analyser, when it is executed first time, it returns 22 records. I executed the same query again and again. After some 10 executions, it returns only 9 records. After some time, it reduces to 4 and then finally to 0 records. Now, if I just insert a SPACE in the query somewhere, it goes back to original state and returns 22 records. The same old story will get repeated now, if I repeatedly execute. After so much of research, I found out that it is purely because of 'Execution Plan Caching' of SQL Server 7.0. The caching technique implementation somehow doesn't work. First time execution takes little time and successive execution take lesser time than the first one. Surprisingly, if I replace the IN clause with the BETWEEN clause it works perfectly by giving consistent resultset all the time. SELECT field_locator_id, copy_code, text_data FROM kpmg_FIELD_DATA WHERE tax_return_id = 2 and FIELD_LOCATOR_ID between 9002 and 9200 I guess SQL server could not handle that query because it is too big. I want to know... Is there a way to set the 'caching off' by any means? Have anyone faced such kind of situation, please let me know how to handle this situation? Any information to contact Microsoft? Thanks in advance... Nithi
View Replies !
Query Optimizer/Wrong Execution Plan
I have SQL 7.0 SP2 on NT 4.0 SP5. My database is 180GIG. 23 Tables. It has been up and running for 2 years without any problems. All of a sudden my queries have started taking a long time to run. The optimizer has decided that table scans are better than indexes. If I use query hints they work just fine, but I can't modify all of our code to make these changes. This is happening on all tables. Records counts are the in the same range they have always been. Statistics and indexes are all fine and current. Have dropped and rebuilt both. Has anybody else seen this behavior.
View Replies !
Dynamic SQL And Execution Plan And Very Slow. A Few Questions
Hey. I've a problem and I think I know the answer also but still want to confirm. We are using SQL 2000 and SSRS 2000. The problem is, we have custom reports which a customer can build and run. I wonder how one can write sp's for that. The way it's written right now is a dynamic select clause then a dynamic, from, a dynamic where, dynamic groupby all appended torgether and run by execute command. I know it'd dynamic SQL and execution plans and stuff will hurt me but someof these reports take forever. Is there anything that can be done to fasten these reports? And if the select will be dynamic and the where will be dynamic, does it make sense to even use a sp? Is it ever going to use the same execution plan? When I run DBCC memorystatus, procedure cache takes up most of this memory. Does the use of dynamic SQL explain that? Thank you for your time and effort in replying.
View Replies !
Execution Plan Caching For Prepared Statements
I think I have a problem with the execution plan caching in context of prepared statements. Please comment and advise. When caching a new execution plan SQL Server apparently takes into account the actual query parameters and the current situation of the SQL Server (open transactions, transaction locks, current workload and so on). That can cause the same prepared statement to be executed verry badly with other parameters. I am having trouble with a production system where some queries more or less suddenly start running extremly bad. The reason is an execution plan which might be optimal for some cases but is in general verry bad. Forcing a recompile of execution plans either by updating statistics or running sp_recompile solves the problem for some time. But after an unpredictable time the bad execution plan is comming back. Probably the good execution plan might also be reinstalled after som time but I cannot wait for this to happen. The factor between good and bad execution plan is about 160 and increasing (30ms vs. 5000ms). Please comment and advise, Thanks
View Replies !
|
TRACKER
