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
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.
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
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
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
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
???, 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
"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.
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.
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
