monitoring configuration for SQL server DB

Did you check Microsoft's recommendations for those counters?
If not, you can check them here.

hi,

do you know if there are part 1 ? I assume part 1 is also only for SQL server?

oh, that one seems more on server side, right?

SCOM monitors everything from Microsoft so it will always be my top choice for monitor SQL Server instances.
BCM Patrol is quite well known in very large companies as it can monitor almost everything, from Windows to Unix, SQL Server to Oracle, storage, network, etc.

hi,

I keep seeing this kind of meansurment:

These should be below the 20% of Batch Requests/sec.

usually monitoring tools can't take this, any link with specific value ?

the second link shown this:

 Available MB must not drop below 100 MB, otherwise you will see severe performance issues due to Windows memory pressure.

– The average of %Processor Time must be less than 70%, otherwise you have a bottleneck at the CPU.

– These two counters need to be constantly under 20 msec to have good disk performance, otherwise you possibly have a bottleneck at the disk subsystem.

– %Usage must be less than 30%, otherwise you will see performance issues due to paging. Install more memory to mitigate this issue.

then we can see a figure which can be setup in any of monitoring tools in the value column.

Monitoring tools doesn't make any kind of calculation. It only gives you the thresholds.
What the above statement says is that counter is dependent of another counter and this is not so unusual. Some counters work on pairs.

"What the above statement says is that counter is dependent of another counter and this is not so unusual. Some counters work on pairs."

yes I knew, but it is hard to put it to monitoring tools to check it, right?

It may be hard, yes but I can't confirm that. Like I said, I'm used to work with the monitoring team and I only give them my requirements and they assure that can be done. I think it depends on the monitoring tool.
To be honest we don't monitor those counters. We use them for troubleshooting only and for that we don't need any especial monitoring tool since we can query them from the SQL Server instance or even use the local Performance Monitor tool.

hi,

I am sorry that, I can see that at the end of that article,  I see this:


" Next week I will discuss how to collect useful data with aProfiler trace!"

ahhaa, I want to read  that too, can you suggest the page?

I want to read  that too, can you suggest the page?

Well, that was what the author wrote in the end of the Part 1 and I've sent you first the Part 2, so is the article that he mentioned, meaning that you already read it.

hi,

that one:

https://blogs.msdn.microsoft.com/john_daskalakis/2013/10/21/how-to-troubleshoot-sql-server-performance-issues-with-simple-tools-part-2-how-to-analyze-the-perfmon-trace-and-detect-sql-server-performance-issues/

 is part 2.

and it said at the end of that link.

sorry I think it is  that one:

https://blogs.msdn.microsoft.com/john_daskalakis/2013/10/30/how-to-troubleshoot-sql-server-performance-issues-with-simple-tools-part-3-the-profiler/

I search by typing this: 'How to troubleshoot SQL Server performance issues with simple tools (Part 3'

I am sorry for that man..

this might be the automated  checking tools,.

The Part 3 isn't helpful also but give it a read.

you mean the use of SQL profiler can't do much to detect problem ?

No. The Part 3 of the article. It doesn't have more performance counters for you.

yes, tks.

I will add one more post to see any counter to detect index issue, please comment.

hi,


for SQL server database mirroring and replication monitoring, any parameter and counter you can suggest with suggested value ?

As far as I know there's no counters for that.

hi,

someone might suggest:

SQLServer: Replication Dist      Dist:Delivery Latency
SQLServer: Database Mirroring      Bytes Received/Sec
                                                                     Bytes Sent/Sec

Dist:Delivery Latency

That's the latency between Publisher and Subscriber. It can be interesting to have this information.

Bytes Received/Sec
Bytes Sent/Sec

Those are only information about how many bytes was sent and received. I don't think you can use those values to monitor the state of a mirroring.

"That's the latency between Publisher and Subscriber"

any value can be suggested for the threshold ?

I guess it depends on the application requirements.
That value is in seconds so I would say that everything that is higher than 1 second will be bad but that's me that thinks that everything in a database need to perform below 1 second :)
If you have a slow network maybe start with a 2 seconds thresholds and see how many alerts it raises. If none during a long period (1 week, 1 month) then you can reduce it to 1 second.

"I guess it depends on the application requirements. "

I also think that replication issue more related to RAM and tempdb contention as per subscription can take on the distributor/publisher 32GB of RAM and all data received in tempdb before merge to the target user database.

agree on that?

Not really. For me, replication has more to do with network than RAM.

I tried one time that in my previous company , they chagned to CDN network and they thought it helps, finally it won't help at all.

previous we have publisher and distributor on the same box and whenever we add RAM, number of replication agent long running alert lesser.

if we add one more replication, these alert come again.

previous we have publisher and distributor on the same box and whenever we add RAM, number of replication agent long running alert lesser.

If they are in the same box of course they will never use network and as they share the same memory they can use it easily. You can't compare things just like that because they've different configurations.

I mean when adding replication all these resource contention rise.  so it did need more RAM. and tempdb contention will be show up in rpelication log.

How about this question?
Do you still need help with it?

"If they are in the same box of course they will never use network and as they share the same memory they can use it easily. Y"

so therefore more RAM.

but actually if they are not on the same box, they publsiher or distributor use more RAM, I think is should be the publisher.

for monitor parameter like this:

1)Page Faults
2) Page life expectancy
3) Page writes/sec
4) Pages Per Second

has a thershold, then we should add more RAM ? for me it is a yes but I would like to hear what you conclude too.

so therefore more RAM.

Basically, yes.

for monitor parameter like this:

 1)Page Faults
 2) Page life expectancy
 3) Page writes/sec
 4) Pages Per Second

 has a thershold, then we should add more RAM ?

What does counters have to have with Replication?

sorry but my question at the beginning is not only replication but monitoring counter.

I think we can close this ticket now.

tks.