SQL Azure is multi tenancy model with synchronize replication to 2 more copies and designed for Data integrity and safety first in mind. SQL Azure wont scale as on-premise SQL Servers. In my project i need to run millions of query within cloud (worker role -> Sql Azure). There was severe performance issue. It is not possible to re-write every query in a batch certain queries has to be executed as one at a time. Then I developed this exe to help support engineers to identify the bottle neck.
To execute this exe browse the sql file. Your sql file can contain one or more sql statement separated by go statement. From the UI fill all the connection credentials such as SERVERNAME, DATABASE (if this database is not exists then this exe will create the database at the end the database will be deleted), LOGIN and PWD. Note if LOGING and PWD is empty then the connection string will be switched to windows authentication (will work locally but not for SQL Azure (cloud database required SQL authentication) ).
Once you click the “Run” it will parse your SQL file and runs all the queries. This will create the TestLog file the log file will look like this.
2/25/2012 10:29:28 PM: Creating database my1…
Query: 1, at time:2/25/2012 10:29:28 PM, local: 820.2274, remote: 0, network: 0
Query: 1, at time:2/25/2012 10:29:31 PM, local: 163.5987, remote: 120, network: 43.5987
Query: 2, at time:2/25/2012 10:29:32 PM, local: 181.745, remote: 37, network: 144.745
Query: 3, at time:2/25/2012 10:29:32 PM, local: 49.9357, remote: 7, network: 42.9357
Query: 4, at time:2/25/2012 10:29:32 PM, local: 88.3577, remote: 40, network: 48.3577
Query: 5, at time:2/25/2012 10:29:33 PM, local: 78.0235, remote: 37, network: 41.0235
Totals: local: 00:00:01.3818880, remote: 00:00:00.2410000, network: 00:00:00.3206606
Here is how time spend on each query in your file…
LOCAL=remote (time spend on SQL Azure) +network (latency)
to download source code for this utility click here