Tuesday, August 29, 2006

Plan cache gets cleared

"Blog this!", my colleague said to me, so I am. Just a simple note this time to mention that when you change the max degree of parallelism on an instance, the plan cache gets cleared. Kind of logic when you think about it because you are changing the rules for the stored plans, but nevertheless he thought it was useful info.

EXEC sp_configure 'max degree of parallelism', 0
RECONFIGURE

Tuesday, August 08, 2006

Behavior of Logins/sec

We are in the middle of migrating our production environment from SQL Server 2000 to SQL Server 2005, but during the stress tests this week I noticed something scary. The performance counter "SQL Server:General Statistics - Logins/sec" indicated a factor 1000 more for SQL 2005. I suspected a severe problem with our connection pooling but in spite of that very high number of logins, there were very little logouts/sec and the number of user connections stayed stable. Also Profiler didn't capture those logins (actual and failed ones) and the familiar sp_reset_connection showed up whenever a connection was reused.

Nothing wrong with the connection pooling luckily, but it seems that the behavior of the performance counter has changed between versions. It seems to indicate not only the actual logins, but also the reuse of connections because of connection pooling. A colleague put the matter on a forum for confirmation. Tbc?