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

SQL Server 2014 SP1

Posted: May 19, 2015 | Author: | Filed under: Microsoft SQL Server, Microsoft SQL Server 2014 | Tags: , | Leave a comment

First off the SQL Server 2014 SP1 installer complains that fdhost.exe is running…

http://www.smooth1.co.uk/installs/dbinstalls.html#3.1.3 – Updated to also mention 2014 SP1!

Cumulative Update 7 for SQL Server 2014

Posted: April 24, 2015 | Author: | Filed under: Microsoft SQL Server, Microsoft SQL Server 2014 | Tags: , | Leave a comment

https://support2.microsoft.com/kb/3046038/en-us?sd=rss&spid=17645&utm_content=bufferab24d&utm_medium=social&utm_source=twitter.com&utm_campaign=buffer

Cumulative Update 7 for SQL Server 2014

Interesting fixes include:

https://support2.microsoft.com/kb/3042544

FIX: A query that requires nested loops join takes longer to complete in SQL Server 2014

https://support2.microsoft.com/kb/3042370
An AlwaysOn secondary replica crashes or raises error 3961 when the AlwaysOn database has CLR UDT in SQL Server 2014

https://support2.microsoft.com/kb/3042135
FIX: Access violation and “No exceptions should be raised by this code” error occur when you use SQL Server 2012 or SQL Server 2014

https://support2.microsoft.com/kb/3042077
FIX: MDX query returns incorrect results when you run it with subselect after an UPDATE CUBE statement in SSAS 2012 or SSAS 2014

https://support2.microsoft.com/kb/3041859
FIX: Performance issue occurs when you use sqlncli11.dll ODBC driver to access SQL Server in an application

https://support2.microsoft.com/kb/3040675
FIX: Data-driven subscription with email notification shows “0 errors” when email delivery fails in SSRS 2012 or SQL Server 2014

https://support2.microsoft.com/kb/3040804
FIX: “Access Denied” error occurs when you run an XML validation task in SSIS 2012 or SSIS 2012

https://support2.microsoft.com/kb/3053664
FIX: Improved memory management for columnstore indexes to deliver better query performance in SQL Server 2014

https://support2.microsoft.com/kb/3041476
FIX: SQL Server takes long time to open database after recovery to add or remove a data file in SQL Server 2014

https://support2.microsoft.com/kb/3045410
FIX: A query that uses the DECRYPTBYKEY function takes longer to be completed in SQL Server 2014

https://support2.microsoft.com/kb/3048752
FIX: A SELECT query run as a parallel batch-mode scan may cause a deadlock situation in SQL Server 2014

https://support2.microsoft.com/kb/3055799
FIX: “Cannot bulk load” error when you run a query that contains INSERT statements when trace flags 4199 and 610 are enabled on a server that is running SQL Server 2014

https://support2.microsoft.com/kb/3052404
FIX: You cannot use the Transport Layer Security protocol version 1.2 to connect to a server that is running SQL Server 2014

https://support2.microsoft.com/kb/3052244
FIX: Incorrect result when the “must be unique” action is evaluated for an attribute by using SQL Server 2014 Master Data Services

https://support2.microsoft.com/kb/3051798
FIX: Table values change after dividing and partitioning the table in SQL Server Analysis Services

https://support2.microsoft.com/kb/3051663
FIX: “8156: A database error has occurred” when you run a business rule in SQL Server 2014 Master Data Services

https://support2.microsoft.com/kb/3048856
FIX: Error 3624 occurs when you execute a query that contains multiple bulk insert statements in SQL Server 2014

SQL Server 2014 Service Pack 1 release information

Posted: April 15, 2015 | Author: | Filed under: Microsoft SQL Server, Microsoft SQL Server 2014 | Tags: , | Leave a comment

https://support.microsoft.com/en-us/kb/3018269

SQL Server 2014 Service Pack 1 release information

Highlights are:

https://support.microsoft.com/en-us/kb/3044954

FIX: Access violation occurs on the I/O Completion routine when you enable AutoClose in SQL Server 2014

https://support.microsoft.com/en-us/kb/2963404

FIX: Rollback causes database to go to suspect mode in SQL Server 2012 or SQL Server 2014

https://support.microsoft.com/en-us/kb/2806979

FIX: Poor performance when you run a query against a table in an RCSI-enabled database in SQL Server 2012 or 2014

https://support.microsoft.com/en-us/kb/2965035

SSAS 2012 logging feature improvement in SQL Server 2012 Service Pack 2 and SQL Server 2014 Service Pack 1- displays machine information and product version information on startup

https://support.microsoft.com/en-us/kb/2963412

New DMF sys.dm_fts_index_keywords_position_by_document in SQL Server 2012 SP2 and SQL Server 2014 SP1

https://support.microsoft.com/en-us/kb/3051521

FIX: “Database ‘model’ cannot be opened” error when you restart SQL Server after tail-log backup for model database

https://support.microsoft.com/en-us/kb/2963382

FIX: Can’t connect to the server when you run a cube creation MDX query in SSAS 2012 or SSAS 2014

https://support.microsoft.com/en-us/kb/2963384

FIX: SQL Server crashes when the log file of tempdb database is full in SQL Server 2012 or SQL Server 2014

https://support.microsoft.com/en-us/kb/3044958

FIX: Rollback recovery on a snapshot fails when you run DBCC CHECKDB and then SQL Server shuts down unexpectedly

https://support.microsoft.com/en-us/kb/3030619

FIX: Incorrect data returned when you query the DATE column in SQL Server 2014

https://support.microsoft.com/en-us/kb/3020112

FIX: Non-yielding error occurs when you execute DML statements in SQL Server 2014

https://support.microsoft.com/en-us/kb/3027860

Error 17066 or 17310 during SQL Server startup

https://support.microsoft.com/en-us/kb/3044953

FIX: Non-yielding scheduler issue when you write pages to buffer pool extension file in SQL Server 2014

https://support.microsoft.com/en-us/kb/3029977

FIX: OS error 665 when you execute DBCC CHECKDB command for database that contains columnstore index in SQL Server 2014

https://support.microsoft.com/en-us/kb/3021757

FIX: Duplicate sequence value is generated when you run sp_sequence_get_range in parallel with NEXT VALUE FOR function

https://support.microsoft.com/en-us/kb/3026082

FIX: SOS_CACHESTORE spinlock contention on system table rowset cache causes high CPU usage in SQL Server 2012 or 2014

https://support.microsoft.com/en-us/kb/3034679

FIX: AlwaysOn availability groups are reported as NOT SYNCHRONIZING

https://support.microsoft.com/en-us/kb/3035165

FIX: Error 8646 when you run DML statements on a table with clustered columnstore index in SQL Server 2014

https://support.microsoft.com/en-us/kb/3044952

FIX: Issues when you enable the buffer pool extension feature in SQL Server 2014

https://support.microsoft.com/en-us/kb/3044519

FIX: Query performance issues when new cardinality estimator is enabled in SQL Server 2014

https://support.microsoft.com/en-us/kb/3029825

FIX: DBCC CHECKDB and DBCC CHECKTABLE take longer to run when SQL CLR UDTs are involved in SQL Server 2012 or SQL Server 2014

Also the following VSTS bug numbers are fixed:

1957464 RESTORE HEADERONLY for an Encrypted Backup file of the Database does not show whether the backup is encrypted or not. After you apply SP1, the output of RESTORE HEADONLY will include three additional columns: KeyAlgorithm, EncryptorThumbprint and EncryptorType that can give additional details about the encrypted backup.

2580641 Invalid query syntax in stored procedure when you drop an article that is published in
more than one publication.

2580651Peer-to-peer (P2P) conflict messages include Table Name, Primary Key (s), Current Version, Pre Version and Post Version.

2580686    Update-Update conflicts in P2P replication when updates are made to text column by using UPDATE or WRITE.

2580693    Incorrect results when you work with block mode unary operators.

2580746    DPM’s full backup on the server with AlwaysOn secondary will be converted to copy_only backup

2581191    Merge Replication logging improvement:

2581192    Transaction Replication logging improvement:

2581382    Provides platform information in the log of SSRS 2014

3143194    LOB reads are shown as zero when “SET STATISTICS IO” is on during executing a query with clustered columnstore index.

3731350    When you use features that rely on log pool cache (for example, Always On) on systems with multiple sockets, you may notice high values for “log write waits” counter. Traceflag T9024 is not longer needed to activate the fix.

3732057    When your instance of SQL Server is handling thousands of connection resets because of connection pooling, performance problems occur when database lock activity increases in SQL Server. Traceflag T1236 is not longer need to activate the fix

3909490    Error 4819 occurs for some combination of database schema and data when the fast load context (TF610) for bulk load and TF4199 are enabled.

There are also some community connect items fixed, many ssms related.

https://connect.microsoft.com/SQLServer/feedback/details/735543/ssms-2012-express-create-a-new-db-from-another-dbs-backup-on-the-same-instance-actually-destroyed-the-original-db

https://connect.microsoft.com/SQLServer/feedback/details/745566/smo-2012-disregards-default-constraint

https://connect.microsoft.com/SQLServer/feedback/details/791929/system-assertion-failed-error-with-insert-into-partitioned-view-with-trigger

https://connect.microsoft.com/SQLServer/feedback/details/785151/show-actual-query-plan-causes-error

https://connect.microsoft.com/SQLServer/feedback/details/773710/unable-to-perform-full-text-ddl-after-reverting-from-db-snapshot

https://connect.microsoft.com/SQLServer/feedback/details/805659/sql-statement-is-nested-too-deeply

Chris Adkin Super Scaling Singleton Insert SQL Server 2014

Posted: March 19, 2015 | Author: | Filed under: Microsoft SQL Server, Microsoft SQL Server 2014 | Tags: , | Leave a comment

Chris Adkin Super Scaling Singleton Insert SQL Server 2014

http://chrisadkin.org/2015/02/19/super-scaling-singleton-inserts/

SQL Server 2014 CU6 is out

Posted: February 22, 2015 | Author: | Filed under: Microsoft SQL Server, Microsoft SQL Server 2014 | Tags: , | Leave a comment

SQL Server 2014 CU6 is out http://blogs.msdn.com/b/sqlreleaseservices/archive/2015/02/17/cumulative-update-6-for-sql-server-2014-rtm.aspx

FIX: Sequence object generates duplicate sequence values when SQL Server 2012 or SQL Server 2014 is under memory pressure

http://support.microsoft.com/kb/3011465

FIX: Duplicate sequence value is generated when you run sp_sequence_get_range in parallel with NEXT VALUE FOR function

http://support.microsoft.com/kb/3021757

FIX: The transaction isolation level is reset incorrectly when the SQL Server connection is released in SQL Server 2014

http://support.microsoft.com/kb/3025845

FIX: Access violation occurs when you delete rows from a table that has clustered columnstore index in SQL Server 2014

http://support.microsoft.com/kb/3029762

FIX: OS error 665 when you execute DBCC CHECKDB command for database that contains columnstore index in SQL Server 2014

http://support.microsoft.com/kb/3029977

FIX: Incorrect data returned when you use DATE data type as a qualifier in a  query with a clustered columnstore store index in SQL Server 2014

http://support.microsoft.com/kb/3030619

FIX: Memory leak occurs when you run DBCC CHECKDB against a database in SQL Server 2014

http://support.microsoft.com/kb/3034615

FIX: The value of statement_start_offset column is incorrect in DMV sys.dm_exec_requests in SQL Server 2014

http://support.microsoft.com/kb/3036328

SQL Relay London 30 October 2014

Posted: October 30, 2014 | Author: | Filed under: Microsoft SQL Server, Microsoft SQL Server 2014 | Leave a comment

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.

Posted: October 27, 2014 | Author: | Filed under: Microsoft SQL Server, Microsoft SQL Server 2014 | Leave a comment

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

Posted: October 24, 2014 | Author: | Filed under: Microsoft SQL Server, Microsoft SQL Server 2014 | Leave a comment

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.

Posted: October 16, 2014 | Author: | Filed under: Microsoft SQL Server, Microsoft SQL Server 2014 | Leave a comment

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.

← Older Entries