Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Provisioning SQL disk on a Compellent SAN Expand / Collapse
Author
Message
Posted Wednesday, April 21, 2010 8:08 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, January 2, 2014 1:54 PM
Points: 396, Visits: 246
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
Post #907747
Posted Thursday, October 21, 2010 11:26 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, November 17, 2014 7:48 AM
Points: 84, Visits: 913
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.

Post #1008682
Posted Thursday, October 21, 2010 11:36 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 2, 2013 6:56 AM
Points: 18, Visits: 120
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.
Post #1008966
Posted Thursday, October 28, 2010 1:50 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 2:15 PM
Points: 258, Visits: 1,103
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
Post #1012622
Posted Thursday, May 26, 2011 10:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 15, 2012 4:29 PM
Points: 2, Visits: 134
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?
Post #1115662
Posted Friday, May 27, 2011 5:07 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, July 18, 2011 3:54 AM
Points: 492, Visits: 481
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
Post #1116139
Posted Friday, May 27, 2011 12:20 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 2:15 PM
Points: 258, Visits: 1,103
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
Post #1116455
Posted Friday, May 27, 2011 12:36 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 12:50 AM
Points: 3,109, Visits: 11,515
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
http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(1230)-tempdb-should-always-have-one-data-file-per-processor-core.aspx






Post #1116465
Posted Friday, May 27, 2011 4:11 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:14 PM
Points: 4,363, Visits: 9,545
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
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1116548
Posted Friday, May 27, 2011 6:39 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 2:15 PM
Points: 258, Visits: 1,103
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
Post #1116572
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse