SQL Server 2012 - Plan Guides

You will be wondering what is this new item called "Plan Guides" in Programmability section of a database in SSMS 2012 as shown below:

SQL Server 2012 comes with many features, Plan Guides is one among them.

Plan Guides will let you optimize the performance of the query without modifying the actual query. In most of the real time scenarios the queries might be embedded in the application code itself and we will not have flexibility to modify/optimize the query within the application, this feature definitely helpful in these kind of scenarios.

There are three types of Plan Guides:
SQL Plan Guide
     This matches the queries which are executed in standalone statements.

Object Plan Guide
     This matches the queries which are executed within the contest of User Defined Functions, Stored Procedures and Triggers.
 
Template Plan Guide
     This matches the standalone queries which can be parametrized to a specified form.

Note that the Plan Guide can be created either by using Management Studio or Transact SQL. I will Transact SQL for all the examples.

Lets take an example to see how SQL Plan Guide works.Let starts with exploring the sp_create_plan_guide procedure.

sp_create_plan_guide procedure is used to create a plan guide for associating the hints to the queries. This built in procedure takes following parameters:

@name -> To provide the name for the Plan Guide
@stmt -> SQL Query Statement where you want to associate the hints
@type -> To Specify the type of the Plan Guide (SQL | Object| Template)
@module_or_batch -> To Specify the object name from the SQL Statement
@params -> To specify query parameters (it is must in case of Type is Template)
@hints -> To specify the hint Option

Now lets create a table and load with dummy data using the following script:

Create Table dbo.Employee (     Empno int Identity(1,1) Primary Key,     EmpName varchar(200) Not Null,     DOB DateTime Not Null,     Salary float ) -- Load Dummy Data for Testing INSERT INTO dbo.Employee (EmpName, DOB, Salary) SELECT TOP 10000                    Enames.FullName,      CAST(RAND(CHECKSUM(NEWID()))*3653.0+CAST(CAST('01-Jan-1950' as DateTime) - CAST('01-Jan-1900' as DateTime) AS INT) AS DATETIME) AS Date,                     CAST(RAND(CHECKSUM(NEWID())) * 10000 as Float) FROM sys.all_columns T1          CROSS JOIN sys.all_columns T2 CROSS APPLY (     SELECT EName AS 'data()'     FROM (         SELECT TOP (CAST(RAND(CHECKSUM(NEWID())) * T1.column_id * T2.column_id as INT) %2 + 1) EName         FROM (         SELECT EName = CAST('Sridhar ' AS VARCHAR(200)) UNION ALL         SELECT 'Kolapalli ' UNION ALL         SELECT 'David ' UNION ALL         SELECT 'Robert ' UNION ALL         SELECT 'Rajesh ' UNION ALL         SELECT 'Naresh ' UNION ALL         SELECT 'Babu ' UNION ALL         SELECT 'Thomas ' UNION ALL         SELECT 'Rich ' UNION ALL         SELECT 'Anthony' UNION ALL         SELECT 'Richard'         ) ENames ORDER BY NEWID()) u2     FOR XML PATH('')         ) Enames(FullName) -- Create a Index on Employee Name Create Index Indx_Emp_Name on dbo.Employee(EmpName)

Lets execute a sample query and see how is the execution plan for the same query:

SELECT count(*) from dbo.Employee E1 INNER JOIN dbo.Employee E2 on E1.EmpName = E2.EmpName

If you closely observe the execution plan for this query, it uses Merge Join.

Now lets see how we can use the Plan Guides to add the hint to the query without modifying the underlying query. Lets create the plan guide with the following TSQL snippet:

EXEC sp_create_plan_guide     @name =  N'SriSampleGuide1',     @stmt = N'SELECT count(*) from dbo.Employee E1 INNER JOIN dbo.Employee E2 on E1.EmpName = E2.EmpName',     @type = N'SQL',     @module_or_batch = NULL,     @params = NULL,     @hints = N'OPTION (TABLE HINT( E1, FORCESEEK))'

This plan guides adds table hint to the query statement mentioned as one of the parameter. Now let try executing the same and see the execution plan, it uses the Table Hint provided in the plan guide.

SELECT count(*) from dbo.Employee E1 INNER JOIN dbo.Employee E2 on E1.EmpName = E2.EmpName

The execution plan for this query looks as:

Note that this sample is only to show how we leverage use plan guides.

Plan Guide is one of the good feature in SQL Server 2012 which helps in optimizing the query without modifying the underlying query. I am sure every application developer appreciate this feature.

Note that creating plan guide either on Object/SQL/template removes the query plan from the plan cache which might impact the overall query performance and also the performance depends on the plan guide too. I will suggest to use plan guide cautiously.

SQL Server 2012 - FileStream Enhancements

This post explains the FileStream Concept and its Enhancements in SQL Server 2012 with an example. At a high level this post covers following topics:

• What is FileStream
• How to Configure FileStream
• An Example to leverage FileStream
• FileStream Enhancements in SQL Server 2012

First we will look at what is FileStream. In Short FileStream is the ability to store unstructured data like documents, images on the file system but still be managed by SQL server, which means these files are part of Database Managment activities like Backup & Restore.

By Default FileStream is Disabled for database server instance. This feature can be configured during the SQL Server Installation or even post Installation. Following steps explain on how we can enable & configure FileStream.

Open SQL Server Configuration Manager by Clicking Start -> All Programs -> Microsoft SQL Server 2012 ->  Configuration Tools Folder -> SQL Server Configuration Manager

In the SQL Server Configuration Manager console highlight the SQL Server Services as shown in the picture

Select the SQL Server instances in the right pane for which you want to enable FILESTREAM, Right click on the server and select properties as shown in the picture:

In the property window select FileStream Tab as shown below

Check the FILESTREAM For Transact-SQL Access to enable FileStream and then select Enable FILESTREAM For File I/O Access so that we can read and write data from Windows. And also type the name of the Windows share name "FileStreamShare" or you can even leave it to default value as shown below

If there is a need for the remote clients to access the data then we need to enable Allow Remote Clients Access To FILESTREAM Data otherwise leave it to default. Now go ahead click Apply followed by OK button.

With this we completed enabling FileStream on a SQL Server Instance. Now we will see in the following steps on we can enable this feature for a particular database.

Let us start with creating a Database called "FileStreamSample" using following script which is generated in SSMS (SQL Server Management Studio).

To execute the script, Open SSMS by Clicking Start -> All Programs -> Microsoft SQL Server 2012 -> SQL Server Management Studio and then copy & paste following code. Now execute the code either by pressing F5 key in the keyboard or by clicking on execute icon.

CREATE DATABASE [FileStreamSample]  CONTAINMENT = NONE  ON  PRIMARY ( NAME = N'FileStreamSample', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FileStreamSample.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )  LOG ON ( NAME = N'FileStreamSample_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\FileStreamSample_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%) GO ALTER DATABASE [FileStreamSample] SET COMPATIBILITY_LEVEL = 110 GO ALTER DATABASE [FileStreamSample] SET ANSI_NULL_DEFAULT OFF GO ALTER DATABASE [FileStreamSample] SET ANSI_NULLS OFF GO ALTER DATABASE [FileStreamSample] SET ANSI_PADDING OFF GO ALTER DATABASE [FileStreamSample] SET ANSI_WARNINGS OFF GO ALTER DATABASE [FileStreamSample] SET ARITHABORT OFF GO ALTER DATABASE [FileStreamSample] SET AUTO_CLOSE OFF GO ALTER DATABASE [FileStreamSample] SET AUTO_CREATE_STATISTICS ON GO ALTER DATABASE [FileStreamSample] SET AUTO_SHRINK OFF GO ALTER DATABASE [FileStreamSample] SET AUTO_UPDATE_STATISTICS ON GO ALTER DATABASE [FileStreamSample] SET CURSOR_CLOSE_ON_COMMIT OFF GO ALTER DATABASE [FileStreamSample] SET CURSOR_DEFAULT  GLOBAL GO ALTER DATABASE [FileStreamSample] SET CONCAT_NULL_YIELDS_NULL OFF GO ALTER DATABASE [FileStreamSample] SET NUMERIC_ROUNDABORT OFF GO ALTER DATABASE [FileStreamSample] SET QUOTED_IDENTIFIER OFF GO ALTER DATABASE [FileStreamSample] SET RECURSIVE_TRIGGERS OFF GO ALTER DATABASE [FileStreamSample] SET  DISABLE_BROKER GO ALTER DATABASE [FileStreamSample] SET AUTO_UPDATE_STATISTICS_ASYNC OFF GO ALTER DATABASE [FileStreamSample] SET DATE_CORRELATION_OPTIMIZATION OFF GO ALTER DATABASE [FileStreamSample] SET PARAMETERIZATION SIMPLE GO ALTER DATABASE [FileStreamSample] SET READ_COMMITTED_SNAPSHOT OFF GO ALTER DATABASE [FileStreamSample] SET  READ_WRITE GO ALTER DATABASE [FileStreamSample] SET RECOVERY FULL GO ALTER DATABASE [FileStreamSample] SET  MULTI_USER GO ALTER DATABASE [FileStreamSample] SET PAGE_VERIFY CHECKSUM  GO ALTER DATABASE [FileStreamSample] SET TARGET_RECOVERY_TIME = 0 SECONDS GO USE [FileStreamSample] GO IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [FileStreamSample] MODIFY FILEGROUP [PRIMARY] DEFAULT GO

Once Database is created, Select the database and click on "New Query" Icon as shown below:

Now we have to configure the file stream access level for the database "FileStreamSample" by executing the following Transact-SQL code.

EXEC sp_configure filestream_access_level, 2 RECONFIGURE

There are three access level options for FileStream as described below:

0 -> No FileStream Support
1 -> Enable FileStream for Transact-SQL access only
2 -> Enable FileStream for Transact-SQL as well as Win32 Streaming access

In our example we are going with the last option as to provide support for Win32 Streaming.

Now In Query Editor, type the above Transact-SQL code and then click on Execute as shown below:

The final step is to configuring File Group & Database Files to store FileStream Data. Execute the following commands in the Query Editor:

Use Master ALTER DATABASE [FileStreamSample] ADD FILEGROUP [FileStreamSampleFG] CONTAINS FILESTREAM GO ALTER DATABASE [FileStreamSample] ADD FILE ( NAME = N'FileStreamSample_FilestreamFile', FILENAME = N'C:\FileStreamSample_FilestreamFile' ) TO FILEGROUP [FileStreamSampleFG] GO

We can also create the File Group and Files using Managment Studio Interface itself but its always easy to go with Transact-SQL Script.

Now lets create a table using following SQL Script:

Create Table [FSExample] (     [fid] uniqueidentifier ROWGUIDCOL  NOT NULL Unique,     [sid] [int] NOT NULL Unique,     [fcontent] [varbinary](max) FileStream NULL )

There are certain point we need to keep in mind while designing tables containing FileStream. Here are few important point:

1. FileStream columns must be accompanied by a corresponding uniqueidentifier ROWGUID column.
2. These kind of table must also be accompanied by a unique index
3. ROWGUIDCOL is required if we need to access FileStream data using Win32 APIs
4. FileStream File Groups and Containers should reside on volumes other than the OS

All the DML operations are pretty much same as any other table. Following are some of the examples:

Insert Into FSExample values (newid(), 1, CAST('my first content' as varbinary(max))) Select * from FSExample Update FSExample     Set fcontent = CAST('Updated content' as varbinary(max)) WHERE sid = 1 Select * from FSExample Delete FROM FSExample WHERE sid=1 Select * from FSExample

While executing these commands keep an eye on folder "C:\FileStreamSample_FilestreamFile", you will notice news files after insert operations.

FileStream Enhancements in SQL Server 2012

There are many enhancements to FileStream in SQL Server 2012. Some of them are:

Ability to support multiple containers & file groups
Ability to specify the max size for the containers
Ability to add multiple storage devices for scalability

You can refer to my previous post on GC related improvements @ SQL Server FileStream GC Made Easy

SharePoint 2013 - Key Changes in Service Applications

There are few key changes to Service Applications with SharePoint 2013. Lets see what are these changes:

Web Analytics Service Application
      
       Web Analytics Service Application is introduced in SharePoint 2010 and it is no more available as a Service Application in SharePoint 2013. It is fully integrated with Search Service Application.

Office Web Application

      Office Web Application is also introduced with SharePoint 2010 and it is no longer a Service Application in SharePoint 2013 and it is not even comes as a Separate Installable for SharePoint.

App Management Service Application

      App Management Service Application is a new feature in SharePoint 2013, this feature is very similar to Mobile App Store/Market place. This feature allows you to purchase an SharePoint Applications directly  from Microsoft Market Place and the licensing for these Applications are managed by the App Management Service.

Machine Translation Service Application

      Machine Translation Service is a new service application introduced with SharePoint 2013 Preview. This Service App provides automatic machine translation of files or all the files within a Document Library or even Site. When the Machine Translation Service application processes a translation request, it forwards the request to a cloud-hosted machine translation service, where the actual translation work is performed.
The Machine Translation Service application processes translation requests asynchronously and synchronously. Asynchronous translation requests are processed when the translation timer job executes.

Machine Translation Service also includes a client object model (CSOM) that enables access to the Machine Translation Service API for online, on-premises, and mobile development.

More details about this Service App is available at MSDN Article.

Work Management Service Application

     The Work Management Service Application provides functionality to aggregate tasks to a central location.
    - Users get the possibility to view and track their to-dos and tasks.
    - Tasks can be cached to a users personal site.
    - Tasks can be aggregated from Exchange, Project Server and SharePoint.

SQL Server FileStream GC Made Easy

In this post I want to explain FileStream System Procedures added in SQL Server 2012. Before jumping into this topic we will spend couple of mins to understand what is FileStream and the issues we might encounter with SQL Server 2008 R2 version?

FileStream is the ability to store unstructured data like documents, images on the file system but still be managed by SQL server.
Here are some of the advantages:

• Store and retrieve BLOBs together with relational data in a single data store
• Unstructured Data is included in backup and restore operations 
• Crud operations on BLOBs and relation database occurs on the same DB Transactions
• NTFS File stytem is faster compared to SQL Server especially with the File Operations
• No limit on the file size but the applications can enforce this limit
• .Net APIs like SQLFileStream can be leveraged to interact with FileStream Objects

By default this feature is disabled and can be enabled either by using SQL Statements or by Management Studio. I guess I am going out of this topic, lets come back.

I am sure who ever leveraged FileStream feature in SQL Server 2008 or 2008 R2 might faced issues or might be looking for a better way for initiating Garbage Collection. There is an interesting blog on FileStream Garbage Collection and how it works. 

With SQL Server 2012 we have new set of procedures which helps us to initiate the FileStream Garbage Collection:

sp_filestream_force_garbage_collection:
        - This system procedure forces the garbage collector to run and delete unneeded files. The FileStream garbage collector runs automatically but if you want to call explicitly you can do it by using this procedure.

sp_kill_filestream_non_transacted_handles:
        - This system procedure helps you to close the non transaction FileStream handlers.

These two System Procedures are very useful for any Database team leveraging FileStream Feature.

Visual Representation of SQL Joins

When I am browsing through net today, I found an interesting article on Visual Representation of SQL Joins. I always draw this image in most of my SQL Trainings to explain Joins. I am so surprised to see this image :)

Venn Diagrams are the easiest way to explain different types of joins like Inner, Left, Right and so on.

It also helps us to easily understand the Set Operations like Union, Intersect and Minus in any Relational Database Queries.

SharePoint Server 2013 Installation and Configuration

In this article I will show how to install & configure SharePoint 2013 Preview.

We can install SharePoint either on a Physical Server or an Virtual Box, In either of the case we have to make sure at least we have minimum Hardware & Software needs for SP 2013 as provided in the technet article.

In this article I am going to explain on how we can create a SharePoint 2013 Virtual Image for development/evaluation purpose only. We need the following software's to setup:

1. Oracle Virtual Box or VM Player
2. Windows Server 2012 Preview Download
3. SQL Server 2012 Enterprise Edition Download
4. SharePoint 2013 Preview Download

While I am writing this blog on how I setup my virtual server using Oracle Virtual Box, I found another blog "SharePoint 2013 Development Image" where the blogger explained in much granular level. I will suggest to refer to this on how to setup SharePoint 2013.

I should have found this blog even before I capture the images during my setup which might saved lots of time for me :)

The only difference I did during my SharePoint setup is installing Pres-Requisites manually one by one as my environment is in Isolated network with no access to Internet (for some security reasons). I might have done this though the command prompt.

Following are some of the screen shots:

1.SharePoint 2013 Central Administration Screen Shot

2. SharePoint 2013 Site Collection Screen Shot

I found one interesting spelling mistake in the setting page as shown below:

Once I evaluate certain feature, I will share it in this blog.

Windows Server 2012 - A Quick View

When I am evaluating windows 8 especially its Metro UI, I was wondering how will be the Windows Server UI looks like. Recently I get a chance to Setup Windows Server 2012 Release Candidate for evaluating SQL Server 2012 features and SharePoint 2013 features.

Here are the some of the screen shots on how Windows Server 2012 looks, A complete different experience compared to previous editions.

1. Login Screen

2. Landing Screen 

3. After installing other software's likeSQL Server 2012 & SharePoint 2013, you are going to see many more tiles for easy access as shown in the following image:

 4. Server Manager Screen - Now it helps to do most of the admin operations from here itself.

I liked the way Microsoft moving towards Metro UI for all their Apps including Windows Server!