CODEWITHSUNDEEP
azureazure-sql-databasesqlperformance
chrisb
chrisb

Reputation: 1415

"select count(id) from table" takes up to 30 minutes to calculate in SQL Azure

I have a database in SQL Azure which is not taking between 15 and 30 minutes to do a simple:

select count(id) from mytable

The database is about 3.3GB and the count is returning approx 2,000,000 but I have tried it locally and it takes less than 5 seconds!

I have also run a:

ALTER INDEX ALL ON mytable REBUILD

On all the tables in the database.

Would appreciate if anybody could point me to some things to try to diagnose/fix this.

(Please skip to UPDATE 3 below as I now think this is the issue but I still do not understand it).

UPDATE 1: It appears to take 99% of the time in a clustered index scan as image below shows. I have

enter image description here

UPDATE 2: And this is what the statistics messages come back as when I do:

SET STATISTICS IO ON
SET STATISTICS TIME ON
select count(id) from TABLE

Statistics:

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 = 317037 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.

(1 row(s) affected)
Table 'TABLE'. Scan count 1, logical reads 279492, physical reads 8220, read-ahead reads 256018, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 297 ms,  elapsed time = 438004 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.

UPDATE 3: OK - I have another theory now. The Azure portal is suggesting each time I do test this simply select query it is maxing out my DTU percentage to nearly 100%. I am using a Standard Azure SQL instance with performance level S1 (20 DTUs). Is it possible that this simple query is being slowed down by my DTU limit?

Upvotes: 22

Views: 8046

Answers (4)

Alex White
Alex White

Reputation: 1516

This is an old question, but I found it while I was looking into a similar problem. If you just want to know the number of records on the table (which is what this looks like), then you could query SQL metadata as follows

SELECT row_count
FROM sys.dm_db_partition_stats
where object_id = object_id('[dbo].[mytable]') and index_id <= 1

I think this should give you the information you want but in a much more reasonable time and cost.

Upvotes: 0

paulsm4
paulsm4

Reputation: 121649

Suggestion: try select count(*) instead: it might actually improve the response time:

Also, have you done an "explain plan"?

============ UPDATE ============

Thank you for getting the statistics.

You're doing a full table scan of 2M rows - not good :(

POSSIBLE WORKAROUND: query system table row_count instead:

http://blogs.msdn.com/b/arunrakwal/archive/2012/04/09/sql-azure-list-of-tables-with-record-count.aspx

select t.name ,s.row_count from sys.tables t
join sys.dm_db_partition_stats s
ON t.object_id = s.object_id
  and t.type_desc = 'USER_TABLE'
  and t.name not like '%dss%'
  and s.index_id = 1

Upvotes: 3

Aaron Hudon
Aaron Hudon

Reputation: 5839

Quick refinement of @FoggyDay post. If your tables are partitioned, you'll want to sum the rowcount.

SELECT t.name, SUM(s.row_count) row_count
FROM sys.tables t
JOIN sys.dm_db_partition_stats s
ON t.object_id = s.object_id
  AND t.type_desc = 'USER_TABLE'
  AND t.name not like '%dss%'
  AND s.index_id = 1
GROUP BY t.name

Upvotes: 3

Lando
Lando

Reputation: 139

I realize this is old, but I had the same issue. I had a table with 2.5 million rows that I imported from an on-prem database into Azure SQL and ran at S3 level. Select Count(0) from Table resulted in a 5-7 minute execution time vs milliseconds on-premise.

In Azure, index and table scans seem to be penalized tremendously in performance, so adding a 'useless' WHERE to the query that forces it to perform an index seek on the clustered index helped.

In my case, this performed almost identical Select count(0) from Table where id > 0 resulted in performance matching the on premise query.

Upvotes: 13

Related Questions