Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 # of SPIDs, Blocking, ++

Author  Topic 

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-04-28 : 19:47:43
When I check the activity on my production database I see a highly variable number of spids when I look at the current activity from EM. I have a few blocking-problems in this database every now and then and I have noticed that the blocking usually occurs when there are alot of spids. Right now I have 12 spids and I find it highly unlikely that any blocking will occur, while earlier today I must have had at least 50 spids and then I also had some blocking-problems. The thing that puzzles me is that the problems usually don't occur during peak-hours but in times with only moderate traffic. What I would like to know is this:

1. Is it really the case that more spids increase the chance of blocking and if so, what can I do to reduce the number?

2. Why are the number of spids changing and does their IDs have any significance? The same procedures seem to be run by the same spids, allthough not consistently...

3. Is there a limit to the number of spids?

4. How do I identify the actual procedure/statement that is causing a blocking spid? The "last TSQL command batch" very often displays procedures that does not lock anything whatsoever, even though the spid has status "blocking"

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-28 : 19:51:18
1. Not necessarily so. Each spid means a connection to the database. You can't reduce the number of spids unless you limit how many can connect to the database. I doubt you want to do this.

2. The number is changing as people connect and disconnect.

3. I can't remember the number, but it's huge.

4. Run DBCC INPUTBUFFER(<spid number>) on the spid that is doing the blocking to determine what is being run.



To reduce blocking, make your transactions as short as possible, check for performance bottlenecks in the hardare using PerfMon, run SQL Profiler to determine what the longest running queries are, make sure your database is properly indexed, don't allow reports to run on the production database, etc...

Tara
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-04-28 : 19:59:05
Ok, regarding 4: will running DBCC INPUTBUFFER(<spid number>) be the same as looking at the properties for the spid and would the the two methods display the same sql/batch? And another thing, when a spid has status "blocking" I assume it is unable to take on other sql-statements/batches until the problem has been resolved...right?
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2004-04-29 : 05:20:16
Add this to the master database or to an admin database
http://www.nigelrivett.net/sp_nrSpidByStatus.html

When you run sp_nrspidbystatus it will give a list of allspids and the commands they executed.
You can run exec sp_nrSpidByStatus 'blk' and it will give blocking and blocked spids.

Blocking spids can carry on doing things but the blocked spids are stuck until the blocking spids release the locks (commit or rollback the transaction).

Usually a lot of blocking is due to poor application design - transactions held over round trips to the server, doing more work than necessary in transactions, incorrect ordering of updates, unnecessary/innefficient aggregate queries, reporting from an oltp database, ....

By your description it sounds like there is a query that takes a long time and blocks. Consider rewriting the query or moving it off this database.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-05-04 : 12:43:20
Ok Nigel, I finally captured some blocking-data using this procedure of yours and it is almost certainly is the procedure that updates the status of my logged in users that are causing the problems. This is the SQL-that I belive to cause the problems:
UPDATE loggedin SET Active = 0
WHERE GUID IN (
SELECT GUID
FROM loggedin
WHERE DATEDIFF(mi, LoggedOut, GETDATE()) > 120
AND Active = 1
)
--> and my table definition:

CREATE TABLE [dbo].[loggedin] (
[GUID] uniqueidentifier NOT NULL ,
[UserID] [int] NOT NULL ,
[IP] [varchar] (15) NOT NULL ,
[LoggedIn] [datetime] NOT NULL ,
[LoggedOut] [datetime] NULL ,
[Active] [smallint] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[loggedin] WITH NOCHECK ADD
CONSTRAINT [PK_loggedin] PRIMARY KEY CLUSTERED
(
[GUID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[loggedin] ADD
CONSTRAINT [DF_loggedin_GUID] DEFAULT (newid()) FOR [GUID] ,
CONSTRAINT [DF_loggedin_Active] DEFAULT (0) FOR [Active]
GO
Something tells me here that I need to do some indexing and that the clustered index on the GUID might not be the best idea, but I'm really interested in hearing your opinions.

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-04 : 13:08:57
Hmmm
Put an index on Active, LoggedOut or maybe just Active
UPDATE loggedin
SET Active = 0
where LoggedOut < DATEADD(mi, -120, GETDATE()) -- guess that's what this does
AND Active = 1

Another thing you can try is move Active on to another table so that this one doesn't get updated at all by this SP.
Depends on what else you do.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-04 : 13:22:21
Nigel, an index on Active when the values are probably only 0 and 1? I don't think that the optimizer would even consider using an index due to the selectivity of the data.

Tara
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-05-04 : 13:34:14
Not sure I understand what you mean by placing Active in a different table...? What happens is this: when a user logs in to my website a record in this table is created with his userid, the time, and then a guid that is placed in a cookie on the client. Then for every page-view the user is verified using the GUID and LoggedOut is updated with the current GETDATE(). If the user hasn't clicked within the last 2 hours they have to log in again. The update-statement I pasted in here is run avery 5 minutes by the sql-agent.

If I should do this in another more efficient way I'm all ears...(hmm, eyes?)
Go to Top of Page

MuadDBA

628 Posts

Posted - 2004-05-04 : 14:47:53
quote:
Originally posted by tduggan

Nigel, an index on Active when the values are probably only 0 and 1? I don't think that the optimizer would even consider using an index due to the selectivity of the data.

Tara



In fact, if you try to put an index on a bit column, it won't let you!
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-04 : 14:49:56
The column is defined as smallint. It probably should be bit though unless he needs to store more than just 1,0, or NULL.

Tara
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-05-04 : 14:57:29
It only holds 0's and 1's so I should have made it bit instead...does it make any difference worth mentioning? Not in relation to this topic but in general, or is it just that it takes a little less space?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-04 : 15:00:51
It takes up less space. It should definitely be defined as bit.

Tara
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-04 : 16:54:19
A bit column isn't optimised as well as a smallint.
An index on the column would be useful if there weren't many 1's - which I thought would be the case in the instance. The main reason though was so that it wouldn't have to access the data pages for the filter as the index would be covering for it.
Moving the active flag to another table was for a similar reason - so that the update would not affect the loggedin table and so not block selects on it.

If this is to see whether or not the user needs to login again thenyou shouldn't run this as a scheduled job - certainly not as often as you are.
The user guid is already being verified for every page access and the guid is indexed. When this happens the loggedout time should be checked and used to see if the user is still logged in - and you can get rid of the active flag.
This means that the access will always be via the guid. You can have a scheduled process that clears up any old records but run it every hour or more likely every day when the site has low activity.

the query would be something like

update loggedin set loggedout = getdate(), @userid = userid where LoggedOut < DATEADD(mi, -120, GETDATE() and guid = @guid
if @userid is null
set @loggedin = 0
select
set @loggedin = 1



==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-05-09 : 12:36:47
Phew...been busy celebrating spring these last days so I never got around to do the updates before now. But using the method Nigel described here I actually don't need the Active-flag at all so I changed my script into something very similar (used DATEDIFF instead of DATEADD but I guess that doesn't really matter) and I disabled the job that ran every 5 minutes because I actually don't need it anymore. Every update to the LoggedOut field is now done with a ROWLOCK and since there is no updating of Active there is no excessive locking either. I have great confidence in this solution and I thank you all very much for contributing

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-09 : 18:17:29
quote:
Originally posted by crazyjoe

quote:
Originally posted by tduggan

Nigel, an index on Active when the values are probably only 0 and 1? I don't think that the optimizer would even consider using an index due to the selectivity of the data.

Tara



In fact, if you try to put an index on a bit column, it won't let you!




ummmmm, sure it will:

-- CREATE TABLE djl_test_bit_index(
-- bit_field BIT)
CREATE INDEX idx_bit_field ON djl_test_bit_index(bit_field)

Works like a charm.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-13 : 18:13:22
That was a change made - think it was with v2k but might have been earlier.
Think nulls were allowed in v7 too.
They'vr been changing the way bit datatypes are stored to allow this sort of thing.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
   

- Advertisement -