> 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
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>

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

>>>>
>>>>