Search results matching tag 'SQL Server 11'

SQL Server 2012 : The "Launch Date" is not what you think it is

AaronBertrand — Mon, 23 Jan 2012 21:58:00 GMT

I see a lot of people getting really excited. There is a "virtual launch event" for SQL Server 2012 being held on March 7, 2012. You can read more about the event at http://sqlserverlaunch.com/.

Let me throw out a dose of reality: if you are not on a TAP or otherwise going live with a private build or release candidate, you will not be installing and deploying SQL Server 2012 on March 7th. I promise. This date will not mark a release, go-live, or general availability. These launch events are marketing-centric sessions to get you excited about the product. Will you be able to download Express editions from the Microsoft web site, and other SKUs from MSDN or your volume licensing portal, shortly after that? Sure. The next day? Almost certainly not. I actually don't remember the lag between the launch event (we'll call it a soft launch) and the RTM availability for previous releases, so I'm not going to throw out any conjecture there. But from all previous launch events by Microsoft, across various product lines, the CD/DVD/download has never been available at the same time as the event. Let's not forget that this virtual launch event is not the only "launch event" in March - later in the month, both SQL Connections and SQL Bits are also serving as launch events. Launch and release are similar words, but not identical.

Just wanted to keep it real, and prevent people from getting too excited or planning deployments around this launch event. It is just a marketing event, folks. Worth attending, no doubt, but you won't get magic golden tickets with a download code on that day.

PS If I end up being wrong, I'll just delete this post. :-)

SQL Server 2012 : A couple of notes about installing RC0

AaronBertrand — Sat, 19 Nov 2011 15:44:00 GMT

If you're going to install Distributed Replay Controller

I've posted about this on twitter a few times, but I thought I should put it down somewhere permanent as well. When you install RC0, and have selected the Distributed Replay Controller, you should be very careful about choosing the "Add Current User" button on the following dialog (I felt compelled to embellish with the skull and crossbones):

In the case where you're installing in a simple workstation or VM that is *not* in a domain, if you click this button (it may also happen for the Add... button), you may experience a little delay in setup, then a non-responding dialog, and then setup will crash with the following error:

For searchability:

SQL Server Setup has encountered the following error:
There was a failure to validate setting CTLRUSERS in validaton function ValidateUsers.
Error code 0x85640004.

This is because the dialog is hard-wired to use Active Directory, since DRU requires domain users (for more info see this MSDN page). And if you are really interested in the DRU feature set, you should check out a blog-series-in-the-making by Jonathan Kehayias (@SQLPoolBoy).

When you click OK, you will not pass GO, you will not collect $200, you will simply get to start setup over. Setup tries to continue, but eventually you will get the "SQL Server 2012 RC0 Setup has stopped working..." dialog, and you will be returned to the Installation Center.

I've filed a bug about this issue on Connect (#705158). So with any luck, they'll figure out this issue quickly and maybe, by the time you install the release candidate, the updated setup files will be waiting for you (since Setup now has the ability to update itself at runtime). My advice in the meantime is to install the DRU components, but do not configure user accounts using setup. Instead, configure them after the fact as explained in Modify the Controller and Client Services Accounts on MSDN.

If you're using SharePoint 2010

You will need to install SharePoint 2010 Service Pack 1 first. However you will need to re-create any PowerView reports that were generated using CTP3.

If you're upgrading from CTP3

Depending on the components you've selected, you may be asked at some point during setup to produce the Visual Studio 2010 DVD. The problem is that if you switch your mounted DVD from the RC0 ISO to the Visual Studio ISO, when setup resumes it will complain about various things and ultimately the engine and other important components will fail to install.

So, you'll want to mount both the Visual Studio 2010 ISO / DVD and the RC0 ISO / DVD as separate drive letters in order to prevent setup from getting confused. This may be challenging on a VM, which can typically only be exposed to a single DVD drive from the host, unless you install ISO mounting software inside the VM. I use SlySoft Virtual CloneDrive.

If you're using a burned DVD or mounted ISO

This is a very minor annoyance, but after setup if you un-mount the ISO or remove the DVD, and forget to close the Installation Center first, you won't be able to close the dialog without using Task Manager. If you try to close it you get a dialog that keeps popping up no matter which of its options you choose:

The tasks you want to kill in Task Manager are dw20.exe and LandingPage.exe. It may take several attempts to kill all of the dialogs that have been spawned. It should come as no great shock that I've complained about this one on Connect as well (#706586).

In general

Setup has a few new prerequisites in RC0 that weren't there in CTP3. You should make sure that you're completely current on Windows Updates, including Windows 7 Service Pack 1 or Windows Server 2008 R2 Service Pack 1. And if you have any Visual Studio 2010 components on the box already, please install Visual Studio 2010 Service Pack 1 *before* installing RC0.

SQL Server 2012 : Changes to system objects in RC0

AaronBertrand — Fri, 18 Nov 2011 22:07:00 GMT

As with every new major milestone, one of the first things I do is check out what has changed under the covers. Since RC0 was released yesterday, I've been poking around at some of the DMV and other system changes. Here is what I have noticed:

New objects in RC0 that weren't in CTP3

Quick summary:

We see a bunch of new aggregates for use with geography and geometry. I've stayed away from that area of programming so I'm not going to dig into them.
There is a new extended procedure called sp_showmemo_xml. The name could send me all over the place guessing whether it has something to do with Access, straight XML handling, or something else. This one doesn't seem to be documented in Books Online so, for now, I'm going to leave it alone.
The undocumented stored procedure sp_MSgetversion has returned. I observed previously that this procedure had been dropped, and brushed it aside - being undocumented and unsupported, it shouldn't be all that prevalent. Who knew that Microsoft's own replication code relied on it?
There are some new AlwaysOn DMVs and a validation stored procedure.
There is a new DMV and catalog view around Resource Governor, reflecting one of the changes I outlined last month.
By far the most interesting to me are the extended stored procedures around parallel nested transactions. Thus far these are undocumented and I would not be shocked if they remain so for the time being. I would hazard a guess as to what these are for but I don't want to sound like a fortune teller so, again, I'll leave them alone - for now.

New columns in existing DMVs

Some relatively self-explanatory new columns for the AlwaysOn and Resource Governor DMVs:

Columns that have changed definition

Several DMV columns have been expanded in size, except in the case of one, which was made smaller. These are all NVARCHAR columns:

Modules that have been modified

It's impossible to tell which modules have been modified since the previous CTP, since all but a few system and internal objects get a new create_date when a new build is produced. But at least for objects represented in sys.sql_modules, we can obtain a list of objects where the size of the definition has changed, meaning there may be some meaningful changes we want to investigate further. I definitely spot a few that I am going to check out more thoroughly:

Hope this information is useful. Let me know if you spot any under-the-cover changes I haven't noticed.

Connect Digest : 2011-05-02

AaronBertrand — Mon, 02 May 2011 11:15:00 GMT

This week, after seeing a lot of the DMV enhancements made in SQL Server 2008 R2 SP1, I thought I would take a look at some dynamic management view/function requests.

Page split personality

Michael Zilberstein blogged on Monday about how Extended Events and the transaction log differ in the way they report page split information, and that it would be useful to have accurate information about these events available in the DMVs. So he filed this Connect item:
#664900 : Please add page splits data per index, index level and page split type
I asked for a very similar item back in 2008, but thought that it belonged in sys.dm_db_index_physical_stats:
#388403 : Include page split information in sys.dm_db_index_physical_stats
They've already provided additional info via Extended Events (see #388482 : SQL Server Extended Events Page/Split Event Additions) so, hopefully, they will take our DMV requests seriously as well.

How stale is my database?

Kimberly Tripp (blog | twitter) posted this item, asking for a column in sys.databases to reflect last accessed time (I think it belongs in an existing or new DMV, not in the catalog view). An additional comment on the item suggested a terrific enhancement to this potential feature, where each individual login would be represented with their last access time, making it easy to differentiate when a database is only being accessed by system or background processes.
#659846 : Database last accessed time
They've recently rejected a similar item requesting last accessed time for objects within a database (see #454714 : I should be able to see the last time an object was accessed in sysobjects), though that one had zero votes, so I'm hopeful that Kimberly's request may be deemed both easier to implement and more important to the community.

I'm an E7-8800, what are you?

Glenn Alan Berry (blog | twitter) asked for an additional column on the sys.dm_os_sys_info DMV that reflects the CPU's description (e.g. Xeon X5505). As you may have noticed on his blog, Glenn spends a lot of effort on analyzing performance of workloads on different CPUs, and there are some pretty substantial differences between CPUs that may look quite similar to the average person - especially when seeing vastly different performance on two servers that are otherwise very similar (same amount of RAM, same SAN, etc).
#657435 : Add a processor_description column to sys.dm_os_sys_info in SQL Server Denali
An additional comment requested that the true observed speed would be shown, as opposed (or in addition) to the manufacturer's stated clock speed - the true speed is a number than can vary greatly if you are using power saving options.

You're a word, but are you reserved?

This almost certainly wouldn't be in a DMV, but I still like the idea and it almost fits here. Greg Low (blog | twitter) is requesting a system view of some kind that exposes a list of reserved words - my comment here was that relying on IntelliSense alone is a fool's errand.
#653455 : System View that lists Reserved Words
On a somewhat-but-not-really-related note, Meredith Ryan-Smith (blog | twitter) recently blogged about a workaround she used to export all of the object names from sys.all_objects into a custom dictionary for use in Word, PowerPoint, etc. to avoid the ugly red squiggly lines pointing out that you misspelled sp_server_diagnostics and sp_who2. I think that if the metadata Greg is asking for were made available, then this idea would be easy to extend to reserved words, so that Word also wouldn't try to convince you that perfectly valid "words" like COLUMNSTORE and DATA_COMPRESSION are spelled wrong.

Do you really like DBCC?

Greg Low also requested that the information returned by running DBCC SHOW_STATISTICS be made available through a DMV (another user suggested adding a column like is_stale to sys.stats, but I think a separate DMV makes more sense).
#611155 : DBCC SHOW_STATISTICS info should be available as a DMV
Exposing this information in a DMV would also, presumably, make it easier to access information like STATS_DATE without having to be in the context of that database (see #654972 : STATS_DATE only works as documented when current db is where the tables are).

SQL Bits 8: "What's New in SQL Server Denali?"

AaronBertrand — Sat, 09 Apr 2011 23:25:00 GMT

At SQL Bits I presented my "What's New in Denali?" deck in two different sessions - once on Friday to about 120 people, and once on Saturday to about 80 people. You can download the deck and samples below.

Meme Monday: Say something somewhat meaningful in 11 words or less

AaronBertrand — Mon, 04 Apr 2011 11:43:00 GMT

I will be delivering my Deep Dives 2 draft on time.

Presenting "What's New in SQL Server Denali" for the Kent, England user group

AaronBertrand — Thu, 31 Mar 2011 17:20:00 GMT

This afternoon (well, evening to them) I presented my "What's New in Denali" deck remotely for the Kent, England SQL Server & .NET User Group. The link for the event is here and you can download the deck and samples below.

SQL Saturday #67, Chicago: "What's New in SQL Server Denali"

AaronBertrand — Sat, 26 Mar 2011 15:01:00 GMT

Today I was in Chicago for SQL Saturday #67 to present my "What's New in SQL Server Denali" deck. You can download the deck and samples below.

I mentioned during the talk that THROW is another command added to the list that require the previous statement to be terminated with a semi-colon. Here is the blog post I referenced that talked more about why you should start using semi-colons today.

I liked the feedback mechanism at this event. Everyone was handed an evaluation, and the drawings took place after each session at the chapter table downstairs. Much more compelling to get your evals in right away, and even better, I get them back right after that. Which means I can tally my scores and see what went well, and more importantly, what didn't go well (and wasn't obvious). The evals were much simpler than some other events. Two basic questions and room for comments (not everyone left comments, and not everyone answered both questions):

Expectation (circle one): Did Not Meet / Met / Exceeded

Met: 23 / 34
Exceeded: 11 / 34

Overall quality (5 = great):

3: 4 / 32
4: 11 / 32
5: 17 / 32
Average: 4.41

Not top scores, no, but I still feel pretty good about this. What I liked most? Seven comments included one of my brand words, "knowledgeable." Well, a couple used the variation "knowledge" - but that counts, right?

The comment that surprised me most was a complaint that I didn't cover any BI. Even though I stated multiple times at the beginning that I wouldn't be covering BI - and even dedicated my very first "real" slide to this fact. Still, I've taken the feedback. For future events I'm going to change the title of the session to "SQL Server Denali : What's New in Engine, Setup and Tools."

I also gave a quick demo of SQL Sentry Plan Explorer in our vendor session at lunch (we were a "You Rock!" sponsor of SQL Saturday #67, after all). I didn't use a deck and it was all demos. Plan Explorer is free; if you haven't already tried it, please go to the download page and read my previous blog posts about the tool.

Presenting "What's New in SQL Server Denali" for WVPASS

AaronBertrand — Thu, 17 Mar 2011 21:00:00 GMT

Recently I was asked by John Sterrett (blog | twitter) to present to his SQL Server user group (WVPASS) in Wheeling, West Virginia. Well, today is the day, and I will be presenting, "What's new in SQL Server Denali" at tonight's meeting, sponsored by SQL Sentry. The slide deck is attached below; the samples are available here.

If you attended this presentation, please feel free to provide feedback at SpeakerRate.

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.]