Home > Performence Tuning > Troubleshoot SQL server performance issue due to user-defined scalar functions

Troubleshoot SQL server performance issue due to user-defined scalar functions


We have faced a strange issue in Database server where it took 20 hours time to upload the 1 million rows. The issue is not related with Database locking and resource consuming issue. The database server has 16 CPU and 32 GB ram RAM. To fixed such kind of issue first we have to look out Inefficient query plan, When generating an execution plan for a query, the SQL Server optimizer attempts to choose a plan that provides the fastest response time for that query. The most significant input the optimizer uses in evaluating the cost of various alternative query plans is the cardinality estimates for each operator, which you can see in the Show plan (EstimateRows and EstimateExecutions attributes). By the below step I am able to figure out that there is a need to use some other process to upload the batch instead of Function. It is a user-defined scalar function and UDF is used in the select list, this happens when creating the results of the query to pass to the next stage of query processing. It’s the row by row processing that seems to slow SQL Server the most.  It does not create any issue for a small number of rows, but this penalty shows up as poor query execution time when a query applies a UDF to a large number of rows, typically 1000 or more. No matter how simple a UDF there’s a large performance penalty paid when they’re used. The penalty is incurred because the SQL Server database engine must create its own internal cursor like processing. It must invoke each UDF on each row. It turns a Seek into a Scan.

Advertisements
Categories: Performence Tuning
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: