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, and you can have a tremendous performance impact by making sure when plans are reused when that is the best option and when a new plan is compiled when that is best choice.  By examining the plan cache metadata, you can see exactly what plans are currently in cache and what those plans are doing, i.e. index seeks or scan? hash joins or loop joins? extra sorting? parallelization?

Here is the link for more info and registration:

http://www.vconferenceonline.com/shows/workshops/plancaching.asp

The seminar has been pre-recorded, but I will be available concurrently for online chat and Q&A.

See you there!

~Kalen

When SQL Server first introduced table and index partitioning in SQL 2005, it was the same version that all the metadata changed. So I decided that I could learn a lot about metadata and a lot about the organization and storage of partitions if I tried to write a query, and then a view, that included almost everything anyone would ever need to know about any partitioned objects you had created.  While doing that I realized that one function that SQL Server was missing, in spite of all the incredible new metadata and built-in functions, was a function to return the name of an index, given the object_id and the index_id. Since so much of the metadata contains these ID columns, it seems like a useful thing. But I had to write one for myself:

CREATE FUNCTION dbo.index_name (@object_id int, @index_id tinyint)
RETURNS sysname
AS
BEGIN
  DECLARE @index_name sysname
  SELECT @index_name = name FROM sys.indexes
     WHERE object_id = @object_id and index_id = @index_id
  RETURN(@index_name)
END;

This function needs to be created in the database where you want to use it.

I wrote the view to return all the partitioning info to be used in my classes. It took quite a while, and was not perfect. The two pieces I kept meaning to fix were:

• It would return no rows for a table that was not partitioned at all
   
• It would only list one boundary point for each partition.  This meant you always had to return the partitions in order in order to see the lower and upper boundary for each. However, one of my students in a class I taught in Salt Lake City in September 2009 took on the task of 'fixing' those problems, so the version I show you here is the updated one.

I decided to make this script  public today in response to a query on the public forums. I'm not including a script that will create a partitioned table for you. If you don't have a partitioned table of your own, you probably won't even be interested in this script!

-- Create a view to return details about a partitioned table or index
--  First run the script to create the function index_name()

-- Written by Kalen Delaney, 2008
--   with a few nice enhancements by Chad Crawford, 2009

CREATE VIEW Partition_Info AS
  SELECT OBJECT_NAME(i.object_id) as Object_Name, dbo.INDEX_NAME(i.object_id,i.index_id) AS Index_Name,
    p.partition_number, fg.name AS Filegroup_Name, rows,
    au.total_pages,
    CASE boundary_value_on_right
        WHEN 1 THEN 'less than'
        ELSE 'less than or equal to'
    END as 'comparison'
    , rv.value,
    CASE WHEN ISNULL(rv.value, rv2.value) IS NULL THEN 'N/A'
    ELSE
      CASE
        WHEN boundary_value_on_right = 0 AND rv2.value IS NULL 
           THEN 'Greater than or equal to'
        WHEN boundary_value_on_right = 0
           THEN 'Greater than'
        ELSE 'Greater than or equal to' END + ' ' +
           ISNULL(CONVERT(varchar(15), rv2.value), 'Min Value')
                + ' ' +
                +
           CASE boundary_value_on_right
             WHEN 1 THEN 'and less than'
               ELSE 'and less than or equal to'
               END + ' ' +
                + ISNULL(CONVERT(varchar(15), rv.value),
                           'Max Value')
        END as 'TextComparison'
  FROM sys.partitions p
    JOIN sys.indexes i
      ON p.object_id = i.object_id and p.index_id = i.index_id
    LEFT JOIN sys.partition_schemes ps
      ON ps.data_space_id = i.data_space_id
    LEFT JOIN sys.partition_functions f
      ON f.function_id = ps.function_id
    LEFT JOIN sys.partition_range_values rv
      ON f.function_id = rv.function_id
          AND p.partition_number = rv.boundary_id    
    LEFT JOIN sys.partition_range_values rv2
      ON f.function_id = rv2.function_id
          AND p.partition_number - 1= rv2.boundary_id
    LEFT JOIN sys.destination_data_spaces dds
      ON dds.partition_scheme_id = ps.data_space_id
          AND dds.destination_id = p.partition_number
    LEFT JOIN sys.filegroups fg
      ON dds.data_space_id = fg.data_space_id
    JOIN sys.allocation_units au
      ON au.container_id = p.partition_id
WHERE i.index_id <2 AND au.type =1

-- Example of use:
SELECT * FROM Partition_Info
WHERE Object_Name = 'charge'
ORDER BY Object_Name, partition_number

Have fun!

~Kalen

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 for making it a really exciting 5 hours for me. I really appreciated all your enthusiasm.

My next seminar with SSWUG will be on Plan Caching and Recompiling. I always love teaching this topic, because there are so many ways you can make a major difference in performance by understanding when reusing a cached plan is a good thing and when it is NOT! This includes Parameter Sniffing and how to avoid it.

We'll look at when automatic statistics updating kicks in and when that causes recompiles, as well as looking at what other changes can cause automatic recompilation.

We'll talk about XML query plans, and -I'll show you how to access the plan cache metadata to see how to look at the plan for every query currently in cache. It's an awesome capability!

I hope to see you there!

~Kalen

It's time for the eighth T-SQL Tuesday, managed this time by Robert Davis at SQL Server Central. This time, the topic was announced very late, and I was just starting to write a blog post of my own when I saw it. Fortunately, my topic easily fits into this month's topic of "Gettin' Schooled", which is supposed to have something to do with either teaching or learning. I feel very lucky, because in my work, I get to do both, at the same time! Many, if not most, of the really cool things I like to tell people about, I learned because questions asked of me by my students.

In my online seminar two weeks ago, I was talking about the difference between estimated and actual execution plans. The simple explanation of the difference is that with an estimate plan (like you get with SET SHOWPLAN_ALL ON), your batch is not executed, but with an actual plan (like you get with SET STATISTICS PROFILE ON), your batch IS executed and results are returned. I talked about when the actual plan might be different than the estimated plan. Although I'm not going to go into all the differences now, I will tell you that for batches of a single query, your estimated and actual plans should be the same. Differences can come up when you have a multi statement batch, or a batch that calls a procedure.

One example that I always mention in classes and seminars is the case where the batch creates a temp table, and then uses that temp table. When trying to look at the estimated plan, you will get an error, because the batch is not executed. If it is not executed, no temp table is created, so the statement that selects from the temp table cannot even be compiled to give a plan:

-- I am using a table called Test1 to get the data for my temp table;
-- You can use any table of your own

SET SHOWPLAN_ALL ON;
GO
SELECT ID INTO #Tmp1
FROM Test1;
SELECT *
FROM #Tmp1;

The above will give the following error:

Msg 208, Level 16, State 0, Line 3
Invalid object name '#Tmp1'.

However, looking an actual plan will not:

SET SHOWPLAN_ALL OFF;
GO
SET STATISTICS PROFILE ON;
GO
SELECT ID INTO #Tmp1
FROM Test1;
SELECT *
FROM #Tmp1;

When discussing this example during the online seminar, one of the participants asked why the behavior was different with a table variable. And I realized, I had never tried my example with a table variable. After the seminar was over, I ran some tests, and sure enough, there was no error looking at an estimate plan when a table variable was created.

-- I am still using a table called Test1 to get the data
-- However, in this case, you'll need a table with a single INT column

SET STATISTICS PROFILE OFF;
GO
SET SHOWPLAN_ALL ON;
GO
DECLARE @Tmp1 TABLE (ID int);
INSERT INTO @Tmp1(ID)
  SELECT ID
  FROM Test1;
SELECT * FROM @Tmp1;

-- No error is returned!

I started searching online to see if anyone had an explanation, and found this blog post written by Lubor Kollar and Lindsey Allen of the SQLCAT Team at Microsoft. (The blog post also contains a script for building the Test1 table, if you want to copy it and execute it.)

Lubor and Lindsey are discussing different execution plans when using temp tables and table variables, but when I looked closer, there weren't ever getting errors, even with a temp table. It turns out their temp table was created with CREATE TABLE statement, rather than SELECT INTO, and that completely changes how the batch is processed:

SET SHOWPLAN_ALL ON;
GO
CREATE TABLE #Tmp1 (ID int);
INSERT INTO #Tmp1(ID)
  SELECT ID
  FROM Test1;
SELECT *FROM #Tmp1;

It appears the ONLY time you get an error with a temporary table and an estimated execution plan is when you create the table with SELECT INTO.  In fact, with SELECT INTO, you'll get the error even if you create a permanent table in the batch.

And I learned this from questions that were asked of me … which is where I learn all kinds of really interesting information!

Happy Tuesday!

~Kalen

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, tools for exploring your query plans, how to detect inefficient plans and best practice guidelines

The workshop starts at 9 in the morning Pacific Time,and I'll be available on live chat to answer questions during the broadcast.

Thanks!

~Kalen

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 can be replayed for up to a week after the broadcast, and you can even buy a DVD of the workshop.

You can get more details by clicking on the workshop name, below, or check out the announcement on the SSWUG site at http://www.sswug.org/editorials/default.aspx?id=1948

Query Plans Workshop (June 30, 2010)
Learn about basic elements, tools for exploring your query plans, how to detect inefficient plans and best practice guidelines

Indexing Workshop (July 23, 2010)
Learn about basic index structures, tools for exploring those structures, index design considerations and best practice guidelines

Plan Caching Workshop (Aug 27, 2010)
Learn about basic management of the plan cache, tools for exploring the plans in cache and detecting when they are reused, query design considerations to provide the optimal caching behavior and best practice guidelines

For those of you who attended my presentation at the Colorado PASS seminar last Wednesday in Denver, note that the 3rd workshop (Plan Caching), includes most of what I talked about in my presentation, and a lot more besides. The scripts from that session are now up on my site at www.sqlserverinternals.com/conferences

Have fun!

~Kalen

TechEd starts in 3 weeks, and I'm not going to make it this year. I had very much wanted to visit New Orleans post-Katrina and see the recovery for myself. I attended a couple of TechEd's there many years ago, but my primary reason for visiting that fabulous city was because my daughter went to school there. She graduated from Tulane University in 1999, but it just so happened that every time TechEd was there, it was after school was over for the year, so I never got to combine my conference trip with a family visit.

If you're going to the conference, and you're trying to decide what PreCon session to sign up for, I've got a suggestion for you.

You might be aware that I have a 5-day SQL Server Internals course, that is based on the material in my book(s). For the last few years, there has only been one other person besides me, in the entire world, whom I have authorized to teach my course.  I'm proud to announce that just last week, the second authorized trainer presented my course for the first time. Maciej (how do you pronounce this?) Pilecki taught the course in Warsaw and will be teaching it again in Germany this November.

I just found out today that Maciej will be giving a full day session at TechEd called :

Secrets of the SQLOS:
Leveraging Microsoft SQL Server Internal Operating System for Improved Scalability and Performance

So this session goes beyond what I cover in my class, down into the interaction between SQL Server and the operating system, and the topic is one of Maciej's specialties. If you've never heard Maciej talk about SQL Server memory management (or even if you have!), you won't regret taking this precon.

It's not too late to sign up!

Have fun!

~Kalen

It's time for the fifth T-SQL Tuesday, managed this time by Aaron Nelson of SQLVariations. Once again, the deadline came up just too quickly, and I'm on the road this week, so my entry will not be too long. Aaron's topic is reporting and in keeping with my past posts, this contribution will include a history lesson. 

Since I first learned SQL, I've always thought of aggregation as a way of producing simple reports. Summary information was frequently all that was needed on an ongoing basis to see what was going on in an organization. Clients would want straightforward summaries of sum of sales per region or average orders per day or per month, or number of products available per category.  Once a TSQL programmer was fluent with the GROUP BY operator, these simple reports could be generated very easily.

But what if we wanted just a bit more? What if we to see a summarization of total sales amount by region and salesperson, and also just by region?

Way back in the earliest versions of Transact-SQL before we had any 3rd party report writing products, any results we wanted had to be generated purely through T-SQL. We could use a clause called COMPUTE BY that allowed us to get aggregated values for any column in the SELECT list, and we could have multiple COMPUTE BY clauses for a single SELECT.  This functionality gave us some ability to have multiple aggregations in a single query, but it had some drawbacks. First, we couldn't just return the aggregate information… we always had to return the detail rows also. Second, the rows that contained the aggregates ONLY contained the requested aggregates, which were based on a single column in the SELECT list; they did not include a value for the nonaggregated columns. This meant that that the result set was non-relational… the detail rows contained different columns than the COMPUTE BY rows. In fact, it was by understanding COMPUTE BY all those years ago that I learned exactly what was meant by relational result set. [COMPUTE BY is actually still in the product, even thought it produces non-relational results. However, the documentation for SQL Server 2008 indicates that it will finally be removed in the next version. ]

Having an interest in using aggregation for reporting, I was very interested in two new options to GROUP BY that were introduced in SQL Server 6.5. There were the CUBE and ROLLUP extensions. Their addition to the SQL language were described in a paper by Jim Gray in 1997, which you can read here:

http://arxiv.org/ftp/cs/papers/0701/0701155.pdf

This  paper was a real eye-opener when I first read it 13 years ago. And it does an awesome job of explaining why a CUBE is called a CUBE.  I'm reproducing one figure from this paper here, that shows the genesis of the cube, when grouping by three different 'dimensions'. The example in the paper is a car dealership, and the grouping values are year, make and color of vehicle. 

Although I have not begun to explore analysis services or reporting services in any depth at all, I find that a thorough understanding of what is a cube and why we should care allows me to at least understand why these services are such powerful additions to the SQL Server product line.

~Kalen