REPLACE Multiple Spaces with One

• Fatherjack

  SSCommitted

  Points: 1575

  More actions

  November 20, 2009 at 6:26 am

  #1082158

  Not 100% convinced I should jump back into this thread now but I have been watching since the start and its certainly made an interesting read.

  I just wanted to mention that the ||*9*9|| string that I suggested was just an example that any string could be used and that the length of it would add to the chance of it not occurring in the string being parsed. I guess it has served a purpose as an example for comparison against the 'bell' etc but it isnt precious or special in any way. I also got told that it broke Jeff's requirement on lengthening the string being worked on and accept that it was therefore not a relevant suggestion.

  I will now return to lurking on the edges of the thread and continue being amazed at the efforts exhibited by C # Screw and everyone else.

  Jonathan

  PS

  For what its worth I also have the automatic response that most people on SQL forums are somewhere to the West of the Atlantic and that I am the only UK based memeber here!! apologies to all members who are situated to the East of Greenwich.

• jcrawf02

  SSC-Insane

  Points: 24198

  More actions

  November 20, 2009 at 6:33 am

  #1082166

  Paul White (11/20/2009)

  ---

  Other side of the Pacific, if you don't mind!

  Paul

  Depends whether you travel west or east. 😉

  ---------------------------------------------------------
  How best to post your question[/url]
  How to post performance problems[/url]
  Tally Table:What it is and how it replaces a loop[/url]

  "stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

• C# Gnu

  SSCertifiable

  Points: 5973

  More actions

  November 20, 2009 at 7:15 am

  #1082202

  How about using a C# version of the SQL REPLACE function?

  If anyone has C# 'REPLACE' function it would be interesting to see if it is faster than the SQL Server Native REPLACE function.

  I am sure the SQL REPLACE function is very good - it would just be interesting to compare to CLR.

  Have a nice weekend : wish I was in NZ!:-)

  C# Gnu
  ____________________________________________________

  
  Excel Database Tasks : efficient business data processing
  Version 3.3 released February 2015

• C# Gnu

  SSCertifiable

  Points: 5973

  More actions

  November 20, 2009 at 8:19 am

  #1082258

  Inline results : oh I see insert a url to an image that works?

  

  Edit: by the way the worst performer in the table below is the Recursive function : its just off the visible area at result No.1

  

  Out of interest : note the slick CLR's are faster when there are allot of double spaces: I think this might be because not writing so much to the return string - and mainly looping quickly through the large chunks of spaces in the test data.:cool:

  C# Gnu
  ____________________________________________________

  
  Excel Database Tasks : efficient business data processing
  Version 3.3 released February 2015

• Jeff Moden

  SSC Guru

  Points: 1004475

  More actions

  November 20, 2009 at 10:40 am

  #1082368

  C# Screw (11/20/2009)

  ---

  Firstly : sorry Jeff : I spotted an error in earlier SQL, script was actually calling Recusive solution but displaying your name, that explains why you were in front of looping/recursive when no double spaces in the data.

  Heh... that's why I've been setting up my own tests (haven't had the time to complete them yet)... it looked like something was wrong because the method in the article should pretty much beat up on the While Loop and they were just too close to each other.

  I haven't looked at all of your test results since you've made the quoted statement above to see if you've made a correction but it sure would be handy if a new set of tests were run using the correct code to see what really happens.

  Thanks.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• C# Gnu

  SSCertifiable

  Points: 5973

  More actions

  November 20, 2009 at 10:55 am

  #1082371

  Jeff Moden (11/20/2009)

  ---

  C# Screw (11/20/2009)

  ---

  Firstly : sorry Jeff : I spotted an error in earlier SQL, script was actually calling Recusive solution but displaying your name, that explains why you were in front of looping/recursive when no double spaces in the data.

  Heh... that's why I've been setting up my own tests (haven't had the time to complete them yet)... it looked like something was wrong because the method in the article should pretty much beat up on the While Loop and they were just too close to each other.

  I haven't looked at all of your test results since you've made the quoted statement above to see if you've made a correction but it sure would be handy if a new set of tests were run using the correct code to see what really happens.

  Thanks.

  Hello Jeff

  The results shown above are after I spotted the error, the error was only introduced after I created the recursive example.

  When there are lots of double spaces in data then you are 1982ms, but SQL looping is 2663ms, so you are quite a bit faster!:cool:

  When there are no double spaces in data then of course the story changes a bit - as results above.

  [edit: Note: The reason of course sql looping is faster when data has no double spaces is because it will do just one CHARINDEX call then drop out of the function, whereas other solutions will execute nested REPLACE at least three times. So of course performance depends on whether you have very many double spaces in your data].

  Here is the T-SQL so you can check all is ok, hope that helps..

  DBCC FREEPROCCACHE

  go

  --PREPARE

  SET NOCOUNT ON

  go

  CREATE FUNCTION dbo.fn_CleanUp_Recursion_Brigzy(@Data varchar(max))

  RETURNS VarChar(Max)

  AS

  BEGIN

  IF CHARINDEX(' ',@Data) > 0

  BEGIN

  SET @data = REPLACE(@Data,' ',' ')

  IF CHARINDEX(' ',@Data) > 0

  SELECT @data = dbo.fn_CleanUp_Recursion_Brigzy(@Data)

  END

  RETURN @data

  END

  GO

  go

  CREATE FUNCTION dbo.fn_CleanUp_Brigzy(@S VARCHAR(max))

  RETURNS VARCHAR(max)

  BEGIN

  WHILE CHARINDEX(' ',@S) > 0

  SELECT @s-2 = REPLACE(@S,' ',' ')

  RETURN @s-2

  END

  go

  CREATE FUNCTION dbo.fn_CleanUp_JeffOriginal(@S VARCHAR(max))

  RETURNS VARCHAR(max)

  BEGIN

  RETURN REPLACE(

  REPLACE(

  REPLACE(

  LTRIM(RTRIM(@S)) ,' ',' '+CHAR(7)) --Changes 2 spaces to the OX model

  ,CHAR(7)+' ','') --Changes the XO model to nothing

  ,CHAR(7),'') --Changes the remaining X's to nothing

  END

  go

  CREATE FUNCTION dbo.fn_CleanUp_MultiChars(@S VARCHAR(max))

  RETURNS VARCHAR(max)

  BEGIN

  RETURN LTRIM(RTRIM(

  REPLACE(REPLACE(REPLACE(@S,' ',' ||*9*9||'),'||*9*9|| ',''),'||*9*9||','')

  ))

  END

  GO

  CREATE FUNCTION dbo.fn_CleanUp_MichaelMeierruth_MKII(@S VARCHAR(8000))

  RETURNS VARCHAR(8000)

  BEGIN

  RETURN replace(replace(replace(replace(replace(replace(ltrim(rtrim(@s)),

  ' ',' '),

  ' ',' '),

  ' ',' '),

  ' ',' '),

  ' ',' '),

  ' ',' ')

  END

  go

  CREATE FUNCTION dbo.fn_CleanUp_MichaelMeierruth_MKIII(@S VARCHAR(8000))

  RETURNS VARCHAR(8000)

  BEGIN

  RETURN replace(replace(replace(replace(replace(replace(replace(ltrim(rtrim(@s)),

  ' ',' '),

  ' ',' '),

  ' ',' '),

  ' ',' '),

  ' ',' '),

  ' ',' '),

  ' ',' ')

  END

  go

  CREATE FUNCTION dbo.fn_CleanUp_MichaelMeierruth_MKIV(@S VARCHAR(8000))

  RETURNS VARCHAR(8000)

  BEGIN

  RETURN replace(replace(replace(replace(replace(replace(replace(ltrim(rtrim(REPLACE(@s,CHAR(9),' '))),

  ' ',' '),

  ' ',' '),

  ' ',' '),

  ' ',' '),

  ' ',' '),

  ' ',' '),

  ' ',' ')

  END

  go

  CREATE FUNCTION dbo.fn_CleanUp_MichaelMeierruth_Original(@S VARCHAR(8000))

  RETURNS VARCHAR(8000)

  BEGIN

  --declare @s-2 varchar(8000)

  --set @s-2 = '*' + replicate(' ',7998) + '*'

  --select len(@s)

  set @s-2 = replace(replace(replace(replace(replace(replace(ltrim(rtrim(@s)),

  replicate(' ',32+1),' '),replicate(' ',16+1),' '),replicate(' ',8+1),' '),replicate(' ',4+1),' '),replicate(' ',2+1),' '),replicate(' ',1+1),' ')

  --select len(@s)

  RETURN @s-2

  END

  CREATE TABLE #TEMP1 (COL1 VARCHAR(900))

  CREATE TABLE #TEMP2 (COL2 VARCHAR(900), COL3 VARCHAR(900), COL4 VARCHAR(900),COL5 VARCHAR(900))

  go

  --INSERT 200k ROWS WITH RANDOM SPACES ON EACH TABLE, SEPARATE TABLES ARE USED TO AVOID CACHING, THIS MAY TAKE QUITE AWHILE

  --DECLARE @SPACECOUNT1 INT,@SPACECOUNT2 INT,@SPACECOUNT3 INT,@SPACECOUNT4 INT

  --SELECT @SPACECOUNT1 = CAST(CAST(NEWID() AS VARBINARY(1)) AS INT),@SPACECOUNT2 = CAST(CAST(NEWID() AS VARBINARY(1)) AS INT)

  --INSERT INTO #TEMP1 (COL1)

  --OUTPUT inserted.COL1 INTO #TEMP2 (COL2)

  --SELECT ' '+REPLICATE('X',@SPACECOUNT1)+' '+REPLICATE('X',@SPACECOUNT2)+' '

  --GO 10000

  DECLARE @SPACECOUNT1 INT,@SPACECOUNT2 INT,@SPACECOUNT3 INT,@SPACECOUNT4 INT

  SELECT @SPACECOUNT1 = CAST(CAST(NEWID() AS VARBINARY(1)) AS INT),@SPACECOUNT2 = CAST(CAST(NEWID() AS VARBINARY(1)) AS INT)

  INSERT INTO #TEMP1 (COL1)

  OUTPUT inserted.COL1 INTO #TEMP2 (COL2)

  SELECT 'TEST1'+SPACE(@SPACECOUNT1)+'TEST2'+SPACE(@SPACECOUNT2)+'TEST3'

  GO 10000

  -- Test result table

  CREATE TABLE #tResults(Tag VARCHAR(100),Result int)

  go

  -- Test 1 : Jeff technique : multi chars for replace char

  DECLARE @StartTime DATETIME

  SELECT @StartTime= GETDATE()

  UPDATE #TEMP2 SET COL3 = dbo.fn_CleanUp_MultiChars(COL2)

  FROM #TEMP2

  INSERT INTO #tResults(Tag,Result)VALUES ('SQL function: Replace Technique : but using replacement chars ||*9*9||',DATEDIFF(ms,@StartTime,GETDATE()))

  go 10

  DECLARE @StartTime DATETIME

  SELECT @StartTime= GETDATE()

  UPDATE #TEMP2 SET COL3 = dbo.fn_CleanUp_Recursion_Brigzy(COL2)

  FROM #TEMP2

  INSERT INTO #tResults(Tag,Result)VALUES ('SQL function: C#Screw : Recursive',DATEDIFF(ms,@StartTime,GETDATE()))

  go 10

  DECLARE @StartTime DATETIME

  SELECT @StartTime= GETDATE()

  UPDATE #TEMP2 SET COL3 = dbo.fn_CleanUp_JeffOriginal(Col2)

  FROM #TEMP2

  INSERT INTO #tResults(Tag,Result)VALUES('SQL function: Jeff Original : single bell char',DATEDIFF(ms,@StartTime,GETDATE()))

  go 10

  DECLARE @StartTime DATETIME

  SELECT @StartTime= GETDATE()

  UPDATE #TEMP2 SET COL4= dbo.fn_CleanUp_Brigzy(COL2)

  FROM #TEMP2

  INSERT INTO #tResults(Tag,Result)VALUES('SQL function :Brigzy (C#Screw) Looping',DATEDIFF(ms,@StartTime,GETDATE()))

  go 10

  DECLARE @StartTime DATETIME

  SELECT @StartTime= GETDATE()

  UPDATE #TEMP2 SET COL4= dbo.fn_CleanUp_MichaelMeierruth_Original(COL2)

  FROM #TEMP2

  INSERT INTO #tResults(Tag,Result)VALUES('SQL function: Michael Meierruth Original',DATEDIFF(ms,@StartTime,GETDATE()))

  go 10

  DECLARE @StartTime DATETIME

  SELECT @StartTime= GETDATE()

  UPDATE #TEMP2 SET COL4= dbo.fn_CleanUp_MichaelMeierruth_MKIII(COL2)

  FROM #TEMP2

  INSERT INTO #tResults(Tag,Result)VALUES('SQL function: Michael Meierruth III - extra replace',DATEDIFF(ms,@StartTime,GETDATE()))

  go 10

  DECLARE @StartTime DATETIME

  SELECT @StartTime= GETDATE()

  UPDATE #TEMP2 SET COL4= dbo.fn_CleanUp_MichaelMeierruth_Original(COL2)

  FROM #TEMP2

  INSERT INTO #tResults(Tag,Result)VALUES('SQL function: Michael Meierruth : with hard coded spaces',DATEDIFF(ms,@StartTime,GETDATE()))

  go 10

  DECLARE @StartTime DATETIME

  SELECT @StartTime= GETDATE()

  UPDATE #TEMP2 SET COL4= dbo.fn_CleanUp_MichaelMeierruth_MKIV(COL2)

  FROM #TEMP2

  INSERT INTO #tResults(Tag,Result)VALUES('SQL function: Michael Meierruth : with hard coded spaces and TAB support',DATEDIFF(ms,@StartTime,GETDATE()))

  go 10

  -- Edit 20/11 15:59 : removed _X functions as they not included in results above

  -- they will be used to test C# alternative to REPLACE function

  --DECLARE @StartTime DATETIME

  --SET @StartTime=GETDATE()

  --UPDATE #TEMP2 SET COL5= dbo.fn_TidySpace_Looping_CLR(COL2)

  --FROM #TEMP2

  --INSERT INTO #tResults(Tag,Result)VALUES('CLR: Brigzy (C#Screw) Looping',DATEDIFF(ms,@StartTime,GETDATE()))

  --go 10

  DECLARE @StartTime DATETIME

  SET @StartTime=GETDATE()

  UPDATE #TEMP2 SET COL5= dbo.fn_TidySpace_NotLooping_CLR(COL2)

  FROM #TEMP2

  INSERT INTO #tResults(Tag,Result)VALUES('CLR: Not looping using ||*9*9|| technique in C#',DATEDIFF(ms,@StartTime,GETDATE()))

  go 10

  DECLARE @StartTime DATETIME

  SET @StartTime=GETDATE()

  UPDATE #TEMP2 SET COL5= dbo.fn_TidySpace_SingleChar_CLR(COL2)

  FROM #TEMP2

  INSERT INTO #tResults(Tag,Result)VALUES ('CLR: using Jeff''s single char technique in C#',DATEDIFF(ms,@StartTime,GETDATE()))

  go 10

  DECLARE @StartTime DATETIME

  SET @StartTime=GETDATE()

  UPDATE #TEMP2 SET COL5= dbo.fn_TidySpace_JCB_CLR(COL2)

  FROM #TEMP2

  INSERT INTO #tResults(Tag,Result)VALUES('CLR :using JCBnew space replacer C#',DATEDIFF(ms,@StartTime,GETDATE()))

  go 10

  DECLARE @StartTime DATETIME

  SET @StartTime=GETDATE()

  UPDATE #TEMP2 SET COL5= dbo.fn_TidySpace_PW_CLR(COL2)

  FROM #TEMP2

  INSERT INTO #tResults(Tag,Result)VALUES('CLR: using Paul White space replacer C#' ,DATEDIFF(ms,@StartTime,GETDATE()))

  go 10

  DECLARE @StartTime DATETIME

  SET @StartTime=GETDATE()

  UPDATE #TEMP2 SET COL5= dbo.fn_TidySpace_FLO_CLR(COL2)

  FROM #TEMP2

  INSERT INTO #tResults(Tag,Result)VALUES ('CLR: using Flo s space replacer C#' ,DATEDIFF(ms,@StartTime,GETDATE()))

  go 10

  DECLARE @StartTime DATETIME

  SET @StartTime=GETDATE()

  UPDATE #TEMP2 SET COL5= dbo.fn_TidySpace_MMSharpScrew_CLR(COL2)

  FROM #TEMP2

  INSERT INTO #tResults(Tag,Result)VALUES ('CLR: Michael Meierruth - C#Screw conversion: hard coded spaces' ,DATEDIFF(ms,@StartTime,GETDATE()))

  go 10

  DECLARE @StartTime DATETIME

  SET @StartTime=GETDATE()

  UPDATE #TEMP2 SET COL5= dbo.fn_TidySpace_MMSharpScrew_ExtraReplace_CLR(COL2)

  FROM #TEMP2

  INSERT INTO #tResults(Tag,Result)VALUES ('CLR: Michael Meierruth - C#Screw conversion: with extra Replace' ,DATEDIFF(ms,@StartTime,GETDATE()))

  go 10

  DECLARE @StartTime DATETIME

  SET @StartTime=GETDATE()

  UPDATE #TEMP2 SET COL5= dbo.fn_TidySpace_SharpScrewII_CLR(COL2)

  FROM #TEMP2

  INSERT INTO #tResults(Tag,Result)VALUES ('CLR: C#Screw II - Spaces and TABS' ,DATEDIFF(ms,@StartTime,GETDATE()))

  go 10

  DECLARE @StartTime DATETIME

  SET @StartTime=GETDATE()

  UPDATE #TEMP2 SET COL5= dbo.fn_TidySpace_FLO_SpaceAndTab_CLR(COL2)

  FROM #TEMP2

  INSERT INTO #tResults(Tag,Result)VALUES ('CLR: Flo - Spaces and TABS - v.compact' ,DATEDIFF(ms,@StartTime,GETDATE()))

  go 10

  DECLARE @StartTime DATETIME

  SET @StartTime=GETDATE()

  UPDATE #TEMP2 SET COL5= dbo.fn_TidySpace_and_TABS_SharpScrew_CLR(COL2)

  FROM #TEMP2

  INSERT INTO #tResults(Tag,Result)VALUES ('CLR: C#Screw : Slightly reduced Paul White''s function and Added Support TABS' ,DATEDIFF(ms,@StartTime,GETDATE()))

  go 10

  DECLARE @StartTime DATETIME

  SET @StartTime=GETDATE()

  UPDATE #TEMP2 SET COL5= dbo.fn_TidySpace_PWSpaceReplacerUnsafe_CLR(COL2)

  FROM #TEMP2

  INSERT INTO #tResults(Tag,Result)VALUES ('CLR: Paul White''s function with Pointers by Mark-101232' ,DATEDIFF(ms,@StartTime,GETDATE()))

  go 10

  DECLARE @StartTime DATETIME

  SET @StartTime=GETDATE()

  UPDATE #TEMP2 SET COL5= dbo.fn_TidySpace_and_TABS_SharpScrew_WithPointers_CLR(COL2)

  FROM #TEMP2

  INSERT INTO #tResults(Tag,Result)VALUES ('CLR: Paul White''s function, reduced by C#Screw with Pointers ideas from Mark-101232' ,DATEDIFF(ms,@StartTime,GETDATE()))

  go 10

  SELECT Tag + CHAR(13) + CAST(FLOOR(AVG(Result)) AS VARCHAR(20)) FROM #tResults GROUP BY

  Tag ORDER BY SUM(Result) DESC

  --CLEANUP

  DROP FUNCTION dbo.fn_CleanUp_Brigzy

  DROP FUNCTION fn_CleanUp_JeffOriginal

  DROP FUNCTION fn_CleanUp_MultiChars

  DROP FUNCTION fn_CleanUp_MichaelMeierruth_Original

  DROP FUNCTION fn_CleanUp_MichaelMeierruth_MKII

  DROP FUNCTION fn_CleanUp_MichaelMeierruth_MKIII

  DROP FUNCTION fn_CleanUp_MichaelMeierruth_MKIV

  DROP FUNCTION fn_CleanUp_Recursion_Brigzy

  DROP TABLE #TEMP1

  DROP TABLE #TEMP2

  DROP TABLE #tResults

  GO

  --SET STATISTICS TIME OFF

  SELECT --query = QT.[text],

  SUBSTRING(QT.[text],CHARINDEX('VALUES',text)+8,(CHARINDEX('DATEDIFF(',text)-CHARINDEX('VALUES',text))-8),

  --execution_plan = QP.query_plan,

  run_count = QS.execution_count,

  --total_cpu_time_ms = QS.total_worker_time/1000,

  --total_logical_reads = QS.total_logical_reads,

  --total_elapsed_time_ms = QS.total_elapsed_time/1000,

  avg_cpu_time_ms = (QS.total_worker_time / QS.execution_count)/1000,

  avg_logical_reads = QS.total_logical_reads / QS.execution_count,

  avg_elapsed_time_ms = (QS.total_elapsed_time / QS.execution_count)/1000

  FROM sys.dm_exec_query_stats QS

  CROSS

  APPLY sys.dm_exec_sql_text (QS.[sql_handle]) QT

  CROSS

  APPLY sys.dm_exec_query_plan (QS.[plan_handle]) QP

  WHERE QT.[text] LIKE '%INSERT INTO #tResults%'

  AND QT.[text] NOT LIKE '%dm_exec_query_stats%'

  ORDER BY

  --QS.last_execution_time ASC;

  QS.total_elapsed_time / QS.execution_count DESC

  C# Gnu
  ____________________________________________________

  
  Excel Database Tasks : efficient business data processing
  Version 3.3 released February 2015

• Nadrek

  SSC-Insane

  Points: 20039

  More actions

  November 20, 2009 at 11:35 am

  #1082382

  C# Screw (11/20/2009)

  ---

  I think DBA folk are interested in CPU time to see if the server is being strained? but that could be seen by looking at CPU Percentage utilisation too maybe.

  Depends on each DBA's situation. CPU Percentage utilization on a server in use by many users and processes doesn't tell you which process did what.

  For servers that are CPU constrained (i.e. CPU percentage is very high much of the time, while disk, memory, and network measures are not), CPU time is very important.

  For servers that are not CPU constrained (quite often the disk or network or memory measures are very high, while CPU is not), CPU time may be more or less irrelevant unless it's obscene, while Reads and Writes (IO measures) would be absolutely critical.

  For either, Duration may be important for something that happens with realtime users... or it might be largely irrelevant unless truly excessive for nightly reporting jobs, large bulk imports/exports (gigabyte or more text files), and so on.

  The new DMV based statistics output is nice; can number of writes be pulled from there as well? If so, I'll have to start using it after some databases are upgraded from 2000.

• C# Gnu

  SSCertifiable

  Points: 5973

  More actions

  November 20, 2009 at 11:49 am

  #1082393

  DMV

  sql_handlevarbinary(64)

  statement_start_offsetint

  statement_end_offsetint

  plan_generation_numbigint

  plan_handlevarbinary(64)

  creation_timedatetime

  last_execution_timedatetime

  execution_countbigint

  total_worker_timebigint

  last_worker_timebigint

  min_worker_timebigint

  max_worker_timebigint

  total_physical_readsbigint

  last_physical_readsbigint

  min_physical_readsbigint

  max_physical_readsbigint

  total_logical_writesbigint

  last_logical_writesbigint

  min_logical_writesbigint

  max_logical_writesbigint

  total_logical_readsbigint

  last_logical_readsbigint

  min_logical_readsbigint

  max_logical_readsbigint

  total_clr_timebigint

  last_clr_timebigint

  min_clr_timebigint

  max_clr_timebigint

  total_elapsed_timebigint

  last_elapsed_timebigint

  min_elapsed_timebigint

  max_elapsed_timebigint

  C# Gnu
  ____________________________________________________

  
  Excel Database Tasks : efficient business data processing
  Version 3.3 released February 2015

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  November 20, 2009 at 1:22 pm

  #1082462

  Can anyone comfirm if CPU, IO, etc information is accurately tracked and reported via the referenced DMVs when CLR stuff is being executed??

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service

• Paul White

  SSC Guru

  Points: 150468

  More actions

  November 20, 2009 at 2:48 pm

  #1082508

  TheSQLGuru (11/20/2009)

  ---

  Can anyone comfirm if CPU, IO, etc information is accurately tracked and reported via the referenced DMVs when CLR stuff is being executed??

  Yes. http://www.docstoc.com/docs/15431250/SQL-Server-2005-Under-the-Hood_-How-We-Host-the-CLR contains the architectural details. See http://blogs.msdn.com/sqlprogrammability/archive/2006/06/03/615743.aspx for details of how some of the DMVs were extended to provide extra CLR info. sys.dm_exec_query_stats in particular has 'total_clr_time', 'last_clr_time', 'min_clr_time' and 'max_clr_time'. To be clear, clr_time is a component of worker_time.

  Separately, in answer to someone else's question of why I'm so keen to measure worker time (scheduled CPU time):

  Mostly it's because it's a better general performance metric than, say, logical IO. See http://sqlblog.com/blogs/joe_chang/archive/2008/09/10/why-logical-io-is-a-poor-performance-metric.aspx. Also http://www.qdpma.com/SQLServerCostBasedOptimizer.html. The second one is very deep, but I fully recommend it to anyone who is serious about understanding this stuff!

  Paul White
  All articles available on SQL.kiwi
  @SQL_Kiwi

• Paul White

  SSC Guru

  Points: 150468

  More actions

  November 20, 2009 at 3:11 pm

  #1082523

  My thanks to those who have voted for the Connect item about the collation performance thing. I'll post the response here as soon as I hear anything </end-plug>

  Paul White
  All articles available on SQL.kiwi
  @SQL_Kiwi

• Jeff Moden

  SSC Guru

  Points: 1004475

  More actions

  November 20, 2009 at 3:52 pm

  #1082539

  Nadrek (11/20/2009)

  ---

  C# Screw (11/20/2009)

  ---

  I think DBA folk are interested in CPU time to see if the server is being strained? but that could be seen by looking at CPU Percentage utilisation too maybe.

  Depends on each DBA's situation. CPU Percentage utilization on a server in use by many users and processes doesn't tell you which process did what.

  For servers that are CPU constrained (i.e. CPU percentage is very high much of the time, while disk, memory, and network measures are not), CPU time is very important.

  For servers that are not CPU constrained (quite often the disk or network or memory measures are very high, while CPU is not), CPU time may be more or less irrelevant unless it's obscene, while Reads and Writes (IO measures) would be absolutely critical.

  For either, Duration may be important for something that happens with realtime users... or it might be largely irrelevant unless truly excessive for nightly reporting jobs, large bulk imports/exports (gigabyte or more text files), and so on.

  The new DMV based statistics output is nice; can number of writes be pulled from there as well? If so, I'll have to start using it after some databases are upgraded from 2000.

  I believe that both disk I/O and time to transport to display (and any wait times the two may have) are also included in duration... as both a DBA and a Developer, I'm always interested in duration as well as CPU and the difference in time between the two. It makes for a very good "health check".

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Nadrek

  SSC-Insane

  Points: 20039

  More actions

  November 20, 2009 at 4:31 pm

  #1082555

  Jeff Moden (11/20/2009)

  ---

  I believe that both disk I/O and time to transport to display (and any wait times the two may have) are also included in duration... as both a DBA and a Developer, I'm always interested in duration as well as CPU and the difference in time between the two. It makes for a very good "health check".

  I've seen quite a few stored procedure/queries/query sets where different implementations can both dramatically increase the number of reads and decrease the duration, simultaneously, on development systems, test systems, and even the production system under periods of lighter load.

  In some cases, what's good for that one query (decreased duration) is very bad for the system as a whole (increased disk I/O on a system that is very seriously disk I/O constrained during periods of load). It's the difference between tuning just one query or stored procedure during happy times, and tuning the entire system for the best case within the set of worst case performance possibilities. If your "worst case" shows up commonly, this can be very important. If it doesn't, then keep an eye on things over the years.

• Jeff Moden

  SSC Guru

  Points: 1004475

  More actions

  November 20, 2009 at 6:04 pm

  #1082571

  My point exactly.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• C# Gnu

  SSCertifiable

  Points: 5973

  More actions

  November 21, 2009 at 3:19 am

  #1082627

  Morning All,

  Earlier in the test we moved all the SQL into functions, in an attempt to make fair comparison.

  Doing so we noticed Jeffs SQL speeded up and we briefly commented on this in the thread.

  However I want to just step back and ask people to try and explain this change in performance between function and inline SQL.

  This is really interesting now that we have the logical reads information (many thanks to Paul)

  

  Please can anyone/everyone comment on this as there is something fundamental here that I don't understand!

  Cheers

  Screw!

  Edit: Added SQL for you to check

  DECLARE @StartTime DATETIME

  SET @StartTime=GETDATE()

  UPDATE #TEMP2 SET COL5= REPLACE(

  REPLACE(

  REPLACE(

  LTRIM(RTRIM(col2)) ,' ',' '+CHAR(7)) --Changes 2 spaces to the OX model

  ,CHAR(7)+' ','') --Changes the XO model to nothing

  ,CHAR(7),'') --Changes the remaining X's to nothing

  FROM #TEMP2

  INSERT INTO #tResults(Tag,Result,StartTime,EndTime)VALUES ('SQL: Jeff INLINE SQL (not an SQL function)' ,DATEDIFF(ms,@StartTime,GETDATE()),@StartTime,GETDATE())

  C# Gnu
  ____________________________________________________

  
  Excel Database Tasks : efficient business data processing
  Version 3.3 released February 2015