1

Topic: What it is better to throw on SSD?

In what an essence, we want to buy SSD for one not so important server, the budget is restricted, now there all on the normal disks, three spot-checks 1 and one 10 spot-check. And so if to look io_virtual_stats on reading the most loaded basis at it tempdb, write stall at all 4 files (they lie together) in 3 three times above than at DB_One (we name it so), but Read in time 10 it is less than at DB_One, at which Read_stall the biggest. DB_One lies separately from tempdb, but together with DB_Two which on loading it is approximately equal DB_One.
spot-check 1.1 - system, spot-check 1.2 tepmdb with a heap of remaining small bases, spot-check 1.3 Broad gulls of all bases, spot-check 10 DB_One and DB_Two, data files. It is necessary to disassemble somehow this dump, and here we with colleagues had a dispute:
1) I suggest to buy one SSD on 512 (in tempdb it is much written to time of different loadings happens to 200-400  grows) if it departs anything terrible, them  a stop to working hours for 10-20 minutes. After transfer DB_One to transfer on spot-check 1.2 where was tempd. DB_Two weighs much and it not to transfer.
2) Colleagues offer to buy two SDD on 256, to make 1 spot-check, to thrust there DB_One and anything else not to touch.
3) Suggest to buy simply  (not a variant certainly).
I will add that 50 heaviest requests in respect of IO if to look under plans, in the majority  in temporary tables and update temporary tables.

2

Re: What it is better to throw on SSD?

I will add that on types of waitings if not to take cxpacket, Buckup* etc. remains PAGEIOLATCH and WRITELOG.  now 70 under SQL if to buy that it is 32 approximately it is possible to buy or , money only on one.
Requests clumsy to horror, but anybody will not touch anything. A question only in pieces of iron.

3

Re: What it is better to throw on SSD?

aleksrov wrote:

I suggest to buy one SSD on 512

Reasonably. It is possible on it and indexes to throw.

aleksrov wrote:

Colleagues offer, to buy two SDD on 256, to make 1 spot-check, to thrust there DB_One and anything else not to touch.

Ambiguous council. Itself did not feel, but, on hearings, SSD in a mirror like to die simultaneously. However, business dark.

4

Re: What it is better to throw on SSD?

wrote:

aleksrov;
Well I do not know... Rate is a constant copying, very intensive loading on the main resource SSD. It is possible to burn very quickly a disk, especially cheap. An order resource 3-5 cycles, then simply ungear a cell on record.

Here the quite good test budgetary SSD on reliability:
https://3dnews.ru/938764

5

Re: What it is better to throw on SSD?

aleksrov;
256 and 512GB are capacities  SSD, a resource at them in overwhelming majority of any (copying 0,1 - 0,3 volumes a day throughout a warranty period is admissible). That you on them would not suppose (from described) - it dies quickly enough.
The second moment: the important feature, among other distinguishing  SSD from  is PLP (Power Loss Protection).
Essence - protection of a cache most SSD. With disconnected WB productivity on record on SSD falls sharply and noticeably, and with - the risk of loss of volume of write cache will be included, and you do not learn, what exactly lost, do not try yet was specific to read it.
Candidate solutions the following:
The ultrabudgetary: one piece 480 Micron 5100MAX (5 DWPD - Drive Writes Per Day, volume copying in day), on it only tempdb.
: two pieces 960GB  (1 DWPD)  SSD in RAID1, and on them to suppose the first basis and tempdb: on a resource will survive at the expense of volume.
Somehow so.

6

Re: What it is better to throw on SSD?

a_shats;
, (4 + the broad gull) for days approximately 80-100  is written to 5 files. There there are constant loadings, architecture certainly strange as I understood, loading goes at first in tempdb, there the data is aggregated, where something is necessary it is considered, besides all it is loaded in temporary tables and then what part of this data is loaded in bases. Thus these temporary tables still hang any time while there are different checks. A trick in that that all these loadings go permanently, i.e. and to working hours. As they go it simply glamour too, loading is launched, at once pieces 20-25 can be, does any part, then waitfor on an hour and we wait other loading or still what   will not be fulfilled yet, on question What the fu ** developers speak type: "I the artist, I so see".
Now Avg Write Stall at files tempdb 900, whereas at principal basis around 100.
Plus these artists for what that  on everyone FK indexes. I.e.  on everyone in all tables, best practice to see. That is the table is wide, 70-80 , 40 it FK, links to different reference manuals in which practically always go only insertions, therefrom is deleted nothing practically and PK is not updated, i.e.  to the main table for check are not present, and here with these 40 FK insertions go to the table permanently, as a result we look to statistican on indexes, some hundreds thousand updates at everyone and any seek or scan. Decided to check up, on the test the insertion only with clustered occupied 10000 lines approximately 6  with a cold cache, added 40 indexes 20 with copecks of seconds on the cold. Still local administrators suggested  to create a little FG and to scatter them on different spot-checks (now all in primary) these creative people told that it is impossible so to do.

7

Re: What it is better to throw on SSD?

aleksrov;
As far as I understand, your basis is optimized for best "responsiveness" on actions of users, instead of for mass insertions.
And yes, at a failure single (not in a mirror at least) the drive the stop for 10-20 minutes happens only if at you other such drive is in a bedside table near to the server. Normal idle time in such cases - till next working day, and it at the best.

aleksrov wrote:

Still local administrators suggested  to create a little FG and to scatter them on different spot-checks (now all in primary) these creative people told that it is impossible so to do.

If at you a small amount of drives (roughly speaking - less than 8-12 pieces) - to scatter on different arrays = to select iops since calls in different DB happen not simultaneously, as a result - in one "big" RAID-group each task receives all iops all disks, and at "" on different arrays - only iops this array.

8

Re: What it is better to throw on SSD?

a_shats wrote:

it is passed...
If at you a small amount of drives (roughly speaking - less than 8-12 pieces) - to scatter on different arrays = to select iops since calls in different DB happen not simultaneously, as a result - in one "big" RAID-group each task receives all iops all disks, and at "" on different arrays - only iops this array.

+1
Always it makes sense to divide physical drives for the data and dens.
If it is a lot of drives, it is possible to select separate spot-check for tempdb.
And what to divide on , etc. is there should be absolutely many drives, hundreds-thousand.

9

Re: What it is better to throw on SSD?

alexeyvg;
I.e. for example one file on 10 spot-check from 8 will be better than two files on 2 than 10 spot-checks from 4 provided that loading on these files will be approximately equal?
Essence in that in this a DB structure such: tables 50 the cores, are wide, on everyone to fig FK, practically at each table the dial-up of reference manuals which practically always are used only by this table. As I wrote there is a set of loadings which plow consider always, the idea was in that to take some tables, to which users rarely address, but in which it is much loaded (such is) and the reference manuals connected to them and to transfer on  spot-check. It also to reduce down time when all departs, for Enterprise.

10

Re: What it is better to throw on SSD?

Ssd does not help.

11

Re: What it is better to throw on SSD?

It is necessary to do  architecture, and to rewrite the codes.

12

Re: What it is better to throw on SSD?

In databases.

13

Re: What it is better to throw on SSD?

sparrow wrote:

It is necessary to do  architecture, and to rewrite the codes.

What-such codes?
Precisely bar codes?
.
------------
Well, citizens, I congratulate all on addition.
The unipolar FET already here.
Though about what it I?
The unipolar FET for a long time already here, before it was simple implicitly

14

Re: What it is better to throw on SSD?

aleksrov wrote:

I.e. for example one file on 10 spot-check from 8 will be better than two files on 2 than 10 spot-checks from 4 provided that loading on these files will be approximately equal?

Loading is equal - means, files from different ?
Then yes, better.
Because loading is equal on the average, but not in each millisecond.
Here join in one spot-check arranges this loading on all disks with a warranty.

aleksrov wrote:

As I wrote there is a set of loadings which plow consider always, the idea was in that to take some tables, to which users rarely address, but in which it is much loaded (such is) and the reference manuals connected to them and to transfer on  spot-check. It also to reduce down time when all departs, for Enterprise.

Well, for the decision of other problems, fail safety type, it is possible so to do, but not so simply to make it because the basis without a part of files will not work all.
And generally, it is better for solving fault tolerant spot-check, presence in it of disks for hot changeover, and adjustment of system of notification messages from WASPS/IRON, instead of smart allocation of disks moreover when so it is not enough of them.

15

Re: What it is better to throw on SSD?

alexeyvg;
And all clever books speak that it is necessary to arrange on different FG and to disks, it is impossible to trust anybody sad
As you wrote above "And what to divide on , etc. is there should be absolutely many drives, hundreds-thousand.", in that case what advantage, after all turns out these hundreds also "arranges this loading on all disks with a warranty"?
Simply with iron rarely was engaged, everywhere where worked it did other department, therefore here small gaps, I will be grateful if educate.

16

Re: What it is better to throw on SSD?

"The budget is restricted"
...It is how much restricted?
What there will be no support of redundancy RAID?
If so it is necessary to take 2 SSD and to stick them in .
At me here houses so.
Two on 256. Total, half-terabyte, a transfer 950  in a second.
...If the server, all the same RAID 5 or 10, with 4-mja disks.
Well, it is necessary to understand that requirements to reliability different....
Houses, or the server...
And disks different too.
Is , type OCZ, is Intelovsky, server (and high-speed characteristics at server, by the way, it is noticeable worse. But TWPD above)
And a warranty

17

Re: What it is better to throw on SSD?

...If the basis is something of type sociological  from Gallup, which  time a day a packet  from  from OCZ rescues.
...But if basis though a little OLTP, disks

18

Re: What it is better to throw on SSD?

Makar4ik wrote:

... If the basis is something of type sociological  from Gallup, which  time a day a packet  from  from OCZ rescues.
...But if basis though a little OLTP, disks

...Disks it is necessary or more, or not such, as  OCZ for example.
It will be necessary a minimum 1 disk of enterprise grade. And it is better 4.
There not so all is tasty with the declared parameters till speeds, but chocolate with the declared parameters on copying.

19

Re: What it is better to throw on SSD?

Makar4ik wrote:

it is passed...
...Disks it is necessary or more, or not such, as  OCZ for example.
It will be necessary a minimum 1 disk of enterprise grade. And it is better 4.
There not so all is tasty with the declared parameters till speeds, but chocolate with the declared parameters on copying.

(Though, I on  OCZ work at home. To me the array produced that for 3 years I 35 times re-recorded it... These are copecks in general)

20

Re: What it is better to throw on SSD?

Makar4ik wrote:

it is passed...
(Though, I on  OCZ work at home. To me the array produced that for 3 years I 35 times re-recorded it... These are copecks in general)

(Yes forgot to tell, terabyte, 3 years, a porno I do not swing, but films occasionally. The main traffic - on operation (when it is necessary to work - films leave in wood.), the DB turning on these disks happens...)

21

Re: What it is better to throw on SSD?

Makar4ik wrote:

it is passed...
...Disks it is necessary or more, or not such, as  OCZ for example.
It will be necessary a minimum 1 disk of enterprise grade. And it is better 4.
There not so all is tasty with the declared parameters till speeds, but chocolate with the declared parameters on copying.

Intelovsky corporate disks, for example, declare any  petabytes on record for the period of a warranty.
..There is parameter TWPD
Total Writes Per Day
...It how many once a day re-records a disk.
At  it no more than 0.3
At Intelovsky server to 20.
the warranty is given by 3 years, Intel of 5 years.
Total, for  a disk:
guarantees record 0.3*0.5*365*3 = 164.25
The server - 18250 Tb.
There is a difference.
And it is clear, why in the price a difference.

22

Re: What it is better to throw on SSD?

aleksrov wrote:

alexeyvg;
And all clever books speak that it is necessary to arrange on different FG and to disks, it is impossible to trust anybody sad
As you wrote above "And what to divide on , etc. is there should be absolutely many drives, hundreds-thousand.", in that case what advantage, after all turns out these hundreds also "arranges this loading on all disks with a warranty"?
Simply with iron rarely was engaged, everywhere where worked it did other department, therefore here small gaps, I will be grateful if educate.

If basis OLAP on SSD to drag the data
If OLTP, broad gulls.
Already considered.

23

Re: What it is better to throw on SSD?

Makar4ik wrote:

it is passed...
If basis OLAP on SSD to drag the data
If OLTP, broad gulls.
Already considered.

If broad gulls climb, but the basis not everything to drag partially, group.
And  to drag the heads in server, and to stick with the person that the basis does not climb)))

24

Re: What it is better to throw on SSD?

quote Makar4ik] Total, for  a disk:
guarantees record 0.3*0.5*365*3 = 164.25 [/quote] Consumer disks in capacity 256  from the test above already passed for some petabyte.
And generally, it is nonprofessional so to speak, " ".
You not select the wife, and the obtaining tool .
It is not necessary for you that it would be good, it is necessary for you, that it  business function, and that cost  multifunctions would cost less.
I.e. it is necessary to select a disk at the minimum cost of a petabyte of a resource of record.
You consider, what  have a huge resource in comparison with , so, what cost of a petabyte of record at them several times more low, than at consumer disks?
then they also should be bought.
But it is necessary to show it a label of cost of a petabyte.
Here while from under the link above it is visible that a resource of some consumer disks of the order 8 petabyte counting on a half-terabyte disk
And at a server disk, a resource as you write, 18 petabyte
Whether there is it of all in 2 times more expensively? Or in 10?
It is necessary to consider.

25

Re: What it is better to throw on SSD?

Corrected formatting:

Makar4ik wrote:

Total, for  a disk:
guarantees record 0.3*0.5*365*3 = 164.25

Consumer disks in capacity 256  from the test above already passed for some petabyte.
And generally, it is nonprofessional so to speak, " ".
You not select the wife, and the obtaining tool .
It is not necessary for you that it would be good, it is necessary for you, that it  business function, and that cost  multifunctions would cost less.
I.e. it is necessary to select a disk at the minimum cost of a petabyte of a resource of record.
You consider, what  have a huge resource in comparison with , so, what cost of a petabyte of record at them several times more low, than at consumer disks?
then they also should be bought.
But it is necessary to show it a label of cost of a petabyte.
Here while from under the link above it is visible that a resource of some consumer disks of the order 8 petabyte counting on a half-terabyte disk
And at a server disk, a resource as you write, 18 petabyte
Whether there is it of all in 2 times more expensively? Or in 10?
It is necessary to consider.