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

Monitoring service broker

Hi!
We have next problem in production.
We have one target that accepts messages from many initiators. Some time target can not get message from some initiator. It may be due hardware problem, for example technitian switch off router. In general service broker succeed to receive all messages when hardware problem was solved (if it takes less than retantion time on initiator side). I would like to build some job that would look at queue of target and could say (or trigger) that from the initiator A for some period of time target did not get any message or did not succeed to close conversation or it constantly have some connectivity problem (like duplicated message).

Any idea?

There are various traces you can use that indicate potential problems, like Message Drop, Message Classify with the subclass Delayed or Connection with the subclass Closing (unless is a ordinary timeout close due to lack of activity). Using the sp_trace_... procedure(s) some automation can be built around them.

An easier solution is to monitor the sys.transmission_queue on all parties involved. If messages are building up, is an indication of a problem that needs to be investigated.

Monitoring Production Instances.

We will soon have 200 + production instances. In an effort to be more
pro active, I am looking for a way to monitor jobs, both failed and
long running, Disk space, and other such things like CPU load etc.
I would appreicate any suggestions you might have on accomplishing
this. For instance, is there anything in reporting services that might
help accomplish this ? Most of the instances are 2k, but we also have
some 2k5.
Best Regards
SGB(sgbergkam@.gmail.com) writes:
> We will soon have 200 + production instances. In an effort to be more
> pro active, I am looking for a way to monitor jobs, both failed and
> long running, Disk space, and other such things like CPU load etc.
> I would appreicate any suggestions you might have on accomplishing
> this. For instance, is there anything in reporting services that might
> help accomplish this ? Most of the instances are 2k, but we also have
> some 2k5.

I think you should look at MOM, Microsoft Operations Manager.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

monitoring deadlock

On production we r having lot of deadlock errors. How can i monitor this and
identified sps creating this havoc... ?
Perhaps you can use this:
http://support.microsoft.com/?kbid=271509
You might find that this is helpful information as well:
http://www.sommarskog.se/sqlutil/aba_lockinfo.html
Keith Kratochvil
"Vikram" <aa@.aa> wrote in message
news:eTt3kRMWGHA.3332@.TK2MSFTNGP02.phx.gbl...
> On production we r having lot of deadlock errors. How can i monitor this
> and
> identified sps creating this havoc... ?
>

monitoring deadlock

On production we r having lot of deadlock errors. How can i monitor this and
identified sps creating this havoc... ?Perhaps you can use this:
http://support.microsoft.com/?kbid=271509
You might find that this is helpful information as well:
http://www.sommarskog.se/sqlutil/aba_lockinfo.html
Keith Kratochvil
"Vikram" <aa@.aa> wrote in message
news:eTt3kRMWGHA.3332@.TK2MSFTNGP02.phx.gbl...
> On production we r having lot of deadlock errors. How can i monitor this
> and
> identified sps creating this havoc... ?
>

monitoring deadlock

On production we r having lot of deadlock errors. How can i monitor this and
identified sps creating this havoc... ?SQL profiler - Events Locks - Lock::Deadlock.
Mel|||Look at
http://www.sql-server-performance.com/deadlocks.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servdatabse_5xrn.asp?
http://support.microsoft.com/default.aspx?scid=kb;en-us;832524
Regards
Amish Shah|||Perhaps you can use this:
http://support.microsoft.com/?kbid=271509
You might find that this is helpful information as well:
http://www.sommarskog.se/sqlutil/aba_lockinfo.html
Keith Kratochvil
"Vikram" <aa@.aa> wrote in message
news:eTt3kRMWGHA.3332@.TK2MSFTNGP02.phx.gbl...
> On production we r having lot of deadlock errors. How can i monitor this
> and
> identified sps creating this havoc... ?
>