{"id":164,"date":"2012-04-23T08:41:15","date_gmt":"2012-04-23T08:41:15","guid":{"rendered":"https:\/\/sqlserverlearner.azurewebsites.net\/2012\/04\/23\/sql-server-affinity-mask-setting\/"},"modified":"2012-04-23T08:41:15","modified_gmt":"2012-04-23T08:41:15","slug":"sql-server-affinity-mask-setting","status":"publish","type":"post","link":"https:\/\/sqlserverlearner.com\/2012\/04\/23\/sql-server-affinity-mask-setting\/","title":{"rendered":"sql server affinity mask setting"},"content":{"rendered":"<p>table.hovertable {<br \/>\nfont-family: verdana,arial,sans-serif;<br \/>\nfont-size:11px;<br \/>\ncolor:#333333;<br \/>\nborder-width: 1px;<br \/>\nborder-color: #999999;<br \/>\nborder-collapse: collapse;<br \/>\n}<br \/>\ntable.hovertable th {<br \/>\nbackground-color:#c3dde0;<br \/>\nborder-width: 1px;<br \/>\npadding: 8px;<br \/>\nborder-style: solid;<br \/>\nborder-color: #a9c6c9;<br \/>\n}<br \/>\ntable.hovertable tr {<br \/>\nbackground-color:#d4e3e5;<br \/>\n}<br \/>\ntable.hovertable td {<br \/>\nborder-width: 1px;<br \/>\npadding: 8px;<br \/>\nborder-style: solid;<br \/>\nborder-color: #a9c6c9;<br \/>\n}<\/p>\n<p><strong>Below is the complete guide to setting SQL Server affinity:<\/strong><\/p>\n<p>SQL Servers affinity tells the server to use specific processors to perform a certain task.<br \/>\nFor example, affinity I\/O mask can be used to configure sql server processors affinity to Input output operations.<\/p>\n<p><strong>How to set the affinity in sql server?<\/strong><\/p>\n<p>SQL Servers affinity on a multiprocessor system can be set by using <a href=\"http:\/\/sqlserverlearner.com\/2011\/using-sp_configure-sql-server\">sp_configure<\/a>.<\/p>\n<p>Sample script:<br \/>\n[sql]<br \/>\nsp_configure &#8216;show advanced options&#8217;, 1<br \/>\nGO<br \/>\nsp_configure<br \/>\n[\/sql]<\/p>\n<p>When you execute the above script you will be able to find 5 items for affinity:<\/p>\n<ul>\n<li>affinity I\/O mask &#8211; For setting sql server disk I\/O affinity to cpu&#8217;s on 32bit processor<\/li>\n<li>affinity mask &#8211; For SQL Server affinity to cpu&#8217;s on 32bit processor<\/li>\n<li>affinity64 I\/O mask &#8211; For setting sql server disk I\/O affinity to cpu&#8217;s on 64bit processor<\/li>\n<li>affinity64 mask &#8211; For SQL Server affinity to cpu&#8217;s on 64bit processor<\/li>\n<\/ul>\n<figure id=\"attachment_1875\" aria-describedby=\"caption-attachment-1875\" style=\"width: 520px\" class=\"wp-caption aligncenter\"><a href=\"\/wp-content\/uploads\/2012\/04\/SQL-Server-affinity-Mask-Configuration.png\"><img loading=\"lazy\" decoding=\"async\" src=\"\/wp-content\/uploads\/2012\/04\/SQL-Server-affinity-Mask-Configuration.png\" alt=\"\" title=\"SQL Server affinity Mask Configuration\" width=\"520\" height=\"142\" class=\"size-full wp-image-1875\"><\/a><figcaption id=\"caption-attachment-1875\" class=\"wp-caption-text\">SQL Server affinity Mask Configuration<\/figcaption><\/figure>\n<p><strong>Changing the values using sp_cofigure<\/strong><\/p>\n<p>Below table gives you the details of the values that can be set and the processors used by sql server:<\/p>\n<table class=\"hovertable\">\n<tbody>\n<tr>\n<th>Configured value<\/th>\n<th>Allow SQL Server on processors number<\/th>\n<\/tr>\n<tr>\n<td>1<\/td>\n<th>0<\/th>\n<\/tr>\n<tr>\n<td>3<\/td>\n<th>0 , 1<\/th>\n<\/tr>\n<tr>\n<td>7<\/td>\n<th>0, 1, 2<\/th>\n<\/tr>\n<tr>\n<td>15<\/td>\n<th>0, 1, 2, 3<\/th>\n<\/tr>\n<tr>\n<td>31<\/td>\n<th>0, 1, 2, 3, 4<\/th>\n<\/tr>\n<tr>\n<td>63<\/td>\n<th>0, 1, 2, 3, 4, 5<\/th>\n<\/tr>\n<tr>\n<td>127<\/td>\n<th>0, 1, 2, 3, 4, 5, 6<\/th>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><em>Below piece of code changes the affintiy mask value to 3:<\/em><br \/>\n[sql]<br \/>\nsp_configure &#8216;show advanced options&#8217;, 1<br \/>\nGO<br \/>\nreconfigure<br \/>\nGO<br \/>\nsp_configure &#8216;affinity mask&#8217;, 3<br \/>\nGO<br \/>\nreconfigure<br \/>\nGO<br \/>\n[\/sql]<br \/>\n<strong>Message:<\/strong> <em>Configuration option &#8216;affinity mask&#8217; changed from 0 to 3. Run the RECONFIGURE statement to install<\/em><\/p>\n<p>If you have set an invalid value as per your processor configuration you get the error below error:<br \/>\n<font color=\"red\"><br \/>\nMsg 5832, Level 16, State 2, Line 1<br \/>\nThe affinity mask specified does not match the CPU mask on this system.<br \/>\n<\/font><\/p>\n<p><a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/aa196717(v=sql.80).aspx\" target=\"_blank\" rel=\"noopener noreferrer\">Link<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>table.hovertable { font-family: verdana,arial,sans-serif; font-size:11px; color:#333333; border-width: 1px; border-color: #999999; border-collapse: collapse; } table.hovertable th { background-color:#c3dde0; border-width: 1px; padding: 8px; border-style: solid; border-color: #a9c6c9; } table.hovertable tr { background-color:#d4e3e5; } table.hovertable td { border-width: 1px; padding: 8px; border-style: solid; border-color: #a9c6c9; } Below is the complete guide to setting SQL Server affinity: SQL Servers&hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[32,3],"tags":[1691,1692,1693,1694,1695,1696,1697,1698,1699,1700,1701,1702,1703,1704,1705,1706,1707,1708,1709,1710,1711,1712,1713,1714,1715,1716,1717,1718,1719,1720,1721,1722,1723],"_links":{"self":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/164"}],"collection":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/comments?post=164"}],"version-history":[{"count":0,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/164\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/media?parent=164"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/categories?post=164"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/tags?post=164"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}