Our organization wants to move the hosting of Enterprise Analyzer from one data center to another. The "old" center has certain clipper queries like Constant search and I/O search coming back with results in about 45 seconds. The "new" center runs the same query on the same number of files and the searches take about 3.5 to 4 minutes. I don't know where the issue is and would like some insight on where to look for poor performance.
The setup is supposed to be mirrored from the old to the new, but I don't know how to verify that the MS SQL setup is the same nor that the network settings are the same. I have elevated rights on both systems, so if there are some ways I can look at performance using built-in WIndows 2008 server tools, then I can start digging in a bit more.
#EnterpriseAnalyzerWith full MS SQL server installs, there is the ‘Microsoft SQL Server Management Studio (MSSMS)’ tool that allows you to connect to various MS SQL Server databases and perform tasks like run queries against the databases.
Within MSSMS, you can look at the properties of the server and this will tell you if the two servers are configured in the same way. What you should do is compare the general properties, the memory the processors and advanced properties. This may well show differences between the two servers and you may find that second server is less powerful than the first server.
I have put a small script together that runs some queries against the tables that are used within the workspace. You can run this query against both servers and it should give you some timings. Again, this allows you to compare the performance of the two servers.
From the MSSMS query option, there is also the option to include client side statistics. This will also give you information about how the queries run and show help you compare the performance of the two servers.
Within the EA, 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. What you can do is run the same clipper query against the same set of files in the two workspaces. 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.
Something else that you can do, is to map a drive to each of the servers. You can try some simple tests like copying a file to the remote machines and seeing which one is faster. This will give you an indication of the speed between the machines. I would recommend that you try to copy a large file (100Mb – 1 Gb) so have enough time to see how long it takes to complete. Something that might be slowing down the access to the new machine could be firewalls between the local machine and the remote server.
Rescue.flg (rename to Rescue.flg)Rescue.txt
MS_5F00_SQL_5F00_Test_5F00_Performance_5F00_2.sql
#MSSQL#EnterpriseAnalyzer#DatabasePerformance
