Re: Performance Questions - disks

From: John Bell (jbellnewsposts_at_hotmail.com)
Date: 04/14/04 

Date: Wed, 14 Apr 2004 15:29:43 +0100

Hi

You ask alot of questions here, but a starter would be to read

SQL Server 2000 Performance Turning Technical Reference ISBN 0-7356-1270-6
http://www.sql-server-performance.com/sql_server_2000_performance_tuning_review.asp

and visit:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/agoptimizing_5lt1.asp

http://www.sql-server-performance.com/

John

"Kevin Hammond" <kghammond@nrscorp.com> wrote in message
news:c5jgrv$429$1@grandcanyon.binc.net...
> I recently started collecting historical performance statistics from our
SQL
> servers. I am gathering information from the big four counters, CPU, RAM,
> Disk, Network. In addition, I am watching CPU and RAM for the
sqlservr.exe
> process.
>
> On one server, %disk time is rather high and so is disk queue length. CPU
> is around 50%. This seems like an obvious disk bottleneck. In a
> multi-processor system, is %disk time based off a 100% max for total CPU
or
> 200% for each indivual processor? Also, can defragmenting the hard drive
> reduce the disk queue length?
>
> In our current server implementations, we have maintenance plans that
backup
> our databases's to files on the same disks as our sql databases's. These
> plans run nightly, so there is a lot of disk I/O on these disks. In
> addition, I suspect that they are highly fragmented due to all the writing
> backup files and deleting old backup files. We also are running around
> 5-10% of free disk space on this particular server. Since the file system
> was formatted with 64 Kbps clusters, I can not defragment the file system.
>
> Questions:
> 1) Do other admins deploy scheduled defragmentation policies on live sql
> volumes? Such as Diskeeper?
> 2) I read that there are mixed opinions on using 64 Kbps NTFS volumes for
> your live SQL DB's.
> 3) If you are going to backup the DB's to file, I assume the best
procedure
> for disk utilization is as follows:
> SYS - System & SQL Log
> DATA - SQL DB
> PAGEFILE - Pagefile & SQL backup files
>
>

Relevant Pages

  • RE: How to create a new instance
    ... get your SAN admin to present the disk to the server. ... create a new resource group in the cluster and add that diskto ... SQL install is not permanent). ...
    (microsoft.public.sqlserver.clustering)
  • Re: "True" hardware requirements for SPPS
    ... Win2K3 Server ... > I find it easiest to think of it as being 3 systems; SQL, SPS and Indexer. ... > it needs CPU and about 1GB of ram, indexing is all about CPU and disk memory ...
    (microsoft.public.sharepoint.portalserver)
  • Re: "True" hardware requirements for SPPS
    ... > Win2K3 Server ... >> I find it easiest to think of it as being 3 systems; SQL, SPS and Indexer. ... >> it needs CPU and about 1GB of ram, indexing is all about CPU and disk memory ...
    (microsoft.public.sharepoint.portalserver)
  • RE: Critical SQL DB and partition impact input
    ... This asumes that you have 3 disk controller cards. ... You mentioned that you plan to use SQL ... Server 2005 Express on the clients and have them synchronized on the SQL ... connected to one raid card. ...
    (microsoft.public.windows.server.sbs)
  • Re: Scheduled Backup for SQL Database
    ... Look in the SQL Server log too, ... Need smaller SQL2K backup files? ... >>Need smaller SQL2K backup files? ...
    (microsoft.public.sqlserver.server)