Efficient Value Update with Large Database

Question

I have a table as large as 8GB and with 20M records. There is an int field called mth. The mth field stores date information in form of YYYYMM, I want to transform the mth field to consecutive integers. So I use a formula to get the year and month from field mth and calculate the month order, specifically, I use the following code:

create function mth_to_num(@month int)
returns int
as 
begin
   return(round(@month/100,0)*12+@month-100*round(@month/100,0))
end

Then I use the following code to update the value in the huge table

update full_orig_month_Q1_1999 
set mth_order = dbo.mth_to_num(period)
Go 

However, the code took quite long time to execute, roughly 2-3 minutes. My system is windows 10 64 bit with SQL server 2016. Is there any way I can speed it up?

Another problem is that I found SQL server occupying as much as 8GB in the database after executing the above query. Does it need so much memory? How can I release them?

Thanks for your help in advance!

Jason

Answer 1

When possible don't use scalar user defined functions (UDF) in queries. They force the entire plan to be serial, they can cause a lot of additional CPU resources to be used, they are a black box to the optimizer and can lead to cardinality estimation issues in other parts of the plan, and they can result in needlessly large memory grants.

Try running your UPDATE query without the UDF. Just take the code that you have and put it into the UPDATE query directly. Something like this:

update full_orig_month_Q1_1999 
set mth_order = round(period/100,0)*12+period-100*round(period/100,0);

Does that improve the runtime and memory usage?

Answer 2

You could try using Paul Randall's script to capture the Wait Statistics occurring during the execution of your script.

Based on the results that are returned you can then search top Wait Types and interpret the performance issues.

Sample results could look like this:

WaitType    Wait_S  Resource_S  Signal_S    WaitCount   Percentage  AvgWait_S   AvgRes_S    AvgSig_S
ASYNC_NETWORK_IO    0.00    0.00    0.00    2   100.00  0.0005  0.0005  0.0000

Searching for ASYNC_NETWORK_IO would then reveal for example:

This wait type is where SQL Server has sent some data to a client through TDS and is waiting for the client to acknowledge that is has consumed the data, and can also show up with transaction replication if the Log Reader Agent job is running slowly for some reason. (Books Online description: “Occurs on network writes when the task is blocked behind the network. Verify that the client is processing data from the server.”)

In response to your comment:

Paul Randall's and Brent Ozar's sites are good starting points for performance tuning and optimizing. And I still find that the SQL Server 2008 R2 Books Online (which can still be downloaded) contain heaps of information regarding database internals even though some of it is outdated.

Aaron Bertrand has some good information on performance tuning and lots of other people equally so.