How to measure performance of your SQL server

---

Issues:

There might be a situation that some actions in Exact Globe 2000 / Exact Globe 2003 take a long time, especially in large databases this might happen. What can we do about this?

Solution:
This problem can be caused by several reasons. See this document on how to troubleshoot this. In this document it is explained how to troubleshoot slow SQL queries.

In order to troubleshoot slow SQL Queries, it is important and necessary to first determine which SQL Query is slow. Therefore you need to run a profiler trace to determine this. Follow the steps below to set this up:

1.- Download the attached file and save it in the folder "C:\Program files\Microsoft SQL Server\80\Tools\Templates\SQL Profiler". (The assumption is made here that Microsoft SQL Server is installed in the default folder, if this was changed during installation the file needs to be stored on this location);
2.- Go to [Start, Programs, Microsoft SQL Server, Profiler];
3.- In the SQL Profiler go to [File, New, Trace] and connect to the correct SQL Server;
4.- Set the correct options for the trace:
a.- open template "performance test":
b.- enter a clear trace name:
c.- it is possible to save the trace in a SQL Table, so it can be analyzed with the Query Analyzer. This is very usefull. It is advisable to store this table in the database where the peformance issue was tested, because than the whole database can be send to Exact and all the necessary data is available.Also store the trace in a normal trace file (extension is TRC), because that is needed as well.

See screenshots for description of the above:

5.- on the tab "Filters", you can enter the minimum duration of SQL Queries you want to trace. This is the duration in milliseconds (1 second = 1000 milliseconds). By default it is 1000 milliseconds. At first it might be better to put it to 5000.

If a customer is using several databases, it is advisable to only trace one database. When you only want (or actually need) to trace one database, you have to specify the database id. On the filter tab you can enter the database id. To determine the database id of the database you want to trace, execute the following query:
use [master]
select name, dbid from sysdatabases

This will give you a list of all database names and id's of the databases attached to use SQL Server. Here you can find the database id of the database you want to trace.

After this you can start the trace. In this trace only slow queries will be captured. Of course it is advisable to only execute the functions in Exact Globe 2000 which are slow according to the customer, however it is also possible to just let the trace for half a day or a whole day. After you completed the trace, you should not closed it down. It needs to be paused or stopped, if you close it down you run into the problem that you cannot copy the complete SQL query into the Query analyzer. The SQL table does not store the entire SQL Query. All the trace data is already stored in the SQL table created by the trace, so there is not need to store it seperately. This SQL table can than afterwards be queried with the Query analyzer.

Some examples of possible queries on this table:
-> select * from general speed test order by duration desc (this will put the slowest queries on top);
-> select * from general speed test where application name = 'general journal' (this will show all queries executed when working in the general journal).

After you have searched for the slowest queries, you have to look up the queries in the profiler (which you stopped/paused). Than select the SQL query and copy it into the Query Analyzer. Now you can start troubleshooting this SQL query.