In order to figure out if you need more memory for a SQL Server you can start by taking a look at Buffer cache hit ratio and Page life expectancy.

Buffer cache hit ratio

Here is what Books On Line has to say about Buffer cache hit ratio

Buffer cache hit ratio

Percentage of pages found in the buffer cache without having to read from disk. The ratio is the total number of cache hits divided by the total number of cache lookups over the last few thousand page accesses. After a long period of time, the ratio moves very little. Because reading from the cache is m...

Yesterday we used the sys.dm_os_performance_counters dynamic management view in the post Find Out What Percent Of The Log Is Being Used For Each Database In SQL Server 2005 and 2008 to find out the log space used, today we will use this dynamic management view to find out if we are using any deprecated features.

So you have upgraded your old server to SQL Server 2008 and you wonder if you have any deprecated features in your code. Well, there is a que...

Sometimes you want to quickly see the percentage of log spaced that each database is using on your server. In SQL Server 2005 and 2008 you can use the sys.dm_os_performance_counters dynamic management view to find out this information. The query below will list all database and the percentage of log spaced used. The cntr_value column will have the percent of the log file that is being used and instance_name will be the database name.

This was asked on twitter the other day and I emailed the person the solution to this. The solution uses dynamic management views and it is not perfect because of a couple of reasons.

1) The dynamic management views don't keep this information forever, restart the server and your data is gone

2) If your table name is in a comment it will be picked up by this query

3) If the table name is part of another object it will also be picked up, for example if you have a table name customer and a view name customers then it will return a row if customers was part of the query but y...