1

In many of the SQL Server articles it is mentioned that the best practice is to use multiple File group on a physical disk to avoid disk contention and disk spindle issues .So my query are :

1:Does the same theory of having multiple file group hold true for a virtual machine ?
2:Should i still create my temp db to a different disk and should i also create multiple temp db files to avoid large read/write operation on the same temp db file in a virtual machine setup for my production environment

You recommendation and reasoning would be helpful to decide the best practice. Thanks.

user3290807
  • 381
  • 1
  • 5
  • 23

2 Answers2

1
  1. Yes, it still applies to virtual servers. Part of the contention problem is accessing the Global Allocation Map (GAM) or Shared Global Allocation Map (SGAM), which exists for each database file and can only be accessed by one process or thread at a time. This is the "latch wait" problem.
  2. If your second disk is actually on different spindles, then yes. If the database files would be on different logical disks but identical spindles, then it's not really important.

The MS recommendation is that you should create one database file for each logical processor on your server, up to 8. You should test to see if you find problems with latch contention on tempdb before adding more than 8 database files.

You do not need to (and generally should not) create multiple tempdb log files because those are used sequentially. You're always writing to the next page in the sequence, so there's no way to split up disk contention.

Bacon Bits
  • 30,782
  • 5
  • 59
  • 66
0

The question needs a bit more information about your environment. If the drives for the VM are hosted on a SAN somewhere and the drives presented to the VM are all spread across the same physical disks on the SAN then you're not going to avoid contention. If, however, the drives are not on the same physical drives then you may see an improvement. Your SAN team will have to advise you on that.

That being said, I still prefer having the log files split from the data file and tempDB being on it's own drive. The reason being that if a query doesn't go as planned then it can fill the log file drive which may take that database offline, but other databases may still be able to keep running (assuming they have enough empty space in their log files).

Again with tempDB, if that does get filled then the transaction will error out, and everything else should keep running without intervention.

Greg
  • 3,442
  • 3
  • 29
  • 50
  • Thanks for the info,i will check the configuration with SAN team since i do not have access to this information. – user3290807 Sep 12 '16 at 03:45
  • So your comment :" If the drives for the VM are hosted on a SAN somewhere and the drives presented to the VM are all spread across the same physical disks on the SAN then you're not going to avoid contention" - if this is the configuration of my environment are you recommending to have multiple database files and temp db file?. – user3290807 Sep 12 '16 at 03:49
  • Microsoft recommends multiple tempdb files regardless of the underlying hardware. If the files are still on the same drive then I don't see the benefit for other databases, but test it out and see what you get. I still find from a management point of view it's easier to have a drive for logs, a drive for data and a drive for tempdb (And a drive for Windows), but that's not for performance – Greg Sep 12 '16 at 05:26