SQL Monitor Custom Metric: Plan Cache; Cache Pages Total

This metric collects the number of 8-kilobyte pages that make up the plan cache to help identify memory pressure or plan cache pollution. A sudden drop in values for this metric may indicate that the instance is under memory pressure and SQL Server had to reclaim part of the plan cache for other use.

Checking the Plan Cache Warnings for a SQL Server Database

How often do you check your query plans to see if they contain any warnings? If you're missing them, it means that you're not getting all those hints about missing indexes, join predicates or statistics. Is the query optimiser trying to tell you about implicit conversions? Dennes shows how to view the warnings in plan cache for a particular database using SQL.

SQL Monitor Performance Metric: SQL Server: Plan Cache: Cache Pages Total

This metric collects the total amount of memory, in kilobytes, used by the plan cache of an instance to help identify memory pressure or plan cache pollution. It is similar to the SQL Server: memory manager: SQL cache memory counter, but instead of providing the number of 8-kilobyte pages that make up the plan cache, it provides the total memory used.

Find Single use plans in Plan Cache

This script will return all the Adhoc Single use Plans currently in your Plan Cache.

It also excludes Plan Shells created from auto/forced parameterization. Works for 2005/2008

sp_prepexec and nHibernate

SQL Server architect David Poole sets about exploring sp_prepexec when called from nHibernate.

RE: Problem deleting old backup

Is your SQL backup going to the local machine or to a network server?  We do our SQL Backups to a network server.  Every once in a while I get this same thing occuring--it errors out and it won't delete the file.  The server doesn't have the fastest disk subsystem and it typically happens when the network traffic […]

RE: DTS newbie help...

Well, unfortunately, no, because I'm doing a bulk insert and need to return all newly created idents. However, I think I've over-come the issue by reselecting back from the table I've inserted into. Now, I have an ActiveX question: I've been looking at the stuff on sqldts.com, and I want to use ActiveX to control […]

RE: Age Limitations?

$125 US a pop. There is a number on the certification site to sign up and ask questions.   http://www.microsoft.com/learning/mcp/default.asp   Look under  How do I register for an MCP exam? for number   http://www.microsoft.com/learning/mcpexams/faq/procedures.asp

RE: Joins vs More Columns

Thanks Frank.And, no, my boss is not a DB expert (but he unfortunately has dealt with DBs and thus has some knowledge which makes him dangerous).As for performance, he would rather trade headache for better performance, so if he sees anywhere that multiple joins would be slower, than he will insist on removing as many […]

RE: fn_get_sql - Not returning the full SQL statement

Just to verify (the braindead because I've worked too many hours check)... Your max characters per column (Tools | Options | Results tab) is set > 267, right? K. Brian Kelley http://www.truthsolutions.com/ Author: Start to Finish Guide to SQL Server Performance Monitoring http://www.netimpress.com/