Configuring SQL Server For Performance?
Published: 2017-12-21   Views: 238
Author: sivanagamahesh
Published in: Article Writing
Configuring SQL Server For Performance?

SQL Server 2012 provides system settings that can be optimized to your unique environment and workload patterns. Some of the most important performance options are Storage, Network Packet Size, Instant File Initialization and tempdb.


The results of going out of the default prices for these two settings may also be misunderstood and frequently overlooked. The least server memory arranging specifies the quantity of memory that is not released back again to the operating-system by SQL Server when allocated. Essentially, SQL Server retains to this minimum degree of memory, even if it's no longer needed.

Network Packet Size:

The typical network packet size for SQL Server 2012 is 4,096 bytes. Establishing a packet size higher than the default size can certainly improve efficiency for SQL Server circumstances that encounter a huge number of bulk functions or that copy substantial volumes of data. In the event that Jumbo Structures are reinforced and permitted by the server's hardware as well as the network system, take full advantage of the network packet size to 8,192 bytes.

Instant Data file Initialization:

At any occasion a database file is produced or requires to broaden, it will at first be zero-filled by the operating system before the completely new space is manufactured accessible for writing.


One of the most significant system databases that will require the distinctive account and coming up with is tempdb. Through the years, tempdb has adopted a lot more responsibility than it offers previously. In the past, tempdb has been utilized for internal procedures for occasion some index builds and stand variable safe-keeping, along with momentary storage space by designers and programmers.

The following is a partial listing of many of the applications of tempdb:

  •  Event notification
  • Query notification
  • Bulk load procedure with triggers
  • Common desk expression
  • Indexer rebuilds, partitioned index kinds, including type_in_tempdb, and online index operations
  • DBCC operations
  • Large subject type factors and parameters
  • Multiple active effect set operations
  • Row versioning

Thank u

Author Bio

Having 3 years of experience at KosmikTechnologies in IT industries. I have worked across several industries that include corporate and agency environment for various domains such as IT, Non-IT etc.

Article Comments


There are zero comments yet, why not be the first?

You must be logged in to post a comment.

Please log in or register to comment or reply to a comment.

Articles Search

Featured Articles

Most Popular Articles