SQL Server Performance

Select count performance

Discussion in 'Performance Tuning for DBAs' started by 7013d0, Nov 1, 2005.

  1. 7013d0 New Member

    Hello everybody!

    I have database used by a 3rd party app [xx(] and users are complainting about a "select count(*) from table with (nolock)" performance. It takes between 5 and 7 minutes to run. It's their only complaint.
    The row size is around 1,9 KB. I can't modify the table since I don't have the app source code and nothing that runs in this DB uses stored procedures.

    Can someone point directions or give me some tips to improve it?

    I've already tried to update stats and defrag/rebuild index isn't needed.

    The server is a Quad Xeon 1,5GHz - 2GB RAM
    The datafiles are in a external storage - RAID5
    The logfiles are in a external storage - RAID1
    Windows 2000 Advanced Server SP4
    SQL Server 2000 EE SP4


    Table Info:
    - Rows: 11,100,938
    - Size: 21,768,728 KB

    DBCC SHOWCONTIG scanning 'Table' table...
    Table: 'Table' (1977058079); index ID: 1, database ID: 27
    TABLE level scan performed.
    - Pages Scanned................................: 2714964
    - Extents Scanned..............................: 340136
    - Extent Switches..............................: 340135
    - Avg. Pages per Extent........................: 8.0
    - Scan Density [Best Count:Actual Count].......: 99.78% [339371:340136]
    - Logical Scan Fragmentation ..................: 0.84%
    - Extent Scan Fragmentation ...................: 42.67%
    - Avg. Bytes Free per Page.....................: 724.9
    - Avg. Page Density (full).....................: 91.04%
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.


    Here is the test results for the select COUNT(*) from table with (nolock) statement:

    SQL Server parse and compile time:
    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:
    CPU time = 0 ms, elapsed time = 0 ms.
    SQL Server parse and compile time:
    CPU time = 0 ms, elapsed time = 63 ms.

    SQL Server Execution Times:
    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:
    CPU time = 0 ms, elapsed time = 0 ms.

    -----------
    11100938

    (1 row(s) affected)

    Table 'CADU_DataSetting_1'. Scan count 1, logical reads 2721047, physical reads 118, read-ahead reads 2708559.

    SQL Server Execution Times:
    CPU time = 41312 ms, elapsed time = 336130 ms.

    SQL Server Execution Times:
    CPU time = 41312 ms, elapsed time = 336130 ms.

    SQL Server Execution Times:
    CPU time = 41312 ms, elapsed time = 336130 ms.
    SQL Server parse and compile time:
    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:
    CPU time = 0 ms, elapsed time = 0 ms.




    Thanks in advance,

    Fernando Toledo
  2. Luis Martin Moderator

    What about execution plan?. May be you need and index.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


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



  3. kpayne New Member

    Are you able to change the COUNT(*) query? If you aren't then you are SOL. COUNT(*) is a table or clustered index scan, no matter what indexes you have. If you are able to change the COUNT(*) query, pick the smallest column in the table, create a non-clustered index on that column, and use that column in the COUNT() instead of *.

    Keith Payne
    Technical Marketing Solutions
    www.tms-us.com
  4. 7013d0 New Member

    quote:Originally posted by LuisMartin

    What about execution plan?. May be you need and index.

    Oh, my bad! I forgot to post it!

    The plan is like this:


    Select Compute Scalar Stream Aggregate Clustered Index Scan
    Cost 0% <--- Cost 0% <--- Cost 0% <=== Cost 100%
    Row Count 11,100,982
    Est Row Size 833
    IO Cost 2011
    CPU Cost 12
    Cost 2023
    Estim. Row Count 1


    Thanks,

    Fernando Toledo
  5. 7013d0 New Member

    quote:Originally posted by kpayne

    Are you able to change the COUNT(*) query? If you aren't then you are SOL. COUNT(*) is a table or clustered index scan, no matter what indexes you have. If you are able to change the COUNT(*) query, pick the smallest column in the table, create a non-clustered index on that column, and use that column in the COUNT() instead of *.


    I don't think so! Everything is inside the app and I don't have its source code! This "thing" doesn't use a single stored procedure!

    Just a doubt... No matter what, a count() will always perform a scan?
    If I could change the count(*) to a count(Col_1) and create an nonclustered index on this column its still going to perform a scan, but in the nonclustered index, right? There is no other way to speed up an select count?


    Thanks,

    Fernando Toledo
  6. kpayne New Member

    quote:Originally posted by 7013d0


    quote:Originally posted by kpayne

    Are you able to change the COUNT(*) query? If you aren't then you are SOL. COUNT(*) is a table or clustered index scan, no matter what indexes you have. If you are able to change the COUNT(*) query, pick the smallest column in the table, create a non-clustered index on that column, and use that column in the COUNT() instead of *.


    I don't think so! Everything is inside the app and I don't have its source code! This "thing" doesn't use a single stored procedure!

    Just a doubt... No matter what, a count() will always perform a scan?
    If I could change the count(*) to a count(Col_1) and create an nonclustered index on this column its still going to perform a scan, but in the nonclustered index, right? There is no other way to speed up an select count?


    Thanks,

    Fernando Toledo

    Correct. The only place that contains a separate row count for the tables are index statistics. There is a short-cut method to get the count from the index statistics, but it is not reliable. The statistics frequently become out of date.

    There will always be a scan when using COUNT(). With a narrow non-clustered index, the scan will be very fast. The data - and the clustered index - occupy 2714964 pages, which all have to be retreived from disk or the cache to get the count. An index on a non-nullable bit column, for instance, will only occupy approx. 1355 pages.

    Keith Payne
    Technical Marketing Solutions
    www.tms-us.com
  7. 7013d0 New Member

    Thanks very much for your reply, Keith and Luis!

    Really helpful!


    Regards,

    Fernando Toledo
  8. joechang New Member

    ???,
    just because you don't have code doesn't mean you can't add an index to this table.

    i gather this is a 2GB table with a primary key clustered,
    all you have to do is create nonclustered index, which will include the cluster key,
    then a SELECT COUNT(*) WITH NOLOCK query can use the nonclustered index instead of the clustered index, leading to a lower cost NC index scan (20MB?)
    then you won't be hitting disk on this query which turns a 41 CPU-sec into 336 sec duration
  9. Luis Martin Moderator

    "just because you don't have code doesn't mean you can't add an index to this table"

    Agree. I work allways with 3rd party software and I can't modify code, but of course indexes.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


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



  10. satya Moderator

    True, in this case PROFILER & ITW will help to some extent to determin better indexes and regular optimization jobs will take care of performance too.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  11. 7013d0 New Member

    Thanks everybody again!

    I didn't knew that a non clustered index could be useful to an count(*)...
    I made some tests in the development environment and the query cost is around 70% of the original cost.

    I had tried ITW for this count query, but it found no improvements to made.

    For maintenance on our servers, I have a job that reindex/defrag all the indexes needed weekly.


    Regards,

    Fernando Toledo

Share This Page