VNX - Large SQL Database Files - FAST/FAST Cache

Question

Hi All, I have a quick question. I have a VNX5300 with FAST and FAST Cache enabled. I also have SSD drives installed for FAST and FAST Cache. But it seems we are not getting the best out of them (performance analysis shows we're getting only 500 IOPs out of flash drives). I have a database server with a large database(2+ TB). I was advised that the best practice is to separate the LOG files and the Database Files which makes sense because the workloads are different (1 is random mainly reads and the other is sequential writes). I will move the database files into a separate pool and the Log files into another pool. I will enable FAST / FAST Cache on the pool which will host the database files. But my problem is that my Database Files will reside on 1 single LUN. This might be a potential problem because of the paths from the server to the storage. Is there a way that i can do something with my database so that it spans more than 1 LUN so that i have more paths to the storage and increase the performance? MS SQL Version: 2005 Thanks JS

RafaelNovo · Answer

Hi,

If you are using PP (or some multipath softwares), you will use multiple paths, even if using a single LUN.

However, disk queue will be only one, and you *may* want to split in multiple luns, to take advantage of disk queues and SP balance. But only if you really have a high io load.

Depending on your DB schema, you can disk SQL File Groups to split your DB.

JamesBEMC · Answer

Hi John,

Exactly along the lines of what Rava has said.

Is this database used for online transaction processing (OLTP), i.e. transactional support, or is used for Analytics / Data Warehousing?

If it is OLTP then bandwidth will not be an issue, and it may be coming down to the performance of that particular pool/Fast Cache. I suspect you are ready IOPS to the FastVP Flash drives in the pool, and perhaps not the IOPS from the FastCache drives?

Questions:

1) What is the utilization of the FastVP Flash drives in Unisphere NAR when you see the 500IOPS?

2) What is the latency from a host perspective for that given LUN?

3) How many Database data files make up that 2TB+ Database?

3a)If it is only one, are you seeing significant page-level, file-level locking then blocking? (GAM/SGAM allocations in SQL Server 2005).

Cheers

James.

Strebeld · Answer

Also if your worried about performance you may think about splitting out TEMPDB into it's own pool of disk and using File groups.

Microsoft

Was this post helpful?