maxima


Hi All

Perfmon shows max 3900 login/sec and avg is 1500.

ASP.NET v 2.0 application uses this SQL 2005 server consists of 10 webservers. Max Pool Size on each was changed today to 300 (was default so I suppose - 100) and it didnt change logins/sec graph at all. Application uses SqlConnection object from ADO.NET v 2.0 obviously.

Can someone explain - what could be reasonable explanation - is load that high Do we need 3000 as Max Pool Size (I cant imagine what could be maximum recommended size of pool for W2003 32-bit server)

Thank you




Re: High level of login/sec

Jerome Halmans


Do logouts/sec match logins/sec If so you should have a look at profiler and ensure that you are actually utilizing connection pooling. Grab the audit login, logout events as well as RPC:Completed and look for calls to sp_reset_connection - that will indicate at least some pooling is going on.

Should you see only login and logout events with no sp_reset_connections then the app isn't using pooling, and you'll need to track down why.







Re: High level of login/sec

maxima

Thank you for the answer

Login/sec at peak-time is about 4000 at max and Logout/sec abt 50.

I will try to apply your advice today later (at peak-time) but I am not entirely sure how sp_reset_connection linked to pooling..

Also I am monitoring now Anonymous Request/sec on one of webservers. I would find it natural if requests to webapplication match Login/sec pattern.

Then I will try to look into pooling (I have NumberOfPooledConnection and NumberOfREclaimedConnection counter)

Thanx






Re: High level of login/sec

Jerome Halmans

sp_reset_connection is the procedure that clears pending results and transactions from a pooled connection. This procedure is executed every time a pooled connection is re-used.






Re: High level of login/sec

WesleyB

We are seeing a very high number of login/sec compared to SQL Server 2000 although logouts/sec remains low and the number of user connections stays stable.

The number of logins/sec match with the number of sp_reset_connection in profiler.

Has the behavior of this counter changed between versions





Re: High level of login/sec

CBColin

We are also seeing the same behavior, defined as:

(1) Very high number of Login/Sec

(2) Virtually zero Logouts/Sec

(3) Number of Active Users is stable

(4) cpu curve fairly well tracks the Login/Sec curve

(5) once the server is under load, performance starts degrading without explanation

We have approximately 12 installations of SQL2005 across three datacenters. Environment is 99% DotNet2.0. Approximately 30 webservers talk to a single cluster-instance. Mostly HP hardware clustered ontop of disk-cabinets ... one installation of Dell HW on EMC array. Behavior is the same across all installations.

We have had a ticket open with MS-support for ~3 weeks and are currently getting no where.

We are on the verge of reverting all SQL2005 installations back to SQL2000.

Any insight would be greatly appreciated.

-Colin






Re: High level of login/sec

maxima

Our problem had been solved heuristically. Company hired SQL consulter who fixed all the stuff in a week time I struggled to solve in 2 months. I cant tell what he did in particular cos that week was compared to fire fighting rather.

I would recommend the same thing plus extensive revising ADO.NET code. Most likely you are experiencing complex of problem which has to be solved with strong hand. Reverting is never sign of progress.

Sorry I cant give exact recipe.





Re: High level of login/sec

CBColin

We received an explanation on Login/Sec versus Logout/Sec from MS-support.

With the introduction of MARS in SQL2005, the engine now has the concept of sessions. So connection pooling is working properly however SQL is churning sessions. Apparently the Login/Sec counter is linked to session-creation but Logout/Sec counter is *not* linked to session-destruction ... leading to a metric discrepancy.

This also explains Login/Sec curve tracking TPS curve .... as sessions of worked are submited, cpu is consumed.

We are still having issues with throughput with SQL2005 versus SQL2000 .... the battle continues.






Re: High level of login/sec

Olof Harwell

Hi Colin!

Just want to check with if you had any success in nailing your 2005 perf. problem down. We seem to have the same behaviour when we up( )graded to 2005 but havn't really found any solution yet.

Kind Regards
Olof




Re: High level of login/sec

CBColin

I'm convinced the core problem was that we transitioned from SQL2000-Enterprise to SQL2005-Standard. The two core database-engines appear to act differently (cpu-utilization, lock-escalation).

We assumed from the marketing litature that we did not need any Enterprise features but there may be more to it then we realized.

Two other SQL2005-SP1 issues that were raging but hard to assess exact cost:

  1. security cache bloat
  2. plan cache bloat

Our environment is dynamic-sql-heavy .... Security cache would regularly grow to 1GB+ ... Plan cache regularly grew to 10GB+. We started running a batch process to clear the caches every ~10 minutes.

Subsequently we have moved to SQL2005-Enterprise-SP2 and are currently not experiencing any performance issues.

hope it helps,

cjf






Re: High level of login/sec

Sudheer_CV

Hi maxima,
Did you fully resolve the issue with the CPU and Logins/Sec
Did you start facing this issue after migrating to 2.0
What is the fix that was done
Did you change pool setting like max connections
Great if you could share your experiences..

Thanks!
sudheer




Re: High level of login/sec

Jerome Halmans

There is no difference in behavior with respect to cpu utilization and lock escalation between editions of SQL Server. The two issues you list were problems in all editions of SQL2005 prior to sp2, both have been fixed by sp2. The fix is identical for all editions.

Glad to hear that your problems are resolved.






Re: High level of login/sec

Brett Selleck

I just ran through this same issue with MS support. They pointed me to the April 2007 books on-line and the entry for the Audit Login Event Class. Here it is on MSDN: http://msdn2.microsoft.com/en-us/library/ms190260.aspx. Notice the community content that has been added at the bottom discussing the EventSubClass data column. Tracing this column will show you truely what is going on in the engine.

You can also look to the content of the sp_reset_connection SP. http://msdn2.microsoft.com/en-us/library/ms187961.aspx. It states, "The sp_reset_connection stored procedure is used by SQL Server to support remote stored procedure calls in a transaction. This stored procedure also causes Audit Login and Audit Logout events to fire when a connection is reused from a connection pool."

The Logins/Sec counter is not correct. It should now be split into two seperate counters. One to show the creation of new sessions and another to show true logins. I will be submitting a DCR to Microsoft via our TAM today.

Thanks,





Re: High level of login/sec

Tim Walker 1963

Thanks Brett, very useful post.

One of our SQL 2005 servers is reporting 1500 logins per second and less than 10 logouts. Followed your link, ran the trace suggested and sure enough the figures balance at less than 10 per second for non pooled logins only.

I'd say the Logouts/Sec should also be two counters really, the same problem applies. It's just that currently Logins\Sec is all logins and Logouts/Sec is just non pooled.

Cheers