SQL Rally Dallas 2012: Tuesday Pre-Con Review

May 8th, 2012 Leave a comment

I landed in Dallas on Monday 7th May 2012 afternoon. I was very excited to be in Dallas, not only it was my first visit, but also the fact that my brain would be like a sponge absorbing new information at SQL Rally.

Dallas Convention Center is huge! I was lucky that I didn’t get lost here. Luckily with the guidebook, I was able to find where I was meant to be for registration, i.e. Area A Lobby. (If all else fails, check your wifi, if you can find “PASS Wifi”, you’re close to it!). The registration was easy and smooth. Sri Sridharan greeted me when I registered for the precon sessions. The morning started very well indeed.

So I went to my first session of SQL Rally today which was a pre Con session with Denny Cherry, Storage for the DBA. I decided to come to this session with a motive of knowing or understanding how to talk to the Infrastructure team in terms of setting up storage / virtualisation in an optimised way.

Denny was great in his presentation. He knows a lot about storage, virtualisation, network, SQL Server (and many others) which makes him a good presenter for the session. Although my knowledge of storage limited, I was able to keep up with the speed. Sure, I still have quite a lot of questions, but now I know what questions to ask and what to bingle.  I would highly recommend this session to any DBAs, especially those who have their own Data Centers. If your company provision the storage / server / virtualisation to another third party such as Rackspace, etc, this session would be immensely useful because  you can ensure that you (or your company) are paying for the service that meet the business requirements. I am very certain that every attendee would have had a unique learning experience from one another. If you missed out this session, hopefully it will be featured in another upcoming session.

Now, my afternoon will be spent on spending time to recover from jetlag and enjoying Dallas.

Upgrading SQL Server 2012 RC0 to RTM for Sharepoint 2010

Apr 27th, 2012 Leave a comment

After attending Rod Colledge’s presentation where he touched on steps in setting up Sharepoint 2010 Business Intelligence with SQL Server 2012 at SQL Saturday 138 in Sydney, I’ve decided that to be brave and tinker with Sharepoint installation and configuration a little bit more.

Recently I have this requirement to upgrade the SQL Server 2012 RC0 installation on a Sharepoint server to SQL Server 2012 RTM version. I decided to do a bit of research and typing in “upgrade sql server 2012 rc0 to rtm sharepoint” on Bing returned a few promising results. One of them was Jen Underwood’s comment posted at http://social.msdn.microsoft.com/Forums/en-US/sqldensetup/thread/cd7287b6-f587-4a87-9676-75ddc9b09800.

The worst case scneario that I could think of is that I would need to rebuild the Sharepoint server. That’s OK because luckily we’re still in an eval mode and not in live production yet. So I did the upgrade. For a non Sharepoint savvy person, the whole process took me about 1.5 hours including copying the SQL Server 2012 iso file across the network.

The good news is, it was successful! (Gee, I never thought that it would be this easy!)

Before we go ahead, I’d like to point out a few things:

  1. At the end of the upgrade, the Sharepoint machine will have 2 installation paths of SQL Server for each instance, i.e. the existing SQL Server 2012 RC0 folder and SQL Server 2012 RTM folder. I am pedantic and try to avoid this as much as possible, but for now, it’s good enough; perhaps we may even be able to clean the folders up later. SQL Server 2012 Directories after Upgrade RC0 to RTM in Sharepoint 2010
  2. The target Sharepoint environment only has SQL Server installation specific for Sharepoint – i.e. SQL Server instances on the Sharepoint machine are not used by any other Applications (if they do, then make sure that they’re no live connection to the SQL Server instances).
  3. The Sharepoint server is not an Active Directory server.
  4. Test this upgrade method in a Test machine first before rolling out to Production.

 

Let’s get into it now.

[Warning: The below steps should be used as guidelines and should be tested in a Test machine first. If you are using a virtual machine for your Sharepoint server, don't forget to create a backup prior to upgrading]

 

Prerequisites

  1. Administrator previlege on the machine as well as Sharepoint.
  2. SQL Server 2012 RTM version – and a product key for the Business Intelligence or the Enterprise Edition.

Steps

  1.  Go to Sharepoint Central Administration > Manage Services on Server (under the System Settings heading). Stop the following services: SQL Server Analysis Services, SQL Server PowerPivot System Service, SQL Server Reporting Services Service.Stop Sharepoint 2010 Services related to the SQL Server 2012 items to be upgraded
  2. Run setup.exe of SQL Server 2012 RC0 and choose Maintenance.
  3. Select the “Edition Upgrade” option (although it only lists SQL Server 2005, SQL Server 2008 and SQL Server 2008R2 upgrade) which will show you the list of instances that can be upgraded.
  4. Select “POWERPIVOT” instance to be upgraded.SQLServer2012 Upgrade RC0 to RTM Select POWERPIVOT Instance
  5. Click Next to move on to the “Select Features” which will grey out all the features installed under POWERPIVOT.
  6. Click Next to “Reporting Services Sharepoint Mode” which presents a message that Continuing with this upgrade may put your SharePoint farm in an inconsistent state. Tick on the “Continue with the upgrade.” option.SQL Server 2012 Reporting Services Sharepoint Mode upgrade message
  7. Click Next to go to “Instance Configuration” which you will need to give a new name, e.g. POWERPIVOT2100

    SQL Server 2012 Upgrade RC0 to RTM Instance Configuration

  8. Click Next until you reach “Upgrade Rules”. An error may come up stating that ‘Rule “SQL Server Analysis Services Upgrade Service Funcitonal Check” failed. The current instance of the SQL Server Analysis Services service cannot be upgraded because the Analysis Services service is disabled or not online. Please start the service and then run the upgrade rules check again‘. If this happens, go to SQL Server Configuration Manager and you will see that the Analysis Server is Stopped. Change this from Disabled to Automatic. (This may be because I stopped the SQL Server Analysis Services Service from Sharepoint earlier on).

    SQL Server 2012 Upgrade Rules Error related to Analysis Services
    SQL Server 2012 SSAS POWERPIVOT Start Mode changed to Automatic

  9. Click OK on the error dialog box and click Re-run on the Upgrade Rules window. It should rerun the upgrade checks and progress on to “Update Progress”.
  10. In the middle of the upgrade of SQL Server Analysis Services Service, a similar error will come up. “The following error has occurred: The service cannot be started, because it is disabled or because it has no enabled devices associated to it“. Again, go to SQL Server Configuration Manager and change the start mode from Disabled to Automatic.
    SQL Server 2012 Upgrade RC0 to RTM Upgrade Progress Error
  11. Click retry and the installation should resume normally to completion.
  12. Once this is done, you can verify the servers installed on the Sharepoint Server by launching SQL Server Management Studio and connecting to different parts of POWERPIVOT SQL Server instances.
    SQL Server 2012 SSMS Instances

After the upgrade, don’t forget to reboot the Sharepoint server.

Post upgrade verifications

  1. Verify that your PowerPivot gallery or Default PowerPivot Service Application is running properly, by going to Sharepoint Central Administration > Manage Service Applications (under Application Management) > Default PowerPivot Service Application
  2. Verify that Microsoft.AnalysisServices.SharePoint.Integration.dll in Windows Assemby is referring to 11.0.2100.60 (right click on the dll, choose Properties and click on Version tab). More detailed info http://msdn.microsoft.com/en-us/library/ee210646(SQL.110).aspx#checkver
  3. Verify that msmdsrv.exe version is 11.0.21.00.60 in \Program Files\Microsoft SQL Server\MSAS11_00.PowerPivot\OLAP\bin (right click on the file, choose Properties and click on the Details tab). More detailed info http://msdn.microsoft.com/en-us/library/ee210646(SQL.110).aspx#checkver

 

And that is it! OK, I bet you can do this in less than 1.5 hours!

Hope you find this instruction useful. Please let me know if you have any suggestions on the steps that I may have missed out from the above.

 

Upcoming DirectQuery vs Vertipaq Presentation

Apr 18th, 2012 1 Comment

How exciting is it for us SQL Professionals to have so many SQL Server events since the beginning of the year? We’ve had 12 Hours of SQL, 24 Hours of PASS, 24 Hours of PASS in Russian edition, SQL Server 2012 Virtual Launch, SQL Saturday (ANZ tour is currently running) and plenty other PASS virtual chapter sessions.
 
I’m quite honoured that I’ve been selected to present at SQL Server User Group in Sydney, 24 Hours of PASS, and SQL Saturday #138 as well as being picked by the Community to present at SQL Rally Dallas this year.

I’d like to focus a bit more on my upcoming SQL Saturday #138 session and SQL Rally Dallas session. The title is “DirectQuery vs Vertipaq Mode in SSAS Tabular Model”. This session will take you to a second step to see what’s beyond the default option (In-Memory / Vertipaq). 

Since the time I wrote the abstract for SQL Rally, Microsoft has rebranded Vertipaq to “xVelocity in-memory analytics engine (VertiPaq)”. Some of the project settings / options have also been changed to refer to “In-Memory”, instead of Vertipaq; while some remain as Vertipaq such as in the Tabular Model Analysis Server Properties. Despite the name changes, they mean the same thing in SSAS. 

The DirectQuery vs Vertipaq Mode in SSAS Tabular Model session brings a tiny step beyond your first leap to deciding/considering In-Memory Tabular mode. It concentrates on introducing DirectQuery and how different it is to In-Memory. The demo will also show how the two modes differ in query execution, design and maintenance aspects; giving you enough information to make an informed decision on which mode to use based on your business case. 

If you are new to Tabular Model or PowerPivot and would like to know more about it, there are quite a number of great resources to get you up to speed with it, as listed at the end of this post. Hopefully by then, you’d be comfortable in learning more in my upcoming “DirectQuery vs Vertipaq Mode in SSAS Tabular Model ” session. Having said that, if you’re completely new to Tabular Model but want to know what’s the fuss is about, come to the session – and have your first leap to learning Tabular Model with me. 

I have been having a great joy using Tabular Model in my current work, and am continuously extending my knowledge by preparing for this presentation. So I do hope that you can attend SQL Saturday #138 in Sydney or SQL Rally in Dallas and join me at the session. 

 

Reading/Watching List

Welcome to Tabular Projects

http://blogs.msdn.com/b/analysisservices/archive/2011/07/13/welcome-to-tabular-projects.aspx

 

Building your first Analysis Services Tabular BI Semantic model with SQL Server 2012

Speaker: Frederik Vandeputte

http://technet.microsoft.com/en-us/edge/building-your-first-analysis-services-tabular-bi-semantic-model-with-sql-server-2012

 

Building the Perfect BI Semantic Tabular Models for Power View

Speaker: Kasper De Jonge

http://technet.microsoft.com/en-us/edge/building-the-perfect-bi-semantic-tabular-models-for-power-view

 

Developing and Managing a Business Intelligence Semantic Model (BISM) in SQL Server Code Name “Denali” Analysis Services [BIA-316-M]

Speaker: Cathy Dumas

http://www.sqlpass.org/summit/2011/Speakers/CallForSpeakers/SessionDetail.aspx?sid=1964

Note: if you have the PASS Summit 2011 DVDs, pull this session out and start watching it. Cathy was superb in this presentation and I would consider this as an energetic presentation where I “ooo… aaaa…”-ed a few times!

SQLRally Dallas 2012 Website

 

 

Undo Bad Tabular Mode Analysis Server Properties

Apr 11th, 2012 3 Comments

Warning: This post contains some advice that may not be ideal for Production / critical environment. Please read thoroughly and proceed any changes on your environment with caution.

Late last week, I decided to change a few settings on my development Tabular Model Server. The first change was related to the VertipaqPagingPolicy value in an attempt to understand the setting a little better. The second was related to changing the DataDir path.  Changing values of the Analysis Server properties can have adverse effects. It is best done only if you understand the implication AND if you have thoroughly tested it in a dev environment prior to making the changes in Production.

The changes I’m outlining below were done through the Analysis Server Properties dialog box retrieved by right clicking on the Tabular Model Server and choosing Properties as shown below.

 

Accessing Analysis Server Properties

This will give you a nice dialog box to edit the Analysis Server Properties:

Tabular Model Analysis Server Properties

Changing Analysis Server Properties usually require a server restart. Fingers crossed, the Analysis Server should restart properly if the configuration is OK.

Let’s talk about the changes I’ve made, why and how I got stuck!

Vertipaq Paging Policy

I have hit a number of errors in relation to Memory Alocation Failure on my dev machine. So, I thought I’d start playing around with the VertipaqPagingPolicy values and see if it could help solving the problem.

The exact error message on my dev box was:

The following system error occurred:  Insufficient quota to complete the requested service.

Memory error: Allocation failure. If using a 32-bit version of the product, consider upgrading to the 64-bit version or increasing the amount of memory available on the machine.

The current operation was cancelled because another operation in the transaction failed.

 

MSDN Books online describes three different values acceptable for VertipaqPagingPolicy.

http://msdn.microsoft.com/en-us/library/ms174514(v=SQL.110).aspx

  • Zero (0) is the default. No paging is allowed. If memory is insufficient, processing fails with an out-of-memory error.
  • 1 enables paging to disk using the operating system page file (pagefile.sys).
  • 2 enables paging to disk using memory-mapped files.

 

As BOL mentioned, when VertipaqPagingPolicy is set to 1 or 2, processing is less likely to fail due to memory constraints because the server will try to page to disk using the method specified.  So I decided to change VertipaqPagingPolicy to 1 then restarted the server. Performing Process Full on the partitions one at a time worked perfectly without error with VertipaqPagingPolicy = 1.

Setting VertipaqPagingPolicy to 1

Marco Russo has published more information on VertipaqPagingPolicy in his article titled “Memory Settings in Tabular Instances of Analysis Services“. I strongly recommend reading this article - may be even a few times.

DataDir

By default the DataDir location for Tabular Model server is located at:

C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Data

This folder typically is a home to the files / folders that I consider as “system” oriented, and it may also have data files and folders of users’ Tabular Model databases. The “system” files/folder that you typically find in DataDir folder are:

  1. ExcelMDX.0.asm.xml
  2. EXCELXLTINTERNAL.0.asm.xml
  3. master.vmp
  4. VBAMDX.0.asm.xml
  5. VBAMDXINTERNAL.0.asm.xml
  6. ExcelMDX.0.asm
  7. EXCELXLTINTERNAL.0.asm
  8. VBAMDX.0.asm
  9. VBAMDXINTERNAL.0.asm
  10. CryptKey.bin

If you have accidentally changed the DataDir location by mistake and restarted the Analysis Services (Tabular Mode) server, it would come up with an error.

Error when Starting Analysis Server

As long as you know what the correct DataDir path and that the files are still in tact, don’t panic. The fix to this is fairly simple.

In the Config folder of the server (by default installed on C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Config\), there is a file called “msmdsrv.ini”. This file contains the settings that will be read and used when the Analysis Server is started. So when the server doesn’t start due to incorrect settings, the easy approach is to correct the configuration settings in msmdsrv.ini file. After the settings are corrected accordingly in this file, the server can be restarted via SQL Server Configuration Manager and it should be able to start properly.

There are two options to correct the msmdsrv.ini file.

Option 1 – Manually fixing it, if you know what you’re doing.

In regards to my earlier case with accidental DataDir change, I changed the <DataDir> element on the second line of “msmdsrv.ini” back to the previous value, i.e.

<ConfigurationSettings>
  <DataDir>C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Data</DataDir>
  <LogDir>...

 

As previously stated, I would not recommend changing settings without understanding the implications and prior testing. I would also like to suggest making a copy / backup of the msmdsrv.ini file BEFORE making the changes manually.

Another tip, when you try opening the “msmdsrv.ini” file and it may come up with the following error message.

Access Is Denied

This is fine and you can treat this as if it was a reminder to create a back up file of the file that you were about to change. So copy the file and call the new file as “msmdsrv_MessedUp.txt” and rename the original file to “msmdsrv.txt” then make the required correction. Then rename the “msmdsrv.txt” back to “msmdsrv.ini”.  Restart the server – and voila! - the Analysis Server should start if the new settings are good.

 

Option 2 – Restoring the “msmdsrv.ini” file from the “msmdsrv.bak” file.

In the same folder as “msmdsrv.ini”, there is also “msmdsrv.bak”. This bak file is a backup copy of the last known good configuration file to the Analysis Services server. So, you may be able to use this bak file to restore everything back to a previous good configuration.

 

Wrap up

As I’ve warned earlier, changes to msmdsrv.ini can result in an unstable state and/or non-startable Analysis Server. So, please explore this with caution. I am a firm believer of giving things a go and exploring options that are available, but always with caution.

Changing VertipaqPagingPolicy has helped me to work around the Memory Allocation Error issue on my dev machine temporarily (next step would be to acquire more powerful machine!). When mistakes are made in configuring the Analysis Server properties, it may cause the Analysis Server to be non-startable. In this case, the solution to it, may simply be reverting the “msmdsrv.ini” file back to its previous working state.

 

 

SQL Saturday #138 in Sydney

Apr 9th, 2012 Leave a comment

Finally! SQL Saturday will be in Sydney for the first time on 21 April 2012 from 08:15 to 17:15 at

Epping Boys High School (EBHS), 213 Vimiera Road, Eastwood, NSW, 2122, Australia.

 

There will be 4 DBA/Development Stream as well as 5 BI Streams. Keynote and Locknote will be presented by Grant Paisley, the president of SQL Server Usergroup Sydney.

 

The DBA/Dev sessions are:

09:30 Database Development & Agile: Strange Bedfellows by Daniel Nolan

13:00 The Dark Art of Performance Tuning by Peter Ward

15:15 Better Together: Merge, Partitioning, ColumnStore by Greg Low

15:45 Keeping the light’s on with SQL 2012 Always On by Warwick Rudd

 

The BI sessions are:

09:30 BI Architecture with SQL 2012 & SharePoint 2010 by Rod Colledge

11:00 Killer Real-World PowerPivot Examples II  by Grant Paisley

13:00 Accelerated BI Growth with Power View & PowerPivot  by Bhavik Merchant

15:15 Analytic T-SQL Functions in 2012 by Rob Farley

15:45 DirectQuery vs Vertipaq mode in SSAS Tabular Model  by Julie Koesmarno

 

As you can see, six of the sessions are related to the new SQL Server 2012 to follow up the recent RTM announcement in March 2012.

After registering for SQL Saturday #138, you can then build your schedule for the day by going to Schedule > Schedule Builder from the menu.

I will be presenting on the last session of the BI Stream, “DirectQuery vs Vertipaq mode in SSAS Tabular Model“. This session will explain the different modes of Tabular Model and how they work. The aim of the session is so that you are equipped with enough information to decide which mode of Tabular Model is suitable for your requirements and to confidently explore these choices.

Well, what are you waiting for? Sign up for SQL Saturday now and join me for SQL learn-athon!

SQL Saturday #138 in Sydney

 

 

 

24 Hours of PASS – High Volume Data Processing Techniques Without Driving Your DBA Crazy

Mar 21st, 2012 Leave a comment

I have just delivered my presentation on “High Volume Data Processing Techniques Without Driving Your DBA Crazy!” via webinar at 24 Hours of PASS (24HOP) Spring 2012. Thank you for those who attended the session. Hope it was useful for you. If you have any questions, please don’t hestitate to contact me and I will do my best to answer them.

 

As promised, below is a list of links that I briefly touched on at the presentation.

SQL Server 2012

ORDER BY Clause (OFFSET .. FETCH NEXT …)

http://msdn.microsoft.com/en-us/library/ms188385%28v=SQL.110%29.aspx

 

Paging Function Performance in SQL Server 2012

http://www.mssqlgirl.com/paging-function-performance-in-sql-server-2012.html

 

Columnstore Indexes

http://msdn.microsoft.com/en-us/library/gg492088(v=SQL.110).aspx

 

 

SSIS

The Data Loading Performance Guide

http://msdn.microsoft.com/en-us/library/dd425070(v=sql.100).aspx

 

Minimally Logging Bulk Load Inserts into SQL Server

http://www.mssqltips.com/sqlservertip/1185/minimally-logging-bulk-load-inserts-into-sql-server/

 

BDD

The “Balanced Data Distributor” SSIS

http://blogs.msdn.com/b/sqlperf/archive/2011/05/25/the-balanced-data-distributor-for-ssis.aspx

 

Download Center – BDD

http://www.microsoft.com/download/en/details.aspx?id=4123

 

Special thanks to 24HOP team and IBTalk for everything. I hope to present with 24HOP in the near future. The recording will be available in a few weeks time via the 24HOP website.

 

 

Paging Function Performance in SQL Server 2012

Mar 11th, 2012 16 Comments

Late last year when I discovered the new TSQL Enhancement introduced in SQL Server 2012, particularly the new Paging Function, I thought to myself that this would be very useful in a number of scenarios. Before I go any further, let’s have a look at what the Paging Function is.

Update: For simplicity, the following scenario uses [Production].[TransactionHistoryArchive] from AdventureWorks2012 and assumes that TransactionID is always continous and incremental as well as starting with TransactionID of 1. (I will discuss more on a scenario where the TransactionID is not continuous in a different post). Special thanks to Shy Engelberg for highlighting the behaviour differences in all three methods discussed in this post. Please read Shy’s comment at the end of this post for further clarification.

The Paging Function is part of the SELECT syntax, as an extension to the ORDER BY clause. Below is an example of its usage to retrieve a data set with TransactionID from 5001 and for the next 100 rows.

-- Paging Function
-- Retrieve 100 rows starting from TransactionID of 5001
SELECT
	 [TransactionID]
	,[ProductID]
	,[ReferenceOrderID]
	,[ReferenceOrderLineID]
	,[TransactionDate]
	,[TransactionType]
	,[Quantity]
	,[ActualCost]
	,[ModifiedDate]
FROM [Production].[TransactionHistoryArchive]
ORDER BY [TransactionID]
OFFSET 5001 ROWS
FETCH NEXT 100 ROWS ONLY

This function is very intuitive and would be applicable for returning data set a “page” (batch of rows) at a time. The OFFSET value can be expressed as an integer variable and so can the FETCH NEXT value, which allows easy and configurable data set paging for web/reporting applications.

A few things to note about the Paging Function:

  • The ORDER BY column(s) doesn’t have to be consecutive, meaning that we can avoid creating a surrogate consecutive integer key for the purpose of paging. This helps in a typical query to retrieve a page of ”active” Transaction records whereby some rows in the table may be deleted or “deactivated”, rendering broken IDs.
  • OFFSET and FETCH can only be used in the last query that participates in UNION, EXCEPT or INTERSECT operation.
  • If the column specified in the ORDER BY column(s) is not unique, the order of the output is not always consistent.

Alternative: TOP … Batching

In an earlier version, you could write a query to return the same data set using the TOP keyword.  Please note that the SET ROWCOUNT clause to limit the number of rows returned in a SELECT query, will not be supported in the next version of SQL Server – and the TOP keyword should be used instead.

-- TOP... Batching
-- Retrieve 100 rows starting from TransactionID of 5001
SELECT TOP 100
	 [TransactionID]
	,[ProductID]
	,[ReferenceOrderID]
	,[ReferenceOrderLineID]
	,[TransactionDate]
	,[TransactionType]
	,[Quantity]
	,[ActualCost]
	,[ModifiedDate]
FROM [Production].[TransactionHistoryArchive]
WHERE [TransactionID] >= 5001
ORDER BY [TransactionID]

Alternative: Direct ID Batching

An alternative to the above in an earlier version to SQL Server 2012 is shown below, The result will be the same, assuming TransactionID values are always consecutive.

-- Direct ID Batching
-- Retrieve 100 rows starting from TransactionID of 5001
SELECT
	 [TransactionID]
	,[ProductID]
	,[ReferenceOrderID]
	,[ReferenceOrderLineID]
	,[TransactionDate]
	,[TransactionType]
	,[Quantity]
	,[ActualCost]
	,[ModifiedDate]
FROM [Production].[TransactionHistoryArchive]
WHERE [TransactionID] BETWEEN 5001 AND 5100
ORDER BY [TransactionID]

By now, you may realise that the Direct ID batching method may need a surrogate consecutive key to ensure the batch/paging size is consistent, with the exception of the last page.

Execution Plan Comparison

Let’s inspect the execution plan of all three methods.

1. Direct ID Batching

As expected, here SQL Server is using Clustered Index Seek as per the index filter on the ID between the 2 values (5001 and 5100). Note that the Actual Number of Rows on the Execution Plan Details of the Clustered Index Seek is 100.

Clustered Index Seek on Production.TransactionHistory

Clustered Index Seek on Production.TransactionHistory

Actual Number of Rows = 100 for Clustered Index Seek - Direct ID Batching

2. Top Batching

Similar to the Direct ID Batching, SQL Server is also using Clustered Index Seek. It then uses TOP as the next step.  Note that here similar to Direct ID Batching, the Clustered Index Seek is also returning Actual Number of Rows of 100.

 

Clustered Index Seek on Production.TransactionHistory

Clustered Index Seek on Production.TransactionHistory

Actual Number of Rows = 100 for Clustered Index Seek - Top ... Batching

 

3. Paging Function

Surprisingly, SQL Server is using Clustered Index Scan and the Actual Number of Rows returned here is 5101!

Clustered Index Seek on Production.TransactionHistory

Clustered Index Seek on Production.TransactionHistory

 

Actual Number of Rows = 5101 for Clustered Index Seek - Paging Function

Performance Comparison

Using Paging Fuction on a source table with a large number of records may not be ideal. As you can already guess, the larger the offset size, the larger the Actual Number of Rows is returned too. Consequently, the Paging Function will take longer and longer as the paging progresses. To demonstrate this, I have a table with over 14.5 Million rows where I iterate through the records in a batch size of 500,000. Each iteration inserts the batched records into a heap table that is empty to start with.

Below is the chart exhibiting the time taken to insert the records in batches using Direct ID batching vs Top batching vs Paging function methods. To start with, the three techniques take about the same amount of time. After inserting about 1.5 Million rows the Paging Function query takes significantly longer than the other two.

SQL_Batching_Methods_Comparison_Chart

Wrap Up

Although SQL Server 2012 new Paging function is intuitive for the purpose of paging a data set, it comes at a cost for large volume of data. The Top Batching and Direct ID Batching perform significantly better under similar circumstances, i.e. simple Source Data with continuous ID.

The above discussion stems from my recent research for the 24 Hours of PASS Spring 2012 session “High Volume Data Processing Without Driving Your DBAs Crazy!” which will be held on 21 March 2012 at 16:00 AEST. 24 Hours of PASS is free and contains 24 back-to-back  great SQL Server sessions. You can find many different topics of SQL Server, from DBA, Database development, BI to Performance sessions. So, register now!

Disclaimer

Please note that every environment is different and that these tests are conducted on my small laptop and should not be used as an indication on how the above batching methods will perform in your Production environment. Please test and confirm that you understand these techniques before applying to your Production environment.

 Last updated on 25 March 2012 to add further explanation on the scenario used in this post.

 

 

Becoming a better BI Developer with SQL Server 2012

Feb 26th, 2012 2 Comments

The 12 Hours of SQL Server 2012 event started at 10:12 PM on February 24th, 2012 GMT and delivered 12 hours back-to-back sessions.

I attended three of them (due to the time zone difference) and am very impressed with how much I have learnt. I highly recommend the following sessions, which I think are useful for me in becoming a better BI developer with SQL Server 2012.

1. 14:12 – 15:12 Power View from the Data Visualisation Perspective

Jen Stirrup reminds us on Data Visualisation principles and discusses how Power View supports Data Visualisation best practices. I enjoy Jen’s examples of good and bad Data Visualisation and how good Data Visulisation contributes to strategic enterprise decisions. Further reading: http://www.jenstirrup.com/2012/02/sqlpass-business-intelligence-virtual.html

2. 18:12 – 19:12 Building the Perfect BI Semantic Tabular models for Power View

Kasper De Jonge demonstrates that the right BISM Tabular Model would enable users to fully utilise Power View capabilities and building great data visulisation. I particularly like how Kasper incorporates Data Mining query in the Tabular Model to show how powerful Power View could be. Further reading: http://www.powerpivotblog.nl/tutorial-optimize-a-sample-powerpivot-model-for-power-view-reports

3. 21:12 – 22-12 Database Schema Management & Deployment using SQL Server Data Tools (SSDT)

Gert Drapers shows how SSDT makes it easier for database development development, testing, deployment and migrations; some features of the Visual Studio 2010 Database Development have been incorporated in SSDT. I like how Gert uses plenty of demos to show a more streamlined database development to deployment. Further reading: http://social.msdn.microsoft.com/Forums/sr-sp-latn/ssdt/thread/ee6f7d4f-aa6c-4051-b4b9-d187e8f89c5d

Hopefully the recorded sessions will be available very soon.

12 Hours of SQL Server 2012

Sharing with SQL Server Community

Feb 21st, 2012 2 Comments

I have just attended a local SQL Server User Group in Sydney run by Victor Isakov earlier this month.

In this meeting, Victor went through some updates on SQL Server 2012, the launch date and most notably the changes to the certifications and the courses being offered. More information about the certifications can be found here: http://specialops.sqlpass.org/Certification.aspx#fbid=Z7vC96QRoxV

Special Ops tour is starting in USA from early March. We, in the southern hemisphere, can watch the Launch online on March 7th. Hopefully there is going to be a SQL Saturday event held in Sydney shortly to go through changes in SQL Server 2012.

Also, the 24 Hours of PASS is on next month on 21 March 2012 00:00GMT (check your local time by clicking here). I attended last year’s sessions and they were very impressive. There are 24 back-to-back sessions that you can pick from and I will be presenting one of them with a topic called “High Volume Data Processing Without Driving Your DBA Crazy!”. So, why not Register Now?

SQLRally 2012 is fast approaching. 40 General Sessions have been announced and there is a chance for the Community to vote for the next 20 Sessions. Check out these sessions and hope to see you there!

Edit: ANZ SQL Saturday Tour is here from 12 – 28 April 2012. For more info on the relevant cities, please go to:

Brisbane http://sqlsaturday.com/135/eventhome.aspx
Wellington http://sqlsaturday.com/136/eventhome.aspx
Canberra http://sqlsaturday.com/137/eventhome.aspx
Sydney http://sqlsaturday.com/138/eventhome.aspx
Adelaide http://sqlsaturday.com/139/eventhome.aspx
Perth http://sqlsaturday.com/140/eventhome.aspx

Hope to see you there!

(Special thanks to Rob Farley for letting me know!)

SQL Server 2012, The Good and The Bad

Jan 27th, 2012 Leave a comment

 

As a SQL Server specialist, hearing new updates on SQL Server always excites me. SQL Server 2012 provides plenty of features, such as Columnstore Index and AlwaysOn features would immensely help my previous projects. The PowerView feature and the integration of BI capabilities of SQL Server 2012 with Sharepoint 2010 is definitely not something to dismiss very easily.  These areas below are the new features in SQL Server 2012 that I would consider as being important for my past and upcoming projects, and I wlil discuss a few of the goods and the bads.

 

1. SQL Server Data Tools (SSDT)

The Good: This will replace BIDS. SSDT is using Visual Studio 2010 Shell and has revamped the look and feel of SSIS in some degree.

The Bad: After installing SQL Server 2012 RC0, I am unsure how to add some third party SSIS data flow component. It may mean that if you are using Balanced Data Distributor SSIS Data Flow component, it requires some testing first.

Further reading: http://msdn.microsoft.com/en-us/data/tools.aspx

 

2. ColumnStore Indexes

The Good: This would immensely help those Data Warehouse databases that are large (or getting larger and larger). Those fact tables with millions of records would definitely benefit this feature. There are some caveats to this; one in particular is that it only comes in the Enterprise Edition of SQL Server 2012.

The Bad: The table with ColumnStore indexes cannot be updated as per SQL Server 2012 release. This may not be critical as there is a workaround on this: http://msdn.microsoft.com/en-us/library/gg492088(v=SQL.110).aspx#Update

 

3. Rapid Data Analysis through PowerView and PowerPivot

The Good: Business Analysts and Data Analysts who are exploring the data, can start with simple, powerful and presentation ready reports using PowerView and PowerPivot.

The Bad: PowerView is only available with Sharepoint 2010 BI integration – as per the SQL Server 2012 RC0. This may change in the final version.

Further reading: http://msdn.microsoft.com/en-us/library/hh213579(v=sql.110).aspx

 

4. Tabular Model SSAS

The Good: This feature would help with leveraging data warehouse design with less effort from building overly complex cubes, for powerful and highly performing data analysis and business reporting.

The Bad: Self joins are not permitted, Composite Keys are not supported, Unique Lookup columns are required, only Single Active Relationship is supported and only One Relationship for each source column is allowed. Choosing DirectQuery mode is not fully compatible with Vertipaq mode. Careful consideration is required before switching all the way to DirectQuery mode. .

Further reading: http://msdn.microsoft.com/en-us/library/hh212945(v=sql.110).aspx

 

5. Licensing / editions

The Good: If you have Software Assurance Agreement with Microsoft, you may not be affected greatly by the costs incurred due to the new pricing model.

The Bad: Planning is required whether to choose Standard, BI or Enterprise edition appropriately and it has been reported that the new licensing model will incur more costs in most cases.

Further Reading: http://www.microsoft.com/sqlserver/en/us/future-editions/sql2012-editions.aspx, http://redmondmag.com/articles/2011/11/05/microsoft-unveils-sql-server-2012-licensing-and-pricing.aspx

 

The above are my take of SQL Server 2012, I strongly urge you to use it and to test it out. You may agree or disagree (or come up with different points to consider) with the above.  Please share your thoughts by leaving comments.

 

Last but not least, don’t forget to attend SQL Server 2012 Virtual Launch Event on March 7th: http://www.sqlserverlaunch.com/ww/Home

First « 23456 » Last