>>
can we get the number of transactions in a given month for a particular server? I know ho wot get transaction/ sec but do not know per month, I googled with no luck.
If you need online information regarding the number of transactions during a specific
short Interval of time like 1 second, then you can use sys.dm_os_performance_counters. Theoretically you can get the counter value in the start time, wait for the end of the time and get the new
value. Those counters are very bad for getting information for long period of time: (1) Restart the server or any other reset of the counters, will leads you to bad result! (2) this is a monitoring that you need to do while the action in progress, and you
can not get information from the counters in retroactive (history), If you don't have the starting value. You can try check the value in the beginning of the month and on the end of the month hopefully the counter was not reset.
There is another option that can help you get the information from the history, as well. That is using the transaction log. you can check directly in the transaction log how many transaction where. In order to do this you should use the function fn_dblog.
This is accurate information, if you know how t get it. It is more complex since you have lot of transaction files, and it need some thinking and organize stuff. By the way you can read from the backup log file as well, using the function fn_dump_dblog.
You can read more about those function here.
>> we have got 2 instances of SQL server, instance A and instance B, if my transactions are high or less in any of the instances
do I need to INVEST to bring the transaction levels to normal?
that is difficult to answer :-) since
normal is relative, and what
normal to one is very low activities to the other. You should know your servers and the use of the instances, in order to say if this is high or low activities :-)