SQLAuthority News – Two Whitepapers on Performance Tuning and Enhancements

The best part of being in the technology domain is that I get a lot of opportunity to talk and write at different places. I travel quite a bit in lengths to deliver some of my learnings with audiences around the world. These learnings and writings as in this blog have influenced and encouraged a number of others to also jump into the blogging or writing habit. I cherish and love doing these outside of this site too. Recently, I wrote a couple of whitepapers and thought it was worth a call here over this blog too. What are these whitepapers all about?

Beginning Performance Tuning

If you want to learn a new competency, where would you start? One cannot wake up on a warm sunny day and start driving on their own. There is a process, steps and most importantly a learning part from an expert to get them started. And over a period of time this practice will make them expert too. The analogy hold good even here. There is no silver bullet to performance tuning and there is no “it depends” to performance tuning.

The facts and the process to learn is what gets discussed in this whitepaper. The best person to start performance testing are the developers and DBAs of the application itself. They exactly know the architecture, workload, dependencies and how access happens in the system. In this whitepaper I talk about this fundamental process first and then delve into some of the simplest techniques I have used for ages to do performance testing.

You can read the Whitepaper: Beginning Performance Tuning with SQL Server 2014 here.

Performance Enhancements with SQL Server 2014

Second in series that I wrote was around Performance Enhancements with SQL Server 2014. The need to upgrade to the latest stack always has its own challenges and administrators are constantly looking at the need to understand the new features so that it can help them at work. This constant struggle and need to learn, implement and troubleshoot the latest and greatest is a challenge in itself.

In this whitepaper, I take my random 4 picks of performance enhancements with SQL Server 2014 which I personally felt are worth a mention. Some of them include ColumnStore Index, Managed Lock Priority, Buffer Pool Extensions and more. I take an introduction to each of these enhancements and what these features bring to the table for an administrator / developer. In reality, each of these topics warrant a whole whitepaper but I am sure the future blogs will contain some of these for sure. Stay tuned on that.

You can read the Whitepaper: Performance Enhancements made with SQL Server 2014 here.

I am sure you will enjoy these whitepapers and the best part is they are absolutely FREE. So do download them and let me know your thoughts. Would love to incorporate some of them in my future writing on this blog or papers I write.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQLAuthority News – Download Whitepaper – A Case Study on “Hekaton” against RPM – SQL Server 2014 CTP1

In this new world of social media, apps and mobile devices, we are all now getting impatient. Automatic updates have spoiled few of our habits. When a new feature is released everybody wants to immediately adopt the feature and start using it. Though this is true in the world of apps and smart phones, but it is still not possible in the developer’s world. When new features are around, before we start using it, we need to spend quite a lots of time to understand it and test it. Once we are sold on the feature we refer the feature to our manager and eventually the entire organization makes decisions on upgrading to use the new feature.

Similarly, when the new feature of In-Memory OLTP was announced, pretty much every SQL Server DBA wanted to implement that on their server. Through the implementation of the feature is not hard, it is not that easy as well. One has to do proper research about their own environment and workload before implementing this feature.

Microsoft has recently released a Case Study on In-Memory OLTP feature. Here is the abstract from the white paper itself.

I/O latch can cause session delays that impact application performance. This white paper describes the procedures and common I/O latch issues when migrating to Hekaton in SQL Server 2014. It also includes challenges that occurred during the migration and the performance analysis at different stages. 

If you are going to implement In-Memory OLTP database, this is a good case study to refer. Download white paper from here.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL Authority News – Microsoft Whitepaper – SQL Server 2014 and Windows Azure Blob Storage Service: Better Together

Microsoft has released SQL Server 2014 earlier this month and now have released very long but interesting white paper on Windows Azure Blog Storage Services. SQL Server Data Files in Windows Azure feature is available in all editions of SQL Server 2014. This feature is enabled by default and at free of cost. This feature makes SQL Server 2014 hybrid cloud database as it provides native support for database files stored as Windows Azure Blobs. If you notice this is a big leap forward where data stored locally as well as on a cloud are accessed by SQL Server 2014 at the same time. You can also host SQL Server Instance in Azure cloud and it can also use the Windows Azure Blobs just like on-premises SQL Server.

Let me give you another example, if you have a situation that you have stored your database files in the cloud as well as backup on Windows Azure Cloud. Now if you want to restore your backup you do not have to bring that back to on-premises before restoring it. You can just restore your data directly from Windows Azure Storage to your SQL Server 2014 instance in a Virtual Machine. It will remove the necessity to do lots of data movement between cloud and on-premises servers.

This white paper discusses exactly the same feature very much in detailed. This white paper has over 116 pages and the size of the word file is 2.5 MB. However, after a long time I have found a Whitepaper which explains concepts from the beginning and walks users step by step with the examples. The appendix file to this Whitepaper contains all the necessary code as well as very interesting reference material. I think I am going to spend my entire weekend reading and learning from this Whitepaper.

Image included in this blog post is courtesy to the same white paper and it explains the story of entire white paper in just one image. Brilliant.

Download the white paper SQL Server 2014 and Windows Azure Blob Storage Service: Better Together.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – Microsoft Whitepaper – Migrating Content Between Report Servers

A very common challenge developer and administrator face when they have to migrate content between SQL Server Reporting Server. This is because SQL Server Reporting Services (SSRS) currently doesn’t include a feature for migrating content items and settings from one Reporting Services report server to another report server. There are two different methods to do overcome this challenge. 1) Create RSS scripts that are used by rs.exe to migrate the content of Reporting Services, between reports servers of the same mode or different modes. 2) Run Reporting Services Migration Tool to migrate the content from a Native mode report server to a SharePoint mode report server.

The white paper discusses the Reporting Services Migration tool and various aspects of method of migration like the location of the tool, supportability of the tool, a list of contents migrated by the tool, migration steps and known issues with the tool. You can download the tool from here.

Click here to download and read the white paper

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – Microsoft Whitepaper – Idle Connection Resiliency

Developers can write data access application that enables ideal connection resiliency with the .NET framework. An idle connection is the one that is active but it’s not executing a command or waiting for data. It is very important to understand how idle connection is reconnecting back in the .NET framework with SQL Server. This white paper actually discusses the same in very simple works. The user has to connect either to a SQL Server 2014 or Microsoft Azure, SQL Database to enable idle connection resiliency.

Here is a very interesting example in the of the idle connection resiliency provided in the Overview section of the Whitepaper.

Let’s imagine that you are a roaming worker that needs to use an Access application to connect to SQL Server.  When you need to move from meeting to meeting, you normally close your notebook’s lid in order to move.  In working online, every time this happens, you may end up disconnected either because your notebooks sleep or due to blind wireless spots in your building.  To avoid the hassle of being disconnected, you may choose to avoid certain places (like elevators, for example) and walk with your notebook’s lid open. Now, imagine if you can close your lid and walk anywhere in your building (even take the elevator) and just arrive to your next meeting, open your lid and find your work there, waiting for you to continue.  To  address this and other scenarios when an idle connection drops, SQL Server introduced a new feature called Idle Connection Resiliency.

Well, that’s it. This white paper describes the internal working of the Idle Connection Resiliency. It further discusses about the Client’s idle connection, reconnect logic, Client session state handling and replay logic, Non-recoverable session states, and General Considerations.

Click here to read the white paper on Idle Connection Resiliency.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – 4 Whitepapers on Windows Azure

Windows Azure is getting quite a lot of traction in the industry and I often get questions about if there is authentic learning on this subject. Microsoft has published following 4 white paper which covers many interesting topics related to SQL Server and Windows Azure.

Application Patterns and Development Strategies for SQL Server in Windows Azure Virtual Machines
You can develop many types of n-tier applications by separating the components of the different application tiers on different machines as well as in separate components. This article explains different application patterns to use for your SQL Server based applications in Windows Azure environment. In addition, the article discusses Windows Azure specific development strategies so that you can design your applications correctly.

Deploy SQL Server Business Intelligence in Windows Azure Virtual Machines
This article describes and walks you through the creation of a multi-server deployment of SQL Server Business Intelligence features, in a Windows Azure Virtual Machines environment. The document focuses on the use of Windows PowerShell scripts for each step of the configuration and deployment process.

How to backup and restore Windows system disk in a Windows Azure IaaS
A comparison of different methods of backup and restore in an IaaS environment.

Performance Guidance for SQL Server in Windows Azure Virtual Machines
This article discusses the key factors to consider when evaluating performance and planning a migration to SQL Server in Windows Azure Virtual Machines. It also provides certain best practices and techniques for performance tuning and troubleshooting when using SQL Server in Windows Azure Infrastructure Services.

All the taxt is from MSDN.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQLAuthority News – Whitepaper – Plan Caching and Recompilation in SQL Server 2012

Plan Caching and Recompilation in SQL Server 2012 Whitepaper has been my favorite paper for a long time. Plan caching and recompilation are two of the best concepts which are explained in depth by my favorite author Greg Low. I have met Greg several times and I have been a big fan of his writing and ability to make complex very easy. The same white paper was earlier available for SQL Server 2005 and 2008.

This paper explains how SQL Server allocates memory for plan caching, how query batches are cached and suggests best practices on maximizing reuse of cached plans. It also explains scenarios in which batches are recompiled, and gives best practices for reducing or eliminating unnecessary recompilations and for minimizing plan cache pollution. The white paper explains SQL Server’s statement-level recompilation feature and many tools and utilities that are useful as observation tools in the processes of query compilation, query recompilation, plan caching, and plan reuse. (Abstract from whitepaper).

I strongly encourage everybody to read this whitepaper if you are interested in the performance tuning as well interested in learning about the essentials of the query optimization.

Download the Plan Caching and Recompilation in SQL Server 2012 Whitepaper.

Reference: Pinal Dave (http://blog.sqlauthority.com)