0

I inherited a performance investigation problem where a SQLServer 2008( and also 2012 ) database has its Data, Indexes and Log files all on a single drive D: The D: drive is actually part of a SAN. I don't have much else at this time in the way of details.

My recommendation is to have a minimum of 3 drives, 1 for Data, 1 for Indexes, 1 for Transaction Log. There is a lot more we can do here, this was just where I wanted to start as we look into all the possible problems there could be.

My intuition is even if they made more "logical disks" on the SAN and made those available to the OS and SQLServer, then we would be further ahead but I am met with complete resistance, stating that its the same thing since they are all on a SAN, so load is distributed.

Maybe they are correct, but I don't think so. I don't really care who is correct, I am trying to find a specific article or something that will clear this up one way or another. I can't seem to find the perfect answer to this.

To me, even though the single D: drive is sitting on a SAN, to the OS it is a single drive and would have a "single buffer" between the OS and the SAN, and would be a candidate for contention. I have no real experience with SAN technology so if I am wrong, I want to understand.

Thanks for any guidance.

2
  • 2
    Have you even bothered making a baseline performance analysis to find out where your problem IO wise is? And analysed the worst queries - experience tells me your problem is quite likely ridiculous abuse of the sql server and NOT the hardware. A performance data warehouse will help analysing this. Not saying the setup is perfect, but I would start with analysing what I have - IO per database file and worst queries. A SAN is generally not really OPTIMAL for sql server, but it goes quite far - and you are quite likely to have other problems.
    – TomTom
    Oct 8, 2015 at 16:36
  • Yes I am doing performance investigations and they have a number of issues going on. Bad indexes, lots of SQL parsing but also Disk Queue Length being high. I will continue to dig into what is there, I just wanted to get some specific information on this topic separate from everything else. Thanks.
    – J Cox
    Oct 8, 2015 at 16:39

2 Answers 2

0

You need to baseline performance because your usage is different to everyone else's usage.

It may be that having seperate arrays is required for your loading but in most cases splitting an existing array will give you worse performance unless you are adding disks.

But what you are asking for makes no sense because it ends up the same. If the rate of items added to queues isn't less than the rate of items taken off, it doesn't matter how many queues you have they will all fill up.

Pages of links with real results https://www.google.co.uk/search?q=sql+san+best+practice

7
  • I was asking for advice when it comes to configuring a SAN and presenting is as a single drive and then using it for RDBMS and some concrete facts as to why that is not a good idea. I am not a SAN expert or System Admin person, just someone who had inherited that configuration. They have since reconfigured the single SAN into several "logical disks" with none of the logical disks sharing any physicals drives in the configuration and are now getting much better performance
    – J Cox
    Dec 20, 2015 at 2:02
  • @JCox lol if you say so. The reason why you found no evidence is because san best practices and most people's experience says otherwise. But if it worked for you it must have been in a terrible state. More likely it was fragmented to hell and moving drives to any config would have given a temporary boost, but what the hey, its your problem.
    – JamesRyan
    Dec 20, 2015 at 2:34
  • I am not sure what hard discovered reality I turned down, or really what point you are making. Best practices for RDBMS is to have separate physical drives for Data, Log and Temp at bare minimum. What I was given was a single drive, but sitting on an SAN. The article showed, at least to me, that is could be possible for parts of the Log and the Data for example to exist on the same physical disk within the SAN which is against best practices for RDBMS. When they reconfigured their SAN from one large logical disk into 4 smaller physically separate logical disks, they got better performance.
    – J Cox
    Dec 20, 2015 at 3:02
  • Could there be other factors, sure there could. I came here seeking knowledge, I don't think I ignored any and if I did please elaborate. This question received no answers and over the weekend I found the article I referenced, so I thought I would post it.
    – J Cox
    Dec 20, 2015 at 3:02
  • @JCox Best practices that requires seperate drives are from when the drives were drives and not sans. That info is outdated. You've ignored everything contrary to the opinion you came here with.
    – JamesRyan
    Dec 20, 2015 at 19:23
-1

http://blogs.technet.com/b/rycampbe/archive/2011/08/23/virtualization-the-san-and-why-one-big-raid-5-array-is-wrong.aspx

I found this link and it seems to answer the question enough for my purposes. I believe I know intuitively why is it not a good idea but could not find a "cant be argued with" article, and I don't have access or permission to an environment that would let me setup the experiment.

3
  • Please include the essential points of the article and include the link only as reference. Questions and answers on this site are supposed to be written such that they can still be understood if all the links are dead.
    – kasperd
    Dec 19, 2015 at 23:41
  • If you are pointing them all to the same san, the requests all end up in the same raid controller queue anyway. Also the article says "I’ve fudged the numbers a little bit to make my point." so not hugely reliable.
    – JamesRyan
    Dec 19, 2015 at 23:55
  • @kasperd. Thanks for the advice when using links and needing to provide more detail in the event they die. I will make sure to do that in the future.
    – J Cox
    Dec 20, 2015 at 1:31

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .