Sunday, August 24, 2014

SQL Server Performance Tuning

SQL Server Performance Tuning and health check is a very challenging subject that requires expertise in Database Administration and Database Development. Here are few pointers how one can keep their SQL Server Performance Optimal. I am often asked what can one do keep SQL Server Health Optimal and SQL Server keep on running very smooth.
Here is the quick list of the pointers which one should consider for performance tuning.

Server/Instance Level Configuration Check

Review all the SQL Server/Instance Level settings of the server and tune it based on system workload.

I/O distribution Analysis

Analyze the I/O of your system and decide the best distribution for the I/O load. Perform object level analysis and do performance tuning at table level. Reduce I/O performance bottlenecks and suggest optimal setting for read and write database. This is especially critical for databases that need to sustain heavy updates during peak usage hours.

SQL Server Resource Wait Stats Analysis

Wait Stat Analysis is very crucial for optimizing databases, but is often overlooked due to lack of understanding. Perform advanced resource wait statistics analysis to proactively reduce performance bottleneck.

Index Analysis

Indexes are considered valuable for performance improvements. Analyze all your indexes to identify non-performing indexes and missing indexes that can improve performance.

TempDB Space Review

Review the size and usage of your TempDB database.

Database Files (MDF, NDF) and Log File Inspection

Review all the files and filegroups of each of your databases and analysis them to identify any object or files that are causing bottlenecks.

Fragmentations and Defragmentations

Identify the optimal settings of your database files and tables to reduce fragmentation and reduce them.

Backup and Recovery health Check

Review all backup & recovery settings and procedures of your databases and understand the functions of the respective databases.

Log Reviews (Windows Event Logs, SQL Server Error Logs and Agent Logs)

Logs reveal many hidden facts. Identity the critical errors and resolve them.

Hardware Review

Review the hardware and and verify that it positively impact the performance or scalability of the database.

DBCC Best Practices Implementations

There are few DBCC commands to be avoided and few very crucial for system. Understand the usage of DBCC FREEPROCCACHE, DBCC SRHINKDATABASE, DBCC SHRINKFILE, DBCC DROPCLEANBUFFER, DBCC REINDEX, as well as the usage of few system stored procedures like SP_UPDATESTATS. If you are currently using any of the above mentioned and a few other DBCC maintenance task commands carefully review their usage.

Deadlock Detection and Resolutions Hands-On

Detecting deadlock is not very difficult, but to learn the tips and trick to resolve them requires an understanding of the issue and some experience. Understand the locking mechanism and resolve them.

The number, 197, is called a circular prime because all rotations of the digits: 197, 971, and 719, are themselves prime. There are thirteen such primes below 100: 2, 3, 5, 7, 11, 13, 17, 31, 37, 71, 73, 79, and 97. How many circular primes are there below one million?

At first I thought circular primes were any permutation of the prime, but then 791 would be in the example as well, so I realised that it is primes that you can rotate such that any rotation is also a prime. There is a long article on different groups of prime numbers at Wikipedia called List of prime numbers. That includes circular primes, so for the lazy reader, you can go there and count them instead of programming the solution.
I must admit that I don’t think I have found a very nice solution, but it runs fast enough to satisfy me. It is a brute force algorithm where we search every prime below 1.000.000 to see if it can be rotated.
Circular primes have two characteristics we will use to speed up the search for all the circular primes.  If it contains an even number or 5 then at some point in the cycle we will encounter a number which is not a prime and thus the number we are investigating is not a circular prime. There are two exceptions to this rule, the primes 2 and 5 are indeed primes even if they follow the rule of containing an even number or five.

The Algorithm

I assume that I have a list of all primes below 1.000.000 for this. This can rather easily be done with a Sieve of Eratosthenes which we built in Problem 10.
  1. Take the first number in the list.
  2. Check if it contains even numbers or five. If not continue to 3. Otherwise discard it and go to 1.
  3. Move the number to a temporary list.
  4. Rotate the number and check if it is a prime. If not discard the temporary list and go to 1.
  5. If the number is in the prime list move it to the temporary list.
  6. if the number is in the temporary list do nothing.
  7. if all rotations are not checked go to 4.
  8. return the length of the temporary list.
This is the algorithm I wrote in order to find the circular primes.
My main philosophy was to stop as soon as I found a number which was not a prime and then remove all the prime numbers already found in  that cycle. The reason I wanted to stop as soon as I encountered a non-prime is that I expect that there are far more candidates than actual circular primes, so I would do a lot more work checking all the cycles to full length.
public int CheckCircularPrimes(int prime){
    int multiplier = 1;
    int number = prime;
    int count = 0;
    int d;
 
    //Count the digits and check for even numbers
    while (number > 0) {
        d = number % 10;
        if (d % 2 == 0 || d == 5) {
            primes.Remove(prime);
                return 0;
            }
            number /= 10;
            multiplier *= 10;
            count++;
    }
    multiplier /= 10;
 
    //Rotate the number and check if they are prime
    number = prime;
    List foundCircularPrimes = new List();
 
    for (int i = 0; i < count; i++) {
        if(primes.Contains(number)) {
            foundCircularPrimes.Add(number);
            primes.Remove(number);
        }else if(!foundCircularPrimes.Contains(number)) {
            return 0;
        }
 
        d = number % 10;
        number = d * multiplier + number / 10;
    }
 
    return foundCircularPrimes.Count;
}

The primes list is a SortedSet I have made as an object variable. The main loop of the algorithm looks like

int noCircularPrimes = 2;
primes = new SortedSet<int>(ESieve(1000000));
//Special cases
primes.Remove(2);
primes.Remove(5);
 
while (primes.Count > 0) {
    noCircularPrimes += CheckCircularPrimes(primes.Min);
}

Even though I don’t really think that it is a nice solution the result of running it is


The number of circular primes below 1.000.000 is 55
Solution took 80 ms

Write a program to print 4 digit circular prime numbers using c#

The number, 197, is called a circular prime because all rotations of the digits: 197, 971, and 719, are themselves prime.

namespace ConsoleApplication4
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("--- Primes between 0 and 100 ---");
            for (int i = 0; i < 100; i++)
            {
                bool prime = PrimeTool.IsPrime(i);
                if (prime)
                {
                    Console.Write("Prime: ");
                    Console.WriteLine(i);
                }
            }
            //
            // Write prime numbers between 10000 and 10100
            //
            Console.WriteLine("--- Primes between 1000 and 2000 ---");
            for (int i = 1000; i < 2000; i++)
            {
                if (PrimeTool.IsPrime(i))
                {
                    Console.Write("Prime: ");
                    Console.WriteLine(i);
                }
            }

            Console.Read();
        }
    }
    public static class PrimeTool
    {
        public static bool IsPrime(int candidate)
        {
            // Test whether the parameter is a prime number.
            if ((candidate & 1) == 0)
            {
                if (candidate == 2)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            // Note:
            // ... This version was changed to test the square.
            // ... Original version tested against the square root.
            // ... Also we exclude 1 at the end.
            for (int i = 3; (i * i) <= candidate; i += 2)
            {
                if ((candidate % i) == 0)
                {
                    return false;
                }
            }
            return candidate != 1;
        }
    }


}

write a program to print "acababacd" from "acaaabbabbacddd" using c#

string s = "acaaabbabbacddd";
            char[] c = s.ToCharArray();
            for (int i = 0; i <= c.Length - 1; i++)
            {
                if (i!= c.Length - 1)
                {
                    if (c[i] == c[i + 1])
                    {
                        Console.Write(c[i]);
                        if (c[i] == c[i + 2])
                        {
                            i = i + 2;
                        }
                        else
                        {
                            i = i + 1;
                        }
                    }
                    else
                    {
                        Console.Write(c[i]);
                    }
                }
            }
            Console.Read();

Wednesday, August 20, 2014

WCF Tutorial: Session Management in WCF

WCF Session is different with Asp.net session so don’t get confused. In WCF session means. to combine several message together into a single conversation. They are initiated from the calling application, in other word’s theyare client side and they can not store any kind of data inside.WCF Session are set by SessionMode” Attribute. 
You can have following types of modes 

  1. Allowed: Specifies the contract supports the reliable sessions if the incoming connection supports them. Here Session can be allowed.
  2. Not Allowed: Contract never supports reliable sessions.
  3. Required: Specifies that the contract requires a reliable sessions at all time. Means session is mandatory.
WCF Session management is very important, if your exception is not handle properly session will be destroyed along with that with exception.

You can manage your session in WCF by using following method. 
· Per Call 
· Per Session 
· Single Call 
imageYou can define instancing mode with the help of instancecontext value ofServiceBehaviour attribute.[ServiceBehavior(InstanceContextMode = InstanceContextMode.PerSession)] 

All above instantiations methods are not supported by basicHTTP binding. You make sure you have the change the binding as well. You can add binding information under the services tag in web.config.
<system.serviceModel>
    <services>
      <service name="Service">
        <endpoint binding="wsHttpBinding" contract="IService">endpoint>
        <endpoint binding="wsHttpBinding" contract="IRsg">endpoint>
       
      service>
    <services>
    <behaviors>


Where IService is my contract and I have chosen wsHttpBinding type. While defining bindings you can define the address as well of your service. In WCF you can use two types of addressing 


  • Absolute address where you can specify the fixed path and port number of your contract such as


<endpoint address="http://localhost:8080/calcservice" binding = "wsHttpBinding" contract="IMathCalc"/>


Relative address, where you are not fixing any address or port.
<endpoint binding="wsHttpBinding" contract="IService"><endpoint>


Now go to your contract page (Interface) and define your [OperationContract]

public interface IService{
    [OperationContract]
    string Method1();
    


Each one has their own pros and cons, as per your requirement you can choose any of them. Before moving ahead let me explain the fundamentals of above mentioned ways. So let’s start with 

Per Call – If your service is configured per call basis, each time a new instance of your service will be created. In other words for each call every time a new service instance will be created and after fulfilling the purpose will be destroyed. 
In Service class you must have to define the instancing behavior of your service 

[ServiceBehavior(InstanceContextMode = InstanceContextMode.PerCall)] 

 public class Service : IService 
{

    public string Method1()
    {
        return "Method1";
    }

Per call is helpful when you have scalability concern.

Per Session - Some time scalability is not your primary concern and you wanted to maintain the states between wcf calls, then you can implement per session mechanisms. In this one single instance will be created for a session. To implement per session instantiations you must have to change the value of instancecontextmode of ServiceBehaviour attribute 

[ServiceBehavior(InstanceContextMode = InstanceContextMode.PerSession)] 


Single Call – Sometime you have the requirement to share some data globally through your wcf application, single call instantiations is the correct choice. Here scalability will not consider. To achieve single call instantiations you need to configure your wcf service as a “Single” instance mode

[ServiceBehavior(InstanceContextMode = InstanceContextMode.Single)] 



In this case once when client make the request to WCF, first time WCF instance will create and fulfill the user request, now it will remain into memory to serve the other request. If you notice here only single wcf service instance is used to server all client request.

Make sure before implementing wcf instantiation, you have addedSessionMode” Attribute in your WCF Service Contract as

[ServiceContract (SessionMode = SessionMode.Required)]


//[ServiceContract]
 public interface IService{
    [OperationContract]
    string Method1();

A Beginner's Tutorial on Creating WCF REST Services

Introduction

In this article we will try to understand what are WCF REST services. We will see what is required from a service developers perspective to create a REST enabled WCF service. We see how we can use and consume restful WCF services.

Background

Overview of REST

REST stands for Representational State Transfer. This is a protocol for exchanging data over a distributed environment. The main idea behind REST is that we should treat our distributed services as a resource and we should be able to use simple HTTP protocols to perform various operations on that resource.
When we talk about the Database as a resource we usually talk in terms of CRUD operations. i.e. Create, Retrieve, Update and Delete. Now the philosophy of REST is that for a remote resource all these operations should be possible and they should be possible using simple HTTP protocols.
Now the basic CRUD operations are mapped to the HTTP protocols in the following manner:
  • GET: This maps to the R(Retrieve) part of the CRUD operation. This will be used to retrieve the required data (representation of data) from the remote resource.
  • POST: This maps to the U(Update) part of the CRUD operation. This protocol will update the current representation of the data on the remote server.
  • PUT: This maps to the C(Create) part of the CRUD operation. This will create a new entry for the current data that is being sent to the server.
  • DELETE: This maps to the D(Delete) part of the CRUD operation. This will delete the specified data from the remote server.
so if we take an hypothetical example of a remote resource that contain a database of list of books. The list of books can be retrieved using a URL like:
www.testwebsite.com/books
To retrieve any specific book, lets say we have some ID that we can used to retrieve the book, the possible URL might look like:
www.testwebsite.com/books/1
Since these are GET requests, data can only be retrieved from the server. To perform other operations, if we use the similar URI structure with PUTPOST or DELETE operation, we should be able to create, update and delete the resource form the server. We will see how this can be done in implementation part.
Note: A lot more complicated queries can be performed using these URL structures. we will not be discussing the complete set of query operations that can be performed using various URL patterns.

Using the code

Now we can create a simple WCF service that will implement all the basic CRUD operations on some database. But to make this WCF service REST compatible we need to make some changes in the configuration, service behaviors and contracts. Let us see what WCF service we will be creating and then we will see how we can make useful over the REST protocol.

creating REST enabled ServiceContract

We will create Books table and will try to perform CRUD operations on this table. 

To perform the Database operations within the service lets use Entity framework. This can very well be done by using ADO.NET calls or some other ORM but I chose entity framework. (please refer this to know about entity framework: An Introduction to Entity Framework for Absolute Beginners[^]). The generated Entity will look like following. 
Now the service contract will contain functions for CRUD operations. Let us create the ServiceContract for this service:
[ServiceContract]
public interface IBookService
{
    [OperationContract]
    List<Book> GetBooksList();

    [OperationContract]
    Book GetBookById(string id);

    [OperationContract]
    void AddBook(string name);

    [OperationContract]
    void UpdateBook(string id, string name);

    [OperationContract]
    void DeleteBook(string id);
}
Right now this is a very simple service contract, to indicate that individual operations can be called using REST protocol, we need to decorate the operations with additional attributes. The operations that are to be called on HTTPGET protocol, we need to decorate them with the WebGet attribute. The operations that will be called by protocols, like POST, PUT, DELETE will be decorated with WebInvoke attribute.

Understanding UriTemplate

Now before adding these attributes to these operations let us first understand the concept of UriTemplate.UriTemplate is a property of WebGet and WebInvoke attribute which will help us to map the parameter names coming from the HTTP protocol with the parameter names of ServiceContract. For example, if someone uses the following URI:
localhost/testservice/GetBookById/2
We need to map this first parameter with the id variable of the function. this can be done using the UriTemplate. Also, we can change the function name specifically for the URI and the name of URI function name will be mapped to the actual function name i.e. if we need to call the same URL as:
localhost/testservice/Book/2
then we can do that by specifying the UriTemplate for the operation as:
[OperationContract]
[WebGet(UriTemplate  = "Book/{id}")]
Book GetBookById(string id);
Following the same lines, let us define the UriTemplate for other methods too.
[ServiceContract]
public interface IBookService
{
    [OperationContract]
    [WebGet]
    List<Book> GetBooksList();

    [OperationContract]
    [WebGet(UriTemplate  = "Book/{id}")]
    Book GetBookById(string id);

    [OperationContract]
    [WebInvoke(UriTemplate = "AddBook/{name}")]
    void AddBook(string name);

    [OperationContract]
    [WebInvoke(UriTemplate = "UpdateBook/{id}/{name}")]
    void UpdateBook(string id, string name);

    [OperationContract]
    [WebInvoke(UriTemplate = "DeleteBook/{id}")]
    void DeleteBook(string id);
}

Implementing the Service  

Now the service implementation part will use the entity framework generated context and entities to perform all the respective operations.
public class BookService : IBookService
{
    public List<Book> GetBooksList()
    {
        using (SampleDbEntities entities = new SampleDbEntities())
        {
            return entities.Books.ToList();
        }
    }

    public Book GetBookById(string id)
    {
        try
        {
            int bookId = Convert.ToInt32(id);

            using (SampleDbEntities entities = new SampleDbEntities())
            {
                return entities.Books.SingleOrDefault(book => book.ID == bookId);
            }
        }
        catch
        {
            throw new FaultException("Something went wrong");
        }
    }

    public void AddBook(string name)
    {
        using (SampleDbEntities entities = new SampleDbEntities())
        {
            Book book = new Book { BookName = name };
            entities.Books.AddObject(book);
            entities.SaveChanges();
        }
    }

    public void UpdateBook(string id, string name)
    {
        try
        {
            int bookId = Convert.ToInt32(id);

            using (SampleDbEntities entities = new SampleDbEntities())
            {
                Book book = entities.Books.SingleOrDefault(b => b.ID == bookId);
                book.BookName = name;
                entities.SaveChanges();
            }
        }
        catch
        {
            throw new FaultException("Something went wrong");
        }
    }

    public void DeleteBook(string id)
    {
        try
        {
            int bookId = Convert.ToInt32(id);

            using (SampleDbEntities entities = new SampleDbEntities())
            {
                Book book = entities.Books.SingleOrDefault(b => b.ID == bookId);
                entities.Books.DeleteObject(book);
                entities.SaveChanges();
            }
        }
        catch
        {
            throw new FaultException("Something went wrong");
        }
    }
}

Restful WCF service Configuration

Now from the ServiceContract perspective the service is ready to serve the REST request but to access this service over rest we need to do some changes in the service behavior and binding too.
To make the service available over REST protocol the binding that needs to be used is the webHttpBinding. Also, we need to set the endpoint's behavior configuration and define the webHttp parameter in the endpointBehavior. So our resulting configuration will look something like:

Test the service

Now to test the service we will simply run the service and use the URLs to retrieve the data. let see this for our GEToperations in action.
 
And now testing the query to get a single record
 
And so we have seen that we received the response in the browser itself in form of XML. We can use this service without even consuming it by adding a service reference by using the URLs and HTTP protocols.
Note: Here I am not demonstrating the other operations for POST, PUT and DELETE but they are fairly straight forwards and a simple HTML page sending the data using the required protocol with the specified parameter names will perform the operation.

Using JSON

We can also change the Response and Request format to use JSON instead of XML. To do this we need to specify properties of the WebInvoke attribute.
  • RequestFormat: By default its value is WebMessageFormat.XML. to change it to JSON format, it needs to be set to WebMessageFormat.Json.
  • ResponseFormat: By default its value is WebMessageFormat.XML. to change it to JSON format, it needs to be set to WebMessageFormat.Json.
Let us create one more operation in our service contract called as GetBooksNames and will apply theResponseFormat as Json for this method.
[OperationContract]
[WebGet(ResponseFormat=WebMessageFormat.Json]
List<string> GetBooksNames();
The response will now appear in the JSON format. 

And now we have a WCF REST service ready with us.
Note: We also have a ready made template in Visual studio to create WCF data services that provides us with easy way to create REST enabled ODATA services. We will perhaps talk about them separately.