October 8, 2014 at 6:01 pm
Hi All,
The CPU usage of one of our production SQL servers has increased significantly, from normally 30% to 60% in recent days. After painstaking investigations, we identified that the increased CPU usage was mainly due to a couple of simple string manipulation scalar UDFs.
These UDFs have been in the database for a long time, and we have NOT changed them.
One of these UDFs ProperCase(@Input) is simply to capitalise the first letter of each word in the input string. For example, the output of ProperCase('peter SMITH') is 'Peter Smith'
The UDF works fine in the past. For an input string of 4000 characters with 700 words, the function took 0ms to complete.
In recent days, for the same input string of 4000 characters with 700 words, the function is now taking 200ms to complete.
Anyone has any idea on what could have impacted the performance of such simple string manipulation functions?
The server is a physical machine, running SQL 2008 R2 SP2 Enterprise on Windows Server 2008 R2.
Thanks in advance
October 8, 2014 at 6:50 pm
Could you post the function? Have you installed any updates/SP?
October 8, 2014 at 6:59 pm
Thanks Luis.
We haven't installed any updates/SP
Below is the function definition.
[font="Courier New"]
Create Function [dbo].[ProperCase] ( @input varchar(4000) )
Returns varchar(4000)
As
Begin
-- Remove leading/trailing white space
Set @input = ltrim(rtrim(@input))
-- Remove recurring white space, replace double space with single space
While charindex(' ', @input) > 0
Set @input = replace(@input, ' ', ' ')
-- Change all to lower case
Set @input = lower(@input)
-- Capitalise first letter
Declare @position int
While IsNull(@position, Len(@input)) > 1
Select @input = Stuff(@input, IsNull(@position, 1), 1, upper(substring(@input, IsNull(@position, 1), 1))),
@position = charindex(' ', @input, IsNull(@position, 1)) + 1
Return (@input)
End
[/font]
October 8, 2014 at 7:24 pm
Would you mind trying with a different version of the function?
ALTER FUNCTION [dbo].[ProperCase](@String [varchar](4000))
RETURNS [varchar](4000) WITH EXECUTE AS CALLER
AS
BEGIN
----------------------------------------------------------------------------------------------------
DECLARE @Position INT
;
--===== Update the first character no matter what and then find the next postion that we
-- need to update. The collation here is essential to making this so simple.
-- A-z is equivalent to the slower A-Z
SELECT @String = replace(replace(replace(replace(replace(replace(replace(ltrim(rtrim(@String)),
' ',' '),
' ',' '),
' ',' '),
' ',' '),
' ',' '),
' ',' '),
' ',' ') COLLATE Latin1_General_Bin,
@String = STUFF(LOWER(@String),1,1,UPPER(LEFT(@String,1))) COLLATE Latin1_General_Bin,
@Position = PATINDEX('%[^A-Za-z''][a-z]%',@String COLLATE Latin1_General_Bin)
;
--===== Do the same thing over and over until we run out of places to capitalize.
-- Note the reason for the speed here is that ONLY places that need capitalization
-- are even considered for @Position using the speed of PATINDEX.
WHILE @Position > 0
SELECT @String = STUFF(@String,@Position,2,UPPER(SUBSTRING(@String,@Position,2))) COLLATE Latin1_General_Bin,
@Position = PATINDEX('%[^A-Za-z''][a-z]%',@String COLLATE Latin1_General_Bin)
;
----------------------------------------------------------------------------------------------------
RETURN @String;
END ;
October 8, 2014 at 9:01 pm
Thanks Luis,
I created your version of the function as ProperCaseLuis() and ran the same query with the function; the resulted CPU time for the query has not improved.
The main problem is not trying the improve the function itself, because it has been running happily in the past. However, it is now suddenly running at least 200 times slower; and we have not made any change to the server.
Any other idea?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply