SQL Server Configuration

The first group of considerations are for the overall SQL Server instance. SQL Server should be tuned with knowledge of the underlying hardware and the other software that may be running on the system.

Number of temporary data files

The number of temporary data files should be adjusted to match the hardware. As a general rule of thumb, there should be one temporary file per CPU core.

To add temporary data files using SQL Server Management Studio (SSMS), follow these steps:

  1. On the Object Explorer pane, expand Databases > System Databases.

  2. Right-click tempdb, and then select Properties. The Database Properties – tempdb window is displayed.

  3. On the left pane of the Database Properties – tempdb window, select Files.
  4. Select Add to add additional database files.
  5. Enter the Logical Name, File Type, Filegroup, Initial Size (MB), and Autogrowth / Maxsize details.
  6. Select OK to save the changes.

Cost threshold for parallelism

The cost threshold for parallelism should be adjusted to match the hardware. This value determines when SQL Server switches from a serial execution plan to a parallel execution plan and indicates the estimated number of seconds a serial plan is expected to take. This should be configured based on the hardware and the type of queries that will be performed.

To modify the Cost Threshold for Parallelism value using SSMS, follow these steps:

  1. On the Object Explorer pane, right-click the database instance, and then select Properties. The Server Properties window is displayed.
  2. Select Advanced to view the Parallelism settings, expanding it if necessary.
  3. Modify the Cost Threshold for Parallelism value.
  4. Select OK to save the changes.

Maximum degree of parallelism

The maximum degree of parallelism should be adjusted to match the hardware. As a general rule of thumb, this should be set to half the number of CPU cores. If the system has eight CPU cores, this should be set to four.

To adjust the maximum degree of parallelism using SSMS, follow these steps:

  1. On the Object Explorer pane, right-click the database instance.
  2. Select Properties. The Server Properties window is displayed.
  3. Select the Advanced tab and view the Parallelism settings, expanding it if necessary.
  4. Modify the Max Degree of Parallelism value.
  5. Select OK to save the changes.

Database size

To avoid issues related to halted database operations due to the mismatch in database size and file autogrowth settings, NCR recommends increasing the size of the database or sizing the database appropriately from the beginning.

Important

Database administrators cannot add or remove a file while a BACKUP statement is running.

To expand the size of the database, do one of the following.

  • Increase the size of an existing database or log file.
  • Add a new file to the database. For more information, consult with your database administrator.

Manually increasing the size of an existing database

To increase the size of an existing database in SQL Server using SSMS, follow these steps.

  1. On the Object Explorer pane, right-click the database instance.
  2. Select Properties. The Database Properties window is displayed.
  3. On the left pane of the Database Properties – tempdb window, select Files.
  4. Increase the Initial Size (MB) value.
    Note

    Database administrators must increase the size of the database by at least one megabyte. For more information, consult with an NCR Representative.

  5. Select OK to save the changes.

Database auto-growth settings

When creating or maintaining a SQL Server database, the auto-growth settings for each data and log file must be defined properly. Proper auto-growth settings improve database performance and disk space utilization. NCR recommends applying the following best practices for defining auto-growth settings:

  1. Pre-size the databases. Make sure to set the appropriate auto-growth settings of each database based on their current use or estimated load across the enterprise.
  2. When manually performing auto-growth events, apply the following practices:
    • Perform manual auto-growth events to a minimum. Auto-grow data files to a size that will accommodate several months of growth.
    • Auto-grow data files during non-peak hours. When an auto-growth event occurs, the length of time the process takes to complete affects the performance and availability of the database. Lengthy auto-growth events can cause Enterprise servers or POS terminals communicating with the database to fail in retrieving data or completing ongoing store transactions.
    • Regularly monitor the growth of databases, and re-establish auto-growth settings as necessary; for example, when a database growth profile changes. Use alerts or monitoring programs to monitor file sizes, and grow files proactively.
  3. Only defragment a database file system when its auto-growth events become beyond manageable.

Index fragmentation

The first step in choosing which defragmentation method to use is to determine the degree of fragmentation. The greater the fragmentation, the slower queries will be. Users can check the fragmentation of a specific index, all indexes on a table or indexed view, all indexes in a database, or all indexes in all databases.

Database tuning

Depending on the degree of fragmentation in the database indexes, NCR recommends the following database tuning methods:

  • Re-organizing database indexes—applies to database indexes with minimum fragmentation.
  • Rebuilding database indexes—applies to database indexes with high levels of fragmentation. It is best to rebuild database indexes as part of a normal maintenance plan.
    Note

    The current recommended index fill factor is 80. However, a different fill factor value may be recommended in the future. It is best to monitor these indexes over time and adjust the fill factor based on usage. For more information, consult with an NCR Representative.

For more information on database tuning methods and tips, consult with your database administrator.