2

This is a bit long; but I have been working through queries figuring out where a performance issue is coming from. Appreciate you taking the time to read it.

I have an application running on a Dedicated Server which is in Turn running SQL Server 2012 @ Azure (West Europe if it matters.) My App won't run on the shared service due to functionality gaps in Azuresql. The application that is running has run on all sorts of 2012 servers in the past and I am noticing a weird performance anomaly between Azure and other non azure sql servers.

The issue is around temporary tables - variable tables v #tables again.

We are finding on Azure that variant tables the query durations are considerably longer; very simple example; run 50 times each and averaged out.

Create table #table (contactid uniqueidentifier, AnotherID uniqueidentifier)
insert into #table 
select top 100 contactid, AnotherID
from dbo.pdContacts

v

declare @table table (contactid uniqueidentifier, AnotherID uniqueidentifier)
insert into @table 
select top 100 contactid, AnotherID
from dbo.pdContacts

Profiler says the stats average out at;

            Variable Table : #Table
CPU         47 : 16
Reads       379 : 204
Writes      11 : 0
Duration    83 : 42

The same query, databases on a dedicated server and no load returned 

            Variable Table : #Table
CPU         16 : 16
Reads       873 : 898
Writes      11 : 0
Duration    5 : 15

In my very simple test the Azure Variant test is almost twice the execution duration as the #table; on stand alone equipment the variant is considerably quicker (which for small tables is consistent with how we have seen it perform.) What constrains the performance of the variant tables that is more noticeable on Azure than stand alone machines; I can manage the performance issues in the short term but would rather than have to optimise the application for azure at the expense of everyone else.

Thanks

u07ch
  • 73
  • 1
  • 9
  • I am not sure if this is an answer, but where is tempdb in all of this. In an on premises today this would ideally exist on an SSD or at least on a disk away from the databases and logs. So are you using different disks for all of this as in Azure each disk has an io limit dependant on the size of the compute (A0, S1 etc.)? – DeepFat Jan 18 '16 at 16:16
  • The server is an A6; the temp db on the azure side is on the servers temporary disk. I have applied for premium storage in the west europe data centre and are waiting for that to be approved. The server generally has 6gb of free ram. Have tried this https://blogs.msdn.microsoft.com/psssql/2014/08/11/having-performance-issues-with-table-variables-sql-server-2012-sp2-can-help/ but it didn't make a big different on my test – u07ch Jan 18 '16 at 18:32

0 Answers0