T-SQL querying tips

Hi Friends,

I already have 2 posts on T-SQL programming/querying tips since the new site has been setup. One of the tips is like a question and if you know the answer, do respond to the blog post. Visit my new blog site at www.BlogBoard.in/AmitBansal

You shall see more technical action on my blog than ever before. So, do visit and start interacting.

Best Regards
Amit

My new blog - http://www.BlogBoard.in/AmitBansal

Hello Friends,

Just a reminder.

I want to let you all know that my new blog site is ready. It is http://blogboard.in/AmitBansal

www.Blogboard.in is a community initiative by my company, eDominer Systems and many experts will be blogging on this site. The site is still under construction as we are updating it every day. However, my blog is active now.

I request you to please subscribe to my new blog and keep supporting me as you have always done in the past.

I certainly need lot of encouragement from you to keep going. With my new site, I am going to be more active then ever before. I am also starting social blogging. :)

So, update you RRS readers with the new address, http://blogboard.in/AmitBansal and see more technical action on my blog than ever before.

Thanks & best regards
Amit

My new blog

Hello Friends,

Wish you all a very happy and prosperous new year. I know I am a little late but nonetheless, it is still the begnning of the year.

I want to let you all know that my new blog site is ready. It is http://blogboard.in/AmitBansal

www.Blogboard.in is a community initiative by my company, eDominer Systems and many experts will be blogging on this site. The site is still under construction as we are updating it every day. However, my blog is active now.

I request you to please subscribe to my new blog and keep supporting me as you have always done in the past.

I certainly need lot of encouragement from you to keep going. With my new site, I am going to be more active then ever before. I am also starting social blogging. :)

So, update you RRS readers with the new address, http://blogboard.in/AmitBansal and see more technical action on my blog than ever before.

Thanks & best regards
Amit

Upgrading SQL Server 2005 Express Edition to Service Pack 2 without fresh installation

Hi All,

MOst of the times, I either work with SQL Server Enterprise Edition or Standrd Edition. Today I was required to help a team member in upgrading SQL Server 2005 Express Edtion to Service Pack 2. As exptected of me, I advised him to download the service pack and install it. However, I did not know that there is no seprate Service Pack download for express edition. Its the complete express edition installation already service packed. That means wht you have to download is SQL Server 2005 Express Editon SP 2 installation file (the complete stuff). But we didnt want to do a fresh installation. We wanted to upgrade an exisiting instance. SO, the trick here is that you need to run the same installation (the complete stuff) and choose to upgrade an exisiting instance during the wizard. The wizrd for some time looks like that you are doing a fresh installation and you may land up getting upset [I did not get upset, my team member did ;)], but hold on, move ahead and you shall see a screen where you can choose exisiting instance to upgrade. So thats the story.
I thank Vinod Kumar of Microsoft. Helped me to sort out his. Here is a link for your assistance:

http://seer.entsupport.symantec.com/docs/288051.htm

Thanks
Amit

FORCESEEK table hint in sql server 2008 - Part II - sloving parameter sniffing issues

Hello Everyone,

In Part 1, I introduced the FORCESEEK hint in SQL server 2008 that can force the query optimizer to perform a seek instead of a scan on an index. However, I did not discuss some scenarios where this hint can be userful. One the of the scenarios where this hint can be useful in SQL Server is working around with 'Parameter Sniffing'.

But first, What is Parameter Sniffing??

Parameter Sniffing is a technique by which the the sql server query optimization engine sniffs the parameter value from the query and generates an optimized execution plan based on that value.

let us take an example. This example uses the northwind database. You can download the northwind database from the following link and restore it in sql server 2008.

http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en


Run the following query and observe the result set and the execution plan:

----
SELECT [OrderId], [OrderDate]
FROM [Orders]
WHERE [ShipPostalCode] = N'05022'
----

You will see that only 1 record is returned, that means, very high selectivity. And now if u see the execution plan you will notice that sql server does a fine job by performing seek and lookups.

now run the following query and observe the result set and the execution plan:
(yes, i have changed the postal code value in this query :))

-------
SELECT [OrderId], [OrderDate]
FROM [Orders]
WHERE [ShipPostalCode] = N'83720';
-----------

You will see that around 30 records are returned, that means, low selectivity. And now if u see the execution plan you will notice that sql server does a fine job by performing a clustered index scan can becuase at this time seek will be expensive.

So, this is known as parameter sniffing, where sql server sniffs the parameter value and prepares the execition plan accordingly.

So where is the problem??

The problem is when u run the same queries and this time pass the parameter vlaues using variables and not by hard cording. when u pass the values using variables, sql server deos not know the parameter value until runtime and makes a hard coded guess. let me prove it:


run both the queries now as shown below and observe the execution plan:
(note that this time i am using variables rather than hardcoding parameter values)


-- simple query with a variable 05022
DECLARE @ShipCode nvarchar(20);
SET @ShipCode = N'05022';
SELECT [OrderId], [OrderDate]
FROM [Orders]
WHERE [ShipPostalCode] = @ShipCode;



--- simple query with a variable 83720
DECLARE @ShipCode nvarchar(20);
SET @ShipCode = N'83720';
SELECT [OrderId], [OrderDate]
FROM [Orders]
WHERE [ShipPostalCode] = @ShipCode;


What did u notice in the execution plan?

You will see that both the execution plan use clustered index scan even though parameter values are different. Comparing this with previous scenario, sql server rightly used seek in case of high selectivity and scan in case of low selectivity.

This has happned because this time sql server could not sniff parameter values as the values were not available until run time since we used variables and sql server used a hardcoded gues based on 30% selectivity (a general mechanism followed by sql server)

now, the crux is:

if your application uses variables, and most of the times these variable values result in high selectivity, how can you force sql server to perform a seek operation instead of scan.

Yes, we can use FORCESEEK :)

run the following queries again and see the execution plan (this time I have added the forceseek table hint to ensure a seek is performed)

-- simple query with a variable 05022
DECLARE @ShipCode nvarchar(20);
SET @ShipCode = N'05022';
SELECT [OrderId], [OrderDate]
FROM [Orders] with (forceseek)
WHERE [ShipPostalCode] = @ShipCode


--dbcc show_statistics ('dbo.orders','shippostalcode')

--- simple query with a variable 83720
DECLARE @ShipCode nvarchar(20);
SET @ShipCode = N'83720';
SELECT [OrderId], [OrderDate]
FROM [Orders] with (forceseek)
WHERE [ShipPostalCode] = @ShipCode;

you will now observe in the execution plan that seek operation is performed as expected for both the queries.

Remember:

-use hints as a last resort for performance tuning (the optimizer does a good job most of the times)
-in this scenario, i insisted that most of the times, the resultset will be hihgly selective so i forced a seek.
-i can also use other hints like OPTIMIZE FOR, RECOMPILE, etc.. but the problem/soltion will change slightly. I am not discussing them as it is out of scope here.


I hope I was able to explain Parameter Sniffing and how FORCESEEK can be used to workaround them.

Do post comments if you liked the blog.

Thanks
Amit

SQL Server 2008 RTM released

Hi All,

The wait is over. SQL Server 2008 has been released.

Many of you have been evaluating the SQL Server 2008 betas, and may be ready to try out the RTM version available at http://technet.microsoft.com/hi-in/evalcenter/bb851664(en-us).aspx

TechNet Plus subscribers can find RTM versions now available for download at http://technet.microsoft.com/hi-in/subscriptions/default(en-us).aspx.

Enjoy the power of of SQL Server 2008. For questions, comments & feedback, participate in our newsgroup www.PoweredBySQLServer.com

Regards
Amit

Some thoughts on Data Mining in SQL Server 2008-Part I

Hi All,

Data Mining is seldom talked about. We all are trying to focus on new features in sql server 2008 from the perspective programming, manageability, availability, etc. I would like to talk a bit about the Data Mining offering from SQL Server. What exactly is Data Mining? Why do u need it? How does it benefit an organization? I plan to write a series of articles on data mining, but let me start with the “WHY” part of it… I hope I am successful in writing a series :)

Data Mining is the process of identifying hidden relationships and patterns in your data. I know that sounds very bookish. Let me explain more. Sometimes, organizations have difficult questions that need to be answered. These difficult questions need intelligent answers. For example, your company sells products to millions of customers, and your company wants to know why does a customer purchase your product? What is the decision making criteria before a customer decides to buy your product? Which factors influence his decision the most? Now with millions of customers in your database, how would you answer such a question? This asks for an intelligent answer. Do human beings have the capability to comprehend or analyze such huge amount of data? Will you have the capacity to browse transaction by transaction (row by row) to fathom the attribute values in your customers/orders tables? For sure this is not practical. Data mining comes to rescue and the company needs data mining because either the data is too complex or the data is huge or both. And casual human observation cannot do the job. The company needs to know the answers to the difficult questions because they want take some action based on the discovering, based on the hidden relationships. This is basically the actionable information that the company wants. There are many scenarios where data mining can be used like:-

• Forecasting sales
• Targeting mailings toward specific customers
• Determining which products are likely to be sold together
• Finding sequences in the order that customers add products to a shopping cart

There are certainly more examples where US agencies use Data Mining to detect frauds, financial institutions use it to indentify potential defaulters, etc..

The subject here that I am tryting to teach is Predictive Analysis. Data Mining is also known as KDD process (Knowledge Discovery in Database).

In my next blog, I shall talk more about the Data Mining process and the project Life cycle. Stay tuned.


Bogging this from 3rd row of Classroom 2 of Building 40 at MS campus in Redmond, US :)

Tanke care, Amit

Installing SQL Server on a domain controller

Hi All,

One of the members in the Mumbai IT forum asked about installing SQL Server 2008 on a domain controller. The following is an abstract from Books Online. This applies to SQL Server 205 as well.

--------------------------------------------------
For security reasons, Microsoft recommends that you do not install SQL Server 2008 on a domain controller. SQL Server Setup will not block installation on a computer that is a domain controller, but the following limitations apply:

-On Windows Server 2003, SQL Server services can run under a domain account or a local system account.
-You cannot run SQL Server services on a domain controller under a local service account or a network service account.
-After SQL Server is installed on a computer, you cannot change the computer from a domain member to a domain controller. You must uninstall SQL Server before you change the host computer to a domain controller.
-After SQL Server is installed on a computer, you cannot change the computer from a domain controller to a domain member. You must uninstall SQL Server before you change the host computer to a domain member.
-SQL Server failover cluster instances are not supported where cluster nodes are domain controllers.
-SQL Server is not supported on a read-only domain controller.
-------------------------------------------

Thanks
Amit