Microsoft's SQL Server is a modern database management framework intended to help business experts store and access data as requested by other programming applications. Several distinct editions of Microsoft SQL Server exist - each focusing on diverse crowds and workloads.
When utilized appropriately, an association's SQL Server can help with productivity, empower coordinated efforts, and be a financially smart addition to the enterprise management tools. Nonetheless, problems can arise over the long haul as an association advances, and organizations may get acquainted with glaring shortcomings from the existing system.
Similar to a vehicle, a business' Microsoft SQL Server also needs performance tuning periodically. Shockingly, SQL Server management and tuning aren't, in every case, straightforward, leaving numerous database administrators confused concerning where to begin.
During a seminar with E2E Networks, Mr. Thirunavukkarasu discussed the best practices for performance tuning the MS SQL Server. With a decade of glorious experience in Microsoft Data The platform, Mr. RM Thirunavukkarasu is a certified Microsoft trainer and professional and is also the founder of GEO Platinum IT Services.
Here are some of the best practices for performance tuning of MS SQL Server.
Know the database structure extremely well
According to Mr. Thirunavukkarasu, having a sound knowledge of the database structure is essential. Understanding the fundamental anatomy of database components, file groups, documents, pages, allocation bitmaps, and compressed files is integral to doing performance tuning.
Having the database in uniform extents
Extents are an assortment of eight physically adjacent pages and are utilized to deal with the pages proficiently. All pages are coordinated into extents. SQL Server primarily includes two kinds of extents:
● A single object claims uniform extents; the owning item must utilize every one of the
eight pages in the extents.
● Up to eight pages share mixed extents, while an alternate object can own every one of the eight pages in the extent.
Mr. Thirunavukkarasu mentioned that keeping the database in uniform extents is important for the performance to be good.
Know about the importance of trace flags
Trace flags are utilized to set explicit server characteristics, or to adjust a specific behavior. Mr. Thirunavukkarasu stated the importance of three trace flags, which are essential while doing a performance tuning:
● 1117 - When a file in the filegroup meets the autogrow threshold, all documents in the filegroup develop. This trace flag influences all databases, and suggests each sheltered component to build all documents in a filegroup through a similar process.
● 1118 - Forces page distributions on uniform extents rather than mixed extents, diminishing disputes on the SGAM page. During the process, when another object is made, as a matter of course, the initial eight pages are designated from various extents (mixed extents). However, if the user requires more pages, they are distributed from a parallel extent (uniform extent). The SGAM page is utilized to follow these blended degrees, so it can immediately turn into a bottleneck when various mixed page allotments are happening. This trace flag dispenses every one of the eight pages from a similar extent while making new objects, limiting the need to filter the SGAM page.
● 2371 - Changes the fixed update insights threshold to a direct update statistics
threshold.
Make sure to set up MAXDOP properly
Suppose a user runs SQL Server on a PC with more than one processor or CPU. In such a case, MAXDOP recognizes the best level of parallelism: the number of processors utilized to run a single proclamation for each query with an equal performance plan. You can utilize the maximum level of parallelism choice to restrict the number of processors used for serial
execution. To forestall run-away queries from affecting SQL Server performance, utilize every available CPU.
Mr. Thirunavukkarasu also mentioned a need for NUMA in a case where there is more than one CPU. MAXDOP should be set at 2 or 4, and not more than that for an excellent performance
result.
Enabling Snapshot Isolation
According to Mr. Thirunavukkarasu, enabling Snapshot Isolation is essential because it abstains from locking and blocking by utilizing row versioning. If a user alters data, the submitted forms of influenced columns are duplicated to tempDB and allotted variant numbers. This activity is
called copy on write and is utilized for all additions, updates, and erases. Correspondingly, when another meeting reads similar data, the submitted variant of the prior information initiates a transactional reading.
Statistics
Mr. Thirunavukkarasu mentioned that statistics are critical, and they should be updated. The latest technology enables default settings that update statistics automatically.
Updating max memory configuration
As Mr. Thirunavukkarasu quoted in the webinar, having enough memory for a SQL server is very crucial. If there is 32 GB space in a processor; and 28 GB is allocated to the SQL server, then the remaining storage capacity is 4 GB for conducting operations and tasks. However, the performance will not be upgraded in a restricted storage capacity. As a result, there should be
more than enough memory for the SQL server to perform effortlessly.
Following these practices, the performance tuning of MS SQL Server can be made seamless. While MS SQL Server is an important addition and a necessity in many setups, it does not mean that it will not ever run into troubles. This is why having a stronghold on some of the best
practices for performance tuning can help greatly.