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 |
|
|
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? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-04-29 : 05:20:16
|
Add this to the master database or to an admin databasehttp://www.nigelrivett.net/sp_nrSpidByStatus.htmlWhen 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. |
|
|
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 = 0WHERE 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]GOALTER TABLE [dbo].[loggedin] WITH NOCHECK ADD CONSTRAINT [PK_loggedin] PRIMARY KEY CLUSTERED ( [GUID] ) ON [PRIMARY]GOALTER 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" |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-05-04 : 13:08:57
|
HmmmPut an index on Active, LoggedOut or maybe just ActiveUPDATE loggedin SET Active = 0where LoggedOut < DATEADD(mi, -120, GETDATE()) -- guess that's what this doesAND Active = 1Another 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. |
|
|
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 |
|
|
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?) |
|
|
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! |
|
|
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 |
|
|
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? |
|
|
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 |
|
|
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 likeupdate loggedin set loggedout = getdate(), @userid = userid where LoggedOut < DATEADD(mi, -120, GETDATE() and guid = @guidif @userid is nullset @loggedin = 0selectset @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. |
|
|
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" |
|
|
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.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
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. |
|
|
|