Monitoring Wish List ...

We are in the process of bringing a new SQL Cluster into production. One of
the things we are insisting on is that we have appropriate performance
monitoring in place BEFORE our customers start using it.
Briefly, the cluster is twin quad xeons, expandable to eight processors. 8
gig ram expandable to 64. Fiber channel to a drive array, log files to a
separate array. The cluster is an ASP environment. Customers access the
database server through a load balanced Citrix farm. The expectation is to
go from about 100 customers to over 1000 in the next 24 months, partly
through aquiring new customers and partly through moving existing customers
to the ASP model. Each customer has a separate set of databases. The obvious
problem is that performance is maintianed as new customers are added. The
monitoring plan is to take a baseline set of readings with no load on the
new cluster, then take data at prescribed intervals so that we can project
utilization and adjust planned upgrades accordingly. All standard procedure
(or at least it should be, right?).
My question is, that if you had the opportunity to set up such monitoring on
a pristine system, what would you include? We are currently going to look at
the following:
Logical Disk:
Avg Queue Length
Current Disk Queue
Physical Disk
Avg Queue Length
Current Disk Queue
Memory
Pages/sec
Page Reads/sec
Page Faults/sec
System
Processor Queue Length
CPU %
SQL:
Cache Hit Ratio
I/O - Single Page Write/Sec
I/O - Page Reads/Sec
I/O - Transactions/Sec
User Connections
What would you add or remove from this list?
Thanks,
Bob Castleman
SuccessWare SoftWareBob,
I'd take a look at the Microsoft SQL Server 2000
operations guide - as it has reccommendations for creating
a performance baseline..
Take a look at :-
http://www.microsoft.com/technet/treeview/default.asp?
url=/technet/prodtechnol/sql/maintain/operate/opsguide/sqlo
ps5.asp
and look for 'Creating The Baseline - Suggested Counters'
Hope this helps.
>--Original Message--
>We are in the process of bringing a new SQL Cluster into
production. One of
>the things we are insisting on is that we have
appropriate performance
>monitoring in place BEFORE our customers start using it.
>Briefly, the cluster is twin quad xeons, expandable to
eight processors. 8
>gig ram expandable to 64. Fiber channel to a drive array,
log files to a
>separate array. The cluster is an ASP environment.
Customers access the
>database server through a load balanced Citrix farm. The
expectation is to
>go from about 100 customers to over 1000 in the next 24
months, partly
>through aquiring new customers and partly through moving
existing customers
>to the ASP model. Each customer has a separate set of
databases. The obvious
>problem is that performance is maintianed as new
customers are added. The
>monitoring plan is to take a baseline set of readings
with no load on the
>new cluster, then take data at prescribed intervals so
that we can project
>utilization and adjust planned upgrades accordingly. All
standard procedure
>(or at least it should be, right?).
>My question is, that if you had the opportunity to set up
such monitoring on
>a pristine system, what would you include? We are
currently going to look at
>the following:
>Logical Disk:
> Avg Queue Length
> Current Disk Queue
>Physical Disk
> Avg Queue Length
> Current Disk Queue
>Memory
> Pages/sec
> Page Reads/sec
> Page Faults/sec
>System
> Processor Queue Length
> CPU %
>SQL:
> Cache Hit Ratio
> I/O - Single Page Write/Sec
> I/O - Page Reads/Sec
> I/O - Transactions/Sec
> User Connections
>
>What would you add or remove from this list?
>
>Thanks,
>Bob Castleman
>SuccessWare SoftWare
>
>.
>|||Thanks Steve.
Unfortunately, the info at the link you provided is pretty generic. I guess
I'm looking for specifics based on the experiences of the experts that post
here.
"Steve Hindmarsh" <steve@.nowhere.com> wrote in message
news:019f01c3db96$3e331ba0$a401280a@.phx.gbl...
> Bob,
> I'd take a look at the Microsoft SQL Server 2000
> operations guide - as it has reccommendations for creating
> a performance baseline..
> Take a look at :-
> http://www.microsoft.com/technet/treeview/default.asp?
> url=/technet/prodtechnol/sql/maintain/operate/opsguide/sqlo
> ps5.asp
> and look for 'Creating The Baseline - Suggested Counters'
> Hope this helps.
> >--Original Message--
> >We are in the process of bringing a new SQL Cluster into
> production. One of
> >the things we are insisting on is that we have
> appropriate performance
> >monitoring in place BEFORE our customers start using it.
> >
> >Briefly, the cluster is twin quad xeons, expandable to
> eight processors. 8
> >gig ram expandable to 64. Fiber channel to a drive array,
> log files to a
> >separate array. The cluster is an ASP environment.
> Customers access the
> >database server through a load balanced Citrix farm. The
> expectation is to
> >go from about 100 customers to over 1000 in the next 24
> months, partly
> >through aquiring new customers and partly through moving
> existing customers
> >to the ASP model. Each customer has a separate set of
> databases. The obvious
> >problem is that performance is maintianed as new
> customers are added. The
> >monitoring plan is to take a baseline set of readings
> with no load on the
> >new cluster, then take data at prescribed intervals so
> that we can project
> >utilization and adjust planned upgrades accordingly. All
> standard procedure
> >(or at least it should be, right?).
> >
> >My question is, that if you had the opportunity to set up
> such monitoring on
> >a pristine system, what would you include? We are
> currently going to look at
> >the following:
> >
> >Logical Disk:
> > Avg Queue Length
> > Current Disk Queue
> >Physical Disk
> > Avg Queue Length
> > Current Disk Queue
> >Memory
> > Pages/sec
> > Page Reads/sec
> > Page Faults/sec
> >System
> > Processor Queue Length
> > CPU %
> >SQL:
> > Cache Hit Ratio
> > I/O - Single Page Write/Sec
> > I/O - Page Reads/Sec
> > I/O - Transactions/Sec
> > User Connections
> >
> >
> >
> >What would you add or remove from this list?
> >
> >
> >Thanks,
> >
> >Bob Castleman
> >SuccessWare SoftWare
> >
> >
> >.
> >