Sunday, November 16, 2014

SQL Server table as a Queue with SQL Hints

Queues can be implemented in SQL Sever using Tables. Consider this scenario, we have 1 Producer and N number of consumers which pick up the job and Execute it. There are issues with this that needs to be take care of

There are 3 concurrency issues we have to keep on eye on in any asynchronous application in Sql Server, namely

1) Dirty Reads  ( Doesn't happen in READ COMMITTED isolation level)
2) Non repeatable Reads (Can happen in READ COMMITTED isolation level)
3) Phantom Reads  ( Can Happen in READ COMMITTED isolation level)

In this scenario, the issue is

1) Consumers can pickup the same job and start executing it, without knowing other consumer has picked up the job.

CREATE DATABASE YashTempDb
GO
DROP TABLE [Queue]
GO
Create Table [Queue]
(
JobId INT IDENTITY PRIMARY KEY,
JobName NVARCHAR(256),
IsExecuting bit,
LastExecutionTime DATETIME2
)
GO
INSERT INTO Queue(JobName,IsExecuting,LastExecutionTime)
VALUES
('One',0,DateAdd(minute,0,GETDATE())),
('Two',0,DateAdd(minute,-1,GETDATE())),
('Three',0,DateAdd(minute,-2,GETDATE())),
('Four',0,DateAdd(minute,-3,GETDATE())),
('Five',0,DateAdd(minute,-4,GETDATE())),
('Six',0,DateAdd(minute,-5,GETDATE())),
('Seven',0,DateAdd(minute,-6,GETDATE())),
('Eight',0,DateAdd(minute,-7,GETDATE())),
('Nine',0,DateAdd(minute,-8,GETDATE())),
('Ten',0,DateAdd(minute,-9,GETDATE()))
GO
Declare @JobId INT
Declare @Count INT
SET @Count = 0;

WHILE (@Count < 5)
BEGIN
BEGIN TRAN
  SET NOCOUNT ON

   -- Pick up a job 
   SET @JobId = (Select TOP 1 JobId from [Queue] WITH (ROWLOCK,READPAST)  WHERE IsExecuting = 0 order by LastExecutionTime asc)
   -- Print the pickedup jobId 
   PRINT @JobId
   -- SET THE FLAG TO EXECUTING
   UPDATE [QUEUE] WITH (ROWLOCK)
   SET IsExecuting = 1
   WHERE JobId = @JobId
   PRINT 'SET THE ISEXECTUING FLAG TO EXECUTING'
   -- SIMULATE THE JOB TIME (Move this out of transaction since you dont want to lock the table for that duration)
   WAITFOR DELAY '00:00:10'
   -- Update the JobId after executing it
   UPDATE [Queue] WITH (ROWLOCK)
   SET LastExecutionTime = DateAdd(Minute,1,GETDATE()), IsExecuting = 0
   WHERE JobId = @JobId

   PRINT Concat('COMPLETED ', @JobId)
   SET @Count = @Count + 1
      COMMIT TRAN
END


Using the sql query hint  (ROWLOCK and READPAST), we can gurantee that the same row will not be picked up by the other transaction.

READPAST -- (As per MSDN Documentation)  "Specifies that the Database Engine not read rows that are locked by other transactions. When READPAST is specified, row-level locks are skipped. That is, the Database Engine skips past the rows instead of blocking the current transaction until the locks are released. READPAST is primarily used to reduce locking contention when implementing a work queue that uses a SQL Server table. A queue reader that uses READPAST skips past queue entries locked by other transactions to the next available queue entry, without having to wait until the other transactions release their locks.

Hope this is useful and will help to recude concurrency issues with queues.

Thanks,
Yash

Tuesday, July 29, 2014

Running Total

Often when you are creating reports you want to do the Running Total  eg. Sum = Current Sum + (Sum of all the preceding values).

There are many ways to do this. I just wanted to outline some of the standard techniques used to do this and the new feature in 2012 which makes it very easy and fast.

1) Using Non-Equi Joins

Select A.PhysicalFileId,A.BytesWritten,A.CreatedDate, 
Sum(B.BytesWritten)as RunningTotal from #X A
inner join #X B  on B.PhysicalFileId <= A.PhysicalFileId
group by A.PhysicalFileId, A.BytesWritten,A.CreatedDate
2) My personal favorite -- Correlated Query

Select A.PhysicalFileId, A.BytesWritten,A.CreatedDate , 
(Select Sum(B.BytesWritten) from #X B where B.PhysicalFileId <= A.PhysicalFileId) as RunningTotal
from #X  A
3) Available in SQL 2012 and beyond and fastest of all, using Window Functions

Select A.PhysicalFileId,A.BytesWritten,A.CreatedDate, Sum(A.BytesWritten) Over( 
Order by A.PhysicalFileId
) As RunningTotal from #X AS A

You can find the sql queries at :: http://sqlfiddle.com/#!6/40d6e/3/0

Until next time!

-Yash

Sunday, July 27, 2014

Pivot table and its uses

Recently I started working on a BI project, which is a new domain for me. I always wanted to work in a Business Intelligence project since that was one area I didn't cover. I have been learning lot of new concepts which are interesting. We generate lot of reports which are used by the management teams to make proper business decisions and for that we use Self Service Power BI. If you have done reporting you will know, Pivot table is one of the most fundamental way of generating Cross-Tabular Reports.

Pivot tables are basically used for 2 purpose

1) To generate cross tabular reporting i.e converting the rows into columns.
2) With Entity-Attribute table ( which is a Key-Value kind of table, like a dictionary)

As an example ::  I have 2 table with one to many relationship, which are used to record Api Success/Failure .

[dbo].[ApiLookUp]  (1) 
[dbo].[ApiHealthMonitoring]   (Many)

You can see the Table Schema and Queries here  :: http://sqlfiddle.com/#!6/d5fbf/3

The best way in my opinion to write Pivot query is to follow these steps

1) Write the Base query i.e the table for which you want to convert rows into columns .
     This becomes your Base Query
2) Select the Aggregate Column from one of the columns of the table, created by the Base query, for the columns you want to pivot on.
3) Select the Columns as the output of the  Pivot Query









One thing worth mentioning about Pivot table is  that Columns in the Pivot table need to be hardcoded. So if the values of the Column on which you are pivoting then you need to either change the query frequently or use dynamic sql.

This same thing can be achieved very easily using the Excel - Power Pivot feature. It is very powerful and simple to use. All you need to do, is to import the tables from the Sql Server Database. Add a pivot table and select the appropriate columns. You can even generate interactive graphs and publish it to SharePoint using power query and power pivot. The deployment story for Excel reports is very powerful and easy to use by even non programmers.

You can also add slicers to the table which help in slicing/dicing of the data. Pretty powerful.








Until next time!

-Yash





Sunday, April 6, 2014

Love for Powershell!

This is a quick blog entry, to appreciate my love for powershell. I have been running into Low disk space on my work computer, and I wanted to fix this issue.

Powershell to the rescue!

Simple powershell command and I was able to get the files which is occupying max disk space and then go from there.

Get-ChildItem -Path C:\ -Recurse | Where-Object {$_.PSIsContainer -eq $false} | 
sort-Object -Property Length -Descending |
Select-Object -First 10 | 
Format-List -Property FullName,Length,
@{name = "SizeInMB";expression = {$_.Length / 1MB}}

I have always been a commadline guy and I really enjoy writing small useful scripts!


Thanks,
Yash

Saturday, January 11, 2014

AutoResetEvent And ManualResetEvent


AutoResetEvent and ManualResetEvent are one of the low-level Thread signaling mechanism in C#. The main reason for writing this blog entry is to differentiate between these 2 WaitHandles.

AutoResetEvent is the like the devices you see in Airport parkings, where you punch you card, and then you can get in, but as soon as you get in, entry closes. The next person has to again punch the card.
You can see from the output of the program, there are 2 Threads and both are waiting on a Set Signal. Since only 1 set signal is received, only one thread can resume the operation while the second thread continues to wait.

ManualResetEvent are like a door. Once you open the door, the door is kept open till someone closes it. So in the code below, both theads are waiting on a Set signal to resume there operation. As soon as the Set Singnal is received, both the thread resume there operations.
       
        static void Main(string[] args)
        {
            ManualResetEvent manualResetEvent = new ManualResetEvent(false);
            AutoResetEvent autoResetEvent = new AutoResetEvent(false);

            Thread t1 = new Thread(() =>
            {
                Thread.Sleep(1000); for (int i = 0; i < 10; i++)
                {
                    Console.WriteLine(i);
                    if (i == 5)
                    {
                        Console.WriteLine("Waiting to be signalled -- Thread 1");
                        //manualResetEvent.WaitOne();
                        autoResetEvent.WaitOne();
                        Console.WriteLine("Signal Received, continuing -- Thread 1" );
                    }
                }
            });
            t1.Start();

            Thread t2 = new Thread(() =>
            {
                Thread.Sleep(1000); for (int i = 101; i < 120; i++)
                {
                    Console.WriteLine(i);
                    if (i == 110)
                    {
                        Console.WriteLine("Waiting to be signalled -- Thread 2");
                        //manualResetEvent.WaitOne();
                        autoResetEvent.WaitOne();
                        Console.WriteLine("Signal Received, continuing -- Thread 2");
                    }
                }
            });
            t2.Start();


            Thread.Sleep(1000);
            Console.WriteLine("In the Main function");
            Thread.Sleep(1000);
            //manualResetEvent.Set();
            autoResetEvent.Set();
            //autoResetEvent.Reset();
            Console.ReadLine();
        }

Tuesday, December 10, 2013

Saturday, September 14, 2013

LogParser and IIS Logs

Sometimes in production environment, when you are debugging some issue and you want to get something from a  IIS logs it becomes difficult to get it because you have so many machines in prod.

I use logparser and powershell together to get the results from all the machines like below. Logparser is a great tool and it pretty fast!!.

IIS Logs are stored by the Site Id. i.e if you go to the advancesSettings of a particular website, you can see a ID column. that's how the folders are arranged in IIS log directory.



$machines = @('Yash000226','Yash000227','Yash000228','Yash000229','Yash000220',
'Yash000231','Yash000232','Yash000226')

foreach ($machine in $machines)
{
$path = "\\$($machine)\d$\data\Logs\IISLogs\W3SVC1\u_ex*.log"

.\LogParser.exe -i:IISW3C -e:50 -o:CSV file:..\Query.sql?path=$($path)+url=/admin/services/configdelta.aspx -stats:off >> output.csv
 
}


Queries ::  Put the below query in a separate file.

sc -->  Server to Client
cs --> Client to Server

date  -- Date
time  -- Time
s-ip  -- Server IP Address
 cs-method -- Client to server Method
 cs-uri-stem  -- Client to server uri stem  for eg /a.aspx etc
cs-uri-query  -- Client to Server Uri query string
s-port -- server port
cs-username -- client to server username
 c-ip -- client ip address
cs(User-Agent) -- client user agent string
 sc-status -- server to client HTTP Status code
sc-substatus  -- server to client HTTP Sub status code
sc-win32-status -- server to client win32 status code
 time-taken -- timetaken to process the request

select sc-status as httpstatusCode,sc-substatus as httpSubStatusCode
from %path%
where (to_lowercase(cs-uri-stem) = to_lowercase('%url%'))
Hope it helps someone!

-Yash