Quick Orion Database Health Check

Version 39

    ***THIS POST IS MOVED HERE WITH MORE DETAILS***

    Quick Orion Database Health Check

    Quick Orion database health check guide - SolarWinds Worldwide, LLC. Help and Support

     

     

     

     

    I have tired to cover some DB performance issues and questions within this post.

     

    1. I have very large Orion DB what should i check ?
    2. I have database performance issues how can i improve my DB performance ?
    3. I am seeing  high fragmentation for Orion DB tables why ?
    4. Orion database is growing very quickly why ?
    5. I have SQL Express and database is over 90% why / How can i clear space from database ?

     

     

    What is DB fragmentation why my Orion DB is over 90 % fragmented ?

    Let me first explain DB fragmentation in details . We already have a KB for this however i have included more details below and steps you can perform in order to improve the DB performance by reducing the DB size .

    Indexes with fragmentation over 90% found during DB maintenance.

     

    Database fragmentation is similar to disk fragmentation in that the data is stored in various places in the database file instead of sequentially or next to like data within the database.

    This often occurs where you have non-sequential keys and the constant inserting, updating and deleting of data causes the data to become fragmented as well as the use of additional data pages to store the data.

    Orion does the same for data summarization therefor high DB Fragmentation within the tables are normal and expected behavior .

    For more details see the post below.

    https://thwack.solarwinds.com/community/solarwinds-community/geek-speak_tht/blog/2013/10/10/index-fragmentation-in-sql-server-the-essentials

     

    Index Fragmentation

    Apart from fragmentation at the file-system level and within the log file, it's also possible to have fragmentation within the data files, in the structures that store the table and index data. There are two basic types of fragmentation that can occur within a data file:

    • Fragmentation within individual data and index pages (sometimes called internal fragmentation)
    • Fragmentation within index or table structures consisting of pages (called logical scan fragmentation and extent scan fragmentation)

    Internal fragmentation is where there is a lot of empty space in a page. As Figure 1 shows, each page in a database is 8KB in size and has a 96-byte page header; as a result, a page can store roughly 8096 bytes of table or index data  Empty space can occur if each table or index record is more than half the size of a page, as then only a single record can be stored per-page.

    This can be very hard or impossible to correct, as it would require a table or index schema change, for instance by changing an index key to be something that doesn't cause random insertion points like a GUID does.

    Figure 1 The structure of a database page

     

    More commonly, internal fragmentation results from data modifications, such as inserts, updates, and deletes, which can leave empty space on a page. Mismanaged fill-factor can also contribute to fragmentation; see Books Online for more details. Depending on the table/index schema and the application's characteristics, this empty space may never be reused once it is created and can lead to ever-increasing amounts of unusable space in the database.

    Consider, for instance, a 100-million-row table with an average record size of 400 bytes. Over time, the application's data modification pattern leaves each page with an average of 2800 bytes of free space. The total space required by the table is about 59GB, calculated as 8096-2800 / 400 = 13 records per 8KB page, then dividing 100 million by 13 to get the number of pages. If the space wasn't being wasted, then 20 records would fit per page, bringing the total space required down to 38GB. That's a huge savings!

    Wasted space on data/index pages can therefore lead to needing more pages to hold the same amount of data. Not only does this take up more disk space, it also means that a query needs to issue more I/Os to read the same amount of data. And all these extra pages occupy more space in the data cache, thus taking up more server memory.

    Logical scan fragmentation is caused by an operation called a page split. This occurs when a record has to be inserted on a specific index page (according to the index key definition) but there is not enough space on the page to fit the data being inserted. The page is split in half and roughly 50 percent of the records moved to a newly allocated page. This new page is usually not physically contiguous with the old page and therefore is called fragmented. Extent scan fragmentation is similar in concept. Fragmentation within the table/index structures affects the ability of SQL Server to do efficient scans, whether over an entire table/index or bounded by a query WHERE clause (such as SELECT * FROM MyTable WHERE Column1 > 100 AND Column1 < 4000).

    Figure 2 shows newly created index pages with 100 percent fill-factor and no fragmentation—the pages are full and the physical order of the pages matches the logical order. Figure 3 shows the fragmentation that can occur after random inserts/updates/deletes.

     

    Figure 2 Newly created index pages with no fragmentation; pages 100% full(Click the image for a larger view)

     

     

    Figure 3 Index pages showing internal and logical scan fragmentation after random inserts, updates, and deletes

     

    Fragmentation can sometimes be prevented by changing the table/index schema, but as I mentioned above, this may be very difficult or impossible. If prevention is not an option, there are ways to remove fragmentation once it has occurred—in particular, by rebuilding or reorganizing an index.

    Rebuilding an index involves creating a new copy of the index—nicely compacted and as contiguous as possible—and then dropping the old, fragmented one. As SQL Server creates a new copy of the index before removing the old one, it requires free space in the data files approximately equivalent to the size of the index. In SQL Server 2000, rebuilding an index was always an offline operation. In SQL Server 2005 Enterprise Edition, however, index rebuilding can take place online, with a few restrictions. Reorganizing, on the other hand, uses an in-place algorithm to compact and defragment the index; it requires only 8KB of additional space to run—and it always runs online. In fact, in SQL Server 2000, I specifically wrote the index reorganize code as an online, space-efficient alternative to rebuilding an index.

    In SQL Server 2005, the commands to investigate are ALTER INDEX … REBUILD to rebuild indexes, and ALTER INDEX … REORGANIZE to reorganize them. This syntax replaces the SQL Server 2000 commands DBCC DBREINDEX and DBCC INDEXDEFRAG, respectively.

    There are many trade-offs between these methods, such as the amount of transaction logging generated, the amount of free space in the database required, and whether the process is interruptible without loss of work. You'll find a white paper that discusses these trade-offs, and more, at microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx. The paper is based on SQL Server 2000 but the concepts translate well to later versions.

    Some people simply choose to rebuild or reorganize all indexes every night or every week (using a maintenance plan option, for instance) rather than figuring out which indexes are fragmented and whether any benefit will come from removing the fragmentation. While that can be a good solution for an involuntary DBA who just wishes to put something in place with minimal effort, note that it can be a very poor choice for larger databases or systems where resources are at a premium.

    A more sophisticated approach involves using the DMV sys.dm_db_index_physical_stats (or DBCC SHOWCONTIG in SQL Server 2000) to periodically determine which indexes are fragmented, and then choosing whether and how to operate on those. The whitepaper also discusses using these more targeted choices. Additionally, you can see some example code to do this filtering in Example D of the Books Online entry for the DMV sys.dm_db_index_physical_stats in SQL Server 2005 (msdn.microsoft.com/library/ms188917) or Example E in the Books Online entry for DBCC SHOWCONTIG in SQL Server 2000 and later (at msdn.microsoft.com/library/aa258803).

    Whichever method you use, it is highly advisable to investigate and fix fragmentation on a regular basis.

     

    You can start following steps from here 

     

    alert_logo.jpg Have FULL Orion Database backup before you make any changes to the environment  .

    Backing Up, Restoring, and Migrating Your Orion Database

    http://www.solarwinds.com/resources/videos/backing-up-restoring-and-migrating-your-orion-database.html

     

    I have SQL Express installed and its showing 90% full how can i reduce my database size ?

    It is important to note that these databases have strict size limits, which may inhibit the amount of data that a Solarwinds application can store in them. These limits are as follows:

    Orion will alert you when approaching these limits .

    • Microsoft SQL Server 2005 Express edition has a database size limit to 4GB
    • Microsoft SQL Server 2008 Express edition has a database size limit to 4GB
    • Microsoft SQL Server 2008 R2 Express edition has a database size limit to 10GB
    • Microsoft SQL Server 2012 Express edition has a database size limit to 10GB
    • Microsoft SQL Server 2014 Express edition has a database size limit to 10GB

    Following below steps will help to reduce the size according to your environment.

    SQL is reporting NPM database is currently out of space - SolarWinds Worldwide, LLC. Help and Support

     

    How can i see which tables in my Orion database taking up most space ?

    Open Orion Database Manager > Start > All Programs > Solarwinds Orion > Advanced Feature > Database Manager > Click "Add default server

    Expand and select Orion Database > Right Click > Database Details > Click on Tables (Tab) > Sort by Data Size

    tablse.PNG

    You should be able to see now which tables are having very high DB Size within the Database

     

    I dont have DBA available , How can i enable de-fragmentation task using Orion Database-Manager quickly ?

    Please find the Solarwinds KB post below .

    Critical index fragmentation detected during database maintenance
    http://knowledgebase.solarwinds.com/kb/questions/3202/Critical+index+fragmentation+detected+during+database+maintenance

    How Orion will run the DB de-fragmentation once its enabled ?

    DB De - Fragmentation will be done through DB Maintenance that is been scheduled default @ 2:15 AM daily .

    Settings> Polling Settings

     

    Database Settings

    Archive TimeThe time of day to run the Orion database maintenance process.
    Auditing Trails Retention  daysAuditing Trails statistics will be deleted from the database after configured days.
    Detailed Statistics Retention  daysDetailed statistics will be summarized into hourly statistics after configured days.

    Can i run Database Maintenance manually now  ?

    Yes you can On Orion server (RDP ) Start > All programs > Solarwinds Orion > Advanced Settings > Database Maintenance > Start

    asdf.PNG

    How can i see the DB maintenance  & DB de-fragmentation progress ?

    You can see the DB Maintenance logs  swdebugMaintenance.log from following location .

    C:\ProgramData\Solarwinds\Logs\Orion  (Scroll to the bottom and you will be able to see the IndexDefragmenation logs and progress )

    Why Orion have not enabled the DB de-fragmenation option by default ?

    What impact will be on the database by enabling db-fragmentaion  ?

    This DB fragmentation normally done by the SQL DBA by running DB Maintenance Tasks on regular basis therefor we have the option disabled by default in order to avoid duplicate tasks.

    There are several things that should be done on a regular basis and one of these things is to manage database fragmentation.  Depending on the tables, queries and indexes that are being used fragmentation can cause performance issues as well as using unnecessary space in the database.

    I have very large Syslog & Traps table in the Orion database   ?

    Please see the post below

    Tips and Tricks for Managing Traps and Syslog in Orion NPM

     

    My manager wanted me to store large number of Syslogs / Traps / Logs for auditing    ?

    We actually have more powerful appliance for more demanding environment It easy to use logs for security, compliance, and troubleshooting storing for longer time period for auditing .

    LOG & EVENT MANAGER

    Log Management & Log Analyzer Software | SolarWinds

     

    Check your default database settings any change can cause database size increase and performance issues on SQL server. 

    We strongly  recommend you should never touch the below default Database Settings unless you are sure you do need to change.

    (If you do need to change the below settings for some reason to increase the retention . Please do consult Solarwinds support and consider your SQL deployment environment  as it could drastically increase the DB size immediately )

    Changing such settings could also create performance issues loading graphs and application such as loading the webpages / loading historical graphs and statistical data  Services connection to the Orion DB and off course high table fragmentation ,

    Settings > Polling Settings >

    So make sure you have the below settings to default as below

    def.PNG

     

    I have very large transaction file within Orion DB / My Orion database is filling up space very quickly what i should check ?

    Database transaction log is full - Recovery (Simple mode VS Full mode )

    Database transaction log is full - Recovery ( Simple Mode vs Full Mode )

     

    Shrinking Orion DB is strongly recommended after following above steps in order to get reclaim free unused space available within the database .

    How to Shrink the Database

    In large DB more then (40 Gig ) we strongly recommend to stop all Orion services

     

    - Open SQL server Management Studio.

    -(If you don't have a copy of this, you can download and install SQL Server Management Studio Express from Microsoft for free)

    - Connect to your SQL server

    sql.PNG

     

    -  Locate your Orion DB > Right Click > Properties > (Note Down the Size + Space Available )

    frsp.PNG

     

    - Now Right click the Orion database and choose All Tasks -> Shrink -> Files

    dbsh.PNG

    To Shrink the Main database file: (.mdf)

    - Ensure the File Type is set to Data, Filegroup "Primary".

    dbsh.PNG

    - For the Shrink Action, select "Reorganise pages before releasing unused space. Change the "Shrink File to" setting to just above the minimum setting shown on the right.

    - Click OK. This can take a while - usually a few mins per GB.

    To Shrink your Transaction Log File (.ldf)0

    - Ensure the File Type is set to Log, file group will not be an available option.

    - Please note, shrinking the log file will not be useful if the database is set to "Full Recovery" mode. This mode causes every transaction to be stored in the transaction log file, to allow the DB to recover to the last transaction if required. In comparison, Simple recovery will only allow recovery to the last good backup.

    - For the Shrink Action, select "Reorganise pages before releasing unused space. Change the "Shrink File to" setting to 0 MB.

    - Click OK. This will take a few seconds.

     

    To shrink your additional filegroups (applies only if using Netflow)

    - Ensure the File Type is set to Data, Filegroup "FG1".

    fg.PNG

    - For the Shrink Action, select "Reorganise pages before releasing unused space. Change the "Shrink File to" setting to just above the minimum setting shown on the right.

    - Click OK. This can take a while - usually a few mins per GB.

    - Repeat for each File group (FG1 to FG4)

    Once Done above .

    Right click the Orion database and choose All Tasks -> Shrink ->Database .

    Click OK

    dag.PNG

     

    Once Done

    Orion DB > Right Click > Properties > (Note Down the Size + Space Available )

    and compare the size after and before shrinking the DB .

    Dont forget to restart all Orion services and check your performance issues improvements.

     

     

    How can i am make my SQL server perform faster ?

    Check your stored DB files path .

    Orion DB > Right Click > Properties >Files

    dbfiles.PNG

    Storing DB files on C drive or even single disk array is strongly not recommended within production environment you can move the files to multiple disk arrays in order to divide the load between the disk arrays such as an example below .

    name

    fileid

    filename

    Oriondb

    1

    D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Oriondb.mdf

    Oriondb_log

    2

    D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Oriondb_log.ldf

    Oriondb_FG1

    3

    D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Oriondb_FG1.mdf

    Oriondb_FG2

    4

    D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Oriondb_FG2.mdf

    Oriondb_FG3

    5

    D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Oriondb_FG3.mdf

    Oriondb_FG4

    6

    D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Oriondb_FG4.mdf

     

    Recommended example below

    name

    fileid

    filename

    SolarWindsOrion

    1

    E:\data01\Data\SolarWindsOrion.mdf

    SolarWindsOrion_log

    2

    F:\Log\SolarWindsOrion_log.ldf

    SolarWindsOrion_FG1

    3

    D:\data01\Data\SolarWindsOrion_FG1.mdf

    SolarWindsOrion_FG2

    4

    D:\data01\Data\SolarWindsOrion_FG2.mdf

    SolarWindsOrion_FG3

    5

    E:\data01\Data\SolarWindsOrion_FG3.mdf

    SolarWindsOrion_FG4

    6

    E:\data01\Data\SolarWindsOrion_FG4.mdf

    SolarWindsOrion_log_2

    7

    T:\Log\SolarWindsOrion_log2.ldf

     

    What are  minimum SQL Server hardware recommendation for production environment ?

    3 GHz, dual core processor or better. This will work for all size licenses. Do not use
    less than a 2 GHz processor for any Orion SQL server.

    Best Practice use RAID 10
    2 x Raid 1 disk (Mirroring) For operating system
    2 x Raid 1 disk   (Mirroring) Place pagefile here and also can be used for applications and ad hoc Stuff
    4 x RAID 1 +0 Stripping and mirroring for database files (2 partitions 1 for Data File and 1 for Log File)

     

    Dedicated Physical Sever recommended

    http://www.solarwinds.com/documentation/orion/docs/orionnpmadministratorguide.pdf

    see page 62

    sql.PNG