#1 Global Leader in Data Resilience
#1 Global Leader in Data Resilience
BACK TO KB LIST

Optimizing and Maintaining SQL Server Instance Hosting Veeam Service Provider Console Configuration Database

KB ID: 4670
Product: Veeam Service Provider Console | 8.0 | 8.1
Published: 2024-10-11
Last Modified: 2024-11-14
mailbox
Get weekly article updates
By subscribing, you are agreeing to have your personal information managed in accordance with the terms of Veeam's Privacy Notice.

Cheers for trusting us with the spot in your mailbox!

Now you’re less likely to miss what’s been brewing in our knowledge base with this weekly digest

error icon

Oops! Something went wrong.

Please, try again later.

Purpose

This article was produced in coordination with the Veeam R&D team to provide Service Providers with a comprehensive list of the most common performance improvements and maintenance actions relating to the underlying Microsoft SQL Server instance that hosts the Veeam Service Provider Console configuration database.

The information, advice, and scripts provided in this article are provided as a courtesy. Assistance with implementing the configuration advice provided in this document is outside the scope of Veeam Support.
If you have feedback regarding the content of this article, please use the Article Feedback form.

Solution

This guide has been broken down into expandable sections below.
System Requirements and Sizing Guidelines
Check if your MS SQL Server follows the VSPC Sizing Guidelines recommendations:
SQL Server Instance Memory Configuration Options

Memory utilization for the SQL Server Database Engine is bounded by a pair of configuration settings, min server memory (MB) and max server memory (MB). Over time and under normal circumstances, SQL Server will attempt to claim memory up to the limit set by max server memory (MB).

Use max server memory (MB) to guarantee the OS and other applications don't experience detrimental memory pressure from SQL Server. Before you set the max server memory (MB) configuration, monitor the overall memory consumption of the server hosting the SQL Server instance during regular operation to determine memory availability and requirements.

Use SQL Server Management Studio to set server memory options:

  1. In Object Explorer, right-click a server and select Properties.
Properties
  1. Select the Memory page of the Server Properties window. The current values of Minimum server memory and Maximum server memory are displayed.
Memory Settings
  1. In Server memory options, enter desired numbers for Minimum server memory and Maximum server memory. For recommendations, see min server memory (MB) and max server memory (MB).
SQL Server Standard and Express Edition CPU Limitations

When utilizing SQL Server Standard Edition or Express Edition, be mindful of the compute capacity limitations when configuring the CPU socket and core configuration of the Virtual Machine (VM) hosting Microsoft SQL Server.

Microsoft SQL Server - Compute capacity limits by edition

  • SQL Server Standard Edition is "Limited to lesser of 4 sockets or 24 cores"
  • SQL Server Express Edition is "Limited to lesser of 1 socket or 4 cores"

Example:

If you intend to assign a total of 24 vCPUs to a VM and configure it as 24 sockets with 1 core each, SQL Server Standard edition would be able to utilize at most 4 cores. If the configuration were 4 sockets with 6 cores each, then SQL Server Standard Edition would be able to access all 24 cores.

Managing SQL Server Data and Log Files

To manage data and log files, ensure the following:

  • Data and log files are on separate drives from each other and from all other files. The drive containing the data should contain only data (no logs), and the drive containing the log files should contain only log files (no data).
  • Using SSD for better I/O performance.
  • Auto-growth is correctly configured (10% by default).
  • Instant file initialization is configured.
  • Auto-shrink is not enabled and shrink is not part of any maintenance plans.

    If auto-shrink is enabled, disable it by using the following command:
ALTER DATABASE VSPC SET AUTO_SHRINK OFF;
Monitoring Physical Disks Performance

Performance Monitor is a built-in Windows Tool for monitoring all aspects of Windows and hosted applications such as SQL Server. A Data Collector Set is used to organize and schedule performance counter data as a single collection to be analyzed later.

To create a Data Collector Set for your physical disks:

  1. Launch the Performance Monitor (perfmon).
Run
perfmon
  1. In the Performance Monitor navigation pane, expand Data Collector Sets.
  2. Right-click User Defined, point to New, and click Data Collector Set.
New Data Collector
  1. Enter a name for the Data Collector Set and select Create manually, then click Next.
Set name and select manually create
  1. Select Create data logs, check the Performance counter box, and click Next.
Create logs and performance counters.
  1.  Click Add to open the Add Counters dialog box.
Add Counters
  1. Find and click the category entry PhysicalDisk, the Instances of selected object section will update.
Select Physical disk
  1. Double click <All instance> to add the PhsysicalDisk counters to the Added counters list, then click OK.
Add counters to list
  1. Click Finish to exit and save the current configuration.
Finish and save
  1. Start the created data collector set by right-clicking on it and selecting Start.
Start data set
  1. After a while, stop the data collection by clicking the Stop the Data Collector Set button.
Stop data collection
Results will be shown in the Reports folder in the navigation pane. It is important for the Avg. Disk Queue Length counter to have a value less than 1. Otherwise, it could mean that the system has poor disk performance.
Review
Reviewing SQL Server Logs

There are several areas where SQL Server logs information about processes that are occurring as well as errors that occur.  The most used is probably the SQL Server Error Log.  This error log provides startup information, integrity check information, backup information, and other information, as well as any SQL Server errors that occur.

To get to the MS SQL Server Logs, follow these steps:

  1. In Object Explorer, expand the Management node and the SQL Server Logs node.
  2. Right-click on the Current log entry and select View SQL Server Log.
View Logs
  1. In the Log File Viewer, you can analyze and export logs.
Monitoring VSPC Database Staging Tables

Almost all of the data collected by VSPC goes through Staging tables. It is important that these tables maintain as near to zero records as possible. If the number of records continues to increase, it could indicate that there is a performance issue.

To check how many records Staging tables have:

  1. In Object Explorer, right-click the VSPC database and go to the Reports -> Standard Reports, select the Disk Usage by Top Tables report.
disk usage table
  1. Check how many records VeeamBP.Staging and VeeamOD.Staging tables have.
staging tables
If either table has more than 5 million records, it could indicate performance issues. In such a situation, wait a couple of hours a generate the Disk Usage by Top Tables report again, and if the number of records hasn’t gone down – contact Veeam Support.
Database Shrinking Considerations

To view the current amount of free (unallocated) space in the database, see Display Data and Log Space Information for a Database.

Shrink Considerations and Limitations

Consider the following information when you plan to shrink a database:

  • A shrink operation is most effective after an operation that creates a large amount of unused storage space, such as a large DELETE statement, truncate table, or a drop table operation.
  • Most databases require some free space to be available for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the free space is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation. Repeatedly shrinking the database causes the SQL instance to trigger autogrow events to grow the database file(s), which hinders performance.
  • A shrink operation doesn't preserve the fragmentation state of indexes in the database and generally increases fragmentation to a degree. This is another reason not to shrink the database repeatedly.
  • Unless you have a specific requirement, do not set the AUTO_SHRINK database option to ON.

Shrink operations in progress can block other queries on the database and can be blocked by queries already in progress.

Database Shrinking Documentation
Fragmentation Impact of Shrinking Database

Data that is moved to shrink a file can be scattered to any available location in the file. This causes index fragmentation and can slow the performance of queries that search a range of the index. To eliminate the fragmentation, consider rebuilding the indexes on the file after shrinking.

Rebuilding Table Indexes

Index and data pages can experience internal fragmentation. Index and table structures can experience logical scan and extent scan fragmentation. Periodically check index fragmentation of the VSPC database. The most sensitive tables for index fragmentation are:

  • VeeamBP. ObjectEntityPropertyValue
  • VeeamOD. ObjectEntityPropertyValue
  • VeeamBP.ObjectEntity
  • VeeamOD.ObjectEntity

Check Index Fragmentation using SQL Server Management Studio:

  1. Right-click the VSPC database and choose Reports -> Standard Reports.
  2. Select the Index Physical Statistics report.

You’ll see table names, indexes, and operation recommendations in that report. Follow the recommendations and rebuild\reorganize indexes.

Rebuild vs Reorganize Indexes

Please note that rebuilding indexes is a heavy task that can take a lot of time. Additionally, online indexing can only be done with the Enterprise Edition of Microsoft SQL Server. In other editions of the SQL Server, only offline indexing is available, meaning tables having their indexes rebuilt will be locked during the rebuild operation. Reorganizing the index, on the other hand, is less heavy an operation and can be done online with all editions. Generally speaking, reorganizing indexes is preferable to rebuilding when possible.

How to Rebuild Indexes (Offline)
  1. In Object Explorer, select the plus sign next to the VSPC database.
  2.  Expand the Tables folder.
  3.  Select the plus sign to expand the table whose indexes you want to rebuild.
  4.  Expand the Indexes folder.
  5.  Right-click the index that you want to rebuild and select Rebuild.
  6. In the Rebuild Indexes dialog box, verify that the correct index is in the Indexes to be rebuilt list and click OK.
How to Rebuild Indexes (Online)

For information about rebuilding Indexes (Online) with SQL Server Enterprise Edition, please review Microsoft documentation:
SQL Server - Perform index operations online - Use SQL Server Management Studio

How to Reorganize Indexes
  1. In Object Explorer, select the plus sign next to the VSPC database.
  2.  Expand the Tables folder.
  3.  Select the plus sign to expand the table whose indexes you want to reorganize.
  4.  Expand the Indexes folder.
  5. Right-click the index that you want to reorganize and select Reorganize.
  6. In the Reorganize Indexes dialog box, verify that the correct index is in the Indexes to be reorganized list and click OK.
Updating Table and Index Statistics
Ensure that the statistics describing the distribution of data values in a table or index are accurate and up-to-date. Updated statistics provide the query optimizer with the best possible information for generating query plans. Statistics are updated when they are created or modified, and they are also periodically updated by SQL Server unless the administrator has disabled automatic statistics updates. Auto Update Statistics doesn’t work for filtered indexes; those indexes need to be updated manually.
Check if Auto Update Statistics is ON
  1. Right-click the VSPC database and choose Properties.
  2. In the navigation menu on the left, select Options.
  3. Check if Auto Update Statistics has the True value and set it to the True if it’s not.
  4. Click OK to close the Database Properties.
Auto Update Statistics
Check VSPC Database Statistics Relevancy

You can check how irrelative the statistics are in your VSPC DB using the following query:

set transaction isolation level read uncommitted;

select quotename( sh.[name] ) + N'.' + quotename( t.[name] ) as [object_name]
     , s.stats_id
     , quotename( s.[name] ) as stat_name
     , s.auto_created
     , s.user_created
     , s.no_recompute
     , replace( sc.stat_columns, N'] [', N'], [' ) as stat_columns
     , cast( sp.last_updated as datetime2( 3 ) ) as last_updated
     , sp.[rows]
     , sp.rows_sampled
     , sp.steps
     , sp.modification_counter
     , ltrim( format( cast( sp.modification_counter as decimal( 20, 2 ) ) / cast( sp.[rows] as decimal( 20, 2 ) ), N'# ##0.00 %' ) ) as irrelevance
     , s.filter_definition    
  from sys.schemas sh
    inner join
      sys.tables t on t.[schema_id] = sh.[schema_id]
    inner join
      sys.stats s on s.[object_id] = t.[object_id]
    outer apply
      ( select quotename( c.[name] ) as [data()]
          from sys.stats_columns sc 
            inner join
              sys.columns c on c.[object_id] = sc.[object_id] and c.column_id = sc.column_id
          where sc.[object_id] = s.[object_id] 
            and sc.stats_id = s.stats_id
          order by sc.stats_column_id
          for xml path( '' ) ) sc( stat_columns )
    outer apply
      sys.dm_db_stats_properties( s.[object_id], s.stats_id ) as sp
  order by [object_name], s.stats_id;
go
How to Update a Statistics Object (using SQL Server Management Studio)
  1. In Object Explorer, select the plus sign next to the VSPC database.
  2.  Expand the Tables folder.
  3.  Select the plus sign to expand the table whose statistic you want to update.
  4.  Expand the Statistics folder.
  5. Right-click the statistics object you wish to update and select Properties.
  6. In the Statistics Properties dialog box, select the check box labeled "Update statistics for these columns" and click OK.
Update Stats

How to Update a Single Statistics Object using Transact-SQL

  1. Connect to the SQL Server instance.
  2. Click New Query.
  3. Copy and paste the following example into the query window and click Execute.
    Note: The statistic in this example can be changed to any statistic you'd like to update.
-- Use the name of your VSPC database instead of the VSPC_DB_NAME
USE VSPC_DB_NAME;
GO
-- The following example updates the statistics for the INDX_ObjectEntityPropertyValue_IsDeleted_DateTime index of the VeeamBP.ObjectEntityPropertyValue table. 
UPDATE STATISTICS VeeamBP.ObjectEntityPropertyValue
INDX_ObjectEntityPropertyValue_IsDeleted_DateTime;
GO

How to Update all Statistics for a Specific Table using Transact-SQL

  1. Connect to the SQL Server instance.
  2. Click New Query.
  3. Copy and paste the following example into the query window and click Execute.
    Note: The table in this example can be changed to any table whose statistics you'd like to update.
-- Use the name of your VSPC database instead of the VSPC_DB_NAME
USE VSPC_DB_NAME;   
GO  
-- The following example updates the statistics for all indexes on the VeeamBP.ObjectEntityPropertyValue table.
UPDATE STATISTICS VeeamBP.ObjectEntityPropertyValue;   
GO

How to Update all Statistics in a Specific Database using Transact-SQL

  1. Connect to the SQL Server instance.
  2. Click New Query.
  3. Copy and paste the following example into the query window and click Execute.
    Note: The statistic in this example can be changed to any statistic you'd like to update.
-- Use the name of your VSPC database instead of the VSPC_DB_NAME
USE VSPC_DB_NAME;   
GO  
-- The following example updates the statistics for all tables in the database.  
EXEC sp_updatestats;
To submit feedback regarding this article, please click this link: Send Article Feedback
To report a typo on this page, highlight the typo with your mouse and press CTRL + Enter.

Spelling error in text

This site is protected by hCaptcha and its Privacy Policy and Terms of Service apply except as noted in our Privacy Policy.
Thank you!

Thank you!

Your feedback has been received and will be reviewed.

Oops! Something went wrong.

Please, try again later.

You have selected too large block!

Please try select less.

KB Feedback/Suggestion

This form is only for KB Feedback/Suggestions, if you need help with the software open a support case
By submitting, you are agreeing to have your personal information managed in accordance with the terms of Veeam's Privacy Notice.
This site is protected by hCaptcha and its Privacy Policy and Terms of Service apply except as noted in our Privacy Policy.
Verify your email to continue your product download
We've sent a verification code to:
  • Incorrect verification code. Please try again.
An email with a verification code was just sent to
Didn't receive the code? Click to resend in sec
Didn't receive the code? Click to resend
Start using Veeam:
Download the product
&
Activate the license key
Thank you!

Thank you!

Your feedback has been received and will be reviewed.

error icon

Oops! Something went wrong.

Please, try again later.