asked
Is Performance improved better by Logs filegroup or Indexes filegroup?
What's a better improvement in performance (SQL Server 2008 R2) - partition indexes to a different filegroup on a different LUN, or partition logs to a different filegroup to a different LUN?
I think that's a very large "it depends". Shooting from the hip, I think that you'd most likely see a performance gain by doing both. Usually the best practice is to partition off the log files onto their own disk/LUN anyway. Is your database used heavily for reading data? If not, then moving the indexes (are they even being used?) might not make that much of an impact. Here I think that it depends on your data access patterns as well as how you have things structured. What are you seeing that makes you pose the question? Or is this just a random question in general?
The database is an operational datastore. I agree that both indexes and logs should be put in different filegroups on different LUNS than the data files. The reason I ask is because my organization is updating applications to point to the ODS instead of multiple replications of the same tables. It will be permanent fixture in the future. The issue that makes the difference is that hardware resources are limited at this time and I am leaning toward separating the indexes because all user interaction is for reading. For the most part, writing is done as bulk inserts. There is about 50 million rows of data in the database. The largest tables are most heavily used. For now, I'm wanting to partition the clustered indexes for improving the majority of lookups. However, I'd like to partition the non-clustered indexes at some point. Thanks for your feedback.
Then I'd say that the Logs are secondary to your primary requirement - read performace from the ODS.
They should both be done as they're different animals. Logs don't end up in fiegroups and are primarily sequentially accessed by nature. This is one of the main reasons to separate them from random access data files. As for which is better it depends on what your system does and how active it is. And of course it depends on your SAN and how it has been set up.
A large number of disks, splitting DATA and INDEX will show you probably no visible and possibly no measurable gain. For those that don't have a large number of disks, this could still be a solution to explore. By at least splitting them, even if you start with a single I/O path, you have the ability to split the I/O at a later time.
Splitting your data across multiple different spindles for better read performance is key for VLDB's. Having multiple log files in my expereince does not help with performance. The only time I have seen a benifit from multiple logs is related to disk space limitations on a legacy system. As others have mentioned, splitting your log file from your data files is key as well. Multiple file groups with indexes split across them will help if your storage is configured properly. If your SAN admin is simply giving you another lun of the same set of disk, well that is a whole different issue. You will need to measure your throughput of any disk you get to see if you are truly getting more throughput across multiple disk than just the one for data.
This article although no directly related to the question gives some interesting insight into the setup of the log file particularly around fragmentation and excess VLF generation.
http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/. Also this article discusses physical disk formatting options for SQL Server.
http://msdn.microsoft.com/en-us/library/dd758814(v=sql.100).aspx Used both articles in a new setup and achieved between 5% and 40% improvement on the run duration of certain transactions.
14 People are following this question.
