SQL Server 2012 :: How To Rearrange The Execution Plan Manually
Nov 27, 2014
I have 2 tables,
1. Need
2. NeedCategory
Need table has clusted index on needid column and NeedCategory have composite clustered index on needid and categoryid.
Now take a look on following query and execution plan for the query.
SELECT N.NeedId,N.NeedName,N.ProviderName
FROM dbo.Need N
JOIN dbo.NeedCategory NC
ON nc.NeedId = n.NeedId
WHERE IsActive=1
AND CategoryId= 2
ORDER BY NeedName
* Clustered index scan on need table is happens for Isactive= 1.
* Clustered index scan on needcategory table is happens for CategoryId=2
My question is,
1. Why scan happens before the join occurs? if it happens after join then the filter would be lighter. Even if optimizer chooses the scan to execute first.
2. Is there any chance to rearrange the execution plan manually?
View 6 Replies
ADVERTISEMENT
Nov 11, 2014
I'm new to using SQL Server. I've been asked to optimize a series of scripts that queries over 4 millions records. I've managed to add indexes and remove a cursor, which increased performance. Now when I run the execution plan, the only query that cost is a DELETE statement from the main table. It shows a SORT which cost 71%. The table has 2 columns and a unique index. Here is the current index:
ALTER TABLE [dbo].[Qry] ADD CONSTRAINT [Qry_PK] PRIMARY KEY NONCLUSTERED
(
[QryNum] ASC,
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Question: Will the SORT affect the overall performance? If so, is there anything I should change within the index that would speed up my query?
View 5 Replies
View Related
Apr 8, 2014
I want to find out more information about the execution plan. I saw Parallelism (Gather Streams) in the excution plan. In what situation we can see this icon?If in case if we need to avoid how can we avoid this?
View 5 Replies
View Related
Jul 4, 2014
We have got a query for fine tuning and it is using lot of CTE ,how can i check the execution plan of that?
CREATE VIEW Mercy
AS
with ADR
as
(
SELECT urpx.RoleID ,
urx.UserID
FROM [DBA].dbo.URPX WITH ( NOLOCK )
INNER JOIN [DBA].dbo.URX WITH ( NOLOCK ) ON urpx.RoleID = urx.RoleID
WHERE PermissionID = '1'
),
SDR
as
(
-- Collect the roles that a configured with Sales Team Create permission
-- This will include Sales Director , Suite Admin,
SELECT urpx.RoleID
FROM [DBA].dbo.URPX WITH ( NOLOCK )
INNER JOIN [DBA].dbo.URX WITH ( NOLOCK ) ON urpx.RoleID = urx.RoleID
LEFT OUTER JOIN ADR ON ADR.UserID = urx.UserID
WHERE ADR.RoleID IS NULL
AND PermissionID='2'
)
View 6 Replies
View Related
Sep 29, 2014
I have an execution plan that is huge, the pdf it generates if I print it is over 1000 pages. Is there a way to change the graphical plan into a table, so I can sort the %, and find the items that are taking the longest?
View 4 Replies
View Related
May 15, 2015
I have same query but when executed from different server use different plan. when it runs on QA box it is faster and when it runs on PRD it is slow.
Is it possible to force SQL Server to use QA plan by giving a hint?
View 2 Replies
View Related
Jul 21, 2014
know if there is any way out to run execution plan for parameterized queries?
As application is sending queries which are mostly parameterized in nature and values being used are very robust in nature, So i can not even make a guess.
View 1 Replies
View Related
Oct 30, 2014
I'm in the process of trying to optimize a stored procedure with many queries. The execution plan provides a missing non-clustered index on nearly every query, and they're all fairly similar. The only real difference between them are what's in the INCLUDE statement. The two key columns are listed in every missing index. Let's say each query is approximately 5% of the total batch and 90% of the queries all fall into the category I listed above. How should I go about creating the missing indexes? Create all of the missing indexes or create one generic one that has all the INCLUDE columns? Create a minimal index with just a few of the common INCLUDE columns?
Here's an example of what I'm talking about with the missing indexes:
/*
USE [DB]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[TABLE_1] ([COLUMN_A],[COLUMN_B])
INCLUDE ([C4ABCD],[C4ARTX],[C4ASTX],[C4ADNB],[C4AFNB],[C4BKVA])
GO
*/
/*
The Query Processor estimates that implementing the following index could improve the query cost by 99.9044%.
*/
/*
USE [DB]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[TABLE_1] ([COLUMN_A],[COLUMN_B])
INCLUDE ([C4ARTX],[C4ASTX],[C4ADNB],[C4CZST])
GO
*/
/*
The Query Processor estimates that implementing the following index could improve the query cost by 99.5418%.
*/
/*
USE [DB]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[TABLE_1] ([COLUMN_A],[COLUMN_B])
INCLUDE ([C4ABCD],[C4ARTX],[C4ASTX],[C4ADNB],[C4AFNB],[C4BKVA])
GO
*/
View 3 Replies
View Related
Jun 17, 2015
Is it possible to check query execution plan of a store procedure from create script (before creating it)?
Basically the developers want to know how a newly developed procedure will perform in production environment. Now, I don't want to create it in production for just checking the execution plan. However they've provided SQL script for the procedure. Now wondering is there any way to look at the execution plan for this procedure from the script provided?
View 1 Replies
View Related
Jul 7, 2006
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 1 Replies
View Related
Feb 22, 2008
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 6 Replies
View Related
May 29, 2004
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 7 Replies
View Related
Feb 6, 2004
My DTS Package work fine if I Execute it manually, but I need to do it automatically just after midnight. I defined my schedule and made sure the job was present in the SQL Server Agent>Jobs, but it fails and the Job History shows the following error:
DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: [Microsoft][ODBC Microsoft Access Driver] Cannot start your application. The workgroup information file is missing or opened exclusively by another user. Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 1901 (76D) Error string: [Microsoft][ODBC Microsoft Access Driver] Cannot start your application. The workgroup information file is missing or opened exclusively by another user. Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.
Help!!!
View 3 Replies
View Related
Jan 15, 2007
Hi
I am having a query
SELECT Dur1.rootId
FROM DurableEventTab Dur1
WHERE (Dur1.dev_ReferenceClusterRoot = 'iyrwd.52' )
AND Dur1.dev_Action = 'Order:Ordered')
AND (Dur1.dev_Active = 1) AND (Dur1.dev_PurgeState = 0)
AND (Dur1.dev_PartitionNumber = 0)
This table has a primary key : aribapk11
and the indexes on the dev_ReferenceClusterRoot,
dev_Action,dev_purgestate .
Now when I fire this query
the query execution plan is actaull doing a Clustered Index scan on the PK :aribaPK11 . What I was expecting was an index seek on the key defined on dev_referenceClusterRoot. Please not the index seek is the behaviour in sql server 2000.
Any idea what is going wrong ?
Clustered Index Scan(OBJECT:([typhoon1902].[dbo].[DurableEventTab].[AribaPK7] AS [Dur1]), WHERE:([typhoon1902].[dbo].[DurableEventTab].[dev_Active] as [Dur1].[dev_Active]=(1.) AND [typhoon1902].[dbo].[DurableEventTab].[dev_PurgeState] as [Dur1].[dev_PurgeState]=(0) AND [typhoon1902].[dbo].[DurableEventTab].[dev_PartitionNumber] as [Dur1].[dev_PartitionNumber]=(0) AND [typhoon1902].[dbo].[DurableEventTab].[dev_ReferenceClusterRoot] as [Dur1].[dev_ReferenceClusterRoot]='iyrwd.52' AND [typhoon1902].[dbo].[DurableEventTab].[dev_Action] as [Dur1].[dev_Action]=N'Order:Ordered')) 0 0 Clustered Index Scan Clustered Index Scan OBJECT:([typhoon1902].[dbo].[DurableEventTab].[AribaPK7] AS [Dur1]), WHERE:([typhoon1902].[dbo].[DurableEventTab].[dev_Active] as [Dur1].[dev_Active]=(1.) AND [typhoon1902].[dbo].[DurableEventTab].[dev_PurgeState] as [Dur1].[dev_PurgeState]=(0) AND [typhoon1902].[dbo].[DurableEventTab].[dev_PartitionNumber] as [Dur1].[dev_PartitionNumber]=(0) AND [typhoon1902].[dbo].[DurableEventTab].[dev_ReferenceClusterRoot] as [Dur1].[dev_ReferenceClusterRoot]='iyrwd.52' AND [typhoon1902].[dbo].[DurableEventTab].[dev_Action] as [Dur1].[dev_Action]=N'Order:Ordered') [Dur1].[rootId] 1 0.00386574 0.0002263 71 0.00409204 [Dur1].[rootId] PLAN_ROW 0 1
View 3 Replies
View Related
Jun 17, 2015
Is it possible to check query execution plan of a store procedure from create script (before creating it)?
Basically the developers want to know how a newly developed procedure will perform in production environment. Now, I don't want to create it in production for just checking the execution plan. However they've provided SQL script for the procedure. Now wondering is there any way to look at the execution plan for this procedure from the script provided?
View 8 Replies
View Related
Jun 17, 2015
I run a query
select col1, col2, col3, col4
from Table
where col2=5
order by col1
I have a primary key on the column.The execution plan showing the clustered index scan cost 30% & sort cost 70%..When I run the query I got missing index hint on col2 with 95% impact.So I created the non clustered index on col2.The total executed time decreased by around 80ms but I didn't see any Index name that is using in the execution plan.After creating the index also I am seeing same execution plan
The execution plan showing the clustered index scan cost 30% & sort cost 70% but I can see the total time is reducing & Logical reads on that table is reducing.I am sure that index is useful but why there is no change in the execution plan?
View 7 Replies
View Related
Jun 25, 2015
Is there a way to leave the graphical 'Include Execution Plan' on by default in SSMS? I don't know how many times I run a long-running query, say to myself, "wow, that took a while; I wonder what the execution plan looks like?" only to realize that I left it turned off. Now I have to turn it on, and wait for the query to run again. I'm guessing there's a setting in the options somewhere to always leave it on, but I'm not sure where
View 2 Replies
View Related
Sep 15, 2015
I have query with an expensive Key Lookup on a joined table. The predicate is the column that I'm joining on, and the output list contains two columns from the joined table.
I've created a basic non-clustered index covering the predicate column and include-ing the two output columns. However, the execution plan ignores this, and insists on using the primary key of the joined table to do the expensive key lookup. I've tried adding the included columns to the index directly and there's no change. I've also tried running dbcc freeproccache and no change.
View 3 Replies
View Related
Aug 21, 2001
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 5 Replies
View Related
Feb 2, 2006
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 5 Replies
View Related
Aug 29, 2007
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 2 Replies
View Related
Feb 29, 2008
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 14 Replies
View Related
Jul 20, 2005
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 1 Replies
View Related
Jul 20, 2005
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 1 Replies
View Related
Jul 9, 2007
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 6 Replies
View Related
Jul 28, 1999
What does 'tablename. index... cost: 100%' mean when I use display estimated execution plan?
View 1 Replies
View Related
Sep 24, 2002
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 3 Replies
View Related
Jul 9, 2003
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 1 Replies
View Related
Jun 12, 2006
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 2 Replies
View Related
Mar 24, 2008
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 2 Replies
View Related
Aug 5, 2005
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 5 Replies
View Related
Mar 9, 2006
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 14 Replies
View Related
May 2, 2006
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 5 Replies
View Related
