TOP10 DEV SKILLS TO MAKE YOUR DBA HAPPY

1. TOP10 DEV SKILLS TO MAKE YOUR DBA HAPPY Kevin Kline Director of Engineering Services, SQL Sentry SQL Server MVP since 2004 Twitter, FB, LI: KEKline Blog: http://KevinEKline.com, http://ForITPros.com

2. Agenda Happy & Helpful DBA!

3. #1. Tools: Everybody’s Best Friend • Books Online • Regular updates available • Microsoft® SQL Server™ Management Studio (SSMS) • Query plans • Object scripting • Quick and dirty data browsing • Good source control integration

4. #2. Execution Plans and Traces • SQL Server processes a query through an execution plan aka a query plan • Learn how to compare in SSMS • Learn how to watch it happen with SQL Profiler • Query performance may be misleading • Should evaluate on comparable data sizes • Different CPU, Disk, caching, etc. • Evaluate the query “Cost”

5. TECHNIQUES

6. #3. Test Harness & Testing • Clear your caches • Measure: • Total elapsed time • Individual statement time • IO load • Facilitates automation of execution plans • Get to know the DMV sys.dm_exec_query_stats

7. #4. Query Performance Tips • Indexes! Statistics! • WHERE clauses: • Don’t put a function around an indexed column • Can ensure or prevent index usage • Use EXISTS to check for existence • Be careful: • NOT IN clauses • Temp variables • User-defined functions

8. #5. Sets Versus Rows • Microsoft® Visual C#®, Visual Basic®, Visual C++®, etc. are procedural languages • Tell the computer what to do • SQL is a declarative language • Tell the computer what you want • Learn to think in terms of sets: • One pass through a table • Let the computer process joins and filters • Loops and cursors for specific use-cases only

9. #6. Connecting to SQL Server • Use the SqlClient namespace in Microsoft® .NET • Use Connection Pooling • On by default in .NET • Set Application Name in connection string • Helps troubleshooting and monitoring • Slightly degrades connection pooling • SqlDataReaders are much faster than DataTables • Update through stored procedures or SQL statements

10. Example Code to Set the App Name SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder(); csb.DataSource = “L40\YUKON”; csb.IntegratedSecurity = true; csb.InitialCatalog = "AdventureWorks"; csb.ApplicationName = "MyDemoApp"; string connectionString = csb.ToString(); SqlConnection conn = new SqlConnection(); conn.ConnectionString = @“Data Source=L40\YUKON; Initial Catalog=AdventureWorks; Integrated Security=True; Application Name=MyDemoApp”;

11. #7. Handling Nulls • NULLs = ? • ANSI SQL-92 requires any comparison to a NULL to fail (i.e. false) • This is SET ANSI_NULLS ON default setting • NULL = NULL returns false • NULL < > NULL returns false • ANSI_NULLS OFF • WHERE NULL = NULL returns true How do I know when I don’t know what I know, y’know?

12. Handling Nulls • Use • WHERE ColumnName IS NULL • Avoid • WHERE ColumnName = NULL • WHERE Col=COALESCE(@Var, Col) • WHERE (@Var IS NULL OR Column=@var)

13. Nulls on the Client • Basic types (int, string, etc.) don’t handle Null • SqlDataReader • IsDBNull tests whether a column is null • DataColumn • AllowDBNull property • System.DBNull.Value • Populate SqlParameters • Nullable types (int?, string?, etc.) do handle Null • Really just Nullable<int> • Any type can be declared nullable

14. Nullable Types string? s; Console.WriteLine(s.HasValue.ToString()); s = “Test”; string? x = null; • Classes (types) that support Null • Use IsDBNull to check database for NULL

15. #8. Transactions and Error Handling • Ability to consume T-SQL errors • Can nest TRY…CATCH blocks • Used in the CATCH block • Preventing Errors • Use EXISTS() to check for rows • Use a return code to signify failure

16. Client-Side Error Handling • Catch a SqlException • Check the Errors collection for multiple errors try { SqlDataReader r = cmd.ExecuteReader(); } catch (SqlException ex) { Console.WriteLine(ex.Message); Console.WriteLine(ex.Errors.Count.ToString()); }

17. Transactions • COMMIT TRAN matches BEGIN TRAN • ROLLBACK TRAN cancels ALL transactions • Always BEGIN, COMMIT, and ROLLBACK transactions at a consistent level • Always test @@TRANCOUNT prior to COMMIT or ROLLBACK

18. Client-Side Transactions SqlConnectionconn = new SqlConnection(“…") ; conn.Open(); SqlTransaction trans = conn.BeginTransaction(); SqlCommandcmd = new SqlCommand(); cmd.Connection = conn; cmd.Transaction = trans; try { // do some stuff trans.Commit(); } catch (SqlException) { trans.Rollback(); }

19. PATTERNS & PRACTICES

20. #9. Security is NOT an Afterthought • SQL Injection is ________________________________. • Plan ahead of time to minimize issues: • Ensure the least privileges principle for applications running on your servers • How much surface area do your servers expose? • Who has access to your servers? • How do you find out the who, what, and when of a breach?

21. #10. Change Management is RISK Management • Understand what the job entails and what makes a DBA successful! • Change control is important! Without it, DBAs face: • Changes that leave things worse than they started • Piecemeal rollbacks that cripple applications • Inconsistent support across applications and servers • Proper change management means: • Processed by a “change management board” composed of all key stakeholders • Performed at pre-planned times and within a defined time limit • Change is tested and verified to have no effect or positive effect on production environment • Changes are isolated, atomic, and reversible

22. Additional Resources • Plan Explorer Free: http://sqlsentry.net/plan-explorer/sql-server-query-view.asp • Twitter and #SQLHelp • SQLCAT.com • SQLSkills.com • Community Sites: • SQLPASS.org • SQLServerCentral.com • SQLBlog.com • SQLServerFAQ.com

23. SUMMARY • SSMS, Traces, and Execution Plans • Test Harness & Testing • Query Tuning Tips • Temp tables • Cursors & Loops • NULL • Error Handling • Transactions • Security isn’t an afterthought • Change Management is risk management

24. Q & A • Send questions to me at: kkline@sqlsentry.net • Twitter, Facebook, LinkedIn at KEKline • Slides at http://KevinEKline.com/Slides/ • IT Leadership content at http://ForITPros.com • THANK YOU!