492
Part V: Enterprise Data Management
To view information about the connections established to this instance of SQL Server, query the sys.dm_exec_
connections dynamic management view. This information includes statistics about each of the databases, con-
nections by both local and remote users, and details for each connection.Priority Boost
Different processes in Windows operate at different priority levels, ranging from 0 to 31.
The highest priorities are executed fi rst and are reserved for the operating-system pro-
cesses. Typically, Windows scheduling priority-level settings for applications are 4 (low),
7 (normal), 13 (high), and 24 (real time). By default, SQL Server installs with a Windows
scheduling priority level of 7. The default value of priority boost gives SQL Server
enough CPU resources without adversely affecting other applications.Best Practice
In almost all cases, it is recommended to leave the priority boost option to the default value of 0.
Raising the priority of SQL Server may drain essential operating system and networking functions and
thereby result in a poorly performing SQL Server; in some cases it may even result in a SQL Server
shutdown. If you do change the priorit y boost from 0 to 1, then be sure to test it thoroughly and evalu-
ate all other performance tuning opportunities fi rst.If you still insist on changing the priority boost confi guration option, you can either
use Management Studio or T-SQL-code. In Management Studio, priority boost is set to
1 by checking the Boost SQL Server priority check box in the Server Properties Processor
tab (refer to Figure 19-7 earlier in this chapter).Using T-SQL-code, the following command can set the priority boost option to 1. This
sets the Windows scheduling priority level to 13 (high).EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'priority boost', 1;
RECONFIGURE;SQL Server service must be restarted for the priority boost option to take effect.Lightweight Pooling
You can use the lightweight pooling option for servers with multiprocessing servers
to reduce the overhead of frequently switching processes among the CPUs.c19.indd 492c19.indd 492 7/30/2012 5:42:56 PM7/30/2012 5:42:56 PM
http://www.it-ebooks.info