This is in continuation to yesterday’s post on T-SQL worst practices that affect the accuracy of the results returned/processed

What have I done? T-SQL Worst Practices

I am attempting to summarize all of the worst T-SQL practices that Jacob demonstrated at Tech-Ed 2011 (India). The summarization has been done after classifying these practices into 3 major buckets:

1. Practices that can affect accuracy of data returned/processed (Part 01 – read here)
2. Practices that can adversely impact performance (Part 02 – today’s post)
3. Practices that can have unpredictable execution side-effects (Part 03 – coming tomorrow!)

While the points covered are exactly the same as covered during the Tech-Ed, I have taken hints from Jacob’s queries and have come up with my own versions using the AdventureWorks2008R2 database supplied as a sample database for SQL 11 (“Denali”) CTP01. In cases where I am directly using Jacob’s scripts, I have mentioned it as such.

Part 02 – Practices affecting performance

Using Incorrect data types in a query/Too much reliance on implicit conversions

When I was a little kid in school, my parents used to tell me that there is a lot more than what meets the eye. A lot of things happen in the background before anything reaches us, and sometimes we forget the importance and underestimate the impact of these background processes. Years later, the movie “Munnabhai M.B.B.S.” came along, and that demonstrated, amongst many others, the same concept – how we tend to ignore the cleaning crew who help maintain one of the most important things in a hospital – hygiene.

Anyway, coming over to SQL Server, one of the most taken-for-granted features is implicit conversion. Implicit conversion is the conversion of one data-type to another without the user knowing that such a conversion is happening. Please note that these conversions happen under some pre-defined rules of  data-type precedence. Data types of a lower precedence are implicitly converted to a higher precedence if a data-type mismatch is found.

With this background in mind, let’s see how this feature can land us in performance trouble.

Assume that we have the following table:

/*-----------------------------------------------------------------------------
  Date			: 23 March 2011
  SQL Version           : SQL Server 2005/2008/Denali
  Author		: Jacob Sebastian
  Email			: jacob@beyondrelational.com
  Twitter		: @jacobsebastian
  Blog			: http://beyondrelational.com/blogs/jacob
  Website		: http://beyondrelational.com

  Summary:
  This script is part of the demo presented by Jacob Sebastian during 
  Tech-ED India 2011 on "TSQL Worst Practices".

  Disclaimer:  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
-----------------------------------------------------------------------------*/

USE Teched 
GO

IF OBJECT_ID('orders', 'U') IS NOT NULL BEGIN
	DROP TABLE orders 
END
GO

CREATE TABLE orders (OrderID INT IDENTITY, OrderDate DATETIME, Amount MONEY, Refno VARCHAR(20))
INSERT INTO orders (OrderDate, Amount, Refno) 
SELECT TOP 100000
	DATEADD(minute, ABS(a.object_id % 50000 ), CAST('2010-02-01' AS DATETIME)),
	ABS(a.object_id % 10),
	CAST(ABS(a.object_id) AS VARCHAR)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
INSERT INTO orders (OrderDate, Amount, Refno)  SELECT GETDATE(), 100, '555'

Now, let’s create a non-clustered index on the RefNo column.

CREATE NONCLUSTERED INDEX idx_refno ON orders(refno)
include(amount)

Next, let’s run through the following queries. Both these queries perform the same function, i.e. select a record whose reference number is 555.

SELECT count(*) 
FROM orders 
WHERE Refno = 555

SELECT COUNT(*) 
FROM orders 
WHERE Refno = '555'

These queries do their work right, but one of them is not advisable for production-class usage.

Here’s why: If the actual execution plan of these queries is enabled by pressing Ctrl+M or by going to Query –> Include Actual Execution plan, we see that Query #1 does an index scan instead of an index seek.

The reason is that in the first query, Refno, which is a VARCHAR column is being compared with an integer value. Per rules of data-type precedence (discussed earlier), VARCHAR values need to be implicitly converted to INT because INT has a higher precedence. Thus, SQL Server first has to create a temporary column with the INT representation of each of the Refno values, and then compare that with the supplied criteria.

In the second case, we are supplying SQL Server with the data in character format, and hence, no implicit conversion is required. SQL Server can therefore simply do an index seek to fetch the right results.

Now, let’s do it the other way around. Let’s explicitly convert Refno to an INT and then perform similar SELECT operations.

DROP INDEX idx_refno ON Orders

ALTER TABLE Orders ALTER COLUMN Refno INT

CREATE NONCLUSTERED INDEX idx_refno ON orders(refno)
include(amount)


SELECT count(*) 
FROM orders 
WHERE Refno = 555

SELECT COUNT(*) 
FROM orders 
WHERE Refno = '555'

Looking at the execution plan, we are in for a shocker. We would have expected that now, the second query should be the slower one, but it is not!

This is because the column being compared is of a higher precedence. Therefore, only the supplied condition parameter needs to be implicitly converted to INT, thus reducing the performance impact to being negligible.

Functions may not always be your friends

SQL Server has a great collection of functions, some of which are really, really useful. Data manipulation is a common operation in most systems, and the functions available at our disposal for date manipulation are very powerful. However, they might land us in a performance bottleneck if not used carefully.

First of all, let’s create a very simple, non-clustered index on the OrderDate column of the Sales.SalesOrderHeader table in the AdventureWorks2008R2 database.

USE [AdventureWorks2008R2]
GO
CREATE NONCLUSTERED INDEX [Idx_MissingIndexOrderDate]
    ON [Sales].[SalesOrderHeader] ([OrderDate])
GO

Next, let us try to find out all the sales done in the month of April, 2008. Now, there are a lot of solutions available, the most popular ones being the ones below. Which one do you think is the performance hog?

--Number of sales done in April, 2008
SELECT COUNT(*)
FROM Sales.SalesOrderHeader sh
WHERE MONTH(sh.OrderDate) = 4 AND YEAR(sh.OrderDate) = 2008

SELECT COUNT(*)
FROM Sales.SalesOrderHeader sh
WHERE sh.OrderDate >= '2008-04-01' AND sh.OrderDate <= '2008-04-30'

SELECT COUNT(*)
FROM Sales.SalesOrderHeader sh
WHERE sh.OrderDate BETWEEN '2008-04-01' AND '2008-04-30'

If you turn on the execution plan, you would notice that Query #1 takes up the most time, i.e. that is the slowest of them all.

The reason is the presence of the functions in the WHERE clause. Functions around columns in a WHERE clause cause table/index scans and must therefore be used judiciously.

If you use these functions in the SELECT clause, you will notice that there isn’t much performance delay. Here’s the proof:

--Number of sales done in April, 2008
SELECT MONTH(sh.OrderDate), YEAR(sh.OrderDate),sh.OrderDate
FROM Sales.SalesOrderHeader sh
WHERE MONTH(sh.OrderDate) = 4 AND YEAR(sh.OrderDate) = 2008

SELECT MONTH(sh.OrderDate), YEAR(sh.OrderDate),sh.OrderDate
FROM Sales.SalesOrderHeader sh
WHERE sh.OrderDate BETWEEN '2008-04-01' AND '2008-04-30'

Question to the audience: Now, let’s say that instead of just the date value (which we were dealing with in case of SalesOrderHeader.OrderDate), we have a DATETIME value to work with. What would be good solutions, according to for the same problem statement? Jacob touched upon this during the Tech-Ed, but I am eliminating that part from the post series so that it can become a great user exercise.

Summarizing – Part 02:

Finally, summarizing all the points that have been discussed today (Part 02) and during Tech-Ed 2011

1. Incorrect use of data-types (especially in the WHERE clause) can cause performance issues because they would force TABLE/INDEX scans due to implicit conversion
2. As far as possible, take the time out during design to ensure that you choose the proper data type. In our example, Refno column should probably never have been a VARCHAR column
3. Functions around columns in a WHERE clause cause table/index scans and must therefore be used judiciously

Thank-you, Jacob, for this really great session!

You can read my memories of Tech-Ed by going through the following series:

Tech Ed 2011 (India) – Day 01 – Connect, Learn, Explore, Evolve!

Tech Ed 2011 (India) – Day 02 – IE 9 – India launch – Fast is now beautiful!

Tech Ed 2011 (India) – Day 03 – For the DBAs – Contained Databases, Extended Events, Virtualization, Wait Stats, Wait Queues & High Availability

Until tomorrow,

Be courteous. Drive responsibly.

Want to ask me a question? Do so at: http://beyondrelational.com/ask/nakul/default.aspx

Last month, all the techies in India had a great time at the Tech-Ed 2011 (India). Out of the many sessions being conducted in parallel, one of the T-SQL programming sessions, there was one session from the renowned SQL Server MVP – Jacob Sebastian, which I found to be very interesting and different.

What have I done? T-SQL Worst Practices

There are a lot of reading materials and classes that talk about and discuss T-SQL Best Practices. But there are very few who talk about T-SQL worst practices. That’s because it takes experience and a lot of deep understanding and intimacy with the product in hand to come up with such a list. This session was one of them, and that’s precisely why my it grabbed my attention.

In this session, Jacob demonstrated some of the most common T-SQL worst practices that one can make (besides of course not clapping when one likes a session). All of these are not major issues that would jump right out at you, but are things that appear to be absolutely normal and harmless in the development and QA environments, but turn into performance bottlenecks or may end up returning incorrect results when deployed into production.

Today, I attempt to summarize these worst practices into a blog post. We will look at:

1. Practices that can affect accuracy of data returned/processed (Part 01)
2. Practices that can adversely impact performance (Part 02)
3. Practices that can have unpredictable execution side-effects (Part 03)

While the points covered are exactly the same as covered during the Tech-Ed, I have taken hints from Jacob’s queries and have come up with my own versions using the AdventureWorks2008R2 database supplied as a sample database for SQL 11 (“Denali”) CTP01. In cases where I am directly using Jacob’s scripts, I have mentioned it as such.

Part 01 – Practices affecting accuracy of data returned/processed

Using NOT IN is not really the solution

Manager: I need to find all the Sales staff who do not fall within a given set of territory IDs.

You: That’s easy. I will write up a simple T-SQL query to get you the results in a moment.

USE AdventureWorks2008R2
GO
SELECT * FROM Sales.SalesPerson WHERE TerritoryID NOT IN (1,2,3,4,5)

Manager: Is this correct? It does not appear to be so.

USE AdventureWorks2008R2
GO
--17 records
SELECT COUNT(*) FROM Sales.SalesPerson
--8 records
SELECT COUNT(*) FROM Sales.SalesPerson WHERE TerritoryID IN (1,2,3,4,5)
--6 records, instead of the expected 17-8 = 9 records!
SELECT COUNT(*) FROM Sales.SalesPerson WHERE TerritoryID NOT IN (1,2,3,4,5)

From the above calculations, we see that about records are missing from the results set. These records are such which do not have any Territory ID allocated!

You: What went wrong? The query should have worked….

Here’s what happened…

Root Cause: NULL values are special. In the world of electronics, we are all familiar with NULL as the “tri-state” – ON, OFF and a third-state mysterious state. NULL technically means undefined, and hence cannot take part in comparison operations, by default.

When we used the NOT IN operator, we were asking SQL Server to carry out a comparison which it cannot do for records with NULL values and hence, these records were ignored.

Solution 01: You can modify this behaviour of SQL Server by setting ANSI_NULLS to OFF. However, this is not recommended because:

1. Setting ANSI_NULLS to OFF causes the comparison operators in SQL Server to stop following the ISO standards
2. ANSI_NULLS will always be ON in a future release of SQL Server (refer Books On Line)

Solution 02: Alternatively, you can also use the NOT EXISTS clause to fetch the correct results:

USE AdventureWorks2008R2
GO
-- Solution #01 - Set ANSI_NULLS to OFF. 
/****************************************
This is not a recommended solution
****************************************/
SET ANSI_NULLS OFF
SELECT * FROM Sales.SalesPerson WHERE TerritoryID NOT IN (1,2,3,4,5)
-- Because setting ANSI_NULLS to OFF is not recommended, revert back
SET ANSI_NULLS ON

-- Solution #02 - Using the NOT EXISTS Clause
SELECT * FROM Sales.SalesPerson sp1 WHERE NOT EXISTS (SELECT *
                                                      FROM Sales.SalesPerson sp2
                                                      WHERE sp1.TerritoryID = sp2.TerritoryID AND sp2.TerritoryID 
                                                      IN (1,2,3,4,5))

COUNT(*) and LEFT v/s RIGHT joins

Assume the following scenario wherein we have a list of Sales Personnel in one table and the orders booked by them in another table. The scripts and data is supplied by Jacob, because such data does not exist in the AdventureWorks2008R2 database.

/*-----------------------------------------------------------------------------
  Date			: 23 March 2011
  SQL Version           : SQL Server 2005/2008/Denali
  Author		: Jacob Sebastian
  Email			: jacob@beyondrelational.com
  Twitter		: @jacobsebastian
  Blog			: http://beyondrelational.com/blogs/jacob
  Website		: http://beyondrelational.com

  Summary:
  This script is part of the demo presented by Jacob Sebastian during 
  Tech-ED India 2011 on "TSQL Worst Practices".

  Disclaimer:  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
-----------------------------------------------------------------------------*/
USE teched 
GO

IF OBJECT_ID('salesrep','U') IS NOT NULL DROP TABLE salesrep
GO

CREATE TABLE salesrep (id int, name varchar(20))
GO

INSERT INTO salesrep(id, name) SELECT 1, 'Jacob'
INSERT INTO salesrep(id, name) SELECT 2, 'Pinal'
INSERT INTO salesrep(id, name) SELECT 3, 'Mike'
INSERT INTO salesrep(id, name) SELECT 4, 'Steve'

IF OBJECT_ID('orders', 'U') IS NOT NULL DROP TABLE orders 
GO

CREATE TABLE orders (salesrep INT, OrderID INT, Amount MONEY)
INSERT INTO orders (salesrep, OrderID, Amount) select 1, 1, 100
INSERT INTO orders (salesrep, OrderID, Amount) select 1, 2, 100
INSERT INTO orders (salesrep, OrderID, Amount) select 2, 3, 100
INSERT INTO orders (salesrep, OrderID, Amount) select 2, 4, 100
INSERT INTO orders (salesrep, OrderID, Amount) select 2, 5, 100

SELECT * FROM salesrep
SELECT * FROM orders 

Manager: I want a list of all Sales Personnel with the number of orders booked by them.

You: Sure. I will have the results ready for your review in 5 minutes

SELECT s.name,
       COUNT(*) AS OrderCount
FROM salesrep s
LEFT JOIN orders o ON o.salesrep = s.id
GROUP BY s.name  

You (thinking): The results don’t look right. What’s going on?

Root Cause: Again, it is the NULL values that bother us. COUNT (*) causes an aggregation of all columns in the result set. Looking at Books On Line (COUNT aggregate function), we can understand that except for COUNT, all aggregate functions ignore NULL values. COUNT is the black sheep in the sense that by default it will count a NULL and all duplicate values as a 1.

Solution(s): There are a minimum of two solutions to this mystery.

1. The first of the solutions is to use the INNER JOIN instead of the LEFT JOIN. However, this might not be what the manager wanted (remember that the manager had asked for a list of all sales personnel with the number of orders booked by them). If we use INNER JOIN, we will only get a list of sales personnel who have booked at least one order in their name
2. The second involves using a column from the right-hand side table of the LEFT JOIN. This would be a correct query

-- Solution 01 - Technically correct, but may not be what the manager wanted
SELECT s.name,
       COUNT(*) AS OrderCount
FROM salesrep s
INNER JOIN orders o ON o.salesrep = s.id
GROUP BY s.name   

-- Solution 02 - Technically correct and what the manager wanted
SELECT s.name,
       COUNT(o.orderid) AS OrderCount
FROM salesrep s
LEFT JOIN orders o ON o.salesrep = s.id
GROUP BY s.name  

Summarizing – Part 01:

Finally, summarizing all the points that have been discussed today (Part 01) and during Tech-Ed 2011

1. Setting ANSI_NULLS to OFF is bad! Any code using this dependency should be revisited
2. Using NOT IN may give you incorrect results. You may use NOT NULL or NOT EXISTS check instead. Be careful about the possibility of NULL values when using NOT IN
3. COUNT(*) with LEFT JOIN may produce incorrect results
4. Use COUNT(column) from RIGHT side table instead of COUNT(*)
5. There are always hidden requirements as in the case of the manager of the COUNT(*) example – pay attention to each and every word!

Thank-you, Jacob, for this really great session!

You can read my memories of Tech-Ed by going through the following series:

Tech Ed 2011 (India) – Day 01 – Connect, Learn, Explore, Evolve!

Tech Ed 2011 (India) – Day 02 – IE 9 – India launch – Fast is now beautiful!

Tech Ed 2011 (India) – Day 03 – For the DBAs – Contained Databases, Extended Events, Virtualization, Wait Stats, Wait Queues & High Availability

Until tomorrow,

Be courteous. Drive responsibly.

Today, we will talk about a slightly different, yet very common subject. We will talk about what are the different ways to measure the number of rows in a table.

I found a couple of posts over the Internet, and I will reference them as and when I demonstrate the associated methods. This post basically is an assimilation of these methods and attempts to perform a comparative analysis to categorize them in terms of:

• Performance (in terms of time)
• Accuracy

The most common methods

The most common of all methods is the one that every person who has even come in contact with Microsoft SQL Server for even 5 minutes knows about. It is the very generic:

SELECT COUNT(*) FROM HumanResources.Employee

Other similar options are:

SELECT COUNT(1) FROM HumanResources.Employee
SELECT COUNT(BusinessEntityID) FROM HumanResources.Employee

Some developers believe that COUNT(1) or using the 1st primary key column is faster for the COUNT() function, however, that is untrue. Here’s the proof:

If you have tables with rows greater than the range of INT data type, you may use COUNT_BIG() instead.

Using Catalog Views

Microsoft SQL Server comes with a few catalog views, which return information that is used by the SQL Server Database Engine. Obviously, this is one of the places where one should go and look for the row count information. A table with a clustered index will have at least one entry for that index in the catalog view – sys.sysindexes. This entry will have the count of the number of rows in the index, which in-turn is equal to the number of rows in the table (for SQL 2008 and up, a clustered index cannot be filtered and hence clustered indexes will always hold the information for the entire table).

SELECT si.rows AS 'RowCount'
FROM sys.sysindexes si
WHERE si.id = OBJECT_ID('HumanResources.Employee') AND si.indid < 2

Comparing against the conventional options, we find that this option is faster!

However, please note that the catalog views may report stale data.

Using DMVs

Mr. Pinal Dave (blog) provides a great method of accurately determining the number of rows in a table with the use of DMVs in his post here.

His query has been modified to only look for the HumanResources.Employee table below:

SELECT sc.name + '.' + ta.name TableName, SUM(pa.rows) RowCnt
FROM sys.tables ta
INNER JOIN sys.partitions pa ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN sys.schemas sc ON ta.schema_id = sc.schema_id
WHERE ta.is_ms_shipped = 0 AND 
      pa.index_id IN (1,0) AND
      sc.name = 'HumanResources' AND
      ta.name = 'Employee'
GROUP BY sc.name, ta.name
ORDER BY SUM(pa.rows) DESC

This method falls behind in performance when searching for row counts of individual tables as demonstrated below. However, without the schema name and the table name filters added in the WHERE clause, this is a handy script to get the row counts for all tables in a database. As a matter of fact, I have verified with the help of the SQL Profiler that Microsoft also uses the sys.partitions DMV to fetch the row counts when showing them on the Object Explorer Details window. (If you would like to know more about the Object Explorer Details window, refer my posts – Part I and Part II, which are part of my series on the Underappreciated features of Microsoft SQL Server)

I would recommend this to be in the "tool-kit” of each and every database developer, administrator and operator.

Using Aggregation Functions

The following method, from Madhivanan’s post (blog) (read this post here) is quite interesting:

SELECT SUM(1) AS ‘RowCount’ FROM HumanResources.Employee

Performance-wise, this method is same as the well-known COUNT(*) or COUNT(1), but it’s interesting because it uses an aggregation function to get the row count.

Using System Stored Procedures

This one again comes from Madhivanan’s post, with a small extension from Books-On-Line.

Microsoft SQL Server comes with a big list of system functions (the most common ones being sp_help, sp_executesql and many others which we use on a regular basis). It is no surprise then that we also have a system function to provide information on the number of rows in a table.

Rows in a table occupy space, and hence, it is fit that the system stored procedure responsible to predict the space usage is also responsible to predict the number of rows in a table.

--Default version, may return stale results
EXEC sp_spaceused @objname=N'HumanResources.Employee';

--Force update of usage statistics, will always return current usage
EXEC sp_spaceused @objname=N'HumanResources.Employee', @updateusage=N'TRUE';

Here’s the catch: System stored procedures ALWAYS return an INT. If you have rows that exceed the range of an INT in number, do not use these.

Also, this is slower in performance when compared to the conventional methods mentioned above.

Read more on sp_spaceused on Books-On-Line at: http://msdn.microsoft.com/en-us/library/ms188776.aspx

The next method that also uses a system function is the one below which came from a friend:

SELECT COUNT(@@ROWCOUNT) FROM HumanResources.Employee

This method performs equally well as the conventional ones.

Summary

Summarizing, I would attempt to present a small table that would serve as a ready look-up for each of these methods. I hope you find it useful.

Method	Performance (Time)	Accuracy
Conventional (SELECT COUNT(*) and others)	No	Yes
Catalog Views (sys.sysindexes)	Yes	Approximate
DMVs	Partial*	Yes
Aggregate Functions	No	Yes
System Stored Procedures	No	Approximate

(*Partial = Fares low on performance for individual tables, but scores high for a group of tables).

There are a couple of points which I would like the reader to keep in mind when gathering data for row count analysis:

• System stored procedures will always return an INT value
• Size information is not maintained for non-clustered indexes – please exercise due diligence before using these methods with non-clustered indexes (or tables without clustered indexes, i.e. heaps)
• Row counts may be incorrect for large tables. For their accuracy, always update statistics by using the following before attempting to fetch an accurate row count:

--Update the table usage statistics
DBCC UPDATEUSAGE ('AdventureWorks2008','HumanResources.Employee')
GO
--Per Books On Line:
--Reports and corrects inaccuracies in the sysindexes table, 
--which may result in incorrect space usage reports by the sp_spaceused system stored procedure.

Computing Row Counts for a range of values

When working with a range of values, getting the row counts is a bit difficult and has very limited options. To the best of my knowledge, for users using SQL Server 2005, we can only use the conventional queries:

SELECT COUNT(*) FROM HumanResources.Employee WHERE BirthDate BETWEEN '1950-01-01' and '1960-01-01'

However, for users using SQL Server 2008 and up, the option to create a filtered index (if the range is pre-defined) and then using the catalog view method is always open.

And finally…

Now for the big question – which of the above methods are my favourites? Here’s my choice:

1. Conventional methods if I need the accurate row count for a single table
2. Catalog Views if I only need approximate row counts
3. For fetching row counts of all tables in a database or a schema, I would go in for Pinal’s script

Do you use any other methods? If yes, do let me know – I will publish the alternatives with due credit. Also, if you use any other methods to get the row counts for range of values, please do share your solutions – they will also be published with your name as the contributor.

Until we meet next time,

Be courteous. Drive responsibly.

As you know, I am documenting my experience at the Tech Ed 2011 (India) held from March 23-25, 2011 at Bengaluru, India.

You can find the summary of my experiences on Day 01 and Day 02 at:

Day 01 – http://beyondrelational.com/blogs/nakul/archive/2011/03/28/tech-ed-2011-india-day-01-connect-learn-explore-evolve.aspx

Day 02 – http://beyondrelational.com/blogs/nakul/archive/2011/03/30/tech-ed-2011-india-day-02-ie-9-india-launch-fast-is-now-beautiful.aspx

Today, as India celebrates it’s win into the cricket World Cup 2011 finals, I will take you through the final day in the wonderful journey of Tech-Ed 2011.

A day for the DBAs

Day 03 was very special – it was a day which had a full track dedicated to the DBA community – guaranteed to attract all DBAs – accidental or not like – bees are to honey.

Hands-on Lab on Contained Databases by Jacob Sebastian

After the keynotes, the day immediately picked up speed as the DBA community got to participate in a hands-on lab on Contained Databases – a long awaited feature, and now part of SQL 11 (Code named “Denali”). The lab was conducted by none other than renowned SQL Server MVP and leader of the Ahmedabad SQL Sever User Group, Jacob Sebastian! His trademark style of explaining difficult concepts by telling a story made this concept a breeze to understand.

I will be taking everyone through the entire hand-on session in a series I am planning for the next week. Stay tuned, and I am sure you will find it useful.

Understanding SQL Server Behavioural Patterns – SQL Server Extended Events by Pinal Dave

Extended Events are not new to SQL Server 11/(“Denali”), but not many know how to efficiently use them. Pinal Dave, the renowned Microsoft Evangelist, demonstrated how to use Extended Events to execute a couple of common database administration tasks. What I liked the most were the demos on:

1. Detect long-running queries
2. Detect page splits

One of the most interesting items of the session was how Pinal demonstrated the concept of what events and extended events are by co-relating them to the human heart and a scenario wherein a hypothetical elephant needs to be taken to the hospital after collapsing during the Tech-Ed. That’s what I call – fun with learning!

You can download the scripts used during this demonstration by visiting Pinal’s blog at: http://blog.sqlauthority.com/2010/03/29/sql-server-introduction-to-extended-events-finding-long-running-queries/

Data Recovery with Backups by Vinod Kumar

Vinod Kumar was back after Day 01 – this time to show the audience the immense value a good backup strategy can have. Vinod demonstrated how to recover from the following situations from a backup:

1. Recovering/Restoring to a point in time
2. Recovering/Restoring to a named transaction
3. Recovering from a page corruption

Quite frankly, I did not know that one could “mark” a transaction and that this can be used during recovery – just as a point-in-time restore. As a proof of the importance of this feature, Vinod discussed one of the practical implementations that have been done using this feature. In the interest of privacy, the exact nature of the project was not disclosed to the audience.

SQLCAT: SQL Server Consolidation – Using Virtualization & Lessons Learned by Prem Mehra

Prem Mehra is a Program Manager with Microsoft at Redmond and works for the SQL Customer Advisory Team (SQLCAT). This team is comprised of solution experts that regularly engage in the largest, most complex and most unique customer deployment engagements worldwide.

In this session, Prem discussed some of the consolidation projects that the SQLCAT team has done in a virtualized/cloud based environment. The audience had some great things to learn from him, and my biggest take-away was the following:

“If a SQL Server is not performing well, the problem is either in the application or in the underlying hardware platform – in the order mentioned.”

Also, Prem demonstrated the ideal approach in moving towards a cloud based deployment (which is shown here as a picture captured of his slide) and how the upcoming SQL Server 11 (“Denali”) will help in the journey.

(Source: http://msdn.microsoft.com/en-us/library/ee819082.aspx)

SQL Server Waits and Queues – Your Gateway to Performance Troubleshooting by Pinal Dave

Do you really know how to remove SQL Server bottlenecks when a server seems to be really, really slow? Yeah, of course! Look at the wait statistics and queues – how difficult can it be?

Not much. So, are CXPACKET waits bad? Can we eliminate LOGBUFFER waits? What are Signal waits?

Pinal did it once again – with a simple example of a group of people awaiting a cab and then stopping to withdraw cash from an ATM, Pinal very beautifully explained Signal Waits. He then went on to demonstrate and explain what CXPACKET waits are and how they will always be there, and that they might sometimes be good. Finally, in an power-packed demo, he demonstrated how moving the data file of a database to his portable USB drive can reduce LOGBUFFER waits. (Yes, you read me right – Pinal moved the data file of a database to his portable USB drive)

Here’s where you can read Pinal’s series on Wait stats & queues – http://blog.sqlauthority.com/2011/02/28/sql-server-summary-of-month-wait-type-day-28-of-28/

SQL Server AlwaysOn: The next generation high availability solution by Balmukund Lakhani

High Availability is one of the key requirements of any SQL Server deployments. Who better than the Technical Lead in the SQL Server support team with Microsoft India GTSC to demonstrate how simple it is to setup a cluster, configure a high-availability solution, and demonstrate a failover – all LIVE and within the hour!

Balmukund demonstrated the High Availability “AlwaysON” features which will be made available in SQL Server 11 (code named “Denali”). The solution will definitely make the lives of all DBAs and IT administrators easier because combined with the power of Windows Server 2008 R2, setting up a cluster (or an Availability Group) is a breeze. The cluster can have one automatic fail-over partner, with other manual fail-over partners. The best benefit of this solution is that it is similar to mirroring – but without the drawbacks of mirroring.

Here’s a step-by-step guide to getting you started on HADR: http://blogs.msdn.com/b/sqlserverfaq/archive/2010/12/17/sql-server-denali-alwayson-hadr-step-by-setup-setup-guide.aspx

Closing Keynote – Visit by Anil Kumble!

Finally, the time came to close the Tech-Ed 2011 (India). With a heavy heart, all participants submitted their feedback forms for the last sessions of the day and proceeded towards the keynote hall for the closing keynote.

The final attraction of this year’s Tech-Ed was that we had a very special guest with us. None other than the star cricketer – Anil Kumble! None other than Gaurav Kapoor, who hosted the live sessions of Tech-Ed, introduced Anil Kumble on stage and the entire group erupted in cheers! Hands went-up all the way to catch a snap or two of Anil Kumble in person. So high was the excitement, that nothing could distract the audience. I will let the pictures speak for themselves.

 

A full-house awaiting the start of the closing ceremony.

What’s next?

My next post will be a deep dive into Jacob’s session on the worst T-SQL practices on Tech-Ed Day 01. Next week, we will continue the series on the Underappreciated Features of SQL Server, and also explore the “Contained Database” feature of SQL Server 11 (code named – “ Denali”). Contained Databases is based on the hands-on lab conducted on the same topic by Jacob on Day 03 of Tech-Ed 2011.

Watch out for some exciting new and exciting reading material hot off the presses!

Until we meet next time,

Be courteous. Drive responsibly.

As many of you would know by now, these days I am attempting to summarize my experience at Tech-Ed 2011 (India) held from March 23-25, 2011 at Bengaluru, India.

The summary of my experiences on Day 01 can be found at:http://beyondrelational.com/blogs/nakul/archive/2011/03/28/tech-ed-2011-india-day-01-connect-learn-explore-evolve.aspx

Today, I will be sharing with you the experience of Day 02.

IE 9 India Launch – Fast is Now Beautiful!

I just can’t believe how lucky I am to be a part of history! Day 02 of the Tech Ed 2011 saw the India launch of the most awaited web-browser – The Internet Explorer 9! Brian Hall, General Manager of Windows Live and Internet Explorer products was at the venue to demonstrate the path towards a more beautiful web!

With full support of HTML 5 and CSS3, this is a completely new browser, that allows us (the end users) to pay more attention to the web-site and the overall browsing experience rather than the browser itself. The IE9 browser can leverage the CPU’s available power for better performance. The browser integrates seamlessly with Windows 7 and is able to make use of features like jumplists to allow for a highly productive web-experience.

Incidentally, the new JavaScript engine in the IE9 is called – “Chakra”! The world’s 2nd largest developer ecosystem (refer Mr. Qi Lu’s comment on the Indian IT industry during the opening keynote on Day 01) does have a deep influence.

Some of the sites where you can see the capabilities of IE9 in full bloom are http://www.snapdeal.com and http://www.flipkart.com. Go check them out!

Download IE9 from http://www.beautyoftheweb.com/

Test your browser against IE9 by using the test tools available at: http://ie.microsoft.com/testdrive/

IE6 countdown…

10 years ago, a browser was born. A browser that changed the browsing experience forever, and was so great that people still use it to this day! It was the Internet Explorer 6. Now, it is time for IE6 to die. Internet technologies have grown to such an extent that all of us need to work together to finally sunset IE6. Visit  http://www.ie6countdown.com/ to do the following:

1. Join the cause – Display the countdown timer on your website for IE6 users
2. Get a list of the reasons of why you absolutely should upgrade to IE9
3. Spread the message – about 15.8K tweets are already out as of writing this post – won’t you do your part?

Windows Azure – a solid cloud platform

After the power-packed launch of IE9, it was time for studying some case-studies on the practical implementations of Windows Azure. Bharat Shyam, General Manager, Windows Azure was at the event to showcase the success stories of Windows Azure. The audience was mesmerized when Bharat shared some of the biggest Azure installations. To name a few, Windows Live, Bing, Exchange Hosted Services, MSN website, Windows Hotmail and a lot of other commonly used sites and services are hosted on Azure.

Breakout Sessions – a mix of Core Dev & BI tracks

Day 02 was a mixed-bag for me. Personal interest in data access technologies and BI technologies made me attend sessions across tracks – some in the Core Dev and others in the BI track.

Core .NET Framework 4.0 enhancements every developer should know

Sanjay Vyas and Raj Chaudhuri, two of the leading independent experts (and MVPs) in the .NET area presented a very concise, but thorough session on the key enhancements of the .NET Framework 4.0.

What’s new in the Base Class Library?

1. Managed Extensibility Framework (MEF) – Declaration & consumption of extensibility points and monitoring of new runtime extensions
2. New Data types of BigInteger/ComplexNumber/Tuple and Sorted Set
3. I/O improvements – Memory mapped files

New Language Features

New in C# 4.0	New in VB.NET 10	New in Both
Named Parameters Optional Parameters Dynamic Scoping	Statement Lambdas Multiline Lambdas Auto-implemented properties Collection initalizer	Generic Variance Extension Property

Data Access Methodologies – When to choose what (ADO.NET, Entity Framework, LINQ, WCF Data Services)

Post lunch, Wriju Ghosh gave a concise pill on Data Access Methodologies explaining when and how to choose between ADO.NET, Entity Framework, LINQ and WCF Data services.

Switching gears with BI

After some core Dev sessions, it was time to join the BI track by attending the following sessions:

1. Building Semantic Models in SQL Server “Denali” – Rushabh B. Mehta (President, PASS and MD, Solid Quality India Pvt. Ltd)
2. SSAS – Designing, Development & Deployment Best Practices – Amit Bansal
3. Internal and working of DAX with Power Pivot – Rushabh B. Mehta

Power Pivot, since it’s release has attracted me for it’s simplicity, and Rushabh’s session was full of pointers to make Power Pivot a very powerful self-service BI tool.

Closing time – Demo Extravaganza!

As on Day 01, the final session of the day was an action-packed show of the sheer power that Microsoft’s technologies possess. From games that one can make in under 3 minutes to calculating the distance between Bengaluru and Hyderabad (venue, Tech-Ed 2009) using SQL Server Spatial databases – this session had it all. The audience never batted an eyelid, and we had a perfect end to another perfect day!

Stay tuned for a detailed account of excitement on the final day of Tech-Ed 2011 (India)!

Until we meet next time,

Be courteous. Drive responsibly.