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:
- Know your data
- Design for Performance – Sessions 1, 2, 3, 6, and 8
- Know your users
- Indexing for Performance – Sessions 4, 5 and 9
- Optimizing Procedural Code – Session 7
- Controlling Mixed Workloads and Concurrency – Session 6
- 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
- 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:
- 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 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.
So, the series comes to an end (even though I still have more work to do). I have to say that it was a lot of fun and I enjoyed everyone's questions. And /start shameless plug here/ starting in March, SQLskills will begin a 10-12 part series on TechNet. The series will include sessions from my colleague Bob Beauchemin as well as me. This will definitely be more Operations and DBA focused but for all of you developers – it may help you better understand the system, High Availability and a myriad of New Features in SQL Server 2005.
I hope to see you there – or at least your DBA… ;-)
Thanks again everyone,