Delete and archive bulk record from SQL Server table with OUTPUT clause

Delete and archive bulk record from SQL Server table with OUTPUT clause

One of my Facebook friend and old colleague who is Team Leader for one financial product asked me that he has a table with millions of records and wanted to archive few million records from that table, how he can do it?

Simple DELETE statement would make the huge transaction log file (if database is not simple recovery mode), lock the table for VERY LONG time and consume too much resource which will ultimately affect performance.

It is always good to keep short transaction and hence I always prefer to delete / update records in bunch, especially when there is a question of million rows in live production environment because sometime deleting millions of records in highly transaction environment take very long time, may be few hours or a day and if you cancel execution of delete query, everything will be roll backed which will again take a long time and if you are deleting in small bunch, like 1000 rows at a time and you stop execution, you will have to roll back maximum 1000 rows which won’t take long time.

Let me show you how can we do it by creating one sample database.

[sourcecode language=”sql”]
–Create sample database
CREATE DATABASE ExtremeAdvice
GO

USE ExtremeAdvice
GO

–create sample table along with 100,000 rows
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 INT)
GO

INSERT INTO orders (OrderDate, Amount, Refno)
SELECT TOP 100000
DATEADD(minute, ABS(a.OBJECT_ID % 50000 ), CAST(‘2012-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
GO

IF OBJECT_ID(‘ordersArchive’, ‘U’) IS NOT NULL BEGIN
DROP TABLE ordersArchive
END
GO

CREATE TABLE ordersArchive (OrderID INT, OrderDate DATETIME, Amount MONEY, Refno INT)
GO
[/sourcecode]

Now we have sample environment ready to use so let us create one stored procedure which will delete rows based on the condition we provide.

[sourcecode language=”sql”]
USE [ExtremeAdvice]
GO

CREATE PROCEDURE [dbo].[SPordersArchive]
AS

SET NOCOUNT ON
DECLARE @cnt INT, @rows INT
SELECT @cnt = 1

DECLARE @msg VARCHAR(1024)
DECLARE @dt DATETIME

–creating infinite loop which will break itself whenever all record gets deleted based on condition given
WHILE 1=1
BEGIN
SELECT
@dt = GETDATE(),
@rows = 0

–Deleting records in bunch
DELETE TOP (1000) o
OUTPUT
deleted.OrderID,
deleted.OrderDate,
deleted.Amount,
deleted.RefNo
INTO ordersArchive(
OrderID
,OrderDate
,Amount
,RefNo
)
FROM Orders AS o (NOLOCK)
WHERE o.OrderID <=50000

SELECT @rows = @@ROWCOUNT

SELECT @cnt = @cnt + 1

SELECT @msg = ‘Lap : ‘ + CAST(@cnt AS VARCHAR) + ‘ ARCHIVED ‘ + cast(@rows AS VARCHAR) + ‘ rows in ‘ + cast(DATEDIFF(second, @dt, GETDATE()) as varchar) + ‘ seconds’
RAISERROR(@msg, 0, 1) WITH NOWAIT
IF @rows = 0 BREAK;
WAITFOR DELAY ’00:00:00.100′
END
[/sourcecode]

Let us now execute the stored procedure, it may take sometime and you will get message later on which will looks something like following screen capture.

1DeleteMessage

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Error Fix: Msg 7391, Level 16, State 2, Line 37 The operation could not be performed because OLE DB provider “SQLNCLI10” for linked server “LinkServerName” was unable to begin a distributed transaction.

Error Fix: Msg 7391, Level 16, State 2, Line 37 The operation could not be performed because OLE DB provider “SQLNCLI10” for linked server “LinkServerName” was unable to begin a distributed transaction.

This error comes when you are trying execute distributed transaction via linked server. Personally I don’t prefer linked server and would always go for other alternatives like SSIS package or something else but one of my friend had a situation where he must had to setup linked server to make distributed transaction. Security wasn’t concern for him as his network was just an intranet and under hardware as well as software firewall so I have helped him to setup distributed transaction for linked server so that he can fix the error:

Msg 7391, Level 16, State 2, Line 37 The operation could not be performed because OLE DB provider “SQLNCLI10” for linked server “MARSLINK” was unable to begin a distributed transaction

For example we have two server named “SATURN” and “MARS” accordingly. We have set link server of MARS in SATURN so that I can access database of MARS from SATURN. I wanted to execute one SP from one of the database of MARS from SATURN and it was working fine after setting up linked server but when I tried to take output of this SP into one temp table, it started popping up the error given above.

Let us try to resolve this error.

First I will connect to first server which is SATURN in my case.

After connecting to it, open “RUN” from Start menu and type down “DcomCnfg” in RUN dialog box and click on “Ok” button so you will get “Component Service” window open.

Now move to the Console Root->Component Services->computers->My Computer->Distributor Transaction Coordinator->Local DTC from left hand side tree view in “Component Service” window and right click on “Local DTC” option then click on “Property”.

Once you have property of “Local DTC” , go to “Security” tab

134

Select “Network DTC Access” checkbox and also select “Allow Outbound” checkbox and click on “OK” which will restart “Distributed Transaction coordinator” service by its own.

Once SATURN is configured, let us do the same in MARS with minor change:

Now, I will connect to second server which is MARS in my case.

After connecting to it, open “RUN” from Start menu and type down “DcomCnfg” in RUN dialog box and click on “Ok” button so you will get “Component Service” window open.

Now move to the Console Root->Component Services->computers->My Computer->Distributor Transaction Coordinator->Local DTC from left hand side tree view in “Component Service” window and right click on “Local DTC” option then click on “Property”.

Once you have property of “Local DTC” , go to “Security” tab .

136

Select “Network DTC Access” checkbox and also select “Allow Inbound” checkbox and click on “OK” which will restart “Distributed Transaction coordinator” service by its own.

Now, you will be able to execute distributed transaction via linked server.

I have some more article on the subject of Linked Server, have a look at it, if you are interested on this subject.

Create Linked server with Excel 2007 worksheet in SQL Server 2005 (Click Here)

Linked Server Error 7303- Cannot initialize the data source object of OLE DB provider (Click Here)

Linked Server is not configured for data access Error: 7411 in SQL Server 2005 (Click Here)

Linked Server in SQL Server 2005 from ACCESS 2007 (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Debugging SQL Server Stored Procedure in SQL Server 2012

Debugging SQL Server Stored Procedure in SQL Server 2012

Microsoft keep giving powerful facility for debugging in SQL Server and SQL Server 2012 is no exception. I still remember days of SQL Server 6.5 and 7.0 where we didn’t have luxury of debugging which we have at the moment.

I can see many of .NET developer shift their interest in SQL Server and become TSQL Developer, they really miss debugging facility which they used to get in .NET for such as F11 (Step Into) and F10 (Step Over).

Well, there is nothing to miss because SQL Server do have this facility but people doesn’t know due to lack of knowledge.

We generally used to use F5 or “Execute” button in standard toolbar in SSMS to execute query/SP etc, have you ever looked or tried “Debug” button in standard toolbar right beside “execute” button?

I am executing one stored procedure with “Debug” button from the toolbar as opposed with “F5” or “Execute” button. Once I start debugging, I can use F11 (Step Into) and F10 (Step Over). Here is the windows I got while debugging where I can keep watch on variable’s value, call stack etc.

1Debugging

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Looping through dataset in SSIS

Looping through dataset in SSIS

I have written quite a few SSIS article so far but two days back I have received an email from one of the user where he wants to iterate through dataset like we used to loop on each records of dataset in .NET. I thought to provide him solution in form of blog article so that everyone can get benefit of it.

I am going to use my AdventureWorks2012 database in SQL Server 2012 along with Visual Studio 2010 to demonstrate this article.

Let us assume that we have two tables called “Sales.SalesOrderHeader” and “Sales.SalesOrderDetails” in our “AdventureWorks2012” database. We have one OrderID 43659” and we want to change (update) orderQty filed. If SalesOrderDetailID is even, we want to change OrderQty to 2 otherwise we want to change it to 1. We have direct TSQL query to do this task for us but we will use ForEach loop container to do this task to demonstrate how dataset works in SSIS.

Create SELECT query in New Query Window of SQL Server Management Studio in Adventureworks2012 database.

[sourcecode language=”sql”]
SELECT
SOH.SalesOrderID
,SOD.SalesOrderDetailID
,SOH.OrderDate
,SOH.DueDate
,SOD.ProductID
,SOD.OrderQty
FROM
sales.salesorderheader AS SOH JOIN sales.salesorderDetail AS SOD
ON SOD.SalesOrderID=SOH.SalesOrderID
WHERE SOH.SalesOrderID=43659
[/sourcecode]

Create one SSIS project from Visual Studio 2010 and follow the steps given below:

1.) Create variables to contain resultset going to be return from above select query and also create variable to hold value of each field inside the foreach loop. Here is the screen capture of variable window. (you can open variable window by right click on “Control Flow” tab, you will find “Variables” option in popup menu)

1Varibles

2.) Create one Execute SQL Task and create one OLEDB connection inside that for Adventureworks2012 database also have the SELECT query copy from SSMS which we have created above and paste it there. Follow the screen capture.

2ExecuteSQLTaskForSELECT

3.) Take ForEach Loop Container and set the following Property.

3ForEachLoop

4.) now move to “Variable Mappings” tab in same Foreach Loop Editor to set parameter mapping.

4ParameterMappingInForEach 

5.) now set Script task to check whether SalesOrderDetailID is even or odd.

5ScriptTask

Here is the script which you have to write down in “Main” after clicking on “Edit Script”.

[sourcecode language=”C#”]
// TODO: Add your code here
if ((int)Dts.Variables["SalesOrderDetailID"].Value % 2 == 0)
{
Dts.Variables["isOdd"].Value = true;
}
else
{
Dts.Variables["isOdd"].Value = false;
}

Dts.TaskResult = (int)ScriptResults.Success;
[/sourcecode]

6.) Now create two SQL Task and connect it with Script task created above. One connector (“Precedence Constraint”) will have “true” value and another will have “false”. I have taken screen capture of first connector with true value.

6ConnectorProperty

7.) Now both the execute SQL task for update will have UPDATE query. First UPDATE will have OrdQty value 2 as static and second will have 1. I am taking screen shot of first SQL Task inside foreach loop.

7UpdateQuery

8.) once we set all of the above value from “General” tab, we will click on “Parameter Mapping” to provide value of SalesOrderID and SalesOrderDetailID to UPDATE query. Here is the screen capture.

8UpdateQuery

9.) Now when you finally execute package, it will looks like this (if it executed successfully):

9FinalExecution

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Execute INSERT, UPDATE, DELETE statement together with MERGE statement in SQL Server

MergeExecute INSERT, UPDATE, DELETE statement together with MERGE statement in SQL Server

I used to review new stored procedure which is going to be live. Recently I have seen that one of the developers has developed one stored procedure which had logic of inserting records if it doesn’t exists already and update some value if record is already exists. He has used complex logic with IF…ELSE and SELECT…EXISTS. Since we are having SQL Server 2008 R2 in that particular server, it is not need to develop complex logic but we can simply use MERGE statement which was introduced from SQL Server 2008.

MERGE statement was one of the very good enhancements in TSQL in SQL Server 2008 though people don’t use to use it much due to unawareness.

MERGE statement will allow you to use any combination of INSERT, UPDATE & DELETE with simple MERGE statement and without having any complex logic which used to have till SQL Server 2005. Apart from that MERGE statement read and process data at once so that you reduce roundtrip of the server which ultimately enhances the performance.

[sourcecode language=”sql”]

USE AdventureWorks2012
GO
MERGE sales.SalesOrderDetail AS SOD
USING
(SELECT SalesOrderID,CustomerID,DueDate FROM Sales.SalesOrderHeader) AS SOH
ON SOD.SalesOrderID=SOH.SalesOrderID
WHEN MATCHED AND SOH.SalesOrderID=43659 AND SOD.ProductID=776 THEN DELETE
WHEN MATCHED AND SOH.SalesOrderID=43659 AND SOD.ProductID=777 THEN UPDATE SET SOD.OrderQty=11
WHEN NOT MATCHED THEN INSERT(SalesOrderID,CarrierTrackingNumber,OrderQty,ProductID)
VALUES(SOH.SalesOrderID,’4911-403C-98′,11,776);
GO
[/sourcecode]

Above query will delete one record from SalesOrderDetail table and Update one record in SalesOrderDetail table in AdventureWorks database. It won’t insert any record as there is no record which is exists in SaleOrderHeader table which doesn’t have child entry in SalesOrderDetails table.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

DISTINCT keyword and query performance in SQL Server

0UniqueDISTINCT keyword and query performance in SQL Server

DISTINCT is really useful and handy whenever you wanted to have distinct value in your result set but nothing comes free and DISTINCT keyword is not an exception. If your database is properly normalized, you face less issue to use DISTINCT. DISTINCT and performance of SELECT query is long debated topic and today I come up with my findings and experience with DISTINCT.

I have also seen some myth hovering around DISTINCT. I have seen few very popular blog post which suggest to use GROUP BY or sub query or EXISTS over the DISTINCT keyword to gain performance over DISTINCT keyword but personally I don’t believe it. SQL Server is very smart and it always try to find best suited execution plan for your query. BTW, I will not give name of those blog post and author, however if you are eager then you can search google with terms something like “speed up DISTINCT query” or “performance of DISTINCT in SQL Server” or anything else which has “DISTINCT” and “Performance” together.

Anyway, let us move on and find the effect of DISTINCT keyword on SELECT query. I have prepared demonstration in AdventureWorks2012 database so either download it from CODEPLEX website or use your own database by changing table name in the queries.

Here are two simple SELECT query with and without DISTINCT. You can see that query with DISTINCT keyword uses more resource.

[sourcecode language=”sql”]
–It is HIGHLY recommended that, DON’T use following two DBCC
–commnad on production environment. this is just for testing
–purpose in developement/test environent
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

SELECT DISTINCT SalesOrderID FROM Sales.SalesOrderDetail
GO

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

SELECT SalesOrderID FROM Sales.SalesOrderDetail
GO
[/sourcecode]

Here is the screen capture of execution plan for both queries we have executed above:

1SimpleSelectwithDistinct

Query with DISTINCT keyword is taking 77% of total batch execution time whereas without SELECT returning more row though it takes only 23%. I have tried to get unique SalesOrderID from SalesOrderDetail table, I should have get it from SalesOrderHeader table as there would be less records with unique SalesOrderID. This query could be considered as bad designed query.

Now let us see whether there is any performance enhancement of GROUP BY or Sub Query or Exists or IN / NOT IN over DISTINCT key words.

Here are some different queries which returns same data. I have used DBCC command before each query so that previously created execution plan won’t be used.

[sourcecode language=”sql”]
–It is HIGHLY recommended that, DON’T use following two DBCC
–commnad on production environment. this is just for testing
–purpose in developement/test environent
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

SELECT DISTINCT SO.SalesOrderID,SH.DueDate,SH.TotalDue FROM
Sales.SalesOrderHeader AS SH INNER JOIN
Sales.SalesOrderDetail AS SO
ON SH.SalesOrderID=so.SalesOrderID
WHERE SO.ProductID=776
GO

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

SELECT SH.SalesOrderID,SH.DueDate,SH.TotalDue FROM
Sales.SalesOrderHeader AS SH
WHERE EXISTS
(SELECT 1 FROM Sales.SalesOrderDetail AS SO WHERE SO.SalesOrderID=SH.SalesOrderID AND SO.ProductID=776)
GO

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

SELECT SH.SalesOrderID,SH.DueDate,SH.TotalDue FROM
Sales.SalesOrderHeader AS SH
WHERE SH.SalesOrderID IN
(SELECT SO.SalesOrderID FROM Sales.SalesOrderDetail AS SO WHERE SO.ProductID=776)
GO

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

SELECT SO.SalesOrderID,SH.DueDate,SH.TotalDue FROM
Sales.SalesOrderHeader AS SH INNER JOIN
Sales.SalesOrderDetail AS SO
ON SH.SalesOrderID=so.SalesOrderID
WHERE SO.ProductID=776
GROUP BY SO.SalesOrderID, SH.DueDate,SH.TotalDue
GO
[/sourcecode]

You can see each of the query above will return same number of row with exact same data, moreover there is no performance enhancement. No matter what condition or keyword you use, SQL Server will choose best suited plan for executing your query. You can confirm this by looking at following screen capture of execution plan.

2SELECTwithDISTINCTandOtherOption

If you like this article, do like “Extreme-Advicepage in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Attach MDF file without LDF file in SQL Server

attachAttach MDF file without LDF file in SQL Server

I mostly use sample database AdventureWorks to demonstrate my articles which is freely provided by Micrsoft, you can have it from CodePlex site. CodePlex site provides only .MDF file for database without transaction log file .LDF.

Recently one of the regular blog reader asked me in chat that how he can attach .MDF file since he doesn’t have .LDF file, is it bug in CodePlex site or what?

Well this is not a bug or issue in CodePlex site, you can surely attach MDF file without LDF file. I sent him following command which can create database for you without LDF file.

[sourcecode language=”sql”]
USE [MASTER]
GO

CREATE DATABASE [AdventureWorksDW2012] ON
( FILENAME = N’D:\Databases\AdventureWorksDW2012_Data.mdf’ )
FOR ATTACH_REBUILD_LOG
GO
[/sourcecode]

As soon as I sent him above command, he came to me again with error. Here is the error he has encountered.

[sourcecode language=”sql”]
File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW2012_log.ldf" may be incorrect.
The log cannot be rebuilt when the primary file is read-only.
Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘AdventureWorksDW2012’. CREATE DATABASE is aborted.
[/sourcecode]

Well this is really misleading error message. He has kept his MDF file in “D:\Databases” folder so I told him to check whether SQL Server Service login has permission to access “Databases” folder or not as this is permission issue. As soon as he has provided proper permission on Databases folder, he was able to execute “CREATE DATABASE” command I have provided. He got succeed and got the following success message.

[sourcecode language=”sql”]
File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW2012_log.ldf" may be incorrect.
New log file ‘D:\Databases\AdventureWorksDW2012_log.ldf’ was created.
[/sourcecode]

This is how you can attach MDF file without LDF file in SQL Server.

If you like this article, do like “Extreme-Advicepage in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Comparing UNION and UNION ALL operator in SQL Server

Comparing UNION and UNION ALL operator in SQL Server

It happens many-time that we need a single result set by the combination of two or more result set in SQL Server. UNION operator comes as a handy tool in this situation.

I have often seen that people tend to use UNION though there UNION ALL can work in that situation. Do you think twice before making a choice between UNION and UNION ALL? Or use UNION always, just to save some (three “ALL”) key stroke???

UNION and UNION ALL has different purpose and that is why those have to be used wisely because it affect performance of the overall query.

Before we go further, let us see what UNION and UNION ALL can do for us.

UNION: This operator can combine two or more resultset into one single resultset and remove duplicate records to show distinct result.

UNION ALL: This operator can combine two or more resultset into one single resultset but don’t remove duplicate records to show distinct result and hence if there is any duplication in result sets, you will get more number rows.

There are some rules to follow in order to use either of these operator.

1.) COMPUTE and ORDER BY clause can be applied for over all resultset, not for individual SELECT statement part of UNION operator

2.) GROUP BY and HAVING clause can work with individual SELECT statement, not with overall resultset

3.) Each SELECT statement participated in UNION (ALL) operator suppose to have same number of column with compatible data types.

4.) Column name or alias given in the first SELECT statement would be used as a column heading in final resultset.

Well, after having an idea of UNION and UNION ALL, let us now look at the TSQL script to know how we can practically use it.

Create two sample tables with following script:

[sourcecode language=”sql”]
USE AdventureWorks2012
GO

CREATE TABLE UnionTest1
(
ID INT IDENTITY(1,1)
,Name VARCHAR(25)
)
GO

CREATE TABLE UnionTest2
(
ID INT IDENTITY(1,1)
,Name VARCHAR(25)
)
GO

INSERT INTO UnionTest1 VALUES
(‘Ritesh Shah’),
(‘Rajan Shah’)
GO

INSERT INTO UnionTest2 VALUES
(‘Ritesh Shah’),
(‘Bihag Thakar’),
(‘Bhushan Shah’)
GO
[/sourcecode]

Now execute following two SELECT query (keep your execution plan on query) out of which first query is using UNION and second query is using UNION ALL. Please note that we have total 4 unique name in both table.

[sourcecode language=”sql”]
USE AdventureWorks2012
GO

SELECT Name FROM UnionTest1
UNION
SELECT Name FROM UnionTest2
GO

SELECT Name FROM UnionTest1
UNION ALL
SELECT Name FROM UnionTest2
GO
[/sourcecode]

Here is the resulset I have received by executing above query:

Union ResultSet

UNION did the DISTINCT internally and hence return only 4 unique name in first resultset whereas UNION ALL return all 5 names.

Now, let us have a look at execution plan to find query cost.

UnionExecutionPlan

If you look at the query cost, UNION has taken 73% cost of total batch execution whereas UNION ALL has taken remaining 27% only.

Thus it is proved that UNION is adding overhead and taking more time to execute. UNION should be used as a last resort. If database is properly normalized, you don’t need UNION. If there is a situation that your result set returning duplicate record, you should find a way to eliminate it, if possible, rather then taking a easy solution of using UNION.

If you like this article, do like “Extreme-Advicepage in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Logical Query Processing in SQL Server

processingLogical Query Processing in SQL Server

I am active online community member and try to help more and more people via my blog and some other forums like Experts-Exchange.com (EE), Asp.net, MSDN etc. in my leisure time. I have recently seen one question in EE where one guy was asking that column alias is not working in his WHERE clause. Obviously, column alias of SELECT clause won’t work in WHERE clause as well as in JOIN clause due to execution order. SQL Server has its own way to execute your SELECT query and if you don’t know how it works, you may fall into this kind of issue. After seeing that question in EE, I decided to write this post.

You SELECT query might consists of SELECT, WHERE, JOIN, GROUP BY etc. clauses in it but SQL Server follows the following order to execute your query.

[sourcecode language=”sql”]
FROM
ON
OUTER
WHERE
GROUP BY
ROLLUP | CUBE
HAVING
SELECT
DISTINCT
ORDER BY
TOP
[/sourcecode]

If you can see “WHERE” clause in in above list then you can find it at 4th position whereas SQL Server know about the column alias you have used in 4th from last position. This is the reason that you can’t get column alias in WHERE clause but you do get it in ORDER BY clause. Let us see it practically by executing simple SELECT query in AdventureWorks2012 database.

[sourcecode language=”sql”]

USE AdventureWorks2012

GO

SELECT

SalesOrderID AS SOID

,SalesOrderDetailID AS SODID

,CarrierTrackingNumber

,OrderQty

FROM

sales.SalesOrderDetail

WHERE

SOID=43659

–you will be greeted with error something like this:
–Msg 207, Level 16, State 1, Line 2
–Invalid column name ‘SOID’.
[/sourcecode]

Following query would work as we have proper column name in WHERE clause whereas alias in ORDER BY clause.

[sourcecode language=”sql”]
USE AdventureWorks2012
GO

SELECT
SalesOrderID AS SOID
,SalesOrderDetailID AS SODID
,CarrierTrackingNumber
,OrderQty
FROM
sales.SalesOrderDetail
WHERE
SalesOrderID=43659
ORDER BY SOID
[/sourcecode]

If you like this article, do like “Extreme-Advicepage in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

CXPACKET wait stats in SQL Server

WaitStatCXPACKET wait stats in SQL Server

“Wait” is one of the very familiar word in human’s life. Nobody likes to wait though it is not always bad to wait for a while for something good, at the same time long unnecessary waiting period resulted into frustration.

When I am about to finish my day in office, I always gets eager to meet my 2 year old son “Teerth”. I enjoy this wait period, if I would be with him for day, I wouldn’t get pleasure of meeting him at the end of day, though I enjoy his company whole day.

I don’t like to wait for upload/download something due to slow internet speed, it is unproductive and unnecessary wait time. Who likes to wait for 1 minute video on YouTube.com gets 5 minutes to load (streaming)?

This is how I evaluate wait period and SQL Server apply almost same kind of rule. It is up-to you to know wait stat, identify whether it is good (necessary) or bad and resolve it if possible.

In simple words, you would like to see result of your request immediately. You expect, you execute query in SQL Server and it finishes operation immediately. In normal scenario SQL Server even do it but there are many cases and real time scenario which wouldn’t let operation go smooth.

There are so many reasons which makes your query waiting, whether it is memory issue, CPU pressure, blocking, locking, dead locking to name a few among so many.

There is one DMV which you can use to monitor wait stats. “sys.dm_os_wait_stats” is your weapon to deal with wait stats.

You can execute simple SELECT query on “sys.dm_os_wait_stats” to see all wait stats along with the value in your system.

Yesterday I got an email from one of the reader this blog, he was asking that he is facing big values in CXPACKET wait stats. Is it good or bad? If bad, how can I can resolve it? This email from the reader insist me to write this blog post.

As per MSDN, CXPACKET occurs when trying to synchronize the query processor exchange iterator. You may consider lowering the degree of parallelism if contention on this wait type becomes a problem. To find detail about different wait stats, click here.

Generally parallelism is being used to execute your process so for example, if you have executed SELECT query and four processor is executing your SELECT query now three processor has finished their part of job but those processors can’t show you the result until last one processor return the result because request result of SELECT query can only be completed if all four processors return result and clubbed together. Since your last processor was still working, three processors had to wait which can raise CXPACKET wait stat.

To overcome this problem, you can decrease value of MAXDOP or Max degree of parallelism. I have written article on this topic earlier which you can refer by following link:

““Max degree of parallelism” or “MAXDOP”, good guy or bad guy?

Note: This is just a generic advice to handle CXPACKET wait type. For more accurate implementation, need to study the environment in detail. Generally in Online Transactional Pr0cessing (OLTP) should have low number of value (near to zero) for MAXDOP as it used to have quick and short transaction and Online Analytical Processing (OLAP) should have higher number of MAXDOP.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.