Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


weired performance killing


weired performance killing

Author
Message
Tara-1044200
Tara-1044200
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1128 Visits: 3059
Here is the part of the query that it taking too long (temp tables involved run in few seconds which is fine)

select a.empno,elno,sdate,enddate,place,jcode,stprev,statecode,jrev,
spr1,spr2,spr3,spr4,spr5,revec,ctsrev,revid,rload
ecode1,ecode2,ecode3,ecode4,ecode5
into #dev2
from employee a inner join #empdts b
on a.empno=b.empno
inner join #dev c
on a.empno = c.empno
where a.sdate between b.efdt and b.exdt


The same query is ran on idential databases but the duration varies about 12hrs. bigger(900gb) database is super fast than the smaller one (300gb)

Execution plan for smaller-poor performing database.
|--Table Insert(OBJECTSad[#dev2]), SETSad[#dev2].[empno] = [Union1009],[#dev2].[elno] = [Union1011],[#dev2].[sdate] = [Union1014],[#dev2].[enddate] = [Union1015],[#dev2].[place] = [Union1023],[#dev2].[jcode] = [Union1026],[#dev2].[stprev] = [Union1021],[#dev2].[statecode] = [Union1024],[#dev2].[jrev] = [Union1025],[#dev2].[spr1] = [Union1016],[#dev2].[spr2] = [Union1017],[#dev2].[spr3] = [Union1018],[#dev2].[spr4] = [Union1019],[#dev2].[spr5] = [Union1020],[#dev2].[revec] = [Union1036],[#dev2].[ctsrev] = [Union1037],[#dev2].[revid] = [Union1034],[#dev2].[rload] = [Union1074]))
|--Top(ROWCOUNT est 0)
|--Nested Loops(Inner Join, OUTER REFERENCESSad[b].[efdt], [b].[exdt], [c].[empno]))
|--Hash Match(Inner Join, HASHSad[c].[empno])=([b].[empno]), RESIDUALSad[tempdb].[dbo].[#empdts].[empno] as [b].[empno]=[tempdb].[dbo].[#dev].[empno] as [c].[empno]))
| |--Table Scan(OBJECTSad[tempdb].[dbo].[#dev] AS [c]))
| |--Table Scan(OBJECTSad[tempdb].[dbo].[#empdts] AS [b]))
|--Concatenation
|--Nested Loops(Inner Join, OUTER REFERENCESSad[PtnIds1097], [Uniq1005], [MI].[empno], [MI].[sdate], [MI].[Reven_ID], [MI].[ASO_optin]) PARTITION IDSad[PtnIds1097]) OPTIMIZED)
| |--Compute Scalar(DEFINESad[PtnIds1097]=RangePartitionNew([State_CTREV].[dbo].[employee_I].[sdate] as [MI].[sdate],(1),'2004-01-01 00:00:00.000','2004-02-01 00:00:00.000','2004-03-01 00:00:00.000','2004-04-01 00:00:00.000','2004-05-01 00:00:00.000','2004-06-01 00:00:00.000','2004-07-01 00:00:00.000','2004-08-01 00:00:00.000','2004-09-01 00:00:00.000','2004-10-01 00:00:00.000','2004-11-01 00:00:00.000','2004-12-01 00:00:00.000','2005-01-01 00:00:00.000','2005-02-01 00:00:00.000','2005-03-01 00:00:00.000','2005-04-01 00:00:00.000','2005-05-01 00:00:00.000','2005-06-01 00:00:00.000','2005-07-01 00:00:00.000','2005-08-01 00:00:00.000','2005-09-01 00:00:00.000','2005-10-01 00:00:00.000','2005-11-01 00:00:00.000','2005-12-01 00:00:00.000','2006-01-01 00:00:00.000','2006-02-01 00:00:00.000','2006-03-01 00:00:00.000','2006-04-01 00:00:00.000','2006-05-01 00:00:00.000','2006-06-01 00:00:00.000','2006-07-01 00:00:00.000','2006-08-01 00:00:00.000','2006-09-01 00:00:00.000','2006-10-01 00:00:00.000','2006-11-01 00:00:00.000','2006-12-01 00:00:00.000','2007-01-01 00:00:00.000','2007-02-01 00:00:00.000','2007-03-01 00:00:00.000','2007-04-01 00:00:00.000','2007-05-01 00:00:00.000','2007-06-01 00:00:00.000','2007-07-01 00:00:00.000','2007-08-01 00:00:00.000','2007-09-01 00:00:00.000','2007-10-01 00:00:00.000','2007-11-01 00:00:00.000','2007-12-01 00:00:00.000','2008-01-01 00:00:00.000','2008-02-01 00:00:00.000','2008-03-01 00:00:00.000','2008-04-01 00:00:00.000','2008-05-01 00:00:00.000','2008-06-01 00:00:00.000','2008-07-01 00:00:00.000','2008-08-01 00:00:00.000','2008-09-01 00:00:00.000','2008-10-01 00:00:00.000','2008-11-01 00:00:00.000','2008-12-01 00:00:00.000','2009-01-01 00:00:00.000','2009-02-01 00:00:00.000','2009-03-01 00:00:00.000','2009-04-01 00:00:00.000','2009-05-01 00:00:00.000','2009-06-01 00:00:00.000','2009-07-01 00:00:00.000','2009-08-01 00:00:00.000','2009-09-01 00:00:00.000','2009-10-01 00:00:00.000','2009-11-01 00:00:00.000','2009-12-01 00:00:00.000','2010-01-01 00:00:00.000','2010-02-01 00:00:00.000','2010-03-01 00:00:00.000','2010-04-01 00:00:00.000','2010-05-01 00:00:00.000','2010-06-01 00:00:00.000','2010-07-01 00:00:00.000','2010-08-01 00:00:00.000','2010-09-01 00:00:00.000','2010-10-01 00:00:00.000','2010-11-01 00:00:00.000','2010-12-01 00:00:00.000','2011-01-01 00:00:00.000','2011-02-01 00:00:00.000','2011-03-01 00:00:00.000','2011-04-01 00:00:00.000','2011-05-01 00:00:00.000','2011-06-01 00:00:00.000','2011-07-01 00:00:00.000','2011-08-01 00:00:00.000','2011-09-01 00:00:00.000','2011-10-01 00:00:00.000','2011-11-01 00:00:00.000','2011-12-01 00:00:00.000','2012-01-01 00:00:00.000','2012-02-01 00:00:00.000','2012-03-01 00:00:00.000','2012-04-01 00:00:00.000','2012-05-01 00:00:00.000','2012-06-01 00:00:00.000','2012-07-01 00:00:00.000','2012-08-01 00:00:00.000','2012-09-01 00:00:00.000','2012-10-01 00:00:00.000','2012-11-01 00:00:00.000','2012-12-01 00:00:00.000','2013-01-01 00:00:00.000','2013-02-01 00:00:00.000','2013-03-01 00:00:00.000','2013-04-01 00:00:00.000','2013-05-01 00:00:00.000','2013-06-01 00:00:00.000','2013-07-01 00:00:00.000','2013-08-01 00:00:00.000','2013-09-01 00:00:00.000','2013-10-01 00:00:00.000','2013-11-01 00:00:00.000','2013-12-01 00:00:00.000')))
| | |--Nested Loops(Inner Join, OUTER REFERENCESSad[Expr1157], [Expr1158], [Expr1159]))
| | |--Merge Interval
| | | |--Concatenation
| | | |--Compute Scalar(DEFINESad([Expr1152],[Expr1153],[Expr1151])=GetRangeWithMismatchedTypes([tempdb].[dbo].[#empdts].[efdt] as [b].[efdt],NULL,(22))))
| | | | |--Constant Scan
| | | |--Compute Scalar(DEFINESad([Expr1155],[Expr1156],[Expr1154])=GetRangeWithMismatchedTypes(NULL,[tempdb].[dbo].[#empdts].[exdt] as [b].[exdt],(42))))
| | | |--Constant Scan
| | |--IX Seek(OBJECTSad[State_CTREV].[dbo].[employee_I].[IX_allfields] AS [MI]), SEEKSad[MI].[empno]=[tempdb].[dbo].[#dev].[empno] as [c].[empno] AND [MI].[sdate] > [Expr1157] AND [MI].[sdate] < [Expr1158]), WHERESad[State_CTREV].[dbo].[employee_I].[Reven_ID] as [MI].[Reven_ID]='NA' OR [State_CTREV].[dbo].[employee_I].[Reven_ID] as [MI].[Reven_ID]='SEN') ORDERED FORWARD)
| |--Clustered IX Seek(OBJECTSad[State_CTREV].[dbo].[employee_I].[IX_main] AS [MI]), SEEKSad[MI].[Reven_ID]=[State_CTREV].[dbo].[employee_I].[Reven_ID] as [MI].[Reven_ID] AND [MI].[ASO_optin]=[State_CTREV].[dbo].[employee_I].[ASO_optin] as [MI].[ASO_optin] AND [MI].[sdate]=[State_CTREV].[dbo].[employee_I].[sdate] as [MI].[sdate] AND [MI].[empno]=[State_CTREV].[dbo].[employee_I].[empno] as [MI].[empno] AND [Uniq1005]=[Uniq1005]) LOOKUP ORDERED FORWARD PARTITION IDSad[PtnIds1097]))
|--Nested Loops(Inner Join, OUTER REFERENCESSad[PtnIds1098], [Uniq1008], [MA].[empno], [MA].[sdate], [MA].[Reven_ID], [MA].[ASO_optin]) PARTITION IDSad[PtnIds1098]) OPTIMIZED)
|--Compute Scalar(DEFINESad[PtnIds1098]=RangePartitionNew([State_CTREV].[dbo].[employee_A].[sdate] as [MA].[sdate],(1),'2004-01-01 00:00:00.000','2004-02-01 00:00:00.000','2004-03-01 00:00:00.000','2004-04-01 00:00:00.000','2004-05-01 00:00:00.000','2004-06-01 00:00:00.000','2004-07-01 00:00:00.000','2004-08-01 00:00:00.000','2004-09-01 00:00:00.000','2004-10-01 00:00:00.000','2004-11-01 00:00:00.000','2004-12-01 00:00:00.000','2005-01-01 00:00:00.000','2005-02-01 00:00:00.000','2005-03-01 00:00:00.000','2005-04-01 00:00:00.000','2005-05-01 00:00:00.000','2005-06-01 00:00:00.000','2005-07-01 00:00:00.000','2005-08-01 00:00:00.000','2005-09-01 00:00:00.000','2005-10-01 00:00:00.000','2005-11-01 00:00:00.000','2005-12-01 00:00:00.000','2006-01-01 00:00:00.000','2006-02-01 00:00:00.000','2006-03-01 00:00:00.000','2006-04-01 00:00:00.000','2006-05-01 00:00:00.000','2006-06-01 00:00:00.000','2006-07-01 00:00:00.000','2006-08-01 00:00:00.000','2006-09-01 00:00:00.000','2006-10-01 00:00:00.000','2006-11-01 00:00:00.000','2006-12-01 00:00:00.000','2007-01-01 00:00:00.000','2007-02-01 00:00:00.000','2007-03-01 00:00:00.000','2007-04-01 00:00:00.000','2007-05-01 00:00:00.000','2007-06-01 00:00:00.000','2007-07-01 00:00:00.000','2007-08-01 00:00:00.000','2007-09-01 00:00:00.000','2007-10-01 00:00:00.000','2007-11-01 00:00:00.000','2007-12-01 00:00:00.000','2008-01-01 00:00:00.000','2008-02-01 00:00:00.000','2008-03-01 00:00:00.000','2008-04-01 00:00:00.000','2008-05-01 00:00:00.000','2008-06-01 00:00:00.000','2008-07-01 00:00:00.000','2008-08-01 00:00:00.000','2008-09-01 00:00:00.000','2008-10-01 00:00:00.000','2008-11-01 00:00:00.000','2008-12-01 00:00:00.000','2009-01-01 00:00:00.000','2009-02-01 00:00:00.000','2009-03-01 00:00:00.000','2009-04-01 00:00:00.000','2009-05-01 00:00:00.000','2009-06-01 00:00:00.000','2009-07-01 00:00:00.000','2009-08-01 00:00:00.000','2009-09-01 00:00:00.000','2009-10-01 00:00:00.000','2009-11-01 00:00:00.000','2009-12-01 00:00:00.000','2010-01-01 00:00:00.000','2010-02-01 00:00:00.000','2010-03-01 00:00:00.000','2010-04-01 00:00:00.000','2010-05-01 00:00:00.000','2010-06-01 00:00:00.000','2010-07-01 00:00:00.000','2010-08-01 00:00:00.000','2010-09-01 00:00:00.000','2010-10-01 00:00:00.000','2010-11-01 00:00:00.000','2010-12-01 00:00:00.000','2011-01-01 00:00:00.000','2011-02-01 00:00:00.000','2011-03-01 00:00:00.000','2011-04-01 00:00:00.000','2011-05-01 00:00:00.000','2011-06-01 00:00:00.000','2011-07-01 00:00:00.000','2011-08-01 00:00:00.000','2011-09-01 00:00:00.000','2011-10-01 00:00:00.000','2011-11-01 00:00:00.000','2011-12-01 00:00:00.000','2012-01-01 00:00:00.000','2012-02-01 00:00:00.000','2012-03-01 00:00:00.000','2012-04-01 00:00:00.000','2012-05-01 00:00:00.000','2012-06-01 00:00:00.000','2012-07-01 00:00:00.000','2012-08-01 00:00:00.000','2012-09-01 00:00:00.000','2012-10-01 00:00:00.000','2012-11-01 00:00:00.000','2012-12-01 00:00:00.000','2013-01-01 00:00:00.000','2013-02-01 00:00:00.000','2013-03-01 00:00:00.000','2013-04-01 00:00:00.000','2013-05-01 00:00:00.000','2013-06-01 00:00:00.000','2013-07-01 00:00:00.000','2013-08-01 00:00:00.000','2013-09-01 00:00:00.000','2013-10-01 00:00:00.000','2013-11-01 00:00:00.000','2013-12-01 00:00:00.000')))
| |--Nested Loops(Inner Join, OUTER REFERENCESSad[Expr1166], [Expr1167], [Expr1168]))
| |--Merge Interval
| | |--Concatenation
| | |--Compute Scalar(DEFINESad([Expr1161],[Expr1162],[Expr1160])=GetRangeWithMismatchedTypes([tempdb].[dbo].[#empdts].[efdt] as [b].[efdt],NULL,(22))))
| | | |--Constant Scan
| | |--Compute Scalar(DEFINESad([Expr1164],[Expr1165],[Expr1163])=GetRangeWithMismatchedTypes(NULL,[tempdb].[dbo].[#empdts].[exdt] as [b].[exdt],(42))))
| | |--Constant Scan
| |--IX Seek(OBJECTSad[State_CTREV].[dbo].[employee_A].[IX_allfields] AS [MA]), SEEKSad[MA].[empno]=[tempdb].[dbo].[#dev].[empno] as [c].[empno] AND [MA].[sdate] > [Expr1166] AND [MA].[sdate] < [Expr1167]), WHERESad[State_CTREV].[dbo].[employee_A].[ASO_optin] as [MA].[ASO_optin]=(1) AND ([State_CTREV].[dbo].[employee_A].[Reven_ID] as [MA].[Reven_ID]='NA' OR [State_CTREV].[dbo].[employee_A].[Reven_ID] as [MA].[Reven_ID]='SEN')) ORDERED FORWARD)
|--Clustered IX Seek(OBJECTSad[State_CTREV].[dbo].[employee_A].[IX_main] AS [MA]), SEEKSad[MA].[Reven_ID]=[State_CTREV].[dbo].[employee_A].[Reven_ID] as [MA].[Reven_ID] AND [MA].[ASO_optin]=[State_CTREV].[dbo].[employee_A].[ASO_optin] as [MA].[ASO_optin] AND [MA].[sdate]=[State_CTREV].[dbo].[employee_A].[sdate] as [MA].[sdate] AND [MA].[empno]=[State_CTREV].[dbo].[employee_A].[empno] as [MA].[empno] AND [Uniq1008]=[Uniq1008]) LOOKUP ORDERED FORWARD PARTITION IDSad[PtnIds1098]))


execution plan for Bigger and fast performing database.
|--Table Insert(OBJECTSad[#dev2]), SETSad[#dev2].[empno] = [Union1009],[#dev2].[elno] = [Union1011],[#dev2].[sdate] = [Union1014],[#dev2].[enddate] = [Union1015],[#dev2].[place] = [Union1023],[#dev2].[jcode] = [Union1026],[#dev2].[stprev] = [Union1021],[#dev2].[statecode] = [Union1024],[#dev2].[jrev] = [Union1025],[#dev2].[spr1] = [Union1016],[#dev2].[spr2] = [Union1017],[#dev2].[spr3] = [Union1018],[#dev2].[spr4] = [Union1019],[#dev2].[spr5] = [Union1020],[#dev2].[revec] = [Union1036],[#dev2].[ctsrev] = [Union1037],[#dev2].[revid] = [Union1034],[#dev2].[rload] = [Union1074]))
|--Top(ROWCOUNT est 0)
|--Parallelism(Gather Streams)
|--Hash Match(Inner Join, HASHSad[c].[empno])=([Union1009]), RESIDUALSad[Union1009]=[tempdb].[dbo].[#dev].[empno] as [c].[empno] AND [Union1014]>=[tempdb].[dbo].[#empdts].[efdt] as [b].[efdt] AND [Union1014]<=[tempdb].[dbo].[#empdts].[exdt] as [b].[exdt]))
|--Hash Match(Inner Join, HASHSad[c].[empno])=([b].[empno]), RESIDUALSad[tempdb].[dbo].[#empdts].[empno] as [b].[empno]=[tempdb].[dbo].[#dev].[empno] as [c].[empno]))
| |--Bitmap(HASHSad[c].[empno]), DEFINESad[Bitmap1190]))
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNSSad[c].[empno]))
| | |--Table Scan(OBJECTSad[tempdb].[dbo].[#dev] AS [c]))
| |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNSSad[b].[empno]), WHERESadPROBE([Bitmap1190])=TRUE))
| |--Table Scan(OBJECTSad[tempdb].[dbo].[#empdts] AS [b]))
|--Concatenation
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNSSad[MI].[empno]))
| |--Nested Loops(Inner Join, OUTER REFERENCESSad[PtnIds1095]) PARTITION IDSad[PtnIds1095]))
| |--Parallelism(Distribute Streams, Demand Partitioning)
| | |--Constant Scan(VALUESSad((1)),((2)),((3)),((4)),((5)),((6)),((7)),((8)),((9)),((10)),((11)),((12)),((13)),((14)),((15)),((16)),((17)),((18)),((19)),((20)),((21)),((22)),((23)),((24)),((25)),((26)),((27)),((28)),((29)),((30)),((31)),((32)),((33)),((34)),((35)),((36)),((37)),((38)),((39)),((40)),((41)),((42)),((43)),((44)),((45)),((46)),((47)),((48)),((49)),((50)),((51)),((52)),((53)),((54)),((55)),((56)),((57)),((58)),((59)),((60)),((61)),((62)),((63)),((64)),((65)),((66)),((67)),((68)),((69)),((70)),((71)),((72)),((73)),((74)),((75)),((76)),((77)),((78)),((79)),((80)),((81)),((82)),((83)),((84)),((85)),((86)),((87)),((88)),((89)),((90)),((91)),((92)),((93)),((94)),((95)),((96)),((97)),((98)),((99)),((100)),((101)),((102)),((103)),((104)),((105)),((106)),((107)),((108)),((109)),((110)),((111)),((112)),((113)),((114)),((115)),((116)),((117)),((118)),((119)),((120)),((121)),((122)),((123)),((124)),((125)),((126)),((127)),((128)),((129)),((130)),((131)),((132)),((133)),((134)),((135)),((136)),((137)),((138)),((139)),((140)),((141)),((142)),((143)),((144)),((145)),((146)),((147)),((148)),((149)),((150)),((151)),((152)),((153)),((154)),((155)),((156)),((157))))
| |--Clustered IX Seek(OBJECTSad[State_NYREV].[dbo].[employee_I].[IX_main] AS [MI]), SEEKSad[MI].[Reven_ID]='NA' OR [MI].[Reven_ID]='SEN') ORDERED FORWARD PARTITION IDSad[PtnIds1095]))
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNSSad[MA].[empno]))
|--Nested Loops(Inner Join, OUTER REFERENCESSad[PtnIds1096]) PARTITION IDSad[PtnIds1096]))
|--Parallelism(Distribute Streams, Demand Partitioning)
| |--Constant Scan(VALUESSad((1)),((2)),((3)),((4)),((5)),((6)),((7)),((8)),((9)),((10)),((11)),((12)),((13)),((14)),((15)),((16)),((17)),((18)),((19)),((20)),((21)),((22)),((23)),((24)),((25)),((26)),((27)),((28)),((29)),((30)),((31)),((32)),((33)),((34)),((35)),((36)),((37)),((38)),((39)),((40)),((41)),((42)),((43)),((44)),((45)),((46)),((47)),((48)),((49)),((50)),((51)),((52)),((53)),((54)),((55)),((56)),((57)),((58)),((59)),((60)),((61)),((62)),((63)),((64)),((65)),((66)),((67)),((68)),((69)),((70)),((71)),((72)),((73)),((74)),((75)),((76)),((77)),((78)),((79)),((80)),((81)),((82)),((83)),((84)),((85)),((86)),((87)),((88)),((89)),((90)),((91)),((92)),((93)),((94)),((95)),((96)),((97)),((98)),((99)),((100)),((101)),((102)),((103)),((104)),((105)),((106)),((107)),((108)),((109)),((110)),((111)),((112)),((113)),((114)),((115)),((116)),((117)),((118)),((119)),((120)),((121)),((122)),((123)),((124)),((125)),((126)),((127)),((128)),((129)),((130)),((131)),((132)),((133)),((134)),((135)),((136)),((137)),((138)),((139)),((140)),((141)),((142)),((143)),((144)),((145)),((146)),((147)),((148)),((149)),((150)),((151)),((152)),((153)),((154)),((155)),((156)),((157))))
|--Clustered IX Seek(OBJECTSad[State_NYREV].[dbo].[employee_A].[IX_main] AS [MA]), SEEKSad[MA].[Reven_ID]='NA' AND [MA].[ASO_optin]=(1) OR [MA].[Reven_ID]='SEN' AND [MA].[ASO_optin]=(1)) ORDERED FORWARD PARTITION IDSad[PtnIds1096]))

Tara-1044200
Tara-1044200
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1128 Visits: 3059
if you need sqlplan file of the execution plan , please check the atatchements.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56047 Visits: 44706
Tara-1044200 (9/22/2010)
if you need sqlplan file of the execution plan , please check the atatchements.


What attachments?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Tara-1044200
Tara-1044200
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1128 Visits: 3059
Gila, actually its the same text files which i copied in the post but could not attach them, can you give an idea with weired behaviour of my databases.
both db's are completely identical(tables,strcutures,indexes etc..) except the amount of data. i have 10 such databases but only this database is choosing a diferent execution plan than others and is running 18hrs instead of regualr 28min.

please let me know if you have any thoughts on this?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56047 Visits: 44706
Please can you post the execution plans as .sqlplan files? http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
There's a lot of important information which is not present in a copy-paste of part of the text plan.

Table and index defs would help a lot too.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


GilaMonster
GilaMonster
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56047 Visits: 44706
Tara-1044200 (9/22/2010)
both db's are completely identical(tables,strcutures,indexes etc..) except the amount of data.


Amount of data is a major factor in query plans.

Can you try a statistics update on the DB that's running slow?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Bhuvnesh
Bhuvnesh
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3472 Visits: 4076
i am also interested in table/index definition

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Tara-1044200
Tara-1044200
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1128 Visits: 3059
I have tried statistics update, drop/re create index, created new db and imported all the data,restored the same database again,wrapped the view in another view which the query is accessing,creates TVF (function)....

all of the above didnt change the query plan.

If it matters with the amount of data, i owuld expect smaller db to finish the query fastert than bigger one, but its reverse, any explanation?
Tara-1044200
Tara-1044200
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1128 Visits: 3059
can some one flush out any idea as to why 2 identical databases behaving different.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56047 Visits: 44706
GilaMonster (9/22/2010)
Please can you post the execution plans as .sqlplan files? http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
There's a lot of important information which is not present in a copy-paste of part of the text plan.

Table and index defs would help a lot too.


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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search