SQL Server Performance

Unusual case

Discussion in 'Performance Tuning for DBAs' started by Luis Martin, Jan 26, 2008.

  1. Luis Martin Moderator

    Windows 2003 SP2, SQL 2000 Enterprise, SP4.
    I have one select using severals tables. One of them is XXX.
    The query run in less than a second and use clustered index on XXX. Execution plan show that.
    Now, I improve other different query adding a new non cluster index on XXX.
    After that the new query run faster.
    The first one run in more than 1 minutes with a lot of read ahead of XXX. However the execution plan still use the cluster index. There is no use of the new non clustered index at all.
    Statistics are up to day. Defrag is ok and so on.
    I was searching the reason and find nothing. So I have to delete the non clustered index and, of course, the first query back to normal and the second one run slow.
    What I'm missing?
    Fell free to ask for any question include both queries and execution plan if you like. Please don't question about statistics, defrag, space free, integrity, service pack, etc.
  2. ndinakar Member

    Is the other non clustered index on the same column as the clustered index?
  3. Luis Martin Moderator

    Yes. All of them as more than one column. Some begins with the same column than clustered, some in other place (2nd or 4th, just and example.).
    The clustered has one column.
  4. ndinakar Member

    My guess is, if a column is part of a clustered index as well as a non-clustered index, SQL Server chooses to use the clustered index. This is because SQL Server can get the data from the same page when using clustered index where as for non-clustered index it has to get the PK value from the NC index and then go to the data page to get the actual value...
  5. Luis Martin Moderator

    I agree with you Nikidar.
    But in this case (may I don't explain correctly) with original query the execution plan is using clustered index. Why when I add other non cluster with cluster column as part of this new index, the query run slowly? and execution plan don't use it. I mean the execution plan is the same and now run slow.
    I delete this new index and the query run faster again. That as no sense to me.
  6. Luis Martin Moderator

    <p>&nbsp;I find something more.</p><p>If I execute the query&nbsp; with or without the new index nothing change in Query Analyzer. Same execution plan, same duration.<br></p><p>Now using the application, that is what is find with the new index using profiler:</p><p>Duration: 46298 &nbsp; CPU: 44844&nbsp; Reads:11645889</p><p>&nbsp;</p><p>Without new index:</p><p>Duration: 1561&nbsp; CPU: 266&nbsp; Reads: 12688&nbsp;</p><p>&nbsp;</p><p>Is a 3rd part application and use ODBC to reach the database.</p><p>&nbsp;</p><p>Any clue?&nbsp;</p><p><img src="file:///C:/DOCUME%7E1/Luis/LOCALS%7E1/Temp/moz-screenshot.jpg" alt=""><img src="file:///C:/DOCUME%7E1/Luis/LOCALS%7E1/Temp/moz-screenshot-1.jpg" alt=""><img src="file:///C:/DOCUME%7E1/Luis/LOCALS%7E1/Temp/moz-screenshot-2.jpg" alt=""><img src="/Community/forums/C:%5CTuning%5CMimo%5CMimo08.01.24%5Ccon%20index.jpg" mce_src="/Community/forums/C:TuningMimoMimo08.01.24con index.jpg"> </p>
  7. satya Moderator

    How about disk placements?
    I see it is not mentioned here and any chance to perfmon stats for those.
  8. Luis Martin Moderator

    Don't know what you mean with placements.
    But, with new index Profiler show a lot of reads. See one post before. Always running from application.
  9. satya Moderator

    Is it on RAID5 or RAID10?
  10. Luis Martin Moderator

    RAID 1.
    4 Physical disks, 2 RAID 1.
    OS, SQL, Log in one, data in other.
  11. ndinakar Member

    Hi Luis
    I dont think its the database issue. The query works fine if you re-write it. Its the way the query is coming to the db thats causing more reads..that sp_cursor thing is very suspicious....
  12. Luis Martin Moderator

    Agree with you.
    I have to talk with Company Developer and show him what is going on with the application.
  13. ndinakar Member

    [quote user="Luis Martin"]
    Agree with you.
    I have to talk with Company Developer and show him what is going on with the application.
    [/quote]
    Yes. And take a cane with you... to whoop his a$$...[:p]
  14. satya Moderator

    LOL Dinakar...
    Using Profiler helps to see what sp_cursor uses handle to execute the queries/proedures and you can capture the sp_cursorprepexec calls to see what it is trying to execute...
    then tune that query/procedure and also using PERFMON by looking at the SQL Server:Cursor Manager By Type – Cursor Requests/Sec counter, you can get a general feel for how many cursors are being used on the system by looking at this performance counter. Determine if cursors are the most appropriate means to accomplish the processing or whether a set-based operation, which is generally more efficient, is possible.
  15. ndinakar Member

    Hi Satya
    I think Luis is trying to find out why the adding an index to fix another query is making this query do more reads..The same query that was working fine..I doubt if he has the flexibility to change the query since its being written by some third party tool..
  16. ndinakar Member

    Is there a chance the query generated from the tool using index hint? can you profile the query from the tool? Does the query remain same all the time?
  17. Luis Martin Moderator

    No index hint.
    Yes, I get previous results with profiler.
    The query is always the same.
  18. ndinakar Member

    Are there columns in WHERE condition that are included in the NC index that are not in the clustered index? this might make the engine choose the NC as it covers more columns than the clustered index..
  19. Adriaan New Member

    Does the 3rd party app send an explicit SELECT statement over ODBC? I would run a trace to verify whether the statement actually being executed is the same as the statement issued by the app.

Share This Page