Ahmadreza's Notes

Imagine you have multiple instances of SQL server on your server and one of them is using almost all of available memory and second one is facing memory shortage. What are possible Cause for this symptom. Does one of application have memory leak?

The answer is NO, When you install multiple instances of SQL server on a single server you have to consider memory allocation for each instances because windows does not balance memory across applications with the memory notification.

The first instance with a work load will  used huge portion of memory (Especially when you have actual data – not testing – on that instance).

Three approaches are available for Server Memory Option documented in the section “Sunning Multiple Instances of SQL server” and if you have selected third one which is “Do nothing”, you might have same problem.

• Do nothing (not recommended). The first instances presented with a workload will tend to allocate all of memory. Idle instances or instances started later may end up running with only a minimal amount of memory available. SQL Server makes no attempt to balance memory usage across instances. All instances will, however, respond to Windows Memory Notification signals to adjust the size of their buffer pools. As of Windows Server 2003 SP1, Windows does not balance memory across applications with the Memory Notification API. It merely provides global feedback as to the availability of memory on the system.

Recently I founded that LINQ-To-SQL Uses magic ROW_NUMBER() function. ROW_NUMBER() function is a magic function which was added in SQL Server 2005. Microsoft put this function in version 2005 so that developers will not take it for granted and appreciate it. ROW_NUMBER "returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition".

ROW_NUMBER() Syntax:

ROW_NUMBER ( )     OVER ( [ ] <order_by_clause> )

For example following query add a number field which is partitioned by ProductID (reset on new ProductID) in descending order on UnitPrice * OrderQty.

SELECT ROW_NUMBER()
OVER (Partition by ProductID ORDER BY UnitPrice * OrderQty DESC) AS ROWNUM,*
FROM Sales.SalesOrderDetail

ROW_NUMBER() helps programmer to select specified amount of rows within a select command. This feature commonly used in paging. Following example returns @P1^th to @P2^th rows which list is ordered by ProductId.

SELECT * FROM
SELECT *, ROW_NUMBER() OVER (ORDER BY ProductID) AS ROWNUMBER
FROM Sales.SalesOrderDetail) AS ALLDATA
WHERE ALLDATA.ROWNUMBER  BETWEEN @P1 and @P2

Skip() and Take()LINQ-To-SQL functions generate ROW_NUMBER syntax in query result. I created a LINQ-TO-SQL dbml file on AdventureWorks. I selected SalesOrderDetail as Data Class.

            AdventureWorksDataContext advDC = new AdventureWorksDataContext();
 
            IQueryable orderDetails = advDC.SalesOrderDetails.OrderBy(f => f.SalesOrderDetailID)
                                                             .Skip(20).Take(10);
 
            foreach (SalesOrderDetail orderDetail in orderDetails)
                Console.WriteLine(orderDetail.ProductID);
            Console.ReadLine();

Above code generates following SQL for orderDetails:

 {SELECT [t1].[SalesOrderID], [t1].[SalesOrderDetailID], [t1].[CarrierTrackingNumber], [t1].[OrderQty], 
	[t1].[ProductID], [t1].[SpecialOfferID], [t1].[UnitPrice], [t1].[UnitPriceDiscount], 
	[t1].[LineTotal], [t1].[rowguid], [t1].[ModifiedDate]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[SalesOrderDetailID]) AS [ROW_NUMBER], 
			[t0].[SalesOrderID], [t0].[SalesOrderDetailID], [t0].[CarrierTrackingNumber], 
			[t0].[OrderQty], [t0].[ProductID], [t0].[SpecialOfferID], [t0].[UnitPrice], 
			[t0].[UnitPriceDiscount], [t0].[LineTotal], [t0].[rowguid], [t0].[ModifiedDate]
    FROM [Sales].[SalesOrderDetail] AS [t0]
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
ORDER BY [t1].[ROW_NUMBER]
}

As you see, Skip and Take functions interpreted as ROW_NUMBER() function.