March 26, 2009 at 4:57 pm
Hello all,
I have a performance issue with all service packs that SQL 2005 have;
I have an application that have a process in order to know our customer payrolls;
When I run the payroll process in an instance that don't have any Service Pack, our time results are.. (for example 1 hr..) and when I Install any Service Pack our time results are approx 2.5 hrs.
I saw the advance parameters that SQL have and not value is changed, I don't know what exactly installed/updated by SQL SP1,2,3 that always the process are afectted on the performance.
I did some tests with some versions of Windows SO. Vista/XP/ Server and always the same..
So, as you can see my real problem is to have our Production Servers with out any Service pack installed.
----------Application: ------------
.Net Framework version 2
.Net Conecction
SQL Server 2005 Enterprise
March 27, 2009 at 9:07 am
First, is the hardware the same? Including disks (type/number). That can have a huge effect.
The Service Packs address bugs, and it's possible you've coded around a bug. I haven't heard anyone having slow performance from a service pack, so I'm thinking that some piece of behavior you count on changed with the service pack.
You'd have to run some execution plans between the two servers to understand what might be different. It might involve you changing code. If you can't do that, then be sure that when you set up the test boxes you have rebuilt all indexes and statistics so that isn't an issue.
March 27, 2009 at 10:44 am
If you are testing this across two machine then likely the issue is not service packs. Its probably a difference in hardware, install settings, configuration etc. That being said... if you are seeing this issue on the same piece of hardware (same physical machine) where it slows after the sp install, and fast again after an un-install then I would say it is likely to be caused by the sp.
I would check your execution plans for the application before and after the sp install. Is the execution plan the same? If not, then adjust the query to use a more optimized execution plan on the sp'd version. Have you run statistics updates, re-indexing, etc after the sp update? I would not think its neccessary, but I would run that to ensure that everything is optimized post sp.
Lastly I would look at release notes of SP1. If you see the issue on all sp's, then it was likely something fixed in SP1 as that fix would still exist in SP2 and SP3. So I would focus on reviewing release notes and seeing if there is any mention about issues addressed with your configuration of hardware, your development methodology, etc.
March 30, 2009 at 1:08 pm
Thx for your answers...
My tests were in the same hardware, for example, In a server win2003 i install two instances with SQL2005, one with SP1,2 or 3, and other with out Service pack.
Then I mounted a couple of database each one in the two instances!! And first I run a process and took the spend time, then after that finish I run the same process in the other instance...
So that the way that I compare the results..
* In the instance with out Service pack the process time is 8 minutes
* In the instance with Service Pack (1,2,3) the process time is 32 minutes with all indexes reorganized. and 45 minutes with out reorganize the indexes..
And also I compare the two times for a Query execution plan and the cost is en general the same and the time is the same only when I run a Back Ground process the difference occur.
Anyone that knows what other thing I could move ...
Thx in advance
Jagustin
SQL DBA.
March 30, 2009 at 6:50 pm
April 2, 2009 at 9:43 pm
Nop... The problem continue..
I Reindexes all DB and also update statistics.. In the same process I create a trace into the both instance's and I saw something that maybe focus the problem.. If I ran a big query in both instances have de same time and also the same Execition plan. The problem is when I run by the Application, so.. the problem maybe is with .Net framework and something that Service pack install or Apply.
Anyone know What thing maybe is afectted the performance at that level of process?
Thx in advance.
Jagustin
SQL DBA
April 2, 2009 at 11:52 pm
Carefully read all updates in service packs and try to figure out, what is clashing with your code.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply