Yes exactly its a contention on my datafile SGAM pages
But not in tempdb its on user db.
How I do this in prod.
Dividing into equal no of files and into different file groups if possible.
How many schema changes are you making per second?! DAAaaang. Alrighty then. The technique is simple enough, but it will require some time. You must have some incredibly serious allocation contention though before this is worthwhile, and you're not doing physical spindle splits...
In general, you do:
ALTER DATABASE <db> ADD FILE (name = <db>2, filename = <path/filename>2.ndf, size = 10485760kb, maxsize = 10485760KB, FILEGROWTH = 0KB), (name = <db>3, filename = <path/filename>3.ndf, size = 10485760kb, maxsize = 10485760KB, FILEGROWTH = 0KB), ... (repeat as necessary) ... to filegroup [PRIMARY]
Please note you have no control without filegroups to what ends up in which file. SQL Server will eventually level them out for you across the files, if memory serves correctly.I still don't recommend this
. I cannot emphasize this enough even though I'm showing you the methodology, you're the only one who can see your actual server and know your local politics. The only reason for a single filegroup like this is on tempdb where the concurrency for create/drop/alters hits a per procedure call level and you can hit contention in allocation. Under incredibly rare to never circumstances should procedures be creating actual local physical tables, nevermind at the volume needed to cause this.
- Craig Farrell
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake. For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally TablesTwitter: @AnyWayDBA