SQL Server Performance

Query tuning

Discussion in 'SQL Server 2008 T-SQL Performance Tuning' started by nduckste, Feb 16, 2010.

  1. nduckste New Member

    I'm having a problem with the following query. The first one doesn't run, the second one does. The only difference is the
    "WHERE msgtype = "
    The query plans for both queries indicate they will both use the Clustered index to seek on UTCDT, Seqno. The second query runs lighting fast, the first not at all.
    Things I tried:1. Setting table hints2. Setting index hints3. Forcing parameterization4. Creating query plan templates and guides5. Updating statistics For diagnosis purposes, the things that made this query work were:
    1. Setting TOP to < 3.
    2. Taking the OR statement out. OR statements are notoriously bad for query optimization but I don't have control over the SQL here.
    3. Adding another MsgType to the where clause, i.e. MsgType in (‘AEGISFIN’, ‘SNFARC’). Again, this was just to see if the query would run.
    The distribution of the database as it pertains to MsgType is about 80% MsgType = SNFARC. There are 50M rows in this table.
    Any help would be much appreciated.
    Thanks,
    NickSELECT TOP (50) [msgid]
    ,[msgdttmposted],[seqno],[msgtype],[msgdata],[msginfo],[eid]
    ,[geid],[oid],[comment],[sourcetitle],[sourceipaddress],[sourceport]
    ,[receivertitle],[receiverexpectedtitle],[receiveripaddress]
    ,[receiverport],[receivedttm],[utcdt] FROM
    [azQueue2]..[MSG201] -- m WITH (INDEX (IX_MSG201_3))WHERE
    msgtype = 'AEGISFIN'AND
    (([utcdt] = '2010-02-13 01:05:06.743' AND [seqno] > 0)
    OR [utcdt] >'2010-02-13 01:05:06.743'
    ) ORDER
    BY [utcdt], [seqno] GO
    SELECT TOP (50) [msgid]
    ,[msgdttmposted],[seqno],[msgtype],[msgdata],[msginfo],[eid]
    ,[geid],[oid],[comment],[sourcetitle],[sourceipaddress],[sourceport]
    ,[receivertitle],[receiverexpectedtitle],[receiveripaddress]
    ,[receiverport],[receivedttm],[utcdt] FROM [azQueue2]..[MSG201] -- m WITH (INDEX (IX_MSG201_3))
    --WHERE msgtype = 'AEGISFIN'
    --WHERE 0=0WHERE
    (msgtype = 'SNFARC') AND
    (([utcdt] = '2010-02-13 01:05:06.743' AND [seqno] > 0)
    OR [utcdt] >'2010-02-13 01:05:06.743'
    ) ORDER
    BY [utcdt], [seqno]
  2. FrankKalis Moderator

    What exactly do you mean by "does not run at all"? Have you cancelled this at some point?
    Also, how did you test this? Have you run DBCC DROPCLEANBUFFERS / DBCC FREEPROCACHE between both queries? Are these queries part of a bigger procedure?
  3. Fhatuwani New Member

    Did you try to thoroughly check the table properties, especially for the msgtype column`s properties?
    you can find that this column only accepts minimum of 6 characters just like the msgtype you have set on the second statement!
    Also check the constraints that are defined for this table.
  4. alaguganesh New Member

    SELECT TOP (50)
    [msgid],[msgdttmposted],[seqno],[msgtype],[msgdata],[msginfo],
    [eid],[geid],[oid],[comment],[sourcetitle],[sourceipaddress],
    [sourceport],[receivertitle],[receiverexpectedtitle],
    [receiveripaddress],[receiverport],[receivedttm],[utcdt]
    FROM [azQueue2]..[MSG201]
    WHERE msgtype = 'AEGISFIN'
    AND (([utcdt] = '2010-02-13 01:05:06.743' )
    OR [utcdt] >'2010-02-13 01:05:06.743'
    )
    AND [seqno] > 0
    ORDER BY [utcdt], [seqno] GO
    you can try this
    and tell if it is working or not

Share This Page