gmamata7 (8/31/2009)
how we will know, if there is a contention on the allocation pages?
You'll see it as latch waits, typically on either resource 2:1:2 or 2:1:3, more commonly the latter. You can check sys.dm_exec_sessions, but it's something that you'll need to monitor over a period of time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability