Your own SPID

Question

Post reply

Your own SPID

idiescreaming

SSC Enthusiast

Points: 173
More actions
January 8, 2010 at 1:21 am

#219085

Hello,
How do you find out your own SPID?

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

sql_lock SSCrazy Eights Points: 9373 More actions · Answer 1

If your talking about within SSMS then it is displayed at the bottom of each query window you open.

Or you can run SELECT @@SPID

idiescreaming SSC Enthusiast Points: 173 More actions · Answer 2

I am creating a C# .NET web application that displays SPIDs that are

causing locks and let users to kill selected spids.

I just wanted hide their spid to prevent users from getting SQLExceptions caused by trying to delete their own spid.

SELECT @@SPID Seems to work fine for me!

Thank you!

tosscrosby SSCoach Points: 18045 More actions · Answer 3

idiescreaming (1/8/2010)
I am creating a C# .NET web application that displays SPIDs that are
causing locks and let users to kill selected spids.

Are you sure this is something you want the users to be able to do? This can be very dangerous. Correcting the process(s) that is causing the locks would be the most appropriate solution, not allowing the users to kill SPIDs and maybe cause corruption or inconsistencies at some point. One question, are you a DBA or developer (or some combination of the two)?

-- You can't be late until you show up.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

idiescreaming (1/8/2010)
I am creating a C# .NET web application that displays SPIDs that are
causing locks and let users to kill selected spids.

That's an extremely bad idea if the users can just kill spids without knowing what those connections are doing, which it seems they don't. Killing a connection is not something that should just be done whenever there are lock. What if the connection that they kill is the HR department updating your salary and bonus (as an example)

If you have blocking problems, you need to identify the queries that are causing the long-duration locks and do some optimisation. This can be changing the query, it can be tuning indexes, it's usually both.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

idiescreaming SSC Enthusiast Points: 173 More actions · Answer 5

Terry and Gail, Thank you for the reply

My role is Systems Engineer developing web apps mainly in C# .NET.

As both of you pointed out that killing SPIDs is not a good idea and I do understand why, is there a way to safely unlock the process using query other than killing SPIDs?

My client's request was to unlock a process that is locking the DB from the web application by clicking a button.

This is the renovation of the previous project and was created to transfer DB between different servers using SSIS packages(DB Transfer Tasks).

This application is introversion so no random user accesses

It needs to be kicked off from the screen.mmm

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

idiescreaming (1/11/2010)
As both of you pointed out that killing SPIDs is not a good idea and I do understand why, is there a way to safely unlock the process using query other than killing SPIDs?

No. The SQL database supported the ACID requirements. That means atomicity, isolation, consistency, durability. Locks are used to ensure isolation, that a process cannot affect what another process is doing; and consistency, that the database appears transactionally consistent at all times. If it were possible for one connection to revoke locks that another had places, isolation (and consistency) would be out of the window

My client's request was to unlock a process that is locking the DB from the web application by clicking a button.

This implies that you're having locking problems. Is that the case? Usually, locking problems are as a result of poorly written queries and/or inadequate indexing. I strongly suggest you look at that and have a discussion with the client regarding why it's a bad idea to roll back transactions without knowing what they're doing.

Other option is to consider the DB-wide use of snapshot isolation levels. That means that readers don't take locks at all, but use row versions to get consistent results. This means that there's very little locking (writers still lock).

The downside is that this really impacts TempDB.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

idiescreaming SSC Enthusiast Points: 173 More actions · Answer 7

This implies that you're having locking problems. Is that the case? Usually, locking problems are as a result of poorly written queries and/or inadequate indexing. I strongly suggest you look at that and have a discussion with the client regarding why it's a bad idea to roll back transactions without knowing what they're doing.

Sorry but I am not really sure of what causing the locks and what type of locks are occurring. I'll be visiting the client tomorrow to ask several questions about this.

What if they know exactly what each processes are doing?

Would that still be no good to kill a process?

I will update with more info tomorrow.

Thank you

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

idiescreaming (1/12/2010)
What if they know exactly what each processes are doing?

Know and understand. That means seeing what the currently executing SQL is, what the currently executing stored proc is and where that fits in with the application and knowing exactly what the consequences of rolling that back are. Not trivial unless the person is very familiar with the app design and code and knows SQL.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

homebrew01 SSC Guru Points: 55548 More actions · Answer 9

Follow Gail's advice and find out the cause.

Think of a traffic jam in the city. You are suggesting to eliminate the traffic jam by using a crane to lift out vehicles stuck in traffic and toss them in the river. Do you care about the contents of the cars & trucks transporting people & material ?

Gail is suggesting to look at the traffic lights to see if they are timed correctly. Or maybe alternate roads are blocked and need to be fixed, or a truck is stalled and causing a blockage ....

When you see spids involved in blocking, use DBCC inputbuffer (spid#) to get an idea of what is being run. It may point you to stored procedures or ad hoc queries. You can then dig into the code with execution plans to track down the cause. Maybe a long running select could use NOLOCK, or there are missing indexes .... Performance tuning is a big area.

AmolNaik SSCarpal Tunnel Points: 4767 More actions · Answer 10

Great ! Nice and true representation of real life blocking and locking scenario 🙂

Amol Naik

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

homebrew01 (1/12/2010)
Maybe a long running select could use NOLOCK,

Aaahhhhh!!

Why does everyone recommend nolock any time there's a long running select? How about tuning the query, tuning the index and maybe switching to snapshot isolation? Or do people like recommending methods that can return incorrect information?

See - http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

homebrew01 SSC Guru Points: 55548 More actions · Answer 12

GilaMonster (1/14/2010)
homebrew01 (1/12/2010)
Maybe a long running select could use NOLOCK,
Aaahhhhh!!
Why does everyone recommend nolock any time there's a long running select? How about tuning the query, tuning the index and maybe switching to snapshot isolation? Or do people like recommending methods that can return incorrect information?
See - http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx

Yes, my bad. I was thinking at least it's better than the OPs imminent plan to kill spids. But they should take the time to fix the problems.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

Better, yes, but still not a good thing to recommend without at least mentioning what nolock really means.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 14

I totally agree with Gail. Find the root cause and fix that.

Killing spids will come back to haunt you. At some point, something bad (maybe really bad) is likely to happen.

And it is likely to get worse over time.

You would be the one remembered because you created and deployed the button.

No one may even know who told you to build it.

Greg E