Production SQL Server 2008 performance is far below expectations. Simple single-table queries sometimes are working for 5 seconds in the mid-day compared to 250ms after hours. We cannot find which resource is the bottleneck when load increases.
Usual advices about performance tuning don't help. CPU is not overloaded (about 30%), page life expectancy is about 1.5 hours; IO queues are almost always empty. There is a suspicious record leading
sys.dm_os_wait_stats view by wait_time_ms:
LATCH_EX. And the largest latch (4 times larger than second largest) is
NESTING_TRANSACTION_FULL. Which is described as "internal use only".
NESTING_TRANSACTION_FULL statistic mean anything? And could it give us a lead to diagnose problems with its configuration, hardware, application etc?