It is no secret that thinking about the problems of configuring the SQL server associated with an increase in performance, IT experts most of them make a choice in favor of an increase in hardware. But is it always justified? Is all the server setup methods already used? It is known that working with configuration parameters and changing their default values \u200b\u200bis capable of improving the performance and other characteristics of this system. Among these configuration options, the SQL configuration has one option with a lot of questions, this is the option - MAX DEGREE OF PARALLELISM (DOP) - here's about it and let's talk about it.
The Maximum Degree of Parallelism (DOP) option determines the number of threads that SQL Server can parallerate the request and means the number of server processors used. The default parameter is 0 - the maximum degree of parallelism. For example, if you have 24 kernels - then the value of the 'Max Degree of Parallelism' will be equal to 24 and the optimizer, if it considers it necessary, can use all processors to execute one instruction, that is, the query will be paralleled by 24 streams. For most cases, this is good, but not for everyone. Also, not always good, using the value of this default parameter. Configuring this parameter may need to be needed, for example, in the following situation: for example, we have an application in which all employees make information about daily operations, and, at a certain period of time, each user performs a request that builds a report on all user operations for Some time lapse. Naturally, if the time gap is large, this query will be executed for a long time and, when installing the DOP by default, will take all available processors, which will naturally affect the operation of other users. Consequently, changing the value of the DOP, we can without a change in the query itself to increase the response time of the SQL server from other users.
MS recommends setting the value as follows:
Setting the parameter on TSQL entire for server:
Exec SP_CONFIGURE "MAX DEGREE OF PARALLELISM", 4; Reconfigure.
You can also set this value for a specific TSQL query:
Use AdventureWorks2008R2; Go Select Productid, Orderqty, Sum (Lineetal) AS TotalFrom Sales.SalesOrderdetail WHERE UNITPRICE< $5.00 GROUP BY ProductID, OrderQty ORDER BY ProductID, OrderQty OPTION (MAXDOP 2); GO
In this example, "Hint" MAXDOP changes the default value of the MAX DEGREE OF PARALLELISM parameter to 2. View the current setting can be:
Exec SP_Configure "Show Advanced", 1; Reconfigure; Exec SP_CONFIGURE "MAX DEGREE OF PARALLELISM"
Now let's see how this value is affected by the rate of execution of the query. In order for the test query, written above, for a longer time, add another SELECT to it. The request will acquire the following form:
< $5.00) dt2 WHERE dt.UnitPrice < $5.00 GROUP BY dt.ProductID, dt.OrderQty ORDER BY dt.ProductID, dt.OrderQty
On my test machine, the value 'MAX DEGREE OF PARALLELISM' is displayed in 0. MSSQL is launched by a machine with a 4-core processor. I conducted a series of experiments with different MAXDOP values: equal to 1 - without parallelization of the query; equal to 2 - using only 2 cores; Equal 4 - using all and without hint to determine the option that uses the default sequel. In order to obtain the execution statistics, the request must be included in the SET Statistics Time ON option, as well as enable the Query Plan Display button in Management Studio. To averaged the results obtained, I performed every query in the cycle 3 times. Results can be seen below:
Select DT.ProductID, DT.OrderQTY, SUM (DT.Linetotal) AS Total From Sales.SalesOrderDetail DT, (Select * from Sales.SalesOrdEtail WHERE UNITPRICE< $5.00) dt2 WHERE dt.UnitPrice < $5.00 GROUP BY dt.ProductID, dt.OrderQty ORDER BY dt.ProductID, dt.OrderQty OPTION (MAXDOP 1); SQL Server Execution Times: CPU time = 45942 ms, elapsed time = 46118 ms. SQL Server Execution Times: CPU time = 45926 ms, elapsed time = 46006 ms. SQL Server Execution Times: CPU time = 45506 ms, elapsed time = 45653 ms.
In terms of the query, it is clear that when installing the hint (MAXDOP 1), the query was performed without parallelization. Average query execution time 45925.66 MS
Select DT.ProductID, DT.OrderQTY, SUM (DT.Linetotal) AS Total From Sales.SalesOrderDetail DT, (Select * from Sales.SalesOrdEtail WHERE UNITPRICE< $5.00) dt2 WHERE dt.UnitPrice < $5.00 GROUP BY dt.ProductID, dt.OrderQty ORDER BY dt.ProductID, dt.OrderQty OPTION (MAXDOP 2); SQL Server Execution Times: CPU time = 51684 ms, elapsed time = 28983 ms. SQL Server Execution Times: CPU time = 51060 ms, elapsed time = 26165 ms. SQL Server Execution Times: CPU time = 50903 ms, elapsed time = 26015 ms.
When installing the hint (MAXDOP 2), the query was performed in parallel to 2 CPUs, it can be seen on the Number of Execution in terms of execution of the query. Average query execution time 27054.33 MS
Select DT.ProductID, DT.OrderQTY, SUM (DT.Linetotal) AS Total From Sales.SalesOrderDetail DT, (Select * from Sales.SalesOrdEtail WHERE UNITPRICE< $5.00) dt2 WHERE dt.UnitPrice < $5.00 GROUP BY dt.ProductID, dt.OrderQty ORDER BY dt.ProductID, dt.OrderQty OPTION (MAXDOP 4); SQL Server Execution Times: CPU time = 82275 ms, elapsed time = 23133 ms. SQL Server Execution Times: CPU time = 83788 ms, elapsed time = 23846 ms. SQL Server Execution Times: CPU time = 53571 ms, elapsed time = 27227 ms.
When installing a hint (MAXDOP 4), the query was performed in parallel to 4 CPUs. The average execution time of the query 24735.33 MS
Select DT.ProductID, DT.OrderQTY, SUM (DT.Linetotal) AS Total From Sales.SalesOrderDetail DT, (Select * from Sales.SalesOrdEtail WHERE UNITPRICE< $5.00) dt2 WHERE dt.UnitPrice < $5.00 GROUP BY dt.ProductID, dt.OrderQty ORDER BY dt.ProductID, dt.OrderQty SQL Server Execution Times: CPU time = 85816 ms, elapsed time = 23190 ms. SQL Server Execution Times: CPU time = 85800 ms, elapsed time = 23307 ms. SQL Server Execution Times: CPU time = 58515 ms, elapsed time = 26575 ms.
the request was performed in parallel, as well as 4 CPUs. Average query time 24357.33ms
links: http://support.microsoft.com/kb/2023536
Purpose: Examine the effect of SQL parallelism to work with requests 1C
Literature:
Test environment:
· Windows Server 2008 R2 Enterprise
· MS SQL Server 2008 R2
· 1c enterprise 8.2.19.90
Figure 1. SQL PROPERTIES "GENERAL"
Figure 2. SQL PROPERTIES "ADVANSED"
Instruments:
· SQL Server Profiler
· 1C requests console
Test request:
CHOOSE
AK. Name as name
OF
Registering. Pressure Classifier as AK
Internal connection Registering. Pressure Classifier as AK1
By ac.kod \u003d ak1.kod
Preparation:
We run SQL Server Profiler, set the connection, mark events and columns as shown in Figure 3.
Figure 3. Trace Properties
Install the selection for our database
Figure 4. Base filter
Reduction:
· Max Degree of Parallelism - MDOP
· Sost Threshold for Parallelism - Cost
Testing a sequential request plan (MDOP \u003d 1)
Figure 5. Request Console - Performance Time 20 sec.
The SQL server "MAX DEGREE OF PARALLELISM" server is set to 1 (without parallelism). We look at the result in the profiler (Fig.6)
Figure 6. Sequential request plan
SQL server has formed a sequential request plan, while: total loading CPU \u003d 6,750 (s), and time for execution of the query \u003d 7,097 (s)
Testing a parallel request plan (MDOP \u003d 0, Cost \u003d 5)
Transfer SQL Server to parallelism mode (in SQL Query):
USE Master;
Exec SP_Configure "Show Advanced Option", 1;
Reconfigure with Override.
USE Master;
exec SP_CONFIGURE "MAX DEGREE OF PARALLELISM", 0;
Reconfigure with Override.
Perform the same query (Figure 7)
Figure 7. Console requests - execution time 16 sec.
Check the result in the profiler (Figure 8)
Figure 8. Parallel Request Plan
The SQL server this time has formed a parallel query plan, while the total loading of CPU \u003d 7.905 seconds, and the duration of the query \u003d 3,458 seconds
Testing a sequential request plan (MDOP \u003d 0, COST \u003d 150)
We will try to get rid of the parallel plan using the "Cost Threshold for Parallelism" parameter. By default, the parameter is set to 5. In our case, from the formation of the parallel plan, it was possible to get rid of 150 (in SQL Query):
USE Master;
exec sp_configure. "COST THRESHOLD FOR PARALLELSM", 150 ;
Check the execution of the query in these conditions (Fig. 9)
Figure 9. Console requests - execution time 20 sec.
Check the result in the profiler (Fig. 10)
Figure 10. Sequential request plan.
SQL server has formed a sequential request plan. Total loading CPU \u003d 7.171 seconds, query execution time \u003d 7, 864 seconds.
Conclusions:
· Performing a test query in a 1C Enterprise environment using a SQL server parallel plan server gives a significant performance gain compared to a sequential plan (16 sec. Against 20 seconds - winnings 4 sec.)
· Perform the test query by the SQL server when using a parallel request plan takes two times faster than when using a sequential request plan (3.5 sec. Against 7.1 sec.)
· SQL server parallelism can not be adjusted not only using the MDOP parameter, but also the parameter "COST THRESHOLD FOR PARALLELISM"
- Tutorial
This manual is designed for beginners seeking a simple manual in Russian for installation english version SQL Server 2012, which will be used to be used for SharePoint 2013.
This article is not for professionals.
All work is divided into 3 stages:
- Installing SQL Server 2012
- Setting the Max Degree of Parallelism server configuration setting
- Setting right accountdesigned to install SharePoint 2013
Attention: under the cut many pictures!
Installing SQL Server 2012
1. Before installation, make sure that there is enough free space on the hard disk (in my case it took 2.7 GB).After starting the distribution, select item " Installation."In the left menu, then" click "item" New SQL Server Stand-Alone Or Add Features To An Existing Installation":
2. Start the installation wizard. It will check. You can click on the "Show Details" button and see a detailed report: 
3. Detailed report. Press the "OK" button: 
4. Enter the product key and press the "Next" button: 
5. Agree with the terms of the license agreement.
To do this, put a tick " I Accept the License Terms
6. At the SETUP ROLE step, select the first item " SQL Server Feature Installation". Press the" Next "button: 
7. In step "Feature Selection" celebrate " Database Engine Services", "Management Tools - Basic"And" Management Tools - Complete". Then press the" Next "button: 
8. Then the installer will execute another check. You can click on the "Show Details" button and see a detailed report: 
9. Detailed report. (At this stage, I had an error in the "Microsoft .NET Framework 3.5 IS Installed ..." rule. About this below). Press the "Next" button: 
10. In the "Instance Configuration" step, you must configure the instance of the SQL server service.
I repeat that this article is intended for beginners. Therefore, we assume that SQL Server was not installed on your server, which means that you will leave all default settings. Press the "Next" button: 
11. At this step, the Installation Wizard displays the requirements for disk space. Press the "Next" button: 
12. At the Server Configuration step, you must specify a domain account for service " SQL Server Database Engine". After filling out the" Account Name "and" Password "fields, press the" Next "button: 
13. At the "Database Engine Configuration" step, it is enough to add the current user to the SQL server administrators. To do this, click the "Add Current User" button, then click the "Next" button: 
14. In the next step, press the "Next" button: 
15. Next, the Installation Wizard again executes the check and displays its results. Press the "Next" button: 
16. In step "Ready to install", the Wizard will display summary information. Here you must click the "Install" button: 
17. After the installation is completed, information on the operations generated is displayed: 
18. Intrievable at this stage to restart the computer. In some cases (for example, when installing Microsoft .NET Framework 3.5), the installation wizard itself will display a window with a proposal to restart the computer. Do not refuse.
Setting the Max Degree of Parallelism server configuration setting
By default, the value of the "MAX DEGREE OF PARALLELISM" parameter is 0.SharePoint 2013 requires that this parameter be equal to 1.
It's easy to fix!
1. Launch Microsoft SQL Server Management Studio (Start - All Programs - Microsoft SQL Server 2012 - SQL Server Management Studio).
2. On the Connection screen to the server, click the "Connect" button.
3. Right-click on your server in the window " Object Explorer."And select" Properties.":
4. In the server properties window that opens in the left menu, select page " Advanced"And do the list of properties at the bottom of the screen. Set the parameter value MAX DEGREE OF PARALLELISM" in 1
and click "OK": 
5. Do not close SQL Server Management Studio, it will come in handy.
Setting the Account Rights for Setting SharePoint 2013
The account, on behalf of which the installation of SharePoint 2013, must have increased rights in the SQL server.This account is recommended to give the following roles:
- dbcreator
- securityAdmin.
- public
2. In the "Login Name" field, enter domain name Account from which you plan to install and configure SharePoint 2013.
3. In the left menu, select the page " Server Roles."And check the role of" dbcreator "and" SecurityAdmin ", and also make sure that the" Public "role is already marked. Then click" OK ": 
Now the SQL server is ready for installation SharePoint 2013.
Installing Microsoft .NET Framework 3.5 in MS Windows Server 2012 R2 Standart
In step number 9 of the point " Installing SQL Server 2012"I had an error: it was not installed .NET Framework 3.5.To solve this problem, the following steps must be performed:
1. You must open the console " Server Manager.".
2. In the left menu, select the "Dashboard" item.
3. In the center of the window, click on the "Add Roles and Features" item.
4. In the master that opens, skip "Before You Begin".
5. In the "Installation Type" step, choose the item " Role-Based Or Feature-Based Installation". Press the" Next "button.
6. In the next step, leave everything by default and press the "Next" button.
7. Skip the Step "Server Roles" by clicking the "Next" button.
8. In the step "Features", we celebrate the ".NET Framework 3.5 Features" checkbox. Press the "Next" button.
9. After completing the installation process, you can close the "Add Roles and Features Wizard" wizard.
10. Ready!
All good and peaceful sky above your head!
P.S. Happy Cosmonautics Day!
Max Degree of Parallelism (DOP) - Additional SQL Server configuration option, which is associated with many questions and which are devoted to many publications. In this article of his blog, the author hopes to make a little clarity that this option does and how to use it.
First, the author would like to dispel any doubts about the fact that the specified option sets how many processors can use SQL Server when servicing multiple connections (or users) - it is not so! If SQL Server has access to four inactive processors, and it is configured to use all four processors, it will use all four processors, regardless of the maximum degree of parallelism.
So what does this option give? This option sets the maximum number of processors that SQL Server can use for one request. If the request to SQL Server must return big volume Data (many records), it sometimes makes sense to parallelate, breaking into several small requests, each of which will return their subset of rows. Thus, SQL Server can use multiple processors, and, consequently, on multiprocessor systems, a large number of records of the entire query can potentially be returned faster than on a single-processor system.
There are many criteria that must be taken into account before SQL Server triggers "Intra Query Parallelism" (breaking a request for several streams), and there is no point in detail here. You can find them in BOL, searching for the phrase "Degree of Parallelism". It says that the decision to parallelization is based on the accessibility of the memory processor and, especially, on the availability of the processors themselves.
So, why we need to think through the use of this option - because, leaving it in the default value (SQL Server itself decides on parallelization), sometimes you can get unwanted effects. These effects look like this:
Parallel requests are slower.
The execution time of requests can become non-deterministic, and it can irregular users. The execution time may change because:
The request may sometimes parallel, and sometimes not.
The request can be blocked by a parallel request if processors have been overloaded before this.
Before we continue, the author would like to notice that there is no particular need to dive into the internal organization of parallelism. If you are interested in this, you can read the article "Parallel Query Processing" in Books on Line, in which this information is set out in more detail. The author believes that there are only two important things that you should know about the internal organization of the parallelism:
Parallel requests can generate more threads than specified in the "Max Degree of Parallelism" option. DOP 4 can generate more twelve threads, four for request and additional streams used for sorting, streams, units and assemblies, etc.
Parallelization of requests can provoke different SPIDs to wait with the waiting type CXPACKET or 0x0200. This can be used to find those SPIDs that are in standby when parallel operations, and have in Sysprocesses Waittype: CXPACKET. To facilitate this task, the author offers to use the stored procedure in his blog: Track_WaitStats.
And so "the request can be performed slower when paralleling" Why?
If the system has a very weak bandwidth Disk subsystems, then when analyzing the request, its decomposition can be performed longer than without parallelism.
Possible data blocks or blocking data ranges for the processor generated by another used in parallel and launched later process, etc.
If there is no index for the predicate, which leads to the scanning of the table. The parallel operation within the request may hide the fact that the request would have completed much faster with a consistent execution plan and with the correct index.
From all this follows the recommendation to check the execution of the query without parallelism (DOP \u003d 1), this will help identify possible problems.
The above-mentioned effects of parallelism, by themselves should bring you out that the internal mechanics of parallelization of requests are not suitable for use in OLTP applications. These are such applications for which a change in the execution time can irritate users and for which the server simultaneously serving many users is unlikely to select the parallel execution plan due to these applications of the processor workload profile.
Therefore, if you are going to use parallelism, then most likely it will be needed, for data extraction tasks (Data Warehouse), support for decision-making or reporting systems, where there are not many requests, but they are hard and executed on a powerful server with a large volume of operational Memory.
If you decide to use parallelism, what value should be installed for DOP?. Good practice for this mechanism is that if you have 8 processors, then install Dop \u003d 4, and this with a great degree of probability will be the optimal installation. However, there is no guarantee that it will work. The only way to make sure this is to test different values \u200b\u200bfor DOP. In addition to this, the author wanted to offer his, based on empirical observations of the Council, never set this number more than half of the number of processors that are in stock. If the author had processors less than six, it would set DOP in 1, which simply prohibits parallelization. He could make an exception if had a database that supports the process of only one user (some data extraction technologies or reporting tasks), in this case, in order of exception, it will be possible to install DOP in 0 (default value) that allows SQL Server to make a decision on the need to parallelize the query.
Before completing the article, the author wanted to warn you about the fact that the parallel creation of indexes depends on the number you install for the DOP. This means that you may want to change it at the time of creating or re-create indexes to increase the performance of this operation, and, of course, you can use the MaxDOP hint query, which allows you to ignore the value set in the configuration and can be used in the minimum load clock .
Finally, your request can slow down when paralleling due to errors, so make sure that the last service pack is installed on your server.
| CREATE PROC TRACK_WAITSTATS (@NUM_SAMPLES INT \u003d 10 , @ Delaynum int \u003d 1 @ DelayType Nvarchar ( 10 ) \u003d "Minutes") AS - T. Davidson - This Stored Procedure Is Provided \u003d As Is \u003d With No Warranties, - And Confers No Rights. - USE OF INCLUDED Script Samples Are Subject to the Terms - Specified at http://www.microsoft.com/info/cpyright.htm - @num_samples is the Number of Times to Capture Waitstats, - Default IS 10 Times. DEFAULT DELAY INTERVAL IS 1 MINUTE - Delaynum Is The Delay Interval. DELAYTYPE SPECIFIES Whether - The Delay Interval Is Minutes or Seconds - Create Waitstats Table If IT Does Not Exist, Otherwise Truncate SET NOCOUNT ON IF NOT EXISTS (Select 1 From SysObjects Where Name \u003d "Waitstats") Create Table Waitstats (VARCHAR ( 80 ), Requests Numeric ( 20 ,1 ), Numeric ( 20 ,1 ), Numeric ( 20 ,1 ), Now DateTime Default GetDate ()) Else Truncate Table Waitstats DBCC SQLPERF (Waitstats, Clear) - Clear Out Waitstats Declare @i int, @ Delay Varchar ( 8 ), @ DT VARCHAR ( 3 ), @ Now DateTime, @ Totalwait Numeric ( 20 ,1 ), @ Endtime DateTime, @ BeginTime DateTime, @ HR int, @ min int, @ sec int select @i \u003d 1 SELECT @DT \u003d Case Lower (@delayType) When "Minutes" Then "M" WHEN "MINUTE" THEN "M" WHEN "MIN" THEN "M" WHEN "MM" THEN "M" WHEN "MI" THEN "M" WHEN "M" THEN "M" WHEN "SECONDS" THEN "S" WHEN "SECOND" THEN "S" WHEN "SEC" THEN "S" WHEN "SS" THEN "S" WHEN "S" THEN "S" ELSE @ DelayType End If @DT NOT IN ("S", "M") Begin Print "Please Supply Delay Type E.g. Seconds or Minutes" Return End If @dt \u003d "s" begin select @sec \u003d @delaynum% 60 select @min \u003d Cast ((@delaynum / 60 ) AS INT) SELECT @HR \u003d CAST ((@min / 60 ) AS INT) SELECT @min \u003d @min% 60 END if @dt \u003d "m" begin select @sec \u003d 0 SELECT @min \u003d @delaynum% 60 SELECT @HR \u003d CAST ((@delaynum / 60 ) AS INT) END SELECT @Delay \u003d Right ("0" + Convert (VARCHAR ( 2 ), @ HR), 2 2 ), @ min), 2 ) + ":" + + Right ("0" + Convert (Varchar ( 2 ), @ SEC), 2 ) if @hr\u003e 23 or @min\u003e 59 or @sec\u003e 59 Begin Select. "HH: MM: SS Delay Time Cannot\u003e 23:59:59" SELECT "DELAY INTERVAL AND TYPE:" + CONVERT (VARCHAR ( 10 ), @ Delaynum) + "," + @delaytype + "converts to" + @delay return end while (@i<= @num_samples) begin insert into waitstats (, requests, ,) exec ("dbcc sqlperf(waitstats)" ) select @i = @i + 1 Waitfor Delay @delay End --- Create Waitstats Report Execute Get_Waitstats --//--//--//--//--//--//--//--//--//-//--//--//--//--//--//--//--//--/ Create Proc Get_Waitstats AS - This Stored Procedure Is Provided \u003d As Is \u003d With No Warranties, and - Confers no Rights. - Use Of Included Script Samples Are Subject to the Terms Specified - at http://www.microsoft.com/info/cpyright.htm. -- - This Proc Will Create Waitstats Report Listing Wait Types by - PERCENTAGE - CAN BE RUN WHEN TRACK_WAITSTATS IS EXECUTIONING Set Nocount on Declare @Now DateTime, @ Totalwait Numeric ( 20 ,1 ), @ endtime datetime, @ begintime datetime, @ hr int, @ min int, @ sec int select @ now \u003d max (nOW), @ begintime \u003d min (now), @ endtime \u003d max (now) from waitstats where \u003d " Total " --- Subtract Waitfor, Sleep, and Resource_Queue From Total SELECT @TotalWait \u003d SUM () + 1 From Waitstats Where Not In ("Waitfor", "Sleep", "Resource_Queue", "Total", "*** Total ***") and now \u003d @now - INSERT ADJUSTED TOTALS, RANK BY PERCENTAGE Descending DELETE WAITSTATS WHERE \u003d "*** TOTAL ***" AND NOW \u003d @NOW INSERT INTO WAITSTATS SELECT "*** TOTAL ***", 0 , @ Totalwait, @ TotalWait, @ Now Select ,, Percentage \u003d Cast ( 100 * / @ Totalwait as numeric ( 20 ,1 )) from Waitstats Where Not In ("Waitfor", "Sleep", "Resource_Queue", "Total") and now \u003d @now Order by Percentage Desc |
In this post, we will talk only about MS SQL Server. If you are going to "try happiness" to use 1C with Oracle, DB2, POSTRGRE to you this information will be useless. But you need to understand that in 1C there are primarily experts on MS SQL Server. Experts on DB2 appear efforts from IBM. You can argue good or bad for a long time, this DBMS is important, one, the most "smooth" 1C works with MS SQL server. Judging by the latest messages with the "front" more or less decent work with DB2. Although I personally had the experience of setting 1C to work with DB2 more in version 8.1 - everything was somehow not very. In any case, the choice of another DBMS should be clearly reasoned - either the options that are not in MS SQL (cluster with load balancing, grid, etc.), or finances (Oracle already purchased) or a platform (all on Linux).
So in order what you need to do with MS SQL Server:
1) Configure the minimum and maximum amount of memory. The minimum is half the system memory. Maximum - memory system without 2GB. This is done via Management Studio - in server properties:
2) if the priority is not installed on the Processor tab - you need to install
3) Maximum degree of parallelism put in 1.
4) Include SQL Server Agent, customize the Database Mail - there is nothing difficult there, I will not describe in detail.
5) Customize service plans:
General:
a) update statistics - every 2 hours
b) DBCC FreeProccache - every 2 hours
For each database:
a) full backup
b) difference backup
c) defragmentation of indexes - every day
d) perestroika indexes - at night on weekends
e) checking the integrity of the base - once a month at night at the weekend
6) I recommend to install the recovery model for each database (in properties) as Simple. If you do not have a 24/7 system and less than 1000 users at the base, there is no fault-tolerant cluster and you did not sign SLA in which we assume in the case of the exit of any equipment to restore the data up to a second (and not since the last backup) This recommendation will be reasonable. Otherwise, you will very soon for a long time and convulsively reflecting where to go born Tranzaction Log
7) Remove the TEMPDB database from ordinary databases to another disk - even if you have to reconfigure the RAID array and reduce its performance. Otherwise, 1 user will be able to paralyze the work of everyone else. If you have a hardware accelereator instead of a hard disk, then of course you can not separate and put tempdb on it, but it is only if there is
8) Set any means of monitoring - for example, I like Spotlight http://www.quest.com/spotlight-on-sql-server-enterprise/
9) Check yourself with Microsoft Best Practice Analizer - http://www.microsoft.com/download/en/details.aspx?id\u003d15289 - a wonderful tool that helps not only with settings, but also with solving many problems.
Now briefly for what we did all this:
1) Memory. The minimum value will simply save you from "glitches" when the SQL server for some other known reasons does not use all the memory available to it. Must eat all! The maximum value will save you from the swap in case the same optimizer of the SQL server memory optimizer will decide that it would still hurt him ....
3) A very important point - IMMO needs to be put in 1 in all transactional systems. Firstly, this prevents part of the locks between different processes trying to execute 1 request, respectively, it protects us from some "strange" errors. Secondly ... 1 "kill" request will be able to pull out all server resources, which is somewhat not true in relation to the rest of the system users. The parameter determines whether 1 request can be processed as many processor cores.
5) About statistics and cleaning of the procedural cache - this is "on a hearing" and here we often forget about the reyndexation. Meanwhile, this procedure is quite important, especially with the increase in the volume of the base, its importance increases. Sometimes up to 60% of performance are lost on index fragmentation.
7) If there is a Hardware Accelerator or simply 2 disks with different access speeds, I would reconsentially think about highlighting file groups in the databases and dividing individual tables to different disk arrays, with different access time. After all, you agree, the pH "goods in warehouses" and the reference book "Storage of additional information" 2 of the object of the requirement for the storage of which is rooted. It is not necessary to store all files and pictures on the fast array in the database - you can select it on a separate, not as fast, but where there are many places (and not afraid of a bunch of files to download, by the way).
