Azure SQL "select" a query that does not display all rows
I just used SQLAzureMW (Azure Migration Tool) to migrate my SQL Server database to Azure SQL. It went through without a hitch - all my tables are there, the site is working fine, etc.
Here's what's odd: if I execute a simple statement SELECT
against my tables, I only get a few rows. I assumed they were missing, but my site uses some of these entries as if they were there. So I asked with a proposal WHERE
and BAM - they appeared. How ... what ... why doesn't my choice show me everything? This applies to many tables that I have tested.
SQL Azure
On-Premise
source to share
Consider this SELECT statement:
SELECT
SvcTimeID,
LoginName,
MeanSeconds,
MedianSeconds,
RequestCount,
StdDevSeconds,
SvcDate,
CAST (TS AS INT) AS TS
FROM dbo.SvcTime
WHERE SvcDate >= @SvcDate
If the parameter is set:
cmd.Parameters["@SvcDate"].Value = DateTime.UtcNow - new TimeSpan(31, 0, 0, 0);
Execute this statement in Azure Web Role - return, say 24 lines.
Now insert two new lines; wait at least one minute; run the expression again. Are the recently inserted rows appearing? In my case, they didn't. Note. Default value for SvcDate in the database getutcdate()
.
Move Azure SQL Database from Web Edition to Standard (S2) Edition. The lines are displayed magically.
Here's my theory. The problem was not with MS SQL Management Studio, but with SQL Azure itself, where under certain circumstances the same query will return the original rows from the cache somewhere and skip new rows in the database.
This led to the fact that all the remaining confidence I had with Azure.
source to share
At first I was scared, but I think there is an explanation for this:
If you have inserted multiple rows into connection "A" and cannot find them in other sessions, you may have an uncommitted transaction. By default, in the original SQL Server, your second connections will hang until the transaction is committed or rolled back. (Read isolation level committed)
Somehow, using the same isolation level, Azure behaves differently. I seem to work like snapshot isolation in some cases. Because of this, you can read from the table, but the results are not updated. Or maybe the lock is set differently.
To fix this problem, check sysprocesses for sessions with open_tran> 0, or just be careful when passing trans. In this example, committing to your session "A" should do this.
Good luck!
source to share