Feeds:
Posts
Comments

Archive for October, 2016

Quick one today:

Optimizing performance of TempDB is critical to the overall performance improvement of VLDBs. Today, we’ll cover a couple of Trace Flags that add to TempDB’s performance improvement.

  • Trace Flag 1117
  • Trace Flag 1118

 

Trace Flag 1117

Each database has a filegroup; Each filegroup could have more than 1 files. Similarly, TempDB also could have multiple data files (for VLDBs). This Trance Flag 1117, enables Sql Server to grow all the files in the filegroup at the same time, when one file needs to be grown.

  • As you can imagine, this option has some pros and cons. It is generally a good idea to grow all the files in a filegroup at the same time. Since this flag impacts TempDB, which is utilized by all user databases, this could be an unwelcome option for some environments.
  • So a better compromise might be to pre-configure TempDB data files to the maximum size needed for your environment, so as to not encounter “file increment” events.

 

Trace Flag 1118

When space is allocated to a database file, internally Sql Server adds in increments of 64k size extents (group of 8 pages, which are 8k each; 8*8k=64k). This Trace Flag enables Sql Server to allocate Uniform Extents during this size increments (and avoid allocating mixed extents). It impacts all new object creations in all the databases.

  • This is important for Sql Server TempDB as it is utilized by all user databases at the same time. There is a possibility that it could result in reduction in efficiency of space utilization, but that is a small price to pay for better performance.

 

Hope this helps,
_Sqltimes
Advertisements

Read Full Post »

Interesting one today:

Recently, in a post, we covered about covered some ideas optimizing transactional log file by reducing or removing too many VLFs. This is an important step in optimizing Sql Server performance, especially for VLDBs.

Important Artifact 1:

There are some interesting nuances to Transactional log file architecture and its fascinating  operational subtleties. As Microsoft documented it extensively, there are something called VLFs in a LDF file (transactional log file). The way each VLF is utilized in a circular -linked-list fashion in an important artifact to finding optimization approaches.

Important Artifact 2:

Adding to that, Paul Randall’s post here uncovers some nuances to Sql Server internals algorithm in which it extends LDF files. This is key to the way we configure LDF size for each database usage levels.

After reviewing the above topics and supporting artifacts, the optimal approach to configuring transactional log files (LDF) for VLDBs to achieve elimination of 1 MB AutoGrowth frequently are two ways:

  1. Versions before Sql Server 2014
  2. Sql Server 2014 and newer

Versions before Sql Server 2014

Pre-cofigure larger transactional log file size in 8 GB increments (after initial size); This results in 16 VLFs in each 8 GB growth increment, with 512 MB for each VLF.

Example:

  • If you need LDF file size less than or equal to 8 GB, start with 8 GB size.
  • From 8 to 16 GB, use 16 GB LDF file size.
  • 16 – 24 GB, use 24 GB as initial size.
  • 72 – 80 GB, use 80 GB as initial size.

Important: It is important to start with 8 GB and keep increasing by 8 GB to larger file size, rather than just going straight up to 80 GB. It is important to perform actions in this sequence, because of the algorithm that assigns VLFs to each size increment.

See the script below for detailed understanding:

--
--	Perform increments in 8 GB (to create 512MB VLFs)
--
ALTER DATABASE SampleDB MODIFY FILE (NAME = N'SampleDB_log', SIZE = 8 GB);
GO

ALTER DATABASE SampleDB MODIFY FILE (NAME = N'SampleDB_log', SIZE = 16 GB);
GO

ALTER DATABASE SampleDB MODIFY FILE (NAME = N'SampleDB_log', SIZE = 24 GB);
GO

ALTER DATABASE SampleDB MODIFY FILE (NAME = N'SampleDB_log', SIZE = 32 GB);
GO

ALTER DATABASE SampleDB MODIFY FILE (NAME = N'SampleDB_log', SIZE = 40 GB);
GO

ALTER DATABASE SampleDB MODIFY FILE (NAME = N'SampleDB_log', SIZE = 48 GB);
GO

ALTER DATABASE SampleDB MODIFY FILE (NAME = N'SampleDB_log', SIZE = 56 GB);
GO

ALTER DATABASE SampleDB MODIFY FILE (NAME = N'SampleDB_log', SIZE = 64 GB);
GO

ALTER DATABASE SampleDB MODIFY FILE (NAME = N'SampleDB_log', SIZE = 72 GB);
GO

ALTER DATABASE SampleDB MODIFY FILE (NAME = N'SampleDB_log', SIZE = 80 GB);
GO

 

Initial or increment Allocation size NoF VLFs VLF size Total Log size Total VLFs
Initial 500 KB 2 250 KB 500 KB 2
Increemnt 8 GB 16 512 MB 8 GB 18
Increment 8 GB 16 512 MB 16 GB 34
Increment 8 GB 16 512 MB 24 GB 50
Increment 8 GB 16 512 MB 32 GB 66
Increment 8 GB 16 512 MB 40 GB 82
Increment 8 GB 16 512 MB 48 GB 98
Increment 8 GB 16 512 MB 56 GB 114
Increment 8 GB 16 512 MB 64 GB 130
Increment 8 GB 16 512 MB 72 GB 146
Increment 8 GB 16 512 MB 80 GB 162

 

Sql Server 2014 and Newer

Starting Sql Server 2014 , the algorithm that assigns VLF for each new LDF size increment has undergone significant changes. Keeping then in mind, we need a different approach to configuring LDF size.

Note: We start with initial 8 GB; Add another 8 GB; From them on, add by 1 GB increment up to required size.


--
-- Perform increments in 8 GB (to create 512MB VLFs), then increase it by 1 GB
--
ALTER DATABASE SampleDB MODIFY FILE (NAME = N'SampleDB_log', SIZE = 8 GB);
GO

ALTER DATABASE SampleDB MODIFY FILE (NAME = N'SampleDB_log', SIZE = 16 GB);
GO

ALTER DATABASE SampleDB MODIFY FILE (NAME = N'SampleDB_log', SIZE = 17 GB);
GO

ALTER DATABASE SampleDB MODIFY FILE (NAME = N'SampleDB_log', SIZE = 18 GB);
GO

..
..
..

ALTER DATABASE SampleDB MODIFY FILE (NAME = N'SampleDB_log', SIZE = 100 GB);
GO

 

Initial or increment Allocation size NoF VLFs VLF size Total Log size Total VLFs
Initial 8 GB 16 512 MB 8 GB 16
Increment 8 GB 16 512 MB 16 GB 32
Increment 1 GB 1 1 GB 17 GB 33
Increment 1 GB 1 1 GB 18 GB 34
Increment 1 GB 1 1 GB 19 GB 35
Increment 1 GB 1 1 GB 20 GB 36
Increment 1 GB 1 1 GB 100 GB 116

 

Other better practices:

  • No benefit to having multiple LDF files
  • Better to have larger size VLFs than too small size (that could result in frequent small increments – not good)
  • Since instant file initialization does not work for LDF files, it might take a few seconds to set up LDF to desired final size.

 

Hope this helps,
_Sqltimes

Read Full Post »

Quick one today:

Ran into an interesting version of the familiar error message. When installing Sql Server 2012 on a Windows Server 2012 R2, this error appeared.

Please note that, during pre-install checks, it just highlighted lack of .Net 3.5 it as a warning, but not as a failure. When installation is attempted, it fails asking for .Net 3.5.

TITLE: Microsoft SQL Server 2012 Setup
 ------------------------------

The following error has occurred:

Error while enabling Windows feature : NetFx3, Error Code : -2146498298 , 
Please try enabling Windows feature : NetFx3 from Windows management tools 
and then run setup again. For more information on how to enable Windows features, 
see http://go.microsoft.com/fwlink/?linkid=227143

For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft%20SQL%20Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=11.0.5058.0&EvtType=0x681D636F%25401428%25401

net3-5_error

It seems like a cryptic error message, but its just a different variation of the same old “Missing .Net 3.5” error message. To resolve follow these steps:

Step 1:

From Task Bar, open Server Manager. Go to Manage, Add Roles and Features.

server_manager_add_roles_and_features

Step 2:

In the Add Roles and Features Wizard, click through the initial screen; When you are in ‘Installation Type’ tab, choose ‘Role-based or Feature-based installation’.

add_roles_and_features_wizard

 

Step 3:

Under Server Selection tab, choose the appropriate server name. If you are running it on the server, its name will be displayed in the Server Pool section.

server_selection_wizard

Step 4:

Skip the next section (Server Roles). Installation of .Net 3.5 is under Features. In the Features section, click the check box to install .Net 3.5.

install_net35

Step 5:

Components necessary to install .Net 3.5 are usually not readily available, so having access to Windows Server 2012 DVD or ISO would be necessary.

Windows_2012_ISO.png

In the Confirm Installation Selections step, at the bottom of the screen, click on Specify an alternate source path link to specify the path to Windows Server 2012 ISO.

Once necessary binaries are identified, installation will progress to completion.

Then reattempt, Sql Server installation.

 

Hope this helps,
_Sqltimes

Read Full Post »

Quick one today:

Not often, but once in a while, we need to check what Sql Components are installed on a given virtual machine; so we could replicate (install the same components) on any dependent database virtual machines. Very rarely do we get opportunities to appreciate the multiple features made available through the Sql Installer. Microsoft provides awesome tools to achieve this fashionably.

Follow these steps:

Step 1:

To open the Sql Installer, go to

  • Start
  • Microsoft Sql Server
  • Configuration Tools
  • Sql Server Installation Center
Sql Server Installation Center

Sql Server Installation Center

 

Step 2:

Under Sql Server Installation Center, open Tools. Go to Installed Sql Server Features Discovery Report.

 

Sql Install Discovery Report

Sql Install Discovery Report

 

Step 3:

When you click on it, Sql Server Installation Center will generate the report.

Running Discovery Report

Running Discovery Report

Once the report completes, it shows the output in, an easy to use, HTML format in a browser. Sample report is attached below:

Installed Components (Discovery Report)

Installed Components (Discovery Report)

Note: The same report is saved as SqlDiscoveryReport.htm under Program Files\Microsoft SQL Server\…\Setup Bootstrap\Log\…

 

Hope this helps,
_Sqltimes

Read Full Post »