64-bit SQL Express not using index unless Mgt Studio open

  • This is really strange. I have a brand new server running 64-bit Windows and installed the x64 SQL Express edition. The database was copied from a 32-bit server where it is in a live environment.

    The most complex view is taking 26 seconds from within SQL Mgt. Studio or my application. However, if I make any change in the design within Mgt. Studio (even adding a space), then run the query, it takes 1.6 seconds. It will only take 1.6 seconds from that point forward. Leaving Mgt. Studio open, my application now has a 1.6 second response time on every subsequent query of that view.

    As soon as I close SQL Mgt. Studio, response time returns to 26 seconds. This condition is very consistent and repeatable.

    Am I missing a setup parameter for query optimization to use the indexes?

    I don't think this is query related, but I'll post it below. The EQUIPMENT table has 25 rows. SENSORSUMMARY has 740,000 rows. Indexes are available on every field used in the query.

    SELECT * FROM dbo.equipment

    INNER JOIN

    dbo.SensorSummary ON dbo.equipment.npk = dbo.SensorSummary.nequippk

    WHERE 

    (dbo.equipment.lactive = 1) AND

    ((CONVERT(VARCHAR, dbo.SensorSummary.tlogdatetime, 25) + dbo.SensorSummary.cEquipID) IN

    (SELECT CONVERT(VARCHAR, MAX(tlogdatetime), 25) + cEquipID AS Expr1 FROM dbo.SensorSummary AS SensorSummary_1

    GROUP BY cEquipID))


    Thanks,

    Bob Roenigk

Viewing post 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply