SQL Server 2012 gives framework settings that can be upgraded to your specific condition and workload designs. The absolute most essential execution settings are Memory, Network Packet Size, Instant File Initialization and tempdb.
Two huge server property design settings incorporate greatest and least server memory. As a matter of course, the product is designed with a base memory of 0MB and a greatest memory of 2,147,483,647MB (2TB)
The results of leaving the default esteems for these two settings is now and again misconstrued and regularly neglected. The base server memory setting indicates the measure of memory that isn’t discharged back to the working framework by SQL Server when assigned. Essentially, SQL Server holds on to this base level of memory, regardless of whether it is never again required.
A run of the mill misguided judgment is normally that SQL Server momentarily apportions to as much as this base measure of memory upon startup. In reality, SQL Server assigns memory just as it is required and might achieve the base server memory esteem determined.
The base server memory setting might not need to be changed aside from when the working framework continually asks for memory assets for different applications having a similar memory space. You might want to abstain from dispensing an inordinate measure of memory to the working framework since that could possibly starve a SQL Server case from memory.
In the meantime, the most extreme server memory sets restrictions for the ideal measure of memory a SQL Server occasion can designate. A memory detail that is set exorbitantly could deny a working framework from memory assets. The most elevated server memory esteem should never equivalent or go over the aggregate sum of accessible server memory. The memory esteem should be no less than 1GB not as much as the general server memory.
System Packet Size:
The standard system parcel measure for SQL Server 2012 is 4,096 bytes. Setting up a bundle measure higher than the default size can surely enhance general execution for SQL Server examples that experience an immense number of mass activities or that exchange generous volumes of information. If Jumbo Frames are bolstered and allowed by the server’s equipment and also the system framework, amplify the system bundle size to 8,192 bytes.
Moment File Initialization:
At any occurrence a database record is produced or requires to extend, it will at first be zero-filled by the working framework before the fresh out of the box new space is made available for composing.
This technique is by and large expensive since the whole compose tasks are obstructed until the point that zero-fill finishes. To keep these sorts of stops and pauses, it is conceivable to empower moment record introduction by including the SQL Server benefit record to the arrangement of clients in the Perform Volume Maintenance
One of the more noteworthy framework databases that will require particular thought and planning is tempdb. As the years progressed, tempdb has received significantly more duty than it has beforehand. Before, tempdb has been used for inward procedures for example some file assembles and table variable stockpiling, alongside brief storage room by engineers and developers.
The accompanying is an incomplete posting of a few of the utilizations of tempdb:
· Event warning
· Query warning
· Bulk stack task with triggers
· Common table articulation
· Indexer revamps, divided record sorts, including sort_in_tempdb, and online list tasks
· DBCC tasks
· Large question write factors and parameters
· Multiple dynamic outcome set activities
· Row forming