Optimizing database access and diving into .Net SqlClient

1. Optimizing database access and diving into .NetSqlClient Mladen Prajdić SQL Server MVP mladenp@gmail.com @MladenPrajdic

2. About me Welcome to Slovenia The sunny side of Alps

3. DisclaimerI use SELECT * in demos only forbrevity 

4. Agenda • Building foundation... Client Data Access • New exciting stuff! AsyncSqlDataReader • Consistency is the key! TransactionScope • Reduce, Reuse, Recycle! Connection Pooling • Make it go faster! Batching Commands • What’s it doing? Statistics • Multiple interleaves? MARS

5. Correct Connection Use using (SqlConnection conn = newSqlConnection(_connString)) { conn.Open(); // dbaccesscodehere // return data reader or objects }

6. Client Data AccessBuilding the foundation...

7. TDS Data Flow “Done” token Result set “Done” token Result set “Done” token Result set using (SqlCommandcmd= newSqlCommand("SELECT * FROM Table1; SELECT * FROM Table2", conn)) { SqlDataReaderrdr= cmd.ExecuteReader(CommandBehavior.CloseConnection); // return the list of data objects returnCreateListOfBusinessObjects(rdr); // or return the SqlDataReader itself returnrdr; }

8. TDS Data Flow “Done” token Result set “Done” token Result set “Done” token Result set Row Data Row Data Row Data “Row” token “Row” token “Row” token

9. TDS Data Flow “Done” token Result set “Done” token Result set “Done” token Result set Row Data Row Data Row Data “Row” token “Row” token “Row” token Column Data Column Data Column Header Column Data Column Header Column Header Small datatype INT Large datatype NVARCHAR(MAX) Medium datatype CHAR(50)

10. SqlDataReader Data Access • Default access (Non-Sequential) • Sequential access // default = non-sequentialaccess varrdr=cmd.ExecuteReader(); // sequentialaccess varrdr=cmd.ExecuteReader(CommandBehavior.SequentialAccess);

11. Non-Sequential (Default) Access 1 2 0 Column Data Column Data Column Header Column Data Column Header Column Header 1 2 0 Column Data Column Data Column Header Column Data Column Header Column Header We can go back and read all previous columns’ data

12. Non-Sequential (Default) Access • The full row is stored in client memory • Move back and forward in the row • Possible scalability issues • Possible Out of Memory exceptions

13. Sequential Access 1 2 0 Column Data Column Data Column Header Column Data Column Header Column Header 1 2 0 Column Data Column Data Column Header Column Data Column Header Column Header ERROR Forward reading only. Trying to go back throws an error.

14. Sequential Access • Only the current value is stored in client memory • Move only forward in the row • Get* methods for getting data • GetBytes, GetCharsfor BLOBs • Save to a stream by reading it in chunks // get first column value that is int rdr.GetInt32(0); // get second column value that is binary byte[] buffer=newbyte[100]; // loop this to get the whole column out in chunks rdr.GetBytes(0, 100, buffer, 0, buffer.Length); // save the byte buffer to file or something

15. Sequential Access • Full streaming support in .Net 4.5 • GetStream, GetTextReader, GetXmlReader • No more chunking

16. Sequential Access SqlCommandreadCmd=// get varbinary(MAX) from source table newSqlCommand("SELECT BinData FROM Source", readConn); SqlCommandwriteCmd=// insert varbinary(MAX) into target table newSqlCommand("INSERT INTO Target(BinData) VALUES (@BinData)", writeConn); // Size is set to -1 to indicate "MAX" SqlParameterstreamParameter= writeCmd.Parameters.Add("@BinData", SqlDbType.Binary, -1); SqlDataReaderreaderSource= readCmd.ExecuteReader(CommandBehavior.SequentialAccess); while (readerSource.Read()) { // Grab a stream to the binary data in the source database StreamdataStream=readerSource.GetStream(0); // Set the parameter value to the stream source that was opened streamParameter.Value=dataStream; // send the data to the target database writeCmd.ExecuteNonQuery(); }

17. AsyncSqlDataReaderNew exciting stuff!

18. Old “Run in Background” Ways SqlCommandcmd=newSqlCommand("SELECT * FROM Table", conn); // either this or a background worker IAsyncResultresult=cmd.BeginExecuteReader(); // for the reader to complete while(!result.IsCompleted) { // do UI operations without problems } using(SqlDataReaderreader=cmd.EndExecuteReader(result)) { // show readerresults ShowDbOperationResult(reader); }

19. What exactly is the new stuff then? • Make C# async programming easier • Since .Net 4.5 • Supports easy cancellation • Uses the new async/await keywords • Reader’s *Async methods privateasyncvoidCustomEventHandler(object sender,EventArgse) { textBox.Text="Start long operation."+Environment.NewLine; // await returns control to the caller // this means that the UI won't be blocked // since CPU is free to do its stuff doubleduration=awaitMyLongIOBoundOperation(); // finish op and show duration in UI textBox.Text="Completed in "+ duration +" sec"; }

20. What is it good for? • Make code async without complex threading code • No threads created by default • Use default for IO-bound workload • Use Task.Run to create background thread for CPU-bound workload • Supported by Entity Framework 6 • For more in depth details on this, search for “Asynchronous Programming with Async and Await” • http://msdn.microsoft.com/en-us/library/hh191443.aspx

21. What to use when? • Prefer NextResultAsync in either access modes • TDS tokens between result sets are processed in async TDS Tokens TDS Tokens Result set Result set Result set Async Async

22. What to use when? • Prefer ReadAsyncin either access modes • TDS tokens betweenmultiple packets are processed in async Single Row TDS Tokens Data Packet Data Packet Data Packet TDS Tokens Async Async

23. What to use when? • Use IsDBNullandGetFieldValue<T> • If ReadAsync() in Default (Non-Sequential) mode • Since data is already in the client buffer • IsDBNullAsyncandGetFieldValueAsync<T> • Sequential mode • target column is large • needed to read past large columns • rdr.GetStream(i).CopyToAsync(outputStream) • for huge columns in Sequential mode

24. DEMO

25. TransactionScopeConsistency is the key!

26. TransactionScope using (TransactionScopetxScope= newTransactionScope()) { using(SqlConnection conn2012AW = newSqlConnection(_connString2012AW)) { // do stuff } txScope.Complete(); }

27. TransactionScope • Easy to use • Keeps code simple • Great transaction management out of the box • MSTDC escalation • Always, unless connecting to the same database with non concurrent connections • sys.dm_tran_active_transactions.transaction_uow • Default isolation level is SERIALIZABLE

28. DEMO

29. Connection PoolingReduce, Reuse, Recycle!

30. Connection Pooling • Connection pools created • per process • per application domain • per connection string • per Windows identity (if integrated security) • Default max pool size = 100 • Audit Login, Audit Logout Events • EventSubClass • 1 = Nonpooled • 2 = Pooled

31. Connection Pooling Resets • sp_reset_connection • Resets the connection for reuse • A long list of what it does on the next slide for reference • Does NOT reset the isolation level • Always explicitly specify the isolation level

32. Connection Pooling Resets • All error states and numbers (like @@error) • Stops all EC's (execution contexts) that are child threads of a parent EC executing a parallel query • Waits for any outstanding I/O operations that is outstanding • Frees any held buffers on the server by the connection • Unlocks any buffer resources that are used by the connection • Releases all allocated memory owned by the connection • Clears any work or temporary tables that are created by the connection • Kills all global cursors owned by the connection • Closes any open SQL-XML handles that are open • Deletes any open SQL-XML related work tables • Closes all system tables • Closes all user tables • Drops all temporary objects • Aborts open transactions • Defects from a distributed transaction when enlisted • Decrements the reference count for users in current database which releases shared database locks • Frees acquired locks • Releases any acquired handles • Resets all SET options to the default values • Resets the @@rowcount value • Resets the @@identity value • Resets any session level trace options using dbcctraceon()

33. DEMO

34. Batching CommandsMake it go faster!

35. Batching Commands • Optimizing network round trips • Consider Bulk Insert if possible • Streaming data insert • SqlBulkCopy Class • 10k inserts = 10k round trips • 1000 x 10 batches = 10 round trips • 1000 CSV SQL statements in one command • Or… a hidden gem 

36. DEMO

37. StatisticsWhat’s it doing?

38. Client Statistics • Enable per connection • Cumulative • Good for tracking the actual db access from app • Save to a queue, process off-peak • Analyze historical access data

39. DEMO

40. MARSMultiple interleaves?

41. Multiple Active Result Sets (MARS) • Statement multiplexing/interleaving • SELECT, FETCH • RECEIVE, READTEXT • BULK INSERT / BCP • Other statements must complete • Transaction savepoints aren’t allowed • Usually used for SELECT N+1 scenarios

42. DEMO

43. ?