Haroon Waheed

Serialize & Deserialize JSON Data

Wed, 13 Aug 2008 00:43:00 GMT

Web Services with AJAX
As most of you would already know, AJAX Extension allows you to create proxies to expose web services to be consumed by the Web Browsers. The mechanism for calling these service functions is completely asynchronous and allows you to specify two functions that will be called when the request complete successfully or error out. In addition to the parameter that you can pass to the service methods, you can also pass in a context parameter that will be available to both success and failure functions.

JSON
The default serialization of AJAX web services is JSON. JSON stands for (JavaScript Object Notation) and is a light weight data interchange format. When building an ASP.NET AJAX application with a web service running at its back you can use either JSON or XML as data interchange means between the Web Browser and the Web Server.

The primary advantage of using JSON over XML is that JavaScript has a build in support to handle JSON strings and by just using the eval function one can get an object representation of the serialized data. Also for browsers, parsing JSON string is much more efficient then parsing a XML document. Additionally JSON produces smaller document therefore reducing the request and response pay load which is exactly what you need while implementing AJAX solutions.

DataContractJsonSerliaizer class provides methods for serialization and de-serialization .NET type to JSON. Suppose there is a user profile class as mentioned:

    public class UserProfile
    {
        public string UserName { get; set; }
        public string Email { get; set; }
        public int Age { get; set; }

}

To enable JSON serialization we need to decorate the class with DataContract attribute and member variables that need to be serialized with DataMember attribute. To use both these attribute we need to import System.Runtime.Serialization namespace.

    [DataContract]
    public class UserProfile
    {
        [DataMember]
        public string UserName { get; set; }
        [DataMember]
        public string Email { get; set; }
        [DataMember]
        public int Age { get; set; }
    }

Server-Side Serialization
To serialize an object of type UserProfile we need to create a instance of DataContractJsonSerializer which is located in System.Runtime.Serialization.Json namespace, pass in the type that needs to be serialized and use WriteObject mehtod to write serialized string to a stream.

   UserProfile Profile = new UserProfile();
   Profile.UserName = "hwaheed";
   Profile.Email = "haroon_6@yahoo.com";
   Profile.Age = 28;
   MemoryStream Stream = new MemoryStream();
   DataContractJsonSerializer JSC;
   JSC = new DataContractJsonSerializer(typeof(UserProfile));
   JSC.WriteObject(Stream, Profile);
   Stream.Flush();
   Stream.Position = 0;
   StreamReader Reader = new StreamReader(Stream);
   string JSONString = Reader.ReadToEnd();

The JSON will be like this:

{"Age":28,"Email":haroon_6@yahoo.com,"UserName":"hwaheed"}

Server-Side De-Serialization
The same DataContractJsonSerializer class can be used to de-serialize JSON string. We create it's instance with type that needs to be de-serialized and use ReadObject to read JSON string from stream to initialize object.

   UserProfile Profile = new UserProfile();
   MemoryStream Stream;
   Stream = new MemoryStream(Encoding.Unicode.GetBytes(JSONString));
   DataContractJsonSerializer JSC;

JSC = new DataContractJsonSerializer(typeof(UserProfile));
Profile = (UserProfile)JSC.ReadObject(Stream);

Client-Side De-Serialization
As mentioned earlier JavaScript has built in support for JSON and we can use eval function to get an object representation of the above JSON string.

var Profile = eval("(" + JSONString + ")")

This will create a new Profile object with three properties. You can access these properties by the names mentioned in serialized string like:

                        Profile.UserName
                        Profile.Email
                        Profile.Age

Client-Side Serialization
To serialize/de-serialze object on client side AJAX 2.0 provides Sys.Serialization.JavaScriptSerializer class. To serialize, use the static serialize method:

var JSONString;

JSONString = Sys.Serialization.JavaScriptSerializer.serialize(Profile);

Conclusion

JSON along with it's built in support in .NET and JavaScript has made data interchange between AJAX web service and client considerably light and when carefully implemented can give your application a well deserved performance boost.

Maintain Browser History with AJAX

Sat, 26 Jul 2008 14:11:00 GMT

Most of you out there would have noticed that browser history button doesn’t work if you are using AJAX. Instead of rolling forward or backward the AJAX step they actually move the complete page. This can be a nightmare for users that always tend to use them.

AJAX has a very nice feature known as History Points that allow the browser to keep track of AJAX steps and therefore move through them using forward and backward buttons.

Let’s understand how History Points work through a simple example. We have a page that show company’s departments in a drop down list. On selecting one, the page shows the employees working in that department in a list; on selecting the employee it shows employee details like name, email, salary etc. Employee information is stored in Employee class.

    public class Employee
    {
        public string Name { get; set; }
        public string Dept { get; set;}
        public string Email { get; set; }
        public int Salary { get; set; }
    }

Our aspx page has three update panels as follows:

    <asp:ScriptManager ID="SManager" runat="server"></asp:ScriptManager>
        <asp:UpdatePanel ID="DeptUpdPanel" runat="server">
            <ContentTemplate>
                <asp:Panel ID="DeptPanel" runat="server" Visible="True">
                    <asp:DropDownList ID="DrpDept" runat="server"
                    AutoPostBack="True"
                    OnSelectedIndexChanged="DeptDrp_SelectedIndexChanged">
                        <asp:ListItem Text="Select Department" Selected="true" />
                        <asp:ListItem Text="Admin" />
                        <asp:ListItem Text="HR" />
                        <asp:ListItem Text="Manufacturing" />
                    </asp:DropDownList>
                </asp:Panel>
            </ContentTemplate>
        </asp:UpdatePanel>
        <asp:UpdatePanel ID="EmpUpdPanel" runat="server">
            <ContentTemplate>
                <asp:Panel ID="EmpPanel" runat="server" Visible="False">
                    <asp:DropDownList ID="DrpEmployee" runat="server"
                    AutoPostBack="True"
                    OnSelectedIndexChanged="EmpDrp_SelectedIndexChanged">
                    </asp:DropDownList>
                </asp:Panel>
            </ContentTemplate>
        </asp:UpdatePanel>
        <asp:UpdatePanel ID="DetailUpdPanel" runat="server">
            <ContentTemplate>
                <asp:Panel ID="DetailPanel" runat="server" Visible="False">
                    Department: <asp:Label runat="server" ID="LblDept"/></asp:Label><br>
                    Name: <asp:Label runat="server" ID="LblName"/></asp:Label><br>
                    Email: <asp:Label runat="server" ID="LblEmail"/></asp:Label><br>
                    Salary: <asp:Label runat="server" ID="LblSalary"/<br>
                </asp:Panel>
            </ContentTemplate>

</asp:UpdatePanel>

The code behind is very simple and contains event handlers for drop down selected index change. The DeptDrp_SelectedIndexChanged generate test employee data and stores them in a generic list named Employees. The handler uses a basic LINQ query to retrieve employee data for selected department.

        protected void DeptDrp_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (Session["Employees"] == null)
            {
                Employees = new List<Employee>(new Employee[]{
                    new Employee(){Name = "David",
                        Dept="HR", Email = "David@org.com",
                        Salary = 2000},
                    new Employee(){Name = "George",
                        Dept="Admin", Email = "George@org.com",
                        Salary = 2500},
                    new Employee(){Name = "Bill",
                        Dept="Admin", Email = "Bill@org.com",
                        Salary = 6000},
                    new Employee(){Name = "Henry",
                        Dept="Manufacturing", Email = "Henry@org.com",
                        Salary = 4750},
                    new Employee(){Name = "Michael",
                        Dept="Manufacturing", Email = "Michael@org.com",
                        Salary = 8000},
                });

                Session["Employees"] = Employees;
            }
            else
            {
                Employees = (List<Employee>)Session["Employees"];
            }
            DeptPanel.Visible = false;
            EmpPanel.Visible = true;
            var Emp = from E in Employees
                      where E.Dept == DrpDept.SelectedValue
                      select new { E.Name };
            DrpEmployee.DataSource = Emp;
            DrpEmployee.DataValueField = "Name";
            DrpEmployee.DataBind();
            DrpEmployee.Items.Insert(0, "Select Employee");

}

The EmpDrp_SelectedIndexChanged handler displays the employee details for the selected employee.

        protected void EmpDrp_SelectedIndexChanged(object sender, EventArgs e)
        {
            Employees = (List<Employee>)Session["Employees"];
            EmpPanel.Visible = false;
            DetailPanel.Visible = true;
            var Emp = from E in Employees
                      where E.Name == DrpEmployee.SelectedValue
                      select new { E.Name, E.Email, E.Dept, E.Salary };

            foreach (var EDetail in Emp)
            {
                LblDept.Text = EDetail.Dept;
                LblEmail.Text = EDetail.Email;
                LblName.Text = EDetail.Name;
                LblSalary.Text = EDetail.Salary.ToString("C");
            }

}

When the project is Build, Run and navigated, you will notice that the browser history buttons are disabled because the browser has no clue regarding the AJAX steps.

To overcome this problem we will have to introduce History Points to the scene. To maintain history we first need to enable it in script manager and specify an event handler that will be invoked when browser history buttons are clicked. The script manager tag will look like this:

<asp:ScriptManager ID="SManager" runat="server"

EnableHistory=true

OnNavigate="History_Navigate" />

We would need to insert two history points as there are two AJAX postbacks being performed, one at the end of DeptDrp_SelectedIndexChanged and other at the end of EmpDrp_SelectedIndexChanged. For this create a method Add_History as follows:

        private void Add_History(string Title)
        {
            NameValueCollection State = new NameValueCollection();
            State.Add("DeptIndex", DrpDept.SelectedIndex.ToString());
            State.Add("EmployeeIndex", DrpEmployee.SelectedIndex.ToString());
            SManager.AddHistoryPoint(State, Title);

}

To create history point we call AddHistoryPoint function of the script manager. This method allow to maintain current state of the page by storing it in a NameValueCollection along with history point's title that is displayed in the browser. This saved state is then used to render the page when browser history buttons are clicked.

To add history point on DeptDrp_SelectedIndexChanged we write the following code at the end of the handler:

if (SManager.IsInAsyncPostBack && !SManager.IsNavigating)

Add_History("Employee List: " + DrpDept.SelectedValue);

!SManager.IsNavigating indicates that the postback didn't occurred as a result of the user clicking the back or forward button. In the same way we add a history point in EmpDrp_SelectedIndexChanged:

if (SManager.IsInAsyncPostBack && !SManager.IsNavigating)

Add_History("Employee Detail:" + DrpEmployee.SelectedItem.Text);

Note that when the first history point is added the value of DrpEmployee.SelectedIndex is zero therefore the state will have EmployeeIndex value set to 0 which identifies that it's the first of AJAX steps. However the second history point will have values in both EmployeeIndex and DeptIndex. Additionally, when the page is initially loaded and department list is displayed there is no state thus DeptIndex and EmployeeIndex are both null. We will use these assumptions to re-create page when History_Navigate handler is called by clicking Backward / Forward buttons.

        protected void History_Navigate(object sender, HistoryEventArgs e)
        {
            NameValueCollection State = e.State;
            string DeptIndex = State["DeptIndex"];
            string EmployeeIndex = State["EmployeeIndex"];

            //Set DeptIndex = "0" when its null
            DeptIndex = string.IsNullOrEmpty(DeptIndex)
                ? "0" : DeptIndex;
            EmployeeIndex = string.IsNullOrEmpty(EmployeeIndex)
                ? "0" : EmployeeIndex;

            //Dept List View
            if (DeptIndex == "0")
            {
                DeptPanel.Visible = true;

EmpPanel.Visible = false;

                DetailPanel.Visible = false;
            }
            //Employee List View
            if (DeptIndex != "0" && EmployeeIndex == "0")
            {
                DeptPanel.Visible = false;
                EmpPanel.Visible = true;
                DetailPanel.Visible = false;
            }
            //Employee Detail View
            if (EmployeeIndex != "0")
            {
                DeptPanel.Visible = false;
                EmpPanel.Visible = false;
                DetailPanel.Visible = true;
            }

}

The History_Navigate handler gets the current state from HistoryEventArgs and uses the values of DeptIndex and EmployeeIndex to hide / show each panel. Build, Run and navigate through the project, the output will look like:

We have successfully added browser history for AJAX calls and can navigate forward / backward. One important point to be mentioned here is that history points are stored in browser as URLs and the history state is maintaned in those. The maximum size of URL can only be 1024 chars so caution needs to be taken to store minimum information in history state.

ASP.NET Performance Tips

Sun, 29 Jun 2008 20:13:00 GMT

At times even after applying the best coding policies & practices you don’t get the desired level of performance you are hoping from your ASP.NET application. This is because there are number other very important factors that directly affect ASP.NET applications. To get the best out of any system requires detail architectural, design, coding and deployment considerations. The post lists few of some of the many performance tweaks that you can implement to boost up ASP.NET performance.

Remove Unused HTTP Modules

There are various HTTP modules in ASP.NET that intercept each request sent to the server. Session State is a very commonly used HTTP module used to load session data in context object. It’s referred with SessionStateModule name. HTTP modules kick in at each request and process them, therefore if you are not using the functionality provided by the module there is no use referring it as they would use additional CPU cycles. There is a list of HTTP Modules that your application automatically uses when it inherits config setting from web.config placed in $WindowsFolder\Microsoft.NET\Framework\$versiosn\CONFIG folder.
Below is a list of such entries:

There are bunch of HTTP modules listed here and I am quite positive not all of them are being used by your application. Removing unused HTTP module can definitely give slight performance boost as there would be less work to be performed. Suppose one doesn’t needs Windows authentication in application. To remove the inherited setting, under httpModules section in your web.config application add a remove element and specify name of the module that isn’t required.

Example:
      <httpModules>
            <remove name="WindowsAuthentication" />
      </httpModules>

<compilation debug=”true”/> Killer

As a developer I have seen numerous incidents were the application is deployed to production with <compilation debug=”true”/>. This is really a performance killer because:

Compilation of ASP.NET pages take longer.
Code execute slower as debug paths are enabled.
More memory is used by the application.
Scripts and images from WebResource.axd handler are not cached.

Always make sure that debug flag is set to false on production instances. You can override this by specifying following entry in machine.config for production instances:

This will disable the <compilation debug=”true”/> for all applications deployed on the server.

Turn off Tracing

Do remember to turn off tracing before deploying application to production. Tracing adds additional overload to your application which is not required in production environment. To disable tracing use the following entries:

Process Model Optimization

ASP.NET allows you to define many process level properties. You can get the detail of all these properties from http://msdn.microsoft.com/en-us/library/7w2sway1.aspx. By default these are set to auto config. This means that ASP.NET automatically configures maxWorkerThreads, maxIoThreads, minFreeThreads, minLocalRequestFreeThreads and maxConnection to achieve optimal performance. You can tailor these by specifying your own value to achieve better performance. Some of the major settings are:

maxWorkerThreads. The default value is 20 per process and it determines the maximum number for request that ASP.NET can process in a given second. For application that are not CPU intensive and most of time wait on database request or any external processing this can increased to get better performance.
maxIOThreads. The default value is 20 per process and it determines the maximum number for I/O request that ASP.NET can process in a given second. If you have enough I/O resources you can increase this value for better results.
memoryLimit: The default is 60%. This is the max memory ASP.NET can use until worker process is refreshed. If you have a dedicated web server with no other services running you can increase this value for better results.
connectionManagement: This is a property of System.Net configuration and specifies the maximum parallel connections that can be established to a server. If your web application extensively connects to other server you can increase this value.

Enable Buffering

Make sure that buffering is enabled unless you have a specific need to turn it off. By default its enabled. ASP.Net sends response to IIS in a 31 KB buffer which then passes that to the client. When buffering is disabled ASP.NET only sends few characters to IIS thus not utilizing this buffer and increasing the trips between IIS and the worker process. To enable it you can change the web.config or enable it on each page through @page directive

<%@ Page Buffer="true"%>

Caching

Caching in ASP.NET dramatically help in boosting application performance by reducing the load on the underlying server and serving cached content that doesn’t need to be recreated on each request. ASP.NET provides two types of caching:

Output Cache which stores dynamic pages and user controls. One each request code is not executed if a cached version of page or control is available
Data Cache which allows application to save application objects, DataSet etc in server memory so they are not recreated on each request.

Use caching whenever possible to reduce the load on your web server and to increase response time.

Caching is a huge topic can not be discussed in detail in one post. For more details visit http://msdn.microsoft.com/en-us/library/xsbfdd8c.aspx.

Kernel Cache

Use Kernel Cache if you are using IIS 6 or above. When Output cache is used in ASP.NET the request still goes to ASP.NET that itself returns the cached content. However if Kernel Cache is enabled and the request is output cached by ASP.NET, IIS receives the cached content. If a request comes for that data again IIS will serve the cached content and end the response. This can save valuable CPU cycles as it minimizes work performed by ASP.NET.

Avoid using Response.Redirect

Instead of using Response.Redirect, use Server.Transfer where ever you can. Response.Redirect sends response to the client which then sends a new request to the server. Server.Transfer however performs the redirect on the server. Only use Response.Redirect when you want authentication and authorization to be performed on redirects or you want URL on client browser to be changed because Server.Transfer will not do this as it is a server side transfer.

Avoid using Server-Side Validation

Where ever you can use client-side validation instead of Server-Side validation. This will save you from additional reposts in cases in invalid input. If you don’t trust the browsers that they will be able to perform complex validations still use client-side validation and on repost check Page.IsValid to check if the input passed the given set of rules.

Avoid DataBinder.Eval Calls
Avoid calling DataBinder.Eval multiple times for example in case of grids, repeaters etc. Instead use Continer.DataBind. DataBinder.Eval uses reflection to evaluate the arguments and therefore can decrease performance if called numerous times.

Avoid Using Page.DataBind

Never call Page.DataBind until your really need to do so. Instead if you want to bind a specific control only bind that. Calling Page.DataBind will call DataBind for all the controls that support binding.

ViewState Optimization

Avoid using ViewState for storing huge objects or disable it when you don’t need it. ViewState is also used by server controls so that they can retain their state after postback. You can also save your objects that are marked Serializable in the ViewState. ASP.NET serializes all objects and controls in the ViewState and transmits them in a hidden field to the browser. If not managed properly ViewState can increase page size and therefore increase network traffic. Also precious CPU cycles are used for Serialization and De-Serialization of ViewState objects. Disable ViewState if:

Your pages don’t do postback.
You controls are not bound to a data source or they don’t handle server events like OnClick, OnSelectedIndexChanged etc or their properties are set on each postback
You recreate controls on every postback.

You can disable ViewState in both web.config or @Page directive

Save or Compress ViewState

In case where ViewState in mandatory and the ViewState contains enough data that can cause Network congestion or increase download response time for the user try saving or compressing the ViewState. The Page class provide two very useful methods LoadPageStateFromPersistenceMedium() and SavePageStateToPersistenceMedium(object ViewState). You can override these methods to either compress the ViewState or even prevent it from going to the client by saving it in some persistent medium on the server.

Use HTTP Compression

If your page size is large enough to cause noticeable lag between subsequent request and response you can use HTTP compression. HTTP compression is a feature of IIS and what it means is that you can compress data sent to the client using compression techniques like GZIP and Deflate. On the other side the browser decompresses the data and shows the response to the client. Most of the modern browser are capable of handling compressed data. You will certainly get a huge performance boost if your page size is large.

For more details on HTTP compression visit http://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/d52ff289-94d3-4085-bc4e-24eb4f312e0e.mspx?mfr=true

Data Paging / Sorting

When ever using data grid to show data with paging enabled one thing needs to understood that if your query returned let say 5000 record and you are only showing 100 records per page the rest of the 4900 record will be discarding and the same will apply when ever you will change the page or apply sorting. The additional 4900 rows will definitely take up memory and if your database is located on a different server which is most commonly the case you will also be transferring unnecessary data over the network. Make sure you are only returning the required results to the ASP.NET application by filtering out the data in your database query and apply custom paging. SQL Server 2005 and onwards provide valuable function for ranking data that can be used to accomplish this.

Connection Pooling

Creating a connection to a database is a resource intensive process and takes time. Connection pooling allows you to reuse these connections saving time and resources. When a new connection is requested the connection pool managers first searches in the connection pool and if doesn’t finds one, it creates a new one. There are various things that need to be done to use connection pooling effectively:

Avoid Connection Leakage. This means that you opened a connection but didn’t close it. If you don’t close the connection the connection pool manager will never put it in the pool for later reuse until the GC is called.
Use the same connection string. Connection pool manager searches for similar connection in the pool by the connection string.
Use SQL Servers and .NET CLR Data performance counters to monitor pooling.
Open connections as late as possible and close them as early as possible
Don’t share same connection between multiple function calls. Instead open a new connection and close it in each function.
Close transactions prior to closing the connection.
Keep at least one connection open to maintain the connection pool.

Avoid Multiple Database Access

Avoid accessing database multiple times for the same request. Analyze your code and see if you can reduce the number of trips to database because these trips reduce the number of request per second your application can serve. You can do this by returning multiple records in the same stored proc, combining multiple DB operations in same stored proc etc.

Use DataReader Instead of DataSet

Use DataReader objects instead of DataSet when ever you need to display data. DataReader is the most efficient means of data retrieval as they are read and forward only. DataSet are disconnected and in-memory therefore uses valuable server resources. Only use them when you need the same data more then once or want to do some processing on the data.

Last but certainly not the least follow the best coding, design and deployment patterns and practices. Here are few more usefull links that can be very helpful in performance optimization of you ASP.NET application

SQL Server 2008 Data Compression Part-1

Tue, 24 Jun 2008 03:21:00 GMT

If most of you think that compression is a brand new feature introduced in SQL Server 2008 then I am afraid, you are wrong. It actually started with SQL Server 2005 when SP2 brought with itself a new storage format called Vardecimal.

Unlike Varchar, Vardecimal was a table-level option and affected the numeric data types stored in the underlying table.

SQL Server 2008 however goes beyond this and has introduced a full blown - Data Compression. Data Compression comes in two flavors:

Row Compression
Page Compression

Row Compression
Row compression changes the format of physical storage of data. Enabling row compression has the following affects on the underlying data:

It minimize the metadata (column information, length, offsets etc) associated with each record.
Numeric data types and fixed length strings are stored in variable-length storage format, just like Varchar.

It’s very simple to create a table with row compression enabled:

CREATE TABLE MyTable
(
      ID int identity Primary key,
      Name char(100),
      Email char(100)
)
WITH (DATA_COMPRESSION = Row);
GO

To enabled row compression on an existing table:

Alter TABLE MyTable REBUILD WITH (DATA_COMPRESSION=Row, MAXDOP=2);

Since compression is a CPU intensive process there is an additional setting MAXDOP which is used to specify the maximum number of processors to be used during compression operation.

Page Compression
The second and the most vital compression method is page compression. Page compression allows common data to be shared between rows for a given page. Its uses the following techniques to compress data:

Row compression. (Already discussed above)
Prefix Compression. For every column in a page duplicate prefixes are identified. These prefixes are saved in compression information headers (CI) which resides after page header. A reference number is assigned to these prefixes and that reference number is replaced where ever those prefixes are being used.
Dictionary Compression. Dictionary compression searches for duplicate values through out the page and stores them in CI. The main difference between prefix and dictionary compression is that prefix is only restricted to one column while dictionary is applicable to the complete page.

The syntax for enabling page compression is same as the one for row:

CREATE TABLE MyTable
(
      ID int identity Primary key,
      Name char(100),
      Email char(100)
)
WITH (DATA_COMPRESSION = Page);
GO

Alter TABLE MyTable REBUILD WITH (DATA_COMPRESSION=Page, MAXDOP=2);

There is however few important points that one needs to understand when using page compression. Page compression works on page level so the amount of benefit that you can get out of this really depends on the data distribution per page. You can get drastically different results by just moving the data around, changing the clustered Index or any other change that modifies the structure of data stored.

Example
It’s time to put every thing that we studied so for to a test. Create a sample table and fill it with some hypo theoretical data.

Create Table CompressionCheck
(
      [ID] Int Identity Primary Key,
      [CharColumn] Char(100),
      [NumericColumn] Numeric(18, 4),
      [DateColumn] DateTime
)
Go
Set NoCount On
Declare @Min int, @Max int, @Rand int, @Str varchar(100), @i int
Declare @Counter Int
Set @Counter = 1
While @Counter < 100001
Begin
      Set @Min = 65
      Set @Max = 90
    Set @i = 0
    Set @Str = ''
    While @i < CONVERT(int, RAND() * 100)
    Begin
        Set @Rand = (((@Max + 1) - @Min) * RAND()) + @Min
        Set @Str = @Str + char(@Rand)
        Set @i = @i + 1
    End
      Insert Into dbo.CompressionCheck(NumericColumn, DateColumn, CharColumn)
      Select RAND() * 10000, GETDATE() + Convert(Int, RAND() * 1000), @Str
      Set @Counter = @Counter + 1
End
Set NoCount Off

The above TSQL script creates a table CompressionCheck and inserts 100000 randomly generated records. Note that page compression is not yet enabled. To find the current space utilization by the table use sp_spaceused proc.

Exec sp_spaceused 'CompressionCheck';

Results:

The above stats suggest that 13 MB of storage space is currently being used by CompressionCheck table. Now let’s apply page compression and see the results.

DBCC DROPCLEANBUFFERS --Removes all clean buffers from the buffer pool
Go
ALTER TABLE CompressionCheck REBUILD WITH (DATA_COMPRESSION=PAGE);
Go

Exec sp_spaceused 'CompressionCheck';

Results:

Comparing both the results we have successfully managed to reduce the space allocated to CompressionCheck table from 13 MB to just 3.5 MB.