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 eachsession 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)BEGININSERT INTO #BUSY_HISTORY (T_Last_Time, T_Last_CPU_BUSY)SELECT GetDate(), @@cpu_busySELECT 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-1ORDER BY T1.T_ID DESCWAITFOR 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 |
|
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_valueFROM 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 OKDECLARE @dtStart datetime, @PageSplitsSec int, @BufferCacheHitRatio int, @TransactionsSec int, @UserConnections int, @DeadlocksSec int, @TotalServerMemory int, @TargetServerMemory intSELECT @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 ENDFROM 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, Cheersrockmoose |
|
|
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 |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-12-04 : 13:35:33
|
"You know, sometimes i wish I had more time on my hands"You would abandon SQLTeam in order to set up a personal website? Pah! Kristen |
|
|
Next Page
|