.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Very Low Procedure Cache Hit Ratio and High CPU Utilization

Posted By:      Posted Date: September 30, 2010    Points: 0   Category :Sql Server

I have a SQL Server 2005 32 bit instance that occassionally has issues with the Procedure Cache.  This instances 200+ databases and is configured with 4 processors and 12 GB of memory.

The Procedure Cache Hit Ratio drops to 25% for an extended period of time.  The size of the procedure cache during this time is very small.  I am able to tell this by quering the DMVs.  Also, DBCC Memory status shows a small procedure cache area.

Using tools such as vmstat.exe or sqbmemory (from Red-Gate) I see that the Commited memory value drops from around 1900 MB to 600 MB and the Reserved memory value increases from 50 MB to 1350 MB.  The memory is being stolen from the Committed bucket and is being reserved by some other SQL process such as XML or CLR.

After we restart the instance, the memory pressure issues go away.

Do any of you know if there is a DMV or a tool that I

View Complete Post

More Related Resource Links

TypePerf, Buffer Cache Hit Ratio, Page Life expectancy and some other counters....

Hello, I 've scheduled a Task on a Windows 2008 R2 Server (a Blade with 2 Processors (6 core HT) and 18 GB RAM) to check some SQL Server 2008 Performance Counters. The task runs TypePerf that checks the Counters every 15 seconds for 10 minutes. The task is scheduled every hour. The strange result is that Buffer Cache Hit Ratio, Page Life expectancy and some other Counters are ALWAYS = 0 but if I run PerfMon, Buffer Cache Hit Ratio is 100 and  Page Life expectancy >>>> 300. Any Idea? Thank youLuca Bovo MCTS - MCITP SQL Server 2005 (Database Administrator and Database Developer)

sqlservr.exe high memory utilization


We have a new SQL Server 2008 R2 implementation running on a Windows 2008 R2 64-bit virtual server w/ 12GB RAM.

We've moved only 1 database to this system so far and although it is a highly accessed DB, it ran quite nicely on a Windows 2003 32bit system w/ 3 GB RAM and SQL 2000.

After a fresh boot, the memory utilization is practically nil, however it slowly grows over a week until it is nearly maxed out.  The process eating up the memory is sqlservr.exe.

So, why would the memory be so high and why would it grow slowly over time.  (I've been reading a bit on the Address Windowing Extension (AWE) though I'm not sure this is or isn't what we're experiencing.)

Thanks for your help!

Kind Regards,


How to increase Log Cache Hit Ratio for tempdb ?


How to increase Log Cache Hit Ratio for tempdb ?

Log Cache Hit Ratio=58%

procedure cache having two query plans


I noticed that a stored procedure takes excessively long when it's called from within an application/management studio. I understand what is actually happening, but I do not have a reason for why this is happening.

The difference between the query plan are as follows




The difference here is that ARITH ABORT is on within one plan and is not on with the other one. ARITH ABORT on tends to be faster. Any ideas on how we are encountering this problem and a long term fix. One fix is to alter the procedure and set ARITH ABORT on, but i am thinking there may be a reason for this.

SQL Server CPU utilization high when idle



I'm running SQL Server 2008 on an 8 CPU machine with 32 gigs of RAM.  When the SQL Server instance is idle, no client requests, the sqlservr.exe process consumes about 10-18% of the CPU on the machine.  I can't seem to figure out why it is consuming this much.  

I found another post here suggesting a user run a script which gets the processor utilization history.  I ran this command and it returns SQLProcessUtilization as 12-13% for the last 30 minutes of usage.  

I ran the profiler and captured the SQL and there was very little being run, all I saw was a query for the DBVersion every 10 seconds or so, and the Duration was 1 with the SQLBatch::Completed in 20 ms.

I'm not as familiar with SQL Server as I am with Oracle, so any help would be appreciated. 



High CPU Utilization of w3wp.exe and Event ID 5000 caused by Search


Hello, I am trying to trouble shoot an issue related to my SharePoint server having the CPU pinned down by w3wp.exe.  The only exception I was able to identify has been related to Search.

Event ID 5000 Office SharePoint Server


EventType ulsexception12, P1 w3wp.exe, P2 6.0.3790.1830, P3 42435e74, P4 microsoft.office.server.search, P5 12.0.6245.0, P6 4bbd6526, P7 3012, P8 191, P9 deletedconcurrencyexception, P10 837s.

I am having no luck with any of the codes as part of this error message.  Could any one point me in the right direction related to Event ID 5000 ulsexception12.  To my knowledge there have been no changes made to Search or SSP.

Any help would be greatly appreciated.  thank you

Mark A. Eckert SharePoint Consultant Pharmica Consulting, Inc.

ASP.NET Best Practices for High Performance Applications

This article lists the techniques that you can use to maximize the performance of your ASP.NET applications. It provides common issues, design guidelines, and coding tips to build optimal and robust solutions.

High-Performance .NET Application Development & Architecture

It has always been a goal of project architects to plan an effective strategy from the ground up in regards to an new application. All relevant factors are taken into consideration with respect to the application, from its design and layout to a functional website infrastructure. Pre-.NET strategies and design guidelines still effective now were developed with Microsoft's DNA (Distributed interNet Application) platform. This model successfully served the purpose of architecting N(any number of)-Tier (levels) applications. In its basic sense, as in most robust, distributed applications, you'll architect 3 main layers or Tiers: presentation, business rules and data access.

High Performance ASP.NET - Speeding Up ASP.NET Pages

have been writing a series of blog posts, which I have named High Performance ASP.NET Websites Made Easy! There is no rhyme or reason to the order of these posts and certainly can be read in any order:

Developing High Performance and Scalable ASP.NET Websites
Avoid Chatty Interfaces Between the Tiers in Your ASP.NET Web Application
ASP.NET Page Profiling - Page Tracing - High Performance and Scalable ASP.NET Websites Made Easy
Web Applications: N-Tier vs. N-Layer - Benefits and Trade-Offs
Query Analyzer - Sql Server Database Indexes and Execution Plans

10 Tips for Writing High-Performance Web Applications

Writing a Web application with ASP.NET is unbelievably easy. So easy, many developers don't take the time to structure their applications for great performance. In this article, I'm going to present 10 tips for writing high-performance Web apps. I'm not limiting my comments to ASP.NET applications because they are just one subset of Web applications. This article won't be the definitive guide for performance-tuning Web applications-an entire book could easily be devoted to that. Instead, think of this as a good place to start.

Send Email from SQL Server Express Using a CLR Stored Procedure

One of the nice things about SQL Server is the ability to send email using T-SQL. The downside is that this functionality does not exist in SQL Server Express. In this tip I will show you how to build a basic CLR stored procedure to send email messages from SQL Server Express, although this same technique could be used for any version of SQL Server.

If you have not yet built a CLR stored procedure, please refer to this tip for what needs to be done for the initial setup.

Inserting rows via stored procedure and under certain conditions


I'm using Dynamic Data with Entity Framework in VS2010.

Let's say my table has these fields:

PersonID (FK)
LocationID (FK)

Hypothetical scenario (it's easier for me to explain this way, so just bear with me for now)... But let's say each row in the table represents "how many items were sold by such-and-such employee at such-and-such location," where location and person are foreign keys which are referencing other tables.  Basically, there should be no more than a ONE row which has a particular combination of Person and Location.  Makes sense?

So, when inserting new rows using my Dynamic Data app, the insert form displays editable fields for Person (dropdown), Location (dropdown), and Items Sold (textbox).  How do I prevent users from inserting another row into the table containing an already-existing combination of Person and Location?   How do I displaying useful feedback to them in the event that they DO attempt to do this?

I have several thoughts about this, but since I'm new to Dynamic Data, I'm not sure which way to go.  For example:

Option 1:  Use "cascading dropdowns" approach in the insert form and only pull in the "allowed" combinations of the two dropd

Creating A Stored Procedure Which Searches Team Names



I'm have on my web page a text search box which I want users to type in there favourite football team and this will display a gridview of the teams with the replica shirts I offer.

This is where I thought about creating a stored procedure to carry out this task.

I looked online for ideas but I not found anything as yet.

If anyone done anything similar to my request please let me know.

Sort by gridview SortExpression parameter via Stored Procedure


I have a gridview that calls data via a stored procedure.  I am unable to enable the gridview columns to be sortable. I need to set the parameter in the Stored Procedure, can someone help me with this?

Here is my gridview:

<asp:GridView ID="AllUsersGrid" runat="server" AutoGenerateColumns="False" DataKeyNames="UserName"
                        GridLines="Vertical" Width="900px" DataSourceID="SqlDataSource1" AllowSorting="True"
                        SelectedRowStyle-Height="30px" CellPadding="4" BackColor="White">
                            <asp:TemplateField HeaderText="Full Name" SortExpression="lastname">
                                    <asp:Label ID="DisplayName" runat="server" Text='<%# Eval("firstname").ToString() & " " & Eval("lastname").ToString() %>' />
                            <asp:BoundField HeaderText="User Name" DataField="UserName" />

Stored procedure generator?


Hi, I am looking for a stored procedure generator with full source code(C#) compatible with Visual studio 2010. I want to create my custom stored procedure code. Please send some link. Regards, ap.

Create stored procedure from asp.net



we are creating a custom report tool, which could be used for generate the report as per end user's needs. In that we are providing an option as user could create a query and procedure as well.

In sql server we can use "EXEC" function for execute dynamic query.

Could anyone help me for create the dynamic query in Oracle?

I just tried with "execute immediate", which would throws error as 

"insufficient privileges".

Please help me.



AppFabric Cache: Real-World Usage and Integration


Windows Server AppFabric provides a distributed cache for both web and desktop applications. Well show you how to integrate AppFabric caching into your apps, along with some hints for taking advantage of new cache features in the .NET Framework 4.

Andrea Colaci

MSDN Magazine June 2010

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend