September 19, 2008 at 6:15 am
Hi,
I use SQL Server 2005 DEV edition 64BIT SP2 on my production server, the OS is Windows Server 2003 ENT 64BIT SP2 and the hardware is HP ML360 G4 6GB Mem 2 xeon CPU
My disks are set to – OS + SQL binaries on Mirror – LDF files on different Mirror and MDF files on RAID10.
I notice a performance decrease on the server relate to the ERP that we use on the Production.
I copied the production DB into my Test Server completely.(this test server is regular P-4 with 1 GB mem with SQL Server 2005 STD SP2)
I restart both servers and then run a few queries that I know they running very slow on my Prod server.(no one connect to both server at night)
The results was on my PROD server those queries run slower then on my Test server.
Just for clarification the different was between 20-30 sec per query.
Can anyone know why I have this performance different?
Is it related to MEMORY?
Is it related to that I use the DEV edition?
THX
September 19, 2008 at 6:50 am
:w00t: you shouldn't be using developer edition on a production server, that's illegal!... look at the door, police is coming 😀
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 19, 2008 at 6:57 am
Developer (as it's name implies) is licenced for development usage only. I would suggest you replace the version on the test server with dev edition and put standard edition onto the production server. Otherwise you should buy a standard edition or enterprise edition licence for your production server.
Have a look at the execution plans on the two servers. Are they different?
How did you copy the DB to test? Backup and restore?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 19, 2008 at 7:20 am
- Please post version details (@@version)
- Did you apply cumulative hotfixes to your sp2 ?
- do you rebuild indexes on a regular basis ,
- did you add the deadlock traces to your instance startup parameters ?
(-T1222 and -T3604)
This way it logs deadlock info in the errorlog.
- do you monitor (b)locking ?
- did you install the free 'performance dashboard' ? It can help out !
- how many users are on your prod system (and on your test system ?) ,
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 19, 2008 at 9:10 am
How much memory has been allocated to sql server? Are you making use of lock pages in memory option--http://support.microsoft.com/kb/918483.
MJ
September 19, 2008 at 1:24 pm
THX for all the replay's.
i did a backup and restore on the Test machine.
lets put the licensing issue beside.should it hurt my performance because it's Dev Edition?
the SQL Server build are 9.0.3056 on both server.(i didn't install any CU)
when i make this test no user was connected to both servers.
both server was restart so the execution plan on both are from scratch.
i rebuild or reorganized indexes weekly,i don't start the Server's with any trace flags.
the lock pages in memory option was on and the results was the same,so i removed it form the PROD server and the performance are the same(just for the test).
any other ideas?
THX
September 19, 2008 at 2:28 pm
avipenina (9/19/2008)
both server was restart so the execution plan on both are from scratch.
Doesn't mean they'll be the same. Please look at the exec plan on the two servers and see if it differs
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 19, 2008 at 2:38 pm
As was mentioned previously, check the execution plans from both systems.
Just a guess, but I think you'll find that the difference in the execution plans is due to your production system escalating to a parallel plan.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 19, 2008 at 3:25 pm
Jeffrey Williams (9/19/2008)
Just a guess, but I think you'll find that the difference in the execution plans is due to your production system escalating to a parallel plan.
I'm thinking much the same thing. I was doing some time tests on my desktop last week (quad core) and noticed some really bad time differences between the parallel and the serial plans for a very simple query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 19, 2008 at 4:04 pm
I've changed the 'max degree of parallelism' to 1 that means only serially,i restart the server and i get the same poor results.
any other ideas what i can check on this issue?
September 19, 2008 at 4:25 pm
As Gail said compare the execution plans....
MJ
September 19, 2008 at 4:35 pm
completely the same.
use the same indexes.
September 19, 2008 at 4:38 pm
Try comparing physical and logical reads on both system by turning on statistics for I/O.
SET statistics io on
MJ
September 20, 2008 at 12:57 am
did any of you install the cumulative updates for SQL Server 2005?
should i install the latest cumulative updates?
if i install cumulative updates 9,does it include cumulative updates from 1 to 9?
THX
September 20, 2008 at 2:21 am
MANU (9/19/2008)
Try comparing physical and logical reads on both system by turning on statistics for I/O.SET statistics io on
MJ
And statistics time. I'd like to see where the time difference appears.
if i install cumulative updates 9,does it include cumulative updates from 1 to 9?
Yup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply