Tham khảo tài liệu 'hướng dẫn học microsoft sql server 2008 part 95', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả | Part VI Enterprise Data Management Depending on the number of connections and the percentage of time those connections are active versus idle making the number of worker threads less than the number of connections can force connection pooling conserve memory and improve performance. In Management Studio the max worker threads option is set by typing or selecting a value in the Maximum Worker Threads box in the Server Properties Processor tab refer to Figure 39-6 . From code the maximum number of worker threads is set by means of the sp_configure stored procedure and the max worker threads option. For example the following code sets the max worker threads to 128 EXEC sp_configure show advanced options 1 RECONFIGURE EXEC sp_configure max worker threads 128 RECONFIGURE The SQL Server service must be restarted for the max worker threads setting to take effect. Best Practice On SQL Server 2005 and SQL Server 2008 the default value of 0 for the max worker threads property provides the best performance of SQL Server. This default value indicates that SQL Server will automatically determine the correct number of active worker threads based on user requests. If you do need to change the default value then I recommend not setting the max worker threads option to a small value which might prevent enough threads from servicing incoming client requests in a timely manner and could lead to thread starvation. Conversely don t set the max worker threads option to a large value which wastes memory because each active thread consumes 512KB on 32-bit servers and up to 4MB on 64-bit servers. To view information about the connections established to an instance of SQL Server query the dynamic management view. This information includes statistics about each of the databases connections by both local and remote users and details about each connection. Priority boost Different processes in Windows operate at different priority levels ranging from 0 to 31. The .
