Create own Server Busy - SQL Server Forums

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

Development Tools

Other Development Tools

Create own Server Busy

Author

Topic

Kristen
Test

22859 Posts

Posted - 2004-11-29 : 18:42:58

We've got an ASP site running with NO IIS Session. We provide a Session Cookie of our own.

My SQL server is getting very busy, and I'd like to limit the number of concurrent sessions. I want to allow sessions that are "in progress" to continue, new sessions to be limited.

If a user arrives with an existing Session Cookie I will let them in, otherwise, and if the server is busy, I will give them a Server Busy response.

I have done a google looking for general reading matter about this, but not found anything relevant. My best guess is that I put an ALERT on the server busy-ness which generates an INCLUDE file for my ASP code (setting a global "g_TooBusy = True" flag) and another process which clears this file once the server busy-ness falls.

How do I define "server too busy" and how do I weave this into my code? Any pointers welcome.

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-29 : 18:58:53

I have no idea Kristen,
so when are You getting new hardware ?

wait.. if you have a global something (session_count) that increases by one every time a new session is created,
a last_active datetime associated with each session, a process that that reduces the (session_count) by one for each
session that has not been active for the last xxx timeflops.
And have a global max_sessions to check against if session_count > max_sessions to generate the server_too_busy error ???

rockmoose

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-29 : 19:07:13

You could use a busy_vector by polling @@cpu_busy in predefined intervals and if the vector is too steep then the server is busy.
busy_vector = (@@cpu_busy - last_busy) / (@@cpu_busy.time - last_busy.time)

rockmoose

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-11-29 : 19:13:39

You could use an ASP Application Variable to store how many active sessions you have. That plus RockMoose's suggestion might do the trick.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Kristen
Test

22859 Posts

Posted - 2004-11-30 : 02:05:41

"when are You getting new hardware"

Difficult question. Client budgetted for 33% increase over last years traffic, and has elbow room to 50%. Best quess I can make based on sessions with only one page, and session created which resulted in no page (timeout etc.) is that client is actually seeing around 120~150% increase.

Client's web box has a raft of other stuff on it, so moving the web sites is not an option. SQL is on the same box (4xXeon, I forget the speed exactly, but it is 2 years old). Upgrading the processors is a PoP, and would give us around 50% more CPU. But I don't think that's worth it, or enough extra. Moving SQL to another box is pretty much a PoP too. Very few external connectors to have to adjust. Getting a the right server, and in time, is probably a bit of an issue, we have 14 days until the Christmas rush is over. I favour renting a box for two weeks - if we could rent a really appropriate box (i.e. probably expensive) it would tell us and the client how much capacity that would handle and whether we need to redesign the application for next year, or whether hardware will do the trick.

I like the "busy_vector", and will program it NOW - thanks moose ...

... and here's my test rig:


CREATE TABLE #BUSY_HISTORY
(
	T_ID		int IDENTITY(1, 1) NOT NULL,
	T_Last_Time	datetime NOT NULL,
	T_Last_CPU_BUSY	int NOT NULL,
	PRIMARY KEY
	(
		T_ID
	)
)

WHILE (1 = 1)
BEGIN
INSERT INTO #BUSY_HISTORY (T_Last_Time, T_Last_CPU_BUSY)
SELECT	GetDate(), @@cpu_busy

SELECT	TOP 10
	T1.T_ID, T1.T_Last_Time, T1.T_Last_CPU_BUSY, 
	[Vector]=(T1.T_Last_CPU_BUSY - T2.T_Last_CPU_BUSY) * 10000 
                 / DATEDIFF(Millisecond, T2.T_Last_Time, T1.T_Last_Time)
FROM	#BUSY_HISTORY T1
	JOIN #BUSY_HISTORY T2
		ON T2.T_ID = T1.T_ID-1
ORDER BY T1.T_ID DESC

WAITFOR DELAY '000:01:00'

END

She's running at around 100 at the moment - I'll let you know what the speed limit is!

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-30 : 03:30:42

You might find this interesting:
use sysperfinfo table to get statistics

rockmoose

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-11-30 : 12:10:04

Be sure to identify the bottlenecks before renting the new servers or getting new servers. No sense spending money on server resources that you don't need.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Kristen
Test

22859 Posts

Posted - 2004-12-01 : 00:30:09

Moose, that rocks! Thanks.

Thanks Michael. We are currently CPU bound at busy times, but also have unexplained "disk write storms" which can last for 30 minutes - and are made much worse because of the general slowdown and subsequent queuing that that causes.

I'll start another thread about what machine we should go for.

Kristen

Kristen
Test

22859 Posts

Posted - 2004-12-01 : 02:15:21

This query is taking about 300ms - is that going to strain anything, or can it be improved?


SELECT	[Date] = GetDate(), 
	[CPU Busy] = @@cpu_busy,
	[Page Splits/sec] = PageSplitsSec.cntr_value,
	[Buffer cache hit ratio] = BufferCacheHitRatio.cntr_value,
	[Transactions/sec] = TransactionsSec.cntr_value,
	[User Connections] = UserConnections.cntr_value,
	[Number of Deadlocks/sec] = DeadlocksSec.cntr_value,
	[Total Server Memory (KB)] = TotalServerMemory.cntr_value,
	[Target Server Memory(KB)] = TargetServerMemory.cntr_value
FROM	master..sysperfinfo PageSplitsSec (nolock),
	master..sysperfinfo BufferCacheHitRatio (nolock),
	master..sysperfinfo TransactionsSec (nolock),
	master..sysperfinfo UserConnections (nolock),
	master..sysperfinfo DeadlocksSec (nolock),
	master..sysperfinfo TotalServerMemory (nolock),
	master..sysperfinfo TargetServerMemory (nolock)
WHERE	   (PageSplitsSec.object_name = 'SQLServer:Access Methods' 
		AND PageSplitsSec.counter_name = 'Page Splits/sec' 
		AND PageSplitsSec.instance_name = '')
	AND (BufferCacheHitRatio.object_name = 'SQLServer:Buffer Manager' 
		AND BufferCacheHitRatio.counter_name = 'Buffer cache hit ratio' 
		AND BufferCacheHitRatio.instance_name = '')
	AND (TransactionsSec.object_name = 'SQLServer:Databases' 
		AND TransactionsSec.counter_name = 'Transactions/sec' 
		AND TransactionsSec.instance_name = '_Total')
	AND (UserConnections.object_name = 'SQLServer:General Statistics' 
		AND UserConnections.counter_name = 'User Connections' 
		AND UserConnections.instance_name = '')
	AND (DeadlocksSec.object_name = 'SQLServer:Locks' 
		AND DeadlocksSec.counter_name = 'Number of Deadlocks/sec' 
		AND DeadlocksSec.instance_name = '_Total')
	AND (TotalServerMemory.object_name = 'SQLServer:Memory Manager' 
		AND TotalServerMemory.counter_name = 'Total Server Memory (KB)' 
		AND TotalServerMemory.instance_name = '')
	AND (TargetServerMemory.object_name = 'SQLServer:Memory Manager' 
		AND TargetServerMemory.counter_name = 'Target Server Memory(KB)' 
		AND TargetServerMemory.instance_name = '')

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-01 : 08:02:43

> This query is taking about 300ms - is that going to strain anything, or can it be improved?

Possibly you could gather the info in a "long" table and crosstab it when you need to analyze the data.
But I guess that will not improve performance much and perhaps needlessly complicate things.

Maybe someone else here has improvement suggestions.

rockmoose

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-12-01 : 09:02:37

Since the tables aren't joined to each other...is there any way to avoid the cross-join' effect?

Kristen
Test

22859 Posts

Posted - 2004-12-01 : 12:14:45

""long table""

I did fiddle with that, to no avail, but I must have got it wrong because the following is managing it in around 75ms, which I guess is OK


DECLARE @dtStart datetime,
	@PageSplitsSec int,
	@BufferCacheHitRatio int,
	@TransactionsSec int,
	@UserConnections int,
	@DeadlocksSec int,
	@TotalServerMemory int,
	@TargetServerMemory int

SELECT	@dtStart = GetDate()
SELECT	@PageSplitsSec = CASE WHEN (object_name = 'SQLServer:Access Methods' 
		AND counter_name = 'Page Splits/sec' 
		AND instance_name = '') THEN cntr_value ELSE @PageSplitsSec END,
	@BufferCacheHitRatio = CASE WHEN (object_name = 'SQLServer:Buffer Manager' 
		AND counter_name = 'Buffer cache hit ratio' 
		AND instance_name = '') THEN cntr_value ELSE @BufferCacheHitRatio END,
	@TransactionsSec = CASE WHEN (object_name = 'SQLServer:Databases' 
		AND counter_name = 'Transactions/sec' 
		AND instance_name = '_Total') THEN cntr_value ELSE @TransactionsSec END,
	@UserConnections = CASE WHEN (object_name = 'SQLServer:General Statistics' 
		AND counter_name = 'User Connections' 
		AND instance_name = '') THEN cntr_value ELSE @UserConnections END,
	@DeadlocksSec = CASE WHEN (object_name = 'SQLServer:Locks' 
		AND counter_name = 'Number of Deadlocks/sec' 
		AND instance_name = '_Total') THEN cntr_value ELSE @DeadlocksSec END,
	@TotalServerMemory = CASE WHEN (object_name = 'SQLServer:Memory Manager' 
		AND counter_name = 'Total Server Memory (KB)' 
		AND instance_name = '') THEN cntr_value ELSE @TotalServerMemory END,
	@TargetServerMemory = CASE WHEN (object_name = 'SQLServer:Memory Manager' 
		AND counter_name = 'Target Server Memory(KB)' 
		AND instance_name = '') THEN cntr_value ELSE @TargetServerMemory END
FROM	master..sysperfinfo PageSplitsSec (nolock)
WHERE	   (object_name = 'SQLServer:Access Methods' 
		AND counter_name = 'Page Splits/sec' 
		AND instance_name = '')
	OR (object_name = 'SQLServer:Buffer Manager' 
		AND counter_name = 'Buffer cache hit ratio' 
		AND instance_name = '')
	OR (object_name = 'SQLServer:Databases' 
		AND counter_name = 'Transactions/sec' 
		AND instance_name = '_Total')
	OR (object_name = 'SQLServer:General Statistics' 
		AND counter_name = 'User Connections' 
		AND instance_name = '')
	OR (object_name = 'SQLServer:Locks' 
		AND counter_name = 'Number of Deadlocks/sec' 
		AND instance_name = '_Total')
	OR (object_name = 'SQLServer:Memory Manager' 
		AND counter_name = 'Total Server Memory (KB)' 
		AND instance_name = '')
	OR (object_name = 'SQLServer:Memory Manager' 
		AND counter_name = 'Target Server Memory(KB)' 
		AND instance_name = '')
SELECT	[Elapsed] = DATEDIFF(Millisecond, @dtStart, GetDate())

SELECT	GetDate(), 
	@@cpu_busy,
	@PageSplitsSec,
	@BufferCacheHitRatio,
	@TransactionsSec,
	@UserConnections,
	@DeadlocksSec,
	@TotalServerMemory,
	@TargetServerMemory

Kristen

Kristen
Test

22859 Posts

Posted - 2004-12-02 : 15:15:11

Can I set a Global Flag when the SQL box is busy?

I'm recording stats every minute (scheduled SProc). I'm then using a five minute average, with a threshold, to decide if the SQL box is busy. If the every-minute job did that calculation it could store a "Busy"/"Not busy" flag somewhere, rather than each "session" doing the same 5-minute-average calculation.

I could put the flag in a table, but is there somewhere "global" that would be faster to retrieve?

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-02 : 16:12:32

A new session does have to login or something to the sql server I guess.

If a sessions first login to the sql server fails with "server too busy" error, set a global ASP application variable to "server to busy", for say 30 sec. After the elapsed time, any new attempts to start new sessions can be made.

rockmoose

Kristen
Test

22859 Posts

Posted - 2004-12-03 : 02:30:12

Saves a trip to the SQL box too - good idea. I'll store the time of the next "refresh" on SQL.

(We create a Session Record on SQL Server, and store a cookie with the ID of that on the client machine. No cookie = You need a new Session Record (or you have cookies turned off). Session Record Create SProc checks the Server Busy state)

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-03 : 16:57:39

I get my share of good ideas, and probably my share of bad ideas too !
Anyway, glad if I could be of help, since ASP is one of my very weakest areas of knowledge.

Waiting to get enough time to start some ASP.NET stuff,
until recently I wouldn't lay hands on ANY "web"programming...but now I have swayed.
Primarily due to MS Avalon making Winforms "more obsolete" and ASP.NET becoming "better" and Jehrmiz postings.

Rant over, Cheers

rockmoose

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-12-03 : 17:17:40

ASP.Net is GREAT man!
I love it. So easy / fast to do very complicated things.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-03 : 17:23:13

Oh no!, now I got another reason to make the -switch- !
Thanks Michael.

rockmoose

Kristen
Test

22859 Posts

Posted - 2004-12-03 : 23:23:39

Have you registered rockmoose.net then?!

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-04 : 06:50:17

Umhh, not yet.
( but i have another one that still is "under construction" )
You know, sometimes i wish I had more time on my hands !

rockmoose