Monitoring your CPU performance from time to time is important in order to know if your machines’ computing power is sufficient for your business needs, also it lets you know in advance about bottlenecks that might negatively affect your systems’ performance in the future and prevent problems from occurring by better planning your resource allocation.

Usually, users and managers only check the CPU performance when they notice a slowdown in the system or when something is not working right and most of these problems can be prevented by monitoring CPU performance as a routine maintenance task.

AimBetter enables you to easily analyze CPU processes, services and performance. It automatically monitors all your servers and helps you both routinely monitor your CPU performance and easily analyze it whenever you encounter any performance issue.

When most users and managers encounter a slowdown in their server’s performance, they usually open the task manager, in which they can see CPU usage per process but not per service, they can see the total threads and total processes but not threads per process and they have no way to know for any process how much CPU is used by the kernel and how much by the user, not to mention the different performance counters that provide critical information for troubleshooting.

In AimBetter, the CPU section of OS Tools is divided to 3 subsections: Process, Services and System.
The CPU section of the OS Tools is a complementary to the CPU section of the performance screen as it gives you detailed information about your CPU usage, in the process level, in the service level and in the performance counter level, it also shows statistic information about counters for the most recent round minute.

The process subsection presents the following information for all the processes: process name, process ID, threads launched, threads used, kernel CPU%, user CPU% and total CPU%.

The services subsection presents the following information for all the services: process executable, process ID, CPU%.

In the services subsection there is also a drilldown of services per process:

The system subsection presents the following information for each performance counter: performance counter name, mean value, minimum value and maximum value.

As shown above, the following performance counters (statistics) are monitored: % registry quota in use, context switches/second, exception dispatches/second, file control bytes/second, file control operations/second, file data operations/second, file read bytes/second, file read operations/second, file write bytes/second, file write operations/second, active processes, process queue length, system calls/second, system up time and threads.

AimBetter provides thorough information about all the CPU metrics affected by your operating system and displays every detail that has a meaning regarding your systems’ performance, this way it lets you know immediately how well your hardware match your software requirements and your systems’ operational demands and help you plan hardware changes according to your business plans and growth expectations. It also helps you know which servers are available for more tasks and which ones are too loaded so that you could balance the load between your servers and optimize the total performance of your systems.

Jitter is the variation in latency as measured in the variability over time of the packet latency across a network. A network with constant latency has no jitter.

In quantitative terms, jitter is the average of the deviation from the network mean latency. The standards-based term for network jitter is Packet Delay Variation (PDV). PDV is an important Quality of Service (QoS) factor in assessment of network performance.

The delay is specified from the start of the packet being transmitted at the source to the start of the packet being received at the destination. A component of the delay which does not vary from packet to packet can be ignored, hence if the packet sizes are the same and packets always take the same time to be processed at the destination then the packet arrival time at the destination could be used instead of the time the end of the packet is received.

For interactive real-time applications, PDV can be a serious issue and hence VoIP and video streaming may need QoS-enabled networks to provide a high quality channel.

The effects of PDV in multimedia can be removed by a properly sized play-out buffer at the receiver, which can only cause a detectable delay before the start of media playback.

There are many tools in the market that monitor jitter, but most of them monitor network transportation only and nothing more. Among the best known network monitoring tools are: PRTG network monitor, OpManager, Cisco IOS SAA, Iperf and SolarWinds VNQM.

AimBetter is an all-in-one monitoring tool that monitors operating system servers, database servers and web servers including all the relevant metrics for each type.

In the following screenshot you can see the network jitter of a few servers:

In the following screenshot you can see the jitter trend of a server:

While most network monitoring tools only allow you to view the current state, AimBetter records your metrics every minute and stores them for as long as you set it to so you could analyze trends and troubleshoot even when you don’t have the trouble in real time, moreover, AimBetter enables you to compare trends of equal periods, as shown in the following screenshot:

Not only that AimBetter allows you to view the average of each presented time frame, as most tools that present trends do, it also allows you to view peaks of time frames, as presented in the following screenshot:

If your systems include VoIP, video streaming and/or UDP transportation using applications it is very important for you to monitor jitter in order to assure high QoS for these applications and AimBetter is designed to make it as easy as possible for you.

Efficiently monitoring your database backups is crucial for your system, as without these reports you won’t be able to restore your system in case of data loss or data corruption. You also wouldn’t be to follow changes in your data over time. For these reasons alone, you must make sure that all of your databases are backed up regularly, each by a service that is best matched to the database’s role in the system and to your organizational needs.

SQL Server Maintenance Plans or third party software are currently the most popular ways to create backup plans. Third party software is also used for monitoring the database.

The following are the most common backup devices:

• local hard drive
• backup tape
• network device

In order to verify that the backup finished successfully most DBAs set an automatic email to be sent to them in case of a failure in the job and they regularly check the automatic emails sent to them so that they know when a backup process fails. Though this is the most common practice, it is not the best practice because a backup process might be halted for any number of reasons. In such a case there would be neither a successful finish notification nor a failure notification. Consequently, it is vital that DBAs verify the success of their backups independently, without relying on automatic reports.

As yourself these questions to make sure that your database backup finished successfully:

• Do my database backups match my backup plans and policies?
• Where are my backups and where do I backup each database to?
• What is my backup type and what was the recovery mode of my database during the backup process?
• Who ran the backup? Was it my service or maybe an SQL injection stealing my data?
• What were the times, durations and sizes of each backup?
• Where can I get alerts when the backup size is abnormal or when the duration is out of the ordinary?
• Did I run the backup during the middle of the day (FULL BACKUP) within working hours?
• If I run several backup operators (Backup local, Network Path, Backup Tape, Virtual SNAPSHOT), are any of them blocked? Do they block each other?
• Did I backup the Log Database using a different operator? We need all log files in order to be able to restore successfully.

Normally, in order to check all of the above you’d need to query MSDB. The MSDB system database is the primary repository for storage of SQL Agent, backup management, Service Broker, Database Mail, Log Shipping, restore manager, and maintenance plan metadata. Here are a few tables that document the backup process:

• backupset: provides information concerning the most-granular details of the backup process
• backupmediafamily: provides metadata for the physical backup files as they relate to backup sets
• backupfile: this system view provides the most-granular information for the physical backup files

Using the following script you can get basic results regarding your backups
–This is an example of getting information about Database Backups for all databases during the previous 3 days:


SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 3)

Of course, the code above was a specific example for a specific case – if you’d like to get more concrete information or filter your information to specific database(s), backup type, back-up device or day(s), etc. you’ll have to alter your query accordingly.

Using AIMBETTER you can get all of this information and relevant alerts.

A few screenshots that illustrate the problems we listed are as below:

Optional filters

Detailed results

Alerts on missed backups

AimBetter enables you to see all the backup information you request in a simple way and on one screen so that you won’t have to remember queries by heart in order to efficiently monitor your backups, which makes it a more efficient and more convenient alternative to today’s outdated (albeit most common) backup practice.

A deadlock in SQL Server occurs when two or more sessions inside of the database engine end up waiting for access to locked resources held by each other. In a deadlock situation, none of the sessions can continue to execute until one of those sessions releases its locks, so allowing the other session(s) access to the locked resource. Multiple processes persistently blocking each other, in an irresolvable state, will eventually result in a halt to processing inside the database engine. A common misconception is that DBAs need to intervene to kill one of the processes involved in a deadlock. In fact, SQL Server is designed to detect and resolve deadlocks automatically, through the use the Lock Monitor, a background process that is initiated when the SQL Server Instance is started. You can monitor your locks using AimBetter in a much easier way, immediately see your deadlock victims and also see your deadlock survivors in the drilldown screen. Using AimBetter, you may see the codes causing deadlocks and handle them in a much simpler way than using trace flags and XML Deadlock Graphs, also, unlike the conventional way, you can easily see historical deadlocks and analyze your system for long term trends, therefore you could see the effects of your deadlocks handling policy.

SQL Profiler XML Deadlock Graph event

In SQL Server 2005 and up, the Deadlock Graph event in SQL Trace captures the deadlock graph information, without writing it to the SQL Server Error Log. The Deadlock Graph event is part of the Locks event category and can be added to a SQL Server Profiler trace by selecting the event in Profiler’s Trace Properties dialog, as shown in the following screenshot. SQL Profiler can be configured to save the deadlock graphs separately, into XDL files, as shown in the following screenshot. AimBetter would tell you the same information as the profiler but in a much easier way, all you need to do is to click on SQL Tools and then on DeadLocks, then you see all the details.

Handling Deadlocks to Prevent Errors

In most cases, the same issues that cause severe blocking in the database, such as poor database design, lack of indexing, poorly designed queries, inappropriate isolation level and so on, are also the common causes of deadlocking. In most cases, by fixing such issues, we can prevent deadlocks from occurring. Unfortunately, by the time deadlocks become a problem, it may not be possible to make the necessary design changes to correct them. Therefore, an important part of application and database design is defensive programming; a technique that anticipates and handles exceptions as a part of the general code base for an application or database. Defensive programming to handle deadlock exceptions can be implemented in two different ways:

• database-side, through the use of T-SQL TRY…CATCH blocks
• application-side, through the use of application TRY…CATCH blocks.

Since AimBetter lets you view and edit the code you won’t need the try and catch blocks, you’ll find the deadlock causes immediately:

Summary

This article has covered how to capture and interpret deadlock graph information in SQL Server to troubleshoot deadlocking. Most often, deadlocks are the result of a design problem in the database or code that can be fixed to prevent the deadlock from occurring. However, when changes to the database are not possible to resolve the deadlock, adding appropriate error handling in the application code reduces the impact caused by a deadlock occurring. The information included in this article should allow rapid and efficient troubleshooting of most deadlocks in SQL Server. AimBetter is the best tool in the market that helps you handle deadlocks and doesn’t require you to use complex code in order to troubleshoot deadlocks, additionally it monitors all of your database operations for you automatically, therefore, it saves you a lot of time and effort and is the best practice for your db maintenance.

General Description

Database locking is a varied, evolving, complicated, and technical topic.

A lock is used when multiple users need to access a database concurrently. This prevents data from being corrupted or invalidated when multiple users try to read while others write to the database. Any single user can only modify those database records (that is, items in the database) to which they have applied a lock that gives them exclusive access to the record until the lock is released. Locking not only provides exclusivity to writes but also prevents (or controls) reading of unfinished modifications (AKA uncommitted data).
Locks can occur for the following types of items: Tables, Data Rows, Data Blocks, Cached Items, Connections and entire systems.

Most of what we’re calling transactional locking relates to the ability of a database management system (DBMS) to ensure reliable transactions that adhere to these ACID properties. ACID is an acronym that stands for Atomicity, Consistency, Isolation, and Durability. However, all of these properties are related and must be considered together. They are more like different views of the same object than independent things.

Factor in slowness

In multi-user systems, locks are a significant factor to slowness because of access requests on items that are being read from or written to by other users and are already locked, that forces them to wait for each item to be unlocked. It is critical to understand that locks will often remain after a statement has finished executing. That is, a transaction may be busy with different, subsequent activity but still hold locks on a table due to an earlier statement. Transactions may even be idle. This is especially dangerous if the application allows user think time within database transactions.

There are a number of problems that can be caused by database locking. They can generally be broken down into 4 categories: Lock Contention, Long Term Blocking, Database Deadlocks, and System Deadlocks.

In order to avoid these problems as much as possible they should be monitored regularly and handled individually. The AimBetter system monitors your database in real time and allows you to see locks and long queries running on your databases at any time.

Difficulty in identifying past issues

When we don’t regularly monitor locks and long queries we cannot identify problems in running queries and procedures in until we catch them just in time of occurrence and even then it is possible that while we run our tests the problem ends and we fail to catch it in time.

AimBetter records all the events in your system and keeps them for as long as you set, so when you come to handle deadlocks and long queries from AimBetter you can view such events that occurred in the past and deal with each case individually.

Here is an example of long locks queries stored in history:

Remote control out of the office

AimBetter is a complete Web system that allows you to connect to it via any Internet-connected device and monitor your system from anywhere, even when you’re not in the office.

Analysis and pattern recognition

AimBetter lets you see all the long queries and locks currently active on your system and allows you to copy them to the SQL Server Management Studio, make changes and simulate them in order to explore ways to resolve:

Users committing exceptional heavy transactions

AimBetter allows you to identify users and applications that run exceptionally long transactions and deal each case accordingly. The user name you’ll see a column Session:

Collision between applications and users activity

AimBetter system allows you to see all users running long queries or locking elements and all applications competing for the same resources. You can identify them in accordance with Session and Program column screen.

Summary

You can AimBetter system using real-time monitor locks and long queries and know about such events have occurred in history and use this information to optimize your systems, improve performance, to significantly reduce the frequency of locks and shorten the time of long-running queries.

Having enough free disk space on your servers is critical for routine processes such as saving data, backups and other processes running on the SQL server. Consequently, it is important to know how to check whether you have enough free disk space on your server or whether more is needed.

The most common way to check whether there is enough free disk space in the server is with the SQL Server Management Studio which functions as follows:
The stored procedure sys.xp_fixeddrives (SQL Server 2005 and up) checks free disk space. With this information you will still need to know whether your free disk space is greater than the required minimum for any process. You can figure this out using the following stored procedure:

CREATE PROCEDURE dbo.spExec_SufficientDiskSpace @MinMBFree int, @Drive char(1) AS
/*
----------------------------------------------------------------------------
-- Object Name: dbo.spExec_SufficientDiskSpace
-- Dependent Objects: master.sys.xp_fixeddrives
-- Called By: Admin Scripts
--------------------------------------------------------------------------------------
*/
SET NOCOUNT ON
-- 1 - Declare variables
DECLARE @MBfree int

-- 2 - Initialize variables
SET @MBfree = 0

-- 3 - Create temp tables
CREATE TABLE #tbl_xp_fixeddrives
(Drive varchar(2) NOT NULL,
[MB free] int NOT NULL)

-- 4 - Populate #tbl_xp_fixeddrives
INSERT INTO #tbl_xp_fixeddrives(Drive, [MB free])
EXEC master.sys.xp_fixeddrives

-- 5 - Initialize the @MBfree value
SELECT @MBfree = [MB free]
FROM #tbl_xp_fixeddrives
WHERE Drive = @Drive

-- 6 - Determine if sufficient free space is available
IF @MBfree > @MinMBFree
BEGIN
RETURN
END
ELSE
BEGIN
RAISERROR ('*** ERROR *** - Insufficient disk space.', 16, 1)
END

-- 7 - DROP TABLE #tbl_xp_fixeddrives
DROP TABLE #tbl_xp_fixeddrives

SET NOCOUNT OFF
GO


Using this method of checking, you would have to add to every process that requires disk space with an exec command that runs the stored procedure with the relevant parameter values. @MinMBFree would contain the minimal free disk space needed for the process and @Drive would contain the relevant drive letter of the drive used by the process. In case you would like to receive an email a notification when there is not enough free disk space, you’d have to embed the SQL code in HTML in order to enable emailing.

In contrast to this traditional, time-consuming approach of checking available disk space, the AimBetter system monitors the free disk space of all of the disks in all of the defined servers and alerts the AimBetter team about any decrease of free disk space below the threshold we’ve set, whether on the system level, the server level or on the specific disk level. With AimBetter you can also compare the trends of free disk space on a daily, weekly or monthly basis and therefore know whether the pattern is routine or exceptional so that you can respond accordingly.

The AimBetter system samples every predefined server every minute so that whenever you are logged into the system you can view the free disk space of any disk in any server monitored by it with no need to manually run any scripts whatsoever.

In addition to informing you know how much disk space is used by any monitored disk, AimBetter also tells you how busy your disk is percentagewise (in other words, how much disk space is currently used by READ/WRITE processes).
Lastly, the AimBetter system can also send email alerts according to your settings so that you can know about any exceptions to your thresholds immediately without the need to manually check your free disk space before any disk space-consuming process takes place. The system can also provide alerts about exceptional decreases in free disk space as indicated by your settings.

If free disk space is a critical factor or problem for your company, AimBetter can provide a convenient, comprehensive solution by monitoring your disk space, highlighting usage trends and alerting you about any decrease below your thresholds and/or exceptional decreases as indicated by your settings.