May 19, 2009 at 4:45 am
Dear All,
Suppose i am writting a query.How can i prepare the Execution Plan of a query?
Is any command supporting for SQL Server to Prepare Execution Plan of a query?
What is out-of-date statistics ?
For Example the Query is::
FROM MRU_LIST M LEFT OUTER JOIN ABC_USER A ON
M.FIELD_VALUE = A.LOGIN_NAME
WHERE M.LOGIN_NAME = 'testap1' AND (A.STATUS='A' or A.STATUS IS NULL ) ORDER BY M.FIELD_ID, M.FIELD_ORDER
Please suggest me for the needful.
Thanks in advance.
Regards
Sumanta Panda
May 19, 2009 at 5:37 am
You don't prepare the execution plan. When you run the query, SQL generates an exec plan as part of the execution.
If you're trying to see the exec plan, there's a button on the toolbar in management studio 'Include actual execution plan'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 19, 2009 at 6:10 am
Though I strongly does not recommend forcing execution plans as SQL Optimizer is smart enough to generate the best plans most of the times. If you fully aware of the impacts (though I am not) you can use the sp_create_plan_guide to create plan guides and use USE PLAN query hint to specify the plan.
--Ramesh
May 19, 2009 at 6:15 am
Out of date statistics will seriously affect the query and might mean it does lots of I/O and doesn't consider indexes.
To fix either
(1) Turn on auto create and auto update statistics
(2) run sp_updatestatistics
(3) Issue an UPDATE STATISTICS command
More detail on all of these is books online.
You have another thread going that may be the same fault, this will be posted there as well.
Tim
.
May 20, 2009 at 2:11 pm
Detail execution plan on that query will show that it would be better to re-write the query because:
- A.STATUS='A' or A.STATUS IS NULL clause makes impossible to use any index by SQL Server;
- ORDER BY M.FIELD_ID, M.FIELD_ORDER clause needs a lot of memory if the result set is big;
- It would be better to replace string 'testap1' with variable so SQL Server would not recompile query if you need data for 'testap2';
- Tables MRU_LIST and ABC_USER need to have index on FIELD_VALUE and LOGIN_NAME columns so JOIN would work faster;
- I would replace that JOIN on FIELD_VALUE with JOIN on FIELD_ID because most likely FIELD_VALUE is a string and FIELD_ID is integer, because FIELD_ID should be shorter (thus index is shorter);
- It would be nice to have clustered index on FIELD_ID. In this case, you do not need ORDER BY because result will be sorted by FIELD_ID automatically.
Is that helpful?
Alex Prusakov
May 20, 2009 at 3:11 pm
Alex Prusakov (5/20/2009)
- A.STATUS='A' or A.STATUS IS NULL clause makes impossible to use any index by SQL Server;
SQL can use an index to satisfy that, depending (of course) on how it runs the rest of the query. It is SARGable though. Not as efficient as just a match Status='A', but it's not an automatic table scan.
Create Table TestIndex (
ID int identity primary key,
SomeString varchar(70),
Status Char(1)
)
insert into TestIndex (SomeString, Status)
SELECT top (40000) a.name, CASE WHEN a.column_id = 1 THEN NULL ELSE CHAR(65+FLOOR(RAND(56889*b.column_id)*8)) END
FROM master.sys.columns a cross join master.sys.columns b
Create index idx_status ON TestIndex (Status)
SELECT ID, Status FROM TestIndex WHERE Status = 'A' OR Status IS NULL
- It would be nice to have clustered index on FIELD_ID. In this case, you do not need ORDER BY because result will be sorted by FIELD_ID automatically.
Well, he'd still need the order by because clustered index order doesn't guarantee query order, but there's a chance that SQL will be able to use the cluster's order and not have to implement the sort.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 21, 2009 at 12:07 am
Dear All,
In the SQL SERVER ,i have seen an artile to metioned how will you prepare the execution paln.
SET SHOWPLAN_ALL - SET SHOWPLAN_ALL will instruct SQL Server not to execute Transact-SQL statements but return detailed information about how the statements will be executed and provides estimates of the resource requirements for the statements.
SET SHOWPLAN_ALL ON
GO
SELECT FIELD_ID, FIELD_ORDER, FIELD_DISPLAY, FIELD_VALUE ,A.STATUS
FROM MRU_LIST M LEFT OUTER JOIN ABC_USER A ON
M.FIELD_VALUE = A.LOGIN_NAME
WHERE M.LOGIN_NAME = 'testap1' AND (A.STATUS='A' or A.STATUS IS NULL ) ORDER BY M.FIELD_ID, M.FIELD_ORDER
Should the above command is the appropriate solution for Execution paln?
Please clarrify my doubt.
Thanks & Regards
Sumanta Panda
May 21, 2009 at 12:21 am
sk.panda (5/21/2009)
Should the above command is the appropriate solution for Execution paln?
What are you trying to do? If you just want to see the query's exec plan, run it in management studio with the "Include Actual Execution Plan" option on.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply