| Author |
Topic |
|
Hariarul
Posting Yak Master
160 Posts |
 Posted - 2007-11-23 : 03:38:28
|
| Hi all,We have 2 database servers with same configuration. One is Development server and the other is Production server. The Production database server is clustered.We find the same queries taking more time in the Production server which is a clustered server. Will the clustering affect the performance in any way.Any help would be appreciated.Thanks,Hariarul |
|
|
suresha_b
Yak Posting Veteran
82 Posts |
Posted - 2007-11-23 : 03:56:36
|
| Are the databases same? Same data and index?Did you compare the time taken using SET STATISTICS TIME ON/OFF?Cluster will not affect the query performance. |
 |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2007-11-23 : 03:57:34
|
| It shouldn't as long as you have your cluster set up properly and fast enough hardware. You should also consider the difference in user base. On your dev server, there are probably a few users, yet on your cluster, there may be hundreds or thousands of users (depending on what it is used for).I would look at the execution plans on both to see if there is a difference. Also make sure that you are running exactly the same version. |
|
|
|
Hariarul
Posting Yak Master
160 Posts |
Posted - 2007-11-23 : 04:12:39
|
| Thanks Suresha & Rick.The data and indexes are the same. The database in the production server is restored from the database backup taken from the development server.Executions plans remains the same. |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2007-11-23 : 04:38:41
|
| Sorry, I have to ask, but are you sure they are the same? Just because it has been restored from the dev server does not mean that the execution plans and stats are the same. Have you run a few SPs with SHOWPLAN turned on?Also, is your whole database on one filegroup on the cluster or have you split it to take advantage of a SAN? |
|
|
|
Hariarul
Posting Yak Master
160 Posts |
Posted - 2007-11-23 : 07:08:46
|
| No issues Rick. The query plan remains the same expect for slight variations in the cost. Example : A index scan that takes 27% in the development server takes 30% in the production server.We have also done the update statistics on the production server. Still no improvement.Any thoughts ? |
|
|
|
evilDBA
Posting Yak Master
155 Posts |
Posted - 2007-11-23 : 07:46:32
|
| Whats about disk arrays and physical DATA/LOG layout?Whats about memory (check memory setting of both SQL servers)Check also the Recovery mode. If it is SIMPLE on dev and FULL on PROD, then PROD can be slowed down by the autogrowth on the log files (do you have TR LOG backups scheduled there?) |
|
|
|
Hariarul
Posting Yak Master
160 Posts |
Posted - 2007-11-26 : 06:44:48
|
| The database server has the DATA filegroups in the SAN and the LOG file in its hard drive.The memory configurations remains the same for both servers. They have the default values.The recovery model is FULL for both the database. |
|
|
|
evilDBA
Posting Yak Master
155 Posts |
Posted - 2007-11-26 : 08:23:35
|
| How do you [perform the measurement?Duration 'Cold' execution might be 100 times longer then a 'Hot' executionOn both servers,execute DBCC DROPCLEANBUFFERSthen execute your query 2 times (the first one will be 'cold' and the second will be 'hot')Compare times. |
|
|
|
Hariarul
Posting Yak Master
160 Posts |
Posted - 2007-11-26 : 09:58:44
|
| We are testing only for the 'hot' execution. |
|
|
|
|
Performance & Clustering...