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

>>>>>
>>>>>

>>>
>>>