Search results matching tags 'denali', 'SQL Server 2011', and 'CTP1'

SQL Server v.Next ("Denali") : How a columnstore index is not like a normal index

AaronBertrand — Mon, 28 Feb 2011 17:34:00 GMT

At the end of my Denali presentation at SQL Saturday #65 in Vancouver, a member of the audience asked, "What makes a columnstore index different from a regular nonclustered index?" At the end of a busy day, I was at a loss for an answer, and I'll explain why.

First, I'll briefly explain the basic, core, high-level functionality of a columnstore index (you can read a lot more details in this white paper). Basically, instead of storing index data together on a page, it divvies up the data from each column into its own set of pages. If you are after the data from only one column, this doesn't provide a great advantage over a traditional index, since you're not going to be able to store that many more rows on a page.

The reason I was having a hard time articulating the benefit is because I was thinking about a simple non-clustered index with one column, and comparing it to a columnstore index with only one column defined. The benefit of the columnstore index is that you can basically declare it against most or all of the columns in a table, effectively similar to creating an index on each column - and this is where column store indexes provide the largest benefit. The differences between a column store index and a handful of indexes for each column are (1) size due to the structure of the index and the fact that compression will likely work much better when you can fit more like values on a page, and (2) you only pay for the index maintenance on a columnstore index once, because of what I'll describe next.

The columnstore index has limitations that definitely make you feel like the grass is greener on the other side. Most importantly, in Denali, due to the cost of maintenance, a columnstore index will be read only... essentially, you will need to rebuild the index when data changes. Some other limitations: only one columnstore index per table, the index must be partition-aligned, can't be filtered, and the base object must be a table (so, no indexed views).

This feature is definitely geared to data warehouse scenarios, or where you are free to rebuild indexes nightly or on some other interval. I can't give you a good idea about the cost of maintaining this index manually compared to multiple traditional indexes, since the feature did not make the cut for the publicly available CTP1. But I hope to publish some blog posts with real numbers on both the read and write sides when the next public CTP is made available. I am fairly certain I will be able to demonstrate cases where this type of index will drastically increase read performance (without losing substantial write performance), provided that you are in a scenario where you can update the data on an infrequent schedule, such as nightly.

If you want some detailed background on this technology, please check out Dr. David DeWitt's PASS Summit 2009 keynote slide deck. And if you’re a PASS member, you can access the video at sqlpass.org (login required).

[I am currently at the MVP Summit, and I just want to clarify (in case anyone was wondering), none of the material I discuss above is in violation of NDA.]

SQL Server v.Next (Denali) : Exploring THROW

AaronBertrand — Mon, 22 Nov 2010 17:20:00 GMT

Sadly, THROW is not mentioned on the Programmability Enhancements (Database Engine) topic of Denali's "What's New" section. So, unless you were at PASS or have been reading the various blogs from the keynotes and other Denali sessions, the presence of this keyword may be news to you. I wanted to touch briefly on what THROW can do and, more importantly, what it can't do - before you start thinking about abandoning all usage of RAISERROR (or "RAISE ROAR" as Tobias calls it).

How THROW and RAISERROR differ

THROW outside of a CATCH block acts similar to RAISERROR, with a few notable exceptions:

The message_id parameter does not need to be defined in sys.messages:

THROW 66666, 'Hi', 1;

Result:

Msg 66666, Level 16, State 16, Line 1
Hi

With RAISERROR, you can use a string for the first parameter, but if you use a number that is not represented in sys.messages:

RAISERROR(54321, 16, 1);

Result:

Msg 18054, Level 16, State 1, Line 1
Error 54321, severity 16, state 1 was raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage.

Why should you care? Well, if you have applications that rely on certain error message numbers coming out of SQL Server (and ignore the text of the error message), converting to THROW will allow you to migrate to new servers without having to set up all of the messages in sys.messages.

The message_id parameter must be an INT (not a BIGINT) >= 50000. If you try to THROW a system message, you will get an error message instead of the error you wanted:

RAISERROR(14088, 16, 1, N'foo');
GO
THROW 14088, N'foo', 1;

Result:

Msg 14088, Level 16, State 1, Line 1
The table 'foo' must have a primary key to be published using the transaction-based method.
Msg 35100, Level 16, State 10, Line 1
Error number 14088 in the THROW statement is outside the valid range. Specify an error number in the valid range of 50000 to 2147483647.

There is no token substitution within the command itself, so printf formatting is ignored. Let's say we've added this message to sys.messages:
EXEC sys.sp_addmessage
    @msgnum = 66667,
    @severity = 16,
    @msgtext = N'There is already a %s named %s.';
With RAISERROR we can simply say:
RAISERROR(66667, 16, 1, N'foo', N'bar');
Result:

Msg 66667, Level 16, State 1, Line 1
There is already a foo named bar.

However, THROW does not accept any parameters for substitution. If you try:
THROW 66667, N'There is already a %s named %s.', 1, N'foo', N'bar';
Result:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.

Since repeating the text defeats the purpose of saving the error message in sys.messages in the first place, you can get around this using FORMATMESSAGE():
DECLARE @msg NVARCHAR(2048) = FORMATMESSAGE(66667, N'foo', N'bar');
THROW 66667, @msg, 1;
Result:

Msg 66667, Level 16, State 1, Line 2
There is already a foo named bar.

Cumbersome, but it works.

The severity level for THROW is always 16 (unless it is a re-throw inside CATCH), and there is no way to force the breaking of the connection like we can with RAISERROR. Assume we have added this error message:

EXEC sys.sp_addmessage 
    @msgnum   = 66668,
    @severity = 24,
    @msgtext  = N'This is really bad.';

If we use RAISERROR, we can obey the severity level by re-specifying and using WITH LOG:

RAISERROR(66668, 24, 1) WITH LOG;

Result:

Msg 2745, Level 16, State 2, Line 1
Process ID 55 has raised user error 66668, severity 24. SQL Server is terminating this process.
Msg 2745, Level 16, State 2, Line 1
Process ID 55 has raised user error 66668, severity 24. SQL Server is terminating this process.
Msg 66668, Level 24, State 1, Line 1
This is really bad.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

There is no way to do that with THROW:

THROW 66668, N'This is really bad.', 1;

Result:

Msg 66668, Level 16, State 1, Line 1
This is really bad.

There is no THROW equivalent to WITH NOWAIT for immediate buffer output. With RAISERROR we can do this:
RAISERROR(N'Printing status...', 0, 1) WITH NOWAIT;
This can be quite useful in place of PRINT when monitoring a query with multiple steps, as it prevents waiting for the buffer to fill up before seeing real-time messages in the results pane of Management Studio.
Unlike RAISERROR, THROW honors XACT_ABORT (see Neils Berglund's post for more information).
There is no way to use WITH LOG in combination with THROW, or to "THROW" a non-severe error (e.g. one that prints in black in Management Studio instead of red, like the above example with severity level 0).

THROW inside CATCH

THROW inside a CATCH block acts like RETHROW - it will re-raise the exception that transferred to the CATCH block in the first place. An example:

BEGIN TRY
    RAISERROR(N'Hi from try.', 16, 1);
END TRY
BEGIN CATCH
    PRINT N'Hi from inside CATCH.';
    THROW;
END CATCH

This will allow you to perform other tasks, such as rollbacks, notifications, etc. before eventually throwing the same errors back to the caller - without having to understand and re-code all of the possible outcomes from within the TRY block. Note that THROW inside CATCH can re-throw multiple errors, not just the most recent one (which is all you have access to from ERROR_MESSAGE() and its cousins). And this is the only place where THROW by itself is valid syntax.

I am not going to pretend to be an expert in error handling, so I will leave it up to Erland to give you a great primer:

Error Handling in SQL 2005 and Later

A few more items to consider

Note that THROW() is one more case where the preceding statement needs to end with a proper statement terminator. So just as a gentle reminder, start using those semi-colons!

Also note that there is still a very noticeable omission in the TRY/CATCH error handling model: FINALLY.

And finally (no pun intended), some housekeeping

The current version of Denali's Books Online indicates that RAISERROR is deprecated. From the RAISERROR (Transact-SQL) topic (http://msdn.microsoft.com/en-us/library/ms178592(SQL.110).aspx):

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. New applications should use THROW instead.

Books Online is currently incorrect, and should be updated soon. There is currently no publicized plan to remove RAISERROR; what the topic should state is that only the old-style RAISERROR is deprecated, e.g.:

RAISERROR 54321 N'This is an old-style RAISERROR.';

Of course you can still use this syntax today, but it has been on the deprecation path for some time. If you run the above code on Denali, SQL Server 2008 R2 or even SQL Server 2008, you will see this performance counter increase:

SELECT [object_name], instance_name, cntr_value
    FROM sys.dm_os_performance_counters
    WHERE [object_name] LIKE N'%:Deprecated Features%'
    AND instance_name = N'Oldstyle RAISERROR'
    AND cntr_value > 0;

SQL Server v.Next (Denali) : Books Online

AaronBertrand — Sun, 14 Nov 2010 03:17:00 GMT

There are posts elsewhere that go through the whole rigamarole (for example, this post from Don Gill, SQL Server's Group Content Publishing Manager). I just wanted to highlight the key bullet points:

if you want to use Books Online locally, you will need to:

install Books Online from Denali setup. Note that Management Studio is now a requirement, so select both if you haven't already. See below for a potential bug here if you've installed Denali but initially did *not* choose Books Online.
if you performed 1. before Thursday afternoon (or weren't connected to the Internet during installation), you'll need to go to the Manage Help Settings option under Start / All Programs / Microsoft SQL Server Denali CTP1 / Help & Community, and choose Update under Check for Updates Online. There should be about 10 items (~300MB) to download and/or install... let them do their thing.
Now launching Books Online from the Start Menu option will work correctly. If you get a 404 with the local URL, launch the Manage Help Settings app again, and choose "Install Content from Online." There should be 169 books available; scroll down to SQL Server and click Add. If Add is not available and Local is displayed, but you are still getting a 404 for the content, file a bug on Connect.

if you want to use Books Online remotely, bookmark this URL:

http://tinyurl.com/Denali-CTP1-BOL
Note that you can still choose whether to use local or online help (see the following dialog, also available from the Manage Help Settings tool referenced above):

As a side note, keep in mind that "local" now really means web-based (but not Internet-based). When you launch Books Online and your preference is set to local, you will go to a URL on localhost (127.0.0.1) on an arbitrary port number (kind of like how the development web server in Visual Studio works). Don't worry, you don't need to install IIS or the web server role in order for this feature to work.

If Books Online is still not working for you, I'd love to hear about it, and hope I can help you resolve the issue!

EDIT: two bugs you should be aware of:

Connect #622180 : SQL Server Denali - installing BOL to additional nodes

The moral here: don't install SQL Server Books Online on a cluster that isn't going to stay the way it is. BOL doesn't currently have the ability to adapt to new nodes that are added after the feature is installed, so depending on the active node, you may or may not be able to access local help. My suggestion is to not bother installing Books Online on a cluster in the first place - put it on your workstations, test machines, etc.
Connect #622099 : [Denali Setup] : HelpLibManager not found if installed later

There is an issue that occurs if you don't install Books Online when first installing Denali, but then decide to install it later. On one machine so far, this has left Books Online completely crippled. So if you haven't installed Denali yet and you think there is any chance you may want BOL later (unless you are on a cluster), make sure to check the box.

This second problem was fixed with a repair. Launch Denali setup, and on the Maintenance tab, choose Repair. When you get to the Select Instance screen, choose <<Repair shared features only>> from the dropdown:

Note that the dialog you get when you launch Books Online for the first time is not worded very well. Does "Yes" mean I am choosing to view content online, or does "Yes" mean I am going to launch a tool that is going to allow me to choose between online and local, or something else?

It turns out this is setting the preference to view content online. You can change it later by going to Manage Help Settings as illustrated above. If you use local help, even after all of this, you may still end up with a 404 error ("The requested topic cannot be found in the local Help content.") I hope that these issues will be sorted out by the time the next CTP rolls around; in the meantime, just use the online version of Books Online.

SQL Server v.Next (Denali) : The instance is down; what happened?

AaronBertrand — Fri, 12 Nov 2010 22:36:00 GMT

If you come across a SQL Server that is not running, it can be troublesome to determine what happened and when. You can run through the Windows event viewer, but this can be a real needle in a haystack problem on busy servers, especially if there are a lot of instances. Wouldn't it be great to be able to easily read the SQL Server error log, even though the server is down? Currently you would typically do this through xp_read_errorlog - which requires the server to be up. So we've already talked about a needle in a haystack problem, now we also have a chicken and egg problem.

In Management Studio in Denali, you may notice a slightly different context menu when you right-click an instance in Registered Servers:

Notice the highlighted menu option "View SQL Server Log" - enabled even for a server that Management Studio is not online. But don't press it yet, unless you have started SSMS as administrator, by selecting the following option from the right-click context menu:

If you haven't launched SSMS as administrator, you will get the following error message (Attempted to perform an unauthorized operation. (SqlManagerUI)):

[This is because the error log is read via WMI. One limitation of this minor implementation detail is that you won't be able to use this feature if you launch SSMS from a regular shortcut that has been pinned to the taskbar in Windows 7 - right-clicking does not provide an option to Run as administrator. I hope the manageability team makes it more obvious that run as administrator is required - I guess a better error message will do.]

When the stars align, you will get the following (click to embiggen):

So you can review the last instance of the error log even though the service is currently offline; you can also go back and review any log file that hasn't been cycled. Cool, right? Of course, it requires that the Windows server hosting the instance of SQL Server is running.

Blogging from the PASS keynote : 2010-11-10

AaronBertrand — Wed, 10 Nov 2010 16:54:00 GMT

It is the second day of keynotes at PASS, and we're gearing up for Quentin Clark's presentation. There are not as many kilts around as I would have expected, and I'm only noticing because this year I'm wearing one too. Actually, at the bloggers' table, there is a pretty good kilt contingent, and I think we've all been a little surprised by the temperature of the metal portions of our chairs.

Bill Graziano steps onto the stage, sans kilt, and asks if he should wear a kilt next year. The crowd responds with a resounding yes. He announces that the 2010 Outstanding PASS Volunteer is Lori Edwards, and that the PASSion Award goes to Wendy Pastrick.

Then the exciting part: governance and financials. Bill reminds us that PASS is completely self-funded through sponsorships and attendance fees. Bill welcomes everyone to talk about the financials at PASS, and you can also log into http://www.sqlpass.org/governance to get as much detail as you want.

Next Bill draws the winner for the Wheel of SQL contest, Denis Paritz, who gets an XBox Kinect. Then he reminds us that the PASS appreciation party is at 7:00 PM at GameWorks.

Quentin Clark takes the stage to talk about SQL Server Codename "Denali." He talks about how they've been dedicated to raising the "Mission Critical" bar in every release since SQL Server 2000, while also focusing on manageability and TCO. His bullet points for how Denali meets their three major focus pillars:

Mission Critical

Enhanced High Availability
Up to 15K partitions for VLDB
HADR for Beyond Relational
Performance for FTS & FILESTREAM
Columnar Index - potential 10x performance with better predictability

(While he's talking about this, twitter is flooded with "move on to the demo already" comments.)

SQL Server AlwaysOn:

faster failover
multi-db failover
multiple secondaries
active secondaries
faster client connection redirection
multi-site clustering

Gopal Ashok comes on to demonstrate AlwaysOn. He shows the Availability Group wizard in Management Studio, which allows you to group databases together for failover purposes, set automatic failover for the group to other instances of SQL Server, and allow some of those servers to be readable while in secondary mode (read: no more snapshots!). This was a pretty cool demo, but please be aware that this functionality is not available in the CTP that we were given access to yesterday.

Productivity

IT Pro

AlwaysOn
Flexible server roles
Default schemas for groups
Enhanced audit and extended events
Improved troubleshooting & performance analysis
Distributed replay for app testing & upgrades
Contained database (logins and jobs)

Developer

Unlock semantics for search in unstructured data (e.g. XML)
Advanced 2D spatial, aggregates, faster queries
T-SQL enhancements (SEQUENCE, OFFSET, THROW)
Interoperability with PHP and Java
FileTable

Rohan Kumar comes on to demonstrate FileTable. This looks and smells like a combination of FILESTREAM and SharePoint. This feature actually makes me very nervous. And he didn't answer an important quetion: what happens if you backup this database and restore it somewhere else?

Don Box comes on to demonstrate Codename Juneau - the new BIDS. It is a unified development tool that works with SQL Server & Visual Studio, and provides SQL Server Edition-specific development and deployment. There's a new Table Designer with two panes - the junk you see in today's designer, and a lower pane with the actual T-SQL for the CREATE. You can generate an ALTER for the diffs, but somehow I don't think they're going to put Red Gate out of business just yet. He demonstrates enhanced refactoring and Go To Definition. On twitter, the reaction to these features is mixed, leaning toward sad trombones (as @Kendra_Little put it).

Pervasive Insight

Project "Crescent"
Breakthrough in-memory PowerPivot perf
Enterprise data integration tools
Highly interactive web-based visualization
One semantic model for reporting & analysis
In-memory tech in SSAS
PowerPivot Model Support in BIDS
MDS, IAL, DQS (data quality services) for data cleansing

Jeff Bernhardt comes on to demonstrate Enterprise Data Integration and Management. He shows Data Quality Services using Master Data Services, but the only applause comes from Undo (Ctrl+Z) in SSIS' package designer.

And on a closing note...

On the way out, all attendees get DVDs containing CTP1. So those unable to get decent bandwidth in the convention center (that is pretty much all of us), you can install it right away without waiting for the download.