Diagnostic SQL Queries

Diagnostic Queries

Monitoring


Monitoring
These queries helped us at several occasions when we exceeded default connection pool size of 100.
We also used these queries identify uneven load across servers and dead lock frequency.

Number of Deadlocks since the Instance Restart

object_namecounter_nameinstance_namecntr_valuecntr_type
DEVSQLSERVER:LocksNumber of Deadlocks/sec_Total22272696576

Number of Connections by Login as well as By Client

login_namesession_count
Sam88
Harry4

Results:

client_net_addressprogram_namehost_namelogin_nameconnection count
local machineDWDiagnosticsSamWSSam9
local machineMicrosoft SQL Server Management StudioHarryMachineHarry2
local machineMicrosoft SQL Server Management Studio - QueryJohnMachineJohn1
local machineRed Gate Software Ltd SQL Prompt 7.2.3.283SamWSSam1
local machineSQLServerCEIPSamWSSam1
192.168.0.1.Net SqlClient Data ProviderSamWSSam1

Current Tasks

System Stored Procedures:

spidecidstatusloginamehostnameblkdbnamecmdrequest_id
90backgroundsa0masterSIGNAL HANDLER0
100sleepingsa0masterTASK MANAGER0
110backgroundsa0masterTRACE QUEUE TASK0
120backgroundsa0masterBRKR TASK0

SPIDStatusLoginHostNameBlkByDBNameCommandCPUTimeDiskIOLastBatchProgramNameSPIDREQUESTID
1BACKGROUNDsa..NULLLOG WRITER116765006/27 16:23:5610
5BACKGROUNDsa..NULLRECOVERY WRITER889812006/27 16:23:5650
6BACKGROUNDsa..NULLLAZY WRITER122687006/27 16:23:5660

Current Tasks SnapShot

CurrentSnapshot


Who’s running what at this moment on this instance”

TaskCntByScheduler

SQLHandle-SQLText

SQL Handle to SQL Text

DatabaseSizeInfo


Database Size Info
Gather Database Size , LogSize , Allocated and Available Percentages for each database in Given Instance

database_idnamestate_descRecoveryModelTotalDBSizeDataSizedata_used_sizePctDataUsedLogSizelog_used_sizePctDataUsedfull_last_datefull_sizelog_last_datelog_size
53A_FullBackupONLINEFULL28.0027.001.315.001.000.362.782016-08-05 04:01:15.0004.382016-08-05 20:02:08.0000.14
4msdbONLINESIMPLE142.63107.7567.2562.0034.8810.453.342016-08-05 04:04:11.00074.17NULLNULL
3modelONLINESIMPLE1.811.311.31100.000.500.261.922016-08-05 04:04:05.0004.38NULLNULL
2tempdbONLINESIMPLE6500.006000.0014.940.00500.00874.070.57NULLNULLNULLNULL
1masterONLINESIMPLE4.504.002.8872.000.500.411.222016-08-05 19:48:53.0004.38NULLNULL

SQL Server Memory Usage

Leave a Reply

Your email address will not be published. Required fields are marked *

*

Top