SQL Relay London 30 October 2014

My slides from today are at http://www.smooth1.co.uk/sqlserver2014/2014NewFeatures.zip

Notes from Christian Boltons talk Understanding Parallelism

Troubleshoot at task level rather than session level.

session->task->worker thread->scheduler->Logical CPU

Query cost value is the same across all sql editions

The decision to use parallelism is at runtime not optimization time e.g. if not enough worker threads

To get the average query cost look at the the xml plan from the plan cache

Gather streams maintains sort order

Other parallelism operators are Repartition (Many to Many) and Distribute (One to Many)

Each parallel task is really a producer and a consumer as seen in dm_os_waiting_tasks

At startup -P option can adjust the number of schedulers to allow you to see how the optimizer will behave even on hardware which does not have that number of cpus, check from dm_os_schedulers.

Bitmap operators only appear in a parallel plan and can be used as a predicate to filter against another query plan leg when joining. This means estimates rows will NOT match actual rows as filtering is done at runtime hence is not in the estimate.

Does the query processor wait for the full bitmap to be built?
With parallism 8 do you get 8 bitmaps?

Do not set degree of parallelism 1 at the server level and not think about the impact – affects dbcc checkdb,index rebuilds.

Check dm_os_sys_info. Each worker thread takes up 2MB stack outside the sql server memory.

CXPACKET wait just means 1 of the parallel tasks is finished and waiting for the others to complete – not necessarily bad, just means lots of long running parallel queries.

Can adjust query cost threshold for parallelism,default is 5, increase to reduce number of parallel plan executions if this is flooding the server.

Claire Mora – T-SQL Performance Issues : What you should know to find and resolve them

Consider sql cruise, may be next year!

Use a Row Generator to create sample test data

Avoid parallel plans with a sort.

For complex queries consider Divide and Conquer http://www.sqlservercentral.com/articles/T-SQL/107611/

Data Bubbles – thick lines in ssms query plan

Quirky Update – http://www.sqlservercentral.com/articles/T-SQL/68467/

Test against all Version and Editions planned for deployment.

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 
j.client_id=c.client_id

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.