SQL server Database high performance queues

We have Queues everywhere. There are queues for asynchronously sending notifications like email and SMS in most websites. E-Commerce sites have queues for storing orders, processing and dispatching them. Factory Assembly line automation systems have queues for running tasks in parallel, in a certain order. Queue is a widely used data structure that sometimes have to be created in a database instead of using specialized queue technologies like MSMQ. Running a high performance and highly scalable queue using database technologies is a big challenge and it’s hard to maintain when the queue starts to get millions of rows queue and dequeued per day. Let me show you some common design mistakes made in designing Queue-like tables and how to get maximum performance and scalability from a queue implemented using simple database features.

Let’s first identify the challenges you have in such queue tables:

• The table is both read and write. Thus queuing and dequeuing impact each other and cause lock contention, transaction deadlocks, IO timeouts, etc. under heavy load.
• When multiple receivers try to read from the same queue, they randomly get duplicate items picked out of the queue, thus resulting in duplicate processing. You need to implement some kind of high performance row lock on the queue so that the same item never gets picked up by concurrent receivers.
• The Queue table needs to store rows in a certain order and read in a certain order, which is an index design challenge. It’s not always first in and first out. Sometimes Orders have higher priority and need to be processed regardless of when they are queued.
• The Queue table needs to store serialized objects in XML or binary form, which becomes a storage and index rebuild challenge. You can’t rebuild index on the Queue table because it contains text and/or binary fields. Thus the tables keep getting slower and slower every day and eventually queries start timing out until you take a downtime and rebuild the indexes.
• During dequeue, a batch of rows are selected, updated and then returned for processing. You have a “State” column that defines the state of the items. During dequeue, you select items of certain state. Now State only has a small set of values, e.g. PENDING, PROCESSING, PROCESSED, ARCHIVED. As a result, you cannot create index on “State” column because that does not give you enough selectivity. There can be thousands of rows having the same state. As a result, any dequeue operation results in a clustered index scan that’s both CPU and IO intensive and produces lock contention.
• During dequeue, you cannot just remove the rows from table because that causes fragmentation in the table. Moreover, you need to retry orders/jobs/notification N times in case they fail on the first attempt. This means rows are stored for longer period, indexes keep growing and dequeue gets slower day by day.
• You have to archive processed items from the Queue table to a different table or database, in order to keep the main Queue table small. That means moving large amount of rows of some particular status to another database. Such large data removal leaves the table highly defragmented causing poor queue/dequeue performance.
• You have a 24x7 business. You have no maintenance window where you can take a downtime and archive large number of rows. This means you have to continuously archive rows without affecting production queue-dequeue traffic.

Linked lists Vs List

string[] dataSet = new string[100];for (int i = 0; i < 99; i++){    dataSet[i] = string.Format("Sample Data {0}", i.ToString());}

This code allocates space for 100 string items and fills each one of them with a string. However, there are a few drawbacks in the regular array. What if there are more than 100 items used later on? The array needs to be extended, and if it was hardcoded to a hundred values, there could be some problems with overflowing data. Another problem is the inefficient memory usage when there are less than a hundred elements. In that case, a lot of memory is kept unused.

Linked lists tend to solve the two of the above problems. First of all, a linked list doesn’t have a single field for a value, but rather two – one with the stored data and one referencing the next value. These two fields together build a node, that is – a unit inside the linked list.

Difference between Read Uncommitted and Nolock - SQL server

The read-uncommitted isolation level is the least restrictive isolation level within SQL Server, which is also what makes it popular for developers when looking to reduce blocking.

The nolock table hint behind the scenes performs the exact same action as running under the read-uncommitted isolation level.

The only difference between the two is that the read-uncommitted isolation level determines the locking mechanism for the entire connection and the nolock table hint determines the locking mechanism for the table that you give the hint to.

Sample:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT *
FROM Table1 T1
INNER JOIN Table2 T2 ON T1.ID = T2.id

It is functionally equivalent to:

SELECT *
FROM Table1 T1 WITH(NOLOCK)
INNER JOIN Table2 T2 WITH(NOLOCK) ON T1.ID = T2.ID

Query to search on specific string in database procedures

select name,OBJECT_DEFINITION(object_id) from sys.procedures
where OBJECT_DEFINITION(object_id) like '%With(Index%'

SQl Query to find out the unused Indexs in SQl server


SELECT object_name(i.object_id) AS TableName, i.name AS [Unused Index]
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s ON s.object_id = i.object_id
      AND i.index_id = s.index_id
      AND s.database_id = db_id()
WHERE objectproperty(i.object_id, 'IsIndexable') = 1
AND objectproperty(i.object_id, 'IsIndexed') = 1
AND s.index_id is null
OR (s.user_updates > 0 and s.user_seeks = 0 and s.user_scans = 0 and s.user_lookups = 0)
ORDER BY object_name(i.object_id) ASC

Adding XML config files along with package using Installshield 2016

> Installation Designer Tab> Left panel -> System Configuration

* Create a component for your existing development XML file and include the XML file in it. Set all of the attributes for the file properly (key file, target location, etc.)

*  Switch to XML view and use the import wizard. Only import the keys you want to change

* Once the wizard is done, select the file itself and set the XML File Destination to match the XML file you're modifying.

* Change the search patterns for the keys you want to modify. For some reason, the IDE shows this as the Element Name - this is actually the XPath query to find the element. 

* Delete anything in the attributes section that you aren't trying to change

*  Set the Value of your attribute as necessary  and set the operation to Append Value.

* Switch to advanced view and make sure that only "Update first matching element only" is checked (unless you want to replace multiple, identical elements )

Delegates Real Example.....

static void Main()
{
var mycar = new Car(20, 100, "baachi");
mycar.RegisterWithCarEngine(OnCarEngineEvent);
//NOW SPEED UP THE CAR
for (int i = 0; i < 6; i++)
{
mycar.AccelerateSpeed(20);
Console.ReadLine();
}
}
public class Car
{
#region Properties
public string PetName { get; set; }
public int MaximumSpeed { get; set; }
public int CurrentSpeed { get; set; }
#endregion
public Car()
{
MaximumSpeed = 100;
}
public Car(int currentSpeed, int maximumSpeed, string petName)
{
CurrentSpeed = currentSpeed;
MaximumSpeed = maximumSpeed;
PetName = petName;
}
//Define delegate
public delegate void CarEngineHandler(string msgForCaller);
private CarEngineHandler _listOfHandlers;
private bool carIsDead;
//registration function for the caller
public void RegisterWithCarEngine(CarEngineHandler methodToCall)
{
_listOfHandlers = methodToCall;
}
public void AccelerateSpeed(int speed)
{
//If this car is dead Send dead message.......
if (carIsDead)
{
if (_listOfHandlers != null)
{
_listOfHandlers("Sorry BOSS : Car Is Dead: OOOOPS");
}
}
else
{
CurrentSpeed += speed;
// If this car is almost dead,
if (MaximumSpeed - CurrentSpeed == 10 && _listOfHandlers != null)
{
_listOfHandlers("Carefull Buddy: Gonna Blow:::");
}
}
if (CurrentSpeed > MaximumSpeed)
{
carIsDead = true;
}
else
{
Console.WriteLine("Current Speed is :{0}", CurrentSpeed);
}
}
}