Vedran Kesegic (1/11/2013)
How many of this 12 000 connections is active (actually executing something, not just sitting there opened)?How many NUMA nodes that machine have? Did you messed with CPU affinity mask setting?
Do you have any HA solution involved (cluster, mirror, replication) ?
Do you have compressed tables/indexes,
How many sql instances do you have on that machine? How many databases are there per instance?
What are overall waiting stats on the server?
What was the output of the sp_whoisactive (WAIT column) ?
It's very hard to solve the problem with little or no information.
This requires serious investigation. Consider hiring an expert to solve this.
1- those connections are just opened we can have just 20 or 15 active
2-We haven't changed the affinity mask, we haven't change any configuration regarding NUMA,
3-We have transactional replication, but just some tables not the whole database and it is an Active/Passive Cluster
4-No compressed Tables or Indexes
5- Just one SQL Instance and 3 databases but just one database is the one in Production with at least 1500 users connected
6- top 5 waits of the server
wait_typewaiting_tasks_countwait_time_msmax_wait_time_mssignal_wait_time_ms
SLEEP_TASK36852490152.61E+09125491.44E+09
CXPACKET30237645401.09E+1024060371.35E+09
OLEDB20844140503.43E+087685720
SOS_SCHEDULER_YIELD800830129858699881318084942124
LAZYWRITER_SLEEP7985025838.53E+09540594122082789
7- This only happens when there is more than 12000 sessions opened and it is happening on this SQL version 2008 r2 enterprise as in SQL server 2005 enterprise I executed the same query under the same conditions and no CPU spike, apparently this is something related with the way Microsoft build this view on this SQL version