Azuredatastudio: Show SPID

[SebastianPfliegel]

• SQL Operations Studio Version: 0.23.6

It'd be nice to show also the current SPID for a connected query tab. The bottom line currently shows already the server and selected database.

[kburtram]

Related issue https://github.com/Microsoft/azuredatastudio/issues/910 to add SPID to status bar.

[Charles-Gagnon]

Note : Due to underlying SqlClient connection resiliency the SPID may silently change due to disconnect-reconnect. SSMS handles this by querying the SPID constantly and updating if it ever changes but that's not an ideal solution either due to increase query load and issues with that query (SELECT @@SPID can fail which causes other issues)

[anthonydresser]

Would it make more sense just to tac a spid query before each query we actually make? since the spid only matters when you are actively doing something with the server?

[Charles-Gagnon]

Doing the SELECT @@SPID query before each query we execute is an option but keep in mind that's not going to cover every case. As mentioned connection resiliency means that the connection can silently change SPIDs in the middle of execution. i.e. this scenario could happen :

1. We query SELECT @@SPID and get a value
2. We then execute a query SELECT * FROM sys.tables on the same connection
3. Something happens when sending this request (network dropped out briefly) and SqlClient silently reconnects - on the server the query is executed with a new SPID (since a new "connection" was made). From our point of view we just saw the query succeed though

If we displayed the SPID from 1. then we'd be showing the incorrect value.

I don't know of any solution to this problem - we can loop in the SqlClient folks to see if they have any suggestions on options.

One additional issue that was mentioned briefly today - this is an issue we had reported recently in SSMS (where we're basically currently doing what you're suggesting @anthonydresser)

I discovered this failure scenario while developing a new azure datawarehouse service. The code I am working on is still a little buggy, and in this case, caused @@SPID to fail. This on itself would not be a big deal, HOWEVER, as I mentioned below, it SILENTLY fails. When this occurs, and especially if this is during a user transaction, the engine will ABORT the transaction. However, back in the client, it looks like the statement succeeds. This gives the false impression that and DML operations are being done inside of the transaction, despite the fact that it no longer is. Here is a scenario:

STEP 1) BEGIN TRANSACTION
STEP 2) INSERT INTO FOO VALUES (10,10)
STEP 3) INSERT INTO FOO VALUES (11,11)
STEP 4) COMMIT
STEP 5) SELECT * FROM FOO

Assume the SSMS “SELECT @@SPID” fails during STEP 1. SSMS will issue the @@SPID query, which can fail (this can actually fail for various reasons inside the engine, including bugs, memory issues, load, etc). STEP 4 will fail by design (since the txn has aborted). However, for STEP 5, the query will return both 10 and 11 values, when in reality, none of the records should have been hardened.

Regardless of the resolution, any failure for “SELECT @@SPID” MUST be reported back to the client, so that user can take appropriate action (i.e. abort workload, etc).

[SebastianPfliegel]

Is it possible to react on those connection status changes?

[kburtram]

I've duplicated #910 against this issue. Let's make sure we consider whether to add the SPID to the titlebar and\or statusbar when addressing this issue.