Kalen Delaney : seminars

Did You Know? I’m delivering my first SQL Server 2012 class this week!

Kalen Delaney — Wed, 08 Aug 2012 04:14:00 GMT

This is actually just a very short post to get my name back up in the list on the right side of SQLBlog, because Adam’s configuration removes people who haven’t posted in 3 months. I’ve been extremely busy trying to get my new book finished, and then...(read more)

Geek City: Ordered Seeks and Scans

Kalen Delaney — Fri, 21 Jan 2011 23:28:00 GMT

I got a couple of really great questions during my SSWUG Workshop this morning , as I was discussing seeks and scans, and since the answers to the two questions are very related, I decided to address both of them in more detail in this blog post. Most...(read more)

Geek City: 24Hours of PASS Followup

Kalen Delaney — Thu, 16 Sep 2010 18:20:00 GMT

Thank you everyone who attended my 24HOP session yesterday. It was a wonderful event to be part of. Here are a few of the questions that I was asked, that didn't get completely answered. Question: Can we set READ_COMMITTED_SNAPSHOT ON for just a table...(read more)

Geek City: Plan Caching Internals

Kalen Delaney — Mon, 23 Aug 2010 17:04:00 GMT

This Friday is the last of my summer seminars. On August 27, I am presenting on plan caching, plan reuse and recompile and plan cache metadata. This is one of my favorite topics to talk about, because a lot of the information is not very widely known,...(read more)

Did You Know: My Online Seminar Lasted Almost 5 Hours!

Kalen Delaney — Sun, 25 Jul 2010 00:06:00 GMT

Yesterday was a rebroadcast of the index internals seminar I presented last February, and I had forgotten how long it was. I was in a chat room the whole time, and the questions just kept coming, fast and (not so) furious! Thanks to all the participants...(read more)

Geek City: Index Internals

Kalen Delaney — Mon, 19 Jul 2010 14:15:00 GMT

Once again, I'll be presenting an online seminar through SSWUG . This on is on Index Internals . The presentation lasts over 3 and a half hours, and I'll be online for live chat for over 4 hours. Ask away! We'll look at juicy details of exactly how clustered...(read more)

Did You Know: Good Stuff!

Kalen Delaney — Wed, 30 Jun 2010 21:33:00 GMT

I know, I promised a technical post after my online seminar today, and I'm still planning on that. There weren't all that many really deep questions, just a lot of basic understanding questions, plus questions about topics I'll be covering in my Plan...(read more)

Did You Know: My next web seminar is this Wednesday!

Kalen Delaney — Mon, 28 Jun 2010 03:40:00 GMT

I know, I haven't made a geeky post in a while... but I promise that I'll do one after this seminar. I'll take some of the best questions that I get during the chat and write them up here. Query Plans Workshop (June 30, 2010) Learn about basic elements,...(read more)

Did You Know? I'm doing 3 more online seminars with SSWUG!

Kalen Delaney — Fri, 11 Jun 2010 22:46:00 GMT

As I told you in April , I recorded two more seminars with Stephen Wynkoop, on aspects of Query Processing. The first one will be broadcast on June 30 and the second on August 27. In between, we'll broadcast my Index Internals seminar, on July 23. Workshops...(read more)

Did You Know? More online seminars!

Kalen Delaney — Thu, 22 Apr 2010 23:20:00 GMT

I am in Tucson again, having just recorded two more online workshops to be broadcast by SSWUG. We haven't set the dates yet, but we are thinking about offering a special package deal for the two of them. The topics really are related and I think they...(read more)

Did You Know? I just finished my first online seminar

Kalen Delaney — Fri, 12 Feb 2010 23:21:00 GMT

I am really jazzed! The seminar was over 3.5 hours in length, and we had 3 15-20 minute breaks, during which questions just kept coming in the chat room. I barely got out to refill my coffee! But there were some great questions and a really enthusiastic...(read more)

Geek City: 24 hours of SQL Server!

Kalen Delaney — Fri, 14 Aug 2009 01:13:00 GMT

I know, my Geek City posts are usually really technical, but I figured it’s only Geeks who are going to want a solid 24 hours of SQL Server presentations… so this post is dedicated to all the SQL Server Geeks out there! I will be presenting as part of...(read more)

Did You Know? I'm Going Down Under Again in June

Kalen Delaney — Wed, 04 Feb 2009 05:16:00 GMT

It's official... and Peter Ward has already blogged about it:

http://wardyit.com/blog/blog/archive/2009/01/29/kalen-delaney-in-australia-in-june.aspx

Anyone registering by March 27th will get a free copy of my new book, SQL Server 2008 Internals:

SQL Server 2008 Internals

But as always, there will be giveaways for more copies of this book and other swag at the end of each seminar.

~Kalen

Geek City: Reducing Cache Bloat and a Metadata Bug

Kalen Delaney — Wed, 29 Oct 2008 23:43:00 GMT

I've been running some tests with the new SQL Server 2008 configuration option 'optimize for ad hoc workloads' which is intended to reduce cache bloat due to holding onto plans for single use ad hoc queries. This option will allow a new type of cached object to be stored, which isn't the only plan, but merely a stub. There will be a corresponding new cacheobjtype value called "Compiled Plan Stub".

There are several ways of looking at the contents of cache. In SQL 2000, we had a pseudo table called master.dbo.syscacheobjects, and we can still access this object in SQL 2005 and 2008 by referencing the compatibility view sys.syscacheobjects (from any database). However, when I look at this view, I don't see "Compiled Plan Stub", but rather "Compiled Plan Stu ".

So I checked the definition of syscacheobjects, using the function object_definition:

USE master;
SELECT OBJECT_DEFINITION(object_id('syscacheobjects'));

and I saw that syscacheobjects was derived from a column in sys.dm_exec_cached_plans, and converted to an nvarchar(17). Unfortunately, "Compiled Plan Stub" is 18 characters long.

I'll be talking about this new cached object type in my 3-day seminar in London next week, and I'll post more information about using the 'optimize for ad hoc workloads' at a later time.

But normally, I wouldn't have run into this problem, because I have created my own view based on the new SQL 2005 metadata, including sys.dm_exec_cached_plans. Usually, I use my own view, which converts to a longer string, so I wouldn't have seen the string truncation. But I was testing this on a new instance, where I hadn't created my own view yet.

I am including my view definition, which is what I have been using since the SQL Server 2005 came out, to inspect the plan cache. In fact, I usually add a where clause to restrict the output only to Compiled Plans. Note that the view name starting with sp_ means we can access the view from any database:

-- Create a view to show most of the same information as
--   SQL Server 2000's syscacheobjects
USE master
GO
CREATE VIEW sp_cacheobjects(bucketid, cacheobjtype, objtype, objid, dbid, dbidexec,
             uid, refcounts, usecounts, pagesused, setopts, langid, dateformat, status, lasttime,
             maxexectime, avgexectime, lastreads,lastwrites, sqlbytes, sql)
AS
SELECT
    pvt.bucketid,
    CONVERT(nvarchar(20), pvt.cacheobjtype) as cacheobjtype, pvt.objtype,
    CONVERT(int, pvt.objectid)as object_id, CONVERT(smallint, pvt.dbid) as dbid,
    CONVERT(smallint, pvt.dbid_execute) as execute_dbid,
    CONVERT(smallint, pvt.user_id) as user_id, pvt.refcounts, pvt.usecounts,
    pvt.size_in_bytes / 8192 as size_in_bytes,
    CONVERT(int, pvt.set_options) as setopts, CONVERT(smallint, pvt.language_id) as langid,
    CONVERT(smallint, pvt.date_format) as date_format, CONVERT(int, pvt.status) as status,
    CONVERT(bigint, 0), CONVERT(bigint, 0), CONVERT(bigint, 0), CONVERT(bigint, 0),
    CONVERT(bigint, 0),CONVERT(int, LEN(CONVERT(nvarchar(max), fgs.text)) * 2),
    CONVERT(nvarchar(3900), fgs.text)

    FROM (SELECT ecp.*, epa.attribute, epa.value
               FROM sys.dm_exec_cached_plans ecp
                    OUTER APPLY sys.dm_exec_plan_attributes(ecp.plan_handle) epa) as ecpa
               PIVOT (MAX(ecpa.value) for ecpa.attribute
                   IN ("set_options", "objectid", "dbid", "dbid_execute",
                       "user_id", "language_id", "date_format", "status")) as pvt
                OUTER APPLY sys.dm_exec_sql_text(pvt.plan_handle) fgs

This view shows the full cached object type:

Have fun!

~Kalen

Did You Know? You Can Win a Copy of My Next Book!

Kalen Delaney — Sat, 25 Oct 2008 03:09:00 GMT

November is conference month for me, and I'll be giving away vouchers for signed copies of my new book, to be mailed to the winners as soon as I receive my author copies.

I'll be giving away one each day of my seminars in London and Seattle!

The London seminars, for SQLKnowHow, are taking place the week of November 3-7, so I'll actually be out of the country the day of the US national elections. I haven't figured out if that is good news or bad news yet; I guess it depends on who wins.

Monday, November 3 is SQL Server Data Storage Formats: Internals, Performance and Best Practices. This will be the first delivery of the seminar I'll be presenting for my preconference session at PASS.

This seminar will describe all the different formats available for storing data, including the new filestream data, compressed data and sparse data columns in SQL Server 2008. We’ll look at the metadata to determine exactly what is being stored and where, and discuss best practices for choosing the particular type of storage format.

Tuesday, November 4 is SQL Server Concurrency Control: Locking, Blocking and Row Versioning

In this seminar I'll look at the concurrency enhancements made in SQL Server 2005 based on row-level versioning which implements optimistic concurrency. We’ll compare concurrency management with row versioning to concurrency management with locking and blocking. We'll look at the metadata that stores information about your transactions, and look at tools for troubleshooting concurrency problems. We will also look at the locking enhancements added to SQL Server 2008.

Wednesday - Friday, November5-7 is SQL Server Data Internals and Tuning

This seminar is a 3-day version of my 5-day SQL Server Internals and Tuning class that I have delivered all over the world. The seminar includes extensive demonstrations that illustrate the details of SQL Server internals as well as tuning techniques. This course will be presented on SQL Server 2008 and cover features specific to that version, but most of the information is relevant to SQL Server 2005 and some is relevant to SQL Server 2000. Topics to be covered include the following:

SQL Server Storage Metadata
Tools for exploring storage structures
Physical Storage Structures
Index Design and Tuning
Query Processing and Query Plans
Optimization and Recompilation
Query Tuning Tips and Techniques

So for each of these 5 days, I'll be giving away a book a day, plus other giveaways as well.

Then later in November, on 18 November, I'll be giving a preconference session at PASS, in Seattle. In addition to vouchers for my new book, I'll have a couple of copies of my SQL Server 2005 books to give away.

So hopefully I'll see many of you soon!

~Kalen