SQL Server Performance

help me increase query speed

Discussion in 'T-SQL Performance Tuning for Developers' started by tsusanto, May 14, 2004.

  1. tsusanto New Member

    I've got a table with 6 columns with only 160,000 records.
    Here's the table schema.

    labelID int,
    FHID int,
    pubID int,
    FHIssueCode nvarchar(40),
    issueID int,
    status int,
    CONSTRAINT labelPK PRIMARY KEY CLUSTERED (labelID)


    There's a query that's executed against the table about once every second i would say. So this is a heavily used table. Here's the query from our application.

    exec sp_executesql N'select * from labelTracker where fhID = @P1 and pubID = @P2 and fhIssueCode = @P3', N'@P1 int ,@P2 int ,@P3 nvarchar(4000) ', 1701, 6505574, N'JUN 04'

    We have a composite index on FHID, pubID, FHIssueCode. I know this is a pretty wide index since there's a nvarchar(40) column included in this index.

    Looking at profiler, each query takes about 300 ms. Anyone has any idea how I can reduce it? i rebuilt this index every night because it gets fragmented very quickly.


  2. Raulie New Member

    Yeah, first I would remove the composite index, and index fhID, pubID capture this in a trace and see what happens. Can you post the execution plan? Also whats up with this fhIssueCode = @P3', N'@P1 int ,@P2 int ,@P3 nvarchar(4000) if you are getting a lot of fragmentation you should set Fillfactor to lower amount. Or you can record this query in a trace during normal workload and have Index tuning wizard figure it out.
  3. Luis Martin Moderator

    Pick this part:
    select * from labelTracker where fhID = @P1 and pubID = @P2 and fhIssueCode = @P3

    Paste in Query Analyzer, replace variables for tipical values, see execution plan.
    Check if index are used.
    Run ITW and see what recomendations, if any.




    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  4. tsusanto New Member

    I ran the statement with show execution plan in query analyzer (although I don't quite know how to copy and post the results here).
    It says it's using the composite index, 98% of the query cost comes from the index seek.
    It says scanning a particular range of rows from a non-clustered index, object labeltracker.labelTrackerFHIDPubIDIssueCode which is my composite index that was created on those 3 columns, then it says ORDERED FORWARD.

    So it is using the index correctly. Will tell u the results of index wizard in a while.

  5. tsusanto New Member

    ok, index wizard recommended another index on just the FHID column alone.
    right now I have 3 existing indexes.
    labelTrackerPK on labelCode
    labeTrackerFHIDPubIDIssueCode on that 3 columns
    labeTrackerStatus on status column.

    Don't think it would make much of a difference, but i'll give it a shot anyway.
  6. Raulie New Member

    Use SET SHOWPLAN_TEXT ON or SET SHOWPLAN ALL or on your query so you can post results of execution plan. Update statistics then run index tuning wizard or try this remove Composite index it is way too wide and makes for an ineffecient index, index the columns that I mentioned to you, are they indexed on the parent tables? The only times Composite indexes are really effective is when you used in a Covered Index meaning all who's key value contains all the data needed to satify the query. Let me know how things turn out.
  7. tsusanto New Member

    select * from labelTracker where fhID = 1709 and pubID = 11076580 and fhIssueCode = '052704'<br /><br /> |--Bookmark Lookup(BOOKMARK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Bmk1000]), OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[zuber].[dbo].[labelTracker]))<br /><br /> |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[zuber].[dbo].[labelTracker].[labeTrackerFHIDPubIDIssueCode]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[labelTracker].[FHID]=Convert([@1]) AND [labelTracker].[pubID]=11076580 AND [labelTracker].[FHIssueCode]=Convert([@3])) ORDERED FORWARD)<br />
  8. Raulie New Member

    Let me know if performance improves after changes to indexes.
  9. tsusanto New Member

    no, number of reads and duration was still about the same after i dropped the composite index and created an index on only 2 columns as you suggested. I issued a dbcc dbreindex on the entire table and duration went down a lot. I put the fillfactor 0f 80, is that enough?
  10. Luis Martin Moderator

    Fill factor is ok. How about Index suggested by ITW?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  11. Raulie New Member

    Luis he did mention that it is a pretty heavily loaded table, do you think a lower amount of 65-70 would be worth wile?
    Tusanto just rereading you post you said each query reports 300ms in profiler. Thats not fast enough??? how many records is each query returning?
  12. Luis Martin Moderator

    May be you are rigth, Lazy, but I don't think the problem is in fillfactor, and yes 300ms is not to much to me.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  13. Raulie New Member

    The reason I suggested he readjust his fill factor was because he mentioned that he rebuilds his Indexes nightly due to heavy fragmentation.
  14. gaurav_bindlish New Member

    Change @P3 datatype in the query from nvarchar(4000) to nvarchar(40).

    This is necassary for to reduce the work for OLE/DB or ODBC to call the query.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

Share This Page