Prepare an operational performance baseline
In this we will learn about operational performance baseline. However, to determine whether your SQL Server system is performing optimally, take performance measurements at regular intervals over time. This can be done even when no problems occur, to establish a server performance baseline.
The following areas affect the performance of SQL Server:
- Firstly, system resources (hardware)
- Secondly, network architecture
- Thirdly, the operating system
- Then, database applications
- Lastly, client applications
At a minimum, use baseline measurements to determine:
- Firstly, peak and off-peak hours of operation.
- Secondly, production-query or batch-command response times.
- Lastly, database backup and restore completion times.
After you establish a server performance baseline, compare the baseline statistics to current server performance. Numbers far above or far below your baseline are candidates for further investigation. They may indicate areas in need of tuning or reconfiguration.
sp_configure (Transact-SQL)
Syntax
syntaxsql
— Syntax for SQL Server
sp_configure [ [ @configname = ] ‘option_name’
[ , [ @configvalue = ] ‘value’ ] ]
— Syntax for Parallel Data Warehouse
— List all of the configuration options
sp_configure
[;]
— Configure Hadoop connectivity
sp_configure [ @configname= ] ‘hadoop connectivity’,
[ @configvalue = ] { 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 }
[;]RECONFIGURE
[;]
Arguments
[ @configname = ] ‘option_name’ Is the name of a configuration option. option_name is varchar(35), with a default of NULL. Moreover, the SQL Server Database Engine recognizes any unique string that is part of the configuration name. If not specified, the complete list of options is returned. [ @configvalue = ] ‘value’ Is the new configuration setting. value is int, with a default of NULL. However, the maximum value depends on the individual option.SQL Server Big Data Clusters
Certain operations, including configuring server (instance level) settings, or manually adding a database to an availability group, require a connection to the SQL Server Instance. However, operations like sp_configure, RESTORE DATABASE, or any DDL command in a database belonging to an availability group require a connection to the SQL Server instance. By default, a big data cluster does not include an endpoint that enables a connection to the instance. You must expose this endpoint manually.
Advanced Options
Some configuration options, such as affinity mask and recovery interval, are designated as advanced options. By default, these options are not available for viewing and changing. However, to make them available, set the Show Advanced Options configuration option to 1.
Permissions
Execute permissions on sp_configure with no parameters or with only the first parameter are granted to all users by default. Further, to execute sp_configure with both parameters to change a configuration option or to run the RECONFIGURE statement. The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.
Reference: Microsoft Documentation