title: SQL Azure Query Performance - Terribly Slow Even With Tuned Queries
body: <p>Here's a basic query that relies on two non-clustered indexes:</p>
  
  <pre><code>SELECT cc.categoryid, count(*) from company c
  INNER JOIN companycategory cc on cc.companyid = c.id
  WHERE c.placeid like 'ca_%'
  GROUP BY cc.categoryid order by count(*) desc
  </code></pre>
  
  <p>When the exact same database is hosted on SQL Server 2008, on virtually any hardware, this returns &lt; 500 ms. Even with the cache buffers cleared:</p>
  
  <pre><code>DBCC FREEPROCCACHE
  DBCC DROPCLEANBUFFERS
  </code></pre>
  
  <p>... this still returns in ~1 second on traditional SQL.</p>
  
  <p>On Azure, it takes approximately 3.5 seconds to return each time.</p>
  
  <p>Some <a href="http://www.brentozar.com/archive/2010/01/sql-azure-frequently-asked-questions/" rel="noreferrer">articles</a> out there seem to suggest that people are generally happy with query performance in <strong>SQL Azure</strong>. And yet here's a basic scenario where 'obvious' tuning has been exhausted and there's no network latency issues to speak of. It's just <em>really</em> slow when working w/ large tables (companycategroy has 1.2M records, places has 7.5K).</p>
  
  <p>The total database size is no more than 4GB. Selecting 'Web' edition vs. 'Enterprise' edition doesn't seem to make much of a difference either. </p>
  
  <p>What am I missing?</p>
  
  <p>This is only a basic example, it only gets worse with more sophisticated queries, all of have been <a href="http://blogs.msdn.com/b/sqlazure/archive/2010/07/27/10043069.aspx" rel="noreferrer">reviewed</a>, tuned, and perform well on-premise.</p>
  
  <p>Here's the execution plan:</p>
  
  <pre><code>  |--Sort(ORDER BY:([Expr1004] DESC))
         |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1007],0)))
              |--Hash Match(Aggregate, HASH:([cc].[CategoryId]), RESIDUAL:([XX].[dbo].[CompanyCategory].[CategoryId] as [cc].[CategoryId] = [XX].[dbo].[CompanyCategory].[CategoryId] as [cc].[CategoryId]) DEFINE:([Expr1007]=COUNT(*)))
                   |--Hash Match(Inner Join, HASH:([c].[Id])=([cc].[CompanyId]))
                        |--Index Scan(OBJECT:([XX].[dbo].[Company].[IX_Company_PlaceId] AS [c]),  WHERE:([XX].[dbo].[Company].[PlaceId] as [c].[PlaceId] like N'ca_%'))
                        |--Index Scan(OBJECT:([XX].[dbo].[CompanyCategory].[IX_CompanyCategory_CompanyId] AS [cc]))
  </code></pre>
  
  <p>And here are the stats:</p>
  
  <pre><code>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 = 14 ms, elapsed time = 14 ms.
  
  (789 row(s) affected)
  
  Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'CompanyCategory'. Scan count 1, logical reads 5183, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'Company'. Scan count 1, logical reads 8710, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  
  (1 row(s) affected)
  
   SQL Server Execution Times:
     CPU time = 3328 ms,  elapsed time = 3299 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.
  </code></pre>
  
  <p>Index definitions are as follows:</p>
  
  <pre><code>CREATE NONCLUSTERED INDEX [IX_Company_PlaceId] ON [dbo].[Company] 
  (
      [PlaceId] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
  GO
  
  CREATE NONCLUSTERED INDEX [IX_CompanyCategory_CompanyId] ON [dbo].[CompanyCategory] 
  (
      [CompanyId] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
  GO
  
  ALTER TABLE [dbo].[Company] ADD  CONSTRAINT [PK_Company_Id] PRIMARY KEY CLUSTERED 
  (
      [Id] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
  GO
  </code></pre>