July 2, 2015 at 2:42 am
Hi,
We are running a SQL 2012 Enterprise edition instance. We have reports of performance issues on this instance. There is an application which calls a function in the database which returns a table variable with just one row in it.
I checked the sys.dm_exec_query_stats and I see that in just 4 hours there are more than 10 million executions of the query plan corresponding to this function. The min_rows and max_rows values of this plan are always 1. Which means for every execution of this function only one row is returned.
Its like the client is pulling one row at a time through the network I/O. I have verified from the wait stats collected over 10 days that the highest wait type is ASync_network_IO and I suspect that this code is causing this wait.
Here is the code of that function. It looks to me like a string manipulation function and it is not actually referencing any data from any table :
create function [dbo].[Split]
(
@string nvarchar(MAX),
@delimiter nvarchar(10)
)
returns @table table
(
[Value] nvarchar(MAX)
)
begin
declare @nextString nvarchar(MAX)
declare @pos int, @nextPos int
declare @commaCheck nvarchar(1)
set @nextString = ''
set @commaCheck = right(@string, 1)
set @string = @string + @delimiter
set @pos = charindex(@delimiter, @string)
set @nextPos = 1
while (@pos <> 0)
begin
set @nextString = substring(@string, 1, @pos - 1)
insert into @table
(
[Value]
)
values
(
@nextString
)
set @string = substring(@string, @pos + 1, len(@string))
set @nextPos = @pos
set @pos = charindex(@delimiter, @string)
end
return
end
Could anyone review and let me know if this is indeed the cause for the performance bottlenecks on this SQL instance?
Can anyone suggest any improvements or if it is possible to move this code entirely to execute on client side as it is not referencing any database objects and I think this sort of string manipulations are easily achievable through some sort of a client side scripting language.
July 2, 2015 at 2:49 am
It's an inefficient split function, but whether it's the problem or not is impossible to tell from the information available.
No, you can't move that to the client, because it's usually used in another query, usually something like
SELECT <columns> FROM SomeTable INNER JOIN dbo.Split(<parameters>)...
Functions get their own execution plans, that doesn't mean the application is calling it.
You could consider replacing it with the delimited8kSplit function, but you'd have to find every place where it's used (every query) and change them.
If you want to identify the actual performance problems, you need to look at what queries are running, what resources they're using and how often they run.
This may be a good place to start https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/
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
July 2, 2015 at 2:50 am
Hi,
Sorry about the earlier post.
I just realized that about 10 stored procedures depend on this function. This function does not return anything to the client. it does so only to the calling stored proc.
thanks,
July 2, 2015 at 2:51 am
Thank you Gail.
Will be checking out the URL.
thanks,
July 2, 2015 at 5:12 pm
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply