Here are my notes from this session of SQL Saturday held Saturday in Vancouver, BC

You can get almost everything presented in this session from BrentOzar.com/go/DMVs so no need to take a lot of notes

sys.processes doesn't show child threads. dm_exec_requests does.

Download and check out sp_WhoIsActive written by Adam Mechanic(sp?)

SQLNexus.codeplex.com for analysis of SQLDiag data

SQL Sentry plan explorer - free tool to explore execution plans

Run resource governor to put queries into pools and you can free the procedure cache for an individual resource pool. This gives you a lot more control over query plan caching when you are testing changes without having to flush the entire query plan cache.

DMVs give "garbage" for heaps. Add a clustered index first before you believe the information.

Here are my notes from this session of SQL Saturday held Saturday in Vancouver, BC

Statistics are a summary of data distribution and is not 100 percent accurate.

Stats: Total number of rows, uniqueness of the data, distribution of values in the leading column.

You can turn off auto create statistics if you have an old database with no new development but leave it on in general.

For large tables when about 20 percent of the data is changed statistics are invalid and need to be updated before they can be used.

DBCC UPDATE_STATISTICS to manually update statistics.

Data is split into a maximum of 256 steps.

Filtered statistics are part of a filtered index. I need to learn more about filtered indexes

Filtered statistics are updated after 20 percent of the table changes.

Here are my notes from this session of SQL Saturday held Saturday in Vancouver, BC

Management Data Warehouse (MDW) and Data Collector (DC) are designed to answer questions about what happened in the past and are there to help you see the biggest problems

Collect different counters and upload to a data warehouse for further review and study

Reports designed around a "click on big things" philosophy so you can easily discover problems.

The tool doesn't add any more than 5 percent overhead on the TPC-C benchmarks.

Using the newer performance counters for queries you only store 10 MB/instance/day instead of the 200-500 MB/instance/day of the old version. The difference is that the new version is storing the hash of the query instead of storing duplicate information for all the queries.

You can create your own custom collections. Don't mess with the jobs that are set up for the collection sets.

Utility control point is for capacity planning not troubleshooting.

Here are my notes from this session of SQL Saturday held Saturday in Vancouver, BC

Perform an estimated execution plan to get the query plan for a scalar function

Track statement completion events in profiler to see what a function does. It will produce a lot of noise but you can dig through it and figure it out

A TVF query plan estimate is based on the TVF only returning 1 row. This can lead to problems with the optimization of the query plan.

Solutions to function problems

1. Don't use functions
2. In profiler add an object type filter for 18004, 20038, and 21321 to see what is happening and tune
3. Use query hints MERGE and HASH to get better query plans

Join order with index hints affects performance

Use a temp table to populate from a TVF and then join to the temp table.

Use an inline TVF since it will be optimized along with the outer query

sp_refreshsplmodule fixes metadata associated with select * and compilation.

Using a TVF in a subquery, cross apply, or outer apply gives better performance than a scalar function.

SQLCLR functions can be fast for simple things. Be wary of using lots of memory.

Data access in SQLCLR can potentially be bad.

Each time you reference a function it wll be evaluated so a join back to a CTE with a function can be bad.

Here are my notes from this session of SQL Saturday held Saturday in Vancouver, BC

When connecting to SQL Azure you are only connecting to a TDS endpoint

A Silverlight SQL Manager is availabile from the portal. It only handles tables, sprocs, and views

He stated "A hybrid approach is 9 times out of 10 the correct approach". I think this statement needs a lot more clarification before I can agree with it.

SQL Azure has been updated about every 3 months

All tables require a clustered index. If you don't have a clustered index you can create the table but not put data into it.

SQL Azure federation will be in CTP this summer

Sharding applies at the DB level

Enzo Multitenant Framework is available at http://www.bluesyntax.net/files/enzoframework.pdf. It provides application level sharding/federation.

I have been doing a series of screencasts on SQL Server 2008. I noticed the other day that the first two have been posted.

Improve Performance and Storage Utilization with Data Compression covers the data compression feature and shows when and how it can help with performance and storage utilization.

Enable Transparent Data Encryption covers the transparent data encryption (TDE) feature. I really love this feature because it protects your data whenever it is written to disk. This alone could help solve a lot of problems with databases that are restored to servers that they shouldn't be or lost backup tapes.

The screencasts are designed to show how to do something from scratch so they are very focused and you get to see my (somewhat poor) typing skills as I build up SQL scripts.

I have a list of topics I am working through but I would love to hear what you find the most important or what you have questions about. If you have any topics that you would like me to cover just leave a comment here and I will see if I can get to it.

You can see screencasts and get news from PDC from a variety of sources including microsoftpdc.com so I won't try to capture every detail but instead put down my thoughts on what I learned/saw.

Today has been an Azure day. In the morning keynote Microsoft announced Microsoft Azure, their new operating system for the cloud. I have spent most of the rest of the day in sessions learning more about the cloud and what all it can do now and what it will do in the future. I have some small ideas for the SQL Services storage and how to use it. I just registered for my Azure account so I can test out the CTP.

One of the useful and impressive demos I saw was of a workflow that started out as a local WF workflow and then sent messages into the cloud where the workflow continued and finally messages were sent back to the originating workflow. I can envision scenarios where certain things like payroll or invoicing could be made part of the workflow and companies could reduce costs for commodity items that are not really part of their core business.

In other news the WCF REST starter kit was released on CodePlex. You can access it at http://www.codeplex.com/aspnet/Wiki/View.aspx?title=WCF%20REST. There is also documentation and other information at the WCF Developer Center REST site.

Also LiveID will become an OpenID provider. It is in CTP now but if it works and gains wide spread adoption it will enable a lot more "single sign on" scenarios. This looks like the first concrete step to come out of Microsoft agreeing to support OpenID.

finally, while not PDC related, TJay Belt had an article on duties for on-call DBAs featured at SQLServerCentral.com.

According to eWeek at http://www.eweek.com/c/a/Application-Development/Microsoft-to-Deliver-SQL-Server-2008-in-August/ Microsoft announced that SQL Server 2008 will be released next month. I have been playing with the release candidate 0 for the last couple of weeks, specifically looking at the spatial data types. I have enjoyed it and can see many applications for this technology. I am looking forward to the full release.