April 21, 2010 at 8:08 am
Does anyone have experience with optimizing storage for a VM (vSphere) hosting SQL Server 2005/2008, using a Compellent SAN for storage?
Our SAN administrator and our Compellent rep maintain that, since there's no mapping between LUNs and physical spindles, there's no point to provisioning separate LUNs for data/log/tempdb. Is this accurate? (Note that we're using raw mapped LUNs, if that makes a difference)
I'm new with this company, so I haven't been able to do any real digging into the SAN, notably including performance profiling - and I've never worked with Compellent gear before. Also because I'm new with the company, I'm hesitant to really push too hard for disk separation if I don't have a good case for it.
I'm Googling for information, of course, but this is kind of a "this morning" situation right now, and I haven't been able to find a definitive answer as yet. I'm hoping someone here has direct experience...thanks in advance for your input.
______
Twitter: @Control_Group
October 21, 2010 at 11:26 am
Any information on Compellent’s SAN working with SQL I could really use. I have not found any information on this SAN using SQL. The rep has promised this san can even make lunch and wash clothes at the same time. Just joking but I want to get the the full story not just the sales pitch. If no one is using their product it must be a reason why.
October 21, 2010 at 11:36 pm
From a san perspective there might not be a reason to split the luns into log, data, etc. However all the Best practice we follow in the physical world should be followed in the virtual world when it comes to database servers. Virtualising the os add some benefits and a small penalty, but it is in most cases worth it. There are storage queues per lun among other thing in both vmware and in the operation system from which you will benefit splitting up data, log, etc.
Also the number of spindles are important (even though you might have solid state as tier 0), because you will do both reads and writes and scaling and io requires spindles even with solid state (it just requires less spindles for solid state)
Since the san most likely will see io as random unless you have a small environment, you will benefit from Compellents tiering.
October 28, 2010 at 1:50 pm
In addition to being my company's DBA, I'm also a certified Compellent SAN administrator.
If you want to achieve optimum performance on your database server(s), separate each of your database files (data, log, (and indexes if you have them in separate physical files)) onto a different LUN. This statement includes the TempDB.
For TempDB, place all of your TempDB data files (one per CPU core) onto a single LUN. Place your single TempDB log file onto another LUN.
The recommendations I'm giving you were worked out with the assistance of Compellent advanced tech support and myself, and has produced an outstanding high-performance environment for our database server.
To give you an idea of the level of performance we've realized, we have our real-time data acquisition and query response environment mixed with our reporting environment. This is not a best practice but is a limitation imposed by our system design. We have 6 application databases (one almost a terabyte) with our server processing up to 1,000,000 online transactions per hour, plus whatever intermittent reporting requirements are placed on top of that.
In this environment, I was able to execute the DBCC CHECKDB command against all of our databases. While it executed, the President of the company and I watched the Performance Monitor, Logical Disk:Average Disk Read Queue Length exceed a sustained rate of 62,000 read requests per second. The command executed for approximately 10 minutes before I stopped it. No one in our tech support group nor any of our clients noticed the slightest bit of degradation in system performance.
LC
May 26, 2011 at 10:35 am
Ok, so a LUN for data and a LUN for Logs, etc. What about a LUN per data file? My Db has 5 data files. Should I put each data file on it own dedicated LUN or is it a moot point and I should get back to 1 data file on a single LUN. I have read that your should have a LUN per processor. I know this is a challenge to administer, but I am trying to plan to extreme growth.
Same question about tempdb. Should tempdb be consolidated back to 1 data file instead of spread across multiple LUNS?
May 27, 2011 at 5:07 am
Create DISK of VM on different physical partition of SAN of host then you have different LUNS
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 27, 2011 at 12:20 pm
Mark Horton-381871 (5/26/2011)
Ok, so a LUN for data and a LUN for Logs, etc. What about a LUN per data file? My Db has 5 data files. Should I put each data file on it own dedicated LUN or is it a moot point and I should get back to 1 data file on a single LUN. I have read that your should have a LUN per processor. I know this is a challenge to administer, but I am trying to plan to extreme growth.Same question about tempdb. Should tempdb be consolidated back to 1 data file instead of spread across multiple LUNS?
Mark,
I have not read any advice about a LUN per processor.
What is most important from my experience and training is:
1. To have your database data files separated from your database log files. Database files are primarily accessed in random seeks based upon the requirements of the various queries that are executed. Database log files are primarily accessed in a linear, sequential record fashion. The 2 methods of access are executed most efficiently if they are separated so place a single (or multiple) database data file(s) on one LUN, its corresponding database log file on another LUN.
2. An exception to that would be the Master, Msdb, and Model database files. Accesses of these database are typically low, mostly reads. I would place all of their data and log files on the same LUN. This has worked very well for me in several different production environments.
3. The other exception is the TempDb database data and log files. I recommend that you place your TempDb database data files on one LUN. Place your TempDb database log file on another LUN. As a general rule, you should have one TempDb database data file per CPU core but there are practical limits to the efficiency of this. It's a debated subject but from what I've read, the practical limit is probably 8 different TempDb data files (less if you have fewer than 8 CPU cores). Would putting each TempDb database data file on a different LUN improve system performance? Maybe. You'll have to experiment with your server. It all depends on the volume of and types of access of your databases and how much your TempDb database is utilized.
4. A LUN per application database data file? I would consider doing this for your application database consisting of 5 physical files. Anything you can do to spread I/O across more physical spindles will speed up your system. I'm not saying for certain that it will speed up your system, but it might, depending on what parts of your database are being accessed concurrently with other accesses of the same database. There is no way to know without experimentation.
Here is a script to create multiple TempDb data files that so you don't have to research how to do it. Please make sure you understand what the query is going to do you before execute it. You'll need to execute it, then restart SQL Server for the changes to become effective:
USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE (NAME='templog' ,FILENAME='H:\templog.ldf' ,SIZE=20GB);
GO
ALTER DATABASE [tempdb] MODIFY FILE (NAME='tempdev' ,FILENAME='G:\tempdb.mdf' ,SIZE=20GB);
ALTER DATABASE [tempdb] ADD FILE (NAME='tempdev1',FILENAME='G:\tempdb1.mdf',SIZE=20GB);
ALTER DATABASE [tempdb] ADD FILE (NAME='tempdev2',FILENAME='G:\tempdb2.mdf',SIZE=20GB);
ALTER DATABASE [tempdb] ADD FILE (NAME='tempdev3',FILENAME='G:\tempdb3.mdf',SIZE=20GB);
ALTER DATABASE [tempdb] ADD FILE (NAME='tempdev4',FILENAME='G:\tempdb4.mdf',SIZE=20GB);
ALTER DATABASE [tempdb] ADD FILE (NAME='tempdev5',FILENAME='G:\tempdb5.mdf',SIZE=20GB);
ALTER DATABASE [tempdb] ADD FILE (NAME='tempdev6',FILENAME='G:\tempdb6.mdf',SIZE=20GB);
ALTER DATABASE [tempdb] ADD FILE (NAME='tempdev7',FILENAME='G:\tempdb7.mdf',SIZE=20GB);
GO
Hope that helps,
LC
May 27, 2011 at 12:36 pm
Paul Randal address the whole tempdb data file per processor core story here:
A SQL Server DBA myth a day: (12/30) tempdb should always have one data file per processor core
May 27, 2011 at 4:11 pm
Lee Crain (10/28/2010)
In addition to being my company's DBA, I'm also a certified Compellent SAN administrator.If you want to achieve optimum performance on your database server(s), separate each of your database files (data, log, (and indexes if you have them in separate physical files)) onto a different LUN. This statement includes the TempDB.
For TempDB, place all of your TempDB data files (one per CPU core) onto a single LUN. Place your single TempDB log file onto another LUN.
The recommendations I'm giving you were worked out with the assistance of Compellent advanced tech support and myself, and has produced an outstanding high-performance environment for our database server.
To give you an idea of the level of performance we've realized, we have our real-time data acquisition and query response environment mixed with our reporting environment. This is not a best practice but is a limitation imposed by our system design. We have 6 application databases (one almost a terabyte) with our server processing up to 1,000,000 online transactions per hour, plus whatever intermittent reporting requirements are placed on top of that.
In this environment, I was able to execute the DBCC CHECKDB command against all of our databases. While it executed, the President of the company and I watched the Performance Monitor, Logical Disk:Average Disk Read Queue Length exceed a sustained rate of 62,000 read requests per second. The command executed for approximately 10 minutes before I stopped it. No one in our tech support group nor any of our clients noticed the slightest bit of degradation in system performance.
LC
Not being familiar with how Compellent SAN's are configured - I have to wonder if each LUN was carved out of the SAN using separate spindles. Or, as in other SANs - is each LUN carved out of a single group of spindles?
When I worked with the HP EVA, we had a disk group with 120 spindles. From that disk group, we presented multiple LUNs to our system. Since every LUN was actually striped across all 120 drives - there really wasn't any separation of IO's and the cache on the SAN itself handled the different IO request with no issues.
For me - regardless of whether or not the LUNs are using shared disks in the SAN or not, I like separating the files. This way, it is much easier to manage and identify where you have issues - not to mention that someone writing a really bad query that explodes tempdb to fill the drive will not cause issues for the logs or data files.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 27, 2011 at 6:39 pm
Not being familiar with how Compellent SAN's are configured - I have to wonder if each LUN was carved out of the SAN using separate spindles. Or, as in other SANs - is each LUN carved out of a single group of spindles?
Compellent won't say exactly. From my observations, the data is spread across an entire tier of drives (like Fiber Channel) before it is migrated to a lower cost and speed tier of drives (like SATA) where it is also spread across the entire tier.
LC
May 27, 2011 at 6:48 pm
Michael Valentine Jones (5/27/2011)
Paul Randal address the whole tempdb data file per processor core story here:A SQL Server DBA myth a day: (12/30) tempdb should always have one data file per processor core
Been there, done that. It's one of many, many articles out there on this subject.
The truth is, just about every database server can benefit from a few TempDb data files. How many? How big? On different LUNs or disks?
It all depends. You have to experiment.
LC
July 22, 2011 at 9:32 am
I saw a demo of this the other day from a Dell rep. He claimed that Compellant's system architect said that performance tuning these tiered SANs is "as easy as clicking checkboxes and then going to drink beer".
I had one question for the Dell rep, and he did not have an answer for me since he was not a SQL Server guy. I asked him how tools that try to use heuristics to automatically discover bottlenecks, like the PALTools[/url] on Codeplex, could still work in this environment. Obviously, PALTools could still show you non-Disk bottlenecks (CPU, Memory), but I am trying to wrap my mind around how it would report on disk bottlenecks.
I am still a "Junior DBA" (really, I am a software developer who was asked to be the performance guru on our SQL Servers), so I apologize if this question is naive or stupid.
The geek in me finds this tiered data storage thing fascinating.
July 22, 2011 at 12:19 pm
johnzabroski (7/22/2011)
I saw a demo of this the other day from a Dell rep. He claimed that Compellant's system architect said that performance tuning these tiered SANs is "as easy as clicking checkboxes and then going to drink beer".I had one question for the Dell rep, and he did not have an answer for me since he was not a SQL Server guy. I asked him how tools that try to use heuristics to automatically discover bottlenecks, like the PALTools[/url] on Codeplex, could still work in this environment. Obviously, PALTools could still show you non-Disk bottlenecks (CPU, Memory), but I am trying to wrap my mind around how it would report on disk bottlenecks.
I am still a "Junior DBA" (really, I am a software developer who was asked to be the performance guru on our SQL Servers), so I apologize if this question is naive or stupid.
The geek in me finds this tiered data storage thing fascinating.
I've not used the PALTools. For the most part, I use the tools provided by Microsoft (Perfmon, Performance Dashboard) and the various UI's provided by Compellent to evaluate system and SAN performance so I can't offer you a comparison.
The Compellent UIs offer an almost overwhelming array of ways to view and evaluate SAN performance and their associated heuristics. You don't look it operations on a per spindle basis. You look at operations in other terms, like how many IOops (IO operations) your SAN can perform relative to how many IOops your SAN is executing, and how much data throughput is occurring relative to the SAN's maximum data throughput in Bytes per second, both on the front end and back end, to determine how the SAN is being used and where the stress points are.
What determines the maximum number of IOops for your SAN? Primarily the number and rotational speed of the disks you have on each Tier. For instance, if Tier 1 storage is 15K rpm Fiber Channel drives, and you have 32 of them, that will be your limiting factor, since all data is written across all 32 spindles. If you have 64 spindles in Tier 1 storage, then the number of IOops your SAN can handle is doubled (relative to the maximum number of IOops for 32 spindles). If you have 16 spindles in Tier 1 storage, then the number of IOops your SAN can handle is cut in half (relative to the maximum number of IOops for 32 spindles).
The same logic would apply to Tier 2 or Tier 3 storage (usually 7000+ rpm SATA or SAS drives), except that the disks' rotational speeds are slower than 15K rpm disks so the maximum number of IOops is lower.
Additionally, there are limitations imposed on the front end of the SAN by the maximum number of packets your iSCSI connnections can handle. There are also limitations imposed on the back end of the SAN by the speed of its internal communications.
In our production environment, these technical aspects are rarely considered because everything is so fast. The most important considerations are setting up data progression properly so that rarely used data that originally was stored on Tier 1 storage is slowly migrated to Tier 3 storage. This keeps your most frequently accessed data on your highest speed Tier and your most infrequently accessed data on your lowest speed Tier.
And, of course, you must have efficient queries and appropriate indexes to support efficient use of your SAN. THIS is still the most important aspect of system tuning that I do. If your queries are causing clustered index scans of large tables, this may appear to be a disk bottleneck when in fact it is an improper database related implementation.
I can't say that tuning a Compellent SAN is as easy as drinking beer (that's pretty easy and comes naturally to me) but it's not rocket science either. It's pretty straightforward once you understand the concepts upon which the Compellent SAN is designed and operates.
LC
July 22, 2011 at 12:30 pm
PALTools just go on top of PerfMon.
It's nothing extraordinarily fancy. What it allows is to share/save performance "gotchas" as relationships so that given new performance data, it can compare the relationships it knows to the new data. It's just automating what you probably do already.
Not familiar with Performance Dashboard. Is that a MOM (Microsoft Operations Manager) feature?
July 22, 2011 at 12:49 pm
Performance Dashboard is an add-on feature for SQL Server.
Type this into Google and you'll get a bunch of hits: "sql server performance dashboard". Pick the selection that is appropriate to your version of SQL Server.
It is one of the most useful set of tools I've ever used. I'm not sure why it is not part of the SQL Server installation.
You'll need to download it, install it, then execute the "Setup.sql" script to get it ready to run as a Custom Report in the Reports section of SQL Server Management Studio.
If you have any difficulties getting it to execute, let me know.
LC
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply