Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

weired performance killing Expand / Collapse
Author
Message
Posted Wednesday, September 22, 2010 3:00 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 9:24 AM
Points: 1,000, Visits: 2,979
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(OBJECT[#dev2]), SET[#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 REFERENCES[b].[efdt], [b].[exdt], [c].[empno]))
|--Hash Match(Inner Join, HASH[c].[empno])=([b].[empno]), RESIDUAL[tempdb].[dbo].[#empdts].[empno] as [b].[empno]=[tempdb].[dbo].[#dev].[empno] as [c].[empno]))
| |--Table Scan(OBJECT[tempdb].[dbo].[#dev] AS [c]))
| |--Table Scan(OBJECT[tempdb].[dbo].[#empdts] AS [b]))
|--Concatenation
|--Nested Loops(Inner Join, OUTER REFERENCES[PtnIds1097], [Uniq1005], [MI].[empno], [MI].[sdate], [MI].[Reven_ID], [MI].[ASO_optin]) PARTITION ID[PtnIds1097]) OPTIMIZED)
| |--Compute Scalar(DEFINE[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 REFERENCES[Expr1157], [Expr1158], [Expr1159]))
| | |--Merge Interval
| | | |--Concatenation
| | | |--Compute Scalar(DEFINE([Expr1152],[Expr1153],[Expr1151])=GetRangeWithMismatchedTypes([tempdb].[dbo].[#empdts].[efdt] as [b].[efdt],NULL,(22))))
| | | | |--Constant Scan
| | | |--Compute Scalar(DEFINE([Expr1155],[Expr1156],[Expr1154])=GetRangeWithMismatchedTypes(NULL,[tempdb].[dbo].[#empdts].[exdt] as [b].[exdt],(42))))
| | | |--Constant Scan
| | |--IX Seek(OBJECT[State_CTREV].[dbo].[employee_I].[IX_allfields] AS [MI]), SEEK[MI].[empno]=[tempdb].[dbo].[#dev].[empno] as [c].[empno] AND [MI].[sdate] > [Expr1157] AND [MI].[sdate] < [Expr1158]), WHERE[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(OBJECT[State_CTREV].[dbo].[employee_I].[IX_main] AS [MI]), SEEK[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 ID[PtnIds1097]))
|--Nested Loops(Inner Join, OUTER REFERENCES[PtnIds1098], [Uniq1008], [MA].[empno], [MA].[sdate], [MA].[Reven_ID], [MA].[ASO_optin]) PARTITION ID[PtnIds1098]) OPTIMIZED)
|--Compute Scalar(DEFINE[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 REFERENCES[Expr1166], [Expr1167], [Expr1168]))
| |--Merge Interval
| | |--Concatenation
| | |--Compute Scalar(DEFINE([Expr1161],[Expr1162],[Expr1160])=GetRangeWithMismatchedTypes([tempdb].[dbo].[#empdts].[efdt] as [b].[efdt],NULL,(22))))
| | | |--Constant Scan
| | |--Compute Scalar(DEFINE([Expr1164],[Expr1165],[Expr1163])=GetRangeWithMismatchedTypes(NULL,[tempdb].[dbo].[#empdts].[exdt] as [b].[exdt],(42))))
| | |--Constant Scan
| |--IX Seek(OBJECT[State_CTREV].[dbo].[employee_A].[IX_allfields] AS [MA]), SEEK[MA].[empno]=[tempdb].[dbo].[#dev].[empno] as [c].[empno] AND [MA].[sdate] > [Expr1166] AND [MA].[sdate] < [Expr1167]), WHERE[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(OBJECT[State_CTREV].[dbo].[employee_A].[IX_main] AS [MA]), SEEK[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 ID[PtnIds1098]))


execution plan for Bigger and fast performing database.
|--Table Insert(OBJECT[#dev2]), SET[#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, HASH[c].[empno])=([Union1009]), RESIDUAL[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, HASH[c].[empno])=([b].[empno]), RESIDUAL[tempdb].[dbo].[#empdts].[empno] as [b].[empno]=[tempdb].[dbo].[#dev].[empno] as [c].[empno]))
| |--Bitmap(HASH[c].[empno]), DEFINE[Bitmap1190]))
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS[c].[empno]))
| | |--Table Scan(OBJECT[tempdb].[dbo].[#dev] AS [c]))
| |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS[b].[empno]), WHEREPROBE([Bitmap1190])=TRUE))
| |--Table Scan(OBJECT[tempdb].[dbo].[#empdts] AS [b]))
|--Concatenation
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS[MI].[empno]))
| |--Nested Loops(Inner Join, OUTER REFERENCES[PtnIds1095]) PARTITION ID[PtnIds1095]))
| |--Parallelism(Distribute Streams, Demand Partitioning)
| | |--Constant Scan(VALUES((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(OBJECT[State_NYREV].[dbo].[employee_I].[IX_main] AS [MI]), SEEK[MI].[Reven_ID]='NA' OR [MI].[Reven_ID]='SEN') ORDERED FORWARD PARTITION ID[PtnIds1095]))
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS[MA].[empno]))
|--Nested Loops(Inner Join, OUTER REFERENCES[PtnIds1096]) PARTITION ID[PtnIds1096]))
|--Parallelism(Distribute Streams, Demand Partitioning)
| |--Constant Scan(VALUES((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(OBJECT[State_NYREV].[dbo].[employee_A].[IX_main] AS [MA]), SEEK[MA].[Reven_ID]='NA' AND [MA].[ASO_optin]=(1) OR [MA].[Reven_ID]='SEN' AND [MA].[ASO_optin]=(1)) ORDERED FORWARD PARTITION ID[PtnIds1096]))
Post #991590
Posted Wednesday, September 22, 2010 3:05 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 9:24 AM
Points: 1,000, Visits: 2,979
if you need sqlplan file of the execution plan , please check the atatchements.
Post #991595
Posted Wednesday, September 22, 2010 3:28 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:40 AM
Points: 40,398, Visits: 36,838
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 2008, MVP
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

Post #991600
Posted Wednesday, September 22, 2010 5:30 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 9:24 AM
Points: 1,000, Visits: 2,979
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?
Post #991652
Posted Wednesday, September 22, 2010 11:14 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:40 AM
Points: 40,398, Visits: 36,838
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 2008, MVP
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

Post #991713
Posted Wednesday, September 22, 2010 11:29 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:40 AM
Points: 40,398, Visits: 36,838
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 2008, MVP
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

Post #991723
Posted Thursday, September 23, 2010 3:46 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:03 AM
Points: 2,840, Visits: 3,975
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
Post #991855
Posted Thursday, September 23, 2010 6:15 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 9:24 AM
Points: 1,000, Visits: 2,979
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?
Post #991936
Posted Thursday, September 23, 2010 9:42 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 9:24 AM
Points: 1,000, Visits: 2,979
can some one flush out any idea as to why 2 identical databases behaving different.
Post #992130
Posted Thursday, September 23, 2010 10:45 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:40 AM
Points: 40,398, Visits: 36,838
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 2008, MVP
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

Post #992189
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse