Inconsistent Reads and performance problems

I have a fairly complex query which takes about 3-4 seconds. Since it
uses quite some tables and views which I won't be able to post, I will
just post my findings.
This SPROC takes a long time to comlete often propting to kill the
process. During such poor show the profiler records unsually high
number of reads. The problem goes away on DBCC DBRINDEX. After
examining I found this SPROC uses multiple tables/views which have DBCC
SHOWCONTIG as follows
IndexName Level Pages Rows Extents Exten
tSwitches AverageFreeBytes AveragePa
geDensity ScanDensity BestCount ActualCo
unt LogicalFragmentation ExtentFragm
entation MinimumRecordSize MaximumRecord
Size AverageRecordSize ForwardedReco
rds
PK_RegHousehold 0 133 6840 17 16 2405.344971 70.28237152 100 17 17 0 0 100 1
32 108.651 0
PK_RegPeople 0 481 19648 61 60 2351.11792 70.95233917 100 61 61 0 0 127 177
138.639 0
IX_RegPeopleToAddress 0 190 18364 24 23
2393.199951 70.43241882 100 24 24 0
0 57 65 57.003 0
PK_RegPeopleToPhone 0 484 46855 61 60 23
84.335938 70.54193115 100 61 61 0 0
57 57 57 0
IX_RegPeopleToStudentFamily 0 850 49155
107 106 2408.256104 70.24640656 100
107 107 0 1.869158864 96 247 96.353 0
PK_RegPhones 0 249 25016 32 31 2409.035889 70.23677063 100 32 32 0 6.25 51 7
2 54.605 0
I apologize for the above formatting try reformatting with excel.
Should I try DROPPING an RECREATING all Indexes on these tables with
default fillfactor?
As you notice the the number of rows are not that significant.
The original fill factor on all tables is 70%. Any recommendations for
optimization would be appreciated.
Thanks
MasterofNoneAnybody?
MasterNone wrote:
> I have a fairly complex query which takes about 3-4 seconds. Since it
> uses quite some tables and views which I won't be able to post, I will
> just post my findings.
> This SPROC takes a long time to comlete often propting to kill the
> process. During such poor show the profiler records unsually high
> number of reads. The problem goes away on DBCC DBRINDEX. After
> examining I found this SPROC uses multiple tables/views which have DBCC
> SHOWCONTIG as follows
>
> IndexName Level Pages Rows Extents Exten
tSwitches AverageFreeBytes Average
PageDensity ScanDensity BestCount Actual
Count LogicalFragmentation ExtentFra
gmentation MinimumRecordSize MaximumReco
rdSize AverageRecordSize ForwardedRe
cords
> PK_RegHousehold 0 133 6840 17 16 2405.344971 70.28237152 100 17 17 0 0 100
132 108.651 0
> PK_RegPeople 0 481 19648 61 60 2351.11792 70.95233917 100 61 61 0 0 127 17
7 138.639 0
> IX_RegPeopleToAddress 0 190 18364 24 23
2393.199951 70.43241882 100 24 24
0 0 57 65 57.003 0
> PK_RegPeopleToPhone 0 484 46855 61 60 23
84.335938 70.54193115 100 61 61 0
0 57 57 57 0
> IX_RegPeopleToStudentFamily 0 850 49155
107 106 2408.256104 70.24640656 10
0 107 107 0 1.869158864 96 247 96.353 0
> PK_RegPhones 0 249 25016 32 31 2409.035889 70.23677063 100 32 32 0 6.25 51
72 54.605 0
> I apologize for the above formatting try reformatting with excel.
> Should I try DROPPING an RECREATING all Indexes on these tables with
> default fillfactor?
> As you notice the the number of rows are not that significant.
> The original fill factor on all tables is 70%. Any recommendations for
> optimization would be appreciated.
> Thanks
> MasterofNone|||MasterNone wrote:
> I have a fairly complex query which takes about 3-4 seconds. Since it
> uses quite some tables and views which I won't be able to post, I will
> just post my findings.
> This SPROC takes a long time to comlete often propting to kill the
> process. During such poor show the profiler records unsually high
> number of reads. The problem goes away on DBCC DBRINDEX. After
> examining I found this SPROC uses multiple tables/views which have DBCC
> SHOWCONTIG as follows
>
> IndexName Level Pages Rows Extents Exten
tSwitches AverageFreeBytes Average
PageDensity ScanDensity BestCount Actual
Count LogicalFragmentation ExtentFra
gmentation MinimumRecordSize MaximumReco
rdSize AverageRecordSize ForwardedRe
cords
> PK_RegHousehold 0 133 6840 17 16 2405.344971 70.28237152 100 17 17 0 0 100
132 108.651 0
> PK_RegPeople 0 481 19648 61 60 2351.11792 70.95233917 100 61 61 0 0 127 17
7 138.639 0
> IX_RegPeopleToAddress 0 190 18364 24 23
2393.199951 70.43241882 100 24 24
0 0 57 65 57.003 0
> PK_RegPeopleToPhone 0 484 46855 61 60 23
84.335938 70.54193115 100 61 61 0
0 57 57 57 0
> IX_RegPeopleToStudentFamily 0 850 49155
107 106 2408.256104 70.24640656 10
0 107 107 0 1.869158864 96 247 96.353 0
> PK_RegPhones 0 249 25016 32 31 2409.035889 70.23677063 100 32 32 0 6.25 51
72 54.605 0
> I apologize for the above formatting try reformatting with excel.
> Should I try DROPPING an RECREATING all Indexes on these tables with
> default fillfactor?
> As you notice the the number of rows are not that significant.
> The original fill factor on all tables is 70%. Any recommendations for
> optimization would be appreciated.
> Thanks
> MasterofNone
>
Forget the index stats for now. Start by analyzing the execution plan
for the query, determine where the bottleneck is (table or index
scans?), and focus on eliminating that.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On 30.08.2006 15:06, Tracy McKibben wrote:
> Forget the index stats for now. Start by analyzing the execution plan
> for the query, determine where the bottleneck is (table or index
> scans?), and focus on eliminating that.
I second that. You might see an improvement just after DBCC DBRINDEX
just because now those index pages are in memory. But this won't help
you in the real application situation.
Kind regards
robert