1 Reply Latest reply on Jul 10, 2012 9:25 AM by travis_vanholland

    Orion SQL Performance/Problems

    travis_vanholland

      Hello all,

       

      Let me know if I am rehashing stuff here but after a ton of research and searching threw the forums I'm still a little unsure of how to perform proper database maintenance. Does this happen automatically? How do I properly see the health of my frontend/backend?

       

      We have our systems deployed virtually with a box for the poller and dedicated backend for SQL.The 2 virtuals are pretty beefy. The front end has 4 vCPU's 8GB of RAM. Backend has 2 vCPU's 8GB of RAM. Both of which are running on SAN storage (Dell Equallogic 4100PS).

       

      I recently just attached the SQL APM template to the backend and its reporting some issues. The Full scan's per second which as you can see from the screenshot is happening quite regularly. Also, it is showing fragmentation on some tables (not sure which ones) which I was alerted on through the events monitor (see other screen shot).

       

      I have followed this article to see if it would help but I can't tell if its working .

       

      If you need more info or could provide some insight I would be forever greatful!

       

      7/9/2012 2:25 AMIndexes with fragmentation over 90% found during DB maintenance.
      7/8/2012 2:27 AMIndexes with fragmentation over 90% found during DB maintenance.
      7/7/2012 2:28 AMIndexes with fragmentation over 90% found during DB maintenance.
      7/6/2012 2:26 AMIndexes with fragmentation over 90% found during DB maintenance.
      7/5/2012 2:28 AMIndexes with fragmentation over 90% found during DB maintenance.
      7/4/2012 2:23 AMIndexes with fragmentation over 90% found during DB maintenance.
      7/3/2012 2:23 AMIndexes with fragmentation over 90% found during DB maintenance.

       

      7/10/2012 8:49 AMFull Scans/sec for SQL Server 2005-2008 Performance (SQL) on ICSI-SQL111 is in a critical state
      7/10/2012 8:43 AMFull Scans/sec for SQL Server 2005-2008 Performance (SQL) on ICSI-SQL111 is up
      7/10/2012 8:39 AMFull Scans/sec for SQL Server 2005-2008 Performance (SQL) on ICSI-SQL111 is in a warning state
      7/10/2012 8:28 AMFull Scans/sec for SQL Server 2005-2008 Performance (SQL) on ICSI-SQL111 is in a critical state
      7/10/2012 8:23 AMFull Scans/sec for SQL Server 2005-2008 Performance (SQL) on ICSI-SQL111 is in a warning state
      7/10/2012 8:18 AMFull Scans/sec for SQL Server 2005-2008 Performance (SQL) on ICSI-SQL111 is in a critical state
      7/10/2012 8:13 AMFull Scans/sec for SQL Server 2005-2008 Performance (SQL) on ICSI-SQL111 is up
      7/10/2012 7:49 AMFull Scans/sec for SQL Server 2005-2008 Performance (SQL) on ICSI-SQL111 is in a critical state
      7/10/2012 7:39 AMFull Scans/sec for SQL Server 2005-2008 Performance (SQL) on ICSI-SQL111 is in a warning state
      7/10/2012 7:29 AMFull Scans/sec for SQL Server 2005-2008 Performance (SQL) on ICSI-SQL111 is in a critical state
      7/10/2012 7:23 AMFull Scans/sec for SQL Server 2005-2008 Performance (SQL) on ICSI-SQL111 is in a warning state
      7/10/2012 7:18 AMFull Scans/sec for SQL Server 2005-2008 Performance (SQL) on ICSI-SQL111 is in a critical state
      7/10/2012 7:09 AMFull Scans/sec for SQL Server 2005-2008 Performance (SQL) on ICSI-SQL111 is up
      7/10/2012 7:03 AMFull Scans/sec for SQL Server 2005-2008 Performance (SQL) on ICSI-SQL111 is in a critical state
      7/10/2012 6:53 AMFull Scans/sec for SQL Server 2005-2008 Performance (SQL) on ICSI-SQL111 is up
      7/10/2012 6:48 AMFull Scans/sec for SQL Server 2005-2008 Performance (SQL) on ICSI-SQL111 is in a critical state
      7/10/2012 6:38 AMFull Scans/sec for SQL Server 2005-2008 Performance (SQL) on ICSI-SQL111 is up
      7/10/2012 6:28 AMFull Scans/sec for SQL Server 2005-2008 Performance (SQL) on ICSI-SQL111 is in a warning state
      7/10/2012 6:23 AMFull Scans/sec for SQL Server 2005-2008 Performance (SQL) on ICSI-SQL111 is up
      7/10/2012 5:29 AMFull Scans/sec for SQL Server 2005-2008 Performance (SQL) on ICSI-SQL111 is in a critical state
      7/10/2012 5:23 AMFull Scans/sec for SQL Server 2005-2008 Performance (SQL) on ICSI-SQL111 is up
      7/10/2012 5:18 AMFull Scans/sec for SQL Server 2005-2008 Performance (SQL) on ICSI-SQL111 is in a warning state
      7/10/2012 5:08 AMFull Scans/sec for SQL Server 2005-2008 Performance (SQL) on ICSI-SQL111 is up
      7/10/2012 4:59 AMFull Scans/sec for SQL Server 2005-2008 Performance (SQL) on ICSI-SQL111 is in a critical state
      7/10/2012 4:53 AMFull Scans/sec for SQL Server 2005-2008 Performance (SQL) on ICSI-SQL111 is up
        • Re: Orion SQL Performance/Problems
          travis_vanholland

          Found a query to display which tables are fragmented.

           

          dbNameSchemaNameTableNamenameavg_fragmenatation_in_percent
          SolarWindsOriondboAlertStatusPK_AlertStatus88.8888888888889
          SolarWindsOriondboNodeL3EntriesIX_NodeL3Entries_NodeID88.4210526315789
          SolarWindsOriondboUDT_PortUsage_DetailPK_UDT_PortUsage_Detail87.012987012987
          SolarWindsOriondboTopologyDataPK_TopologyData85
          SolarWindsOriondboIPAM_IPHistoryIX_IPAM_IPAddressN84.6153846153846
          SolarWindsOriondboNodePortInterfaceMapIX_NodePortInterfaceMap_NodeID76.158940397351
          SolarWindsOriondboNodeL2ConnectionsIX_NodeL2Connections_NodeID73.1481481481482
          SolarWindsOriondboAPM_ThresholdPK_APM_Threshold70
          SolarWindsOriondboEventsIX_NetObjectID67.1641791044776
          SolarWindsOriondboCPUMultiLoad_DetailIX_CPUMultiLoad_Detail_CPUIndex65.3081510934394
          SolarWindsOriondboAPM_ComponentTemplatePK_APM_ComponentTemplate63.6363636363636
          SolarWindsOriondboUDT_EndpointIX_UDT_Endpoint_MACAddress60
          SolarWindsOriondboVIM_HostStatistics_DailyPK_VIM_HostStatistics_Daily59.0909090909091
          SolarWindsOriondboNPM_NODESPK_NPM_NODES53.8461538461538
          SolarWindsOriondboIPAM_IPHistoryIX_IPAM_FromValue50
          SolarWindsOriondboUDT_PortIX_UDT_Port_TrunkMode50
          SolarWindsOriondboIPAM_IPHistoryIX_IPAM_IntoValue47.0588235294118
          SolarWindsOriondboCredentialPropertyPK_CredentialProperty46.6666666666667
          SolarWindsOriondboNodeChildStatusPK_NodeChildStatus46.4285714285714
          SolarWindsOriondboContainerMemberSnapshotsPK_ContainerMemberSnapshots45.4545454545455
          SolarWindsOriondboAPM_PortEvidence_DetailIX_APM_PortEvidence_Detail_ComponentStatusID42.1052631578947
          SolarWindsOriondboNPM_NV_EW_DEVICEPK_NPM_NV_EW_DEVICE41.6666666666667
          SolarWindsOriondboUDT_PortIX_UDT_Port_MACAddress39.2857142857143
          SolarWindsOriondboDiscoveredInterfacesPK_DiscoveredInterface37.5
          SolarWindsOriondboInterfacesIX_Interfaces_PhysicalAddress36.8421052631579
          SolarWindsOriondboWebUserSettingsPK_WebUserSettings34.4827586206897
          SolarWindsOriondboNodeMACAddressesPK_NodeMACAddresses34.2857142857143
          SolarWindsOriondboResourcesPK_Resources33.3333333333333
          SolarWindsOriondboDiscoveredPollersPK_DiscoveredPollers33.3333333333333
          SolarWindsOriondboAPM_ComponentStatus_DailyPK_APM_ComponentStatus_Daily33.3333333333333
          SolarWindsOriondboNPM_DVC_PROPERTIESIX_NPM_DVC_PROPERTIES_DVC_PROP33.3333333333333
          SolarWindsOriondboVIM_ClusterStatistics_DetailIX_VIM_ClusterStatistics_Detail_DateTime32.4324324324324
          SolarWindsOriondboVolumeUsage_DetailPK_VolumeUsage_Detail32.3778195488722
          SolarWindsOriondboVolumesVolumes_PK31.8181818181818
          SolarWindsOriondboUDT_DNSNamePK_UDT_DNSName31.5789473684211
          SolarWindsOriondboVIM_VirtualMachinesPK_VIM_VirtualMachines31.25
          SolarWindsOriondboAlertLogIX_AlertLog_DateTime30.7692307692308
          SolarWindsOriondboEventsIX_NetworkNode28.921568627451
          SolarWindsOriondboCiscoBuffers_DetailPK_CiscoBuffers_Detail28.5429141716567
          SolarWindsOriondboUDT_EndpointPK_UDT_Endpoint28
          SolarWindsOriondboContainerMemberDefinitionsPK_ContainerMemberDefinitions27.2727272727273
          SolarWindsOriondboEventsPK_Events27.0833333333333
          SolarWindsOriondboCPUMultiLoad_DetailPK_CPUMultiLoad_NodeID26.8471104608632
          SolarWindsOriondboInterfaceErrors_DetailPK_InterfaceErrors_Detail26.2241566920566
          SolarWindsOriondboCPUMultiLoad_DailyIX_CPUMultiLoad_Daily_CPUIndex24.5283018867925
          SolarWindsOriondboVIM_ClusterStatistics_DailyIX_VIM_ClusterStatistics_Daily_DateTime23.5294117647059
          SolarWindsOriondboVIM_HostStatistics_HourlyIX_VIM_HostStatistics_Hourly_DateTime23.3333333333333
          SolarWindsOriondboCPULoad_DetailPK_CPULoad_Detail22.7955911823647
          SolarWindsOriondboAPM_ApplicationStatus_DetailPK_APM_ApplicationStatus_Detail22.2222222222222
          SolarWindsOriondboContainerStatus_DailyDataPK_ContainerStatus_DailyData21.4285714285714
          SolarWindsOriondboPollersIX_Pollers_NetObject20.9302325581395
          SolarWindsOriondboUDT_DNSNameIX_UDT_DNSName_ID20