SQL SERVER – White Papers – Consolidation Guidance for SQL Server – Consolidation Using SQL Server 2008

What are the considerations when creating a consolidation plan for my environment?
What are the key differentiators among the three consolidation options?
How can I use these differentiators to choose the appropriate consolidation option for my environment?

Read Consolidation Guidance for SQL Server

Many companies are considering or have already implemented consolidation of computing resources, including Microsoft SQL Server instances and databases, in their organization. A consolidation effort is a complex task that requires information, a detailed plan and timeline for success, and a strategy for administering the consolidated environment. This white paper walks through the journey of gathering and analyzing the information to base all planning and implementation decisions on; how to plan, architect, and implement consolidation; and finally, the considerations for administering a consolidated SQL Server environment.

Read Consolidation Using SQL Server 2008

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

Original Post : http://blog.sqlauthority.com/2009/12/06/sql-server-white-papers-consolidation-guidance-for-sql-server-consolidation-using-sql-server-2008/

SQLAuthority News – Notes from TechDays 2009 at Infosys, Bangalore

I recently had opportunity to attend TechDays 2009 Infosys. This event was the largest technology conference by Microsoft in Infosys. Microsoft Tech Days focused on positioning Microsoft as the company to bet on for future technology investments by businesses and consumers alike. The event was a showcase of Microsoft’s products and solutions to technologists, decision-makers, technology influencers, and analysts.

The in-campus event in Infosys was attended by 2500 tech professionals and decision makers. The event was also broadcast live to all non-Bangalore Infosys locations by using Infosys’ internal infrastructure. 2.5K Attendees I 1.2K In Person Attendees I 1K+ Online Views. Microsoft Tech Days provided the ideal platform to have targeted engagements with multiple audiences under one roof. This event also stood out for its offering of engagement opportunities for technical audience – activities ranging from sessions, connect with experts, to Demo extravaganza. The in-campus event brought the rich experience of learning the latest Microsoft technologies to tech professionals and decision-makers, at their own campus. With six tracks and 32 sessions, this event covered topics ranging from Windows 7, Cloud and VSTS 2010 to various other latest Microsoft Technologies.

I was fortunate to present two different SQL Server sessions at the location. I really enjoyed presenting at Infosys as I had a great audience that supported by wonderful participation. I had received nearly 30 questions during session, and yeah, it was fun to answer them all. Couple of questions on the subject of Business Intelligence were so advanced that I had to dig really deep in my knowledge to answer them. Vinod Kumar, the top SQL Evangelist from Microsoft, was also present in the session on Business Intelligence, and he had also answered few insider-related questions.

Both the sessions were very well received. Huge cheers to Microsoft for arranging one of the largest events at Infosys! I was also very much impressed with the campus of Infosys. I have never seen such a large and well organized campus for any software company before Infosys.

Session 1: The History of Log – Change Data Capture (CDC)

Abstract: Learn to capture the history of data using CDC. An age-old method of writing queries and triggers to capture change in database table is replaced with a considerably powerful asynchronous method of change data capture (CDC). All attendees will learn how to configure CDC in less than 60 seconds.

If you are interested to read more about CDC, please download CDC script from here.

Session 2: PowerPivot Self-Service Business Intelligence in Excel 2010

Abstract: You most likely have already heard of PowerPivot, the ground-breaking new BI technology shipping in Microsoft SQL Server 2008 R2. In this session, we introduce PowerPivot for both analysts and IT in the context of Self Service BI. We look at the client capabilities of PowerPivot for Microsoft Office Excel power users, the collaboration features for teams, and the important IT tools for compliance and effective administration.

Session of Vinod Kumar

The main highlight of TechDays was the session by Vinod Kumar. This session had the largest number of attendees in the entire event. He talked about the subject “Locking and Blocking with SQL Server”, which is not a very new subject, but Vinod talked on the same subject with a fresh prospective and with lots of new examples. In fact, the attendees were fully absorbed the session and they continued their involvement even after the session was over. Vinod was awesome as usual. I really learned a lot from Vinod during the quality time spent at the event.

I also had pleasure to meet Phaneendra Babu Subnivis, a very keen reader of this blog and SQL Expert from Infosys.

A great piece of advice that I had received from Vinod during the event:

“Build a credibility in such a way that attendees has expectation from the speaker for something new and interesting.”

I end my notes on this event with this wonderful piece of advice. This was indeed one of the most interesting community events that I had been involved with in recent times.

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

Original Post : http://blog.sqlauthority.com/2009/12/05/sqlauthority-news-notes-from-techdays-2009-at-infosys-bangalore/

SQL SERVER – 2008 Star Join Query Optimization

Business Intelligence (BI) plays a significant role in businesses nowadays. Moreover, the databases that deal with the queries related to BI are presently facing an increase in workload. At present, when queries are sent to very large databases, millions of rows are returned. Also the users have to go through extended query response times when joining multiple tables are involved with such queries. ‘Star Join Query Optimization’ is a new feature of SQL Server 2008 Enterprise Edition. This mechanism uses bitmap filtering for improving the performance of some types of queries by the effective retrieval of rows from fact tables.

Improved Query Response Times
In general, data warehouses employ dimensionally modeled star or snowflake schemas. These schemas have one or more than one fact tables that contain transactional data and many dimension tables, which holds information such as product data, customer information, and times and dates – all these define the fact table data. Usually, foreign keys are employed for maintaining relationships between the rows in fact tables and also between the rows in the dimension tables. Databases that contain star schemas are recognized by SQL Server 2008 Enterprise. It uses the new Star Join Query logic for processing queries against such star schemas more efficiently. Typically, on an average, data warehouse queries run faster to approximately 20 percent.

Automatically Implemented
Star Join Query Optimization is automatically implemented by the SQL Server. It does not require a special database or application configuration. The query processor will usually optimize queries with medium selectivity (this refers to the queries that retrieve approximately 10% to 75% of rows from a fact table). Such queries are usually handled using hash joins to join the dimension and fact tables by employing the foreign keys to identify the matching rows. A hash table is built for each dimension table referenced in the query in the case of hash joins; the optimization process uses these hash tables for deriving bitmap filters. The key values from each dimension table are identified by bitmap filters; these key values qualify for inclusion in the query. When the fact table is scanned, the bitmap filters are applied to it. These bitmap filters eliminate those rows of the fact table which are not qualified for inclusion in the result set. The most selective bitmap filter is applied first as it is found to eliminate the highest number of rows. Since the eliminated rows do not need further processing, the subsequent filters need not be applied to them – this way the process becomes more efficient.

Query Selectivity
The performance is enhanced in the case of medium selectivity queries while using bitmap filtering because the rows are filtered before any joins are implemented. Hence, there is a decrease in the number of rows that are processed by each join. Bitmap filtering is not applied when queries are highly selective (i.e., those queries that return less than 10% of the rows in a fact table). In such case, a nested loop join is found to be generally more efficient. Similarly, when the queries are not very selective at all (queries which return more than 75% of the rows in a fact table), bitmap filtering is not applied as there are very few rows to be filtered, and hence, there is no requirement of enhancement in performance in this case.

Integer Data Types
Star join optimization is found to give the highest efficiency when the data type of the columns used in the joins is integer. This feature enables the bitmap filter to be applied as part of the initial table or index scan rather than being used at a later stage in the query plan. Most of the queries are benefited from star join optimization since foreign key relationships are commonly implemented using integer-based alternate key columns.

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

Original Post : http://blog.sqlauthority.com/2009/12/04/sql-server-2008-star-join-query-optimization/

SQLAuthority News – Airline Review – Paramount, Kingfisher, Go Air, Indigo, Jet Airways, Indian Airlines, Spicejet

First of all, this is a totally different article that I have ever written on this site. As the regular readers of my blog are aware that I am always traveling due to my different assignments at work. In last two months, I have been on flight for 36 times; this makes me a regular air traveler, who travels almost every other day. For instance, considering a month of 24 days (excluding the weekends), for two months, there are 48 business days. In such case, I was almost on air always!

There are many airlines in India, and I have traveled in almost all of them. I do not want to write an in-depth review on the basis of my personal experience, but there are a few generic points that I would like to point out. I will list all the airlines on the basis of my preference.

Paramount Airlines (#1 – The Best)
Pros – All seats business class, 3-course food service, great attitude, extremely comfortable seats
Cons – Does not have North Indian connections

Kingfisher (#2)
Pros – 5 star service, good food
Cons – Quite occasionally late or cancelled

Go Air (#3)
Pros – Always on time, good food, economical rates
Cons – Not as many connections as some other airlines

Indigo (#4)
Pros – Always on time, good food
Cons – Charges extra for few windows seats and front rows

Jet Airways/Indian Airlines (#5)
Pros – Connects all over India, good food
Cons – Often canceled flights, management issues

Spicejet (#6)
Pros – Many direct connections all over India, economical rates
Cons – Worst food, unfriendly staff, receipt of purchase not available, frequent canceled flights

Overall I rate Paramount and Kingfisher very high. Spicejet definitely needs improvement for sure.

Let me know your opinion, and if you agree or disagree with me. Again, there is no intention to start any intense debate here. I just wanted to share my experience.

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

Original Post : http://blog.sqlauthority.com/2009/12/03/sqlauthority-news-airline-review-paramount-kingfisher-go-air-indigo-jet-airways-indian-airlines-spicejet/

SQL SERVER – Validate an XML document in TSQL using XSD by Jacob Sebastian

Following article is sent by SQL Server MVP Jacob Sebastian on request to provide solution for validating XML document.

XML Schema Collections

Because of the ‘eXtensible’ nature of XML (eXtensible Markup Language), often there is a requirement to restrict and validate the content of an XML document to a pre-defined structure and values. XSD (XML Schema Definition Language) is the W3C recommended language for describing and validating XML documents. SQL Server implements XSD as XML Schema Collections.

An XML Schema Collection can be used to validate an XML document stored in an XML column or variable. To understand this better, let us see an example.

XML Document

For the purpose of this example, let us create an XML Schema Collection to validate the following XML Document.

Jacob V Sebastian

Validation Rules

Here are the validation rules we are trying to implement.

1. The root element should be named ‘Employee’
2. The root element should have three child elements, named ‘FirstName’, ‘MiddleName’ and ‘LastName’.
3. Child elements should appear exactly in the order given above.

Creating the XML Schema Collection

Here is the XML Schema collection that performs the above validations.

CREATE XML SCHEMA COLLECTION EmployeeSchema AS' '

Validating an XML variable

The following code validates an XML document against the rules defined in the XML Schema Collection.

DECLARE @x XML(EmployeeSchema) SELECT @x = ' Jacob V Sebastian '

Note that the operation will succeed only if all the validation rules defined in the XML schema collection are met. For example, the following will fail because the XML document is missing the ‘MiddleName’ element.

DECLARE @x XML(EmployeeSchema) SELECT @x = ' Jacob Sebastian '

Please read further details on the same subject over SyntaxHelp XML.

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

Original Post : http://blog.sqlauthority.com/2009/12/02/sql-server-validate-an-xml-document-in-tsql-using-xsd-by-jacob-sebastian/

SQLAuthority News – A Daily Doze of Technology – Alvin Ashcraft’s Morning Dew

A common question that I receive is regarding how I keep myself updated with latest information about technology and what is going on at present. I read lots of blogs and books. I am usually traveling 4 days in my any regular work week. I read physical books at the time. I prefer to read the books in hard copy and not on the computer screen. If you ever spot me reading books, quite often you can see me with a fiction book rather than a SQL Book.

Ok… So the question is what do I read to keep myself updated with latest knowledge? Well, the answer is very simple; I read the daily post by Alvin Ashcraft. Everyday, he makes a list of the best of the best posts of different technologies and lists them category-wise in a single post. You might have came across many technology bloggers like him earlier but just like any other regular blogs, many have either stopped blogging and some stop updating their blogs at all. Well Alvin has been blogging for long time and has been consistently updating on different technologies. The quality of the links Alvin has been including is just getting better day by day.

I must admit that I have been addicted to his blog from long time. Every evening (Indian time), I wait for his blog to be updated; this way, I can read what has happened in the previous day. Alvin is from Philadelphia – also a husband, dad, atheist and geek – and has about 14 years of experience with software development.

Read Alvin Ashcraft’s Morning Dew. No matter when you are reading this post, I am sure that visiting his blog will bring up the latest news updates from technology.

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

Original Post : http://blog.sqlauthority.com/2009/12/01/sqlauthority-news-a-daily-doze-of-technology-alvin-ashcrafts-morning-dew/

SQL SERVER – Size of Index Table for Each Index – Solution

Earlier I have posted small question on this blog and requested help from readers to participate here and provide solution. Please read the original Puzzle here.

SQL SERVER – Size of Index Table – A Puzzle to Find Index Size for Each Index on Table

The puzzle was to write a query that will return the size for each index that is on any particular table. We need a query that will return an additional column in the above listed query and it should contain the size of the index.

So far I have found two potential solutions. I have done some good amount of testing of both the script and they both are really giving accurate results 99.9% of the time. There were couple of instances where it provided incorrect results but for the most of the time, it just worked like charm. Again, it may be a bit of rounding up logic and I will not hold it against the script.

I want to thank Gaurav Sharama and Adam Hutson for their participation.

Solution from Gaurav Sharma

DECLARE @OBJECT_NAME VARCHAR(255) = 'HumanResources.Shift';
DECLARE @temp TABLE
(
indexID BIGINT,
objectId BIGINT,
index_name NVARCHAR(MAX),
used_page_count BIGINT,
pages BIGINT
)
--Insert into temp table
INSERT INTO @temp
SELECT
P.index_id,
P.OBJECT_ID ,
I.name,
SUM (used_page_count),
SUM (
CASE
WHEN (p.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE lob_used_page_count + row_overflow_used_page_count
END
)
FROM sys.dm_db_partition_stats P INNER JOIN sys.indexes I ON I.index_id = P.index_id AND I.OBJECT_ID = P.OBJECT_ID
WHERE p.OBJECT_ID = OBJECT_ID(@OBJECT_NAME)
GROUP BY P.index_id, I.Name, P.OBJECT_ID;
SELECT index_name INDEX_NAME,
LTRIM (STR ((CASE WHEN used_page_count > pages THEN (used_page_count - pages) ELSE 0 END) * 8, 15, 0) + ' KB') INDEX_SIZE
FROM @temp T
GO

Solution from Adam Hutson

DECLARE
@objname NVARCHAR(776),
@id INT,
@dbname sysname
SELECT
@objname = 'HumanResources.Shift',
@dbname = ISNULL(PARSENAME(@objname, 3),DB_NAME()),
@id = OBJECT_ID(@objname)
SELECT
i.*,
CASE
WHEN ps.usedpages > ps.pages THEN (ps.usedpages - ps.pages)
ELSE 0
END * 8 indexsize
FROM sys.indexes i
INNER JOIN (
SELECT
OBJECT_ID,
index_id,
SUM (used_page_count) usedpages,
SUM (
CASE
WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE lob_used_page_count + row_overflow_used_page_count
END
)pages
FROM sys.dm_db_partition_stats
WHERE OBJECT_ID = @id
GROUP BY OBJECT_ID, index_id
) ps ON i.index_id = ps.index_id
WHERE i.OBJECT_ID = @id
GO

If you have different script or have suggestions on this script, please let us know.

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

Original Post : http://blog.sqlauthority.com/2009/11/30/sql-server-size-of-index-table-for-each-index-solution/

SQL SERVER – Azure Start Guide – Step by Step Installation Guide

As SQL Azure CTP is released I have included here step by step guide for how to configure the CTP.

For pricing and introduction please read SQLAuthority News – SQL Azure – Microsoft SQL Data Services – Introduction and Pricing

First it has to be configured online at https://sql.azure.com/

Login using your Live ID

Type in invitation code received from Microsoft for CTP. You can request one for your self here.

Accept the TOU.

Once logged it you will have to create server username and password.

Click on my project and it will provide you details about your servername where your data is going to be stored in Azure.

Currently default database is master database. Click on ‘Create Database’ button to create new user defined database.

After please switch to Firewall Settings tab and click on checkbox “Allow Microsoft Services Access to this server”.

Select the first rule and click on “Edit Rule”. Che the value of to IP range to either your static IP only for security purpose. If due to any reason, you want to leave it open so you can connect to your Azure from any where keep the From IP to 0.0.0.0 and change the to IP to 255.255.255.255.

After the change the settings should look like following image.

Now the next step is to connect to SQL Azure from SQL Server Management Studio. When you open SQL Server Management Studio, it opens up the connection window. CLOSE IT!

Now click on New Query.

Once click on new query enter username and password as well the servername noted earlier and click on connect. It should connect fine and new query window with master as default database should be displayed. You can start writing any query in New Query Window and result will be visible in Result Window.

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

Original Post : http://blog.sqlauthority.com/2009/11/29/sql-server-azure-start-guide-step-by-step-installation-guide/

SQLAuthority News – SQL Server R2 Resources Downloads, Documentations

Microsoft SQL Server 2008 R2 November Community Technology Preview
Building on SQL Server 2008, R2 provides an even more scalable data platform with comprehensive tools for managing your databases and applications, improving the quality of your data, and empowering your users to build rich analyses and reports using tools they are already familiar with.

Microsoft SQL Server 2008 R2 November Community Technology Preview Feature Pack
The Microsoft SQL Server 2008 R2 Feature Pack is a collection of stand-alone packages which provide additional value for SQL Server 2008 R2.

SQL Server 2008 R2 Books Online Community Technology Preview November 2009
Download the a community technology preview (CTP) release version of the documentation and tutorials for Microsoft SQL Server 2008 R2.

Microsoft SQL Server 2008 R2 November Community Technology Preview – Express Edition
Microsoft SQL Server 2008 R2 Express is a powerful and reliable data management system that delivers a rich set of features, data protection, and performance for embedded application clients, light Web applications, and local data stores.

SQL Server 2008 R2 November CTP Reporting Services Add-in for Microsoft SharePoint Technologies 2010
The Microsoft® SQL Server 2008 R2 November CTP Reporting Services Add-in for Microsoft SharePoint Technologies allows you to integrate your reporting environment with SharePoint to experience the benefits of using the collaborative environment provided by SharePoint.

Microsoft SQL Server 2008 R2 November CTP StreamInsight
Microsoft® SQL Server® StreamInsight is Microsoft’s new Complex Event Processing technology to help businesses derive better insights by correlating event streams from multiple sources with near-zero latency.

Microsoft SQL Server 2008 R2 November CTP Report Builder 3.0
Microsoft SQL Server 2008 R2 Report Builder 3.0 provides an intuitive report authoring environment for business and power users. It supports the full capabilities of SQL Server 2008 R2 Reporting Services. The download provides a stand-alone installer for Report Builder 3.0.

Microsoft SQL Server 2008 R2 – PowerPivot for Microsoft® Excel 2010 – November Community Technology Preview
Microsoft® PowerPivot for Microsoft Excel 2010 provides ground-breaking technology, such as fast manipulation on large data sets (often in millions of rows), streamlined integration of data, and the ability to effortlessly share your analysis through Microsoft SharePoint 2010.

What’s New in SQL Server 2008 R2 November Community Technology Preview (CTP)
This document describes what’s new in the November CTP of Microsoft SQL Server 2008 R2. Analysis Services, Database Engine, Reporting Services, and Service Broker all have new or enhanced features in this release. Master Data Services is a new component in this release.

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

Original Post : http://blog.sqlauthority.com/2009/11/28/sqlauthority-news-sql-server-r2-resources-downloads-documentations/

SQLAuthority News – Subscribe to Blog – Search a Blog

Quite often I get request if I send blog post in newsletter or through email. Here are few important links.

You can for sure get email of my post, however, I strongly suggest to visit blog as if there are any updates in my post they are reflected on blog.

Subscribe to blog post through email

Subscribe SQLAuthority Feed

Search SQLAuthority – This is very powerful search. Give it a try.

Follow me on Twitter

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

Original Post : http://blog.sqlauthority.com/2009/11/27/sqlauthority-news-subscribe-to-blog-search-a-blog/