Sql-server – CPU vs Elapsed & Parallelism

execution-planparallelismsql-server-2016stored-procedurest-sql

I have read threads here and here and I get that elapsed time is the total duration of the task – and if the elapsed time is less than CPU time, the query went parallel.

After typing that, I was trying to improve a stored procedure's performance in an area in which we are experiencing some slowness.

The existing TSQL:
Paste the Plan

As you can see, we have 6 parameters that are all optional – a customer lookup where you can use a variety or just one variable to search.

When executed, this procedure goes parallel and the statistics IO and time are as follows:
enter image description here

My initial thought was to rewrite the WHERE clause to replace the COALESCE & ISNULL functions to simply ( @paramCustomerID IS NULL or c.id = @paramCustomerID )

And because I find CTE's more readable than a sub-query, I did change that portion of the query as well.

Here is the execution plan for the rewrite: Paste the Plan

And the results of the Statistics IO & Time:
enter image description here

The logical reads from the Customers table was cut by nearly a 1/3 and the CPU time was drastically cut, but the elapsed time is nearly double: new version was 1.2 seconds to .545 for the existing version.

I'm not an expert by any means and I am trying to learn, but the main differences I see is that the new version is performing a Key Lookup and the existing version is using Parallelism.

The advice or knowledge I'm hoping to gain here is which version of the stored procedure would give the best performance? And if the new version should be better, is there anything that could be done to make it run parallel so the elapsed time would be shorter?

Trying to clarify the question –

1) This maybe purely subjective and possibly frowned upon on this site, but based on the information provided; which procedure would you use to get the results to the end user the quickest? The proc with COALESCE/ISNULL functions in the WHERE clause the goes parallel or the revised procedure that has fewer logical reads but a greater elapsed time?

2) If we choose not to use dynamic SQL, what advice would you give to improve query performance for the revised procedure?

As I am typing the edits to try and clarify, I do see that Max has given some very useful information.

Just wanted to add, the Statistic Parser information was provided by this site.

Best Answer

Your first query plan shows parallelism, whereas your second query is purely serial; this is why the second version is showing longer "duration".

The key lookup operations could be prevented by a suitable covering index for the tables where the key lookup is occurring. The standard warning about not blindly creating indexes applies here - don't create duplicate indexes, and check to see if you can leverage an existing index by possibly adding an include clause. For instance, the key lookup on the Customers table is pulling these columns, which it couldn't get by scanning the IX_CustomersSocialSecurityNumber index:

[GoOutdoorsTN_TEST].[dbo].[Customers].driversLicenseNumber
, [GoOutdoorsTN_TEST].[dbo].[Customers].lastName
, [GoOutdoorsTN_TEST].[dbo].[Customers].DocTypeNumber
, [GoOutdoorsTN_TEST].[dbo].[Customers].driversLicenseState

If you added those columns to the index in an INCLUDE clause, that scan would not need to go back to the table to get those columns, making the output that much faster.

Your query uses the "kitchen sink" pattern; i.e. this:

WHERE (@x IS NULL OR someCol = @x)
     AND (@y IS NULL OR someOtherCol = @y)

You can typically get much better query plans, customized for each variation, using dynamic SQL instead of the @x IS NULL piece. Pseudo-code would be:

IF @x IS NULL AND @y IS NOT NULL
    SET @where = 'WHERE someOtherCol = @y';
IF @y IS NULL AND @x IS NOT NULL
    SET @where = 'WHERE someCol = @x';
IF @y IS NULL AND @x IS NULL
    SET @where = '';

This allows the query optimizer to use column statistics in a far more effective manner, since it only needs to think about the columns presented in each unique where clause.

Also of note, I see you're using WITH (NOLOCK) in an effort to prevent your query being affected by blocking. You may want to ensure you understand the effects of reading uncommitted rows inherent in the READ UNCOMMITTED isolation level used by the NOLOCK hint. Aaron Bertrand has a great article about that here

I've noticed the plans show a couple of computer scalar operators where you're doing:

= LTRIM(RTRIM(lastName))

Does your data really have blank space around the real content of lastName? If not, getting rid of those needless functions will really help the query processor provide better plans.

As a way of showing how you might approach the kitchen sink problem, and strictly for learning purposes, consider the below code.

CREATE TABLE dbo.Customers
(
    CustomerID int NOT NULL
        CONSTRAINT PK_Customers
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , FirstName nvarchar(100) NOT NULL
    , LastName nvarchar(100) NOT NULL
    , SSN char(9) NULL
);

Some sample data:

INSERT INTO dbo.Customers (FirstName, LastName, SSN)
VALUES ('Joe', 'Belfiore', '012345678')
    , ('Bill', 'Gates', '876543210')
    , ('Max', 'Vernon', '123123123');
GO

A stored procedure to perform searches:

CREATE PROCEDURE dbo.SearchCustomers
(
    @FirstName nvarchar(100) = NULL
    , @LastName nvarchar(100) = NULL
    , @SSN varchar(9) = NULL
)
AS
BEGIN
    /*
        BE AWARE THIS IS PROTOTYPE CODE THAT IS NOT SAFE
        AGAINST SQL INJECTION VULNERABILIES.

        IT IS STRICTLY TO SHOW HOW TO COMPILE A DYNAMIC
        WHERE CLAUSE!
    */
    SET NOCOUNT ON;
    DECLARE @Where nvarchar(max);
    DECLARE @connector nvarchar(max);
    DECLARE @qry nvarchar(max);
    SET @qry = 'SELECT CustomerID, FirstName, LastName, SSN
FROM dbo.Customers c
';
    SET @where = 'WHERE ';
    SET @connector = '';
    IF @LastName IS NOT NULL
    BEGIN
        SET @where = @where + @connector + 'c.LastName LIKE ''%' + @LastName + '%''';
        SET @connector = ' AND ';
    END
    IF @FirstName IS NOT NULL
    BEGIN
        SET @where = @where + @connector + 'c.FirstName LIKE ''%' + @FirstName + '%''';
        SET @connector = ' AND ';
    END
    IF @SSN IS NOT NULL
    BEGIN
        SET @where = @where + @connector + 'c.SSN LIKE ''%' + @SSN + '%''';
        SET @connector = ' AND ';
    END
    IF @connector <> '' SET @qry = @qry + @Where + ';';
    EXEC sys.sp_executesql @qry;
    PRINT @qry;
END
GO

Some test searches:

EXEC dbo.SearchCustomers @FirstName = N'Max';
EXEC dbo.SearchCustomers @LastName = N'Vernon';
EXEC dbo.SearchCustomers @SSN = N'994', @LastName = N'V'

The queries show in the "Messages" tab are:

SELECT CustomerID, FirstName, LastName, SSN
    FROM dbo.Customers c
    WHERE c.FirstName LIKE '%Max%';

SELECT CustomerID, FirstName, LastName, SSN
    FROM dbo.Customers c
    WHERE c.LastName LIKE '%Vernon%';

SELECT CustomerID, FirstName, LastName, SSN
    FROM dbo.Customers c
    WHERE c.LastName LIKE '%V%' AND c.SSN LIKE '%994%';

Before you implement that code, you really need to read Erland Sommarskog's seminal work on dynamic SQL. He also has a great article about dynamic search which should help.

Related Question