Jos olet hallinnoidun ylläpidon asiakas, tämä sisältö ei koske sinua.

Using SQL Server Management Studio

For the most part, MSSQL Server is a fairly independent, self-tuning system that adjusts to the workloads of the user community. To further improve performance, modify the following values using the SQL Server Management Studio:

  • Max Degree of Parallelism: The Max Degree of Parallelism (MAXDOP) is a configuration indicating how the SQL Server optimizer will use the CPUs. This is a server-wide configuration that by default uses all of the CPUs to have the available portions of the query executed in parallel. The default MAXDOP configuration for SQL Server is 0; however, Blackboard recommends lowering this setting to lower than n - 1, where n is equal to the number of CPUs. In the Blackboard Performance Engineering Laboratory, the MAXDOP is often to set to a value of 1. Rarely has there been a performance difference by setting the value from 1 to any number higher than 1. Query parallelism can become a CPU resource saturation problem when the database optimizer incorrectly distributes the query across multiple CPUs. To determine whether an unlimited MAXDOP setting is causing high synthetic CPU utilization, look for the wait event CXPACKET (Wait Event Category of Parallelism). The MAXDOP does not work in some versions of SQL Server 2008. To learn more about how to avoid this issue, see http://support.microsoft.com/kb/970399.
  • Address Windowing Extensions (AWE): AWE allows 32-bit operating systems to access large amounts of memory. It is exposed by the operating system and is implemented with slight differences depending on the operating system version. To run locked pages in memory, the SQL Server operating system user account must have administrative permissions. After enabling and configuring AWE from the SQL Server Administration console, SQL Server will now be able to take advantage of more than 2GB of memory. To learn more, see http://msdn.microsoft.com/en-us/library/ms187499.aspx.

    In Microsoft SQL Server 2008 R2, the configuration option, awe enabled, is deprecated. Learn more.

  • Maximum Worker Threads: Maximum Worker Threads are SQL Server threads that execute user or batch requests. A worker is bound to a batch until it completes, so the maximum number of workers limits the number of batches that execute concurrently. Blackboard recommends using the default setting of 0 because it will spawn the appropriate number of Worker Threads based on need. To learn more, see http://msdn.microsoft.com/en-us/library/ms187024.aspx.
  • Fiber Mode: Fiber mode, also known as lightweight pooling, should not be used as it can cause certain features to fail. To learn more, see http://msdn.microsoft.com/en-us/library/aa175385(SQL.80).aspx and http://msdn.microsoft.com/en-us/library/ms189267.aspx.

Shrinking the database log in MSSQL 2008

Prior versions of Microsoft SQL used the backup log dbname with truncate_only. This is no longer supported in Microsoft SQL 2008. If attempting to use this method, you will receive the error message of " 'Truncate_Only' is not a recognized BACKUP option".

The transaction log is automatically truncated when the database is using the simple recovery model. If you must remove the log backup chain from a database, switch to the simple recovery model. If you have any script which has the TRUNCATE_ONLY option then the script must be rewritten.

Shrink the database log using the simple recovery model in MSSQL 2008

When using the simple recovery model, the backup intervals should be short enough to prevent the loss of significant amounts of data. However, the intervals should be long enough to keep the backup overhead from affecting production work. Including differential backups in the backup strategy can help reduce the overhead. See http://msdn.microsoft.com/en-us/library/ms189275.aspx.

  1. Change the Recovery Model to Simple
  2. Alter database BB_BB60 SET Recovery simple
  3. Shrink the file using DBCC ShrinkFile

    USE BB_BB60

    GO

    DBCC SHRINKFILE (N'BBLEARN_log' , 1)

  4. Verify the recovery mode with the following command:

    select name,recovery_model_desc from sys.databases

The transaction log size should be determined based on disk usage, and activity usage. With this in mind, it is not always necessary to shrink the Database log.

Is MSSQL 2008 Express Edition supported?

No, MSSQL Express Edition is not supported. Blackboard Learn requires the MSSQL Agent for database background jobs. The MSSQL Agent is not enabled in the Express Edition. The following versions are supported:

  • Enterprise
  • Developer
  • Standard