Return
-
Title
How to manually pull fragmentation info for all sql connections from SSR. -
Description
User likes to manually collect Spotlight fragmentation report data from Spotlight statistic repository database. -
Cause
By default Spotlight doesn't provide fragmentation report. -
Resolution
Using below query to collect fragmentation data from SSR. You will need to replace a few literals with your own valuesUSE [SpotlightStatisticsRepository]EXEC [spotlight_rt_get_batch_data]@start_date = N'jan 1 2018',@end_date = N'dec 1 2018',@domain_name_list = N'{{domain}}',@monitoredobject_list = N'{{instance1}},{{instance2}} ,@table_name = N'FragmentationByIndex',@column_list = N'AllocUnitTypeDesc,AverageFragmentation,DBName,Disabled,IndexName,Key,OwnerName,PageCount,PartitionNumber,TableName,Type,TypeEnum'ReplaceDatabase name in the USE statement if the SSR database name is differentStart_date and end_date with the date range required.{{domain}} with a value from this selectselect domain_description from spotlight_domainsIf you have more than one Diagnostic Server writing to the same SSR you can include them in the parameter by separating them by commas.{{instance1}} and {{intance2}} with the connection names you want to report on. These are in the internal format that Spotlight uses. The format is host_instance_sqlserver .Use this select to see a list of names and then use values from the monitored_object_name columnselect monitored_object_display_name, monitored_object_name from spotlight_monitored_objectswhere monitored_object_name like '%_sqlserver'order by monitored_object_display_name**See the Spotlight Statistics Repository Database blog for more information on how to query data from Spotlight repository database.