About Me

My Photo
Mumbai, Maharastra, India
He has more than 4.2 years of experience in the software development. He has spent most of the times in web/desktop application development. He has sound knowledge in the ASP.Net, C#, VB,SQL Server, AJAX. You can reach him at viki_keshari@yahoo.co.in or viki.keshari@gmail.com http://www.facebook.com/viki.keshari

Monday, January 26, 2015

Difference between COUNT and BIG_COUNT from the angle of execution plan


We all know when we need to find the the count of rows in a table which exceed the int datatype range, we use BIG_COUNT instead of COUNT, which has return type bigint unlike int in case of COUNT.

Let’s see from the prospective of execution plan, how it differ in plan

select count(*) from Production.ProductCategory pc 



Lets see the text plan to see, what optimizer in detail did to execute the above query.

SET STATISTICS PROFILE ON
select count(*) from Production.ProductCategory pc 



Again a very simple plan, lets go through with each step to see how optimizer executes the query. First step, all rows are read from NonClustered Index Scan, then Stream Aggregate counts the number of rows in the record set and place the record count in Expr1002. Then Compute Scalar Operator takes the record count stored in Expr1002 and convert it to INT Datatype and kept the result in Expr1001.

The output of the Stream Aggregate operator  is a BigInt, but as we know the COUNT aggregate function returns value of type INT, so it use Compute scalar to convert result of count in INT data type. You may remember that, in order to count BigInt values, you should use the COUNT_BIG function. If you change the query above to use the COUNT_BIG, then you will see that the plan no longer uses the compute scalar operator

Lets check out the same query with BIG_COUNT.
select count_big(*) from Production.ProductCategory pc 

 
Here we can see there is no Compute scalar operator in the execution plan. Lets see now whether this plans makes any difference in cost compared to query using COUNT.



Here if we compare the final tree cost the query using BIG_Count has 0.0032893 where as query using COUNT has  0.0032893. So it came out as same. So it makes no or minimal cost change while using count vs big_count.

Conclusion: BIG_COUNT don’t use compute Scalar Operator because the Stream Aggregate operator of COUNT returns result in BIG INT datatype where as in case of COUNT function, since it has return type of INT, the compute scalar operator is used to convert the result returned from  Stream Aggregate from big int to int.
 
SQL Server with a Tea glass makes a perfect combination to deal with darkness :)


Post Reference: Vikram Aristocratic Elfin Share

Saturday, January 24, 2015

Blocking Operator and Non Blocking Operator

Non Blocking Operator: A non blocking operator is one which reads one row from its previous operator and returns the read; Nested loop is a prime example of non blocking operator.

select top 10 pc.ProductCategoryID,pc.Name, psc.ProductSubcategoryID, psc.Name from Production.ProductCategory pc
inner join Production.ProductSubcategory psc
on pc.ProductCategoryID = psc.ProductCategoryID



Blocking Operator: A blocking operator needs to read all the rows from its previous operator to perform  and then return the data. A classic sample of a blocking operator is the SORT operator; it needs to read all rows, sort the data and then return the ordered rows. The execution of the query will wait until all rows to be read and ordered, before continuing with the command

select distinct ProductCategoryID from Production.ProductSubcategory



Here we can see the blocking sort operator reads all rows from clustered index then rearrange the records in ascending fashion and then gives the output to subsequent operator.

Thing starts work, when you are with me… SQL Server  :)


Post Reference: Vikram Aristocratic Elfin Share

Tuesday, January 20, 2015

Index vs Sort Operator, how we can remove sort operator from execution plan


Here in this article we will see how we sort operator is performing in a challenge with index scanning operator, lets create platform for executing this test.

Here we are creating table Tab_EmployeeAvoidingSort which has no index defined on any column.

set nocount on
if object_id('Tab_EmployeeAvoidingSort') is not null
drop table Tab_EmployeeAvoidingSort
go

create table Tab_EmployeeAvoidingSort
(id int identity(1,1),
name varchar(30),
salary numeric(18,2));
go

Lets insert some set of data to the table, say 1000

declare @i smallint
set @i = 0
while @i < 1000
begin
insert into Tab_EmployeeAvoidingSort(name, salary)
select 'Employee', abs(convert(numeric(18,2), (checksum(newid()) / 500000.0)))
set @i = @i + 1
end

Our table is ready to do the test on sort operation. Lets on the Statistic profiler to track the IO and CPU and total cost taken by query involving sort operator.

SET STATISTICS PROFILE ON
Select name from Tab_EmployeeAvoidingSort order by name



Here we can see the cost for execution involving the sort operator came out to 0.0035.

Lets create index on name column on name column.

create nonclustered index ix_name on Tab_EmployeeAvoidingSort(name)
go
Command(s) completed successfully.

Index is all set, lets try out the same query to see what cost it involves

SET STATISTICS PROFILE ON
select name from Tab_EmployeeAvoidingSort with (index = ix_name) order by name



Oh!!! Great, total cost of query comes out as 0.0066, which is much lesser then the one involving sort operator.
  
Conclusion: Try to implement index on order by column.

Get your tea and start your work :)

Post Reference: Vikram Aristocratic Elfin Share

Saturday, December 27, 2014

Utility generating DML statement taking just table name; smarter way of doing your work


DML statements are those which you need to start any kind of manipulation of your data; these are the statements which you use very often employ in your stored procedure to build up any Business Logic.

Just think about any table which has say 50 odd column which is very common, now writing any kind of DML statement say SELECT statement, how long it takes, probably 1 or 2 or say for if you r not taking help of sys.columns 3-4 minutes.

Now think about how many DML statements you cover in any business logic SP, yup a lot!!! Now imagine about an utility which in a second produce your required DML statement.

Fascinated about the idea, let start writing a routine which will make our daily work ease

IF OBJECT_ID('dbo.usp_getDMLText') IS NOT NULL
BEGIN 
       DROP PROCEDURE dbo.usp_getDMLText
END
GO

CREATE PROCEDURE dbo.usp_getDMLText (@TblName VARCHAR(50))
AS
DECLARE @designSelect VARCHAR(max)
DECLARE @designUpdate VARCHAR(max)
DECLARE @designDelete VARCHAR(max)
DECLARE @designDrop VARCHAR(max)
DECLARE @designTruncate VARCHAR(max)
DECLARE @schemaName VARCHAR(255)

BEGIN
       IF OBJECT_ID('tempdb.#TAB_DMLStatement') IS NOT NULL
              DROP TABLE #TAB_DMLStatement

       CREATE TABLE #TAB_DMLStatement (
              Id INT identity(1, 1)
              ,Operation VARCHAR(10)
              ,[Text] VARCHAR(max)
              )

       SELECT TOP 1 @schemaName = s.NAME
       FROM sys.tables t
       INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
       WHERE t.NAME LIKE 'Address'

       SET @schemaName = @schemaName + '.'

       SELECT @designSelect = substring((
                           SELECT (', ' + Column_Name + CHAR(10))
                           FROM INFORMATION_SCHEMA.COLUMNS
                           WHERE Table_Name = @TblName
                           FOR XML PATH('')
                           ), 3, 1000)

       SET @designSelect = 'SELECT ' + @designSelect + ' FROM ' + @schemaName + @TblName

       INSERT INTO #TAB_DMLStatement
       VALUES (
              'SELECT'
              ,@designSelect
              )

       SELECT @designUpdate = substring((
                           SELECT (', ' + Column_Name + '= @p' + Column_Name + CHAR(10))
                           FROM INFORMATION_SCHEMA.COLUMNS
                           WHERE Table_Name = 'Address'
                           FOR XML PATH('')
                           ), 3, 1000)

       SET @designDelete = @designUpdate
       SET @designUpdate = 'UPDATE ' + @schemaName + @TblName + CHAR(10) + 'SET ' + @designUpdate + CHAR(10) + 'WHERE ' + REPLACE(@designUpdate, ',', ' AND ')

       INSERT INTO #TAB_DMLStatement
       VALUES (
              'UPDATE'
              ,@designUpdate
              )

       SET @designDelete = 'DELETE ' + @TblName + CHAR(10) + 'WHERE ' + REPLACE(@designDelete, ',', ' AND ')

       INSERT INTO #TAB_DMLStatement
       VALUES (
              'DELETE'
              ,@designDelete
              )

       SET @designDrop = 'DROP TABLE ' + @schemaName + @TblName

       INSERT INTO #TAB_DMLStatement
       VALUES (
              'DROP'
              ,@designDrop
              )

       SET @designTruncate = 'TRUNCATE TABLE ' + @schemaName + @TblName

       INSERT INTO #TAB_DMLStatement
       VALUES (
              'TRUNCATE'
              ,@designTruncate
              )

       SELECT Id
              ,Operation
              ,TEXT
       FROM #TAB_DMLStatement

       DROP TABLE #TAB_DMLStatement
END

Now you have here a routine which will generate DML statement for you taking just table name.

Lets configure it to shortcut key
For this Go to your SSMS
TOOL à OPTION à Environment à Keyboard àQuery Shortut à in Ctrl + F1 text box write your routine name i.e usp_getDMLText

Now our shortcut is set.
Now just take any table name select it and press Ctrl + F1
Eg Address




That’s all J so simple, Guys if you have any other idea, do coin up, will try to implement :)

I am happy even if I don’t get food for my daily meal, till you are with me; my SQL Server  :)

 

Post Reference: Vikram Aristocratic Elfin Share