Feeds:
Posts
Comments

Archive for the ‘Performance Improvement’ Category

Quick one today:

Years ago, there was a popular comment that Sql Server did not wide variety of functions; Once of them is the much needed IsNumeric functionality. Though such a function existed, there was a case where it resulted in incorrect results. So there were several custom functions like IsReallyNumeric or IsInteger, etc (my personal DBA library also had such custom functions)

Now, there is a powerful function that does more than just IsNumeric capability. It is called TRY_PARSE. Let’s take an example:

--
-- Sample code for TRY_PARSE
--
SELECT	  TRY_PARSE('ABCDEF' AS INT)		AS [INT_Test1]
	, TRY_PARSE('123456' AS INT)		AS [INT_Test2]
	, TRY_PARSE('ABCD-01-01' AS DATETIME)	AS [DateTime_Test1]
	, TRY_PARSE('2016-01-01' AS DATETIME)	AS [DateTime_Test2]
GO

TRY_PARSE.PNG

This function takes input in VARCHAR/NVARCHAR format and attempts to convert into the requested format. If the result is successful, the value is returned in requested data type. If not, NULL is returned. This makes it very useful in data processing logic flows; And makes it more useful function than the legacy (IsNumeric) functions.

Hope this helps,
_Sqltimes

Read Full Post »

Interesting one today:

MSDTC is one of the popular software components that is on all Windows systems. It is one of the Windows  Operating System components that Sql Server relies on it to perform some crucial tasks (when needed).

What does it do?

MSDTC, Microsoft Distributed Transaction Coordinator, is essentially, as name suggests, a coordinator/manager to handle transactions that are distributed over multiple machines. Let’s say we start a transaction, where one of the steps includes querying data from a different Sql Server instance on a different physical machine; MSDTC comes into action with these specific tasks that need transaction coordination across different physical machines. It executes the section of code that is supposed to run on remote machines and brings back the results to local Sql instance. In this process, if any issue were to occur, on the remote machine that results in rollback, MSDTC makes sure the original transaction on this machine also rolls-back safely.

How does it do?

MSDTC comes with necessary Operating System controls and memory structures to carry out these operations independent of the Sql Instances, while keeping integrity of the transaction across the multiple physical Sql machines a.k.a. the complete two-phase distributed commit protocol and the recovery of distributed transactions.

Where does Sql Server use it?

The key point here is that these need to be Sql Instances on different physical machines. Queries that request data across different instances on the same physical box do not go through MSDTC.

MSDTC is used by query activities like

  • Linked Servers
  • OPENROWSET
  • OPENQUERY
  • OPENDATASOURCE
  • RPC (Remote Procedure Calls)
  • Ones with
    • BEGIN DISTRIBUTED TRANSACTION
  • etc…

So, every time we run SQL queries that utilize above techniques, they rely on MSDTC to carry out operation while maintaining transaction integrity.

Who else uses it?

MSDTC is an Operating System resource that is used by applications other than Sql Server, to perform any distributed transaction activities; Like eXtended Architecture applications.

Is MSDTC required?

MSDTC is not required for Sql Server installation or operation. If you are only going to use Database Engine, then it is not required or used. If your Sql uses any of the above mentioned query techniques (Linked Server, OPENQUERY, etc), or SSIS or Workstation Components then MSDTC is required.

If you are installing only the Database Engine, the MSDTC cluster resource is not required. If you are installing the Database Engine and SSIS, Workstation Components, or if you will use distributed transactions, you must install MSDTC. Note that MSDTC is not required for Analysis Services-only instances.

What about Sql Cluster?

Same rules as above apply to Sql Clusters as well with one additional rule. If you have two instances on the same machine (that are clustered across different physical machines), then you’ll need MSDTC. Since the Cluster could failover to remote machine at anytime.

Let’s take an example:

Let’s say Instance1 is on physical machines A & B, with B as active node. Instance2 is on machines B & C, with B as active node. A query going from Instance1 to Instance2 will need MSDTC (even if both the instances are active on the same physical machine B at that given point in time.).

This is because, there is no guarantee that they will remain on the same physical machine at any given time; They might failover to other machines, resulting in instances being on physically different machines. So MSDTC is required (when distributed operations are performed).

Also the recent Sql Server versions do not required MSDTC during Sql Server installations.

Other points in a Clustered Environment

We could have multiple instances of MSDTC as different clustered resource (along with default MSDTC resource).

In scenario with multiple MSDTC, we could configure each Sql Cluster resource to have a dedicated MSDTC instance. If such mapping does not exist, it automatically falls back to use the default MSDTC resource.

Hope this helps,
_Sqltimes

Read Full Post »

Interesting post today:

Earlier this week, there was an interesting error in replication of our production environment:

select spid, blocked, lastwaittype from sysprocesses 
where '~68~~619~~1017~~1277~' like '%~' + cast (spid as nvarchar)+ '~%' 
and '~68~~619~~1017~~1277~' like '%~' + cast (blocked as nvarchar)+ '~%'

We have a dedicated distributor that delivers data from publisher to a remote subscriber. Before this error ocured, the data was flowing well, but once this error occured, the agent kept retrying, but there was no movement in delivery.

Reasons:

On further investigation, it became apparent that this agent has multiple threads configured to deliver the payload to subscribers a.k.a. SubscriptionStreams.

On a previous post on SubscriptionStreams, an important point is highlighted, which will be presented here again:

  • If any one threads runs into problems and rolls back, all other threads also rollback.

With the above point in context, it looks like there is an issue in the thread-scheduling logic in the Distribution Agent. When distribution agent is a single thread or multiple threads, ACID properties need to be maintained; so when one thread timesout (or runs into a problem), the rest of the threads need to be rolled back.

My guess is, distribution agent keeps trying with multiple threads and it runs into some scheduling conflict when one of the threads timesout or unable to divide the workload to be carried out in parallel.

Let’s say, if the replication payload is too small for parallel operation, Sql Server allows only one thread to do the work; In this scenario, the other threads need to be maintained active — but in some weird situation they might timeout — resulting in the whole batch to be rolledback.

Solution:

  • Microsoft confirmed that this is a bug and released a patch (for Sql Server 2005).
  • In our situation, we are in Sql Server 2008 R2, so this patch does not apply; The trick that worked was to remove parallel operation : SubscriptionStreams.

Once parallelism is removed, the traffic started flowing through.

Important notes:

  • Make sure you play with CommitBatchSize & CommitBatchThreshold before you advance to SubscriptionStreams.
  • SubscriptionStreams is a good option that improves replication performance . But it is an advanced option that is only good for certain situations.
  • So, when replication traffic patterns vary, the usage of this parameter needs to vary.
  • For heavy workloads, we set paralleism; For lower than normal workloads, we remove or reduce parallelism.
Hope this helps,
_Sqltimes

Read Full Post »

Interesting topic today:

In Sql Server, as we all know, concurrency is maintained through locks & latches. When a particular row is being retrieved for any activity, a lock is requested on that row a.k.a. Shared lock or Exclusive lock, depending on the nature of the activity performed. Once activity is completed, the lock is released. So, when a large number of records in a table are retrieved, then something interesting happens. Sql Server, rather than issuing individuals locks on each row, it perform Lock Escalation to lock the entire table.

This behavior is helpful in some scenarios and detrimental in others. Each lock takes some resources (memory, etc). So establishing a large number of smaller locks (ROWLOCKs) aggregates to a lot of resources. So, Sql Server escalates the lock to either PAGE level or TABLE level (depending on the scenario). Sometimes this could result in longer waits for the PAGE (or entire table) to be freed from other locks, before this new lock request could be granted.

To avoid all of this, sometimes, developers use ROWLOCK to force Sql Server to use ROWLOCK even when performing operations on larger number of records (to avoid waiting for entire PAGE/TABLE to be free from other locks). There are several pros and cons to this approach. As always, the details of the situation guide the best approach in each scenario. One exception to this scenario is, even though we use ROWLOCK, sometimes Sql Server will force lock escalation.

Today, we’ll look at this one aspect of this automatic lock escalation:

Question: At what point does Sql Server, force lock escalation even when ROWLOCK is used?

Let’s run DELETE on a table and see how lock escalation happens. Three diferent levels:

  • 2000 ROWLOCK requests
  • 4000 ROWLOCK requests
  • 6000 ROWLOCK requests

Step 1: At 2000 ROWLOCKS

--
-- Let's run a large DELETE operation
--
DELETE TOP (2000) FROM dbo.SampleTable WITH (ROWLOCK) GO

Now, in a different SSMS window, let’s check the number of locks on the table.

--
-- Check locks on the table
--
EXEC sp_lock 56
WAITFOR DELAY '00:00:01'    -- keep running every second, to capture locks from other window
GO 10

 

Row Level Locks Granted

Row Level Locks Granted

As you can see ROWLOCKS on keys are GRANTed. Now, lets increase the batch size and see where the force lock escalation happens.

Step 2: At 4000 ROWLOCKS

--
-- Let's run a large DELETE operation
--
DELETE TOP (4000)
FROM dbo.SampleTable WITH (ROWLOCK)
GO

Let’s check the lock situation:

ROWLOCKs granted at 4000

ROWLOCKs granted at 4000

So, even at 4000, something interesting happens. Some ROWLOCKs are issues and some PAGELOCKS are issued. Looks like for some rows, the lock is escalated to the PAGELOCK level for efficiency. Let’ continue the effort

Step 3: At 6000 ROWLOCKS

--
-- Let's run a large DELETE operation
--
DELETE TOP (6000)
FROM dbo.SampleTable WITH (ROWLOCK)
GO

Let’s review the locks situation:

ROWLOCKs at 6000

ROWLOCKs at 6000

BINGO !!! As you can see the lock escalation occurs to TABLOCK level at 6000.

Initially, ROWLOCKS are issued; Then just a second later, some locks are escalated to PAGE and subsequently to TABLE level. This is interesting.

Another Nuance: Percentage

Question: Does this escalation occur based on a strict number (or range) or is it based on percentage of records being accessed in a table?

  • From the tests, it seems like the LOCK ESCALATION occurs based on the number, and not percentage of records being manipulated.
Table Size Total Record Count Count of records Lock Requested Type of Lock Granted
Small 2,100 2000 ROWLOCK
Medium 35,000 2000 ROWLOCK
Large 500,000 2000 ROWLOCK

Conclusion:

  1. Sql Server makes intelligent estimations on what is better at each level and makes best decisions to escalate locks as needed. Keep the 2000, 4000 & 6000 as general rule in mind and not as a set in stone rule.
  2. At any given point, Sql Server makes the best judgement call on what is more efficient.
  3. Lock Escalation is based on the number of records on which lock is requested and not on the percentage of records relative to the total records in the table.

Important Note:

  1. This behavior applies only to Sql Server 2012 as this behavior varies from version to version.
  2. In the past, for Sql Server 2008 and before, the number was at 1800 – 2200 before TABLE lock escalation occurs.
  3. If this continues, may be for Sql Server 2016, the number would be slightly higher, as Microsoft improves the lock efficiency by reducing the amount f resources required for each lock.
  4. This does not mean to go ‘free range’ crazy and use ROWLOCK on every query (obviously) & use 4000 everytime. Keep this information handy in making data retrieval decisions. Each ROWLOCK takes resources (memory & CPU); So we need to use caution and minimize the overhead. This a refined technique to be used in infrequent occassions that seem the most suitable for such techniques. Perfom tests before using in production.

 

Hope this helps,
_Sqltimes

Read Full Post »

Quick one today:

When we install Sql Server software on a Windows machine, sometimes, we need to install Service Packs (SP); Either SP1 or SP2 or SP3, etc. As service packs are cumulative, it helps to keep it down to a two step process. In some scenarios, this two step process is not posible due to version conflicts that do not allow RTM Sql Server versions on later versions of OS.

SlipStream allows us to combine these two steps into one. Combine RTM version with ServicePacks (SP) and install them together.

Benefits:

  • Reduce or remove need for multiple restarts
  • Reduce install duration times
  • Avoid version conflicts mentioned above (RTM Sql Server versions may not work on later versions of OS)

Preparatory Steps:

  • Install any prerequisites like .Net 3.5
  • Install Windows Installer 4.5
  • Download and extract SP file to a local drive a.k.a. C:\Temp\SP1\
  • Run the Service Pack (SP) first to install Setup & Support files. This prevents any surprises when actual install is performed.

SlipStream Install:

  • Open Command prompt as Administrator
  • Go to the location where install DVD is located.
  • Use this command to run install in SlipStream mode.
    • Setup.exe /PCUSource=C:\Temp\SP1
  • After a second or two, the Sql Installer Wizard opens
    • Walk through the normal install steps.
  • When you get to the “Ready to Install” screen, it indicates that SlipStream is engaged during this install (see in the image below).
SlipStream SqlCluster Install

SlipStream SqlCluster Install

  • Perform restart if you need to.

Please note that this is just one of the techniques. On Microsoft Support, they have other options detailed with troubleshooting techniques.

Hope this helps,
_Sqltimes

Read Full Post »

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

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 »

Older Posts »