Forcing garbage collection with in memory oltp

To force manual garbage collection:

  1. First we check the status of the checkpoint files:

select container_id,state_desc,lower_bound_tsn,upper_bound_tsn from sys.dm_db_xtp_checkpoint_files

Anything that is MERGE TARGET is pending a merge.

In SQL Server 2014 we can run:

sys.sp_xtp_merge_checkpoint_files database_name, @transaction_lower_bound, @transaction_upper_bound

to force a merge, manual merges are not possible in SQL Server 2016!

2. Once the merge is complete we have to run a checkpoint to ensure everything is persisted on disk.

3. Next we need to run a log backup otherwise dm_db_xtp_checkpoint_files.state_desc would be ‘WAITING FOR LOG TRUNCATION’

4. Finally we can force garbage collection using

sys.sp_xtp_checkpoint_force_garbage_collection [[ @dbname=database_name]

Advertisements

19th September 2015 SQL Saturday Denmark Questions.

What editions is replication in?

As per https://msdn.microsoft.com/en-us/library/cc645993.aspx#Replication replication is supported in Standard Edition, this does not include Oracle publishing or Peer to Peer transactional replication.

How can I read the transaction log?

To read the current logs:

SELECT * FROM fn_dblog (NULL, NULL)

The parameters to fn_dblog are StartLSN and EndLSN in 3 part decimal format

To read from a log backup:

SELECT * FROM
fn_dump_dblog (
NULL, NULL, N’DISK’, 1, N’D:\LogBackups\Justdave_Log1.bak’,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);

The parameters are:

  • Start LSN starting with 0x
  • End LSN starting with 0x
  • Device Type ‘DISK’ or ‘TAPE’ deffault DISK
  • Seqnum – used to indentify a backup within a backup device, usually 1
  • Filename of log backup on disk
  • the name of a backup file in the default backup directory for the SQL Server instance
  • The remaining parameters can be used to specify multiple media families in a media set used for a log backup i.e. the filenames of the remaining stripes if more than 1 stripe was used for the log backup

Warning: As per http://www.sqlskills.com/blogs/paul/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn/

There is a bug where using these functions to read log information will result in a new hidden SQLOS scheduler being created and up to three threads, which will not go away (and will not be reused) until a server restart. This is fixed in 2012 SP2 and 2014.


Reoganizing SQL Server internal tables

How to reorganize the internal tables in SQL Server is not immediately obvious:

For example to see the internal tables used for Event Notification:

SELECT q.name AS queue_name
,q.object_id AS queue_id
,s.name AS internal_schema_name
,it.name AS internal_table_name
,it.object_id AS internal_table_id
FROM sys.internal_tables AS it
JOIN sys.service_queues  AS  q ON it.parent_id = q.object_id
JOIN sys.schemas AS s ON it.schema_id = s.schema_id
WHERE it.internal_type_desc = ‘QUEUE_MESSAGES’;

queue_name    queue_id    internal_schema_name    internal_table_name    internal_table_id
QueryNotificationErrorsQueue    1003150619    sys    queue_messages_1003150619    1019150676
EventNotificationErrorsQueue    1035150733    sys    queue_messages_1035150733    1051150790
ServiceBrokerQueue    1067150847    sys    queue_messages_1067150847    1083150904

alter index ALL on sys.queue_messages_1003150619 REORGANIZE;

Msg 1088, Level 16, State 9, Line 11
Cannot find the object “sys.queue_messages_1003150619” because it does not exist or you do not have permissions.

Find we have to connect using the DAC – connect as a sysadmin user changing the servername to start with admin:

alter index ALL on sys.queue_messages_1003150619 REORGANIZE;

— Msg 1914, Level 16, State 3, Line 1
— Index cannot be created on object ‘sys.queue_messages_1003150619’ because the object is not a user table or view.

So how do we reorgnize this then?

DBCC DBREINDEX(‘sys.queue_messages_1003150619’)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

NOTE: We included the schema name ‘sys’ in the command!

As per https://msdn.microsoft.com/en-gb/library/ms187799.aspx these internal tables are used for:

QUEUE_MESSAGES

XML_INDEX_NODES

FULLTEXT_CATALOG_FREELIST

FULLTEXT_CATALOG_MAP

QUERY_NOTIFICATION

SERVICE_BROKER_MAP

EXTENDED_INDEXES (such as a spatial index)

FILESTREAM_TOMBSTONE

CHANGE_TRACKING

TRACKED_COMMITTED_TRANSACTIONS


What speakers (and attendees) expect from an event

Speakers expect the following from an event

  • Signage at the event to show the event is being held there
  • Signage at the event to guide you to the reception desk and show this is the reception desk
  • Inform speakers if the projectors are vga or hdmi
  • Remind speakers they need power adapters if the power sockets are not 3 pin sockets!
  • Mention if there is a speakers room or not
  • Conferences should tell you the ratio of the projectors (including resolution) so you can optimize your decks.
  • If you want me to use your PPT template. make it usable
  • If only we speakers would band together, use their front and back material, but our own internal slides….

Microsoft White Paper on Columnstores

Interestiing white paper (from Microsoft) on columnstores

https://www.google.co.uk/url?sa=t&rct=j&q=&esrc=s&source=web&cd=3&cad=rja&uact=8&ved=0CC0QFjACahUKEwiv0_G1kt3HAhVCXCwKHd8qAp0&url=http%3A%2F%2Fdownload.microsoft.com%2Fdownload%2FD%2F0%2F0%2FD0075580-6D72-403D-8B4D-C3BD88D58CE4%2FSQL_Server_2016_In_Memory_OLTP_and_Columnstore_Comparison_White_Paper.pdf&usg=AFQjCNG3Mm2Au5a9Tn3qISeOQ6M8ugJxyA&bvm=bv.101800829,d.bGg


SQL Server 2016 CTP 2.3 Row Level Security for Memory Optimized Tables

CTP 2.3 supports Row Level Security for Memory Optimized Tables.

The function use with the security policy must be a natively compiled inline table valued function.

CREATE TABLE Sales
(
OrderID int PRIMARY KEY NONCLUSTERED,
SalesRep sysname,
Product varchar(10),
Qty int
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY);

INSERT Sales VALUES
(1, ‘Sales1’, ‘Valve’, 5),
(2, ‘Sales1’, ‘Wheel’, 2),
(3, ‘Sales1’, ‘Valve’, 4),
(4, ‘Sales2’, ‘Bracket’, 2),
(5, ‘Sales2’, ‘Wheel’, 5),
(6, ‘Sales2’, ‘Seat’, 5);

SELECT * FROM Sales;

CREATE USER Manager WITHOUT LOGIN;
CREATE USER Sales1 WITHOUT LOGIN;
CREATE USER Sales2 WITHOUT LOGIN;

GRANT SELECT ON Sales TO Manager;
GRANT SELECT ON Sales TO Sales1;
GRANT SELECT ON Sales TO Sales2;

CREATE SCHEMA Security;

DROP FUNCTION Security.fn_securitypredicate;

— For in-memory need Natively Complied Function

CREATE FUNCTION Security.fn_securitypredicate(@SalesRep AS sysname)
RETURNS TABLE
WITH NATIVE_COMPILATION,SCHEMABINDING
–WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @SalesRep = USER_NAME() OR USER_NAME() = ‘Manager’;

DROP SECURITY POLICY SalesFilter;
CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE Security.fn_securitypredicate(SalesRep)
ON dbo.Sales
WITH (STATE = ON);

EXECUTE AS USER = ‘Sales1’;
SELECT * FROM Sales;
REVERT;

EXECUTE AS USER = ‘Sales2’;
SELECT * FROM Sales;
REVERT;

EXECUTE AS USER = ‘Manager’;
SELECT * FROM Sales;
REVERT;

DROP SECURITY POLICY SalesFilter;
DROP FUNCTION Security.fn_securitypredicate;
DROP TABLE Sales;