Hey there everyone! I know I still owe you a few Q&A entries (for sessions 7, 8 and 9) but I wanted to get this blog entry out there so that you can play a bit with some of the resources. This series was targeted at developers but really helps to "bridge the gap" between development and administration by always remembering the impacts of what you implement (and techniques to help you to see if you do). More specifically, everything you do and/or design, has the potential for a negative impact to something else - there's no free lunch, eh?

So, this series focused more on Scalability but always remembered the impact to availability and/or reliability. This last session brought together the three primary areas to remember while developing a scalable and reliable architecture:

1. Know your data
   • Design for Performance - Sessions 1, 2, 3, 6, and 8
2. Know your users
   • Indexing for Performance - Sessions 4, 5 and 9
   • Optimizing Procedural Code - Session 7
   • Controlling Mixed Workloads and Concurrency - Session 6
3. Users lie
   • Profile - to make sure that you're tuning what's really happening as opposed to what you think was going to happen! - Session 9

This last session had some great questions and as a result, a few new resources were used. Here are a few of the things we talked about:

Event Notifications and DDL Triggers

• Designing Event Notifications (BOL hyperlink if SQL Server 2005 installed)
  • DDL Event Groups for Use with Event Notifications (BOL hyperlink if SQL Server 2005 installed)
  • Trace Events for Use with Event Notifications (BOL hyperlink if SQL Server 2005 installed)
• Designing DDL Triggers (BOL hyperlink if SQL Server 2005 installed)
  • Event Groups for Use with DDL Triggers (BOL hyperlink if SQL Server 2005 installed)
• Especially useful function if using DDL Triggers or Event Notifications - EVENTDATA() - see BOL

DMVs

• Favorites - dm_db_index_physical_stats, dm_exec_query_stats, dm_exec_query_plan, dm_db_index_usage_stats
• In general - GREAT that these are "views" so that you can query them directly and push their results DIRECTLY into a temp table, even with SELECT INTO
• How can you find them: SELECT * FROM sys.system_objects WHERE name LIKE 'dm[_]%'
• How can you find out how to use them - start in the BOL:
  • System Views (Transact-SQL)
  • Dynamic Management Views and Functions
• Use my DMObjects.sql script to better learn their input types and output columns: DMObjects.sql (8.37 KB)

Webcast links for the entire series!

Part 1: Creating a Recoverable Database

      For the MSDN Download for Part 1, click here.

      For the SQLskills Blog Entry for Part 1, click here. 

 

Part 2: Creating a Reliable and Automated Backup Strategy

      For the MSDN Download for Part 2, click here.

      For the SQLskills Blog Entry for Part 2, click here.

 

Part 3: Designing Tables that Scale, Best Practices in Data Types and Initial Table Structures

      For the MSDN Download for Part 3, click here.

      For the SQLskills Blog Entry for Part 3, click here.

 

Part 4: SQL Server Indexing Best Practices

      For the MSDN Download for Part 4, click here.

      For the SQLskills Blog Entries for Part 4

         Resource links blog entry, click here.

         Q&A blog entry, click here.

 

Part 5: SQL Server Index Defrag Best Practices

      For the MSDN Download for Part 5, click here.

      For the SQLskills Blog entry, click here.

 

Part 6: Mixed Workloads, Secondary Databases, Locking and Isolation

      For the MSDN Download for Part 6, click here.

      For the SQLskills Blog Entry for Part 6, click here.

 

Part 7: Understanding Plan Caching and Optimizing Procedure Performance

      For the MSDN Download for Part 7, click here.

 

Part 8: Data Loading and Aging Strategies - Table and Index Partitioning

      For the MSDN Download for Part 8, click here.

 

Part 9: Profiling for Better Performance

      For the MSDN Download for Part 9, click here.

 

Part 10: Session Summary - Common Roadblocks to Scalability

      For the MSDN Download for Part 10, click here.

      Transcript can be found here.

Kimberly