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