SP4 issues

• davrosdja

  Grasshopper

  Points: 22

  More actions

  June 20, 2006 at 6:11 am

  #366478

  Hello All

  I recently installed SP4 onto my SQL2000 Ent which had SP3A. Since installation when running sp_who i notice that different spid's are blocking themselves. Previous to SP4 i was not getting any of this info about processes blocking themselves.

  Also i think that SP4 has slowed the system down a bit. I can not prove anything yet, but i am monitoring it. The only reason i put SP4 on was to fix an issue we encoutered in our dev environment and the fix was sp4.

  Any thoughts or comments would be greatly appreciated.

• EdVassie

  SSC Guru

  Points: 60472

  More actions

  June 21, 2006 at 6:29 am

  #644996

  I recently did an investigation about SP4, which I have summarised below.  In the end we have decided to stick with our SP3 + HF 928 build until next year when we plan to move to SQL2005.  This way we can avoid the regression testing required by a move to SP4.

  I have looked on the web for issues with SP4 and found 4, all of which have good resolutions except d).

   

  a) SP4 rtm does not allow full use of AWE memory.  A hotfix is available for this that is included in the hotfix rollup package.

   

  b) SP4 introduces restrictions on what registry keys can be accessed by xp_regread and some other routines.  KB article 887165 describes a number of ways we can persuade xp_regread, etc, to access the keys we want to use.

   

  c) Some people have reported more blocking issues with SP4 than SP3.  The documented reason for this is that SP4 is better at reporting short-term blocking than SP3 was, and all that is being seen is what really happened in SP3 but was not shown.

   

  d) SP4 is more restrictive in performing implicit data type conversions for predicates than SP3.  SP3 would do its best to convert data types, even if this resulted in truncation of data and unexpected results from the query.  SP4 tries to only get good results from the query, and this can result in some SQL statements being rejected in SP4 that 'worked' in SP3, or some queries performing slower.  There is still some discussions in the forums about if some of the restrictions could be seen as bugs or good design, but the only sure fix is to re-code offending queries to avoid implicit conversions.  However there is a new trace flag 9059 which will cause SP4 to perform data type conversions in the same way as SP3, including the risk of truncation and unexpected results.  Note that our HF 928 build appears to contain some of the changes for data type conversions that are in SP4.

   

  Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
  

  When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

• Patrick Folan

  Mr or Mrs. 500

  Points: 512

  More actions

  June 21, 2006 at 10:22 am

  #645079

  I noticed that too and discovered its just the way SQL Server with SP4 reports Latch waits on a process, its nothing to worry about Check out this page for more info...

  http://support.microsoft.com/default.aspx/kb/906344

  ...SUMMARY

  After you install Microsoft SQL Server 2000 Service Pack 4 (SP4), you may notice that the blocked column in the sysprocesses system table is populated for latch waits in addition to lock waits. Sometimes, you may notice brief periods of time when a single server process ID (SPID) is reported as blocking itself. This behavior is expected.