Possibly a faster DBCC CHECKDB

This article describes a utility that is able to report the most common DBCC CHECKDB errors significantly faster than DBCC CHECKDB does itself. This can be very important in determining quickly the correctness of data in large databases.
Now that I have your attention I should point out this utility is NOT a replacement for DBCC CHECKDB. However, I believe it will report on the most common cause of errors significantly faster that the usual DBCC CHECKDB routines.

Background

DBCC CHECKDB is great utility for ensuring data is correct. In my experience, most shops that have large databases run it on a weekly basis, since it can be a time consuming operation. It is a typical and recommended practice to restore a backup of the production database to another server and the DBCC CHECKDB is performed against this, thus freeing up the production server for other work.

The Problem

The main problem I hear about DBCC CHECKDB is that for large databases in particular, it can take a long time to run. This is exacerbated by the fact that if errors are found, it digs deeper into the data and takes even longer to complete.
I've known a typical DBCC CHECKDB on a 5 terabyte databases to take more than 10 hours to complete. However, when there were errors, the DBCC CHECKDB took in excess of 48 hours to complete.
It is possible to run a cut down version of DBCC CHECKDB using the PHYSICAL_ONLY option, however, even this takes significantly longer to run than the utility proposed in this article.

The Utility

The T-SQL code used to create this utility is attached to this article. The utility described here has the following advantages over DBCC CHECKDB:

• It runs much faster (typically in 10% of the time of DBCC CHECKDB)
• It stops immediately you get an error. This can represent a considerable saving in time over DBCC CHECKDB. On average it would identify an error in less than 5% of the time DBCC CHECKDB would take. Additionally DBCC CHECKDB takes even longer if an error is found.
• It processes heaps and clustered indexes first, this is important in determining how critical a problem is. A problem with heaps and clustered indexes is more serious since non-clustered indexes there can be rebuild from the heaps/clustered indexes.

There are also some limitations:

• It only reports on IO errors. However, a short email conversation with Paul Randal (who has written much of the DBCC CHECKDB code), confirmed that most errors from DBCC are due to IO problems. So maybe this limitation is not much of a disadvantage.
• Assumes the databases's page_verify_option is set to 2 (CHECKSUM). This is a typical and sensible setting.

How it works

This utility reads every page of the databases' heaps, clustered and non-clustered indexes, reporting immediately when it encounters any IO problems.
The first part of the script clears the buffer pool. This is the area of memory that contains data that has been read from the underlying physical disks. The buffer pool allows much faster access to data that is needed again. By clearing the buffer pool, using the command DBCC DROPCLEANBUFFERS, we ensure any data is read from the underlying physical disks, and this is where we want to find any errors.
The next part of the script gets details of the heaps, clustered indexes and non-clustered indexes to check. These details are stored in a temporary table named #IndexDetails.
Next we dynamically build up the SQL we want to execute. For each heap, clustered index and non-clustered index in #IndexDetails, we create the SQL that will count the number of rows in the underlying table, using an index hint. This index hint forces the underlying data to be read using the index number associated with the named heap, clustered index, or non-clustered index. Since we have cleared the buffer pool, the data is read from the physical disk, thus allowing us to check for any IO errors.
A typical example of the SQL we want to execute is:

SELECT COUNT_BIG(*) AS [TableName: NameOfTable. IndexName: NameOfIndex. IndexId: 1] 
 FROM [dbo].[NameOfTable] WITH (INDEX( 1));

The keyword COUNT_BIG is used because the tables may have more rows than the maximum value of the int data type.
The heap and clustered indexes are processed first (as shown by the ORDER BY statement, heaps have an indexid of 0 and clustered indexes have an indexid of 1). This ensures the most important underlying data structures (i.e. heaps and clustered indexes) are processed first.
For each heap, clustered index and non-clustered index processed, the name of the table, index, and indexid is output. This can be used to show the progress of the executing SQL. An example of this output is shown in figure 1 below:

Figure 1: example output showing the underlying table, index and indexid.
If an error occurs, the script stops, and an error message output. You can determine the heap/index in error by comparing the expected normal output (shown in figure 1) with the debug output shown in the message tab of SQL Server management Studio. Additionally inspecting the table msdb..suspect_pages will also provide error details.
An example error message is given below:

Msg 824, Level 24, State 2, Line 3
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:28254611; actual 0:0). It occurred during a read of page (1:28254611) in database ID 10 at offset 0x000035e4326000 in file 'K:\MSSQL.1\MSSQL\Data\Paris_Paris3.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Conclusion

The utility provided in this article can identify the most common IO errors significantly faster than DBCC CHECKDB. As such it should be useful to quickly identify errors. It should be noted again, this is NOT a replacement for DBCC CHECKDB which does many more things.

Credits

Ian Stirk has been working in IT as a developer, designer, and architect since 1987. He holds the following qualifications: M.Sc., MCSD.NET, MCDBA, and SCJP. He is a freelance consultant working with Microsoft technologies in London England. His new book, SQL Server DMVS in Action, was published in May 2011. He can be contacted at Ian_Stirk@yahoo.com.

The SQL Code

-- I. Stirk. ian_stirk@yahoo.com LightweightPageChecker utility...
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED-- Ensure buffer pool is empty.
DBCC DROPCLEANBUFFERS
-- Get details of all heaps, clustered indexes and non-clustered indexes to check.
SELECT 
 ss.name AS SchemaName
 , st.name AS TableName
 , s.name AS IndexName 
 , s.rowcnt AS 'Row Count' 
 , s.indidINTO #IndexDetailsFROM sys.sysindexes s INNER JOIN sys.tables st ON st.[object_id] = s.[id]
INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id]
WHERE s.id > 100 -- Only user tables
AND s.rowcnt >= 1 -- Ignore stats rows 
ORDER BY s.indid, [Row Count] DESC -- Do heaps and clustered first
DECLARE @CheckIndexesSQL NVARCHAR(MAX)
SET @CheckIndexesSQL = ''
-- Build SQL to read each page in each index (including clustered index).
SELECT @CheckIndexesSQL = @CheckIndexesSQL + CHAR(10) 
 + 'SELECT COUNT_BIG(*) AS [TableName: ' + SchemaName + '.' 
 + TableName + '. IndexName: ' + ISNULL(IndexName, 'HEAP') 
 + '. IndexId: ' + CAST(indid AS VARCHAR(3)) + '] FROM ' 
 + QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName) 
 + ' WITH (INDEX(' + CAST(indid AS VARCHAR(3)) + '));'
FROM #IndexDetails-- Debug.
DECLARE @StartOffset INT
DECLARE @Length INT
SET @StartOffset = 0
SET @Length = 4000
WHILE (@StartOffset < LEN(@CheckIndexesSQL))
BEGIN
 PRINT SUBSTRING(@CheckIndexesSQL, @StartOffset, @Length)
 SET @StartOffset = @StartOffset + @LengthEND
PRINT SUBSTRING(@CheckIndexesSQL, @StartOffset, @Length)
-- Do work.
EXECUTE sp_executesql @CheckIndexesSQL-- Tidy up.
DROP TABLE #IndexDetails

Resources:

A faster DBCC.doc

Resize my browser

Hi All,

Ever tried to resize your browser to check how your website adapts with different resolution "in a flash"? Here is the link that helps you do that.

EPPlus - Open Source Excel export library for Excel 2007/2010 using .NET

Create advanced Excel 2007/2010 spreadsheets on the server

EPPlus is a .net library that reads and writes Excel 2007/2010 files using the Open Office Xml format (xlsx).
 
EPPlus supports:

• Cell Ranges
• Cell styling (Border, Color, Fill, Font, Number, Alignments)
• Charts
• Pictures
• Shapes
• Comments
• Tables
• Protection
• Encryption
• Pivot tables
• Data validation
• Many more...

Overview

This project started with the source from ExcelPackage. It was a great project to start from.
It had the basic functionality needed to read and write a spreadsheet.
Advantages over other:

• Totally rewritten using dictionaries
• Can now load 50 000 cells in seconds
• Complete integration with .NET

Examples

To see how this works let’s do a short walkthrough of sample 6 that creates a report on a directory in the file system.
The spreadsheet is created without any template.
First sheet is a list of subdirectories and files, with an icon, name, size, and dates. The second sheet contains some statistics...

• Content sheet
• Statistics sheet

Version 2.8 has added support for enumeration of cells....

• EPPlus and Linq

 Heres an example how you can use EPPlus in a webapplication...

• Web Applications

 Here's a few screenshots from the sample project...

• Screenshots

Find more here...

Wealth of Sql and Relational DB resources

Hi All,

Here is the link to get the wealth of information for Sql and Relational DB System

Sql Wealth

Understanding the SQL Server NOLOCK hint

Problem

I see the use of the NOLOCK hint in existing code for my stored procedures and I am not exactly sure if this is helpful or not.  It seems like this has been a practice that was put in place and now is throughout all of the code wherever there are SELECT statements.  Can you explain the what NOLOCK does and whether this is a good practice or not?

Solution

It seems that in some SQL Server shops the use of the NOLOCK (aka READUNCOMMITED) hint is used throughout the application.  In this tip we take a closer look at how this works and what the issues maybe when using NOLOCK.

Example

Let's walk through some simple examples to see how this works. (These queries are run against the AdventureWorks database.)
Here is a query that returns all of the data from the Person.Contact table. If I run this query I can see there is only one record that has a Suffix value for ContactID = 12.

SELECT * FROM Person.Contact WHERE ContactID < 20

Let's say another user runs the below query in a transaction.  The query completes and updates the records, but it is not yet committed to the database so the records are locked.

-- run in query window 1
BEGIN TRAN
UPDATE Person.Contact SET Suffix = 'B' WHERE ContactID < 20
-- ROLLBACK or COMMIT

If I run the same query from above again you will notice that it never completes, because the UPDATE has not yet been committed.

-- run in query window 2
SELECT * FROM Person.Contact WHERE ContactID < 20

If I run sp_who2 I can see that the SELECT statement is being blocked.  I will need to either cancel this query or COMMIT or ROLLBACK the query in window one for this to complete.  For this example I am going to cancel the SELECT query.

To get around the locked records, I can use the NOLOCK hint as shown below and the query will complete even though the query in window 1 is still running and has not been committed or rolled back.

-- run in query window 2
SELECT * FROM Person.Contact WITH (NOLOCK) WHERE ContactID < 20

If you notice below the Suffix column now has "B" for all records.  This is because the UPDATE in window 1 updated these records.  Even though that transaction has not been committed, since we are using the NOLOCK hint SQL Server ignores the locks and returns the data.  If the UPDATE is rolled back the data will revert back to what it looked like before, so this is considered a Dirty Read because this data may or may not exist depending on the final outcome in query window 1.

If I rollback the UPDATE using the ROLLBACK command and rerun the SELECT query we can see the Suffix is back to what it looked like before.

-- run in query window 1
ROLLBACK

-- run in query window 2
SELECT * FROM Person.Contact WITH (NOLOCK) WHERE ContactID < 20
-- or
SELECT * FROM Person.Contact WHERE ContactID < 20

So the issue with using the NOLOCK hint is that there is the possibility of reading data that has been changed, but not yet committed to the database.  If you are running reports and do not care if the data might be off then this is not an issue, but if you are creating transactions where the data needs to be in a consistent state you can see how the NOLOCK hint could return false data.

---

Locks

So what kind of locking is used when the NOLOCK hint is used.
If we run our SELECT without NOLOCK we can see the locks that are taken if we use sp_lock.  (To get the lock information I ran sp_lock in another query window while this was running.)

SELECT * FROM Person.Contact WHERE ContactID < 20

If we do the same for our SELECT with the NOLOCK we can see these locks.

SELECT * FROM Person.Contact WITH (NOLOCK) WHERE ContactID < 20

The differences are that there is a "S" shared access lock that is put on the page (PAG) that we are reading for the first 19 rows of data in the table when we don't use NOLOCK.  Also, we are getting a Sch-S lock versus an IS lock for the table (TAB). 
So another thing to point out is that even when you just SELECT data SQL Server still creates a lock to make sure the data is consistent.
These are the lock types and the lock modes that are used for the above two queries.

Lock Types

• MD - metadata lock
• DB - database lock
• TAB - table lock
• PAG - page lock

Mode

• S - Shared access
• Sch-S - Schema stability makes sure the schema is not changed while object is in use
• IS - Intent shared indicates intention to use S locks

---

READUNCOMMITED

The NOLOCK hint is the same as the READUNCOMMITED hint and can be used as follows with the same results.

SELECT * FROM Person.Contact WITH (READUNCOMMITTED)

---

SELECT statements only

The NOLOCK and READUNCOMMITED hints are only allowed with SELECT statements. If we try to use this for an UPDATE, DELETE or INSERT we will get an error.

UPDATE Person.Contact with (NOLOCK) SET Suffix = 'B' WHERE ContactID < 20

Msg 1065, Level 15, State 1, Line 15
The NOLOCK and READUNCOMMITTED lock hints are not allowed for target tables of INSERT, UPDATE, DELETE or MERGE statements.

---

Schema Change Blocking

Since a NOLOCK hint needs to get a Sch-S (schema stability) lock, a SELECT using NOLOCK could still be blocked if a table is being altered and not committed. Here is an example.

-- run in query window 1
BEGIN TRAN
ALTER TABLE Person.Contact ADD column_b VARCHAR(20) NULL ;

If we try to run our SELECT statement it will be blocked until the above is committed or rolled back.

-- run in query window 2
SELECT * FROM Person.Contact WITH (NOLOCK) WHERE ContactID < 20

---

Issues

We mentioned above how you can get dirty reads using the NOLOCK hint.  These are also other terms you may encounter for this hint.

• Dirty Reads - this occurs when updates are done, so the data you select could be different.
• Nonrepeatable Reads - this occurs when you need to read the data more than once and the data changes during that process
• Phantom Reads - occurs where data is inserted or deleted and the transaction is rolled back.  So for the insert you will get more records and for the delete you will get less records.

To learn more about these issues read this article: http://msdn.microsoft.com/en-us/library/ms190805.aspx

---

Isolation Level

You can also set the Isolation Level for all queries instead of using the NOLOCK or READUNCOMMITTED hint. The isolation level will apply the READUNCOMMITTED to all SELECT statements that are performed from when this is turned on until it is turned off.
In the example below, the two SELECT statements will use the READUNCOMMITED or NOLOCK hint and the UPDATE will still function as normal.  This way you can set a whole batch of statements instead of modifying each query.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- turn it on

   SELECT * FROM Person.Contact WHERE ContactID < 20

   UPDATE Person.Contact SET Suffix = 'B' WHERE ContactID = 1

   SELECT * FROM Person.Contact WHERE ContactID < 20

SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- turn it off

Next Steps

• Now that you have a better understanding of how this works check your code to see if there are instances where the NOLOCK hint doesn't make sense.

Creating and Using a CAPTCHA in ASP.NET MVC

A CAPTCHA is a challenge-response system wherein typically a dynamically generated image consisting of random letters and numbers is displayed to the users and the users are asked to re-enter the text in an attempt to verify that both of them match. Any ASP.NET developer would want to prevent spam and automated form submissions in the website being developed and that is where CAPTCHA is extremely useful. The primary purpose of CAPTCHA is to ensure that data is being submitted by humans and not by an automated software system. CAPTCHA is frequently used in user registration and contact forms to prevent spam and abuse of the facility. In this article you will learn how a simple CAPTCHA system can be developed in ASP.NET MVC.

Basic Steps

Before you delve into further details create a new ASP.NET MVC 3 application with ASPX views. Once created add a new controller named Home into the Controllers folder. You will be adding the required action methods to the Home controller later.

Creating a Custom Action Result

An action result indicates the response from an action method and is represented by System.Web.Mvc.ActionResult class. Though most of the action methods can use ActionResult class as their return value you can also create a custom action result class. In fact, the MVC framework includes several classes that inherit from the ActionResult base class. Many of them are shown in the following figure.

Figure 1: The MVC framework includes several classes

In order to create a CAPTCHA system it is necessary that we emit a dynamically generated image with random text onto the response stream. To meet this requirement you will create a custom Action Result class. The new action result will inherit from ActionResult base class. To create a new action result, add a new class in the Models folder and name it CaptchaImageResult. The following listing shows the empty CaptchaImageResult class.

public class CaptchaImageResult:ActionResult
{
}

Generating Random Strings

The CaptchaImageResult class needs to display random string values consisting of letters and numbers. To generate such random strings you will need to create a method inside the CaptchaImageResult class that does the job for you. The GetCaptchaString() method as shown below is responsible for creating fixed length strings consisting of alpha-numeric values.

public string GetCaptchaString(int length)
{
    int intZero = '0';
    int intNine = '9';
    int intA = 'A';
    int intZ = 'Z';
    int intCount = 0;
    int intRandomNumber = 0;
    string strCaptchaString="";
 
    Random random = new Random(System.DateTime.Now.Millisecond);
 
    while (intCount < length)
    {
        intRandomNumber = random.Next(intZero, intZ);
        if (((intRandomNumber >= intZero) && (intRandomNumber <= intNine) || (intRandomNumber >= intA) && (intRandomNumber <= intZ)))
        {
            strCaptchaString = strCaptchaString + (char)intRandomNumber;
            intCount = intCount + 1;
        }
    }
    return strCaptchaString;
}

As you can see, the GetCaptchaString() method accepts a single parameter - length - that indicates the length of the resultant CAPTCHA string. The four variables intZero, intNine, intA and intZ simply store ASCII values for numbers (0-9) and letters (A-Z). Note that we are using only upper case letters in the above logic. You can, of course, add lower case letters if required. Inside the while loop, Random class is used to generate random numbers between a range of 0-9 and A-Z. The random number obtained from the Random class is converted into a character value and stored in strCaptchaString variable. Finally, the complete CAPTCHA string is returned back.

Outputting an Image Dynamically in the Response Stream

Generating a random string is just one part of the story. More important is to emit this random string as an image in the response stream. Emitting the random string as an image will make it difficult for automated software programs to read the CAPTCHA string from the HTML source. To generate an image dynamically, you need to override ExecuteResult() method of the ActionResult base class. The ExecuteResult() method is responsible for any custom processing that the child class wants to plug-in. The complete ExecuteResult() method is shown below:

public override void ExecuteResult(ControllerContext context)
{
    Bitmap bmp = new Bitmap(100, 30);
    Graphics g = Graphics.FromImage(bmp);
    g.Clear(Color.Navy);
    string randomString = GetCaptchaString(6);
    context.HttpContext.Session["captchastring"] = randomString;
    g.DrawString(randomString, new Font("Courier", 16), new SolidBrush(Color.WhiteSmoke), 2, 2);
    HttpResponseBase response = context.HttpContext.Response;
    response.ContentType = "image/jpeg";
    bmp.Save(response.OutputStream,ImageFormat.Jpeg);
    bmp.Dispose();
}

The ExecuteResult() method has one parameter - ControllerContext - that provides the context information in which the method is being called. For example, the HTTP response stream is accessed using the HttpContext provided by the ControllerContext parameter. The method first creates a Bitmap of size 100 X 30. It then washes it with a Navy background. A random CAPTCHA string with a length of 6 characters is generated by calling the GetCaptchaString() method. This random string is then stored in a Session variable so that you can compare the user input later. The DrawString() method of the Graphics object draws the random string onto the image. The image is then saved onto the response stream in JPEG format.
In the above example, you are using a simplistic approach of generating CAPTCHA images. You can also make use of certain techniques (distorting the characters or emitting two sets of characters for example) that make it even more difficult for the automated software programs to read them.

Showing a CAPTCHA Image on a View

Next, you need to create an action method in the Home controller as shown below:

public CaptchaImageResult ShowCaptchaImage()
{
    return new CaptchaImageResult();
}

The ShowCaptchaImage() method simply returns a new instance of CaptchaImageResult class you created earlier. Note that the MVC framework takes care of calling the ExecuteResult() overridden method for you.
The ShowCaptchaImage() action method is used in the Index view as shown below:

Captcha in ASP.NET MVC
<%
using (Html.BeginForm("index", "home"))
{
%>

Please enter the string as shown above:
<%= Html.TextBox("CaptchaText")%>

<%
}
%>
<%= ViewBag.Message %>

As you can see, the above markup creates an HTML form. Notice how the src attribute of the tag is pointing to the ShowCaptchaImage action method. This way whenever the image is rendered it invokes the ShowCaptchaImage() method and a CAPTCHA image is outputted. The TextBox allows the user to enter the text as seen on the CAPTCHA.
The following figure shows the Index view in action:

Figure 2: The Index View

Verifying the CAPTCHA Value

Before you run the Index view, you need to add Index() action methods as shown below:

public ActionResult Index()
{
    ViewBag.Message = "A fresh CAPTCHA image is being displayed!";
    return View();
}
 
[HttpPost]
public ActionResult Index(string CaptchaText)
{
    if (CaptchaText == HttpContext.Session["captchastring"].ToString())
        ViewBag.Message = "CAPTCHA verification successful!";
    else
        ViewBag.Message = "CAPTCHA verification failed!";
 
    return View();
}

The first Index() action method takes care of GET requests whereas the second one takes care of POST requests (as indicated by [HttpPost] attribute). The first Index() method simply puts a Message in the ViewBag and shows the Index view (Figure 2). When the user enters the CAPTCHA value in the TextBox and submits the form, the second Index() action method compares the submitted value with the value stored in the Session earlier (recollect that ExecuteResult() method stores the CAPTCHA text in a session variable). Depending on the outcome of the comparison either a success or failure message is displayed in the view.

Summary

A CAPTCHA is a challenge-response system frequently used to keep away spammers and automated software programs from web pages accepting data from the end users. Common examples of such pages include user registration forms and contact forms. In order to create your own CAPTCHA system in ASP.NET MVC you create a custom ActionResult class that generates a random string value and then emits it as an image on the response stream. You can add more features to the CAPTCHA you developed in this article to make it more difficult for automated software programs to read the CAPTCHA text.

Save Performance Counters in Windows Server 2008 or Windows 7

If you’re writing multi-user applications like just about any ASP.NET application, it’s probably worth your time to get familiar with performance counters.  Performance Monitor, or perfmon for short, is the tool you use to view these counters on your computer or server.  You can add it to an MMC instance (run MMC and add the performance snap-in) or you can just run perfmon.  (Sidenote – I once got dinged by a manager for mis-spelling perfmon in a proposal for a client.  I forget what he thought I was trying to spell but it made no sense.  In any event, trust me, perfmon is really how it’s spelt).  There are about 3 counters that you can work with in Windows.  Wait, did I say 3?  I meant about 3000+.  Of these, there really are only a few that are going to be of interest to you at any particular moment, and the rest are just noise.  Once you’ve identified the ones you care about, it’s nice to be able to save those to the desktop or a sync folder so that you can easily view them again later.

In the old days…
Once upon a time, it was possible to just save the settings of a perfmon session.  Then you could just double-click on the saved file, and voila! You had your settings running again.
Sadly, this no longer works,  and if you’re used to this simple approach, it can be quite difficult to figure out how to achieve this same functionality using the latest Windows OSes (Windows Server 2008, Windows 7, et al).  Thanks to James Kehr (of ORCSWeb fame) for providing me with the answer to this. 
Saving and Reloading Performance Counters with Perfmon
There are actually a few ways you can achieve this.  The first one is the one I find myself using the most, although the latter is really the way I was familiar with in previous OSes.  You can copy the properties of a given perfmon session by clicking on the Copy Properties icon ( ) next to the highlight icon above the graph.  Next, open up Notepad and paste into it.  Save this file somewhere handy.  Next time you want to get back to the settings you were using, just open the file, copy all of the text in it, and click on the Paste Properties icon ().  If you’re interested, here’s what the data looks like (click to enlarge):

Another way you can save your settings it through Data Collector Sets.  Once you have one defined and have run it to completion, you’ll have Reports you can view.  You can copy and paste counters from one report to another.  So, if you define a Data Collector Set and add some performance counters to it, then run it, you’ll have some reports to look at.  You can look at any of these reports and simply highlight the counters you want, Copy, and then go back up to Performance Monitor (the live view) and Paste, and you’ll have the counters.  This is what I do now in practice, since the counters I want to watch are in user defined Data Collector Sets now already (so I can view a whole day or arbitrary period in a day after-the-fact). 
The third solution is to use the MMC.  Run mmc from Start-Run, then go to File – Add or Remove Snap-ins.  Add Performance to the selected snap-ins:

Add whatever counters you want, then simply save it.  Back when I used to do this all the time I would have a perfmon.msc file on my server’s desktop that had the counters I was most interested in.

Close the MMC and you should be able to open the saved .msc file by simply double-clicking on it.  This is exactly the behavior that I was used to on previous versions of Windows Server.  Apparently the change is that perfmon no longer launches the performance monitor tool inside an MMC instance, but rather as a standalone process (which doesn’t directly support Save).
Ref: http://stevesmithblog.com/blog/how-do-i-save-performance-counters-in-windows-server-2008-or-windows-7/

WebGrid in ASP.NET MVC

Earlier this year Microsoft released ASP.NET MVC version 3 (asp.net/mvc), as well as a new product called WebMatrix (asp.net/webmatrix). The WebMatrix release included a number of productivity helpers to simplify tasks such as rendering charts and tabular data. One of these helpers, WebGrid, lets you render tabular data in a very simple manner with support for custom formatting of columns, paging, sorting and asynchronous updates via AJAX.
In this article, I’ll introduce WebGrid and show how it can be used in ASP.NET MVC 3, then take a look at how to really get the most out of it in an ASP.NET MVC solution. (For an overview of WebMatrix—and the Razor syntax that will be used in this article—see Clark Sell’s article, “Introduction to WebMatrix,” in the April 2011 issue at msdn.microsoft.com/magazine/gg983489).
This article looks at how to fit the WebGrid component into an ASP.NET MVC environment to enable you to be productive when rendering tabular data. I’ll be focusing on WebGrid from an ASP.NET MVC aspect: creating a strongly typed version of WebGrid with full IntelliSense, hooking into the WebGrid support for server-side paging and adding AJAX functionality that degrades gracefully when scripting is disabled. The working samples build on top of a service that provides access to the AdventureWorksLT database via the Entity Framework. If you’re interested in the data-access code, it’s available in the code download, and you might also want to check out Julie Lerman’s article, “Server-Side Paging with the Entity Framework and ASP.NET MVC 3,” in the March 2011 issue (msdn.microsoft.com/magazine/gg650669).

Getting Started with WebGrid

To show a simple example of WebGrid, I’ve set up an ASP.NET MVC action that simply passes an IEnumerable to the view. I’m using the Razor view engine for most of this article, but later I’ll also discuss how the WebForms view engine can be used. My ProductController class has the following action:

public ActionResult List()
  {
    IEnumerable model =
      _productService.GetProducts();
 
    return View(model);
  }

The List view includes the following Razor code, which renders the grid shown in Figure 1:

@model IEnumerable< span="">.Domain.Product>
@{
  ViewBag.Title = "Basic Web Grid";
}
< span="">>Basic Web Grid
< span="">>
@{
  var grid = new WebGrid(Model, defaultSort:"Name");
}
@grid.GetHtml()

(click to zoom)
Figure 1 A Basic Rendered Web Grid
The first line of the view specifies the model type (for example, the type of the Model property that we access in the view) to be IEnumerable. Inside the div element I then instantiate a WebGrid, passing in the model data; I do this inside an @{...} code block so that Razor knows not to try to render the result. In the constructor I also set the defaultSort parameter to “Name” so theWebGrid knows that the data passed to it is already sorted by Name. Finally, I use @grid.GetHtml() to generate the HTML for the grid and render it into the response.
This small amount of code provides rich grid functionality. The grid limits the amount of data displayed and includes pager links to move through the data; column headings are rendered as links to enable paging. You can specify a number of options in the WebGrid constructor and the GetHtml method in order to customize this behavior. The options let you disable paging and sorting, change the number of rows per page, change the text in the pager links and much more. Figure 2 shows the WebGrid constructor parameters and Figure 3 the GetHtml parameters.
Figure 2 WebGrid Constructor Parameters

Name	Type	Notes
source	IEnumerable	The data to render.
columnNames	IEnumerable	Filters the columns that are rendered.
defaultSort	string	Specifies the default column to sort by.
rowsPerPage	int	Controls how many rows are rendered per page (default is 10).
canPage	bool	Enables or disables paging of data.
canSort	bool	Enables or disables sorting of data.
ajaxUpdateContainerId	string	The ID of the grid’s containing element, which enables AJAX support.
ajaxUpdateCallback	string	The client-side function to call when the AJAX update is complete.
fieldNamePrefix	string	Prefix for query string fields to support multiple grids.
pageFieldName	string	Query string field name for page number.
selectionFieldName	string	Query string field name for selected row number.
sortFieldName	string	Query string field name for sort column.
sortDirectionFieldName	string	Query string field name for sort direction.

Figure 3 WebGrid.GetHtml Parameters

Name	Type	Notes
tableStyle	string	Table class for styling.
headerStyle	string	Header row class for styling.
footerStyle	string	Footer row class for styling.
rowStyle	string	Row class for styling (odd rows only).
alternatingRowStyle	string	Row class for styling (even rows only).
selectedRowStyle	string	Selected row class for styling.
caption	string	The string displayed as the table caption.
displayHeader	bool	Indicates whether the header row should be displayed.
fillEmptyRows	bool	Indicates whether the table can add empty rows to ensure the rowsPerPage row count.
emptyRowCellValue	string	Value used to populate empty rows; only used when fillEmptyRows is set.
columns	IEnumerable	Column model for customizing column rendering.
exclusions	IEnumerable	Columns to exclude when auto-populating columns.
mode	WebGridPagerModes	Modes for pager rendering (default is NextPrevious and Numeric).
firstText	string	Text for a link to the first page.
previousText	string	Text for a link to the previous page.
nextText	string	Text for a link to the next page.
lastText	string	Text for a link to the last page.
numericLinksCount	int	Number of numeric links to display (default is 5).
htmlAttributes	object	Contains the HTML attributes to set for the element.

The previous Razor code will render all of the properties for each row, but you may want to limit which columns are displayed. There are a number of ways to achieve this. The first (and simplest) is to pass the set of columns to the WebGrid constructor. For example, this code renders just the Name and ListPrice properties:

var grid = new WebGrid(Model, columnNames: new[] {"Name", "ListPrice"});

You could also specify the columns in the call to GetHtml instead of in the constructor. While this is slightly longer, it has the advantage that you can specify additional information about how to render the columns. In the following example, I specified the header property to make the ListPrice column more user-friendly:

@grid.GetHtml(columns: grid.Columns(
 grid.Column("Name"),
 grid.Column("ListPrice", header:"List Price")
 )
)

Often when you render a list of items, you want to let users click on an item to navigate to the Details view. The format parameter of the Column method allows you to customize the rendering of a data item. The following code shows how to change the rendering of names to output a link to the Details view for an item, and outputs the list price with two decimal places as typically expected for currency values; the resulting output is shown in Figure 4.

@grid.GetHtml(columns: grid.Columns(
 grid.Column("Name", format: @@Html.ActionLink((string)item.Name,
            "Details", "Product", new {id=item.ProductId}, null)),
 grid.Column("ListPrice", header:"List Price", 
             format: @@item.ListPrice.ToString("0.00"))
 )
)

Figure 4 A Basic Grid with Custom Columns
Although it looks like there’s some magic going on when I specify the format, the format parameter is actually a Func—a delegate that takes a dynamic parameter and returns an object. The Razor engine takes the snippet specified for the format parameter and turns it into a delegate. That delegate takes a dynamic parameter named item, and this is the item variable that’s used in the format snippet. For more information on the way these delegates work, see Phil Haack’s blog post at bit.ly/h0Q0Oz.
Because the item parameter is a dynamic type, you don’t get any IntelliSense or compiler checking when writing your code (see Alexandra Rusina’s article on dynamic types in the February 2011 issue at msdn.microsoft.com/magazine/gg598922). Moreover, invoking extension methods with dynamic parameters isn’t supported. This means that, when calling extension methods, you have to ensure that you’re using static types—this is the reason that item.Name is cast to a string when I call the Html.ActionLink extension method in the previous code. With the range of extension methods used in ASP.NET MVC, this clash between dynamic and extension methods can become tedious (even more so if you use something like T4MVC: bit.ly/9GMoup).

Adding Strong Typing

While dynamic typing is probably a good fit for WebMatrix, there are benefits to strongly typed views. One way to achieve this is to create a derived type WebGrid, as shown in Figure 5. As you can see, it’s a pretty lightweight wrapper!
Figure 5 Creating a Derived WebGrid

public class WebGrid : WebGrid
  {
    public WebGrid(
      IEnumerable source = null,
      ... parameter list omitted for brevity)
    : base(
      source.SafeCast<object>(), 
      ... parameter list omitted for brevity)
    { }
  public WebGridColumn Column(
              string columnName = null, 
              string header = null, 
              Func, object> format = null, , 
              string style = null, 
              bool canSort = true)
    {
      Func, object> wrappedFormat = null;, 
      if (format != null)
      {
        wrappedFormat = o => format((T)o.Value);
      }
      WebGridColumn column = base.Column(
                    columnName, header, 
                    wrappedFormat, style, canSort);
      return column;
    }
    public WebGrid Bind(
            IEnumerable source, 
            IEnumerable<string> columnNames = null, 
            bool autoSortAndPage = true, 
            int rowCount = -1)
    {
      base.Bind(
           source.SafeCast<object>(), 
           columnNames, 
           autoSortAndPage, 
           rowCount);
      return this;
    }
  }
 
  public static class WebGridExtensions
  {
    public static WebGrid Grid(
             this HtmlHelper htmlHelper,
             ... parameter list omitted for brevity)
    {
      return new WebGrid(
        source, 
        ... parameter list omitted for brevity);
    }
  }

So what does this give us? With the new WebGrid implementation, I’ve added a new Column method that takes a Func for the format parameter, which means that the cast isn’t required when calling extension methods. Also, you now get IntelliSense and compiler checking (assuming that MvcBuildViews is turned on in the project file; it’s turned off by default).
The Grid extension method allows you to take advantage of the compiler’s type inference for generic parameters. So, in this example, you can write Html.Grid(Model) rather than new WebGrid(Model). In each case, the returned type is WebGrid.

Adding Paging and Sorting

You’ve already seen that WebGrid gives you paging and sorting functionality without any effort on your part. You’ve also seen how to configure the page size via the rowsPerPage parameter (in the constructor or via the Html.Grid helper) so that the grid will automatically show a single page of data and render the paging controls to allow navigation between pages. However, the default behavior may not be quite what you want. To illustrate this, I’ve added code to render the number of items in the data source after the grid is rendered, as shown in Figure 6.

Figure 6 The Number of Items in the Data Source
As you can see, the data we’re passing contains the full list of products (295 of them in this example, but it’s not hard to imagine scenarios with even more data being retrieved). As the amount of data returned increases, you place more and more load on your services and databases, while still rendering the same single page of data. But there’s a better approach: server-side paging. In this case, you pull back only the data needed to display the current page (for instance, only five rows).
The first step in implementing server-side paging for WebGrid is to limit the data retrieved from the data source. To do this, you need to know which page is being requested so you can retrieve the correct page of data. When WebGrid renders the paging links, it reuses the page URL and attaches a query string parameter with the page number, such as http://localhost:27617/Product/DefaultPagingAndSorting?page=3 (the query string parameter name is configurable via the helper parameters—handy if you want to support pagination of more than one grid on a page). This means you can take a parameter called page on your action method and it will be populated with the query string value.
If you just modify the existing code to pass a single page worth of data to WebGrid, WebGrid will see only a single page of data. Because it has no knowledge that there are more pages, it will no longer render the pager controls. Fortunately, WebGrid has another method, named Bind, that you can use to specify the data. As well as accepting the data, Bind has a parameter that takes the total row count, allowing it to calculate the number of pages. In order to use this method, the List action needs to be updated to retrieve the extra information to pass to the view, as shown in Figure 7.
Figure 7 Updating the List Action

public ActionResult List(int page = 1)
{
  const int pageSize = 5;
 
  int totalRecords;
  IEnumerable products = productService.GetProducts(
    out totalRecords, pageSize:pageSize, pageIndex:page-1);
            
  PagedProductsModel model = new PagedProductsModel
                                 {
                                   PageSize= pageSize,
                                   PageNumber = page,
                                   Products = products,
                                   TotalRows = totalRecords
                                 };
  return View(model);
}

With this additional information, the view can be updated to use the WebGrid Bind method. The call to Bind provides the data to render and the total number of rows, and also sets the autoSortAndPage parameter to false. The autoSortAndPage parameter instructs WebGrid that it doesn’t need to apply paging, because the List action method is taking care of this. This is illustrated in the following code:

< span="">>
@{
  var grid = new WebGrid< span="">>(null, rowsPerPage: Model.PageSize, 
    defaultSort:"Name");
  grid.Bind(Model.Products, rowCount: Model.TotalRows, autoSortAndPage: false);
}
@grid.GetHtml(columns: grid.Columns(
 grid.Column("Name", format: @< span="">>@Html.ActionLink(item.Name, 
   "Details", "Product", new { id = item.ProductId }, null)),
  grid.Column("ListPrice", header: "List Price", 
    format: @< span="">>@item.ListPrice.ToString("0.00"))
  )
 )
 

With these changes in place, WebGrid springs back to life, rendering the paging controls but with the paging happening in the service rather than in the view! However, with autoSortAndPage turned off, the sorting functionality is broken. WebGrid uses query string parameters to pass the sort column and direction, but we instructed it not to perform the sorting. The fix is to add the sort and sortDir parameters to the action method and pass these through to the service so that it can perform the necessary sorting, as shown in Figure 8.
Figure 8 Adding Sorting Parameters to the Action Method

public ActionResult List(
           int page = 1, 
           string sort = "Name", 
           string sortDir = "Ascending" )
{
  const int pageSize = 5;
 
  int totalRecords;
  IEnumerable products =
    _productService.GetProducts(out totalRecords,
                                pageSize: pageSize,
                                pageIndex: page - 1,
                                sort:sort,
                                sortOrder:GetSortDirection(sortDir)
                                );
 
  PagedProductsModel model = new PagedProductsModel
  {
    PageSize = pageSize,
    PageNumber = page,
    Products = products,
    TotalRows = totalRecords
  };
  return View(model);
}

AJAX: Client-Side Changes

WebGrid supports asynchronously updating the grid content using AJAX. To take advantage of this, you just have to ensure the div that contains the grid has an id, and then pass this id in the ajaxUpdateContainerId parameter to the grid’s constructor. You also need a reference to jQuery, but that’s already included in the layout view. When the ajaxUpdateContainerId is specified, WebGrid modifies its behavior so that the links for paging and sorting use AJAX for the updates:

< span=""> id="grid">
 
@{
  var grid = new WebGrid< span="">>(null, rowsPerPage: Model.PageSize, 
  defaultSort: "Name", ajaxUpdateContainerId: "grid");
  grid.Bind(Model.Products, autoSortAndPage: false, rowCount: Model.TotalRows);
}
@grid.GetHtml(columns: grid.Columns(
 grid.Column("Name", format: @< span="">>@Html.ActionLink(item.Name, 
   "Details", "Product", new { id = item.ProductId }, null)),
 grid.Column("ListPrice", header: "List Price", 
   format: @< span="">>@item.ListPrice.ToString("0.00"))
 )
)
 

While the built-in functionality for using AJAX is good, the generated output doesn’t function if scripting is disabled. The reason for this is that, in AJAX mode, WebGrid renders anchor tags with the href set to “#,” and injects the AJAX behavior via the onclick handler.
I’m always keen to create pages that degrade gracefully when scripting is disabled, and generally find that the best way to achieve this is through progressive enhancement (basically having a page that functions without scripting that’s enriched with the addition of scripting). To achieve this, you can revert back to the non-AJAX WebGrid and create the script in Figure 9 to reapply the AJAX behavior:
Figure 9 Reapplying the AJAX Behavior

$(document).ready(function () {
 
  function updateGrid(e) {
    e.preventDefault();
    var url = $(this).attr('href');
    var grid = $(this).parents('.ajaxGrid'); 
    var id = grid.attr('id');
    grid.load(url + ' #' + id);
  };
  $('.ajaxGrid table thead tr a').live('click', updateGrid);
  $('.ajaxGrid table tfoot tr a').live('click', updateGrid);
 });

To allow the script to be applied just to a WebGrid, it uses jQuery selectors to identify elements with the ajaxGrid class set. The script establishes click handlers for the sorting and paging links (identified via the table header or footer inside the grid container) using the jQuery live method (api.jquery.com/live). This sets up the event handler for existing and future elements that match the selector, which is handy given the script will be replacing the content.
The updateGrid method is set as the event handler and the first thing it does is to call preventDefault to suppress the default behavior. After that it gets the URL to use (from the href attribute on the anchor tag) and then makes an AJAX call to load the updated content into the container element. To use this approach, ensure that the default WebGrid AJAX behavior is disabled, add the ajaxGrid class to the container div and then include the script from Figure 9.

AJAX: Server-Side Changes

One additional point to call out is that the script uses functionality in the jQuery load method to isolate a fragment from the returned document. Simply calling load(‘http://example.com/someurl’) will load the contents of the URL. However, load(‘http://example.com/someurl #someId’) will load the content from the specified URL and then return the fragment with the id of “someId.” This mirrors the default AJAX behavior of WebGrid and means that you don’t have to update your server code to add partial rendering behavior; WebGrid will load the full page and then strip out the new grid from it.
In terms of quickly getting AJAX functionality this is great, but it means you’re sending more data over the wire than is necessary, and potentially looking up more data on the server than you need to as well. Fortunately, ASP.NET MVC makes dealing with this pretty simple. The basic idea is to extract the rendering that you want to share in the AJAX and non-AJAX requests into a partial view. The List action in the controller can then either render just the partial view for AJAX calls or the full view (which in turn uses the partial view) for the non-AJAX calls.
The approach can be as simple as testing the result of the Request.IsAjaxRequest extension method from inside your action method. This can work well if there are only very minor differences between the AJAX and non-AJAX code paths. However, often there are more significant differences (for example, the full rendering requires more data than the partial rendering). In this scenario you’d probably write an AjaxAttribute so you could write separate methods and then have the MVC framework pick the right method based on whether the request is an AJAX request (in the same way that the HttpGet and HttpPost attributes work). For an example of this, see my blog post at bit.ly/eMlIxU.

WebGrid and the WebForms View Engine

So far, all of the examples outlined have used the Razor view engine. In the simplest case, you don’t need to change anything to use WebGrid with the WebForms view engine (aside from differences in view engine syntax). In the preceding examples, I showed how you can customize the rendering of row data using the format parameter:

grid.Column("Name", 
  format: @@Html.ActionLink((string)item.Name, 
  "Details", "Product", new { id = item.ProductId }, null)),

The format parameter is actually a Func, but the Razor view engine hides that from us. But you’re free to pass a Func—for example, you could use a lambda expression:

grid.Column("Name", 
  format: item => Html.ActionLink((string)item.Name, 
  "Details", "Product", new { id = item.ProductId }, null)),

Armed with this simple transition, you can now easily take advantage of WebGrid with the WebForms view engine!

Wrapping Up

In this article I showed how a few simple tweaks let you take advantage of the functionality that WebGrid brings without sacrificing strong typing, IntelliSense or efficient server-side paging. WebGrid has some great functionality to help make you productive when you need to render tabular data. I hope this article gave you a feel for how to make the most of it in an ASP.NET MVC application.

Ref: http://msdn.microsoft.com/en-us/magazine/hh288075.aspx

What is Public Cloud

A public cloud is one based on the standard cloud computing model, in which a service provider makes resources, such as applications and storage, available to the general public over the Internet. Public cloud services may be free or offered on a pay-per-usage model.
  The main benefits of using a public cloud service are:

• Easy and inexpensive set-up because hardware, application and bandwidth costs are covered by the provider.
• Scalability to meet needs.
• No wasted resources because you pay for what you use.

The term "public cloud" arose to differentiate between the standard model and the private cloud, which is a proprietary network or data center that uses cloud computing technologies, such as virtualization. A private cloud is managed by the organization it serves. A third model, the hybrid cloud, is maintained by both internal and external providers.
Examples of public clouds include Amazon Elastic Compute Cloud (EC2), IBM's Blue Cloud, Sun Cloud, Google AppEngine and Windows Azure Services Platform.

Ref: http://searchcloudcomputing.techtarget.com/definition/public-cloud

Identifying the blockers in SQL Server 2005 and 2008

Problem

In our SQL Server environment, we have frequent locking and blocking across a few different versions of SQL Server. How can I find blocking and blocked SPID’s in SQL Server 2005 and later versions?  Is there only one way to find out which spids are blocking?  Are there any commands that I can run against multiple SQL Server versions?  Check out this tip to learn more about locking and blocking.

Solution

Whenever a user contacts the DBA team indicating a processes looks hung or a process is not proceeding checking the applicable database blocking makes a great deal of sense. Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock. This forces the second connection to be blocked until the first connection completes. With this being said, locking is a natural occurrence in SQL Server in order to maintain data integrity.  For more information about locking and blocking review these tips: Understanding SQL Server Locking and Understanding SQL Server Blocking.
There are number of ways to find out the details of the system processes IDs (spids) involved in blocking. I have tried to cover some of the options in this tip to include:

• sp_who2 System Stored Procedure
• sys.dm_exec_requests DMV
• Sys.dm_os_waiting_tasks
• SQL Server Management Studio Activity Monitor
• SQL Server Management Studio Reports
• SQL Server Profiler

---

sp_who2 System Stored Procedure

The sp_who2 system stored procedure provides information about the current SQL Server processes with the associated users, application, database, CPU time, etc. The information returned can be filtered to return only the active processes by using the ‘active’ parameter.  Below is some sample code and a screen shot with showing process 55 being blocked by process 54.

USE Master
GO
EXEC sp_who2
GO

Additional resources:

• SQL Server Command Line Tools To Manage Your Server
• Locking and Blocking Scripts in SQL Server 2000 vs SQL Server 2005
• Killing a SPID and Checking Rollback progress in SQL Server

---

sys.dm_exec_requests DMV

The sys.dm_exec_requests DMV provides details on all of the processes running in SQL Server. With the WHERE condition listed below, only blocked processes will be returned.

USE Master
GO
SELECT * 
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;
GO

Additional resources:

• How to isolate the current running commands in SQL Server
• Identifying the input buffer in SQL Server 2000 vs SQL Server 2005
• Finding a SQL Server process percentage complete with dynamic management views

---

sys.dm_os_waiting_tasks DMV

The sys.dm_os_waiting_tasks DMV returns information about the tasks that are waiting on resources. To view the data, users should have SQL Server System Administrator or VIEW SERVER STATE permissions on the instance.

USE Master
GO
SELECT session_id, wait_duration_ms, wait_type, blocking_session_id 
FROM sys.dm_os_waiting_tasks 
WHERE blocking_session_id <> 0
GO

Additional resources:

• SQL Server Tempdb Usage and Bottlenecks tracked with Extended Events

---

SQL Server Management Studio Activity Monitor

If you are more comfortable using SQL Server Management Studio to review locking and blocking as opposed to querying system objects or executing stored procedures, you are in luck.  There are even a few different tools in SQL Server Management Studio you can use.  The first option is the Activity Monitor, which can be accessed by navigating to the instance name | right click | select 'Activity Monitor'.  To view the Activity Monitor in SQL Server 2005 and SQL Server 2008, users should have SQL Server System Administrator or VIEW SERVER STATE permissions on the instance.

Additional resources:

• Performance Analysis Using SQL Server 2008 Activity Monitor Tool

---

SQL Server Management Studio Reports

The second option in SQL Server Management Studio to monitor blocking is with the standard reports, which can be accessed by navigating to the instance name | right click | Reports | Standard Reports | Activity - All Blocking Transactions.  Once again, users should have SQL Server System Administrator or VIEW SERVER STATE permissions on the instance.

Additional resources:

• Built-In Performance Reports in SQL Server 2005

---

SQL Server Profiler

To capture blocking related data on a continuous basis, one option is to run SQL Server Profiler and save the data to a table or file for analysis purposes.  In order to configure Profiler to capture blocking related data, execute Profiler, configure the general properties then navigate to Event Selection tab | select Show all events | Errors and Warnings | check the Blocked process report and then run the application.  In addition, be sure to configure the 'blocked process threshold' before you start Profiler using this code:

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO 
sp_configure 'blocked process threshold', 20
GO 
RECONFIGURE 
GO 

Ref: http://www.mssqltips.com/tip.asp?tip=2429

WCF System.Net.WebException: The underlying connection was closed: The connection was closed unexpectedly

WCF System.Net.WebException: The underlying connection was closed: The connection was closed unexpectedly

If you get the above error when trying to return large numbers of items in a List from a WCF service, then ensure you have set the following behaviour for the dataContractSerializer in both your client and service configuration files..

<dataContractSerializer maxItemsInObjectGraph="2147483646"/>

As in this blog, but with larger number (i.e. max of int) http://processmentor.com/community/blogs/scott_middleton/archive/2007/06/08/169.aspx

Need the settings in service layer and client, set to this.

Service has this

<behavior name="MyServiceBehavior">

                                  <serviceDebug includeExceptionDetailInFaults="true" />

                                  <serviceMetadata httpGetEnabled="true" />

                                  <dataContractSerializer maxItemsInObjectGraph="2147483646"/>

                           behavior>

                     serviceBehaviors>

<service behaviorConfiguration="MyServiceBehavior"

                      name="MyService.ServiceImplementation.MyService">

                           <endpoint binding="basicHttpBinding" bindingConfiguration="BasicHttpBinding_Leads"

                                           bindingNamespace="http://MyService.ServiceContracts/2007/04"

                            contract="MyService.ServiceContracts.IMyService" />

                           <endpoint address="mex" binding="mexHttpBinding" contract="IMetadataExchange" />

                     service>

              services>

Client has this …

<behaviors>

<endpointBehaviors>

<behavior name="LargeList">

<dataContractSerializer maxItemsInObjectGraph="2147483646" />

                           behavior>

                     endpointBehaviors>

              behaviors>

<endpoint address="http://localhost/myservice.svc" binding="basicHttpBinding"

                                    bindingConfiguration="BasicHttpBinding_IMyService" contract="LeadsServiceInternal.IMyService" name="BasicHttpBinding_IMyService"

                             behaviorConfiguration="LargeList"

                                    />

Note you must also set the following two settings on the client maxReceivedMessageSize and maxBufferSize

<binding name="BasicHttpBinding_IMyService" closeTimeout="00:01:00" openTimeout="00:01:00" receiveTimeout="00:10:00" sendTimeout="00:10:00" allowCookies="false" bypassProxyOnLocal="false"

                                          hostNameComparisonMode="StrongWildcard" maxBufferSize="2147483646" maxBufferPoolSize="524288" maxReceivedMessageSize="2147483646" messageEncoding="Text" textEncoding="utf-8" transferMode="Buffered" useDefaultWebProxy="true">

                                  <readerQuotas maxDepth="32" maxStringContentLength="8192" maxArrayLength="16384" maxBytesPerRead="4096" maxNameTableCharCount="16384"/>

                                  <security mode="None">

                                         <transport clientCredentialType="None" proxyCredentialType="None" realm=""/>

                                         <message clientCredentialType="UserName" algorithmSuite="Default"/>

                                  security>

                           binding>

Ref: http://consultingblogs.emc.com/merrickchaffer/archive/2007/09/19/WCF-System.Net.WebException_3A00_-The-underlying-connection-was-closed_3A00_-The-connection-was-closed-unexpectedly.aspx