“A performance issue between 2 MS SQL Server environments has been highlighted. One environment runs certain clipper queries in a few seconds, and the other in a few minutes.
How can each environment be analyzed to identify any potential problem areas, which may cause such a difference in performance?”
With full MS SQL server installs, there is the ‘Microsoft SQL Server Management Studio (MSSMS)’ tool that allows you to connect to MS SQL Server databases and perform tasks on the server including run queries on the databases.
Within MSSMS, it is possible to view the properties of a given server. The properties of the two servers can be compared to see if they have been configured in the same way. The server properties that should be compared are:
Then for the database that holds the workspace the following database properties can be compared:
This may well show differences between the two servers and indicate if the second server is less powerful than the first server.
Another option that can be used from within MSSMS, is to include client side statistics. This can be turned on when running a query. The client side statistics will provide information detailing what the server does to fulfil the query. This will indicate if indexes are being used when running the query or if full table scans are being performed.
Generally, full table scans means that the server is doing more work to process the query and this means that the query takes longer to run. If full table scans are being performed on large tables this may be the cause of the slow down between the two servers.
For more details on how to use the client side statistics, please refer to Microsoft documentation or your Microsoft Database Administrator.
There could be an issue with the speed of the network to one of the machines. A simple test that can be performed, is to copy a file to both of the remote machines and see which one completes first. This will give an indication of the speed between the machines. It is recommended that the test is done with a large file (100Mb – 1 Gb) so there is enough time to see how long it takes to complete. The performance would be impacted by any firewalls that are in place between the local and remote servers.
Attached is a small script that runs some queries against the tables that are used within the workspaces. These queries can be run against both of the MS SQL servers so they can be used to judge the performance of the two servers.
Within Enterprise Analyzer, it is possible with the use of the rescue.flg file to turn on database tracing. By placing the attached rescue.flg into the main Enterprise Analyzer installation directory, it will trace all the SQL statements for the current session. It will give timings of how long statements took to process. With the tracing turned on, the same clipper query can be run in both workspaces so the performance of the two workspaces can be traced and compared. The trace files that are generated (within the EA/logs directory) should show which SQL statements take longer to run on the second server. It may also indicate if the two environments are configured in the same way or not.
Attached is a small script that runs some queries against the tables that are used within the workspaces. These queries can be run against both of the MS SQL servers so it can be used to judge the performance of the two servers. As part of the script, it ’uses’ a database. The database that is mentioned in the script is a test database and this needs to be changed to the name of the correct database before the script is run.
Incident - none