Search results matching tag 'SQL 11'

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

More changes you might not have noticed in SQL Server 2008 R2 SP1 CTP

AaronBertrand — Mon, 25 Apr 2011 14:28:00 GMT

On Friday, after Microsoft released the CTP, I talked a little bit about the changes you will see in SQL Server 2008 R2 SP1. Namely:

new rowcount-related columns in sys.dm_exec_query_stats;
extended functionality of the FORCESEEK query hint;
a new FORCESCAN query hint;
support for 15,000 partitions (to match new functionality added in SQL Server 2008 SP2);
a new DAC framework for in-place upgrades; and,
a new disk space control in PowerPivot for SharePoint.

Since then, I have been digging a little deeper, as the release notes are pretty vague about the changes, and the Books Online updates have not been published yet - at least not that I have seen (and I don't expect them to be published until after the service pack is RTM).

In addition to the new columns in sys.dm_exec_query_stats, there are some other DMVs that have been added or changed in comparison to the RTM release of SQL Server 2008 R2, and some new Extended Events objects as well. I'll also note below each item description whether the change is present in Denali (initially I thought there would be more, but there is only one case where this has happened for DMVs in CTP1).

New system objects:

All columns for new DMVs, and new columns on existing DMVs:

sys.dm_os_volume_stats

This new Dynamic Management Function provides you with information about the disk subsystem where your database files live. It takes the parameters @DatabaseID and @FileID, making it easy to CROSS APPLY against sys.sysalfiles to get all kinds of information about all of the databases across your instance. You can see free vs. available space, whether the drive supports compression / alternate streams / sparse files, and whether the drive is compressed or read only:
SELECT 
   vs.volume_mount_point, -- e.g. C:\
   vs.volume_id,
   vs.logical_volume_name,
   vs.file_system_type,  -- e.g. NTFS
   db = DB_NAME(f.dbid), 
   [file] = f.[name],
   f.[filename],
   file_size_MB = f.[size] / 128,
   drive_size_MB = vs.total_bytes/1024/1024, 
   drive_free_space_MB = vs.available_bytes/1024/1024, 
   drive_percent_free = CONVERT(DECIMAL(5,2), vs.available_bytes * 100.0 / vs.total_bytes),
   vs.supports_compression, 
   vs.supports_alternate_streams, 
   vs.supports_sparse_files, 
   vs.is_read_only, 
   vs.is_compressed
FROM
   sys.sysaltfiles AS f
CROSS APPLY
   sys.dm_os_volume_stats(f.dbid, f.fileid) AS vs
WHERE
   f.dbid < 32767
ORDER BY
   drive_percent_free DESC,
   db,
   [file];
This is much more powerful than the undocumented and unsupported extended procedure xp_fixeddrives, and I assume - though I haven't tested yet - that it will see things like external hard drives, mount points and other specialty devices. Granted, with or without the usage of sys.sysaltfiles, it is only going to tell you about volumes where you actually have at least one database file.

* Not yet in Denali.

sys.sp_db_increased_partitions

This stored procedure was first introduced in SQL Server 2008 SP2, and enables you to create up to 15,000 partitions on a single table (before this change, the maximum was 1,000 partitions). The reason for the change is that some big customers wanted a partition per day, and the built-in limit of 1,000 meant they couldn't even reach three years of data using that structure. The stored procedure takes two parameters: the database name (@dbname sysname) and whether to turn the setting on or off (@increased_partitions varchar(6)). The latter parameter can take any of the following values: 'on', 'off', 'true' or 'false' - I recommend using lower case to be sure the parameters are not misinterpreted on a binary or case-sensitive collation. As an example, to turn on and then off increased partition support for the AdventureWorks database, you can run the following code:
EXEC sys.sp_db_increased_partitions 
    @dbname = N'AdventureWorks', 
    @increased_partitions = 'on';

EXEC sys.sp_db_increased_partitions 
    @dbname = N'AdventureWorks', 
    @increased_partitions = 'off';

-- or

EXEC sys.sp_db_increased_partitions 
    @dbname = N'AdventureWorks', 
    @increased_partitions = 'true';

EXEC sys.sp_db_increased_partitions 
    @dbname = N'AdventureWorks', 
    @increased_partitions = 'false';
* Not yet in Denali.

sys.sp_MScheckIsPubOfSub

I assume this stored procedure is not meant for public consumption, but is probably used by other internal stored procedures involving replication. From the comments in the stored procedure:
/*
  *  This proc will discover if the specified subid is a subscriber to the specified pubid.
  *  Currently, this is a one level check, it does not support n-level re-publishing.
*/
The procedure takes two uniqueidentifer input parameters (@pubid and @subid), and returns a single output parameter (@pubOfSub bit).

* Not yet in Denali.

sys.dm_os_windows_info

This new DMV complements sys.dm_os_sys_info with a bit of information about the underlying Windows operating system - version and service pack information, sku and language.
SELECT
   windows_release,
   windows_service_pack_level,
   windows_sku,
   os_language_version
FROM
   sys.dm_os_windows_info;
On my system (US English Windows 7 Ultimate SP1), the following results were returned (the only other windows_sku value I know of for this DMV so far is 28, for Windows 7 Ultimate N):

* Not yet in Denali.

sys.dm_server_memory_dumps

Thankfully I have only had to look into memory dumps a couple of times, but this new DMV will give you a row for each dump that has been generated, including the name of the file, when it was created, and how big it is:

SELECT
   [filename],
   creation_time,
   size_in_bytes
FROM
   sys.dm_server_memory_dumps;
And also thankfully, this query returns 0 rows on all of the systems I have updated to the CTP of Service Pack 1. Note that, unlike almost every other date/time-related column in every DMV in the system, creation_time is returned as datetimeoffset, not datetime. Just something to keep in mind if you are accessing this data programmatically.

* Not yet in Denali.

sys.dm_server_registry

One of the things I've always hated is having to resort to the undocumented and unsupported extended procedure xp_regread. This DMV eliminates the need to use that xp, provided you are after information that is explicitly relevant to the current instance of SQL Server. On this particular instance, I get information about the engine and SQL Server Agent services (including service account names, startup parameters and agent history properties), as well as plenty of network-related information that is very tedious to obtain in current versions (for example, the port(s) in use and whether they are dynamic):
SELECT 
   registry_key,
   value_name,
   value_data
FROM
   sys.dm_server_registry;
I'm not going to show all of the data for this specific instance, but here are the distinct registry keys that are returned for a named instance "SQL2008R2" - this should give you a good idea of the information you can get to without having to trawl through the SQL Server logs, Agent property dialogs, or dig into the registry manually:

* Not yet in Denali.

sys.dm_server_services

In SQL Server 2008, sys.dm_os_sys_info was enhanced with the column sqlserver_start_time which shows when the SQL Server service was started. This new DMV has a row for each server service, and includes the startup time as well the current status, the startup type, the current process_id (making it easy to determine the instance you see in perfmon or task manager on a machine with multiple instances), the account the service is running under, and whether the service is clustered (which is returned as an nvarchar(1), for some strange reason). Like the new registry DMV, you can now get to this information programmatically and a whole lot easier than you could do in previous versions. Here is an example from my system, which is only currently running SQL Server and SQL Server Agent (I truncated some column names to get the image to fit into this post):
SELECT
   servicename,
   [startup] = startup_type_desc,
   [status] = status_desc,
   pid = process_id,
   last_startup_time,
   service_account,
   [clus] = is_clustered
FROM
   sys.dm_server_services;
Results:

There are two things to note about last_startup_time for the core SQL Server service: (1) it is exposed as a datetimeoffset data type, so it includes offset information from UTC, and (2) it may be a second or two off from the sqlserver_start_time value in sys.dm_os_sys_info. I don't have an explanation for the latter, but I did complain about both the missing Agent startup time and the odd data type choices in this DMV (this is not the only questionable data type choice in this DMV's output):

#664557 : sys.dm_server_services.last_startup_time is always NULL for SQL Agent

#664559 : Odd data type choices in sys.dm_server_services

* Not yet in Denali.

sys.dm_exec_query_stats

I talked about this DMV in my previous post; it is not a new DMV, but it gets some shiny new columns. Essentially, there are four new columns showing row count statistics for each query exposed by the view: total_rows, last_rows, min_rows and max_rows. This enhances the information you have about each query if you are want to know if it has returned a vastly different number of rows over time.

* Not yet in Denali.

sys.dm_os_sys_info

This DMV is also not new, but it has a couple of new columns dedicated to virtualization, so you can tell programmatically, for example, if the SQL Server instance is on a virtualized or physical host. The new columns are virtual_machine_type and virtual_machine_type_desc. I don't have a full mapping of the possible values yet; since I only have 2008 R2 running on virtual machines, I can only show you the values in that situation:
SELECT 
   virtual_machine_type,
   virtual_machine_type_desc
FROM
   sys.dm_os_sys_info;
Results:

Note that the machines where I ran these queries are running on Windows 7 virtual machines under VMWare Fusion, so don't always believe that "HYPERVISOR" means Hyper-V explicitly.

Also note: the current Denali BOL documentation for "Breaking Changes to Database Engine Features" incorrectly states that these columns were removed.

* This DMV is the only one on this page that has already been added/changed in current builds of Denali (CTP1).

Extended Events

Some new xevents were added in SP1 as well. I'm sure Jonathan Kehayias (blog | twitter) will talk more about them soon enough, but I can at least identify them - by joining a couple of XE views on the local, SP1 server to a linked server running 2008 R2 RTM:
SELECT
   l.name,
   l.map_value
FROM
   sys.dm_xe_map_values AS l
LEFT OUTER JOIN
   [GREENLANTERN\SQL2008R2RTM].[master].sys.dm_xe_map_values AS r
   ON l.name = r.name
   AND l.map_key = r.map_key
WHERE
   r.name IS NULL;

SELECT
   l.name,
   l.description
FROM
   sys.dm_xe_objects AS l
LEFT OUTER JOIN
   [GREENLANTERN\SQL2008R2RTM].[master].sys.dm_xe_objects AS r
   ON l.name = r.name
WHERE
   r.name IS NULL;
Results:

* These are partially in Denali CTP1. Well, the first two rows in the first resultset, at least.

Reference queries

For reference, you can see the queries I used here to perform this Sherlock Holmes-esque work against the DMVs:

I talked about how to find new DMVs and other system objects in November 2010.
I talked about how to find new and changed DMV columns in August 2009.

Download

SP1 CTP for "normal" SKUs (and also a stand-alone download for upgrading client tools only):

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=bd200f8e-ba8a-45e3-af59-e28a9e2d17df

SP1 CTP for the various Express Editions (Express, Express with Tools, Express with Advanced Services):

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=e70a4b51-53be-48d3-8030-80dc9e755be3

SP1 CTP Feature Packs (please don't ask me what all of these files are, but someone else did a pretty good job):

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=15dcfec2-abb8-409d-91ff-c7c8e18c8409

Summary

I'm still torn about features being added in service packs, but if you're on this platform, clearly there are some changes that you might be able to take advantage of once the Service Pack 1 is officially RTM. And I have every reason to believe that these changes will be ported to Denali, probably by the time you see the next CTP.

Please remember, though, that this service pack is not going to include fixes from cumulative update #7 for SQL Server 2008 R2, so if you're relying on those fixes, don't install this service pack - it will undo those changes. Also don't install the service pack CTP if you think you might be interested in cumulative update #8, since it will be problematic to downgrade to that CU once the service pack is installed (even the CTP). And who knows when the service pack will be officially released (never mind the subsequent CU that will get it "caught up" with the current branch of RTM fixes).

Also, please remember that DMV data does not persist through a service restart. So, while some of these queries will yield relatively static results (such as those that talk about the operating system or that dig information out of the registry), things like the rowcount values added to sys.dm_exec_query_stats are only valid since the time the service was started.

And finally, please don't try, and please don't ask; you cannot install this service pack on SQL Server 2008. This is ONLY for the SQL Server 2008 R2 release.

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

Slides and samples from SQL Saturday #65 - Vancouver

AaronBertrand — Sun, 27 Feb 2011 00:53:00 GMT

Today I presented my "What's New in SQL Server 'Denali'" deck to about 40 attendees at SQL Saturday #65 in Vancouver, British Columbia. Attached below (and here) are the slightly updated slides and samples.

Slide decks and samples from SQL Saturday #60 - Cleveland

AaronBertrand — Sat, 05 Feb 2011 22:34:00 GMT

Attached are the slide decks and demos from my two presentations at SQL Saturday #60 in Cleveland: