SQL Server Performance

'Shockingly Poor' SELECT speed

Discussion in 'Performance Tuning for DBAs' started by kitemar, Feb 17, 2004.

  1. kitemar New Member


    A system I'm looking at has got a 400+ user base on a 4 processor server with 4gb RAM. This hosts three databases;

    1 - Main database - 400 tables, heavy querying going on.
    2 - Login database - 2 table, very little activity.
    3 - Info database - 5 tables, relatively little activity.

    Everything has been going fine but in the last month or so on the Info database a select for a row on this database has been running sometimes at 1.8 seconds to retrieve a row. The select is against a single table with about 550,000 rows. The WHERE clause is simple: WHERE key_field = 0x0...(guid)...0000. Performance is poor when called by apps and when the query is executed from Query Analyzer.

    The key field is covered as the only field in a unique, non-clustered index.

    Other indexes on the table are:

    Clustered, non-unique, guid+name+flag+date
    Non-Clustered, non-unique, guid

    Putting a backup of the Info database onto another server has allowed me to select 100 rows at randon (DBCC DROPCLEANBUFFERS before the testing) in less than two seconds.

    The execution plan on the problem server shows that an index seek is being performed.

    Any ideas why the performance might be so dire when querying this table by an indexed column?


  2. Luis Martin Moderator

    Did you run Update Statistics and Index Defragmentation as part of maintenance plan?

    Luis Martin
  3. kitemar New Member

    Statistics are updated automatically on a weekly basis. The performance has been poor for a month or so.

    Index defragmentation... I'm not aware of that having been performed. I'll check that out.

    Thankyou for the response.
  4. joechang New Member

    what else is going on?
    is this query always slow or occasionaly slow?

    suppose other people are doing inserts, updates etc
    periodically, sql server checkpoint flushes all modified buffers to disk,
    when it does so, it just dumps everything into the disk queue,
    so it is not unusual to see disk queue at >100,
    it may take several seconds for the queue to clear,
    now suppose your read query comes along and the data is no in memory, so it has to read from disk, but first, it must wait in line for all the writes already in the queue to clear up.
    this is just one example

  5. Twan New Member

    also check for file fragmentation on the disk itself

    I presume that the query still poor if you force the index?

  6. kitemar New Member

    The server quite happily service the requests of 400+ users at a time, primarily hitting the main database (hundreds of tables). Note that this server does nothing except handle the database. Flat files / docs / other info is all held on a different server. The front end application operates on 18+ Citrix servers which are load-balanced.

    I have seen the duration for a single select on the problem table vary from 0 to 1,800ms but a typical value is about 300ms.

    Although I was not there to see this, I am told that the area of the application accessing this table was performing poorly at the weekend when there were only four or five people on the system.
  7. kitemar New Member

    Hi Twan.

    The execution plan presented when I was testing this showed that the index was being used so I never tried forcing it.

  8. kitemar New Member

    I'll check out the index and physical disk fragmentation.

  9. Twan New Member

    If the problem only happens sometimes, then as Joe mentioned it is likely to be due to other processes influencing the resource usage. There are no jobs which ran during the time that performance was poor (even on other databases?)

    The reason for the variation may be explained by the index being forced out of the SQL cache, forcing SQL to have to read it from disk again. This can easily take an order of magnitude more than reading it from memory. This is made worse if the query then needs to do a bookmark lookup to get the columns listed in the select part of the statement

    What are the rest of the columns in the select statement, if it is only a few, then a covered index might be worthwhile (i.e. an index which contains all of the fields mentioned in the select)

  10. kitemar New Member

    The server can happily accomodate requests from 400+ users, but the performance has been bad when there have been only 4 or 5. I've not been told that there was some other job running when the performance was poor with 4/5 users.

    Alas, the select is obtaining just about everything in the table, 16 of the 17 columns present.

    I'll check out the fragmentation and if that looks bad I'll get it defragmented. Perhaps the next step might be to consider pinning the table?
  11. satya Moderator

    Apart from the discussion, can you confirm the levels of service pack on SQL & OS and check whether any network issues by taking help from SYSMON & other network tools.


    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  12. Chappy New Member

    Also check your I/O is not maxing out.. a guid is quite a wide field to have in a clustered index in terms of bytes, especially as this also increases storage requirements for all your non clustered indices.

Share This Page