Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Suggestions on fragmentation stats below?

Author  Topic 

kshahzad
Starting Member

45 Posts

Posted - 2013-07-23 : 09:10:53
Schema Table Index IndexType AvgFrag% AvgSpaceUsed% RecordCnt FragmentCnt
dbo address IX_Address_szPhone NONCLUSTERED INDEX 3 99 23200 4
dbo AddressPhone PK_AddressPhone CLUSTERED INDEX 0 99 15818275 7765
dbo Consultants PK_Consultants CLUSTERED INDEX 0 99 6 NULL
dbo Consultants_History PK_Consultants_History CLUSTERED INDEX 0 99 9 NULL
dbo CustomReport_Output_PatientPerFamilyPhysician CK_CustomReport_Output_PatientPerFamilyPhysician CLUSTERED INDEX 0 99 216680 NULL
dbo Drug3_Backup_Table NULL HEAP 7 99 52399 8
dbo Drugforms_Backup_Table NULL HEAP 3 99 58180 2
dbo ICD_backup_ICPC NULL HEAP 0 99 88797 2
dbo map_tempaltes_14July849pm NULL HEAP 35 99 858075 202
dbo map_templates_12Jul13 NULL HEAP 13 99 857683 80
dbo map_templates_16July1302 NULL HEAP 1 99 858157 12
dbo MASTER_TemplatesDialoguesControlCheckbox NULL HEAP 0 99 189913 2
dbo MASTER_TemplatesDialoguesControlSelectList NULL HEAP 1 99 46691 2
dbo MASTER_TemplatesDialoguesControlSelectListListItems NULL HEAP 0 99 600884 3
dbo MASTER_TemplatesDialoguesControlSelectListListName NULL HEAP 1 99 45090 2
dbo MASTER_TemplatesDialoguesControlSimpleTableDefaultData NULL HEAP 0 99 548355 2
dbo MASTER_TemplatesDialoguesControlStringBuilderNode NULL HEAP 2 99 68251 4
dbo MASTER_TemplatesDialoguesControlTextbox NULL HEAP 0 99 91018 2
dbo MASTER_TemplatesDialoguesStructure NULL HEAP 0 99 364743 2
dbo MASTER_TemplatesStructure NULL HEAP 1 99 102174 2
dbo NIC_Dashboard_FinancialStat_Summary PK_NIC_Dashboard_FinancialStat_Summary CLUSTERED INDEX 6 99 37875 24
dbo NIC_Dashboard_OperationalStat_Summary PK_NIC_Dashboard_OperationalStat_Summary CLUSTERED INDEX 0 99 503003 180
dbo NIC_Dashboard_OptionalChart_Summary PK_NIC_Dashboard_OptionalChart_Summary CLUSTERED INDEX 1 99 159886 65
dbo NIC_Dashboard_PatientTrends PK_NIC_Dashboard_PatientTrends CLUSTERED INDEX 5 99 18774 12
dbo PhysicianLabReportsTests PK_LabReportsTests CLUSTERED INDEX 0 99 2 NULL
dbo Prescription PK_Prescription CLUSTERED INDEX 0 99 106 NULL
dbo Tmp_Map_DiagnosisToProgressNote NULL HEAP 6 99 9004921 451
dbo Tmp_TemplatesDialoguesStructure NULL HEAP 0 99 326785 2
dbo UnmatchingPhysicianLabReportsTests IX_UnmatchingPhysicianLabReportsTests_LabReport CLUSTERED INDEX 0 99 4 NULL
dbo update_inactive NULL HEAP 88 99 30856 42
dbo update_route NULL HEAP 87 99 17353 16
dbo Bill_NS_ServiceProviders_20111202 NULL HEAP 37 98 16693 11
dbo DirectoryNB_26June2013_0957pm NULL HEAP 30 98 10068 10
dbo DirectoryNS_26June2013_0928pm NULL HEAP 23 98 185635 127
dbo DirectoryNS_JULY NULL HEAP 2 98 86487 8
dbo DirectoryNS1 NULL HEAP 7 98 82283 19
dbo DrugCompanies_Backup_Table NULL HEAP 1 98 30414 2
dbo icd_bill_nb_back NULL HEAP 11 98 15227 9
dbo LabForm_NSHIS PK_LabForm_NSHIS CLUSTERED INDEX 0 98 1607860 NULL
dbo LOINCDB IX_LOINCDB_COMPONENT NONCLUSTERED INDEX 0 98 175 1
dbo MASTER_Templates NULL HEAP 3 98 20440 2
dbo MASTER_TemplatesDialogues NULL HEAP 2 98 24991 2
dbo MASTER_TemplatesDialoguesControlDate NULL HEAP 10 98 9801 2
dbo MASTER_TemplatesDialoguesControlSimpleTable NULL HEAP 5 98 18944 2
dbo MASTER_TemplatesDialoguesControlStringBuilderNode NULL HEAP 0 98 70383 NULL
dbo MASTER_TemplatesDialoguesControlTextarea NULL HEAP 1 98 36157 2
dbo multum_drug_name_map NULL HEAP 23 98 80306 15
dbo Report_AdHoc_Results CK_Report_AdHoc_Results CLUSTERED INDEX 0 98 212201 NULL
dbo Route_Backup_Table NULL HEAP 5 98 33140 2
dbo RPTResults PK_RPTResults CLUSTERED INDEX 0 98 2503755 NULL
dbo TemplatesCDMOverdueAlerts PK_TemplatesCDMOverdueAlerts CLUSTERED INDEX 4 98 39094 28
dbo UnmPhysicianLabReportTestPathNetHTML PK_UnmPhysicianLabReportTestPathNetHTML CLUSTERED INDEX 3 98 35673 8
dbo update_comp NULL HEAP 90 98 15096 38
dbo update_form NULL HEAP 85 98 15266 15
dbo update_ingred3 NULL HEAP 97 98 25627 47
dbo update_ther NULL HEAP 92 98 14533 26

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-07-23 : 13:10:35
Are you having performance issues?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2013-07-24 : 12:15:35
Generally,most of them rebuild indexes whose fragmentation level is above 30 and reorganize between 10 and 30.
Ola Hallengren's script can be used for this and others too have some scripts.

mohammad.javeed.ahmed@gmail.com
Go to Top of Page

kshahzad
Starting Member

45 Posts

Posted - 2013-07-24 : 16:55:18
yes i am having performance issues
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-07-24 : 16:56:05
I would look at these things then:
statistics
perfmon
missing indexes
execution plans
code refactoring

Removing fragmentation will help very little with performance, if at all. But if running reorg or rebuild helps things, then I would bet a huge amount of money it was due to statistics and not fragmentation as you get updated stats with index maintenance.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
   

- Advertisement -