Search results matching tag 'denali'

Memory Manager Configuration changes in SQL Server 2012

SQLOS Team — Thu, 12 Jul 2012 14:07:00 GMT

Continuing from yesterday's article about Memory Manager surface area changes in SQL Server 2012, this post looks at corresponding configuraton changes for the new Memory Manager. The surface area change article mentioned that the Memory Manager redesign resulted in being able to more accurately size and govern all SQL Server memory consumption. Let's look at what this means in terms of sizing the total memory usage.

Max server memory

In SQL Server 2008, the max server memory configuration setting only governed single 8K page allocations. CLR allocations, multi-page allocations, direct Windows allocations (DWA) and memory required by thread stacks would not be included, so you'd have to set the -g memory_to_reserve startup setting on 32-bit systems if you're using CLR for example, to reserve sufficient address space.

Setting max server memory becomes more straightforward with SQL Server 2012. The redesigned Memory Manager takes a central role in providing page allocations for the other components, and the max server memory setting governs all memory manager allocations.

With all page allocations are governed, including CLR page allocations, instance sizing is more predictable, which can help a lot in memory constrained and multi-instance scenarios. Direct Windows Allocations, (i.e. calls to VirtualAlloc()) will still remain outside of Memory Manager control.

Here's a summary of the how the memory startup option settings changed in 2012:

SQL Server 2008 R2

Sp_configure option

Default setting

Lowest possible value

Highest possible value

Min server memory

16 (MB)

Value
less than max server

memory setting

Max
server memory

2147483647

(available memory in the system)

16 (MB)

2147483647

(available memory in the

system)

SQL Server 2012

Sp_configure option

Default setting

Lowest possible value

Highest possible value

Min server memory

16 (MB)

Value
less than max server

memory setting

Max
server memory

2147483647

(available memory in the system)

32 bit - 64 (MB)

64 bit – 128 (MB)

2147483647

(available memory in the

system)

sp_configure awe_enabled

SQL Server 2008 R2 was the last release to support the awe_enabled option, which allowed use of memory above 4GB on 32-bit systems. In SQL Server 2012 32-bit architectures can no longer use >4GB, though you can still set this value on 32-bit systems to allow the use of locked pages but it's not needed on 64-bit. Note this setting does not affect the way Address Windowing Extensions are use to implement locked pages in memory on 64-bit systems.

- Guy

Originally posted at http://blogs.msdn.com/b/sqlosteam/

Memory Manager surface area changes in SQL Server 2012

SQLOS Team — Wed, 11 Jul 2012 13:31:00 GMT

There were various changes to memory related DMVs, DBCC memory status, and Perfmon counters in SQL Server 2012 as part of the redesign of the Memory Manager component of SQLOS. The Memory Manager redesign resulted in being able to more accurately size and govern all SQL Server memory consumption, making page allocations more efficient, and better handling and tracking of NUMA node memory allocations. This post aims to document these surface area changes in one place to serve as a quick reference.

Some of these changes were to standardize memory counters as "kb" rather than bytes or pages, continuing a process begun earlier, but now more relevant since the single and multi-page allocators were replaced by an any-size page allocator, so counting pages allocated would no longer give a meaningful view of memory use. This consolidation of allocators also resulted consolidated counters for page allocation.

Other changes reflect the central role of the redesigned Memory Manager in being the interface to the OS, providing page allocations for the other components. A modular summary of the architectural changes is shown below:

DMV Changes

sys.dm_os_memory_nodes

The consolidation of the single and multi-page allocators is reflected in the pages_kb column in sys.dm_os_memory_nodes. The new foreign_committed_kb counter is a result of the improved NUMA management and reporting in SQL 2012 and identifies memory on a noe that belongs to a remote node, which can be useful if you're troubleshooting slower memory access. We will always try and use local memory first, at least until the target memory is reached.

sys.dm_os_memory_nodes
SQL Server 2012	SQL Server 2008 R2
pages_kb	single_pages_kb + multi_pages_kb
foreign_committed_kb	-

sys.dm_os_sys_info

The main memory manager changes here are to standardize memory counters.

sys.dm_os_sys_info
SQL Server 2012	SQL Server 2008 R2
physical_memory_kb	physical_memory_in_bytes
virtual_memory_kb	virtual_memory_in_bytes
committed_kb	bpool_committed
committed_target_kb	bpool_commit_target
visible_target_kb	bpool_visible

sys.dm_os_memory_cache_counters

sys.dm_os_memory_cache_counters
SQL Server 2012	SQL Server 2008 R2
pages_kb	single_pages_kb + multi_pages_kb
pages_in_use_kb	single_pages_in_use_kb + multi_pages_in_use_kb

sys.dm_os_memory_cache_entries

sys.dm_os_memory_cache_entries
SQL Server 2012	SQL Server 2008 R2
pages_kb	pages_allocated_count

sys.dm_os_memory_clerks

sys.dm_os_memory_clerks
SQL Server 2012	SQL Server 2008 R2
pages_kb	single_pages_kb + multi_pages_kb
page_size_in_bytes	page_size_bytes

sys.dm_os_memory_objects

sys.dm_os_memory_objects
SQL Server 2012	SQL Server 2008 R2
pages_in_bytes	pages_allocated_count
max_pages_in_bytes	max_pages_allocated_count

DBCC Memory Status changes

Many of the DBCC memory counters that were previously under Buffer Pool have now been consolidated under Memory Manager to reflect the revised architecture. There is also some additional information to reflect OS errors and NUMA awareness. NUMA growth phase shows memory growth before the target is reached. The “Pages In Use” counter shows the actual number of pages in use, including the stolen pages, database pages, and the reserved memory. The Away Committed counter indicates how much memory from this node is committed from other nodes.

SQL Server 2012	SQL Server 2008 R2
Memory Manager \ Large Pages Allocated	-
Memory Manager \ Emergency Memory	Memory Manager \ Reserved Memory
Memory Manager \ Emergency Memory In Use	Memory Manager \ Reserved Memory In Use
Memory Manager \ Target Committed (KB)	Buffer Pool \ Target (Pages)
Memory Manager \ Current Committed (KB)	Buffer Pool \ Committed (Pages)
Memory Manager \ Pages Allocated (KB)	Buffer Pool \ Stolen (Pages) + Buffer Pool \ Database (Pages)
Memory Manager \ Pages Reserved (KB)	Buffer Pool \ Reserved (Pages)
Memory Manager \ Pages Free (KB)	Buffer Pool \ Free (Pages)
Memory Manager \ Pages In Use	-
Memory Manager \ Page Alloc Potential (KB)	Buffer Pool \ Stolen Potential (Pages)
Memory Manager \ NUMA Growth Phase	-
Memory Manager \ Last OOM Factor	Buffer Pool \ Last OOM Factor
Memory Manager \ Last OS Error	-

SQL Server 2012	SQL Server 2008 R2
Memory Manager \ Large Pages Allocated	-
Memory Manager \ Emergency Memory	Memory Manager \ Reserved Memory
Memory Manager \ Emergency Memory In Use	Memory Manager \ Reserved Memory In Use
Memory Manager \ Target Committed (KB)	Buffer Pool \ Target (Pages)
Memory Manager \ Current Committed (KB)	Buffer Pool \ Committed (Pages)
Memory Manager \ Pages Allocated (KB)	Buffer Pool \ Stolen (Pages) + Buffer Pool \ Database (Pages)
Memory Manager \ Pages Reserved (KB)	Buffer Pool \ Reserved (Pages)
Memory Manager \ Pages Free (KB)	Buffer Pool \ Free (Pages)
Memory Manager \ Pages In Use	-
Memory Manager \ Page Alloc Potential (KB)	Buffer Pool \ Stolen Potential (Pages)
Memory Manager \ NUMA Growth Phase	-
Memory Manager \ Last OOM Factor	Buffer Pool \ Last OOM Factor
Memory Manager \ Last OS Error	-

SQL Server 2012	SQL Server 2008 R2
Memory node Id = n \ Pages Allocated	Memory node Id = n \ MultiPage Allocator + Memory node Id = n \ SinglePage Allocator
Memory node Id = n \ Target Committed	-
Memory node Id = n \ Current Committed	-
Memory node Id = n \ Foreign Committed	-
Memory node Id = n \ Away Committed	-
Memory Clerks \ Pages Allocated	Memory Clerks \ SinglePage Allocator + Memory Clerks \ MultiPage Allocator

Perfmon counters

The Perfmon counter changes follow the same consolidation of counters under the Memory Manager object, and the standardization of counters to KB. Several deprecated AWE counters are now removed.

SQL Server 2012	SQL Server 2008 R2
Object - SQLServer:Memory Manager	Object - SQLServer:Buffer Manager
Database Cache Memory (KB)	Database pages
Free Memory (KB)	Free pages
Reserved Server Memory (KB)	Reserved pages
Stolen Server Memory (KB)	Stolen pages
Target Server Memory (KB)	Target pages
Total Server Memory (KB)	Total pages
-	AWE lookup maps / sec
-	AWE stolen maps / sec
-	AWE write maps / sec
-	AWE unmap calls / sec
-	AWE unmap pages / sec

SQL Server 2012	SQL Server 2008 R2
Object - SQLServer:Memory Node	Object - SQLServer:Buffer Node
Database Node Memory (KB)	Database pages
Free Node Memory (KB)	Free pages
Foreign Node Memory (KB)	Foreign pages
Stolen Node Memory (KB)	Stolen pages
Target Node Memory (KB)	Target pages
Total Node Memory (KB)	Total pages

I'll follow up this article with some additional surface area changes for configuration and new memory manager messages that appear in the errorlog.

The goal of these changes is to make the new Memory Manager activity easier and more intuitive to track, and to clean up some of the values that no longer make sense with the any size page allocator and deprecation of AWE. Anything you used to use but can't find any more? Let me know.

Originally posted at http://blogs.msdn.com/b/sqlosteam/

SQL Server 2012 : The Data Tools installer is now available

AaronBertrand — Mon, 21 Nov 2011 15:18:00 GMT

Last week when RC0 was released, the updated installer for "Juneau" (SQL Server Data Tools) was not available. Depending on how you tried to get it, you either ended up on a blank search page, or a page offering the CTP3 bits. Important note: the CTP3 Juneau bits are not compatible with SQL Server 2012 RC0.

If you already have Visual Studio 2010 installed (meaning Standard/Pro/Premium/Ultimate), you will need to install Service Pack 1 before continuing. You can get to the installer simply by opening Visual Studio, going to File > New > Project, and then choose the option "SQL Server" that appears under the "Other Languages" category (not the "SQL Server" option that appears under the "Database" category):

When you click OK, you will get another dialog that prompts you to install SQL Server Data Tools:

When you click Install, your web browser will be launched on the Data Tools "Get Started" page. There you will see a link to "Download SQL Server Data Tools" which will launch or download SSDTSetup.exe:

At this point I would close Visual Studio 2010, because you'll probably be prompted to close it anyway.

As usual you'll be prompted with a security warning:

Next you'll need to agree to the lengthy license terms and conditions:

Next it will download LoaclDB (the new dev/Express runtime) and install. For me on multiple installs the Installation Progress bar held at 99% for a noticeable amount of time:

With a little bit of luck you should end up at the "Success" screen:

Now you can open Visual Studio directly (or from the "SQL Server Data Tools" menu item under All Programs > Microsoft SQL Server 2012 RC0. Choose File > New > Project and again choose "SQL Server" under "Other Languages." The option depicted above has changed to "SQL Server Database Project":

Click OK. You will find a Toolbox and a familiar Object Explorer on the left, where you can easily create new objects:

And now you can go to town developing your project. I haven't tried upgrading a project from CTP3 or from a previous version of "Data Dude." You're on your own there, sorry.

As an aside the "other" SQL Server option under "Database" contains all the old project types that you may not want to use anymore. I hope they straighten this out and consolidate by RTM:

If you already had Juneau CTP3 installed, you should be able to install SQL Server Data Tools CTP4 as an upgrade by simply downloading the updated SSDTSetup.exe from the "Get Started" page. You may still need to install Visual Studio 2010 Service Pack 1 prior to doing so. For what it's worth, I completely removed SQL Server 2012 CTP3 and Juneau CTP3 before attempting to install the updated bits. If you try an upgrade and come across any issues, please let me know.

You may be wondering why the versioning seems out of cadence - SQL Server is at RC0 while Juneau is at CTP4. This is because SQL Server is feature complete and an upgrade to RTM will now be supported. Juneau is not quite complete (there are still some pending changes before RTM); the naming remains CTP so that folks do not get the impression that an upgrade to RTM will be supported.

A rose by any other name...

AaronBertrand — Mon, 24 Oct 2011 17:55:00 GMT

At the PASS Summit, one of the most anticipated announcements was the release date, and marketing label, for SQL Server code-named "Denali" - the release date is "first half of 2012" (which probably means somewhere around June 25th), and the official name is SQL Server 2012. The logo was revealed on stage, and in a previous blog post, I crafted up my own quick, dirty and horrible rendition with the limited tools available to me on my laptop while sitting at the blogger table. Here is the official version:

Now, you will probably see many people continue to call it Denali in writing and conversation, both formal and casual alike. It will take a while for the new name to take hold, never mind be manually updated in Books Online, other forms of documentation, and even in GUIs and dialogs within Management Studio and other client components.

When the name was revealed, I heard some minor objections to continuing with the "year of release" theme. Some folks thought that it should be SQL 11 or SQL 12, corresponding to the actual version number (depending on whether you consider 2008 R2 a full version). Let me explain why I think the chosen naming scheme makes the most sense, at least to Microsoft.

The year of release is a fantastic model when dealing with customers down the road. In 2016 it makes it so much more obvious that their software is four years old than if it was called SQL Server 11.0 or SQL Server 12.0. What does this mean? A more compelling upgrade story. "Come on, you're on SQL Server 2012!" sounds a lot more antique than "Come on, you're on SQL Server 12.0, which was released in 2012."
SQL Server 2012 will likely be released in conjunction with Windows 8 Server, which will also likely be named with a 2012 suffix. In the 2000 and 2008 timeframes, this story was consistent; with Windows Server 2003 and SQL Server 2005, they got a little out of sync. If I were a betting man I'd say they'll be back in step in 2012.
Be quite thankful that they're not using the previous method of just appending some release flag to an existing version, yet still trying to maintain that it is a full release. We know that in reality they didn't give it a "full release name" because there really wasn't a whole lot to it in the core engine - Unicode compression for Enterprise customers, and backup compression for Standard customers. And we also know that this horrible naming blunder causes unending confusion with customers:
- "Is this service pack for 2008 also applicable to 2008 R2?"
- "Do I need to install 2008 SP3 before or after 2008 R2?
The fun never ends. So while I joke occasionally that they should have named it...

...I'm really not serious. I believe (or at least hope) that they've learned their lesson with that one. Though, I had hoped they'd learned their lesson with bad data type names like TIMESTAMP, yet they came out with DATETIME2 amidst very loud objections from several MVPs. And who could forget entities with multiple personality disorders, like user-defined types and DAC? They also recently announced that Crescent will be officially named "Power View" with a space, unlike PowerPivot, PowerPoint, and PowerShell, which have no spaces. Since consistency is such a priority, early in 2014, I wouldn't be too surprised to see the announcement of:

Will I still be excited about it? Probably at least as excited as I was about SQL Server 2008 R2. And I'll leave it at that.

SSIS: Script component debugging comes to SQL Server Integration Services 2012

jamiet — Fri, 14 Oct 2011 08:48:00 GMT

One of the biggest complaints I see developers raising about SSIS since it was released in 2005 is that the only way to debug Script Components is the distinctly simplistic and old fashioned method of sticking up message boxes - there is no way to step through the code as it executes (something that Microsoft developers have generally been able to do for eons).

Happily that is no longer the case, or won't be soon anyway. In his session yesterday at the PASS Summit SSIS team member Matt Masson revealed that proper Script Component debugging (breakpoints, step into, step over, etc...) will be coming to SSIS 2012. Matt asked me to point out that nothing is certain, things may change before RTM blah blah blah but ostensibly SSIS developers can now sleep easy in the knowledge that message box debugging has gone away forever. Great news!

@jamiet

PASS Summit 2011, Day 1

KKline — Thu, 13 Oct 2011 13:40:00 GMT

I've already had a few good days in Seattle/Redmond this week, meeting with the Microsoft SQL Server program teams and with other Microsoft SQL Server MVPs. I was as excited as a squeeling Justin Beiber fangirl waiting for his new video, wishing I could tell you all of the cool things I learned at Redmond about the future of SQL Server. But as you'd expect, all of that cool stuff is presently NDA. I'm sure there'll be some cool announcements from Microsoft this week. So be on the lookout for the good word from Microsoft.

Keynote

Rushabh Mehta, the PASS president, spent a few moments extolling the value of community and the achievements of the professional association. And he's got a lot to be proud of. PASS has come such a long way. One of the most telling facts about the significance of PASS, to me, is that important SQL Server announcements now happen at the PASS Summit. There was a time, and not very long ago too, in which Microsoft made important SQL Server announcements at other Microsoft events like PDC and TechEd. No longer! PASS is the nexus for Microsoft's data management users. And it shows. Ted Kummert, Microsoft's top data executive, had a lot of exciting talking points about how the community has grown. PASS now has hundreds of chapters worldwide and nearly ninety thousand members. The event has over 4000 paying attendees this year, which means probably around 6000 total attendees including press, exhibitors, speakers, etc. That's big! In fact, that's just about the peak capacity for the Washington State Convention Center here in Seattle. No wonder PASS will be at other locations in the future.

It's Officially called SQL Server 2012

SQL Server "Denali" is officially rolling out as SQL Server 2012. There are a lot of interesting new developments with SQL12 regarding the way the product is splitting into multiple types of appliances designed for specific workloads and customer needs. Need a massive processing appliance, check! That's PDW. Need a hybrid solution for data housed both on premises and in the cloud? Check. Need processing power for BigData? Need processing for non-relational and unstructured data? Check. Microsoft's improving tools will culminate in a new release of development tools called "SQL Server Data Tools", formerly known as Project Juneau, while the business intelligence side of the house will have a new set of tools in "Power View", formerly known as Project Crescent. Hadoop figured large in the keynote, as Microsoft acknowledges that many BigData problems are best served by non-relational data stores. Denny Lee, of SQLCAT, proposed an in-house data marketplace during his demos. My face lit up like a kid at a surprise 10-yr birthday party. Really?!? FOR ME?!!? I laugh because I'd been doing that at jobs throughout my career, offering up what I used to call the "data feedstore" to managers within my team. +! for validation of your ideas.

First Session of the Day

From there I headed out to my first presentation of the conference, which I was delivering with my pal Buck Woody (blog | twitter) of Microsoft. Our session was all about Cloud 101 - when it's appropriate to use the cloud and where you can learn more about the specific technologies like IaaS, PaaS, and SaaS. Many IT pros don't know the difference and are being subjected to the "implement it!" decrees of their bosses who simply read an article on an airplane saying that the cloud is the future. The best quote from the Twittersphere about our session? "Elastic is fantastic" I couldn't have said it better! Speaking of conference sessions, my buddy Brent Ozar (blog | twitter) pointed out this great mobile schedule planning resource:

Go to Guidebook and download the app for your iPhone, Windows Phone 7, Android, or Blackberry. After launching it, you’ll be prompted to download a guide. Type in PASS Summit, and we’re near the bottom of the list.

Voila! Instant mobile schedule guidebook to the PASS Summit.

The Energy is Nuts!

After delivering my session, it was off to the Exhibit Hall, where I played the role of booth jockey for Quest Software for the rest of the proceedings that day. I noticed two things of significance. First, the crowds were thicker and more energetic than I've seen in years. Wow! I knew attendance was our highest ever, but the crowd was near to bursting out at the seems like a 14-year old kid wearing last season's clothes. So either the Washington State Convention Center is no longer big enough or more planning is needed to make this venue work. When I was in leadership for PASS, planning and properly utilizing the venue was always a logistical nightmare. So I don't envy the current leadership in figuring out how to make the PASS Summit scale to an even larger size. The second thing I noticed was how focused the crowd was. Usually, you get a lot of tire-kickers in the booth who, deep down inside, only want your vendor swag. Yes, we had some cute swag this year (a Toad beanie baby and some cool ribbons for your badge). But we also had huge crowds even after we ran out of swag. And, in case you didn't detect the important part of the previous sentence, we ran out of swag! That's right we gave out everything on day 1 of a 3 day event. I nearly freaked the freak out. What is going on here, folks? Haven't you heard that there's a recession going on?

PASS Summit 2011 Resource List : What's New in SQL Server 2012

AaronBertrand — Wed, 12 Oct 2011 15:00:00 GMT

At PASS I am giving a presentation on ~~Denali~~ SQL Server 2012 - What's New in Engine and Tools. In previous blog posts I've compiled a list of resources and, now that CTP3 is out, I thought I'd compile a much larger list that flows somewhat like my slide deck.

Here is the deck and sample files:

http://bit.ly/AB-PASS-2012

The second demo file, 11.SSMS.sql, doesn't really help you at all, since it was meant to be an interactive demo where I would show you visually what I was doing. I do plan to write a more extensive blog post on the snippets feature specifically, but some of the benefits of the new VS shell for Management Studio are ones you just have to touch and feel for yourself.

In any case, since I cleared out a lot of the noise in the deck (mostly links), I promised a resource list, and here it is - largely in the order of the slides. Please note that much of the content being linked to was written months ago, and that some of the information may have changed (and continues to change) - so may not be 100% in line with what is on the slide deck above.

Licensing FAQ

http://bit.ly/AB-2012-Licensing

Setup

http://bit.ly/AB_Denali_Setup

Books Online

http://bit.ly/AB_Denali_BOL

AlwaysOn

AlwaysOn resource site: http://bit.ly/AB_Denali_AlwaysOn
PASS keynote video: http://bit.ly/AB_Denali_Keynote2
Bob Dorr blog post: http://bit.ly/AB_Denali_BobDorr
Brent Ozar blog post: http://bit.ly/AB_Denali_OzarAlwaysOn
Leroy Tuttle AlwaysOn Solutions Guide: http://bit.ly/AB-2012-Tuttle

Contained Databases

http://bit.ly/AB_Denali_CDBtag

Custom server roles

Mike Walsh blog post: http://bit.ly/AB_Denali_MikeWalsh

Juneau

TechEd Europe video: http://bit.ly/AB_Denali_BIDSVid

Intro / download: http://bit.ly/AB-Denali-Juneau

Programmability / SSMS

http://bit.ly/AB_Denali_SSMS

Metadata discovery / EXEC ... WITH RESULT SETS

http://bit.ly/AB_Denali_Metadata

OFFSET

http://bit.ly/AB_Denali_OFFSET

SEQUENCE

http://bit.ly/AB_Denali_SEQUENCEtag

THROW

http://bit.ly/AB_Denali_THROW

Windowing Functions

Itzik Ben-Gan: SQL Mag article / Tech Ed Video

EOMONTH()

http://bit.ly/AB-Denali-EOMONTH

FORMAT()

http://bit.ly/AB-Denali-Format

TRY_CONVERT / PARSE / TRY_PARSE / CHOOSE / CONCAT / IIF

Itzik Ben-Gan's post for SQL Mag

FORCESEEK / FORCESCAN

http://bit.ly/AB_Denali_ForceSeek

Spatial Whitepaper

http://bit.ly/AB_Denali_Spatial_WP

Columnstore Index

ColumnStore Performance Tuning: http://bit.ly/AB-2012-ColumnStoreTuning
ColumnStore Index FAQ: http://bit.ly/AB-2012-ColumnStoreFAQ
My blog post: http://bit.ly/AB_Denali_Apollo

Full-Text Search : Semantic Search

Naveen Grag's blog post

List of changed system objects

http://bit.ly/AB_Denali_SystemObjects

http://bit.ly/AB-Denali-SystemObjectsCTP3

Resource Governor changes

http://bit.ly/AB-Denali-RG

sys.sp_server_diagnostics

Joe Sack's blog - part 1 / part 2

Deprecated / discontinued functionality

http://bit.ly/AB_Denali_Deprecated
http://bit.ly/AB-RC0-Discontinued

Launch point for Denali BI resources

http://bit.ly/AB_Denali_BI

----------------------

Download SQL Server 2012 RC0 (11.0.1750)

http://bit.ly/AB-RC0-Download

SQL Server 2012 RC0 Developer Training Kit

http://bit.ly/AB-RC0-TrainingKit

Forums

http://bit.ly/AB_Denali_Forums

Feedback / suggestions / bug reports

http://connect.microsoft.com/sql/

Getting Juneau CTP3 to work - SqlCeManager issues

AaronBertrand — Tue, 11 Oct 2011 15:48:00 GMT

Since installing Juneau, I've had various issues accomplishing even the simplest of tasks. I would get errors saying that the SqlCeManager could not be initialized or "object reference not set to an instance of an object." Depending on what I tried to do I might also see a big error dialog that suggested installing the fix from KB #974247, however this fix did not work. What ended up solving my issues, and allowing me to actually demo the new code-aware table designer in my Denali session tomorrow, was KB #2553608. You'll need to go to this link:

http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=2553608&kbln=en-us

Check the box, fill in your e-mail address (twice), decipher the captcha, and request the download. Once it comes in, extract the archive, and run SSCERuntime-ENU.exe. This will extract SSCE_Runtime-x86.msi and SSCE_Runtime-x64.msi. If you are on a 64-bit system, you'll want to install *both* MSIs. I installed the x86 version first, then the x64. Once I did that, I was able to successfully connect to databases, create projects, etc.

Hope this helps.

SQL Server v.Next (Denali) : Changes to Resource Governor

AaronBertrand — Mon, 10 Oct 2011 19:49:00 GMT

After reading the headline, you're probably quite excited - but sadly, no, Denali does not introduce the ability to govern I/O. But there are some cool additions here that you will be able to take advantage of. For now I'll just talk about these enhancements in short form but will hopefully elaborate on them later. Note that these changes are not present in CTP3 but should be available in the next publicly available build.

More Resource Pools

Under 64-bit only, you will now be able to have 64 resource pools (the current limit is 20). This number includes the default and internal pools, so really you can only create 18 resource pools today. The increased limit can help folks on larger hardware consolidate more instances into a single instance, and can help service providers offer more customers a "slice" of an instance's resources.

Scheduler Affinity

You will now have the ability to set affinity to a scheduler, group of schedulers, or a NUMA node. So for example, if you wanted to set the affinity of a certain resource pool to schedulers 9 through 12 only, you could say:

ALTER RESOURCE POOL pool_name WITH (AFFINITY SCHEDULER = (9 TO 12));

As a warning of sorts, this can make your calculations for, as an example, MIN_CPU_PERCENT (which can't add to more than 100%) a lot more complex. There is also a new DMV so that you can inspect the affinity settings (only resource pools with explicit affinity settings will show in the result):

sys.dm_resource_governor_resource_pool_affinity

Columns: 
    pool_id, processor_group, scheduler_mask

Real CPU capping

You will now be able to cap CPU usage by a pool, even when there is no contention on the box. Currently MAX_CPU_PERCENT is only enforced if there are other workloads competing for CPU resources. This will allow providers to offer more predictable chargeback if their customer is often the only one on the box, in which case the MAX_CPU_PERCENT limit is ignored. You will be able to set the cap this way:

ALTER RESOURCE POOL pool_name WITH (CAP_CPU_PERCENT = 30);

This would prevent this pool from exceeding 30% CPU regardless of what other workloads are (or are not) running on the box.

Memory Control

Resource Governor will be able to control all memory allocations *except* buffer pool and column store cache. Today it only handles query grant memory, but now this coverage will be extended (mostly due to changes in the memory manager).

A quick look at SQL Server Audit in Denali CTP3

Linchi Shea — Fri, 07 Oct 2011 18:27:00 GMT

I have been using the SQL Server 2008 audit feature on a regular basis and generally like it a lot. No doubt there is still room for improvement to make it practically more useful. Here are three changes I would love to see.

· You can’t really just audit the SELECT statements. Yes, you can pick SELECT as an audit action type. But because of the way the audit feature is implemented, you most likely would also end up with a lot of UPDATE or DELETE in the audit log. Note that SQL Server audit logs its data at the points when permissions are checked and a SELECT permission check is almost always performed when UPDATE/DELETE is executed. In other words, practically you can’t specify that you only want to audit SELECT queries on a table but don’t care about any DML on the same table.

Why is this even an issue? One of primary reasons I use the audit feature is to track usage for deprecation purposes. On one reporting server, we suspect that a table is not really being used and would like to confirm that’s the case and drop the table. So I set up the table for audit on all SELECT queries by specifying SELECT as the audit action type on the table, expecting to find none or very few audit records. But I end up with millions of records in the audit log, and virtually all of them are updates. Note that this is a reporting server, and the table is being replicated from an OLTP server, on which the table is heavily updated. These updates are faithfully replicated by a transactional replication distribution agent. For our purpose, we know the table is being updated by the distribution agent and we don’t care about. We just want to know if anyone is querying the table for reporting. But we don’t seem to be able to do that efficiently with the SQL Server 2008 audit feature.

· When you have a server with a lot of stored procedures calling each other, it can be difficult to find out where a statement in an audit record comes from. To me, the top most stored procedure is a key piece of audit information in addition to the exact statement being applied to the table directly. It should be captured, but it is not.

· Much of the useful session level information is not captured in the audit record, making it difficult to correlate what (e.g. program name) did what (i.e. the info in the audit record) from where (e.g. hostname). There may be a perfect reason for not providing more/tighter correlation with the session level information. But this makes the audit feature less useful.

So the question is: do we have any good news from Denali CTP3? The answer is yes and no. Denali CTP3 has some nice improvement, but in my opinion it isn’t going far enough.

Good news is that in Denali CTP3, (1) we now can selectively retain audit records by using any or a combination of the columns in the audit records to setup a filtering predicate, and (2) the Additional Information column of the audit log now may include the T-SQL stack that has information on upper level calling stored procedures.

Not so good news is that these changes are not as effective as they could be or I’d love them to be.

I see two deficiencies in the audit filter capability. First, it is an attribute of the server audit object. It would give us more flexibility to add it as part of a database audit specification so that, for instance, we could create filters on what action type to include and what action type to filter out. The second issue with the current filter design is that it’s difficult to create an effective filter to retain only SELECT queries that are actually submitted instead ‘bogus’ SELECT as a result of permission checks on UPDATE or DELETE.

The inclusion of the T-SQL stack information is excellent. But my testing with CTP3 shows that it only provides information going up one level (I don’t know if this will change in the final release). For instance, if you audit SELECT on table foo, and you have the following two stored procedures:

CREATE PROC p_foo_1

SELECT * FROM foo

CREATE PROC p_foo_2

EXECUTE p_foo_1

When you execute p_foo1 and p_foo2, the statement captured in the audit records will be the same:

SELECT * FROM foo

The T-SQL stack for p_foo_1 is as follows, clearly identifying the calling proc:

<tsql_stack>

<frame nest_level = '1' database_name = 'test' schema_name = 'dbo'

object_name = 'p_foo_1'/>

</tsql_stack>

And the T-SQL stack captured for p_foo_2 is:

<tsql_stack>

<frame nest_level = '2' database_name = 'test' schema_name = 'dbo'

object_name = 'p_foo_1'/>

</tsql_stack>

In both cases, the stored procedure is identified as p_foo_1, the one doing SELECT on the table directly. In the case of p_foo_2, you’ll need to pay attention to the value in the nest_level attribute. The nest_level value is 2, and you know that you must search one level up to find the calling stored procedure. This definitely helps. But if the nest_level value is 5, you end up having to do a lot of code search. So why not just identify the top calling stored procedure and save the user from having to search through the code?

With respect to the third issue I mentioned earlier, I have not found any change in CTP3 to better integrate/correlate the audit records with the SQL Server session level attributes.