State Server Mode in Session State

Introduction

·         Asp.Net provides concept of Sessions. Session means that you can store some data temporarily with asp.net session state and get it back on next server call.

·         In web application development, developers deal with session to access value from multiple pages. Sometime it will be lost session when webserver (IIS) is restarted. We cannot access that variable in application anymore because HTTP protocol is stateless.

      Various Type of Session State Mode

·         Three types of Session State mode available in .Net

o   In Process

§  This is default setting. In this session information will be stored into Webserver.

o   State Server

§  In this mechanism, a Windows service is used to store the session information. It should be noted that there should be only one state server instance running per server farm so that sessions may be shared. State Service is more reliable than in memory because even if one of the web servers goes down (assuming not the one which is executing the state server) the website may still work as the sessions are not lost.

o   SQL Server

§  In this mechanism session is stored in SQL server database.

To Configure Session in Session State Service

·         To use State server mode you ensure that .net framework is installed into computer and to start state service following screen shot.

           Step1:

Go to Computer à Manage à Services à Asp.Net State Service OR

Go to run à Type Service.msc à Start Asp.net State Service

      

 

   Step 2

o   Update web.config file of our application as per following screen shot.

·         For your understanding I made sample code for session state mode and bind grid view. We store data in session from data set. If the IIS restarted then your session variable will not be lost and your application will still be running.

Out Put:

First Output

·         Data will be loaded from the database server on the first call.

IIS restarted Output      

·         Session state preserved even when the webserver (IIS) is restarted.

Conclusion

·         If you don’t specified session mode in your application web.config file then it will automatically takes “In Process Mode” mode.

·         In Process Mode: we cannot preserve session information if Webserver (IIS) is restarted

·         In State Server: we can  preserve session information if Webserver (IIS) is restarted

Indexing in SQL Server 2008

Introduction:

We are dealing with database and each and every day increase data and we have face to deal problem related retrieve and access of data from database and it will be taken too much time. So we need to increase in data access speed.

 Index (in simple words it like index of any book eg. While searching a word in Book we use index back of book to find the occurrence of that word and its relevant page numbers) which provides faster access to rows in tables. If we don’t create indexes then SQL engine searches every row in table. We data grow in table then searching without indexing become much slower.

 In this article I want to show how to indexing on table improve performance.

 For that I will create demo script with three tables.

 First table I have created “No Index” on Table and Insert 1, 00,000 records in table.

Second table “Clustered Index” on table Column [ID] and Insert 1, 00,000 records in table

Third table having “Non Clustered index” on table Column [ID] and Insert 1, 00,000 records in table

I am having following script with three tables and insert 1, 00,000 records into tables.

No Index on Table

-- Create Table NoIndex Table

CREATE TABLE NoIndex

(

ID INT,

FirstName VARCHAR(100)

)

GO

GO

-- Insert One Hundred thousan of Records

-- Insert 1

INSERT INTO NoIndex (ID,FirstName)

SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,

CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name) %2 =1 THEN 'Mistry'

                          ELSE 'Nilay' END,FROM sys.all_objects a

CROSS JOIN sys.all_objects b

GO

Cluster Index on Table

GO

-- Create Table Index Table

CREATE TABLE ClusteredIndex

(

ID int,

FirstName VARCHAR(100),

)

GO

GO

-- Create Non clustered  Index on Last Name

CREATE Clustered INDEX [IX_ClusteredIndex_ID] ON dbo.ClusteredIndex

(

      [ID] ASC

) ON [PRIMARY]

GO

GO

-- Insert One Hundred thousan of Records

-- Insert 1

INSERT INTO ClusteredIndex (ID,FirstName)

SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,

CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name) %2 =1 THEN 'Mistry'

                          ELSE 'Nilay' END,FROM sys.all_objects a

CROSS JOIN sys.all_objects b

GO

Non Cluster Index on Table

GO

-- Create Table Index Table

CREATE TABLE NonClusteredIndex (ID int,

FirstName VARCHAR(100),

)

GO

GO

-- Create Non clustered  Index on Last Name

CREATE Clustered INDEX [IX_NonClusteredIndex_ID] ON dbo.NonClusteredIndex

(

      [ID] ASC

) ON [PRIMARY]

GO

GO

-- Insert One Hundred thousan of Records

-- Insert 1

INSERT INTO NonClusteredIndex (ID,FirstName)

SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,

CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name) %2 =1 THEN 'Mistry'

                          ELSE 'Nilay' END,FROM sys.all_objects a

CROSS JOIN sys.all_objects b

GO

Now, I am running with simple query as per following screen and see execution plan for NoIndex, ClusterIndex and NonClusteredIndex tables.

Query 1 return table Scan operation and Query 2 and Query 3 return Index Seek Operation.

Index Seek which means that optimizer has found a useful index to retrieve recordset. But if it is not able to do so either because there is no index or no useful indexes on the table then SQL Server has to scan all the records that satisfy query condition perform with Table Scan operation.

You clearly see above output which one is faster. Query 3 return same amount to percentage as ” No Index”  table is used that mean that Query3 is not used Non clustered index so it will be bad decision to take Non Clustered Index on table [ID] column.

If suppose I forcefully assign Non Clustered Index on Query then you will get following output as shown in following screen shot.

Conclusion:

For above example I explained you how index will be used to increase performance with examples and also bad index decrease performance. So take care when we used index on table. If index is not going too used further delete it.