CODEWITHSUNDEEP
sql-serverfilegroup
user3290807
user3290807

Reputation: 391

Multiple File groups on a Virtual machine for SQL Server

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.

Upvotes: 1

Views: 700

Answers (2)

Bacon Bits
Bacon Bits

Reputation: 32180

  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.

Upvotes: 1

Greg
Greg

Reputation: 3522

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.

Upvotes: 0

Related Questions