Indexed When using Remote Servers

[SQLSvr2010]

Hi,

I have an update query in which I am using tables from a remote server .Attached is the Execution Plan.Can't I see the Index Type for the tables queried from the Remote Server?Also, I am not able to tune the Query any further.Any help is appreciated.Below is the Query.


UPDATE cp
SET CustKey = c.CustKey,
SOKey = so.SOKey,
OrderShipDate = sh.Shipdate
FROM  dbo.tSCN_CommissionsPending cp INNER JOIN  [Server-MAS].mas500_app_SalesCommissionsTest.dbo.tarcustomer c
ON c.CRMCustID = cp.AccountID INNER JOIN
(SELECT CustKey,MIN(ShipKey) As ShipKey,MIN(PostDate) AS ShipDate FROM [Server-MAS].mas500_app_SalesCommissionsTest.dbo.tsoshipment
WHERE [dbo].[get_month_start](PostDate) = [dbo].[get_month_start](DATEADD(m,-1,GETDATE()))
GROUP BY CustKey) sh
ON c.CustKey = sh.CustKey
INNER JOIN [Server-MAS].mas500_app_SalesCommissionsTest.dbo.tsoSalesOrdShip so
ON so.ShipKey = sh.ShipKey
WHERE cp.CustKey is NULL or cp.CustKey = '' and cp.SalesBoardID = 1065

[Bru Medishetty]

Hi,

Look at the execution plan on the remote server locally.

I mean.. look at the execution plan for the query

Code:

SELECT CustKey,MIN(ShipKey) As ShipKey,MIN(PostDate) AS ShipDate FROM [Server-MAS].mas500_app_SalesCommissionsTest.dbo.tsoshipment
WHERE [dbo].[get_month_start](PostDate) = [dbo].[get_month_start](DATEADD(m,-1,GETDATE()))
GROUP BY CustKey

and you may be able to tune it.

[SQLSvr2010]

I ran the query locally and checked but I am getting Index Scan .I added Index on CustKey and included PostDate and Ship Key columns to that index.Please see the attached execution plan.

[Bru Medishetty]

What is this section of your Where clause?

WHERE [dbo].[get_month_start](PostDate) = [dbo].[get_month_start](DATEADD(m,-1,GETDATE()))

[SQLSvr2010]

PostDate is the Column in the table and I am checking if the Post Date is the Previous month.I even tried making PostDate as part of the index along with Custkey and just including ShipKey as the included Column .But even then no luck.

[Bru Medishetty]

How is that table "dbo.get_month_start" related to "dbo.tsoshipment" ?

I am not able to find any relation between them and I would recommend you, if you can furnish the details about all the tables involved (their table definition) that would help better.

[SQLSvr2010]

dbo.get_month_start is a function and not a table which gives the first day of previous month of current date.

[Bru Medishetty]

Yes I doubted that it was a function and not a table and that was the reason I asked what was that.

When functions are used, it would not be possible to tune after some extents and I would like to know what is the performance of that query right, now and how long does it take to execute.

[SQLSvr2010]

It is taking almost 3 seconds to run the Update query .But it is showing Index Scan on the table that is on the local Server and showing a warning symbol regarding statistics.And the Operator and CPU Cost on the Remote Server tables is huge.Even after adding the indexes I dont see any change.

[Bru Medishetty]

The warning about Statistics missing is to be considered for sure, and create statistics on that column that is mentioned in the warnings.

Next, you got to give details about the Indexes on the table "dbo.tSCN_CommissionsPending"...

[SQLSvr2010]

CommissionsPending table has one clustered index(CommissionsPendingID) and 4 non-clustered indexes
Non-Clustered Indexes on the below columns :
1)SalesBoardID,
2)CustKey,SOKey,OrderShipDate
3)AccountID
4)OrderShipDate,SalesBoardDate with few columns included for this index

There is already a Statistics created when the second non-clustered index is created with CustKey,SoKey and OrderShipDate and I am getting the Statistics warning for SOKey,so do I have to create a statistics for SoKey alone?What is a Statistics and why do we have to create it?

[Bru Medishetty]

Yes you need to create the stats for that column that is shown in the execution plan.

What are those few columns included in the Index with OrderShipDate ? that is important..

[SQLSvr2010]

I created a Statistics on the column that was mentioned in the warning of the execution Plan.But one thing I noticed is the Details tab for the new statistics created says No statistics information available .Is it supposed to be so (I noticed that all the statistics that were created automatically when indexes were created have Some info in the Details tab).

The Columns included in the OrderShipDate index are
CommissionPendingID,PendingDate,PrimaryUserID,SecondaryUserID,ADIUserID,AccountID,CustKey,CompPlanID,SOKey,
SalesBoardID,ProductType,SalesBoardTerm,SalesBoardQty,SalesBoardPrice,SalesBoardRMR,SalesBoardEquipment,
SalesBoardLabor.