3

I am very new to any type of tuning but have been learning more.. One of my specific databases one of the problems identified with slow queries are a number of Implicit conversions. It is my understand that when 2 non like column types are compared SQL Server has to convert one to the other, there is listing of priorities that tell it how to convert - Since the data type is converted, its possible that it will not use indexes correctly.. One of the first queries identified is a very basic one, and I would like to see if someone could help me identify what I'm seeing on my execution plan...

The basic query is:

(
@1 TINYINT
,@2 VARCHAR(8000)
,@3 NUMERIC(4, 0)
)
SELECT CONVERT([float], [low] / @3, 0)
FROM [master].[dbo].[spt_values]
WHERE [number] = @1
AND [type] = @2

The Execution plan shows:

enter image description here

Detail on the clustered index seek

IndexSeek

spt_vaules.Type is Defined as nchar(3)

The Seek Predicates section is confusing me, exactly which part of this is it converting... My query says [low] is being converted from int to numeric but that doesn't seem to be what the cluster index seek details is showing, is it?

Is there a way to tell in this specific example how much "Better" it would be if I did the conversions Explicitly? I would think I could just do a "cast" in the query and insert a few numbers where the variables are, is that correct?

4
  • 3
    Before doing anything else, download SQL Sentry Plan Explorer it will really help a lot into looking execution plans and analyzing them more thoroughly
    – Yaroslav
    Sep 25, 2014 at 13:54
  • Also, is this an estimated plan? If so, can you get us the actual plan? Better yet, the plan from the plan cache? Estimated plans are usually worthless and not a true indicator of how the plan actually manifests. Sep 25, 2014 at 14:15
  • This is coming from the sys.dm_exec_cached_plans, So I assume it was the actual plan that was used to run this query Sep 25, 2014 at 14:26
  • 2
    sys.dm_exec_cached_plans holds the plan the query will use, not the plan the query did use. Much more useful to generate an actual post-execution plan by running the query with "Include actual execution plan" on (or collecting an actual plan with Plan Explorer). Sep 25, 2014 at 14:35

1 Answer 1

1

Why don't your input parameters match the type for the table? Why would you want to keep the wrong types there and perform any casts or conversions at all (whether implicit or explicit)? Why are you converting anything to FLOAT, of all things? To address specific questions:

My query says [low] is being converted from int to numeric but that doesn't seem to be what the cluster index seek details is showing, is it?

The convert of low is happening in the output, not in the seek predicate (the predicate is what is used to find matching rows and/or eliminate non-matching rows).

Is there a way to tell in this specific example how much "Better" it would be if I did the conversions Explicitly? I would think I could just do a "cast" in the query and insert a few numbers where the variables are, is that correct?

There's no way to make the execution plan show you how much better a different plan would be, except to generate that different plan and compare. You can use this comparison to document how much better it would be if the interface were correct (and two other ways would be to keep the interface but (a) perform explicit converts in the query - not of the column, but of the variables or (b) use local variables of the right type and assign them the values of the parameters). So you could show them 3 different ways to solve the problem, and show evidence that all 3 are better than the current version.

My recommendation is to fix the procedure the right way. First let's look at the actual types you care about:

USE master;
GO
SELECT t.name, c.max_length/CASE 
  WHEN t.name LIKE N'n[cvt]%' THEN 2 ELSE 1 END
FROM sys.all_columns AS c 
INNER JOIN sys.types AS t
ON c.system_type_id = t.system_type_id
AND c.system_type_id = t.user_type_id
WHERE EXISTS
(
  SELECT 1 FROM sys.all_objects AS o
    INNER JOIN sys.schemas AS s
    ON o.[schema_id] = s.[schema_id]
    WHERE o.[object_id] = c.[object_id]
    AND o.name = N'spt_values'
    AND s.name = N'dbo'
)
AND c.name IN (N'number',N'type');

Results:

number    int     4
type      nchar   3

So the interface to your stored procedure should be:

USE yourdb;
GO
ALTER PROCEDURE dbo.some_name
  @1 INT,
  @2 NCHAR(3),
  @3 NUMERIC(4, 0)
AS
BEGIN
  SET NOCOUNT ON;

  SELECT CONVERT([float], [low] / @3, 0) -- don't think you want float here
    FROM [master].[dbo].[spt_values]
    WHERE [number] = @1
    AND [type] = @2;
END
GO

Implicit conversions between varchar and nvarchar can be particularly bad (especially in the opposite scenario as yours - parameter is nvarchar and column is varchar), but there really is no reason to allow for a 8000-character parameter of any type when the longest string possible in the table is 3 characters...

1
  • Aaron - I completely agree, this is not a database designed by "us", this is a software package that our company uses, they have multiple other problems and I am compiling a listing of issues to get to them to fix. Which is why I was asking about the execution plan, I need to be able to document it, so I need to understand myself to explain it to our higher ups. Sep 25, 2014 at 14:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.