| Author |
Topic |
|
Hariarul
Posting Yak Master
160 Posts |
 Posted - 2007-12-06 : 04:20:27
|
| Hi all, We have 2 SQL servers Development & Production servers with below configurations.System Info:Database Version - SQL Server 2005Database Size - 120 plus GBOS Platform - Windows 2003Database Load - 50 usersCPUs - 4 RAM - 8 GBBit - 64 bitsWe are having a big performance difference between these two SQL Servers. The Development server is working fine and has a good response time. The Production server ( has Itanium processor) is showing a very very bad response time.Example: A stored procedure that takes just 4 seconds in the Development server is taking 8 minutes , 4 minutes , 17 minutes , 2 minutes randomly in the Production server.The work load , indexes remains the same. The statistics are updated. The recovery model is same. The SQL server agent is also disabled in the production server. No replication in the production server.Findings :Finally , When we checked the DMV 'sys.dm_os_waiting_tasks' of the two servers , we were able to see a very huge difference in the column 'wait_duration_ms'. The difference is 739776235 milliseconds. That is the Production server's 'wait_duration_ms' value is 739776235 milliseconds higher than that of the Development server.Note : The Production server was a newly built and was up and running just before a month. The Development server is running for more than 6 months.Any thoughts based upon the above findings ?Any help would be appreciated.Thanks & Regards, Hariarul |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2007-12-06 : 06:37:20
|
| Yes, have you checked the network cards? You may want to start looking to see if you have an issue there. |
 |
|
|
Hariarul
Posting Yak Master
160 Posts |
Posted - 2007-12-06 : 06:42:20
|
| Thanks Rick. Do you mean this issue would be straight related to the network cards ? |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2007-12-06 : 06:45:41
|
| Quite possibly. If you haven't opened the ports correctly or set the card up properly, then all data requests will queue until they can be returned.There are also other things to check, such as the disk I/O, the % processor time, disk queue length etc, but I would start with checking the server (including network cards, firewall and genberal networking) is set up correctly. |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2007-12-06 : 06:48:11
|
| Also check you haven't limited something in sp_configure as I am guessing you switched on the AWE (You must have if you have 8GB ram). |
|
|
|
Hariarul
Posting Yak Master
160 Posts |
Posted - 2007-12-06 : 07:00:21
|
| Thanks Rick. We will check the parameters you have mentioned. BTW , we have not switched on AWE for both the machines. Should we do it ? |
|
|
|
Rovastar
Starting Member
38 Posts |
Posted - 2007-12-06 : 07:00:26
|
| How are other queries?Select * from mylargetable takes x time on dev is it 10*x (or whatever)on prod?I am confused is your production sever 'live' if so. How did it fair in the testing/staging before going live? in stress testing, etcDid this sp perform ok then? Have you tested this on no load?Has the sp in production always taken ages or is this new thing? What does the execution plan imply the problem points are? It may point to a part in teh process that is slow. Mayeb a table is not indexed properly or something.If it is throughout - all different queries are similiarly problematic then you can rule out specfics that sp, etc. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-06 : 07:06:16
|
quote:
Originally posted by Hariarul
The difference is 739776235 milliseconds.
This is 205h 29m 36s (8 days, 13 hours, 29 minutes and 36 seconds).
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Hariarul
Posting Yak Master
160 Posts |
Posted - 2007-12-06 : 07:22:59
|
| Thanks Rovastar & Peso.Rovaster, your questions really makes sense. It is not always 10*x time duration in the Production server comparing to the Development server. It differs and varies randomly.As I have mentioned earlier , a same procedure takes different durations when executed consequently in the Production server( 9 mins, 2 mins, 7 mins, 32 mins :-( ) . But when the stored procedure is executed for the same load in the Development server , it runs for less than 5 seconds.So, The issue is not only with a single stored procedure. Almost all the procedures in this server is taking time. We are right now in the UAT phase and not gone LIVE. Main point to note :The testing , stress testing , performance testing etc were done on the same Production server. But the Production server was re built from the scratch ( OS , SQL Server etc ) before the UAT. So , we could say that this issue suddenly poped out on the same machine after the re built..Peso,Does the 205 hours difference gives us any clue for the performance issue ?Thanks,Hariarul |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-06 : 07:32:57
|
Not more than it seems very high.You had a query that run for more than 8.5 days and no one noticed?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-12-06 : 09:51:56
|
| What were the spids of the long running tasks? |
|
|
|
|
wait_duration_ms & Performance.