Hot answers tagged execution-plan
It was all down to collation of the column. It was different from the database's (and the table's) collation. Now changed the column's collation to database's and no more implicit conversion shows up. Have no idea about the internals and why it caused the problem.
1. How to understand estimated operator cost? Tb1 which don't have index is scanned and cost is 2 %, whereas index is being used on tb2 and cost is 98%. The heap table is only fully scanned once, but the index seek is executed 1,000,000 times. The optimizer estimates that a million seeks in this case will represent 98.4% of the total cost of executing ...
The CONVERT_IMPLICIT is occurring because you have a collation on the column which does not match the parameter's collation. So the parameter is converted to the column's collation. To explain further - there are collation coercion rules which triggers this conversion. So if you have an implicit collation for the column and a coercible-default for the ...
As Marian noted in the comments, the implicit conversion is in the call to the stored procedure. You should call the procedure like this EXEC test @Username='webSite.com' You are calling the procedure with a nvarchar parameter, but the procedure is expecting a varchar parameter
The decision about whether to use a heap or a clustered index for your underlying table structure is a lot more complicated than the speed of insertion, although that certainly can be a factor. When you insert a new row, it is typically going to be quicker to get the row into the heap, but there are a lot of things going on that can affect both the estimate ...
You can use this select st.text, qp.query_plan, qs.plan_handle, total_worker_time/execution_count AS [Avg CPU Time] FROM sys.dm_exec_query_stats AS qs cross apply sys.dm_exec_sql_text(qs.plan_handle) st cross apply sys.dm_exec_query_plan(qs.plan_handle) qp if you are using sql server 2008 and above you can configure Extended Events as ...
Only top voted, non community-wiki answers of a minimum length are eligible