SQL Server 2014 CU4 is out – new trace flag.

SQL Server 2014 CU4 is out – http://support.microsoft.com/kb/2999197

http://phoebix.com/2014/10/21/cumulative-updates-sql-server-2014-rtm-cu4/ also mentions a new trace flag

For clustered column store index

” This fix introduces a new trace flag 9348 that helps in deciding whether or not a bulk insert is required. If the estimated number of rows is greater than 102400 than a bulk insert is used. If less than 102400 then SQL Server does not use a bulk insert.”


SQL in the City London 24 October 2014

Slides for my lightening talk are at  http://www.smooth1.co.uk/sqlserver2014/SITC14_Slides.pptx

Items I learnt from SQL Server tips and tricks – Ike Ellis

Top right of most MS product is a quick launch search box similar to Windows 8 menu search.

Redgate SQL Prompt – in Powershell press Ctrl+K Ctrl+Y to prettify sql code

SQL Server Central has a code prettifier http://extras.sqlservercentral.com/prettifier/prettifier.aspx

WordPress has a code Formatter http://en.support.wordpress.com/code/posting-source-code/

select j.job_location,c.client_name 
from job j,client c 
where j.job_id=1 and 

October 13 Microsoft SQL Relay Reading.

Interesting talks at Microsoft SQL Relay Reading, the first SQL Relay event this year.

The Introduction to OLTP In memory from Amanda Ford and another lady from Microsoft covered some items I had not seen before:

1. The in-memory oltp filegroup preallocates more pages that you make think when you first create the filegroup – this will increase the size of your backup. A database with just the in-memory filegroup with no tables will result in a 5GB backup!

2. The in-memory filegroup is made up of pairs of files called checkpoint pairs – a datafile and a delta file.
The datafile contains inserts and the delta file deleted rows.

3. You want to keep 30% of your buckets free.

4. In-memory OLTP uses Multi Version Concurrency Control (MVCC), rows have a start and end timestamp and the current version has an end timestamp of infinity. There are a maximum of 4096 checkpoint pairs hence maximum is 250GB for in-memory data including previous versions.

5. To list the current checkpoint files query sys.dm_db_xtp_checkpoint_files.

6. New data files are listed as “UNDER CONSTRUCTION” under a checkpoint occurs.

7. Trace flag 1851 disables the automerge functionality for in-memory oltp.

8. To manually merge checkpoint files use call sys.sp_xtp_merge_checkpoint_files, status goes to PENDING, checkpoint.

9. Checkouts occur anyway every 512MB of log.

10. Checkpoint files can have a status of “REQUIRED FOR BACKUP”

11. Merge requests can be see by quering sys.dm_db_xtp_merge_requests, once the merge is complete the request_state_desc changes to installed.

12. Garbage collection can be forced by calling sys.sp_xtp_checkpoint_force_garbage_collection similar to sp_filestream_force_garbage_collection.

13. In-memory OLTP limitations include no MERGE statements, no EXISTS query, no MARG (Multiple Active Results Sets), no nested queries, no parallel plans.

14. To determine tables which are suitable to move to In-memory OLTP use AMP (Access,Migrate,Repeat).
There is a new Data Collection Set for this, Transaction Performance collect set and AMR Reports.

Cardiff User Group Talk June 24 2014 Speaking Questions

Answer to Questions for Cardiff User Group Talk Jun 24 2014.

1. Backup Encryption in 2014 – Is this an Enterprise only feature?

SQL Server Express and SQL Server Web do not support encryption during backup. However restoring from an encrypted backup to an instance of SQL Server Express or SQL Server Web is supported.


2. SSD Buffer Pool Extension – 32x vs 4x how much addition memory is used inside SQL Server?

There a 2 structures per page in the bufferpool, 1 ‘BUF’ header (~100 bytes,corresponds to sys.dm_os_buffer_descriptors which is a list of BUF structures for all hashed buffers). and the actual page itself.

For pages in the bufferpool extension file they only need a ‘BUF’ structure in memory which contains file_id,page_id and is_in_bpool_extension =1. This points to the actual page in the BPE file!

Therefore only additonal small ‘BUF’ header structures are needed for a large bufferpool file.

NOTE: Bufferpool memory can also be seen via sys.dm_os_memory_clerks type=’MEMORYCLERK_SQLBUFFERPOOL’.

select type,sum(pages_kb) pages_kb

from sys.dm_os_memory_clerks

group by type


order by type

3. SSD or fast disk?

SSD may well be quicker even than fast disk as it can be larger then the cache on a disk array.

Fast Disks will help with writes which SSDs do not.

Database product installation FAQ

Database product installation FAQ http://www.smooth1.co.uk/installs.html

Currently SQL Server,DB2 10.5,Oracle,SAP ASE 16.0,Informix 12.10.FC4

Currently reading…


DBA Survivor – Become a Rockstar DBA .


All DMVs are under sys schema.



Wait stats for memory



– LCM_M_*




Wait stats for CPU




SQL Profile





SQL Server 2012 Tabular Data Model

Documented at http://msdn.microsoft.com/en-us/library/hh212945.aspx

Securing the Tabular BI Semantic Model whitepaper http://msdn.microsoft.com/en-us/library/jj127437.aspx

Securing your tabular model http://blogs.msdn.com/b/cathyk/archive/2011/11/21/securing-your-tabular-model.aspx

Getting started with Tabular Model in SQL Server 2012 http://www.mssqltips.com/sqlservertip/2821/getting-started-with-tabular-model-in-sql-server-2012–part-1/

Roles (SSAS Tabular) http://msdn.microsoft.com/en-us/library/hh213165.aspx – includes row filters and dynamic security.

Perspectives – like views http://msdn.microsoft.com/en-us/library/hh230816%28v=sql.110%29.aspx

BISM – Tabular Security Model http://paultebraak.wordpress.com/2011/09/04/bism-%E2%80%93-tabular-security-model – more on row filters.

Implement Dynamic Security by Using Row Filters http://msdn.microsoft.com/en-us/library/hh479759%28v=SQL.110%29.aspx

(DMV Question)