SQL MTL (Memory To Leave)

What is MTL?

“Memory To Leave” area

SQL Server divides the total memory dedicated to it into two regions, referred to as Buffer Pool (BPool) and Memory to Leave (MemToLeave). BPool is the larger of the two and is used for most memory operations: stored procedure query plan generation, caching data and creating indexes. MemToLeave is dedicated to executing linked server queries, extended stored procedure calls and OLE automation procedure calls. By default, MemToLeave is 384 MB and the balance of total memory available to SQL Server will determine the size of BPool. It is important to realize that although MemToLeave is predominantly used for in-process COM objects, such as extended procedures and linked server queries; memory allocations from this area are also used for large stored procedure or query execution plans. Any memory allocation by SQL Server that is larger than 8KB comes from MemToLeave. Furthermore, memory allocations less than 8KB can, in some cases, come from MemToLeave region as well.

Why should you care about how SQL Server splits the memory between BPool and MemToLeave? Well, if you're working with applications that utilize extended stored procedures, linked server queries and OLE automation procedures heavily; you might need to allow additional memory for these objects by increasing the size of MemToLeave area. On the other hand, if your usage of MemToLeave objects is minimal then you can shrink.

When you start SQL Server, it will compute the maximum size for BPool first; then it reserves the memory for MemToLeave. After MemToLeave is reserved, SQL Server will reserve memory for BPool and subsequently release the MemToLeave memory so that it is available for external processes. This is why even if you set maximum server memory configuration option to 6GB you will only see SQL Server use approximately 5.7GB for buffer pool.

SQL Locking

What is locking?

To manage multi user access to data while maintaining data consistency, SQL Server uses a locking mechanism for data. Locks occur at three different levels and can be of three different types. A lock can be applied at a row, page, or table level.

The main decision threshold occurs at approximately three percent to five percent. If  SQL Server determines that a query requires locks on three percent to five percent of the rows on a given page, it acquires a page-level lock. Similarly, if SQL Server determines that a query requires locks on three percent to five percent of the pages in a given table, it acquires a table-level lock. Because it is not always possible to accurately predict the percentage of rows or pages that require a lock, SQL Server can automatically promote from fine-grained locks to a coarser level of lock. This process is called lock escalation.

Difference between DELETE & TRUNCATE commands

Definition :-

Delete command removes the rows from a table based on the condition that we provide with a WHERE clause.

Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.

TRUNCATE
TRUNCATE is faster and uses fewer system and transaction log resources than

TRUNCATE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log.

TRUNCATE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column.

You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
 

TRUNCATE can not be Rolled back.

TRUNCATE is DDL Command.

TRUNCATE Resets identity of the table.

DELETE

DELETE removes rows one at a time and records an entry in the transaction log
for each deleted row.

If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.

DELETE Can be used with or without a WHERE clause

DELETE Activates Triggers.

DELETE Can be Rolled back.

DELETE is DML Command.

DELETE does not reset identity of the table.

SQL Svr Enterprise 2008 Transaction replication and latency issue

Here’s a summary of the troubleshooting and a few suggestions:

-There was a concern that the replication agent(distribution) was not delivering commands
-We expected a Profiler trace on the subscriber server and found distribution was delivering about 5000 commands every 10 seconds
-The command(s) were calls to the sp_delete procdedure located on the C O R E D e l e t e d O b j e c t s table
-We executed the following command on the subscriber to find the last fully “delivered” command:

Select * from msreplication_subscrptions

-Using the value found, we checked the currently “being delivered” transaction:
select top 1 * from msrepl_transactions
where xact_seqno > 0x00058A9000052ED40127000000000000
order by xact_seqno asc

--0x00058AA50000ABEA00CD --Oldest undelivered transaction

-We then checked how many commands were in the transactions:

select count(*) from MSrepl_commands nolock
where xact_seqno = 0x00058AA50000ABEA00CD
--160,727,115 commands

-With approximately 5000 commands delivered every 10 seconds, we estimated close to 90~hours for the distribution agent to deliver all the commands
-We suspect that on the publisher, a large delete against the table “CoreDeletedObjects” occurred via the application E.g.:

DELETE FROM COREDELTEDOBJECTS WHERE VALUES = X

-By default, when a statement is executed in a transaction, like the DELETE, it is broken apart by the Log Reader agent into a separate command for each row deleted.
-For example

*Pseudo code*
INSERT INTO TABLE X VALUES(1)
INSERT INTO TABLE X VALUES(2)
INSERT INTO TABLE X VALUES(3)
INSERT INTO TABLE X VALUES(4)
INSERT INTO TABLE X VALUES(5)

BEGIN TRAN
DELETE FROM TABLE X WHERE COL < 6
COMMIT

-The delete above would be converted into:

BEGIN TRAN
EXEC SP_MSDEL_X (1)
EXEC SP_MSDEL_X (2)
EXEC SP_MSDEL_X (3)
EXEC SP_MSDEL_X (4)
EXEC SP_MSDEL_X (5)
COMMIT TRAN

-Whereas the single delete would be very fast, the broken apart version would take close to 5x longer.
-There are multiple options for working around this problem:

*Break deletes/updates into smaller transactional batches. E.g.

BEGIN TRAN
DELETE FROM TABLE X WHERE COL < 500
COMMIT

BEGIN TRAN
DELETE FROM TABLE X WHERE COL > 500 AND COL < 1000
COMMIT

BEGIN TRAN
DELETE FROM TABLE X WHERE COL > 1000 AND COL < 1500
COMMIT

*Issue large updates/deletes via a procedure call. For example

CREATE PROCEDURE SP_MONTHLYCLEANUP(DATE INPUT)
AS
DELETE FROM COREDELTEDOBJECTS WHERE VALUES = @INPUT)

EXEC SP_MONTHLYCLEANUP(5/21/2015)

-Then, replicate the procedure call:

Publishing Stored Procedure Execution in Transactional Replication
https://msdn.microsoft.com/en-us/library/ms152754.aspx

-On the subscriber, you would see:

EXEC SP_MONTHLYCLEANUP(5/21/2015)

Best regards,

SQL Error 4064

Issue Description :

Each user has a default database. When you connect to computer that is running Microsoft SQL Server, and you do not specify a login database, the default database is used. However, if the default database is unavailable at the time of the connection, you may not be able to connect. Instead, you receive error message 4062 or error message 4064. The text of the error messages is as follows.

Cause :

The user default database is unavailable at the time of connection. It is possible that the database:

• Is in suspect mode.
• No longer exists.
• Is in single user mode and the only available connection is already being used by someone else or by something else.
• Has been detached.
• Has been set to the RESTRICTED_USER state.
• Is offline.
• Is set to emergency status.
• Does not have the login account mapped to a user or the user has been denied access.
• Is part of a database mirror.

Additionally, the login account may be a member of multiple groups and the default database for one of those groups is unavailable at the time of connection.

Solution :

To work around this behavior, specify a valid, available database in the connection string. To avoid the error when the user's default database is unavailable, log on as a user who can modify logins. Then, change the user's default database to a database that is currently available for a connection.

SQL Server 2005 and later versions

You can use the sqlcmd utility to change the default database in SQL Server 2005. To do this, follow these steps:

1. Click Start, click Run, type cmd, and then press ENTER.
2. Use one of the following methods, depending on the kind of authentication that the SQL Server login uses:
   
   • If the SQL Server login uses Microsoft Windows authentication to connect to the instance, type the following at the command prompt, and then press ENTER:
     
     sqlcmd –E -S InstanceName –d master
   • If the SQL Server login uses SQL Server authentication to connect to the instance, type the following at the command prompt, and then press ENTER:
     
     sqlcmd -S InstanceName -d master -U SQLLogin -P Password
   Note InstanceName is a placeholder for the name of the SQL Server 2005 instance to which you are connecting. SQLLogin is a placeholder for the SQL Server login whose default database has been dropped. Password is a placeholder for the SQL Server login password.
3. At the sqlcmd prompt, type the following, and then press ENTER:
   
   ALTER LOGIN SQLLogin WITH DEFAULT_DATABASE = AvailDBName
   Note AvailDBName is a placeholder for the name of the existing database that can be accessed by the SQL Server login in the instance.
4. At the sqlcmd prompt, type GO, and then press ENTER.