April 22, 2014 at 12:11 pm
Hi,
In my execution plan i can see some warning like:
1. "Type conversion in expression (CONVERT_IMPLICIT(varchar(40),[PAIntel].[ActivationDateTime],0)) may affect "CardinalityEstimate" in query plan choice, Type conversion in expression (CONVERT_IMPLICIT(varchar(24),[Machine].[MachineKey],0)) may affect "CardinalityEstimate" in query plan choice "
2. I am not using any order by clause in my query but still its having an sort with warning as
Operator used tempdb to spill data during execution with spill level 2
3. I have a Key look up (clustred) with high cost how do we remove this.
I am doing join with 2 views (there is no indexed view)
and one table is having 84 million records
Any help much appreciated.
April 22, 2014 at 12:53 pm
Query, table definitions, index definitions please.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 22, 2014 at 1:56 pm
1 st tabel is having 48 Lacks recoprds
the 5 th table is having 84 million records
April 22, 2014 at 2:06 pm
Wow!
The script name have nothing to identify the table in question.
You really like to provide puzzles, do you?
Also, please provide the code of DML.GetRetailOrOnlineIndicator(), which seems to be a function you're calling in your insert script.
April 22, 2014 at 2:15 pm
CREATE FUNCTION [DML].[GetRetailOrOnlineIndicator](
@GrpID INT
,@ChanID INT
,@SeqNo INT
)
RETURNS NVARCHAR(10)
AS
BEGIN
DECLARE @RetailOrOnlineIndicator NVARCHAR(10)
IF EXISTS (SELECT 1
FROM DML.ExtKeyWestRetailOnlineInfo
WHERE GrpID = @GrpID AND SeqNo = CONVERT( BIGINT, CAST(@ChanID AS VARCHAR(3)) + RIGHT('000000'+ CAST(@SeqNo AS VARCHAR(6)),6) ))
SET @RetailOrOnlineIndicator = 'Online'
ELSE
SET @RetailOrOnlineIndicator = 'Retail'
RETURN @RetailOrOnlineIndicator
END
GO
April 22, 2014 at 2:30 pm
May I ask for the table definition involved?
You provided a view definition. But we'd need the table def. Especially for [ExtOLSMachine].
April 22, 2014 at 2:37 pm
i dodn't have any table with name as ExtOLSMachine
April 22, 2014 at 2:49 pm
The View [DML].[ExtOLSMachineEntitlement] refers to a table DML.ExtOLSMachine.
You might want to take the effort to have a look at your execution plan... -> The table is indeed referenced.
I guess I'm out of this challenge...
April 22, 2014 at 3:09 pm
Hi Can you pls help me to study and understand the execution plan properly, i have not done this work earlier but not sure where to look,what to look so posted all query.
April 22, 2014 at 5:19 pm
First thing I see in the plan is Query 6 which shows up as 99% of the cost.
Within Query 6, there is a section that accounts for 96% of the plan cost
I suspect (from the estimated and actual rowcounts and the predicates present in the Index Seek on ExtPAIntelActivationEvent.NCIX_ISOCountrycode which includes a condition; ISOCountryCode <> N'' which does not appear anywhere in the query text) that you have another view, perhaps within the Scalar function, perhaps this "DML.ExtKeyWestRetailOnlineInfo"
I would inspect that view to see if it is optimised for the purpose of this query.
I would also rewrite the Scalar function GetRetailOrOnlineIndicator as a Table Valued Function (see here : http://www.sqlservercentral.com/articles/T-SQL/91724/[/url] )
Once that is done, test Query 6 from the plan without the INSERT to see if the row estimates are any better and to see if performance has improved.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply