Search results matching tags 'SQL Server 2008' and 'SQL Server 2012'

PowerPivot Compatibility across versions

sqlbi — Mon, 14 Jan 2013 12:44:00 GMT

There are several versions of PowerPivot available and starting with Excel 2013 there are also several versions of Excel. It is useful to look at the compatibility between the different versions of Excel and PowerPivot available now.

As a general rule when you have a PowerPivot workbook saved with a specific version of PowerPivot:

You can upgrade a workbook to a newer version of PowerPivot
You can upgrade a workbook to a newer version of Excel
You cannot open a workbook using a previous version of PowerPivot
You cannot open a workbook using a previous version of Excel, if it contains PowerPivot data

First caveat: you can open an Excel 2010 workbook containing a PowerPivot data model in Excel 2013, but once you save it in Excel 2013, you can no longer open it in Excel 2010. This is because a different file format for PowerPivot data used by Excel 2013 that cannot be understood by Excel 2010.

Second caveat: A file saved in Excel 2010 with PowerPivot 2012 (RTM or SP1) cannot be opened by an Excel 2010 running PowerPivot 2008 R2.

Each workbook has a compatibility level for PowerPivot data that corresponds to the PowerPivot version used to save the data. Here is the list of currently available compatibility levels:

1050 (2008 R2)
1100 (2012 RTM/SP1)
1103 (Excel 2013)

The following table tells you what happens when you try to open an Excel 2010 file in a certain compatibility level (columns) with a certain version of PowerPivot (rows):

	1050	1100
2008 R2	Read/Modify	Not Supported
2012 RTM	Read/Upgrade++	Read/Modify
2012 SP1	Read/Upgrade++	Read/Modify/Upgrade

++ It is important to call out that it is *not* supported editing 1050 PowerPivot models in the 2012 release – you *have to* upgrade the model to 110x before you can edit/refresh the model.

A detailed list of errors you can have opening different a workbook with a different compatibility level than the current version of PowerPivot you have is available in the article Version compatibility between PowerPivot Data Models in Excel 2010 and Excel 2013. As the article mention, PowerPivot for SharePoint can open PowerPivot workbooks of previous compatibility levels, but in order to upgrade the data on the server (scheduling a data refresh) you need to upgrade the workbook to the newer format.

You have to be careful using PowerPivot in mixed environment, especially when you want to share an Excel file containing PowerPivot data. Once you refresh PowerPivot data, you need to upgrade the compatibility level if you opened the workbook with a newer version of PowerPivot, and once you do that, your colleague with a previous version of PowerPivot can no longer open the same workbook. Saving the file on SharePoint makes it possible to navigate into data, but you need a correspondent or newer version of PowerPivot on SharePoint than that used to save the file.

For these reasons, when we provide the examples for our PowerPivot Workshop and our book for Excel 2010, the sample files are saved with the older version of PowerPivot (2008 R2). Now that we are working on the Excel 2013 version of the book and of the workshop (soon to be available!), we are finally going to release samples using a newer version of Excel and PowerPivot.

MS12-070 : Security Updates for all supported versions of SQL Server

AaronBertrand — Wed, 10 Oct 2012 16:57:00 GMT

This week there was a security release for all supported versions of SQL Server. Each version has 32-bit and 64-bit patches, and each version has GDR (General Distribution Release) and QFE (Quick-Fix Engineering) patches. GDR should be applied if you are at the base (RTM or SP) build for your version, while QFE should be applied if you have installed any cumulative updates after the RTM or SP build. (More details here.)

SQL Server 2005

RTM, SP1, SP2, SP3 - not supported
SP4 - GDR = 9.00.5069, QFE = 9.00.5324

SQL Server 2008

RTM, SP1 - not supported
SP2 - GDR = 10.00.4067, QFE = 10.00.4371
SP3 - GDR = 10.00.5512, QFE = 10.00.5826

SQL Server 2008 R2

RTM - not supported
SP1 - GDR = 10.50.2550, QFE = 10.50.2861
SP2 - not affected

SQL Server 2012

RTM: GDR = 11.00.2218, QFE = 11.00.2376
SP1 - not yet supported; should not be affected once SP1 is released.

Now, a couple of oddities you might have noticed:

The security bulletin mentions something about SQL Server instances with Reporting Services installed. Yet the KB articles for individual updates state that all instances of SQL Server are eligible for the update. And the update does, in fact, update sqlservr.exe and @@VERSION, even for systems where SSRS is not installed. So until there is some clarification on this point, I'm going to treat this as a patch for all instances.
Both the GDR and QFE KBs for multiple patches state that the preceding cumulative updates are included. I believe this is a copy & paste error and that the cumulative updates for a specific branch are only included with the QFE patch. I will update here if I get any confirmation on this.

Even if they come back and say, whoops, our bad, the KBs should mention it is SSRS only, and the GDRs do not affect sqlservr.exe and do not include the CU updates, I'm still going to apply the patch everywhere. Why? Well, for consistency, I'd rather have all of my instances at @@VERSION = x, than have the SSRS instances at x and the non-SSRS instances at < x.

MDX Studio download #mdx #ssas

sqlbi — Mon, 24 Sep 2012 11:52:00 GMT

Short version: the latest available version of MDX Studio can be downloaded from http://www.sqlbi.com/tools/mdx-studio/

Long version: Last week Stacia Misner twitted that the online version of MDX Studio was no longer available. It was hosted on http://mdx.mosha.com. It was a sad news, and it is also not good that nobody is maintaining the desktop version of MDX Studio. The latest release is the 0.4.14 and as I am writing it is still available on a SkyDrive link provided by Mosha Pasumansky, who wrote MDX Studio. Mosha does not work in Microsoft now and the entire BI community hopes that somebody will continue its work on this product. Unfortunately, it cannot be published on CodePlex because of some IP restrictions.

Only bad news? Well, I hope no. The first good news is that MDX Studio also works with Analysis Services 2012 in Multidimensional mode. The second news is that, after having checked that we can do that, we created a web page on SQLBI web site to download the latest available release of MDX Studio. I hope it will be necessary to update it in the future, by now it is just a way to simplify the finding and download of this precious tool, and to grant that it will not disappear in case the current SkyDrive using to host the download would be discontinued, like it happened to the MDX Studio online version.

Now a question to the BI Community: I know that there was some content available regarding tutorial on MDX Studio. I’d like to gather it and to put all in a single place. If you have such content, please contact me directly writing to marco (dot) russo (at) sqlbi [dot] com. Thanks!

SSIS Design Patterns, the Book

andyleonard — Mon, 06 Aug 2012 16:37:43 GMT

For the past two years, I have had the honor and privilege or authoring SSIS Design Patterns alongside Jessica Moss, Michelle Ufford, Tim Mitchell, and Matt Masson. Publication of the book – like many projects of this scope – has been delayed. The current publication date is 27 Aug 2012 and I have high confidence in this date.

I take responsibility for publication delays and apologize to those who pre-ordered the book. The reasons for the delays are not important. I have built a career as a software developer and architect based on the following maxim:

Deliver quality late, no one remembers.
Deliver junk on time, no one forgets.

The shared goal of everyone working on this project has been to deliver quality. Proofing the manuscripts, I believe we have achieved that goal.

:{>

The Curious Case of the Optimizer that doesn’t

Hugo Kornelis — Thu, 03 May 2012 22:13:00 GMT

The optimizer is the part of SQL Server that takes your query and reorders and rearranges your query to find the optimal execution plan. In theory.

In practice, that doesn’t always work out well. Often, the optimizer manages to come up with brilliant ways to execute a complex query very efficiently – but sometimes, it misses an option that appears to be so simple that you can only stare in utter amazement at the execution plan before going to the Connect site.

Here is an example I recently ran into. I tested it on SQL Server 2012 and on SQL Server 2008 R2, and it reproduces on both. Execute the query below in the AdventureWorks sample database, with the option to Include Actual Execution Plan enabled (Ctrl+M), or request an Estimated Execution Plan (Ctrl-L).

SELECT   TerritoryID,
         Name,
         SalesLastYear,
         SalesYTD,
         RANK() OVER (ORDER BY SalesLastYear) AS Rank1,
         RANK() OVER (ORDER BY SalesYTD) AS Rank2
FROM     Sales.SalesTerritory
ORDER BY SalesLastYear;

When following the flow of data (by reading the execution plan from right to left), we see that the data is first read (with an unordered clustered index scan, since there is no better index available), then sorted by SalesLastYear, so that Rank1 can be computed (using two Segment operators and a Sequence Project operator – don’t ask). After that, the rows are sorted again, now by SalesYTD, and we see another combination of two Segment and one Sequence Project, for the calculation of Rank2. And then, finally, the rows are re-sorted by SalesLastYear so that they can be returned in the requested order.

Now the big question is: why does the plan include two sort operators that both sort the data in the same (SalesLastYear) order? If the task of the optimizer is to find smart ways to rearrange computation order for better performance, why doesn’t it simply compute Rank2 first and Rank1 after that? In that case, the rows are already in the SalesLastYear order after the last Sequence Project, so no extra sort is needed. The execution plan of the query below confirms this suspicion:

SELECT   TerritoryID,
         Name,
         SalesLastYear,
         SalesYTD,
         RANK() OVER (ORDER BY SalesYTD) AS Rank2,
         RANK() OVER (ORDER BY SalesLastYear) AS Rank1
FROM     Sales.SalesTerritory
ORDER BY SalesLastYear;

Indeed, the execution plan of this query includes only two Sort operators instead of the three we had in the first execution plan. If you include both queries in a single batch, you’ll see an estimated cost of 59% for the first query, and 41% for the second. (Some people think that the percentages shown in an Actual Execution Plan are an indication of the actual cost; that is not the case – the percentages are computed from the Estimated Subtree Cost property of the left-most SELECT operator). The SalesTerritory table is too small to measure any actual performance differences, but I tried queries with a similar pattern on the SalesOrderDetail table (121,317 rows), and on FactProductInventory in AdventureWorksDW (776,286 rows) and I did see an actual difference in execution time. No surprise, but now I know for sure!

So, we have seen that simply reordering the two columns that use an OVER clause reduces the query cost by about 30%. How is it possible that such a simple, basic reordering strategy is not considered by the optimizer? Surely, this can only be a bug?

That’s what Fabiano Neves Amorim thought when he filed this bug on Connect. But, as you can see, the bug has been closed as “By design”. That probably doesn’t mean that someone wrote a design document telling the optimizer team to make sure that OVER clauses must always be evaluated in the order in which they appear in the query, even if a different order would be cheaper. I rather think of it as “missing an optimization opportunity is not a bug; the results are still correct, just a bit slower – so we’re going to close this “bug” report”. In this case, maybe the optimization opportunity was not identified during the design phase, or it was just too hard to implement. The latter statement may sound ridiculous at first (how can such a basic rewrite be too hard?), but you have to be aware that the optimizer does not operate on the original query text, but on an internal representation that is based on mathematics and set theory. Rewrites that may be complex in the query text may be obvious in this representation, but the reverse can also be true – so I’m prepared to accept the comment that Andrew Richardson made on behalf of Microsoft to the above Connect item: that it would be fairly complicated for the Query Optimizer.

That does not mean I agree with the rest of Andrew’s comment. He suggests that this is a case where we should not rely on the optimizer, but rewrite our queries ourselves, especially since it’s such an easy rewrite in this case. Well, I would agree with that, except that: (a) this missed optimization opportunity is not documented, so how are developers supposed to know that they may need to reorder columns in a SELECT clause for optimal performance? (that is one of the reasons for this blog post); and (b) the behavior of the optimizer in this situation is not documented, so it can change at any time; I’d hate to rewrite all my queries and then find that the sysadmin just installed a patch and now the optimizer always starts with the last instead of the first OVER clause (or, worse, I don’t find it and just get all the bad performance right back).

However, Andrew is right in so far that, at this time, rewriting queries does consistently improve performance in all my tests. So at this time, rewriting does seem to be the right thing to do. Just keep in mind that you have to test all your queries, not only on your test server but also on your production hardware, and that you’ll have to repeat these tests on a regular basis (at least after each patch, CU, service pack, or other change).

The basic rewrite pattern is simple – for each query that uses OVER clauses with different ORDER BY subclauses as well as an ORDER BY clause on the query that matches one of the ORDER BY subclauses, make sure that the OVER clause that uses the matching ORDER BY comes last in the SELECT list. If you have a client that expects the columns in a particular order, the rewrite becomes a bit more complex – in that case, you have to use a CTE that includes the OVER clauses in the optimized order, and then you can reorder the columns in the query that references the CTE – as shown in this example:

WITH MyCTE
AS (SELECT   TerritoryID,
             Name,
             SalesLastYear,
             SalesYTD,
             RANK() OVER (ORDER BY SalesYTD) AS Rank2,
             RANK() OVER (ORDER BY SalesLastYear) AS Rank1
    FROM     Sales.SalesTerritory)
SELECT   TerritoryID,
         Name,
         SalesLastYear,
         SalesYTD,
         Rank1,
         Rank2
FROM     MyCTE
ORDER BY SalesLastYear;

These rewrites are indeed the best option for the time being – but I still think that the optimizer should be improved to do these rewrites internally. So I decided to file a new item on Connect, this time labeling it as a suggestion instead of a bug. If you agree with me that this would be a great investment of Microsoft’s engineering time, then please add your vote to this item. (Or vote it down if you think I’m just being ridiculous). But don’t stop there! Microsoft knows me; they know I’m a geek who plays around with options like this and then runs into this issue. No real production databases were hurt during the production of this blog. And if I am the only one, then, frankly, I myself will say that they have better things to do with their engineering time. However, if I know that this affects real people, I can make a much stronger case to Microsoft for getting this fixed.

So – go out to your production servers and find if you use queries with this pattern (two OVER clauses with different ORDER BY and an ORDER BY on the final query), then check to see if you should rewrite them. And then report back – add a comment here or on the Connect item; share if this affected you, and how much performance you were able to gain as a result of the rewrite.

If Microsoft knows that their customers would actually benefit, they’ll be much more inclined to add this improvement to the optimizer then if it’s just about me, a geek moaning about an edge case that no one in the real world would ever run into.

Busy months ahead

Hugo Kornelis — Thu, 16 Feb 2012 19:48:01 GMT

Almost two months have passed since my last blog post. And while it’s true that I’ve had (much) longer breaks, I do have a good reason now. All the time that I would normally at least in part spend on preparing new blog posts is now reserved for preparing presentations for a few upcoming events. I’ll give you an overview – who knows, maybe you’ll have a chance to attend one of them and meet me there? I’m looking forward to it!

On Saturday, February 25, I’ll present my session on “Advanced Indexing” at SQL Saturday 108, in Redmond, WA. This session covers included columns, filtered indexes, indexed views, tools for finding the right indexes, and the dangers of over-indexing. I see several top speakers on the agenda, so this is definitely an event you don’t want to miss out – especially considering the price! Unfortunately, I hear that the event is fully booked, but you can still get yourself added to the waiting list. If other people have to cancel, you may get a chance to get admitted.

I’ll stay in the Redmond area the whole next week, attending the MVP Summit. Not as a presenter, but to soak up all the information the product team wishes to share with us, and to give feedback on current and planned functionality. And to have lots of funs with my fellow MVPs, of course!

Shortly after that, I’ll be heading to SQL Saturday 115, in Lisbon, Portugal. I will first present a full-day training session on database design on Friday, March 16. The design method I’ll teach is one that observes all the principles of modelling I presented in some previous blog posts. The next day, I’ll present two sessions: the one on advanced indexing I already mention above; and one about the MERGE statement, that covers not only the intended use but also several other interesting possibilities of this statement – and warns about some caveats that you need to know if you want to use MERGE. This event has an awesome line-up of speakers, so if you see a chance to go to Lisbon, do so!

And finally, I’ll head to London for SQLBits X, which lasts from Thursday, March 29 until Saturday, March 31. On Thursday, I’ll present a training day on database design, so if you can’t attend in Lisbon, you get a second chance in London. I then have a day off on Friday, which gives me a chance to attend some of the superb sessions that are scheduled for this day; on Saturday, I will present a session on columnar indexes, a new feature introduced in SQL Server 2012 that has the potential to yield enormous performance benefits for queries that have to process large tables of read-only (or mainly read-only) data. In this session, I will peek below the hood and disclose a bit about how this new kind of indexes works, and why that results in such enormous performance benefits.

So, if you were waiting for my next blog post with actual technical content, I have to disappoint you. That will probably have to wait until after these busy conference weeks are over.

Session memory – who’s this guy named Max and what’s he doing with my memory?

extended_events — Wed, 08 Dec 2010 06:12:00 GMT

SQL Server MVP Jonathan Kehayias (blog) emailed me a question last week when he noticed that the total memory used by the buffers for an event session was larger than the value he specified for the MAX_MEMORY option in the CREATE EVENT SESSION DDL. The answer here seems like an excellent subject for me to kick-off my new “401 – Internals” tag that identifies posts where I pull back the curtains a bit and let you peek into what’s going on inside the extended events engine.

In a previous post (Option Trading: Getting the most out of the event session options) I explained that we use a set of buffers to store the event data before we write the event data to asynchronous targets. The MAX_MEMORY along with the MEMORY_PARTITION_MODE defines how big each buffer will be. Theoretically, that means that I can predict the size of each buffer using the following formula:

max memory / # of buffers = buffer size

If it was that simple I wouldn’t be writing this post.

I’ll take “boundary” for 64K Alex

For a number of reasons that are beyond the scope of this blog, we create event buffers in 64K chunks. The result of this is that the buffer size indicated by the formula above is rounded up to the next 64K boundary and that is the size used to create the buffers. If you think visually, this means that the graph of your max_memory option compared to the actual buffer size that results will look like a set of stairs rather than a smooth line. You can see this behavior by looking at the output of dm_xe_sessions, specifically the fields related to the buffer sizes, over a range of different memory inputs:

Note: This test was run on a 2 core machine using per_cpu partitioning which results in 5 buffers. (Seem my previous post referenced above for the math behind buffer count.)

input_memory_kb	total_regular_buffers	regular_buffer_size	total_buffer_size
637	5	130867	654335
638	5	130867	654335
639	5	130867	654335
640	5	196403	982015
641	5	196403	982015
642	5	196403	982015

This is just a segment of the results that shows one of the “jumps” between the buffer boundary at 639 KB and 640 KB. You can verify the size boundary by doing the math on the regular_buffer_size field, which is returned in bytes:

196403 – 130867 = 65536 bytes

65536 / 1024 = 64 KB

The relationship between the input for max_memory and when the regular_buffer_size is going to jump from one 64K boundary to the next is going to change based on the number of buffers being created. The number of buffers is dependent on the partition mode you choose. If you choose any partition mode other than NONE, the number of buffers will depend on your hardware configuration. (Again, see the earlier post referenced above.) With the default partition mode of none, you always get three buffers, regardless of machine configuration, so I generated a “range table” for max_memory settings between 1 KB and 4096 KB as an example.

start_memory_range_kb	end_memory_range_kb	total_regular_buffers	regular_buffer_size	total_buffer_size
1	191	NULL	NULL	NULL
192	383	3	130867	392601
384	575	3	196403	589209
576	767	3	261939	785817
768	959	3	327475	982425
960	1151	3	393011	1179033
1152	1343	3	458547	1375641
1344	1535	3	524083	1572249
1536	1727	3	589619	1768857
1728	1919	3	655155	1965465
1920	2111	3	720691	2162073
2112	2303	3	786227	2358681
2304	2495	3	851763	2555289
2496	2687	3	917299	2751897
2688	2879	3	982835	2948505
2880	3071	3	1048371	3145113
3072	3263	3	1113907	3341721
3264	3455	3	1179443	3538329
3456	3647	3	1244979	3734937
3648	3839	3	1310515	3931545
3840	4031	3	1376051	4128153
4032	4096	3	1441587	4324761

As you can see, there are 21 “steps” within this range and max_memory values below 192 KB fall below the 64K per buffer limit so they generate an error when you attempt to specify them.

Clarification: I got another question about this topic last week, this time from a student in one of Jonathan Kehayias' classes. Someone noticed that if you take the size of the buffer (regular_buffer_size) and divide that by 64 KB you don't get the nice even boundary that I am claiming above and that someone is wondering "What gives?". A reasonable question.

What gives is that regular_buffer_size is reporting the usable buffer space, or the space left after we subtract a small amount of memory for the overhead of managing the buffers. You'll notice that if you take any regular_buffer_size and divide it by 64 KB you'll have the same fraction left over every time: 0.99687194824219, that's the overhead.

Max approximates True as memory approaches 64K

The upshot of this is that the max_memory option does not imply a contract for the maximum memory that will be used for the session buffers (Those of you who read Take it to the Max (and beyond) know that max_memory is really only referring to the event session buffer memory.) but is more of an estimate of total buffer size to the nearest higher multiple of 64K times the number of buffers you have. The maximum delta between your initial max_memory setting and the true total buffer size occurs right after you break through a 64K boundary, for example if you set max_memory = 576 KB (see the green line in the table), your actual buffer size will be closer to 767 KB in a non-partitioned event session. You get “stepped up” for every 191 KB block of initial max_memory which isn’t likely to cause a problem for most machines.

Things get more interesting when you consider a partitioned event session on a computer that has a large number of logical CPUs or NUMA nodes. Since each buffer gets “stepped up” when you break a boundary, the delta can get much larger because it’s multiplied by the number of buffers. For example, a machine with 64 logical CPUs will have 160 buffers using per_cpu partitioning or if you have 8 NUMA nodes configured on that machine you would have 24 buffers when using per_node. If you’ve just broken through a 64K boundary and get “stepped up” to the next buffer size you’ll end up with total buffer size approximately 10240 KB and 1536 KB respectively (64K * # of buffers) larger than max_memory value you might think you’re getting. Using per_cpu partitioning on large machine has the most impact because of the large number of buffers created. If the amount of memory being used by your system within these ranges is important to you then this is something worth paying attention to and considering when you configure your event sessions.

The DMV dm_xe_sessions is the tool to use to identify the exact buffer size for your sessions. In addition to the regular buffers (read: event session buffers) you’ll also see the details for large buffers if you have configured MAX_EVENT_SIZE. The “buffer steps” for any given hardware configuration should be static within each partition mode so if you want to have a handy reference available when you configure your event sessions you can use the following code to generate a range table similar to the one above that is applicable for your specific machine and chosen partition mode.

DECLARE @buf_size_output table (input_memory_kb bigint, total_regular_buffers bigint, regular_buffer_size bigint, total_buffer_size bigint)
DECLARE @buf_size int, @part_mode varchar(8)
SET @buf_size = 1 -- Set to the begining of your max_memory range (KB)
SET @part_mode = 'per_cpu' -- Set to the partition mode for the table you want to generate

WHILE @buf_size <= 4096 -- Set to the end of your max_memory range (KB)
BEGIN
BEGIN TRY

        IF EXISTS (SELECT * from sys.server_event_sessions WHERE name = 'buffer_size_test')
            DROP EVENT SESSION buffer_size_test ON SERVER
        DECLARE @session nvarchar(max)
        SET @session = 'create event session buffer_size_test on server
                        add event sql_statement_completed
                        add target ring_buffer
                        with (max_memory = ' + CAST(@buf_size as nvarchar(4)) + ' KB, memory_partition_mode = ' + @part_mode + ')'

EXEC sp_executesql @session

SET @session = 'alter event session buffer_size_test on server
state = start'

EXEC sp_executesql @session

        INSERT @buf_size_output (input_memory_kb, total_regular_buffers, regular_buffer_size, total_buffer_size)
            SELECT @buf_size, total_regular_buffers, regular_buffer_size, total_buffer_size FROM sys.dm_xe_sessions WHERE name = 'buffer_size_test'
    END TRY
    BEGIN CATCH
        INSERT @buf_size_output (input_memory_kb)
            SELECT @buf_size
    END CATCH
    SET @buf_size = @buf_size + 1
END

DROP EVENT SESSION buffer_size_test ON SERVER

SELECT MIN(input_memory_kb) start_memory_range_kb, MAX(input_memory_kb) end_memory_range_kb, total_regular_buffers, regular_buffer_size, total_buffer_size from @buf_size_output group by total_regular_buffers, regular_buffer_size, total_buffer_size

Thanks to Jonathan for an interesting question and a chance to explore some of the details of Extended Event internals.

- Mike