Thursday, February 6, 2014

Harpoon for SQL Server DBA's

Hi Guys,
It's been sometime now that I have spent in this RDBMS world and I know there is still plethora of things still waiting for me in here. But today I thought of compiling my tools in One Place so that it's one stop destination for DBA's working on Production Issues.

I  will try to put all the counters and values or the queries required for looking into Production Issues. I will also try to document the significance of a parameter. I will alter/customize the content depending upon the feedback I have from the guests.

I will start with places I have the most issues and try to fight my fears before the D Day so that I don't have a cold feet.

Classic Question : What do I do if my Query/Report is responding slow and taking more time than ever?
Answer: There is never a right/wrong answer here. The only thing important is your approach and the answer will come eventually. The doctors first tries to identify the symptoms and then does some test and based on his experience he judges what is wrong with the patient. If the doctor has not faced the disease earlier then he will not be in a situation to prescribe any medication nor will he be able to tell what's wrong with the patient.
So the Answer will be two fold what are the parameters you will try to judge the situation on and the analysis you will do based on the data you have.
Now first the parameters : There are a lot of parameters you can look into starting from PLE ,Page Fault,Disk Write/Sec, Disk Queue length, Processor Queue Length, etc.. The list if almost endless

Processor Queue Length:
How will it help? This is a parameter which will give the awareness that whether the CPU in the system is adequate or you need more CPU to be added.
Value : The Processor Queue Length should be 0.
Significance : The Processor Queue Length as the name suggests is a parameter which tells us if there are any processes in queue waiting for the Processing. So if the Value is 0 then there are no processes waiting to be catered. You need not worry even the CPU utilization is very high if the Processor queue length is 0. What a I mean to say is there is no performance hit yet. Definitely a thing to work upon why is the CPU going high.

Disk Queue Length:

ok.. first things first.. I apologize for not working on this blog for long long time now. But then today I thought I will ink some of the important things I need to revise before the interview calls. :) 

Let Start with Mirroring: 
I know it's an old school technology and we should switch to always on like asap but then hold on to your horses causes the questions are not stopping to come very soon. 

So 1st question which is the table which will show you the redo queue for the mirroring. 

SELECT  db_name(database_id) as DatabaseName       ,[retention_period]      ,[time_behind]
      ,[enable_time_behind]      ,[send_queue]      ,[enable_send_queue]      ,[redo_queue]      ,[enable_redo_queue]      ,[average_delay]      ,[enable_average_delay]
  FROM [msdb].[dbo].[dbm_monitor_alerts]  order by [database_id]

So basically the msdb.dbo.dbm_monitor_alerts table will have the key to your performance configuration of the mirroing setup.

SELECT  db_name(database_id) as DatabaseName      ,[role]      ,[status]      ,[witness_status]
      ,[log_flush_rate]      ,[send_queue_size]      ,[send_rate]      ,[redo_queue_size]      ,[redo_rate]
      ,[transaction_delay]      ,[transactions_per_sec]      ,[time]      ,[end_of_log_lsn]      ,[failover_lsn]
      ,[local_time]  FROM [msdb].[dbo].[dbm_monitor_data]  order by [database_id], [local_time] desc

Now msdb.dbo.dbm_monitor_data will have the answer to your redo queue size 

OR use sp_dbmmonitorresults database_name       , rows_to_return        , update_status 
which will give you all the information you can seek for :)

There will another questions related to setup as well and I would like to address the most interesting one as of now but I promise to update this more soon. 
What is the process of failover for asyncronous mode in the mirror server 
use master; 
alter database set partner  force_service_allow_data_loss

ok.. lets now look at replication questions we might encounter 


Exec sys.sp_replmonitorrefreshjob @iterations = 1;
we need to run this proc to update all the data in the distribution database monitoring tables.

exec sp_replmonitorsubscriptionpendingcmds   @publisher ='Enter publisher server name',
 @publisher_db = 'Enter publisher database name', @publication ='Enter name of publication',
 @subscriber ='Enter subscriber server name', @subscriber_db ='Enter subscriber database name',
 @subscription_type ='0' --0 for push and 1 for pull 

The MSrepl_transactions table contains one row for each replicated transaction.
The MSrepl_commands table contains rows of replicated commands
The MSrepl_errors table contains rows with extended Distribution Agent and Merge Agent failure information. 

Okay all for Indexes on this page 
https://technet.microsoft.com/en-us/library/ms177443(v=sql.105).aspx

Al Time Favourite !!! Will post on the lines of Paul Randal sometime sson. May be getting Lucky Tonite !! 



































No comments:

Post a Comment