SQL optimization to improve the speed of SharePoint page access

In the previous two articles we discussed with you the application pool recycling of SharePoint IIS, the number of w3wp processes and the optimization of reset recovery, and today we will talk about the optimization of the backend SQL Server.

For the optimization of SQL, the main introduction today is two points, CPU optimization and memory optimization.

Many students after the installation of SQL, in fact, did not set the memory optimization, resulting in SQL memory allocation is unreasonable, for SharePoint, it is recommended to set the use of SQL memory minimum of 8192 MB, up to 20480 MB of this threshold.

650) this.width=650; "Width=" 820 "height=" 497 "title=" image "style=" border-right-width:0px;background-image:none; border-bottom-width:0px;padding-top:0px;padding-left:0px;margin:0px;padding-right:0px;border-top-width:0px; " alt= "image" Src= "http://s3.51cto.com/wyfs02/M00/8F/12/wKioL1jTJ0awtxjwAAGpWcO9qzE785.png" border= "0"/>

650) this.width=650; "width=" 552 "height=" 497 "title=" image "style=" border-right-width:0px;background-image:none; border-bottom-width:0px;padding-top:0px;padding-left:0px;margin:0px;padding-right:0px;border-top-width:0px; " alt= "image" Src= "http://s3.51cto.com/wyfs02/M01/8F/14/wKiom1jTJ0eRE8vzAAE35iY5mNQ476.png" border= "0"/>

As set above, note that the configuration values and operating values here must be configured two times, and to ensure that they have been, otherwise will not take effect, if not match, more than a few clicks can.

By default, the settings for these two values are not the same, we need to click on the configuration item, click OK to save, then enter the value, click Run Project, then click OK. Set several times, two places repeatedly OK, try a few more times.

OK, say the memory, now we have a CPU, in a SharePoint environment, or a private cloud environment, normally, SQL CPU should run at least 40%, along with the hard disk will have frequent read and write Io.

If the CPU is not high, disk IO read and write is not high, that is, SQL dragged hind legs, SQL once dragged down, the front-end Web server how to optimize and cattle x, user access is still very slow.

By default, SQL and IIS, for each request, there will only be one person to serve you, but in fact, SQL can be used by many people to serve you, to handle your query, but if you do not optimize it, it will be lazy, by default only activate a waiter for you to work.

Also in the processor options of the SQL instance, note the following three values.

650) this.width=650; "Width=" 710 "height=" 497 "title=" image "style=" border-right-width:0px;background-image:none; border-bottom-width:0px;padding-top:0px;padding-left:0px;margin:0px;padding-right:0px;border-top-width:0px; " alt= "image" Src= "http://s3.51cto.com/wyfs02/M01/8F/14/wKiom1jTJ0jCUAQWAAG0K0mm0I4159.png" border= "0"/>

It is recommended that you start with a 128 thread, which can open up to 128 threads and provide a query service for the front end.

and tick the enhanced SQL priority.

Finally, as with memory configuration items, remember to set several more times on both the configuration value and the running value to ensure that the same values take effect.

In the maximum worker thread this place, the default is 0, is only open 1 threads to service, that is to say how many queries you come over, only a service personnel reception, the following query all please queue up.

OK, after modifying the SQL memory and CPU configuration items, you can try restarting the SQL Server service, or restart the server directly, the effect is still obvious. Today's discussion is here, you are welcome to discuss together, thank you!

This article is from the "June Ma Run Space" blog, be sure to keep this source http://horse87.blog.51cto.com/2633686/1909461

SQL optimization to improve the speed of SharePoint page access