Table Insert is given 95% of the estimated cost – why?
This query also takes 30+ seconds but that duration is only on one server. When i pull the database locally to my laptop the query runs in 2. The difference is the scalar UDF's contents operate in parallel on the production box but serially locally. What could account for that? I have more detail about that on my blog. I will furnish any other information that would help.
Not quite zero, but pretty close. Conor Cunningham explains the reasons in his post SQL Server Scalar Operator Costing aka “umm, what costing?”
This query also takes 30+ seconds but that duration is only on one server. When I pull the database locally to my laptop the query runs in 2. The difference is the scalar UDF's contents operate in parallel on the production box but serially locally. What could account for that?
Any and all of the usual reasons, including those listed in my post Forcing a Parallel Execution Plan. The choice between a parallel and serial plan can be quite close, in estimated cost terms. Small differences in statistics (and the samples they were based on) can affect estimated costs, and hence the decision to use parallelism or not. Other factors that influence costing include the number of logical processors available to the SQL Server instance at the time the plan was compiled, the configured cost threshold, and even the maximum size of the buffer pool (various elements of costing are influenced by this). The list goes on.
Also, scalar T-SQL UDFs cache plans and take parameters, so parameter-sensitivity can be an issue too. Consider the following function in Adventure Works:
CREATE FUNCTION dbo.DAUDF (@TID integer) RETURNS bigint WITH SCHEMABINDING AS BEGIN RETURN ( SELECT COUNT_BIG(*) FROM Production.TransactionHistory AS th WHERE th.TransactionID > @TID ); END
With a zero cost threshold for parallelism, the following invocation uses parallelism inside the UDF:
The cached plan uses parallelism. The following invocation returns no rows but reuses the cached plan and again uses parallelism:
If you clear the plan cache and run the statements in reverse order, a serial plan is cached and reused.
Scalar T-SQL functions are just horrible things, I hope you have a super-stellar excuse for using them 🙂
Definitely check out the PRO version as Greg suggests.
Build="10.50.4270.0" for the production system
Build="10.50.2500.0" for my laptop
The other difference was the compile time node
only the time values are different.
Since the parallelism is in the UDF i didn't expect the plan to provide a difference.
To make a case to my boss, what would plan explorer pro give in the output that SSMS doesn't nor the free version of plan explore provide?