SQL About AMO: sec

Showing posts with label sec. Show all posts

Thursday, March 8, 2012

about writes/sec SQL server

Hi Guys,
Do you know how many writes/sec SQL server is capable of performing
generally? Trying to work out what % of a DB we would be consuming. ThanksProbably many more than you will even need. But it is usually not SQL
Server that is the bottle neck. It is mostly the hardware that will
determine how many writes per second you can do.
--
Andrew J. Kelly SQL MVP
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:33DBA316-4AE8-49D0-A7EE-D49F8ECD9E7D@.microsoft.com...
> Hi Guys,
> Do you know how many writes/sec SQL server is capable of performing
> generally? Trying to work out what % of a DB we would be consuming. Thanks
>|||How many transactions the sql server can handle for a minutes. Thanks.
"Andrew J. Kelly" wrote:
> Probably many more than you will even need. But it is usually not SQL
> Server that is the bottle neck. It is mostly the hardware that will
> determine how many writes per second you can do.
> --
> Andrew J. Kelly SQL MVP
> "Iter" <Iter@.discussions.microsoft.com> wrote in message
> news:33DBA316-4AE8-49D0-A7EE-D49F8ECD9E7D@.microsoft.com...
> > Hi Guys,
> > Do you know how many writes/sec SQL server is capable of performing
> > generally? Trying to work out what % of a DB we would be consuming. Thanks
> >
> >
>
>|||try to navigate to www.tpc.org: you'll find a lot of different answers.
The number of transactions/min depends on a lot of factrors:
- Hardware
- Infrastructure
- OS
- physical and logical design of the DB
- logical design and implementation of the transaction
- etc.
Usualy any RDBMS is implemented to give the most efficiency, but...
Gilberto
"Iter" wrote:
> How many transactions the sql server can handle for a minutes. Thanks.
>
> "Andrew J. Kelly" wrote:
> > Probably many more than you will even need. But it is usually not SQL
> > Server that is the bottle neck. It is mostly the hardware that will
> > determine how many writes per second you can do.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> > "Iter" <Iter@.discussions.microsoft.com> wrote in message
> > news:33DBA316-4AE8-49D0-A7EE-D49F8ECD9E7D@.microsoft.com...
> > > Hi Guys,
> > > Do you know how many writes/sec SQL server is capable of performing
> > > generally? Trying to work out what % of a DB we would be consuming. Thanks
> > >
> > >
> >
> >
> >|||Like I said it is mainly up to the hardware and how the application and
schema were developed. I have worked on SQL Servers that do over 60K
transactions per second. The TPC link that was given would be a good
resource but that does nothing to show how much your system will do.
--
Andrew J. Kelly SQL MVP
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:098E0931-DD8C-403F-8837-5091D21AE42A@.microsoft.com...
> How many transactions the sql server can handle for a minutes. Thanks.
>
> "Andrew J. Kelly" wrote:
>> Probably many more than you will even need. But it is usually not SQL
>> Server that is the bottle neck. It is mostly the hardware that will
>> determine how many writes per second you can do.
>> --
>> Andrew J. Kelly SQL MVP
>> "Iter" <Iter@.discussions.microsoft.com> wrote in message
>> news:33DBA316-4AE8-49D0-A7EE-D49F8ECD9E7D@.microsoft.com...
>> > Hi Guys,
>> > Do you know how many writes/sec SQL server is capable of performing
>> > generally? Trying to work out what % of a DB we would be consuming.
>> > Thanks
>> >
>> >
>>|||"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:098E0931-DD8C-403F-8837-5091D21AE42A@.microsoft.com...
> How many transactions the sql server can handle for a minutes. Thanks.
How big are your transactions?
Are you committing 5 bytes or 5 gigabytes?
How many disks do you have?
How fast are they?
Quite seriously, your question is really a bit open ended.
Note, even with the TPC benchmarks that Andrew pointed you towards, that's
really a matter of "how much money you want to spend."
With more money, MS could probably post even better numbers. But until
someone betters them enough, they won't bother.
>
> "Andrew J. Kelly" wrote:
>> Probably many more than you will even need. But it is usually not SQL
>> Server that is the bottle neck. It is mostly the hardware that will
>> determine how many writes per second you can do.
>> --
>> Andrew J. Kelly SQL MVP
>> "Iter" <Iter@.discussions.microsoft.com> wrote in message
>> news:33DBA316-4AE8-49D0-A7EE-D49F8ECD9E7D@.microsoft.com...
>> > Hi Guys,
>> > Do you know how many writes/sec SQL server is capable of performing
>> > generally? Trying to work out what % of a DB we would be consuming.
>> > Thanks
>> >
>> >
>>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Simple answer: enough for your needs - given sufficient hardware. :-) And
it doesn't take a kajillion dollars worth of hardware to get REALLY good
performance either!
--
TheSQLGuru
President
Indicium Resources, Inc.
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:098E0931-DD8C-403F-8837-5091D21AE42A@.microsoft.com...
> How many transactions the sql server can handle for a minutes. Thanks.
>
> "Andrew J. Kelly" wrote:
>> Probably many more than you will even need. But it is usually not SQL
>> Server that is the bottle neck. It is mostly the hardware that will
>> determine how many writes per second you can do.
>> --
>> Andrew J. Kelly SQL MVP
>> "Iter" <Iter@.discussions.microsoft.com> wrote in message
>> news:33DBA316-4AE8-49D0-A7EE-D49F8ECD9E7D@.microsoft.com...
>> > Hi Guys,
>> > Do you know how many writes/sec SQL server is capable of performing
>> > generally? Trying to work out what % of a DB we would be consuming.
>> > Thanks
>> >
>> >
>>

Monday, February 13, 2012

About how many writes per sec for a single drive?

Deos anybody have a rough estimate for how many writes per second a
single drive system (hardware mirrored to a second drive) should be
able to handle? (ie: Win2000, IIS, apps, SQL, db + log files -- all on
one drive)
I know that's a slippery question, but I'm just looking for an order
of magnitude... Should I expect to max out at about 1-5 writes/sec?
20-50? 100-200?
On the other hand, if I were to bump that up to a RAID-10 with 3
stripped drives, mirrored to a second set of three -- in that case,
about how many writes per second should I expect?
I'm trying to build a relatively inexpensive box to do the job,
without going crazy and over-engineering it. Even a rough sense of
writes/sec should help me better understand what sort of disk system I
might need...
Many Thanks,
Scott> I know that's a slippery question, but I'm just looking for an order
> of magnitude... Should I expect to max out at about 1-5 writes/sec?
> 20-50? 100-200?
The hardware drives themselves can do in the order 100 write/second.
Depends a bit on how near the writes are. Most drives nowadays
have a 'seek' time around 8 milliseconds. So if the drive has to
do a 'seek' for each write and one complete rotation (depending
on the structure it could be less or more). A rotation (7200 pm =around 8 millisecond for one rotation) is also 8 milli's this would
be 16 milli's for a write. This comes to 62 writes a second. If
the drive is a bit intelligent and the writes occure close to eachother
or are sorted by the drive 100 writes per second is a fair number.
For raid devices were 3 disks can be used at the same time.
the number of 'small' writes can be 3 times as high as a single
disk. (Using 3 disks). (But only if data is needed from the 3 different
devices). This is seldom the case.
Cheap raid solutions can not handle reads or writes at the
same time on different positions on the three disks. With cheap
read devices you only win when accessing large chunks.
If accesses are short, raid wil perform better than a single drive,
but this is often expressed in a percentage and not in a factor.
This report is produced from memory, and as we all know memory
is not build to keep data accurately but to survive. So reading my
numbers this should be taken into account.
ben brugman
> On the other hand, if I were to bump that up to a RAID-10 with 3
> stripped drives, mirrored to a second set of three -- in that case,
> about how many writes per second should I expect?
> I'm trying to build a relatively inexpensive box to do the job,
> without going crazy and over-engineering it. Even a rough sense of
> writes/sec should help me better understand what sort of disk system I
> might need...
> Many Thanks,
> Scott|||There is a great book on I/O performance, its called SQL
Server 2000 performance tuning from MS Press by Edward
Whalen, et al. -- ISBN 0-7356-1270-6. Go to page 73 and
it discusses all that you are asking about. For a general
ball park answer on a RAID 10, you should track the
following with System Monitor:
Disk Reads/sec
Disk Writes/sec
Avg. Disk Queue Length
Avg. Disk Sec/Read
Avg. Disk Sec/Write
I/Os per disk = [Reads + (2 * Writes)]/Number of Disks
For example, a 9GB/10,000 RPM disk should have the
capacity of 100 I/Os per sond for random I/O. Your milage
may vary depending on you disk subsystem.
Later,
Chris
>--Original Message--
>Deos anybody have a rough estimate for how many writes
per second a
>single drive system (hardware mirrored to a second drive)
should be
>able to handle? (ie: Win2000, IIS, apps, SQL, db + log
files -- all on
>one drive)
>I know that's a slippery question, but I'm just looking
for an order
>of magnitude... Should I expect to max out at about 1-5
writes/sec?
>20-50? 100-200?
>On the other hand, if I were to bump that up to a RAID-10
with 3
>stripped drives, mirrored to a second set of three -- in
that case,
>about how many writes per second should I expect?
>I'm trying to build a relatively inexpensive box to do
the job,
>without going crazy and over-engineering it. Even a rough
sense of
>writes/sec should help me better understand what sort of
disk system I
>might need...
>Many Thanks,
>Scott
>.
>|||An excellent article here.
http://research.microsoft.com/research/pubs/view.aspx?
tr_id=686
>--Original Message--
>Deos anybody have a rough estimate for how many writes
per second a
>single drive system (hardware mirrored to a second drive)
should be
>able to handle? (ie: Win2000, IIS, apps, SQL, db + log
files -- all on
>one drive)
>I know that's a slippery question, but I'm just looking
for an order
>of magnitude... Should I expect to max out at about 1-5
writes/sec?
>20-50? 100-200?
>On the other hand, if I were to bump that up to a RAID-10
with 3
>stripped drives, mirrored to a second set of three -- in
that case,
>about how many writes per second should I expect?
>I'm trying to build a relatively inexpensive box to do
the job,
>without going crazy and over-engineering it. Even a rough
sense of
>writes/sec should help me better understand what sort of
disk system I
>might need...
>Many Thanks,
>Scott
>.
>|||Hi
If you want to read up on this sort of thing then Microsoft SQL Server 2000
Performance Tuning Technical Referencby Whelan, Garcia, DeLuca and Thompson
ISBN 0-7356-1270-6 is worth reading.
The actual numbers will be dependent on the drives specifications. The
examples quoted in the above book work out at about 125 I/Os per second, but
reading and writing would both have to be considered for as system (assuming
that data is retrieved) therefore the distribution of these will effect the
overall figures. This factor will be the same for both RAID1 and RAID10, but
the number of discs will reduce this value proportionaly for RAID10.
John
"Scott Matthews" <scott@.turnstyle.com> wrote in message
news:f11b846.0311280630.78dd010c@.posting.google.com...
> Deos anybody have a rough estimate for how many writes per second a
> single drive system (hardware mirrored to a second drive) should be
> able to handle? (ie: Win2000, IIS, apps, SQL, db + log files -- all on
> one drive)
> I know that's a slippery question, but I'm just looking for an order
> of magnitude... Should I expect to max out at about 1-5 writes/sec?
> 20-50? 100-200?
> On the other hand, if I were to bump that up to a RAID-10 with 3
> stripped drives, mirrored to a second set of three -- in that case,
> about how many writes per second should I expect?
> I'm trying to build a relatively inexpensive box to do the job,
> without going crazy and over-engineering it. Even a rough sense of
> writes/sec should help me better understand what sort of disk system I
> might need...
> Many Thanks,
> Scott

Thursday, March 8, 2012

about writes/sec SQL server

about writes/sec SQL server

about writes/sec SQL server

Monday, February 13, 2012

About how many writes per sec for a single drive?

SQL About AMO

Blog Archive

About Me