> For transaction logging, ideally it should be < 10ms. Generally speaking, for
> small I/Os (e.g. 12KB per I/O), an I/O should be less tha 15~20ms (on some
> systems this would be too high a threshold). If it's into 50ms, you
> definitely have an I/O latency problem.
>
> Linchi
>
> "Eli Silverman" wrote:
>

>> We do have a hardware refresh policy. Unfortunately our company was acquired
>> by another and even though the server refresh has been approved in our budget
>> each of the past two years they have been dragging their feet allowing us to
>> do so.
>>
>> I will take a look at the avg disk sec/read and avg disk sec/write settings.
>> Considering the drives are 15krpm ul320, what is an acceptable reading?
>>
>> "Linchi Shea" wrote:
>>

>>>> The server itself is about 5 and a half years old. I know it is a Dual

>>>
>>> A 5~6 years old server is a very old server. You would definitely benefit
>>> from regular hardware refresh. That's right, regardless of how a sever may
>>> perform, you should put in a policy to refresh your hardware (primarily your
>>> server) once every three/four years or so. If you don't have any current
>>> performance issue, this keeps you ahead of the game. If you do have some
>>> performance issue, this shold help alleviate the performance problems. This
>>> should also help improve stability and so on.
>>>

>>>> The one reading I did see was that when we ran one of the reports the disk
>>>> Queue lengh waivered between 1 and 6 every second.

>>>
>>> You should also look at Avg Disk sec/Read and Avg Disk sec/Write to see what
>>> kind of storage I/O latency you are experiencing.
>>>

>>>> As I mentioned, the server is one node in a cluster environment, and SQL is
>>>> the main service running but I don't know what overhead the cluster service
>>>> adds.

>>>
>>> The cluster service does NOT add any overhead.
>>>
>>> Linchi
>>>
>>> "Eli Silverman" wrote:
>>>

>>>> The server itself is about 5 and a half years old. I know it is a Dual
>>>> processor 2.2ghz Xeon. I am having our network people try to ge me some
>>>> numbers but I am not versed enough in performance monitor to know what
>>>> appropriate readings should be, or even which ones should be run.
>>>>
>>>> Seeing that the RAID is shared by 3 servers we are having dificulty
>>>> determining if the server is disk bound.
>>>>
>>>> You are correct, I am guessing. To date I have been relying on our IT staff
>>>> to monitor the performance but I am not certain they know that more about it
>>>> than me.
>>>>
>>>> The one reading I did see was that when we ran one of the reports the disk
>>>> Queue lengh waivered between 1 and 6 every second.
>>>>
>>>> As I mentioned, the server is one node in a cluster environment, and SQL is
>>>> the main service running but I don't know what overhead the cluster service
>>>> adds. IIS is also running and this is just one of the 4 databases hosted on
>>>> the SQL server.
>>>>
>>>>
>>>>
>>>> "Andrew J. Kelly" wrote:
>>>>

>>>>> Having 3GB of memory for SQL Server these days is just asking for trouble.
>>>>> Heck my laptop has 4GB alone. But if the db is only 1.5GB that still should
>>>>> be enough for most things. I suspect you have other things running on the
>>>>> server than just SQL Server. If so it is hard to say how much more you need.
>>>>> But the bottom line is you need to narrow down where the bottle necks really
>>>>> are. It sounds like you are guessing at the moment. When these reports are
>>>>> running are you CPU or Disk bound? Are you sure you are not simply being
>>>>> blocked? How many CPU's do you have? Have you run any traces to see which
>>>>> queries may need tuning?
>>>>>
>>>>> --
>>>>> Andrew J. Kelly SQL MVP
>>>>> Solid Quality Mentors
>>>>>
>>>>>
>>>>> "Eli Silverman" discussions.microsoft.com> wrote in message
>>>>> news:8C7E94D7-7DA8-42F8-9198-5A22F6CB71DB@microsoft.com...

>>>>>> Thanks for the quick feedback.
>>>>>> The idea of keeping the database in memory is appealing, but can that be
>>>>>> implemented in a clustered environment?
>>>>>> Also how would one implement that any way.
>>>>>> We actually are running RAID 1+0 and our aplication is definately more
>>>>>> read
>>>>>> than write intensive.
>>>>>>
>>>>>> We get tollerable performance at best. but the second someone tries to run
>>>>>> a
>>>>>> report or copy a large datafile performance slows to a crawl. A page that
>>>>>> typically loads in 1-2 seconds can take 20-30. and the report that takes
>>>>>> 10-15 seconds to run when nobody is on can take 2-3 minutes.
>>>>>> Our report SPs are vry tempDB and memory intensive.
>>>>>> A typical report may require 15-20 tables
>>>>>>
>>>>>> We have found that in order for them to run in a timely fashion we have
>>>>>> sometimes needed to process the data in several separate stems creating 3
>>>>>> or
>>>>>> 4 temp table to hold the data and then join all of the temp tables
>>>>>> together
>>>>>> at the end.
>>>>>> Maybe we should be using @ memory tables for processing the reports
>>>>>> instead
>>>>>> of # temp tables but for the most part I seemd to get better performance
>>>>>> out
>>>>>> of the # temp tables.
>>>>>> Probably because our SQL server only has 3 GB of RAM.
>>>>>>
>>>>>> There are a number of inherited inefficiencies from the original design
>>>>>> that
>>>>>> we are working on eliminating so I think the IO is much more intensive
>>>>>> than
>>>>>> it needs to be.
>>>>>> Additionally there is a lot of IO from the fileshare.
>>>>>>
>>>>>> Also we have 45 people that access the database continually in house and
>>>>>> roughly 3000 people that have access to one of the 5 client web sites that
>>>>>> access this particular database. We also have 3 other databases that
>>>>>> reside
>>>>>> on the same server but they are smaller and see much less activity. Our
>>>>>> sites
>>>>>> use pooled connections but we typically have about 60 connections to the
>>>>>> SQL
>>>>>> server at any given time.
>>>>>>
>>>>>> I think my best choice at the moment is to try and convice them that SQL
>>>>>> needs it's own SAN device, or at least a dedicated IO channel on the SAN.
>>>>>> And
>>>>>> to mak ecertain that we can get as much memory as possible.
>>>>>>
>>>>>>
>>>>>> "Andrew J. Kelly" wrote:
>>>>>>

>>>>>>> Eli,
>>>>>>>
>>>>>>> Explaining SAN storage can take up a whole week but some general comments
>>>>>>> from what you have said so far. First off a 1.5GB database is extremely
>>>>>>> small for SQL Server these days. You should be able to fit the entire
>>>>>>> database in memory so most of the IO should be logical and not physical
>>>>>>> anyway. The exception would be the transaction log file. But again with a
>>>>>>> db
>>>>>>> so small you probably don't have much I/O to begin with. If you are
>>>>>>> currently using a single Raid 1 array and share that with Exchange and a
>>>>>>> file share and get OK performance now you should only see improvement
>>>>>>> with a
>>>>>>> SAN. Even though you will most likely still share an array on the SAN
>>>>>>> with
>>>>>>> other apps you will most likely have many more spindles in the array than
>>>>>>> you have now with a Raid 1. The SAN also most likely has more cache to
>>>>>>> buffer the writes which will also help. The key in your case is to ensure
>>>>>>> you have enough memory to keep the db in cache and you should be pretty
>>>>>>> set.
>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> Andrew J. Kelly SQL MVP
>>>>>>> Solid Quality Mentors
>>>>>>>
>>>>>>>
>>>>>>> "Eli Silverman" discussions.microsoft.com> wrote in message
>>>>>>> news:4F01EC36-1F3A-4B73-8004-313E186878B3@microsoft.com...

>>>>>>>>I am hoping someone can give me some advice about the performance
>>>>>>>>difference
>>>>>>>> between Multi-Channel Raid vs SAN storage.
>>>>>>>>
>>>>>>>> My basic question has to do with Performance and optimization.
>>>>>>>> Assuming I have the same spec drives (size, speed, throughput, ets...)
>>>>>>>> is
>>>>>>>> SQL better off with a Multi-channel raid controller or a SAN storrage
>>>>>>>> array?
>>>>>>>> Are there such things as multi-channel SAN storrage Arrays?
>>>>>>>> Can anybody direct me to some good white papers abbout SAN storage and
>>>>>>>> SQL?
>>>>>>>>
>>>>>>>> We currently run a two node Clustered SQL 2000 environment.
>>>>>>>> Unfortunately the nodes are attached to a common 1 channel raid device
>>>>>>>> so
>>>>>>>> everything runs off the 1 Raid array.
>>>>>>>>
>>>>>>>> We are getting ready to upgrade our hardware, however our parent
>>>>>>>> company
>>>>>>>> is
>>>>>>>> insisting that we implement a SAN for all of our data storage and I am
>>>>>>>> wondering if that is the best solution for our SQL server. We will also
>>>>>>>> be
>>>>>>>> upgrading to SQL 2005 at the same time.
>>>>>>>>
>>>>>>>> I know we could benefit from moving the logs, tempdb and possibly some
>>>>>>>> of
>>>>>>>> our indexes over to separate IO channels, but I am uncertain as to how
>>>>>>>> a
>>>>>>>> SAN
>>>>>>>> environment affects these concepts.
>>>>>>>>
>>>>>>>> I don't think the database is all that big, roughly 1.5GB but there are
>>>>>>>> a
>>>>>>>> couple of sizeable tables. one audit table has over 5 million records
>>>>>>>> and
>>>>>>>> our
>>>>>>>> expense tables have about 300K and will probably grow by at least
>>>>>>>> 25-30K
>>>>>>>> records per year.
>>>>>>>>
>>>>>>>> The RAID array has been divided into multiple volumes. One dedicated to
>>>>>>>> SQL,
>>>>>>>> another to Exchange and a third to general fileshare. But they are
>>>>>>>> still
>>>>>>>> on a
>>>>>>>> single SCSI channel.
>>>>>>>>
>>>>>>>> My concern is that when we migrate to our new hardware they are going
>>>>>>>> to
>>>>>>>> try
>>>>>>>> and implement the same architecture and drive throughput is going to
>>>>>>>> continue
>>>>>>>> to be stolen from SQL by the other servers and applications. I want to
>>>>>>>> make
>>>>>>>> certain tha I ask all the right questions and make all the right points
>>>>>>>> to
>>>>>>>> avoid having SQL crippled by our new infrastructure.
>>>>>>>>
>>>>>>>> Thanks in advance for any advice.
>>>>>>>>
>>>>>>>> Eli Silverman
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>

>>>>>>>
>>>>>>>

>>>>>
>>>>>