Summary: This article describes the recommended maintenance plan for Qlik Sense databases using SQL Server Management Studio.
Solution 1: IMPORTANT: These routine tasks include SQL Server maintenance jobs that keep the data and the engine performing at satisfactory levels. The tasks also keep the data backed up to aid recovery if there is a disaster. This information is intended for use by database administrators (DBA) and Qlik Sense administrators only. Use the following procedure at your own risk. Qlik does not assume responsibility for any damage because of following these instructions.
Background: SQL Server uses the concept of Write Ahead Logging, where each data change operation is first written to the Transaction log (.LDF) from memory (buffer pool) and periodically flushed to the disk data file (.MDF) as part of the Checkpoint process. (Data change operations are Insert, Update, Delete, and other operations such as index rebuild and reorganize). Using a Transaction log, makes sure that if there is a disaster, you can restore the database to an earlier state with minimal data loss. Examples of disasters include hardware failure or human error.
Full Recovery Model: After backing up the Transaction using the Full Recovery model, SQL Server flags backed-up records as Inactive and truncates the log. In this way, any new operations that are logged to the Transaction log, can reuse that space by overwriting the inactive entries. This design helps to prevent the log size from growing.
If no periodic backup of the Transaction log is done, the size of the Transaction log continues to grow until it consumes all available disk space. So, if your Qlik Sense database is configured to use the Full Recovery model, it is important to perform regular Transaction log backups to keep its size in check.
Simple Recovery Model: In the Simple Recovery Model, after the Checkpoint occurs, and the records are flushed to disk, SQL Server truncates the Transaction log. This action frees up the space internally in the Transaction log file. The Transaction log does not grow as long as there is enough space available for the current open transactions.
In the Simple Recovery Model, the concept of backing up the Transaction log is not used, because you only take a regular full backup of the Qlik Sense database. If there is a disaster, you can recover only to the last full backup. All changes that occurred after the last full backup are lost.
The Simple Recovery Model is an acceptable solution for most enterprise customers, because the data lost in a disaster is usually event data since the last full backup. The Full Recovery Model includes the administrative overhead of backing up the Transaction log for your Qlik Sense database periodically.
For this reason, the Simple Recovery Model for the Qlik Sense database is recommended. But, if you choose to use the Full Recovery Model, make sure that you have a good backup plan for both your Qlik Sense database and Transaction log. A discussion of the backup plan for SQL Server databases is beyond the scope of this article. For more details, see SQL Server Books Online at: http://msdn.microsoft.com/en-us/library/ms130214.aspx.
NOTE: If you have multiple databases with different recovery models, you can create separate database maintenance plans for each recovery model. In this way, you can include a step to back up your Transaction logs only on the databases that do not use the Simple Recovery Model.
Set the Qlik Sense database Recovery Model to Simple.
To verify that the recovery model is set to Simple, make the following changes in the SQL Server Management Studio:
1. Click All Programs, Microsoft SQL Server <version>, SQL Server Management Studio. 2. Select the Authentication type (Windows or SQL Server) and click Connect to log on to the SQL Server instance hosting the Qlik Sense database. 3. In the Object Explorer window, expand the Databases node. 4. Right-click the QlikSense_<server name> entry. 5. Select Properties. The Database Properties window opens. 6. Click Options in the Select a Page area on the left pane. 7. Click the drop-down arrow on the right of the Recovery model and select Simple. 8. Click OK.
Shrink Database and why it is NOT recommended: Avoid shrinking the Qlik Sense database as much as possible. Shrinking a production SQL Server database would introduce logical fragmentation. Physical order of the pages in the leaf level of an Index is not the same as the logical order of the pages. Effectively, the disk head must go back and forth in reading the pages. This action results in more Input and Output (I/O) operations and degrades performance.
When you shrink the data file, pages at the end of the data file are moved to the beginning of the file. This action disregards any potential fragmentation that is introduced in this process.
If the Qlik Sense database grows in size after you delete events and shrink the database, space is needed for events sent by the agent. Shrinking the data file after deleting the tables would only cause the file to grow back, in addition to causing fragmentation. If space is a concern, consider filtering the non-essential events using Database Filtering.
NOTE: You can consider shrinking the data file after performing many delete operations. Such as purging old events, if you know that you do not need that space again for storing new events. Otherwise, rebuild the indexes periodically and filter the unnecessary contents using Filtering to avoid capturing unwanted data in the first place.
IMPORTANT: You must have a proper database maintenance plan configured so that the Qlik Sense database performance is healthy.
Create a maintenance plan for the Qlik Sense database in SQL Server:
1. Click All Programs, Microsoft SQL Server <version>, SQL Server Management Studio. 2. Select the Authentication type (Windows or SQL Server) and click Connect to log on to the SQL Server instance hosting the Qlik Sense database. 3. Expand Management in the Server Object Explorer window. 4. Right-click Maintenance Plans and select Maintenance plan wizard. 5. Type a name for the Maintenance Plan (for example, Qlik Sense Database Maintenance Plans). 6. Change the schedule. Click Change and then click Next.
NOTE: Schedule the task so it runs during nonpeak times, for example, a recurring task every Saturday at 11 p.m. or any time in weekends.
7. Select the following options under Maintenance tasks and click Next:
o Check Database Integrity o Rebuild Index o Back Up Database (Full)
8. Define the order for the tasks to execute as follows and click Next:
o Check Database Integrity o Back Up Database (Full) o Rebuild Index
NOTE: These tasks can be interchangeable in terms of the order in which they execute. Create a database backup before the index rebuild process. This configuration makes sure that there is a working backup copy of the database if there is an issue during the rebuild process.
9. Define a Check Database Integrity task:
a. Select the Qlik Sense database QlikSense_<servername>. b. Select Include indexes. c. Click Next.
10. Define a Backup Database (Full) task:
a. Select the Qlik Sense database QlikSense_<servername>. b. Type the backup path location. c. In the Set backup compression drop-down, select Compress backup. d. Click Next.
11. Define a Rebuild Index task:
a. Select the Qlik Sense database QlikSense_<servername>. b. Click Object: Tables and Views. c. Click Change free space per page percentage to: 10%. d. Under Advanced options, select Keep index online while re-indexing.
NOTE: All editions of SQL Server do not support online Index rebuild. For more details about which editions support the Online Index rebuild, see SQL Server Books Online documentation.
For index types that do not support online index rebuilds, select the option Rebuild Indexes offline. Click Next.
An Index Rebuild task would cause the statistics to be updated as part of the rebuild. Effectively with full scan, so an Update Statistics task is not needed after a Rebuild Index.
12. Define Select Report Options:
a. Select Write a report to a text file and type the wanted folder location. b. Click Next. c. Click Finish.
NOTE: Monitor the maintenance task and avoid running the task during production hours for a large Qlik Sense database.
Solution 2: IMPORTANT: If you have a large production database, either use a custom index rebuild or reorganize script. Instead of the Index Reorganize and Rebuild Maintenance plan task.
Custom tasks allow more flexibility about which objects need to be reorganized and rebuilt. Instead of rebuilding every object regardless of the fragmentation level.
According to SQL Server Books Online: • If fragmentation is between 20% and 30%, reorganize the index. • If fragmentation is > 30%, rebuild the index. You can determine the fragmentation level of an index by querying the sys.dm_db_index_physical_stats dynamic management view (DMV) entry. SQL Server Books Online provides a sample SQL Script that provides a fragmentation ratio as listed above. See the topic on sys.dm_db_index_physical_stats in the SQL Server Books Online documentation link below:
NOTE: Example D in the online documentation provides the sample code.
It is important that you update the statistics after an Index Reorganize command. Unlike Index Rebuild, statistics are not automatically updated as part of an Index Reorganize.
You can further customize the script to include the option to perform an Online Rebuild of Indexes. Online Rebuild provides more concurrency during the Index Rebuild and is resource-intensive. This feature is not available in all editions of SQL Server. See the Books Online documentation on which editions support the Online Rebuild of Indexes feature.
