Showing posts with label plan. Show all posts
Showing posts with label plan. Show all posts

Friday, March 30, 2012

Query xml field in Query Plan DMV

I anot expertin XML and need to query a value in the field "query_plan" of
the "sys.dm_exec_query_plan" DMV. The value I is the whether queries has
Parallelism value more than 0. I remeber have seen something like
sys.dm_exec_query_plan.query_plan.value ('declare namespace.......
Is there in BOL a chapter on how querying special SQL xml schemas ?
Thanks in advanceHello eliassal,
You could query the XML column in the DMV as any other XML column in SQL by
using the XQuery.
For example, I use this Statement to query the query_Plan:
select query_plan.query('declare namespace
showplan="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
/showplan:ShowPlanXML/showplan:BatchSequence/showplan:Batch') from
sys.dm_exec_query_plan(<my plan handle id> )
For more detailed information about how to query the XML date using XQuery,
please refer this article:
XQuery Against the xml Data Type
http://msdn2.microsoft.com/en-us/library/ms189075.aspx
For more detailed information about the schema of the query_plan, pleaser
refer this article:
sys.dm_exec_query_plan
http://msdn2.microsoft.com/en-us/ms189747.aspx
http://schemas.microsoft.com/sqlserver/
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==========
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||So many thanks, I tried so many times, so many syntaxes to work with the
"RelOp" element without success
In the sql schemq it is defined as follows :
<xsd:element name="RelOp" type="shp:RelOpType" />
<xsd:element name="ParameterList" type="shp:ColumnReferenceListType"
minOccurs="0" maxOccurs="1" />
As I said yesterday, I understood in a sql article that if the
max(...RelOp/@.parelle.....) > 0 gives us an idea if the query would be
paralleize.
I am deserate!!!HELP :-)
Thanks again
"Wei Lu [MSFT]" wrote:

> Hello eliassal,
> You could query the XML column in the DMV as any other XML column in SQL b
y
> using the XQuery.
> For example, I use this Statement to query the query_Plan:
> select query_plan.query('declare namespace
> showplan="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
> /showplan:ShowPlanXML/showplan:BatchSequence/showplan:Batch') from
> sys.dm_exec_query_plan(<my plan handle id> )
> For more detailed information about how to query the XML date using XQuery
,
> please refer this article:
> XQuery Against the xml Data Type
> http://msdn2.microsoft.com/en-us/library/ms189075.aspx
> For more detailed information about the schema of the query_plan, pleaser
> refer this article:
> sys.dm_exec_query_plan
> http://msdn2.microsoft.com/en-us/ms189747.aspx
> http://schemas.microsoft.com/sqlserver/
> Hope this will be helpful!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ========================================
==========
> Get notification to my posts through email? Please refer to
> l]
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> [url]http://msdn.microsoft.com/subscriptions/support/default.aspx." target="_blank">http://msdn.microsoft.com/subscript...t/default.aspx.
> ========================================
==========
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
>|||Hello Eliassal,
I found that article. The statement should be:
select
p.*,
q.*,
cp.plan_handle
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where
cp.cacheobjtype = 'Compiled Plan' and
p.query_plan.value('declare namespace
p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
max(//p:RelOp/@.Parallel)', 'float') > 0
Troubleshooting Performance Problems in SQL Server 2005
https://www.microsoft.com.nsatc.net...005/tsprfprb.ms
px
Is this article you want?
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==========
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||LOVELY, so many thanks
"Wei Lu [MSFT]" wrote:

> Hello Eliassal,
> I found that article. The statement should be:
> select
> p.*,
> q.*,
> cp.plan_handle
> from
> sys.dm_exec_cached_plans cp
> cross apply sys.dm_exec_query_plan(cp.plan_handle) p
> cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
> where
> cp.cacheobjtype = 'Compiled Plan' and
> p.query_plan.value('declare namespace
> p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
> max(//p:RelOp/@.Parallel)', 'float') > 0
>
> Troubleshooting Performance Problems in SQL Server 2005
> l]
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> [url]http://msdn.microsoft.com/subscriptions/support/default.aspx." target="_blank">https://www.microsoft.com.nsatc.net...t/default.aspx.
> ========================================
==========
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Lines: 33
X-Tomcat-ID: 34576541
MIME-Version: 1.0
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Organization: Microsoft
X-Tomcat-NG: microsoft.public.sqlserver.xml
NNTP-Posting-Host: tomcatimport2.phx.gbl 10.201.218.182
Xref: leafnode.mcse.ms microsoft.public.sqlserver.xml:1829
Hello Eliassal,
My pleasure. If you have any question, please feel free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==========
(This posting is provided "AS IS", with no warranties, and confers no
rights.)sql

Query xml field in Query Plan DMV

I anot expertin XML and need to query a value in the field "query_plan" of
the "sys.dm_exec_query_plan" DMV. The value I is the whether queries has
Parallelism value more than 0. I remeber have seen something like
sys.dm_exec_query_plan.query_plan.value ('declare namespace.......
Is there in BOL a chapter on how querying special SQL xml schemas ?
Thanks in advance
Hello eliassal,
You could query the XML column in the DMV as any other XML column in SQL by
using the XQuery.
For example, I use this Statement to query the query_Plan:
select query_plan.query('declare namespace
showplan="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
/showplan:ShowPlanXML/showplan:BatchSequence/showplan:Batch') from
sys.dm_exec_query_plan(<my plan handle id>)
For more detailed information about how to query the XML date using XQuery,
please refer this article:
XQuery Against the xml Data Type
http://msdn2.microsoft.com/en-us/library/ms189075.aspx
For more detailed information about the schema of the query_plan, pleaser
refer this article:
sys.dm_exec_query_plan
http://msdn2.microsoft.com/en-us/ms189747.aspx
http://schemas.microsoft.com/sqlserver/
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
|||So many thanks, I tried so many times, so many syntaxes to work with the
"RelOp" element without success
In the sql schemq it is defined as follows :
<xsd:element name="RelOp" type="shp:RelOpType" />
<xsd:element name="ParameterList" type="shp:ColumnReferenceListType"
minOccurs="0" maxOccurs="1" />
As I said yesterday, I understood in a sql article that if the
max(...RelOp/@.parelle.....) > 0 gives us an idea if the query would be
paralleize.
I am deserate!!!HELP :-)
Thanks again
"Wei Lu [MSFT]" wrote:

> Hello eliassal,
> You could query the XML column in the DMV as any other XML column in SQL by
> using the XQuery.
> For example, I use this Statement to query the query_Plan:
> select query_plan.query('declare namespace
> showplan="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
> /showplan:ShowPlanXML/showplan:BatchSequence/showplan:Batch') from
> sys.dm_exec_query_plan(<my plan handle id>)
> For more detailed information about how to query the XML date using XQuery,
> please refer this article:
> XQuery Against the xml Data Type
> http://msdn2.microsoft.com/en-us/library/ms189075.aspx
> For more detailed information about the schema of the query_plan, pleaser
> refer this article:
> sys.dm_exec_query_plan
> http://msdn2.microsoft.com/en-us/ms189747.aspx
> http://schemas.microsoft.com/sqlserver/
> Hope this will be helpful!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
>
|||Hello Eliassal,
I found that article. The statement should be:
select
p.*,
q.*,
cp.plan_handle
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where
cp.cacheobjtype = 'Compiled Plan' and
p.query_plan.value('declare namespace
p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
max(//p:RelOp/@.Parallel)', 'float') > 0
Troubleshooting Performance Problems in SQL Server 2005
https://www.microsoft.com.nsatc.net/technet/prodtechnol/sql/2005/tsprfprb.ms
px
Is this article you want?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||LOVELY, so many thanks
"Wei Lu [MSFT]" wrote:

> Hello Eliassal,
> I found that article. The statement should be:
> select
> p.*,
> q.*,
> cp.plan_handle
> from
> sys.dm_exec_cached_plans cp
> cross apply sys.dm_exec_query_plan(cp.plan_handle) p
> cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
> where
> cp.cacheobjtype = 'Compiled Plan' and
> p.query_plan.value('declare namespace
> p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
> max(//p:RelOp/@.Parallel)', 'float') > 0
>
> Troubleshooting Performance Problems in SQL Server 2005
> https://www.microsoft.com.nsatc.net/technet/prodtechnol/sql/2005/tsprfprb.ms
> px
> Is this article you want?
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
|||Hello Eliassal,
My pleasure. If you have any question, please feel free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Friday, March 23, 2012

Query tuning

How do you guys go about compare the efficiency of two queries?
What I usually do is run the "Display estimated execution plan" in the query
analyser and see each query cost (relative to the batch) and pick one that
gives lower percentage. Is this a good way to compare queries? Am I
missing something just by looking at that number?
Do I actually have to check IO costs, CPU running time, run the profile (and
look for what)?
Correction, tips and suggestion of best practice will be appreciated.
ThanksJustin
> Do I actually have to check IO costs, CPU running time, run the profile
> (and look for what)?
Yes , sure , as well as looking at EXECUTION PLAN of the both queries
http://www.sql-server-performance.c...performance.asp
"Justin" <nospam@.nospam.com> wrote in message
news:Oe3YBjflGHA.4244@.TK2MSFTNGP02.phx.gbl...
> How do you guys go about compare the efficiency of two queries?
> What I usually do is run the "Display estimated execution plan" in the
> query analyser and see each query cost (relative to the batch) and pick
> one that gives lower percentage. Is this a good way to compare queries?
> Am I missing something just by looking at that number?
> Do I actually have to check IO costs, CPU running time, run the profile
> (and look for what)?
> Correction, tips and suggestion of best practice will be appreciated.
> Thanks
>|||Justin wrote:
> How do you guys go about compare the efficiency of two queries?
> What I usually do is run the "Display estimated execution plan" in the que
ry
> analyser and see each query cost (relative to the batch) and pick one that
> gives lower percentage. Is this a good way to compare queries? Am I
> missing something just by looking at that number?
> Do I actually have to check IO costs, CPU running time, run the profile (a
nd
> look for what)?
> Correction, tips and suggestion of best practice will be appreciated.
> Thanks
>
The estimated plan is a good place to start, you can identify the most
expensive parts of the query from that. You should also look at the I/O
stats and the actual execution plan. From the I/O stats, you can
identify the tables that are hit the hardest, and focus on potential
indexes, etc for those tables. Looking at the actual execution plan
will help you identify potential new indexes, improved joins, sorts, etc..

Query Tuning

I want to take the execution plan of some transact sql queries, I took the execution plan as text based one, that having

index scan, index seek

Remote scan, remote Update

sort order by cluases

in the above clauses what is the high performance, and how will i change to the high performance clauses by changin the query to improve the execution speed of the query

Please guide me

the execution plan is

SELECT [Inventory_Profile].[InventoryID] ,[Inventory_Profile].[Alias] ,[Inventory_Profile].[InventoryStatusID] ,[Inventory_Profile].[InventorySubTypeID] ,[Inventory_Profile].[InventoryTypeID] ,[Inventory_Profile].[AcquisitionDate] ,[Inventory_Profile].[UnitNumber] ,[Inventory_Profile].[YearOfManufacture] ,[Inventory_Profile].[Manufacturer] ,[Inventory_Profile].[Make] ,[Inventory_Profile].[Model] ,[Inventory_Profile].[SerialNumber] ,[Inventory_Profile].[UsageConditionID] ,[Inventory_Profile].[Description1] ,[Inventory_Profile].[Description2] ,[Inventory_Profile].[LocationEffectiveFromDate] ,[Inventory_Profile].[IsFlaggedForSale] ,[Inventory_Profile].[RentalPurchaseOrderNumber] ,[Inventory_Profile].[AquisitionPurchaseOrderNumber] ,[Inventory_Profile].[SortOrder] ,[Inventory_Profile].[IsSaleLeaseBack] ,[Inventory_Profile].[InterimRentReceivableUpfrontTaxModeID] ,[Inventory_Profile].[LeaseRentalReceivableUpfrontTaxModeID] ,[Inventory_Profile].[OverTermReceivableUpfrontTaxModeID] ,[TaxDepreciation_Inventory].[IsTaxDepreciationRequired] ,[TaxDepreciation_Inventory].[IsComputationPending] ,[TaxDepreciation_Inventory].[TaxDepreciationTemplateID] ,[TaxDepreciation_Inventory].[InventoryCostBasisAmount] ,[TaxDepreciation_Inventory].[DepreciationBeginDate] ,[TaxDepreciation_Inventory].[DepreciationEndDate] ,[TaxDepreciation_Inventory].[IsTaxDepreciationTerminated] ,[TaxDepreciation_Inventory].[IsStraightLineMethodUsed] ,[TaxDepreciation_Inventory].[IsLeaseTermUsedForStraightLineMethod] ,[Inventory_PTMS].[Division] ,[Inventory_PTMS].[Branch] ,[Inventory_PTMS].[SalesTaxPercent] ,[Inventory_PTMS].[SalesTaxAmount] ,[Inventory_PTMS].[IsSalesTaxIncluded] ,[Inventory_PTMS].[GLExpenseAccount] ,[Inventory_PTMS].[GLAssetAccount] ,[Inventory_PTMS].[SoftwareExclusionAmount] ,[Inventory_PTMS].[AssetCategoryCodeID] ,[Inventory_PTMS].[OwnershipCodeID] ,[Inventory_PTMS].[ManufacturingCodeID] ,[Inventory_PTMS].[ReimburseCodeID] ,[Inventory_PTMS].[BillingStatusID] ,[Inventory_PTMS].[PropertyTaxExemptionCodeID] ,[Inventory_PTMS].[UserDefinedField1] ,[Inventory_PTMS].[UserDefinedField2] ,[Inventory_PTMS].[Notes] FROM [Inventory_Profile] INNER JOIN [TaxDepreciation_Inventory] ON [Inventory_Profile].[InventoryID]=[TaxDepreciation_Inventory].[InventoryID] INNER JOIN [Inventory_PTMS] ON [Inventory_Profile].[InventoryID]=[Inventory_PTMS].[InventoryID] INNER JOIN [Inventory_Status_CnfgLocale] ON [Inventory_Profile].[InventoryStatusID] in (SELECT InventoryStatusID FROM Inventory_Status_CnfgLocale WHERE InventoryStatusName <> 'Donated' and InventoryStatusName <> 'Scrap' and InventoryStatusName <>'Write Off' and InventoryStatusName <> 'Sold')

|--Nested Loops(Inner Join)
|--Nested Loops(Inner Join, OUTER REFERENCES:([LW_Winthrop].[dbo].[TaxDepreciation_Inventory].[InventoryTaxDepreciationDetailID]))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([LW_Winthrop].[dbo].[Inventory_PTMS].[InventoryID]))
| | |--Nested Loops(Left Semi Join, OUTER REFERENCES:([LW_Winthrop].[dbo].[Inventory_Profile].[InventoryStatusID]))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([LW_Winthrop].[dbo].[Inventory_PTMS].[InventoryID]))
| | | | |--Clustered Index Scan(OBJECT:([LW_Winthrop].[dbo].[Inventory_PTMS].[PK_IInvPTMS]))
| | | | |--Clustered Index Seek(OBJECT:([LW_Winthrop].[dbo].[Inventory_Profile].[PK_IInventory_InvID]), SEEK:([LW_Winthrop].[dbo].[Inventory_Profile].[InventoryID]=[LW_Winthrop].[dbo].[Inventory_PTMS].[InventoryID]) ORDERED FORWARD)
| | | |--Clustered Index Seek(OBJECT:([LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[PK_cdInvStatus_Locale]), SEEK:([LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[InventoryStatusID]=[LW_Winthrop].[dbo].[Inventory_Profile].[InventoryStatusID]), WHERE:([LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[InventoryStatusName]<>N'Donated' AND [LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[InventoryStatusName]<>N'Scrap' AND [LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[InventoryStatusName]<>N'Sold' AND [LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[InventoryStatusName]<>N'Write Off') ORDERED FORWARD)
| | |--Index Seek(OBJECT:([LW_Winthrop].[dbo].[TaxDepreciation_Inventory].[UK_ITaxDepInfo]), SEEK:([LW_Winthrop].[dbo].[TaxDepreciation_Inventory].[InventoryID]=[LW_Winthrop].[dbo].[Inventory_PTMS].[InventoryID]) ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT:([LW_Winthrop].[dbo].[TaxDepreciation_Inventory].[PK_ITaxDepInfo]), SEEK:([LW_Winthrop].[dbo].[TaxDepreciation_Inventory].[InventoryTaxDepreciationDetailID]=[LW_Winthrop].[dbo].[TaxDepreciation_Inventory].[InventoryTaxDepreciationDetailID]) LOOKUP ORDERED FORWARD)
|--Clustered Index Scan(OBJECT:([LW_Winthrop].[dbo].[Inventory_Status_CnfgLocale].[PK_cdInvStatus_Locale]))

Hello,

could you please provide the full execution plan as output by Query Analyzer, as well as the query you are running?

|||

Hi,

--index scan good, index seek better :-)

--Remote scan und update is always involved if your doing something on a remote basis, like a linked server. If you want to use the remote indexes you will have to create a logic / stored procedureon the remote side on execute that via a linked server command.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Index scan sometimes means bad news :)
Say, when a clustered index is defined on a table, the query plan will never show a "table scan" operation, it will always show "index scan", even if it is scanning the table.
Index seek means that your index is actually been used, this is generally a good news. But nothing general can be said, sometimes index seek is worse than index scan, and vice vers. For example, if you perform a range query and have a clustered index on appropriate columns, this results in an index scan, which is the fastest plan in most cases. Meanwhile, if you have a non-clustered index on those columns, you will get an index seek(or table scan, depending on statistics). This is almost always worse that index seek. If you do not have clustered index on the columns in where clause, but you have some other clustered index, you will get an index scan which is bad, it is a table scan in fact. So in the first case scan > seek and in the second case seek > scan.

Post the query, and table schema, we will be able to be more definite in that case.