Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a very active database server with a ecclectic collection of applications running on it. Two of the busiest are a Laserfiche database that does document scanning and workflow processing all day long (average of around 2800 batch requests / second) and a blackberry server app that routes emails. There are also about 25 other small application databases.

We're a govt agency so we were only given the budget for a single DB server license.

Recently we were given a SAN to address disk contention issues.

So currently we have the TempDB running on it's own disk (raid 1 mirror pair), and we've moved the transaction logs and data files onto the SAN. The transaction logs were put in one logical location and the data files in another. Physically it's the same array but it's an array made up of 14 total spindles (disks) in a raid 1+0 configuration.

A pretty beefy SAN - and things are running much better. Queue lengths cut in half.

Just today we were also given another option. We could also have a 4 disk array if we needed it currently on the file server. I know having MDF and LDF on two separate arrays is generally recommended but the only way to do that in our situation would be to move either data or transaction logs off the SAN onto the 4 disk array configured as Raid 5. Keep in mind they are currently in separate logical volumes but sharing the same physical array.

Shooting from the hip I feel like having the MDFs and LDFs together on a 14 spindle raid 1+0 array is probably as good as having them separate with one on a 4 spindle raid 5 array. But, I wouldn't be here asking if I was a disk logic expert. Both options are using basically identical 15k SAS disks - i.e. each spindle is basically indentical.

So, in essence the question is. Is having MDF / LDF on a single 14 spindle array configured as raid 1+0 going to be improved by any significant margin (or at all) by moving either data or log to it's own 4 spindle raid 5 array?

Thoughts?

UPDATED INFO:

I will also note that currently average queue length on the Log volume stays pretty consistently around 0.55. The average queue length on Data volume is rarely more than 0.01 (usually 0.00)

sys.dm_io_virtual_file_stats QUERY RESULT:

<table>
 <tr>
  <td>database
  id</td>
  <td>Volume</td>
  <td>io_stall_read_ms</td>
  <td>num_of_reads</td>
  <td>avg_read_stall_ms</td>
  <td>io_stall_write_ms</td>
  <td>num_of_writes</td>
  <td>avg_write_stall_ms</td>
  <td>io_stalls</td>
  <td>total_io</td>
  <td>avg_io_stall_ms</td>
 </tr>
 <tr>
  <td>25</td>
  <td>h</td>
  <td>175086</td>
  <td>1411</td>
  <td>124</td>
  <td>69</td>
  <td>41</td>
  <td>1.6</td>
  <td>175155</td>
  <td>1452</td>
  <td>120.5</td>
 </tr>
 <tr>
  <td>16</td>
  <td>h</td>
  <td>54691</td>
  <td>748</td>
  <td>73</td>
  <td>34</td>
  <td>23</td>
  <td>1.4</td>
  <td>54725</td>
  <td>771</td>
  <td>70.9</td>
 </tr>
 <tr>
  <td>41</td>
  <td>h</td>
  <td>192255</td>
  <td>982</td>
  <td>195.6</td>
  <td>5232</td>
  <td>2142</td>
  <td>2.4</td>
  <td>197487</td>
  <td>3124</td>
  <td>63.2</td>
 </tr>
 <tr>
  <td>24</td>
  <td>h</td>
  <td>8616</td>
  <td>178</td>
  <td>48.1</td>
  <td>55</td>
  <td>22</td>
  <td>2.4</td>
  <td>8671</td>
  <td>200</td>
  <td>43.1</td>
 </tr>
 <tr>
  <td>29</td>
  <td>h</td>
  <td>54617</td>
  <td>1247</td>
  <td>43.8</td>
  <td>71</td>
  <td>41</td>
  <td>1.7</td>
  <td>54688</td>
  <td>1288</td>
  <td>42.4</td>
 </tr>
 <tr>
  <td>40</td>
  <td>h</td>
  <td>132664</td>
  <td>3158</td>
  <td>42</td>
  <td>184</td>
  <td>85</td>
  <td>2.1</td>
  <td>132848</td>
  <td>3243</td>
  <td>41</td>
 </tr>
 <tr>
  <td>15</td>
  <td>h</td>
  <td>26556</td>
  <td>763</td>
  <td>34.8</td>
  <td>33</td>
  <td>23</td>
  <td>1.4</td>
  <td>26589</td>
  <td>786</td>
  <td>33.8</td>
 </tr>
 <tr>
  <td>37</td>
  <td>h</td>
  <td>6943152</td>
  <td>500214</td>
  <td>13.9</td>
  <td>310706380</td>
  <td>9083273</td>
  <td>34.2</td>
  <td>317649532</td>
  <td>9583487</td>
  <td>33.1</td>
 </tr>
 <tr>
  <td>32</td>
  <td>h</td>
  <td>14832</td>
  <td>426</td>
  <td>34.7</td>
  <td>42</td>
  <td>38</td>
  <td>1.1</td>
  <td>14874</td>
  <td>464</td>
  <td>32</td>
 </tr>
 <tr>
  <td>43</td>
  <td>h</td>
  <td>46937</td>
  <td>947</td>
  <td>49.5</td>
  <td>1203</td>
  <td>844</td>
  <td>1.4</td>
  <td>48140</td>
  <td>1791</td>
  <td>26.9</td>
 </tr>
 <tr>
  <td>17</td>
  <td>h</td>
  <td>23166</td>
  <td>786</td>
  <td>29.4</td>
  <td>260</td>
  <td>125</td>
  <td>2.1</td>
  <td>23426</td>
  <td>911</td>
  <td>25.7</td>
 </tr>
 <tr>
  <td>44</td>
  <td>h</td>
  <td>65563</td>
  <td>2968</td>
  <td>22.1</td>
  <td>451</td>
  <td>334</td>
  <td>1.3</td>
  <td>66014</td>
  <td>3302</td>
  <td>20</td>
 </tr>
 <tr>
  <td>27</td>
  <td>h</td>
  <td>73622</td>
  <td>3325</td>
  <td>22.1</td>
  <td>547</td>
  <td>483</td>
  <td>1.1</td>
  <td>74169</td>
  <td>3808</td>
  <td>19.5</td>
 </tr>
 <tr>
  <td>31</td>
  <td>h</td>
  <td>19557</td>
  <td>978</td>
  <td>20</td>
  <td>218</td>
  <td>128</td>
  <td>1.7</td>
  <td>19775</td>
  <td>1106</td>
  <td>17.9</td>
 </tr>
 <tr>
  <td>13</td>
  <td>h</td>
  <td>5238308</td>
  <td>176396</td>
  <td>29.7</td>
  <td>70537264</td>
  <td>4109175</td>
  <td>17.2</td>
  <td>75775572</td>
  <td>4285571</td>
  <td>17.7</td>
 </tr>
 <tr>
  <td>26</td>
  <td>h</td>
  <td>849303</td>
  <td>57475</td>
  <td>14.8</td>
  <td>496337</td>
  <td>19098</td>
  <td>26</td>
  <td>1345640</td>
  <td>76573</td>
  <td>17.6</td>
 </tr>
 <tr>
  <td>28</td>
  <td>h</td>
  <td>12707</td>
  <td>799</td>
  <td>15.9</td>
  <td>103</td>
  <td>62</td>
  <td>1.6</td>
  <td>12810</td>
  <td>861</td>
  <td>14.9</td>
 </tr>
 <tr>
  <td>42</td>
  <td>h</td>
  <td>28630</td>
  <td>1233</td>
  <td>23.2</td>
  <td>1139</td>
  <td>883</td>
  <td>1.3</td>
  <td>29769</td>
  <td>2116</td>
  <td>14.1</td>
 </tr>
 <tr>
  <td>14</td>
  <td>h</td>
  <td>91425</td>
  <td>5480</td>
  <td>16.7</td>
  <td>3762</td>
  <td>2470</td>
  <td>1.5</td>
  <td>95187</td>
  <td>7950</td>
  <td>12</td>
 </tr>
 <tr>
  <td>35</td>
  <td>h</td>
  <td>8253</td>
  <td>546</td>
  <td>15.1</td>
  <td>193</td>
  <td>174</td>
  <td>1.1</td>
  <td>8446</td>
  <td>720</td>
  <td>11.7</td>
 </tr>
 <tr>
  <td>12</td>
  <td>h</td>
  <td>33008</td>
  <td>1852</td>
  <td>17.8</td>
  <td>2179</td>
  <td>1335</td>
  <td>1.6</td>
  <td>35187</td>
  <td>3187</td>
  <td>11</td>
 </tr>
 <tr>
  <td>36</td>
  <td>h</td>
  <td>4322</td>
  <td>470</td>
  <td>9.2</td>
  <td>150</td>
  <td>119</td>
  <td>1.3</td>
  <td>4472</td>
  <td>589</td>
  <td>7.6</td>
 </tr>
 <tr>
  <td>6</td>
  <td>h</td>
  <td>537</td>
  <td>61</td>
  <td>8.7</td>
  <td>21</td>
  <td>22</td>
  <td>0.9</td>
  <td>558</td>
  <td>83</td>
  <td>6.6</td>
 </tr>
 <tr>
  <td>33</td>
  <td>h</td>
  <td>4172</td>
  <td>585</td>
  <td>7.1</td>
  <td>63</td>
  <td>59</td>
  <td>1.1</td>
  <td>4235</td>
  <td>644</td>
  <td>6.6</td>
 </tr>
 <tr>
  <td>30</td>
  <td>h</td>
  <td>633</td>
  <td>71</td>
  <td>8.8</td>
  <td>57</td>
  <td>38</td>
  <td>1.5</td>
  <td>690</td>
  <td>109</td>
  <td>6.3</td>
 </tr>
 <tr>
  <td>10</td>
  <td>h</td>
  <td>479</td>
  <td>65</td>
  <td>7.3</td>
  <td>63</td>
  <td>22</td>
  <td>2.7</td>
  <td>542</td>
  <td>87</td>
  <td>6.2</td>
 </tr>
 <tr>
  <td>38</td>
  <td>h</td>
  <td>635</td>
  <td>70</td>
  <td>8.9</td>
  <td>54</td>
  <td>40</td>
  <td>1.3</td>
  <td>689</td>
  <td>110</td>
  <td>6.2</td>
 </tr>
 <tr>
  <td>19</td>
  <td>h</td>
  <td>867</td>
  <td>125</td>
  <td>6.9</td>
  <td>133</td>
  <td>40</td>
  <td>3.2</td>
  <td>1000</td>
  <td>165</td>
  <td>6</td>
 </tr>
 <tr>
  <td>20</td>
  <td>h</td>
  <td>2157</td>
  <td>342</td>
  <td>6.3</td>
  <td>107</td>
  <td>37</td>
  <td>2.8</td>
  <td>2264</td>
  <td>379</td>
  <td>6</td>
 </tr>
 <tr>
  <td>18</td>
  <td>h</td>
  <td>655</td>
  <td>84</td>
  <td>7.7</td>
  <td>109</td>
  <td>44</td>
  <td>2.4</td>
  <td>764</td>
  <td>128</td>
  <td>5.9</td>
 </tr>
 <tr>
  <td>9</td>
  <td>h</td>
  <td>22017</td>
  <td>1269</td>
  <td>17.3</td>
  <td>12187</td>
  <td>4510</td>
  <td>2.7</td>
  <td>34204</td>
  <td>5779</td>
  <td>5.9</td>
 </tr>
 <tr>
  <td>11</td>
  <td>h</td>
  <td>1641</td>
  <td>226</td>
  <td>7.2</td>
  <td>158</td>
  <td>94</td>
  <td>1.7</td>
  <td>1799</td>
  <td>320</td>
  <td>5.6</td>
 </tr>
 <tr>
  <td>7</td>
  <td>h</td>
  <td>556</td>
  <td>66</td>
  <td>8.3</td>
  <td>40</td>
  <td>40</td>
  <td>1</td>
  <td>596</td>
  <td>106</td>
  <td>5.6</td>
 </tr>
 <tr>
  <td>20</td>
  <td>t</td>
  <td>639</td>
  <td>90</td>
  <td>7</td>
  <td>118</td>
  <td>43</td>
  <td>2.7</td>
  <td>757</td>
  <td>133</td>
  <td>5.6</td>
 </tr>
 <tr>
  <td>24</td>
  <td>t</td>
  <td>1031</td>
  <td>167</td>
  <td>6.1</td>
  <td>67</td>
  <td>29</td>
  <td>2.2</td>
  <td>1098</td>
  <td>196</td>
  <td>5.6</td>
 </tr>
 <tr>
  <td>25</td>
  <td>t</td>
  <td>1269</td>
  <td>210</td>
  <td>6</td>
  <td>111</td>
  <td>47</td>
  <td>2.3</td>
  <td>1380</td>
  <td>257</td>
  <td>5.3</td>
 </tr>
 <tr>
  <td>29</td>
  <td>t</td>
  <td>680</td>
  <td>94</td>
  <td>7.2</td>
  <td>113</td>
  <td>54</td>
  <td>2.1</td>
  <td>793</td>
  <td>148</td>
  <td>5.3</td>
 </tr>
 <tr>
  <td>30</td>
  <td>t</td>
  <td>233</td>
  <td>25</td>
  <td>9</td>
  <td>145</td>
  <td>47</td>
  <td>3</td>
  <td>378</td>
  <td>72</td>
  <td>5.2</td>
 </tr>
 <tr>
  <td>13</td>
  <td>t</td>
  <td>11947937</td>
  <td>55092</td>
  <td>216.9</td>
  <td>163346886</td>
  <td>33789532</td>
  <td>4.8</td>
  <td>175294823</td>
  <td>33844624</td>
  <td>5.2</td>
 </tr>
 <tr>
  <td>15</td>
  <td>t</td>
  <td>693</td>
  <td>119</td>
  <td>5.8</td>
  <td>82</td>
  <td>29</td>
  <td>2.7</td>
  <td>775</td>
  <td>148</td>
  <td>5.2</td>
 </tr>
 <tr>
  <td>16</td>
  <td>t</td>
  <td>731</td>
  <td>119</td>
  <td>6.1</td>
  <td>48</td>
  <td>29</td>
  <td>1.6</td>
  <td>779</td>
  <td>148</td>
  <td>5.2</td>
 </tr>
 <tr>
  <td>29</td>
  <td>h</td>
  <td>176</td>
  <td>34</td>
  <td>5</td>
  <td>1</td>
  <td>1</td>
  <td>0.5</td>
  <td>177</td>
  <td>35</td>
  <td>4.9</td>
 </tr>
 <tr>
  <td>18</td>
  <td>t</td>
  <td>305</td>
  <td>44</td>
  <td>6.8</td>
  <td>165</td>
  <td>52</td>
  <td>3.1</td>
  <td>470</td>
  <td>96</td>
  <td>4.8</td>
 </tr>
 <tr>
  <td>5</td>
  <td>h</td>
  <td>500</td>
  <td>75</td>
  <td>6.6</td>
  <td>60</td>
  <td>40</td>
  <td>1.5</td>
  <td>560</td>
  <td>115</td>
  <td>4.8</td>
 </tr>
 <tr>
  <td>8</td>
  <td>t</td>
  <td>462</td>
  <td>74</td>
  <td>6.2</td>
  <td>118</td>
  <td>48</td>
  <td>2.4</td>
  <td>580</td>
  <td>122</td>
  <td>4.7</td>
 </tr>
 <tr>
  <td>5</td>
  <td>t</td>
  <td>294</td>
  <td>33</td>
  <td>8.6</td>
  <td>80</td>
  <td>47</td>
  <td>1.7</td>
  <td>374</td>
  <td>80</td>
  <td>4.6</td>
 </tr>
 <tr>
  <td>3</td>
  <td>h</td>
  <td>520</td>
  <td>109</td>
  <td>4.7</td>
  <td>3</td>
  <td>4</td>
  <td>0.6</td>
  <td>523</td>
  <td>113</td>
  <td>4.6</td>
 </tr>
 <tr>
  <td>39</td>
  <td>h</td>
  <td>394</td>
  <td>62</td>
  <td>6.3</td>
  <td>77</td>
  <td>40</td>
  <td>1.9</td>
  <td>471</td>
  <td>102</td>
  <td>4.6</td>
 </tr>
 <tr>
  <td>39</td>
  <td>t</td>
  <td>124</td>
  <td>16</td>
  <td>7.3</td>
  <td>165</td>
  <td>47</td>
  <td>3.4</td>
  <td>289</td>
  <td>63</td>
  <td>4.5</td>
 </tr>
 <tr>
  <td>8</td>
  <td>h</td>
  <td>459</td>
  <td>78</td>
  <td>5.8</td>
  <td>93</td>
  <td>43</td>
  <td>2.1</td>
  <td>552</td>
  <td>121</td>
  <td>4.5</td>
 </tr>
 <tr>
  <td>41</td>
  <td>t</td>
  <td>8295</td>
  <td>470</td>
  <td>17.6</td>
  <td>4035</td>
  <td>2359</td>
  <td>1.7</td>
  <td>12330</td>
  <td>2829</td>
  <td>4.4</td>
 </tr>
 <tr>
  <td>31</td>
  <td>t</td>
  <td>637</td>
  <td>91</td>
  <td>6.9</td>
  <td>170</td>
  <td>95</td>
  <td>1.8</td>
  <td>807</td>
  <td>186</td>
  <td>4.3</td>
 </tr>
 <tr>
  <td>7</td>
  <td>t</td>
  <td>242</td>
  <td>29</td>
  <td>8.1</td>
  <td>80</td>
  <td>47</td>
  <td>1.7</td>
  <td>322</td>
  <td>76</td>
  <td>4.2</td>
 </tr>
 <tr>
  <td>22</td>
  <td>h</td>
  <td>298</td>
  <td>80</td>
  <td>3.7</td>
  <td>0</td>
  <td>1</td>
  <td>0</td>
  <td>298</td>
  <td>81</td>
  <td>3.6</td>
 </tr>
 <tr>
  <td>38</td>
  <td>t</td>
  <td>139</td>
  <td>22</td>
  <td>6</td>
  <td>115</td>
  <td>47</td>
  <td>2.4</td>
  <td>254</td>
  <td>69</td>
  <td>3.6</td>
 </tr>
 <tr>
  <td>40</td>
  <td>t</td>
  <td>1016</td>
  <td>159</td>
  <td>6.4</td>
  <td>388</td>
  <td>236</td>
  <td>1.6</td>
  <td>1404</td>
  <td>395</td>
  <td>3.5</td>
 </tr>
 <tr>
  <td>22</td>
  <td>t</td>
  <td>76</td>
  <td>18</td>
  <td>4</td>
  <td>7</td>
  <td>5</td>
  <td>1.2</td>
  <td>83</td>
  <td>23</td>
  <td>3.5</td>
 </tr>
 <tr>
  <td>44</td>
  <td>t</td>
  <td>873</td>
  <td>133</td>
  <td>6.5</td>
  <td>479</td>
  <td>263</td>
  <td>1.8</td>
  <td>1352</td>
  <td>396</td>
  <td>3.4</td>
 </tr>
 <tr>
  <td>4</td>
  <td>h</td>
  <td>10565</td>
  <td>2057</td>
  <td>5.1</td>
  <td>2840</td>
  <td>2061</td>
  <td>1.4</td>
  <td>13405</td>
  <td>4118</td>
  <td>3.3</td>
 </tr>
 <tr>
  <td>32</td>
  <td>t</td>
  <td>94</td>
  <td>18</td>
  <td>4.9</td>
  <td>72</td>
  <td>33</td>
  <td>2.1</td>
  <td>166</td>
  <td>51</td>
  <td>3.2</td>
 </tr>
 <tr>
  <td>19</td>
  <td>t</td>
  <td>116</td>
  <td>16</td>
  <td>6.8</td>
  <td>116</td>
  <td>60</td>
  <td>1.9</td>
  <td>232</td>
  <td>76</td>
  <td>3</td>
 </tr>
 <tr>
  <td>11</td>
  <td>t</td>
  <td>144</td>
  <td>18</td>
  <td>7.6</td>
  <td>181</td>
  <td>91</td>
  <td>2</td>
  <td>325</td>
  <td>109</td>
  <td>3</td>
 </tr>
 <tr>
  <td>10</td>
  <td>t</td>
  <td>69</td>
  <td>12</td>
  <td>5.3</td>
  <td>48</td>
  <td>29</td>
  <td>1.6</td>
  <td>117</td>
  <td>41</td>
  <td>2.8</td>
 </tr>
 <tr>
  <td>6</td>
  <td>t</td>
  <td>69</td>
  <td>11</td>
  <td>5.8</td>
  <td>40</td>
  <td>29</td>
  <td>1.3</td>
  <td>109</td>
  <td>40</td>
  <td>2.7</td>
 </tr>
 <tr>
  <td>1</td>
  <td>h</td>
  <td>941</td>
  <td>181</td>
  <td>5.2</td>
  <td>368</td>
  <td>356</td>
  <td>1</td>
  <td>1309</td>
  <td>537</td>
  <td>2.4</td>
 </tr>
 <tr>
  <td>9</td>
  <td>t</td>
  <td>5034</td>
  <td>394</td>
  <td>12.7</td>
  <td>9584</td>
  <td>5599</td>
  <td>1.7</td>
  <td>14618</td>
  <td>5993</td>
  <td>2.4</td>
 </tr>
 <tr>
  <td>17</td>
  <td>t</td>
  <td>1335</td>
  <td>213</td>
  <td>6.2</td>
  <td>1293</td>
  <td>1119</td>
  <td>1.2</td>
  <td>2628</td>
  <td>1332</td>
  <td>2</td>
 </tr>
 <tr>
  <td>3</td>
  <td>h</td>
  <td>21</td>
  <td>6</td>
  <td>3</td>
  <td>15</td>
  <td>13</td>
  <td>1.1</td>
  <td>36</td>
  <td>19</td>
  <td>1.8</td>
 </tr>
 <tr>
  <td>34</td>
  <td>h</td>
  <td>1140</td>
  <td>132</td>
  <td>8.6</td>
  <td>4146</td>
  <td>2921</td>
  <td>1.4</td>
  <td>5286</td>
  <td>3053</td>
  <td>1.7</td>
 </tr>
 <tr>
  <td>14</td>
  <td>t</td>
  <td>714</td>
  <td>111</td>
  <td>6.4</td>
  <td>10175</td>
  <td>6589</td>
  <td>1.5</td>
  <td>10889</td>
  <td>6700</td>
  <td>1.6</td>
 </tr>
 <tr>
  <td>42</td>
  <td>t</td>
  <td>949</td>
  <td>139</td>
  <td>6.8</td>
  <td>3420</td>
  <td>2671</td>
  <td>1.3</td>
  <td>4369</td>
  <td>2810</td>
  <td>1.6</td>
 </tr>
 <tr>
  <td>37</td>
  <td>t</td>
  <td>4006</td>
  <td>489</td>
  <td>8.2</td>
  <td>15548682</td>
  <td>9892846</td>
  <td>1.6</td>
  <td>15552688</td>
  <td>9893335</td>
  <td>1.6</td>
 </tr>
 <tr>
  <td>2</td>
  <td>f</td>
  <td>164348</td>
  <td>106669</td>
  <td>1.5</td>
  <td>178498</td>
  <td>121970</td>
  <td>1.5</td>
  <td>342846</td>
  <td>228639</td>
  <td>1.5</td>
 </tr>
 <tr>
  <td>28</td>
  <td>t</td>
  <td>1305</td>
  <td>194</td>
  <td>6.7</td>
  <td>7006</td>
  <td>5797</td>
  <td>1.2</td>
  <td>8311</td>
  <td>5991</td>
  <td>1.4</td>
 </tr>
 <tr>
  <td>45</td>
  <td>h</td>
  <td>123</td>
  <td>93</td>
  <td>1.3</td>
  <td>510</td>
  <td>373</td>
  <td>1.4</td>
  <td>633</td>
  <td>466</td>
  <td>1.4</td>
 </tr>
 <tr>
  <td>43</td>
  <td>t</td>
  <td>1243</td>
  <td>130</td>
  <td>9.5</td>
  <td>13330</td>
  <td>10836</td>
  <td>1.2</td>
  <td>14573</td>
  <td>10966</td>
  <td>1.3</td>
 </tr>
 <tr>
  <td>35</td>
  <td>t</td>
  <td>329</td>
  <td>47</td>
  <td>6.9</td>
  <td>25471</td>
  <td>19582</td>
  <td>1.3</td>
  <td>25800</td>
  <td>19629</td>
  <td>1.3</td>
 </tr>
 <tr>
  <td>27</td>
  <td>t</td>
  <td>1866</td>
  <td>294</td>
  <td>6.3</td>
  <td>12196</td>
  <td>10243</td>
  <td>1.2</td>
  <td>14062</td>
  <td>10537</td>
  <td>1.3</td>
 </tr>
 <tr>
  <td>12</td>
  <td>t</td>
  <td>13789</td>
  <td>2852</td>
  <td>4.8</td>
  <td>28818</td>
  <td>29040</td>
  <td>1</td>
  <td>42607</td>
  <td>31892</td>
  <td>1.3</td>
 </tr>
 <tr>
  <td>4</td>
  <td>t</td>
  <td>511</td>
  <td>67</td>
  <td>7.5</td>
  <td>158330</td>
  <td>130742</td>
  <td>1.2</td>
  <td>158841</td>
  <td>130809</td>
  <td>1.2</td>
 </tr>
 <tr>
  <td>34</td>
  <td>t</td>
  <td>99</td>
  <td>13</td>
  <td>7.1</td>
  <td>134764</td>
  <td>112984</td>
  <td>1.2</td>
  <td>134863</td>
  <td>112997</td>
  <td>1.2</td>
 </tr>
 <tr>
  <td>2</td>
  <td>f</td>
  <td>23</td>
  <td>147</td>
  <td>0.2</td>
  <td>17980</td>
  <td>16879</td>
  <td>1.1</td>
  <td>18003</td>
  <td>17026</td>
  <td>1.1</td>
 </tr>
 <tr>
  <td>1</td>
  <td>h</td>
  <td>54</td>
  <td>10</td>
  <td>4.9</td>
  <td>1845</td>
  <td>1761</td>
  <td>1</td>
  <td>1899</td>
  <td>1771</td>
  <td>1.1</td>
 </tr>
 <tr>
  <td>33</td>
  <td>t</td>
  <td>220</td>
  <td>33</td>
  <td>6.5</td>
  <td>746</td>
  <td>980</td>
  <td>0.8</td>
  <td>966</td>
  <td>1013</td>
  <td>1</td>
 </tr>
 <tr>
  <td>36</td>
  <td>t</td>
  <td>199</td>
  <td>27</td>
  <td>7.1</td>
  <td>26330</td>
  <td>25429</td>
  <td>1</td>
  <td>26529</td>
  <td>25456</td>
  <td>1</td>
 </tr>
 <tr>
  <td>45</td>
  <td>t</td>
  <td>5</td>
  <td>8</td>
  <td>0.6</td>
  <td>6306</td>
  <td>6142</td>
  <td>1</td>
  <td>6311</td>
  <td>6150</td>
  <td>1</td>
 </tr>
 <tr>
  <td>26</td>
  <td>t</td>
  <td>2969</td>
  <td>348</td>
  <td>8.5</td>
  <td>64557</td>
  <td>70819</td>
  <td>0.9</td>
  <td>67526</td>
  <td>71167</td>
  <td>0.9</td>
 </tr>
</table>
share|improve this question
1  
I would make sure the SAN is configured for best performance. Brent Ozar's site has a good write up on the tools he has used to do this. I have read some setups like to keep the log files on local disk arrays versus SAN, but that can depend on the disk specs of the local versus SAN. –  Shawn Melton Sep 7 '11 at 17:15
1  
I see Ozar has an upcoming webinar on SANs for DBAs... sanbasics.eventbrite.com hmmm, good timing maybe. His presentations are usually pretty good. –  RThomas Sep 7 '11 at 17:34
    
Updated answer after your queue length addition. –  Mark Storey-Smith Sep 7 '11 at 18:51
    
h: is data and t: is log? The data is a little odd, the highest read file is ~500k, the highest write is 33.8m. Guess this is the writing of blobs from your Laserfiche database, which is read compartively little? For anyone else looking at this, copy out to an HTML file, open and select all, copy/paste to excel, select all, Insert ribbon, click table. –  Mark Storey-Smith Sep 7 '11 at 20:40
1  
I caught Ozar's webcast on 24 hour PASS, missed the first 15 min though. It was very good and very informative. I believe they will be available for replay next week sometime. You should be able to check this website for updates or when they become available: sqlpass.org/24hours/fall2011 –  Shawn Melton Sep 7 '11 at 20:55

2 Answers 2

up vote 7 down vote accepted

Unfortunately this is a big ol' "it depends". Complicated further by your having so many applications, probably with wildly different IO profiles, sharing the same instance/server and arrays.

Usually you would want to isolate data and log files as the IO profiles are polar opposite. Typically weighted toward random read for data files and sequential write for logs. The log element is somewhat different in your case as you have multiple log files.

I'd start by getting an understanding of the IO the databases are consuming relative to each other.

    SELECT
        DB_NAME(fs.database_id) AS [Database Name]
      , mf.physical_name
      , io_stall_read_ms
      , num_of_reads
      , CAST(io_stall_read_ms / (1.0 + num_of_reads) AS NUMERIC(10, 1)) AS [avg_read_stall_ms]
      , io_stall_write_ms
      , num_of_writes
      , CAST(io_stall_write_ms / (1.0 + num_of_writes) AS NUMERIC(10, 1)) AS [avg_write_stall_ms]
      , io_stall_read_ms + io_stall_write_ms AS [io_stalls]
      , num_of_reads + num_of_writes AS [total_io]
      , CAST((io_stall_read_ms + io_stall_write_ms) 
        / (1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1)) AS [avg_io_stall_ms]
    FROM
        sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
    INNER JOIN sys.master_files AS mf
    ON  fs.database_id = mf.database_id
        AND fs.[file_id] = mf.[file_id]
    ORDER BY
            DatabaseName ASC, mf.physical_name
--  num_of_reads DESC
--  num_of_writes DESC  
    OPTION (RECOMPILE) ;

If you can format the output of this into a readable form and add to your question, someone can make a more educated guess at the best use of your 18 disks.

If I was doing this blind, I'd probably start off allocating:

  • 6 RAID10 for the busiest database's data files
  • 6 RAID10 for the other database's data files
  • 4 RAID10 shared Logs
  • 2 RAID1 Tempdb (your single disk configuration is a risky single point of failure)

Assuming that:

  1. the SAN hosts other arrays and therefore hot spares are available from a pool
  2. snapshot isolation isn't in use or isn't placing heavy demands on tempdb

Edit: Following update to question regarding queue lengths:

Queue length counters are oft misunderstood when evaluating SQL Server performance. One of the best analogies I've come across is from a Simon Sabin blog post, Disk Queue Length - a bit like buying Guiness.

In your case, they are so low that you might get by with half the spindles. Would still be interesting to see the other stats from the above query though.

share|improve this answer
    
+1 just for the query... I'm looking it over now, but appreciate the suggestion. One of these days I'm going to get approved for a full week of DMV training. And "wildly different profiles" is a very good way to put it. –  RThomas Sep 7 '11 at 17:42
    
While you're DMVing, try the buffer pool query in this answer. If any of the databases are particularly unruly, you could restrict their resources with Resource Governor. –  Mark Storey-Smith Sep 7 '11 at 18:01
    
I've updated my original question with queue lengths for the data vs log volumes on the san. I'll also add that our tempDB disk actually is a raid 1 (mirror pair) for the pof reason you caught. –  RThomas Sep 7 '11 at 18:06
    
I've uploaded a table as html code that contains the query results. I wasn't sure of a better way to do it. The volumns marked as T are trans log, those marked as H are data. –  RThomas Sep 7 '11 at 19:55
    
Thanks again... having spent the day monitoring performance ... the thing really is humming along nice - especially over the last single volume for everything raid 5 install. Still I got a lot of good info and education. I appreciate the advice. –  RThomas Sep 7 '11 at 22:07

First the tempdb needs to have RAID under it. When that disk fails your SQL Server will come to a stop until you replace the failed disk and get it back up and running. If no one else is using the 4 disk RAID 5 you could reconfigure that as RAID 10 and use that for the logs or the data depending on which has the lower IO needs, and leave the higher IO one on the bigger RAID set.

This all requires a good looking at the perfmon numbers that you are getting for each disk to see which one should go where.

share|improve this answer
    
Good catch... I actually mis-spoke in my original post. The tempDB is on a raid 1 (mirror pair). –  RThomas Sep 7 '11 at 19:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.