The question I had going into the meetup was: should Eucalyptus be used to make an organization's private cloud? The short answer is no.

The project is of high quality, the people are of the highest quality, but in the end Eucalyptus is a research project from a university. As an academic project Eucalyptus is subject to changes in funding and the research interests of the team. When funding sources dry up so does the project. If the team finds another research area more interesting, or if they get tired of chasing a continuous stream of new Amazon features, or no new grad students sign on, which will happen in a few years, then the project goes dark.

Fears over continuity have at least two solutions: community support and commercial support. Eucalyptus could become community supported open source project. This is unlikely to happen though as it conflicts with the research intent of Eucalyptus. The Eucalyptus team plans to control the core for research purposes and encourage external development of add-on service like SQS. Eucalyptus won't go commercial as University projects must stay clear from commercial pretensions. Amazon is "no comment" on Eucalyptus so it's not clear what they would think of commercial development should it occur.

Taken together these concerns imply Eucalyptus is not a good base for an enterprise quality private cloud. Which they readily admit. It's not enterprise ready Rich repeats. It's not that the quality isn't there. It is and will be. And some will certainly base their private cloud on Eucalyptus, but when making a decision of this type you have to be sure your cloud infrastructure will be around for the long haul. With Eucalyptus that is not necessarily the case. Eucalyptus is still a good choice for it's original research purpose, or as cheap staging platform for Amazon, or as base for temporary clouds, but as your rock solid private cloud infrastructure of the future Eucalyptus isn't the answer.

The long answer is a little more nuanced and interesting.

read more

Can't get enough cloud computing? Then you must really be a glutton for punishment! But just in case, here are some cloud computing resources, collected from various sources, that will help you transform into a Tesla silently flying solo down the diamond lane.

Meta Sources

The accompanying system, Pig, is fully implemented, and compiles Pig Latin into physical plans that are executed over Hadoop, an open-source, map-reduce implementation. Pig has just graduated from the Apache Incubator and joined Hadoop as a subproject.

The paper has a few examples of how engineers at Yahoo! are using Pig to dramatically reduce the time required for the development and execution of their data analysis tasks, compared to
using Hadoop directly.

References: Apache Pig Wiki

The problem with free is that every time you double the size of your database the cost of maintaining the site grows 6 fold. I really underestimated how much resources it would take, I have one database table now that exceeds 3 billion records. The bigger you get as a free site the less money you make per visit and the more it costs to service a visit...There is really no money in being free and we have to start experimenting with other models now or we won’t be able to compete in 3 or 4 years.

As one commenter succinctly put it: the “golden time” of AdSense is over. Time to look at costs. The POF architecture is to run scarily huge tables on single machines. They also buy and maintain their own SAN. So it seems scaling up is what is increasing costs and decreasing profits. I wonder if the economics of cloud storage and cloud architectures might have a more linear cost curve?

Like Tibor, I prefer a downloadable BOL, partly because I do a lot of research and writing while traveling and I don't always have an internet connection, like at 42,000 ft (~14000m) above the Atlantic Ocean.

However, I need the latest documentation while I am finishing up my book, so I decided to start using it today, now that I am back from my travels to the UK. However, I discovered another reason while I like the desktop copy of BOL better and that is the searching functionality. The web-only BOL only has the search box to search the ENTIRE MSDN site, and it's not nearly as convenient as the search capabilities in the local documentation.

To my delight, I just found out that Microsoft has a nice search macro to help limit your search on MSDN to just SQL Server 2008 topics:

http://search.live.com/macros/sql_server_user_education/sql2008bolsearch/

It's still not as nice as the local searching, but since web-only is all we have for now, for the most recent BOL, it's definitely a good thing.

There is also a 2005 online search macro at:

http://blogs.msdn.com/sqlserverue/archive/2007/10/01/eliminate-extranneous-search-results.aspx

Have fun!

~Kalen

Nicholas Carr

A former executive editor of the Harvard Business Review, Nicholas Carr writes and speaks on technology, business, and culture. His provocative 2004 book Does IT Matter? set off a worldwide debate about the role of computers in business.

The Big Switch – Rewiring the World, From Edison to Google

Carr's core insight is that the development of the computer and the Internet remarkably parallels that of the last radically disruptive technology, electricity. He traces the rapid morphing of electrification from an in-house competitive advantage to a ubiquitous utility, and how the business advantage rapidly shifted from the innovators and early adopters to corporate titans who made their fortune from controlling a commodity essential to everyday life. He envisions similar future for the IT utility in his new book

• The Big Switch: Rewiring the World, from Edison to Google

read more

I just today found out about something that was in plain sight in the Books Online... but the documentation is so vast, I haven't discovered everything yet.

I blogged about clearing a single plan from cache in SQL Server 2005 a while back, and mentioned a rumor that there would be a new way to do this in SQL Server 2008. And then just today, someone posted a comment on that blog post, asking about the new 2008 method.

One of my contacts on the SQL Server team at Microsoft just sent me an email telling me about new features related to plan caching, as I am starting to work on updating that chapter for my 2008 book. He mentioned that DBCC now takes parameters, and one of the parameters is a plan handle!

DBCC FREEPROCCACHE [ ( { plan_handle | sql_handle | pool_name } ) ] [ WITH NO_INFOMSGS ]

There is full example in the Books Online, so I won't repeat it here. If you don't have a local copy of BOL handy, you can read about the new option to DBCC FREEPROCCACHE online here:

http://msdn.microsoft.com/en-us/library/ms174283.aspx

Have fun!

~Kalen

So what do I do with them?

I will be trying to sell them directly (because I still haven't even recouped all my travel expenses flying to New Jersey to record the training), and after I get back from London I'll post info on how to buy one. You can read some of the reviews here.

But there are SO many of them! So, I decided I will be giving one away to anyone who takes a seminar or class from me, from now until they are all gone. I will bring enough to London for all the registered delegates for my seminars: www.SQLKnowHow.com (It's a good thing I have Premiere Executive status on United, so I can bring extra luggage for no additional charge.)

I will also be bringing enough to give away to all attendees at my pre-conference seminar at PASS, later in November. (I can drive to the conference, so I don't have to worry about luggage restrictions for this one.)

And in every public class I teach, I will give one to all students.  Check my schedule at http://schedule.kalendelaney.com

Thanks!

~Kalen

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

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

Less than a week ago, I told you about making some major progress on my new book, and that it would "soon" be pre-orderable.

It turns out it already was orderable, as my helpful readers let me know.  So here is the link to Amazon, for all of you who want to get the book as soon as it hits the streets!

I just searched through my old blog posts, and realized I've never talked about the new name! The "Inside" name was getting a bit diluted, with all kinds of other Inside books on the market, including some "Inside SQL Server" books from other authors through other publishers. Since I wanted the title to be really clear about what the book was covering, we decided to change the name.

This name change is not without precedent. Some of you may recall that there used to be a book called "Inside Windows" which morphed into "Windows Internals".

I have a terrific group of co-authors, including two people who are (or were very recently) on the SQL Server development team at Microsoft. My co-authors are: Paul Randal, Kimberly Tripp, Conor Cunningham, and Adam Machanic (who wrote the Tracing Chapter for Inside SQL Server 2005: Query Tuning and Optimization)

Thanks!

~Kalen

I just finished a major milestone, so I thought I would share that here....

I was dreading working on Chapter 7, called "Special Storage", because half of it was brand new stuff and it was really gory technical detail stuff. The old stuff is row-overflow, LOBs and partitioning, and the new stuff is filestream, sparse columns and compression. I was calling this my "Monster Chapter", but now it is done! I just sent the chapter in to my editor this morning... so I can take a short break, maybe even an hour or so. :-)

None of the rest of the chapters have nearly as much new content, and nothing is so deeply technical (bit and bytes oriented) as explaining the storage of sparse columns and compressed data. Even though, each chapter will take a least a week and maybe two.

The problem is that I am scheduled to be teaching quite a bit between now and end of the year, so there are few weeks to write. My all-in due date is middle of December, after that there are chapter reviews plus introduction, etc to write. We are looking at mid-March for an availability date.

There should be a pre-release item up on Amazon soon. I'll let you know when that appears.

~Kalen

But today, I realized it is not all that uncommon. I was checking on Amazon to see if they had put up any pre-order information for my new book.  Well, I didn't see any news about my next SQL Server book, but I did see this:

http://www.amazon.com/Oracle-10g-Kalen-Delaney/dp/8448149394

I wrote a book on Oracle 10g and I didn't even know it! I even followed the link to buy new or used copies from resellers, the same kind of link my son followed, and saw this:

So the book is apparently available... a used copy from one reseller and a NEW one from another. I'm tempted to order it and see what I said. Because they say it is IN STOCK....

Or if one of you decides to buy it, let me know how you like it!

Thanks

~Kalen

SQLKnowHow has been offering one day seminars and multi-day training events since last summer, and my first event with them will  in early November.

This is, of course, not my first trip to the UK, or my first time speaking at an event organized by my friend Tony Rogerson, founder of the (in)famous and awesome UK SQL Server Community.

The venue is in Harpenden, Hertfordshire, which I happen to know well, because that is where Tony lives, along with his wonderful wife and his amazing little boy. In truth, I am actually looking forward to seeing Tony's son even more than Tony. (After all, I can see Tony at conferences, and can communicate by email whenever I need to. Plus (sorry Tony), his son is much cuter.)

Now that the previous event with Paul Nielsen has completed, seats are starting to fill fast for my seminars. I am presenting two one-day seminars and a three-day course:

3 November: SQL Server Data Storage Formats: Internals, Performance and Best Practices
(I have just finished writing the chapter for my next book on all the cool new storage, with compression, sparse columns and filestream, and I am really excited about this topic!)

4 November: SQL Server Concurrency Control: Locking, Blocking and Row Versioning
(This is always one of my favorite topics, because it is crucial to good performance, but is frequently overlooked in tuning discussions.)

5-7 November: SQL Server Data Internals and Tuning
(This 3-day seminar is a condensed version of my 5-day course which I have taught all over the world. We will start where the Data Storage Formats seminar leaves off, and look at how your storage structures can affect query performance. We'll go deeper into the structure of indexes and then talk about how your choice of indexes affects your query performance. We'll look at query tuning techniques, and plan caching, including new behaviors in SQL Server 2008.)

The great guys at SQLKnowHow are offering an early bird discount, too. Plus swag... plus great food....what else could you want?

See you there!

~Kalen

Yesterday, when writing about row compression, I realized I needed an easier way to create wide tables, so I wrote a script that allowed me to specify the number of columns, and also get a little creative with the datatypes, lengths and properties of the columns I was creating. I'll show you that script in just a minute!

Then just today, I read a blog entry by Simon Sabin, who indicated that even with sparse columns, you could not create a table with more than 1024 columns. In the CREATE TABLE statement, you had to limit yourself, but then could use ALTER TABLE to add more columns. Although I hadn't tested this yet, I was intrigued, and realized I could use my new script to test out this claim.

So here is my original script. You can replace the 100 (for the value of @numcols) by the number of columns you'd like, but the script will create one more, because it starts with a ID int identity column. So the script as is will create a table with 11 columns. In the loop that adds columns to the creation string, I had three different datatypes of columns that I can use. One third of the columns will be type int with a default of 0, one third will be char(5) with a default of 'hello' and one third will be varchar(25) with a default of 'this is a longer message'. You can modify this however you like. You can change the datatypes within the loop, or add more conditionals and change expression to use modulo of some other number.

DECLARE @create varchar(max);
DECLARE @tabname sysname;
DECLARE @numcols int;
DECLARE @col int;
SELECT @numcols = 100;
SELECT @tabname = 'wide' + CONVERT(varchar, @numcols);

SELECT @create = 'CREATE TABLE ' + @tabname +
      ' (ID int IDENTITY, ';

SELECT @col = 1;
WHILE @col < @numcols BEGIN
    IF (@col % 3) = 0
       SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
           ' int default 0,';
    IF (@col % 3) = 1    
            SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
             ' char(5) default ''hello'',';
     IF (@col % 3) = 2     
            SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
             ' varchar(25) default ''this is a longer message'',';
  SELECT @col = @col + 1;
END;
SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
           ' int default 0);'
PRINT @create 
EXECUTE (@create) 
EXEC ('INSERT INTO '+ @tabname + ' DEFAULT VALUES');
EXEC ('SELECT * FROM '+ @tabname);

To use this script to test the creation of a table with more than 1024 columns, I knew some (or most?) of the columns would need to be sparse. In addition, I found this Connect entry which indicated that if you do have more than 1024 columns, the table must have a column set, so it was easy enough to make that the last column that is added to the create string after the loop.

DECLARE @create varchar(max);
DECLARE @tabname sysname;
DECLARE @numcols int;
DECLARE @col int;
SELECT @numcols = 1200;
SELECT @tabname = 'wide' + CONVERT(varchar, @numcols);

SELECT @create = 'CREATE TABLE ' + @tabname +
      ' (ID int IDENTITY, ';

SELECT @col = 1;
WHILE @col < @numcols BEGIN
    IF (@col % 3) = 0
       SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
           ' int sparse,';
    IF (@col % 3) = 1    
            SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
             ' varchar(5) sparse,';
     IF (@col % 3) = 2     
            SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
             ' varchar(25) sparse,';
       SELECT @col = @col + 1;
END;
SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
           ' xml column_set for all_sparse_columns);';
PRINT @create 
EXECUTE (@create) 
EXEC ('INSERT INTO '+ @tabname + ' DEFAULT VALUES');
EXEC ('SELECT * FROM '+ @tabname);
 

Running this script, I was able to create a table with 1200 columns.

And you can too, if you're using SQL Server 2008.

Have fun!

~Kalen

http://www.simple-talk.com/content/article.aspx?article=563

Thanks, Richard!

~Kalen

The catalog views are the preferred interface going forward, as they are fully documented and the naming conventions are much more user friendly. There are also no bitstrings, like the status fields that need to be decoded. Each attribute stored in the view is available in a separate column.

The compatibility views are there to let you use the same code that you used in SQL Server 2000, hopefully temporarily, while you convert your code to the new metadata using the catalog views. The compatibility views have the same names as the system tables in SQL Server 2000, and the column names are the same.

You might think that writing a query against the compatibility views would then allow you to continue to write metadata queries that work with either SQL 2000 or SQL 2005. This might be true to some extent, but we might have to define what we mean by 'work'. Another change that happened in SQL 2005 was the separation of users and schemas.  Users can have permissions granted to them, including permissions to create objects, but the objects exist in containers called schemas.

In SQL 2000, users and schemas are treated interchangeably. If you create a user sue, SQL Server automatically creates a schema sue which is the user sue's default schema. When the user sue selects from an unqualified object t1, SQL Server 2000 assumes she is selecting from an object sue.t1. (If there is no object sue.t1, SQL Server then looks for dbo.t1.)

In SQL 2005, a user sue can have any schema as her default schema and there may or may not be a schema named sue.  When accessing an object that is not in  your default schema or in the dbo schema, you must qualify the object with the schema name, not the owner  name.

So what's my point here?

If you're looking up information about objects in aSQL 2005 database, would you rather know the owner of your objects or the schema they are contained in? I suggest that it is more often the case that you want to know the schema, so that you can then access the objects you are exploring.  And here's where the incompatibility of the compatibility views comes in.

In SQL 2000, the following code will give you the owner and name of all your user tables, and you can then use the information returned to access the objects. I wrote this query this morning in answer to a newsgroup posting asking for information about getting owner and object information from both SQL 2000 and 2005.

SELECT u.name as [user],
       o.NAME as [object]
FROM  sysobjects o
         INNER JOIN sysusers u
           ON o.uid = u.uid
WHERE   type = 'U';

In SQL 2005, the same code will give you the owner name, but that is NOT the name needed to access the objects. Here's an example that uses a test database, and creates a login sue, a user sue, and a schema called sue_schema. The user sue is given permission to create tables and to alter the sue_schema schema.  The user sue then creates a table called sue_table.

USE testdb;
GO
CREATE LOGIN sue
    WITH PASSWORD = 'sue_password';
CREATE USER sue FOR LOGIN sue;
GO
CREATE SCHEMA sue_schema;
GO
GRANT CREATE TABLE TO sue;
GO
GRANT ALTER ON schema::sue_schema TO sue;
GO
EXECUTE AS user='sue';
GO
CREATE TABLE sue_schema.sue_table (a int);

We can run the metadata query above, and add a filter to look for sue-type objects:

SELECT u.name as [user],
       o.name as [object]
FROM  sysobjects o
         INNER JOIN sysusers u
           ON o.uid = u.uid
WHERE   type = 'U' AND o.name like '%sue%';

I get these results:

user    object
------- ---------
sue     sue_table

However, for table access, this information is not helpful. I cannot execute the following statement, even as dbo with full privileges:

REVERT; -- so I no longer execute as user 'sue'
SELECT * FROM sue.sue_table;

So is the metadata query really compatible? It may be compatible in the facts it gives back, but in function, it is not. For SQL Server 2005, the query does not tell us what we need to know in order to access object. Instead, we'll need a catalog view sys.schemas, which has no equivalent in SQL 2000.

SELECT s.name as [user],
       o.name as [object]
FROM  sysobjects o
         INNER JOIN sys.schemas s
           ON o.uid = s.schema_id
  WHERE   type = 'U' AND o.name like '%sue%';

So because of the split between users and schemas, we can't really write one query that gives us information about how to access objects in both SQL 2000 and SQL 2005.

~Kalen