On 20 août 2018
dans Méthode, Procédure, SQL Server
If this counter is still growing the server has not yet reached its steady-state, and it is still trying to populate the cache and get pages loaded into memory. Performance will likely be somewhat slower during this time since more disk I/O is required at this stage. This behavior is normal. Eventually Total Server Memory should approximate Target Server Memory.
Note: For “Lock Wait Time” it is recommended to look beyond the Avg value. Look for any peaks that are close (or exceeds) to a wait of 60 sec. Though this counter counts how many total milliseconds SQL Server is waiting on locks during the last second, but the counter actually records at the end of locking event. So most probably the peaks represent one huge locking event. If those events exceeds more than 60seconds then they may have extended blocking and could be an issue. In such cases, thoroughly analyze the blocking script output. Some applications are written for timing out after 60 seconds and that’s not acceptable response for those applications.
Over 1000 batch requests per second indicates a very busy SQL Server, and could mean that if you are not already experiencing a CPU bottleneck, that you may very well soon
From a network bottleneck approach, a typical 100Mbs network card is only able to handle about 3000 batch requests per second. If you have a server that is this busy, you may need to have two or more network cards, or go to a 1Gbs network card.
On 9 août 2018
dans Script, SQL Server
-- Donne la liste des compteurs avec leur type
select object_name, counter_name, instance_name, cntr_value, CASE
WHEN cntr_type = 1073939712 THEN 'LARGE_RAW_BASE'
WHEN cntr_type = 537003264 THEN 'LARGE_RAW_FRACTION'
WHEN cntr_type = 1073874176 THEN 'AVERAGE_BULK'
WHEN cntr_type = 272696576 THEN 'COUNTER_BULK_COUNT'
WHEN cntr_type = 65792 THEN 'COUNTER_LARGE_RAWCOUNT'
END cntr_type from sys.dm_os_performance_counters
Fièrement propulsé par Fred & Co.