I have written a C# and ASP.net application. The application is pretty straight forward and nothing fancy. I use LINQ to perform my data queries that return data to mostly forms that use Infragistics Webgrids. The application as a whole runs great. I can see no problems with performance issues.
As a feature of the application, I allow users to upload files from their desktop and store them on the server. More specifically, I store these files as a varbinary(max). When a user selects a row in the grid with one of these attached files, the user is presented with a button to view the file. The file types are primarily .jpg or .pdf. In addition I have a second table where I store records with rich text data. I store this type of data in a field type of nvarchar(max). The contents of this file is primarily HTML type data.
When the user clicks the button to view this data, it takes about 3 to 5 minutes to appear on the users screen. When I view the performance monitor on the server, it typically show 1 to 3 percent utilization on the processor and the disk are almost idle. I am trying to figure out what to watch on the SQL server side to see where the bottleneck is but I not sure what performance counters to watch.
One additional piece of information..... If I reboot the SQL server, the problem goes away. When the user clicks the button to view the document, it is almost instantly displayed on the screen. At this time, I have not determined exactly how long it takes for the problem to arise.
Any help in diagnosing this problem is greatly appreciated.