How to Check Server Load on a Windows Server

Posted on by Ross Chesley
Category: Common Fixes | Tags: , , , , , , , , , , ,
Reading Time: 6 minutes

What Does Server Load Mean?

Checking a server’s load allows us to evaluate server resources and confirm they are sufficient for any running application. It enables us to troubleshoot slow performance and reliably pinpoint any server resource that may need attention.

While there are many tools and options available, today let’s focus on Windows Task Manager as a way to help us quickly see what is going on, and interact with applications, processes, and services to identify the load. This article will also include an introduction to Resource Monitor as it can be opened from Task Manager to provide more detail.

Ways to start Task Manager

  • Click the Start menu and type task… then choose Task Manager
  • Right-click the Taskbar area and choose Task Manager from the menu
  • Press Ctrl+Alt+End keys on the keyboard when in a Remote Desktop session
  • Run the command taskmgr

Let’s bring up the Task Manager and take a look at what it has to offer.

On systems where it has never been used, you may find Task Manager offering this very uninteresting display. Click More details to discover the treasure trove of information it is hiding.

Task Manager provides quick access to Processes, Performance, Users, Details, and Services. We’ll go through each tab to see what they have to offer and discover what to look for when checking server load.

Processes

The Processes tab shows us everything that is running in the system and the amount of CPU and memory resources it is using. At the top, we can see the total CPU and memory utilization.

By clicking the CPU or Memory column headings, we can sort the processes list by that criteria, and use the sort arrow to determine whether to sort from highest to lowest usage or the opposite. You can click on any individual process and end the task, see resource usage, and more.

Troubleshooting Tip:
If we see a particular application is using a high amount of the CPU or memory, it may be a potential source of performance issues. In the example above, we can see this server is using 78% of memory and only a very small amount of the CPU.

Performance

Performance tab has the most visual display of information and allows us to select from CPU, Memory, and Ethernet views to show activity over a 60 second period. With this view, we can identify spikes or see the trend over time to determine if a condition is temporary or sustained.

 

CPU Performance

CPU performance information shows us the type of CPU and speed, the number of processes, threads, and handles in use, as well as the number of virtual CPUs, in most cases. We can also see how long the system has been up (up time). This last bit of information can tell us how long the server has been running, confirm if it successfully completed a restart, or if it rebooted unexpectedly due to running out of resources.

Troubleshooting Tip:
In this example, we see the CPU is at 94%. If this level or higher is sustained over a long period of time, server performance will be sluggish, and it could affect the stability of the system. Sustained high CPU use is an indicator the system is struggling. We need to look at other systems to determine whether it is due to applications or insufficient physical memory that pushes the system to use virtual memory. Doing this will cause the CPU and disk resources to spike and remain high.

 

Memory Performance

Memory Performance information shows us the total amount of memory in the system as well as what is in use and available. Committed represents virtual memory and the pagefile (an extension of RAM) on disk. Cached represents memory used by Windows, and the Paged pool represents memory used by Windows that can be paged out to the pagefile on disk if memory starts running low. Non-paged cannot be paged to the pagefile.

Troubleshooting Tip:
n this example, we see the CPU is at 94%, Memory is at 90%, and we are using virtual memory. When looking at the Committed Memory, we can see that virtual memory is 2.7 GB while the pagefile is 4.9 GB. In this example, we have not maxed out the pagefile. If we find the system is continuously running with the CPU and Memory at or above 90%, it is a strong indicator to add physical memory to the system to reduce the use of virtual memory.

 

Ethernet Performance

Ethernet performance information shows us the type of network adapter and the amount of resources it is using with a graphed line for both send and receive as well as numeric values for data being sent. We can also see the Adapter name, Connection type, and the IP address(es) assigned. Right-clicking on the graph will allow us to see network details including network utilization, link speed and state, bytes send and received, etc. On the Performance tab, we also have the option to launch Resource Monitor to see even more detail.

 

Users

The Users tab shows us a list of all the users connected to the server and how much CPU and memory resources the user is utilizing. We can click on a specific user to Disconnect them, send them a message, or take over their session if we have Administrator rights. In the context of checking for load, we can determine if a specific user is consuming too many resources or has disconnected from a session, leaving it running in memory, and choose whether to log the user out to free up resources.

Details

The Details tab shows us a list of all the running programs and processes along with their PID (Process ID) number, whether the program is running or suspended, the user name it is running under, the amount of CPU and memory it is using, and a description of the process. You can click any of the column names to sort by that column in highest to lowest or the opposite order. The PID number can be very helpful to track down a specific process that is referenced in event logs. Right-clicking an item allows us to choose options including:

  • ending a process or process tree
  • set a priority for the running process
  • establish affinity to a specific processor or all processors
  • additional options

 

Services

The Services tab shows us a list of service names, their PID (Process ID) numbers, a description of the service, the status as either stopped or running, and the Group the service is running under. Right-clicking on a service allows us to start, stop, restart, and access additional options. We should be careful not to change the status of some services as they depend on others, and stopping the wrong one could have unintended consequences on the system or devices. To learn more about a service, we can right-click it and choose Search Online.

How Do I Check My Resouce Monitor?

Ways to start Resource Monitor

  • Click the Start menu and type resource… then choose Resource Monitor
  • Right-click the Taskbar area and choose Task Manager from the menu, then from Performance tab choose Open Resource Monitor
  • Run the command resmon

Let’s bring up Resource Monitor and take a look at what it has to offer. You’ll find this has more depth but is very similar to the information available from Task Manager. For this reason, we’ll only cover the overview and a brief description of each tab in this article.

Overview provides us with data on CPU, Memory, Disk, and Network options and graphs all on one page with the option to expand or collapse each section. It will also show current usage of a resource as well as the highest active time. Clicking individual sections provides more detail.

CPU shows processes, services, associated handles, and modules, and will show individual CPUs and their load in addition to total CPU.

Memory shows processes in addition to a breakdown of the physical memory and graphs to show commit charge which relates to use of the pagefile and the number of hard faults per second which can be an indicator of how many times Windows has to access the swap file. If your system is showing hundreds of hard faults per second, this indicates a need more physical memory.

Disk shows the processes in addition to a breakdown of how much each task is reading and writing to disk. The graphs show total disk activity in addition to Queue Length. Disk Queue length indicates how many disk I/O operations are queued up waiting for their turn to be processed by the disk. If we find that the highest active time is above 80% and the disk queue length is 2 or higher, it means processes are waiting, and the performance of the disk is affecting the overall performance of the system. In many cases, this number will be high due to a system that lacks sufficient physical memory and is constantly paging information to disk or relying too heavily on virtual memory. It will often be accompanied by a CPU running above 90% for sustained periods.

Network shows the processes with network activity, in addition to TCP connections and listening ports, and graphs to show network transfer and TCP connections. Sustained high network utilization can indicate congestion issues and a need for more capacity.

Still having trouble determining what is bogging down your server?  With Liquid Web’s servers, you can talk to a experienced support tech night or day.  Our techs have the expertise needed to help determine bottlenecks in your system. Switch to Liquid Web today and get the support you’ve been looking for!

How to Setup and Use Microsoft SQL Server Management Studio

Posted on by Ross Chesley | Updated:
Category: Getting Started | Tags: , , ,
Reading Time: 4 minutes

What is SSMS?

SQL Server Management Studio (SSMS) is a free Windows application to configure, manage, and administer Microsoft SQL Server (MSSQL). SSMS includes an Object Explorer to view and interact with databases and other elements, a Query window to write and execute Transact-SQL queries, and script editors for developers and administrators.

Installing SSMS

Step 1: Let’s get started by installing SSMS. The first step is to download the latest general availability (GA) version of SSMS. General availabilityis the most stable version for everyone to use.

When downloading, choosing Save As will allow us to choose the location to save the installer file for easy access. Let’s save it to the Desktop.

Step 2: Next, from our desktop, we double-click to open the SSMS Setup file to begin the SSMS install process.

Step 3: Installing SSMS is a very simple and straightforward process. When the installer opens, we need to click Install to accept the license and start installing components.

The installation will take several minutes and may require a restart of your computer if it needs to update older components. Once setup is completed and all specified components are installed successfully, you can close the installer and remove it from your system.

[OPTIONAL]:If you need to install MSSQL, you may find this KB article helpful.

To install MSSQL or run SSMS from the server where it is installed, you will need to use Remote Desktop to log in to the server. If you need more information about Remote Desktop Connection, these Knowledge Base articles will help you get connected:

How to Use a Remote Desktop
Windows: Accessing Your Server with Remote Desktop
Using Remote Desktop Protocol (RDP) to Log into Your Windows Server

 

Using SSMS to Manage MSSQL

The settings we use when connecting to MSSQL with SSMS depend on whether we are accessing it on a remote system/network or locally.

  • When accessing MSSQL locally we can refer to server name as localhost.
  • On remote systems the most reliable way to reference the server is by the IP address.

 

Step 1: Let’s jump right in by opening SSMS. To open SSMS, click the

Start Menu and start typing SSMS or look for the program in the Microsoft SQL Server Tools folder. For convenience, you may choose to right-click the program and pin it to the Start menu and/or pin it to the taskbar, so it is easier to find.

Step 2: When we start SSMS, it asks us to connect to a MSSQL server and allows us to set connection properties. Below are available options and what we need to supply to get connected.

These fields in the SSMS login need to be filled in to connect to your MSSQL instance.

 

If you are having difficulties connecting to MSSQL on a remote system, you or the SQL Administrator may need to configure MSSQL to allow TCP/IP Ports and configure a hardware firewall or the Windows Firewall to allow access from your IP address. This KB article covers Windows Firewall Basics.

If you experience Error 18456 when attempting to connect to MSSQL, this KB article can help you identify and resolve the issue: Troubleshooting Error 18456, login failed for user

Step 3: Once we have entered the required information, we can click Connect to access to the SQL Server.

 

SSMS Object Explorer

The SSMS Object Explorer is the main area we will interact with to Manage MSSQL. It includes options for managing databases, security and more.

At the top of Object Explorer, we have a Connect drop-down that allows us to make more connections, and icons to connect/disconnect the current MSSQL server object. The example below shows we are connected to a MSSQL server on IP 192.168.11.128 on the default port 1433, as the user RemoteUser.

Right-Clicking Server Objects in Object Explorer allows you to access menus for additional tasks related to the object type.

Expanding Objects in Object Explorer allows you to see the contents, and interact and manage them. To expand an object, you can double-click it, or click on the [+] symbol to the left of the object.

 

Managing Databases in SSMS Object Explorer

Let’s create a new database…expand Databases to see the current list of databases. Right-click Databases and choose New Database…

The New Database page opens. This is where we name the database, select the owner and set options including the MSSQL compatibility level. Once we are done, we click OK to create the database with the options we have selected.

 

Managing Security in SSMS Object Explorer

Let’s create a Login…expand Security to see the available objects. Expand Logins to see the current list of logins. Right-click Logins and choose New Login…

The New Login page opens. This is where we name login (1), set the authentication type, set a password, select a role, and map the user to a database. In most cases, we want to create SQL logins, so we choose SQL Authentication (2). It is a good idea to uncheck the box for User must change password at next login (3) to avoid the potential for remote logins to fail with Error 18456. Set the Server Roles (4) to allow the login to have any required server-wide privileges. User Mapping allows us to map the login to databases and select the user and default schema. Once we are done configuring the login, click OK (5) to create the login with the options we have selected.

Let’s allow login for a user and change permissions. In SSMS Object Explorer, expand Security, Logins. A red x on a Login indicates login is disabled.

To fix this, right-click the user and choose Properties, then click the Status page. Enabling login for the user and click OK.

We can fix the issue where a user’s login is locked out, and grant login permission to connect to the database engine. Object Explorer, Security allows us to change properties of logins, reset passwords, rename the login, and delete the login.

In this article, we learned that SSMS is a powerful and convenient way to manage a MSSQL Server. SSMS is free, it can be installed with only a few steps on a Windows system, and it will allow you to connect to and manage MSSQL Server directly from the server or a remote workstation.

 

Troubleshooting Microsoft SQL Server Error 18456, Login failed for user

Posted on by Ross Chesley
Category: Tutorials | Tags: , , , , , ,
Reading Time: 4 minutes

Login errors with Microsoft SQL Server (MSSQL) are a fairly common issue and can be easily solved with some basic troubleshooting steps. Before we dig in, let’s take a look at the details of the error to try and determine the cause.

Solutions to Microsoft SQL Server Error 18456

Sometimes, the error presents as “login failed for user ‘<username>’,” this information will help us as we identify the user we need to troubleshoot. From the message, we’ll know the error number as a reference to search for next steps. In this case, it is Microsoft SQL Server, Error: 18456.

Other times, we may only see “Microsoft SQL Server Error 18456” along with severity and state number. On its own, a state number might not mean much, yet it can offer more details as to what is wrong and where to look next.

These states of the error, 18456, are the most common. The descriptions and potential solutions offer a quick explanation and potential troubleshooting guide.

For Microsoft SQL Server Error 18456, there are several solutions for solving the issue.

Step 1:  Log In with Remote Desktop

The troubleshooting and solutions require you to login to the server or at least be able to make a Windows Authentication connection to MSSQL using Microsoft SQL Server Management Studio. The most common and easiest method is to connect directly to the server with a Remote Desktop Connection. If you need more information about Remote Desktop Connection, these Knowledge Base articles will help you get connected:

Step 2: Run Microsoft SQL Server Management

Once you are logged into the server, you’ll want to run Microsoft SQL Server Management Studio (SSMS). SSMS is the tool best suited to configure, manage, and administer MSSQL.

When you start SSMS, you will be asked to log in to the server. By default, most MSSQL servers have Windows Authentication enabled, meaning you must log in with the Windows Administrator or the account specified as the SQL Administrator when MSSQL was installed and configured.

In addition to Windows Authentication, MSSQL supports SQL Server Authentication. Depending on the version of MSSQL and how it was installed and configured, you may or may not have SQL Server Authentication enabled by default.

Step 3: Checking the Server Authentication Mode

Once we login to SSMS using Windows Authentication, we need to check the security settings to confirm whether MSSQL is set up to allow both Windows and SQL Authentication.

In SSMS, right-click the Server Name at the top of the Object Explorer window and choose Properties.

Next, click the Security page.

If you find Windows Authentication is the only mode configured, this is the likely cause of Error 18456, Login failed for user ‘<username>’.

Setting the Server authentication mode to allow SQL Server and Windows Authentication, you will be able to login to MS-SQL with a SQL user and password or a Windows user and password. After making this change, you will need to restart the SQL Server service.

 

Step 4: Restart the SQL Service

In SSMS, right-click the Server Name at the top of the Object Explorer window and choose Restart to apply the new authentication mode settings.  

In the above example, Windows Authentication mode was the only mode configured, and the Error 18456 occurred because the user ‘sa’ is a SQL user and SQL Server Authentication was not permitted.

 

Step 5: Checking SQL User Permissions

As we check the SQL user permissions, we need to answer the following questions:

  • Is the user allowed to log in?
  • Does the user have a valid password set up?
  • Does the user have the needed permissions for access to the desired database?

In SSMS Object Explorer, expand Security, Logins. Locate the user that was failing to log in. A red x on the user indicates this user has login disabled.

To allow the user to login, right-click the user and choose Properties, then click the Status page. Enabling login for the user and click OK.

After refreshing the list user logins, we can confirm the user no longer has a red x present. This should allow the user to log in. In this example, the SQL user ‘sa’ failed to log in because there was no permission to log in. 

Continuing with user troubleshooting, right-click the user and choose Properties, then click the General page. Here you can enter a new password and then enter the confirmation password. Click OK to save the new password. We set a new password for the user so that we are certain of the password when we attempt to log in.

Step 6: Mapping the User to the Database

Our last step in troubleshooting a user is to check user mapping to verify the user has access to the desired database and to set or verify their role for the database. Right-click the user and choose Properties, then click the User Mapping page. Select the Database from the list of databases. From the database role memberships, select the desired/required memberships. Click OK.

In this example, we mapped the user ‘ProdX709’ to the database Production X709.2019 and granted them database role db_owner. In many cases, you only need a user to have db_datareader and db_datawriter roles to be able to read and write to the database.

 

In this troubleshooting article, we learned how to identify specifics of Error 18456 to help us track down the root cause of the issue. Still looking for support?  Our MSSQL database solutions come with assistance from our technical support team. Find out how our high-availability database can work for you!