I'm hoping someone can really give me a hand here. My dumb SAN storage guy
will not assign me proper storage for a low end SQL server unless I give him
some IOPS, bandwith to calculate disk size and for both numbers he wants to
know the average and expected peak, plus a confirmation of the average and
peak ratio. HOw in the HELL do I come up with this? Is there an easy way or
tool I can generate some real numbers either using performance monitor with
some counters, running some test or any formula? How do I get these numbers
to him? I'm looking to deploy Windows 2003 and SQL 2005 if that s.

> I'm hoping someone can really give me a hand here. My dumb SAN storage guy
> will not assign me proper storage for a low end SQL server unless I give
> him
> some IOPS, bandwith to calculate disk size and for both numbers he wants
> to
> know the average and expected peak, plus a confirmation of the average and
> peak ratio.

In defense of your storage admin, he needs some sort of SLA requirements in
order to present the appropriate storage.

> HOw in the HELL do I come up with this? Is there an easy way or
> tool I can generate some real numbers either using performance monitor
> with
> some counters, running some test or any formula? How do I get these
> numbers
> to him? I'm looking to deploy Windows 2003 and SQL 2005 if that s.

With the exception of space requirements, the requested metrics are
available in Performance Monitor. However, your storage admin needs to
understand how SQL Server performs IO in order to make the numbers
meaningful. SQL Server writes a large number pages to disk asynchronously
during a checkpoint process that runs every minute or so and dynamically
adjusts the number of outstanding I/Os based on the queue depth.
Consequently, the reported peaks will depend much on the speed of the I/O
subsystem rather than the nature of the application. I suggest he peruse
http://www.microsoft.com/technet/pro...liobasics.mspx
for a more detailed discussion.

If you don't have a performance lab with the same hardware as production and
the resources to replication the production workload, I think your best bet
is to just use stats from a similar SQL Server application in your
environment that meets SLAs. You mention that this new box is a low-end
server (presumably supporting a small low-priority application with minimal
performance requirements). Maybe so maybe you can simplify this a bit and
just ask for the least costly storage solution.

--
Hope this s.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Erasmo" <Erasmo@discussions.microsoft.com> wrote in message
news:2B348959-53F3-4A71-9B78-A3E4DDDFFC47@microsoft.com...
> I'm hoping someone can really give me a hand here. My dumb SAN storage guy
> will not assign me proper storage for a low end SQL server unless I give
> him
> some IOPS, bandwith to calculate disk size and for both numbers he wants
> to
> know the average and expected peak, plus a confirmation of the average and
> peak ratio. HOw in the HELL do I come up with this? Is there an easy way
> or
> tool I can generate some real numbers either using performance monitor
> with
> some counters, running some test or any formula? How do I get these
> numbers
> to him? I'm looking to deploy Windows 2003 and SQL 2005 if that s.

Assuming that you have given your SAN admin what Dan mentioned, I'd be very
curious to see whether your SAN admin can fulfill his end of the SLA bargin.
You should press him for an SLA, espeically in terms of latency, and see his
response.

Linchi

"Erasmo" wrote:

> I'm hoping someone can really give me a hand here. My dumb SAN storage guy
> will not assign me proper storage for a low end SQL server unless I give him
> some IOPS, bandwith to calculate disk size and for both numbers he wants to
> know the average and expected peak, plus a confirmation of the average and
> peak ratio. HOw in the HELL do I come up with this? Is there an easy way or
> tool I can generate some real numbers either using performance monitor with
> some counters, running some test or any formula? How do I get these numbers
> to him? I'm looking to deploy Windows 2003 and SQL 2005 if that s.

OK fair enough, but if you could tell me which counters I should include to
determine the information I'm looking for and once I have them, how do I
interpret them and come up with the right number either by using a formula or
whatever

"Dan Guzman" wrote:

> > I'm hoping someone can really give me a hand here. My dumb SAN storage guy
> > will not assign me proper storage for a low end SQL server unless I give
> > him
> > some IOPS, bandwith to calculate disk size and for both numbers he wants
> > to
> > know the average and expected peak, plus a confirmation of the average and
> > peak ratio.
>
> In defense of your storage admin, he needs some sort of SLA requirements in
> order to present the appropriate storage.
>
> > HOw in the HELL do I come up with this? Is there an easy way or
> > tool I can generate some real numbers either using performance monitor
> > with
> > some counters, running some test or any formula? How do I get these
> > numbers
> > to him? I'm looking to deploy Windows 2003 and SQL 2005 if that s.
>
> With the exception of space requirements, the requested metrics are
> available in Performance Monitor. However, your storage admin needs to
> understand how SQL Server performs IO in order to make the numbers
> meaningful. SQL Server writes a large number pages to disk asynchronously
> during a checkpoint process that runs every minute or so and dynamically
> adjusts the number of outstanding I/Os based on the queue depth.
> Consequently, the reported peaks will depend much on the speed of the I/O
> subsystem rather than the nature of the application. I suggest he peruse
> http://www.microsoft.com/technet/pro...liobasics.mspx
> for a more detailed discussion.
>
> If you don't have a performance lab with the same hardware as production and
> the resources to replication the production workload, I think your best bet
> is to just use stats from a similar SQL Server application in your
> environment that meets SLAs. You mention that this new box is a low-end
> server (presumably supporting a small low-priority application with minimal
> performance requirements). Maybe so maybe you can simplify this a bit and
> just ask for the least costly storage solution.
>
> --
> Hope this s.
>
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
>
> "Erasmo" <Erasmo@discussions.microsoft.com> wrote in message
> news:2B348959-53F3-4A71-9B78-A3E4DDDFFC47@microsoft.com...
> > I'm hoping someone can really give me a hand here. My dumb SAN storage guy
> > will not assign me proper storage for a low end SQL server unless I give
> > him
> > some IOPS, bandwith to calculate disk size and for both numbers he wants
> > to
> > know the average and expected peak, plus a confirmation of the average and
> > peak ratio. HOw in the HELL do I come up with this? Is there an easy way
> > or
> > tool I can generate some real numbers either using performance monitor
> > with
> > some counters, running some test or any formula? How do I get these
> > numbers
> > to him? I'm looking to deploy Windows 2003 and SQL 2005 if that s.
>

> OK fair enough, but if you could tell me which counters I should include
> to
> determine the information I'm looking for and once I have them, how do I
> interpret them and come up with the right number either by using a formula
> or
> whatever

For the I/Os per second, you can monitor the Physical Diskisk
Transfers/sec counter for each drive. You can break these down by
reads/writes with the Disk Writes/sec and Disk Reads/sec. Note that the log
drive will have a different profile than the data drive(s). Bandwidth is
basically Disk Bytes/sec and can be broken into Disk Read Bytes/sec and Disk
Write Bytes/sec.

I'm not sure what kind of interpretation/formula you are asking for. If you
gather this info on an adequately performing server, you only need report
the actual values.


--
Hope this s.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Erasmo" <Erasmo@discussions.microsoft.com> wrote in message
news:3D4B6F29-BF8E-4890-A036-C36317038B07@microsoft.com...
> OK fair enough, but if you could tell me which counters I should include
> to
> determine the information I'm looking for and once I have them, how do I
> interpret them and come up with the right number either by using a formula
> or
> whatever
>
> "Dan Guzman" wrote:
>
>> > I'm hoping someone can really give me a hand here. My dumb SAN storage
>> > guy
>> > will not assign me proper storage for a low end SQL server unless I
>> > give
>> > him
>> > some IOPS, bandwith to calculate disk size and for both numbers he
>> > wants
>> > to
>> > know the average and expected peak, plus a confirmation of the average
>> > and
>> > peak ratio.
>>
>> In defense of your storage admin, he needs some sort of SLA requirements
>> in
>> order to present the appropriate storage.
>>
>> > HOw in the HELL do I come up with this? Is there an easy way or
>> > tool I can generate some real numbers either using performance monitor
>> > with
>> > some counters, running some test or any formula? How do I get these
>> > numbers
>> > to him? I'm looking to deploy Windows 2003 and SQL 2005 if that s.
>>
>> With the exception of space requirements, the requested metrics are
>> available in Performance Monitor. However, your storage admin needs to
>> understand how SQL Server performs IO in order to make the numbers
>> meaningful. SQL Server writes a large number pages to disk
>> asynchronously
>> during a checkpoint process that runs every minute or so and dynamically
>> adjusts the number of outstanding I/Os based on the queue depth.
>> Consequently, the reported peaks will depend much on the speed of the I/O
>> subsystem rather than the nature of the application. I suggest he peruse
>> http://www.microsoft.com/technet/pro...liobasics.mspx
>> for a more detailed discussion.
>>
>> If you don't have a performance lab with the same hardware as production
>> and
>> the resources to replication the production workload, I think your best
>> bet
>> is to just use stats from a similar SQL Server application in your
>> environment that meets SLAs. You mention that this new box is a low-end
>> server (presumably supporting a small low-priority application with
>> minimal
>> performance requirements). Maybe so maybe you can simplify this a bit
>> and
>> just ask for the least costly storage solution.
>>
>> --
>> Hope this s.
>>
>> Dan Guzman
>> SQL Server MVP
>> http://weblogs.sqlteam.com/dang/
>>
>> "Erasmo" <Erasmo@discussions.microsoft.com> wrote in message
>> news:2B348959-53F3-4A71-9B78-A3E4DDDFFC47@microsoft.com...
>> > I'm hoping someone can really give me a hand here. My dumb SAN storage
>> > guy
>> > will not assign me proper storage for a low end SQL server unless I
>> > give
>> > him
>> > some IOPS, bandwith to calculate disk size and for both numbers he
>> > wants
>> > to
>> > know the average and expected peak, plus a confirmation of the average
>> > and
>> > peak ratio. HOw in the HELL do I come up with this? Is there an easy
>> > way
>> > or
>> > tool I can generate some real numbers either using performance monitor
>> > with
>> > some counters, running some test or any formula? How do I get these
>> > numbers
>> > to him? I'm looking to deploy Windows 2003 and SQL 2005 if that s.
>>