When investigating a performance issue you MUST ask the following questions to begin.
- What exactly is slow, what area, what task is being performed. Take clear notes of what the client advises.
- Is the issue affecting one PC or all PCs in the office?
Issue is affecting one PC, PC Connects to the Server to Access the Database- All Data Slow
- Uptime of the PC. To check uptime open Task Manager, choose Properties tab and see Up Time at the bottom of the screen.
If you cannot see up time from here the other way to get up time is to open command prompt and check from there. Go to the start menu, search or run ‘cmd’. When cmd opens type the following –
For a local PC – net statistics workstation, then hit enter.
A lot of information will appear, scroll back up to where you had typed in the information and you will see ‘Statistics since’, followed by a date. This date is the date when the PC was last restarted.
If a PC is up a few days and the user finds it is slow, ask them to reboot the PC.
- Ensure that the user has good internet connection. If the internet connection is weak then the client is going to encounter performance issues.
These two things will normally resolve the issue on a PC where all data is affected.
Issue is Affecting All Users, all Data
- Get connected to the server where SQL is installed and begin by checking the version of SQL installed.
Launch SQL Server Management Studio. On the first screen select ‘Connect’. From the left panel expand ‘Databases’. Right click on the database being used and select ‘New Query’.
On the right panel type ‘Select @@version’ and select ‘Execute’ in the toolbar. The version of SQL installed will be displayed in the results window as highlighted at point 3 below.
Different versions of SQL have different database size limits. As the database beings to reach this limit it will begin to slow down until it eventually stops working. Below are the version of SQL and the limits. If the client is using a standard version of SQL you do not need to check the size of the database as it will not matter.
- SQL 2017, 2016, 2014, 2012 Express: 10GB
- SQL 2008 R2 Express: 10GB
- SQL 2008 Express: 4GB
- SQL 2005 Express: 4GB
- SQL STANDARD (All years): Unlimited.
Now that you know the data limits the next step is to check the size of the database. To check this open SQL, Connect. Right click on the database, select Properties.
Choose ‘Files’ from the left panel.
Then scroll to the right until you see ‘Path’. This is the location of the database files. You will also see ‘File Name’; these are the files you are looking for. Go to that location on the PC.
From here you will see the size of the ldf and the mdf. The combined total of the ldf and mdf should NOT be near the max limit of the SQL Version installed. Note: the LDF should ideally always be less than 1GB. The ldf can sometimes grow to up to 30GB, if the ldf is larger than expected you will need to truncate the database. (See guide)
If the mdf is approaching its max size for the version of SQL being used you will need to advise the client that they need to speak to their IT about upgrading to SQL Standard. This is a paid for version of SQL, can be quite expensive but not something that we can assist with. The IT organise the purchase and install of SQL Standard.
- Check the uptime of the server.
To check uptime open Task Manager, choose Properties tab and see Up Time at the bottom of the screen.
If you cannot see up time from here the other way to get up time is to open command prompt and check from there. To do this go to the start menu, search or run ‘cmd’. When cmd opens type the following –
For a server – net statistics server (below example is taken from a local, replace ‘workstation with server’)
A lot of information will appear, scroll back up to where you had typed in the information and you will see ‘Statistics since’, followed by a date. This date is the date when the Server was last restarted.
If the server is up over 3 weeks and the ‘Memory’ (Can be found in Task Manager, Performance) is running high (over 90%) ask the IT to reboot the server. This must always be done by the IT.
- If the above steps have not uncovered the issue log into Accounts Production on the server and see if you can replicate the issue.
If you cannot replicate the issue and SQL Standard is being used then it will be a network issue for the IT to look at.