locked
Using indexes across databases RRS feed

  • Question

  • I just started a new job and after going over some of the existing stored procedures, I have a question.  Here is the setup.

     

    On SQLPROD01 server, there are two databases.  One is ProductOrders and the other is ProductCatalog.  The ProductOrders database has order information and the ProductCatalog has all the items in the catalog.  Both are fairly big with some tables have > 1,000,000 rows.

     

    A lot of the stored procedures will pull data from both databases.  So usp_Report1 in the ProductOrders database will have some SQL like this in it.

     

    SELECT

    <various columns and calculations>

    FROM

    ProductOrders..tblOrders ord

    INNER JOIN ProductCatalog..ItemDescription cat ON ord.ItemNumber = cat.ItemNumber

    WHERE

    <various conditions>

     

    My question is this.  Will SQL Server know to correctly use the indexes on the tables in the SQL query even if the tables are in different databases?

     

    Looking at the execution plans for some of these, it looks like it does for some stored procs and does not for others. Would creating a view into the other server be better?  What is the general rule for this kind of query?

     

    Thanks in advance for any help.  I really appreciate it!!

    Wednesday, September 24, 2008 4:58 PM

Answers

  • Yes, it will use the indexes the query optimizer determines are best.  The query optimizer doesn't care the tables are in different databases. 

    Wednesday, September 24, 2008 5:09 PM
  • Jim,

     

    It should be using indexes across databases on the same server.

     

    You can check for missing indexes the following way:

     

    http://msdn.microsoft.com/en-us/library/ms345364.aspx

     

    Also you can submit frequent queries/sprocs to the Database Engine Tuning Advisor for analysis from Query Editor.

     

    Make sure there is no index fragmentation and the SQL software is up-to-date with service packs.

     

    Optimization article:

    http://www.sqlusa.com/articles/query-optimization/ 

     

     


    • Edited by Kalman Toth Wednesday, January 4, 2012 6:38 AM
    Wednesday, September 24, 2008 5:31 PM

All replies

  • Yes, it will use the indexes the query optimizer determines are best.  The query optimizer doesn't care the tables are in different databases. 

    Wednesday, September 24, 2008 5:09 PM
  • Jim,

     

    It should be using indexes across databases on the same server.

     

    You can check for missing indexes the following way:

     

    http://msdn.microsoft.com/en-us/library/ms345364.aspx

     

    Also you can submit frequent queries/sprocs to the Database Engine Tuning Advisor for analysis from Query Editor.

     

    Make sure there is no index fragmentation and the SQL software is up-to-date with service packs.

     

    Optimization article:

    http://www.sqlusa.com/articles/query-optimization/ 

     

     


    • Edited by Kalman Toth Wednesday, January 4, 2012 6:38 AM
    Wednesday, September 24, 2008 5:31 PM
  • Thank you very much!!!

     

    Wednesday, September 24, 2008 5:40 PM