Forcing garbage collection with in memory oltp

Posted: September 27, 2015 | Author: | Filed under: Microsoft SQL Server, Microsoft SQL Server 2014, Microsoft SQL Server 2016 | Tags: , , | Leave a comment

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]

Advertisement

19th September 2015 SQL Saturday Denmark Questions.

Posted: September 20, 2015 | Author: | Filed under: Microsoft SQL Server, Speaker Answers | Tags: , | Leave a comment

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

Posted: September 17, 2015 | Author: | Filed under: Microsoft SQL Server | Tags: | Leave a comment

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

Posted: September 10, 2015 | Author: | Filed under: speaking | Tags: | Leave a comment

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

Posted: September 4, 2015 | Author: | Filed under: Microsoft SQL Server | Tags: | Leave a comment

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

Posted: September 1, 2015 | Author: | Filed under: Microsoft SQL Server 2016 | Tags: | Leave a comment

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;