Check the SQL Server and the database...
How to measure performance of your SQL server
Issues:
In some menu paths in Exact Globe 2000 or Exact Globe 2003 it takes a very long time to get the information on the screen, what can we do about it?
Solution:
This problem can be caused by several reasons.
For some solutions in this document it is necessary that the SQL Server Agent is running, below I will explain how to minimize the risk that this service is not running. This has no influence on the performance, but is needed to improve performance.
1. Automatically start the SQL Server Agent when the operating system starts;
2. Automatically restart the SQL Server Agent when it stops unexpectedly.
Follow the steps below to achieve above mentioned points:
- Start the Enterprise Manager, expand the SQL Server group, expand the proper SQL Server and expand management;
- Right-click on the SQL Server agent and click on properties;
- On the advanced tab, mark the first two options
- Click OK to close this screen;
- Right-click on the proper SQL Server and click on properties;
- On the general tab, mark the option “Autostart SQL Server Agent”.
There are several ways to improve the performance of a SQL Database and of the SQL Server, the scope of this document is to give an overview of all possibilities:
- Switch off auto shrink in the properties of the database;
- Make sure SQL does not use all memory of the server;
- Make sure the statistics are up to date and that they are regularly updated via a SQL job;
- Defragment indexes on a regular basis;
- Switch autogrowth from percentage to megabytes for databases that grow rapidly.
Switch off auto shrink in the properties of the database:
Auto shrink is a feature that automatically shrinks tables in a database to free up space in the database. This does not mean that it will also free up disk space, which can only be achieved by manually shrinking the databases. In big databases we advise to switch off this setting to prevent automatic shrinking, since this might have a negative impact on the performance.
In order to switch off the auto shrink option, take the following steps:
- start the enterprise manager and go to correct SQL server and to the correct database;
- right click on the database and select properties;
- go to the tab options and remove the tick at the option “auto shrink”.
Do this for every user database (not the master, model, tempdb and msdb database).
Make sure SQL does not use all memory of the server:
By design Microsoft SQL Server will use all memory that is available in the server. Because we supply the standard edition of Microsoft SQL Server, it will never use more than 2GB (effectively around 1.8GB). So in case the server is also running other services (like for example Exchange server or the server is a domain server), it is advisable to fix the memory used by SQL Server. If the server is only running SQL Server, set the memory allocation to dynamically configure SQL Server memory. In that case SQL Server will arrange the memory allocation in the most efficient way for SQL to use. If you want SQL Server to use more than 2GB of physical memory, you have to buy the Enterprise Edition of SQL Server which has a limit of 64GB physical memory.
In order to limit the amount of memory used by Microsoft SQL Server, take the following steps:
- start the enterprise manager and go to the correct Microsoft SQL Server;
- right click on this server and click on properties;
- on the tab memory you can limit the amount of memory used by Microsoft SQL Server. Mark the option “use a fixed memory size (MB)” and set the amount of memory you want to use.
Make sure the statistics are up to date and that they are regularly updated via a SQL job:
Statistics are very important for Microsoft SQL Server. This is a technical issue I will try to explain. With everything you do in Exact Globe 2000/2003 SQL queries are executed to retrieve data. In order to determine how to execute the SQL query in the most efficient way, SQL uses execution plans. In a execution plan you can see how a query will be executed and how much time this will cost (in percentages of the total). When generating an execution plan SQL uses the statistics to get the most efficient execution plan. In case these statistics are not up to date, less efficient execution plans are generated causing the speed of the queries to go down and thus the performance of Exact Globe 2000/2003 goes down.
There are three ways to keep the statistics up to date:
- automatically update the statistics
- sp_updatestats;
- update statistics;
Automatically update the statistics:
This is a database option and is switched on by default, this makes sure that the statistics are up to date during normal usage of the database. This means that they are up to date as long as no new indexes, columns or tables are created. This is an option per database and can be set at the same point as the option “auto shrink” which is described earlier in this document.
SP_Updatestats:
This is the same as update statistics (see next point), but it is executed for all user tables in the database. By default this stored procedure uses the default sampling option (explained later), but it can be set to use the resample option. The advantage of this method is that it runs for all databases, the disadvantage is that not all parameters can be used.
Update statistics:
The advantage of this way is that you can give some parameters to the SQL statement to run it in the way that is best for your situation. The disadvantage of this method is that it has to be executed per table. One of the parameters that can be used is described below, this is also the one we advise to use in specific cases (see later on in this document):
- Fullscan àThis means that all rows in a table or view should be read to gather statistics. It provides the same behavior as sample 100 percent.
Normally these statistics are updated automatically by Microsoft SQL Server, however this does not always work correctly. In case lots of inserts or updates are executed statistics can become out of date without being rebuilt. Therefore we advice to create SQL Agent jobs to keep the statistics up to date, at the end of the document it is described how to do this.
IMPORTANT: Updating the statistics can take several hours, depending on the size of the database, the accuracy of the statistics and the disk configuration. This process can be cancelled at all times without creating problems.
Defragment indexes on a regular basis:
Tables always consist of the actual data and the indexes. Indexes are used to search faster through a table to find the requested data. Data and indexes are both stored in the data file (mdf or ndf) of the database. Indexes are always stored in a tree structure (logically). However, physically it is stored in the same way as data. This means that when working with (update, insert, delete) the data the indexes might be moved to other parts of the data file. In that case the logical structure deviates from the physical structure, which has a negative impact on performance. This is only valid for the indexes, for the data itself this is not a problem. Describing the whole file structure of SQL goes beyond the scope of this document, to resolve this defragmented indexes the database needs to be defragmented on a regular basis. The easiest way to do this is via a schedules SQL job. Which is explained later.
IMPORTANT: Defragment of indexes can take several hours, depending on the size of the database, the percentage of Defragment and the disk configuration. This process can be cancelled at all times without creating problems.
Switch auto growth for databases from percentage to megabytes:
In databases that grow rapidly, SQL Server has to increase the size of the data files on a regular basis. This can have a negative impact on performance. In order to prevent this, the way the database grows has to be switch from percentage to megabytes. In that way you can control how big the database grows each time the free space in the database it too small. In order to switch this, take the following steps:
- Start the Enterprise Manager, expand your SQL Server group, expand your SQL server and expand databases;
- Right-click on the database and select properties;
- On the tab data files mark the option “In megabytes” and enter a figure you estimate to be enough. This depends on the database size;
- On the tab data files mark the option “In megabytes” and enter a figure you estimate to be enough. It depends on the selected recovery model how big the log file will be, this has to be taken into account when estimating the size of the file.
SQL Job to update statistics and defrag indexes automatically:
The easiest way to make sure the statistics and the indexes are optimized is to create a scheduled SQL job for this. It is advisable to create a job for each database separately. Below it is explained how to set this up.
- Start the Enterprise Manager, go to the correct SQL Server, go to Management and go to SQL Server Agent;
- Right click on Jobs and select “New Job”. The following screen will be shown: Enter a name and a description for the Job. It is advisable to make the SA user owner of the job, in that way the job will not fail because of insufficient rights.
On the second tab press new to create the first step. This job will consist of two steps. The first step is for defragging indexes and the second step is for updating the statistics.
In the first step enter the correct step name and select the correct database.
In the command section press the open button and load the file “defrag.sql” (attached to this document).
On the advanced tab make sure that the job always goes to the next step. In case of some failure at least the statistics are updated.
Create an additional step to update the statistics.
On the advanced tab, let the job quit on success and on failure.
On the next step we are going to create a schedule so it runs every Sunday at 02:00 AM
.
Press the button New Schedule and the following screen will pop up.
Press the change button to get the next screen and determine when you want to run the job.
On the notifications tab use the default options. If the job fails it will be written to the event log. On success nothing will be written to the application log.
IMPORTANT: Defragment of indexes and updating statistics can take several hours, depending on the size of the database, the percentage of Defragment, the accuracy of the statistics and the disk configuration. This process can be cancelled at all times without creating problems.
