SQL Server Best Practices...In Practice!

The intent of this session is to drill into the reasoning behind SQL Server best practices. Most of us have had experiences with releases that "worked fine in test" but led to issues in production. This session is aimed both at those who develop code as well as those who maintain the servers and are looking for more effective ways to engage with developers.
Ever used a NOLOCK hint, only to find that nothing broke the next day? Odds are most folks reading this have. So if fire and brimstone did not rain from the sky, does that mean it was OK? (Spoiler alert - it was not). But why do we worry so much about this particular hint? What could actually go wrong?
SQL Server supports a few table types. The two most traditional choices are heaps and tables with clustered indexes. We almost always create a unique clustered index on tables ... but why? Since heaps are part of the product they must have a use case but what is it? And what about columnstore? What is the secret sauce in clustered indexes that makes them withstand the test of time?
There is a substantial body of best practice information in the SQL Server community. Many of these practices are routinely ignored in some organizations, presumably because the consequences of not following aren't always clear. Come join this full day session for in-depth explanations of why we give some of the advice that we do.
Topics covered include query hints, indexing, LINQ to Entities, common table expressions, looping vs set-based operations and more.

Attendees will leave with knowledge of how to better manage a database server, build better data structures, and avoid performance pitfalls.

Lessons:

1. Query hints
2. Real world NOLOCK issues
3. RCSI and Snapshot as an alternative to nolock
4. Parameter sniffing, RECOMPILE, and OPTIMIZE
5. INDEX and other miscellaneout hints
6. Parameter sniffing and RECOMPILE and OPTIMIZE for hints
7. Large objects - storage and performance issues
8. Cursors and loops
9. Cursor review
10. Looping review
11. Thinking in recordsets and why recordsets are more efficient
12. Batching - finding the right balance between too little and too much work
13. User defined functions
14. Types of UDFs
15. Why inline UDFs are less problematic
16. Function inlining
17. Risks of object relation mapping (such as Entity Framework)
18. Triggers
19. Heaps vs clustered indexes vs columnstore
20. Common table expressions - what they do and what they do not actually do
21. Table variables vs temporary tables

...and more!

Rick is a Microsoft Certified Master for SQL Server and independent contractor/consultant specializing in performance and query tuning. He began his career as a system programmer, before transitioning through database development positions to eventually focus on performance issues as well as relationship counseling between DBA and developer teams.

Event Venue & Nearby Stays

Massry Center for Business and School of Business Accolades, Massry Center for Business and School of Business Accolades, Albany, United States