Monday, March 22, 2010

How to calculate Buffer Cache Hit Ratio

 

The buffer cache hit ratio value measures how well your pages are staying in the buffer cache.  The closer the buffer cache hit ratio is to 100 % the better your buffer cache is being utilized, and the better performance you will get out of your SQL Server.   Here is how you would use this DMV to calculate the buffer cache hit ratio:

 

SELECT cast((CAST(A.cntr_value1 AS NUMERIC) / CAST(B.cntr_value2 AS NUMERIC))*100  as decimal(10,2)) AS Buffer_Cache_Hit_Ratio

FROM (

SELECT cntr_value AS cntr_value1

FROM sys.dm_os_performance_counters

WHERE object_name = 'SQLServer:Buffer Manager'

AND counter_name = 'Buffer cache hit ratio'

) AS A,

(

SELECT cntr_value AS cntr_value2

FROM sys.dm_os_performance_counters

WHERE object_name = 'SQLServer:Buffer Manager'

AND counter_name = 'Buffer cache hit ratio base'

) AS B



 

 

 

Friday, March 19, 2010

Memory used by SQL Server

Memory or RAM utilization by SQL Server

Here is the Query:

SELECT 
cntr_value/1024 as 'MB used'
FROM
master.dbo.sysperfinfo
Where
object_name = 'SQLServer:Memory Manager'
and counter_name = 'Total Server Memory (KB)'

or 
DBCC MEMORYSTATUS 

Apart from this you can use Prefmon with counters name SQLServer:Memory Manager, Total Server Memory (KB)
And if server is only used for SQL Server then in task manager check the memeory out of that only some percent
is used by your OS and rest is by SQL Server.

Wednesday, March 10, 2010

Prefmon Counter to be watch for High CPU utilization

Prefmon Counter to be watch for High CPU utilization

 

Performance Counter

Counter Object

Threshold

Notes

% Processor Time

Processor

> 80%

Potential causes include memory pressure, low query plan reuse, non-optimized queries.

Context Switches/sec

System

> 5000 x processors

Potential causes include other applications on the server, more than one instance of SQL Server running on the same server, hyper-threading turned on.

Processor Queue Length

System

> 5 x processors

Potential causes include other applications on the server, high compilations or recompilations, more than one instance of SQL Server running on the same server.

Compilations/sec

SQLServer:SQL Statistics

Trend

Compare to Batch Requests/sec.

Re-Compilations/sec

SQLServer:SQL Statistics

Trend

Compare to Batch Requests/sec.

Batch Request/sec

SQLServer:SQL Statistics

Trend

Compare with the Compilation and Re-Compilations per second.

Page Life Expectancy

SQLServer:Buffer Manager

< 300

Potential for memory pressure.

Lazy Writes/sec

SQLServer:Buffer Manager

Trend

Potential for large data cache flushes or memory pressure.

Checkpoints/sec

SQLServer:Buffer Manager

Trend

Evaluate checkpoints against PLE and Lazy Writes/sec.

Cache Hit Ratio: SQL Plans

SQLServer:Plan Cache

< 70%

Indicates low plan reuse.

Buffer Cache Hit Ratio

SQLServer:Buffer Manager

< 97%

Potential for memory pressure.

 

 

Monday, March 8, 2010

How to shrink the tempdb database in SQL Server?

Method 1 to Shrink Tempdb

This method requires you to restart SQL Server.

Stop SQL Server. Start SQL Server by typing the following command:
sqlservr -c -f
Run Following Command

ALTER DATABASE tempdb MODIFY FILE
(NAME = 'tempdev', SIZE = target_size_in_MB) 
ALTER DATABASE tempdb MODIFY FILE
(NAME = 'templog', SIZE = target_size_in_MB)
                                

Stop SQL Server by pressing Ctrl-C at the command prompt window, restart SQL Server as a service

 

Method 2 to Shrink Tempdb

Use the DBCC SHRINKDATABASE command to shrink the tempdb database as a whole. DBCC SHRINKDATABASE receives the parameter target_percent, which is the desired percentage of free space left in the database file after the database is shrunk. If you use DBCC SHRINKDATABASE, you may have to restart SQL Server.

dbcc shrinkdatabase (tempdb, 'target percent') 

 

There are limitations for use of the DBCC SHRINKDATABASE command on the tempdb database. The target size for data and log files cannot be smaller than the size specified when the database was created or the last size explicitly set with a file-size changing operation such as ALTER DATABASE with the MODIFY FILE option or the DBCC SHRINKFILE command. Another limitation of DBCC SHRINKDATABASE is the calculation of the target_percentage parameter and its dependency on the current space used.

 

Method 3 to Shrink Tempdb

Use the command DBCC SHRINKFILE to shrink the individual tempdb files. DBCC SHRINKFILE provides more flexibility than DBCC SHRINKDATABASE because you can use it on a single database file without affecting other files that belong to the same database. DBCC SHRINKFILE receives the target size parameter, which is the desired final size for the database file.

use tempdb
dbcc shrinkfile (tempdev, 'target size in MB')
dbcc shrinkfile (templog, 'target size in MB')

 

An advantage of DBCC SHRINKFILE is that it can reduce the size of a file to a size smaller than its original size. You can issue DBCC SHRINKFILE on any of the data or log files. A limitation of DBCC SHRINKFILE is that you cannot make the database smaller than the size of the model database.

 

For More details Check out:

http://support.microsoft.com/kb/307487

 

 

 

Wednesday, March 3, 2010

Optimizing Where clause

Try to avoid WHERE clauses that are non-sargable. The term "sargable" (which is in effect a made-up word) comes from the pseudo-acronym "SARG", which stands for "Search ARGument," which refers to a WHERE clause that compares a column to a constant value. If a WHERE clause is sargable, this means that it can take advantage of an index (assuming one is available) to speed completion of the query. If a WHERE clause is non-sargable, this means that the WHERE clause (or at least part of it) cannot take advantage of an index, instead performing a table/index scan, which may cause the query's performance to suffer.

Non-sargable search arguments in the WHERE clause, such as "IS NULL", "<>", "!=", "!>", "!<", "NOT", "NOT EXISTS", "NOT IN", "NOT LIKE", and "LIKE '%500'" generally prevents (but not always) the query optimizer from using an index to perform a search. In addition, expressions that include a function on a column, expressions that have the same column on both sides of the operator, or comparisons against a column (not a constant), are not sargable.

But not every WHERE clause that has a non-sargable expression in it is doomed to a table/index scan. If the WHERE clause includes both sargable and non-sargable clauses, then at least the sargable clauses can use an index (if one exists) to help access the data quickly.

In many cases, if there is a covering index on the table, which includes all of the columns in the SELECT, JOIN, and WHERE clauses in a query, then the covering index can be used instead of a table/index scan to return a query's data, even if it has a non-sargable WHERE clause. But keep in mind that covering indexes have their own drawbacks, such as producing very wide indexes that increase disk I/O when they are read.

In some cases, it may be possible to rewrite a non-sargable WHERE clause into one that is sargable. For example, the clause:

WHERE SUBSTRING(firstname,1,1) = 'm'

Can be rewritten like this:

WHERE firstname like 'm%'

Both of these WHERE clauses produce the same result, but the first one is non-sargable (it uses a function) and will run slow, while the second one is sargable, and will run much faster.

WHERE clauses that perform some function on a column are non-sargable. On the other hand, if you can rewrite the WHERE clause so that the column and function are separate, then the query can use an available index, greatly boosting performance. For example:

Function Acts Directly on Column, and Index Cannot Be Used:

SELECT member_number, first_name, last_name
FROM members
WHERE DATEDIFF(yy,datofbirth,GETDATE()) > 21

Function Has Been Separated From Column, and an Index Can Be Used:

SELECT member_number, first_name, last_name
FROM members
WHERE dateofbirth < DATEADD(yy,-21,GETDATE())

Each of the above queries produces the same results, but the second query will use an index because the function is not performed directly on the column, as it is in the first example. The moral of this story is to try to rewrite WHERE clauses that have functions so that the function does not act directly on the column.

WHERE clauses that use NOT are not sargable, but can often be rewritten to remove the NOT from the WHERE clause, for example:

WHERE NOT column_name > 5

To:

WERE column_name <= 5

Each of the above clauses produces the same results, but the second one is sargable.

If you don't know if a particular WHERE clause is sargable or non-sargable, check out the query's execution plan in Query Analyzer or Management Studio. Doing this, you can very quickly see if the query will be using index lookups or table/index scans to return your results.

With some careful analysis, and some clever thought, many non-sargable queries can be written so that they are sargable. Your goal for best performance (assuming it is possible) is to get the left side of a search condition to be a single column name, and the right side an easy to look up value.

 

How to find Virtual Address Space on your SQL Server

What to do if Virtual Address Fragmentation and getting OOM (out-of-memory) errors in your SQL Server.  More RAM is definitely not a solution here!

Here are some sample error messages you might have seen in your SQL Errorlog which will indicate if this is physical memory or virtual memory issue:

SQL 2000
WARNING: Failed to reserve contiguous memory of Size= 65536.
SQL Server could not spawn process_loginread thread.


SQL 2005/2008
Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 122880

Error: 701, Severity: 17, State: 123.
There is insufficient system memory to run this query.

As the errors above indicate, the problem is in reserving a fixed size in the virtual address space of SQL Server.

Typical symptoms you would notice in these situations are:-

1) Database or Log Backups might start failing
2) You are unable to make a new connections to SQL.
3) Certain jobs which require memory from non-BPool region will fail.

Many a time, these problems go away automatically without any action taken. This indicates an intermittent problem when at a certain point in time; there was virtual memory pressure which resulted in above messages being printed to the SQL Errorlog.

Dealing with VAS fragmentation or running out of VAS on SQL server 2000 was rather painful and required setting up additional debugging techniques to get down to the bottom of the issue. You might have used some of the following tools:-

1) VMSTAT
2) Debugging Tools for Windows (to capture a manual dump of sqlservr.exe)
3) T2551 to generate a filtered dump in SQL Server when running into a OOM condition.
4) TLIST.exe to identify modules loaded in SQL Server.

Luckily, starting with SQL 2005 there is an in-memory DMV which tracks the virtual address space (VAS) of your SQL Server process. Here are some queries which will help you find out how much virtual address is available on sqlservr.exe which is FREE and how much is total available (Free+InUse)

1. Will tell you the size of the biggest contiguous block in VAS

SELECT convert(varchar,getdate(),120) as [Timestamp], max(region_size_in_bytes)/1024 [Total max contiguous block size in KB]

from sys.dm_os_virtual_address_dump where region_state = 0x00010000 --- MEM_FREE

2. Will also tell us size of largest contiguous block plus the region marked as MEM_RESERVE (this is your non-BPool area reserved during SQL Startup, sometimes referred to as MTL - MemToLeave)

SELECT SUM(CONVERT(BIGINT,Size)*Free)/1024 AS 'Total Memory Available (in KB)' ,

CAST(MAX(Size) AS BIGINT)/1024 AS 'Max free mem (in KB)' 

From

(SELECT

    Mem.Size,

    Reserved =  SUM(CASE(CONVERT(INT, Mem.Base)^0)

    WHEN 0 THEN 0 ELSE 1 END),

    Free = SUM(CASE(CONVERT(INT, Mem.Base)^0)

    WHEN 0 THEN 1 ELSE 0 END)

FROM

(

    SELECT  CONVERT(VARBINARY, SUM(region_size_in_bytes))

    AS Size, region_allocation_base_address AS Base

    FROM sys.dm_os_virtual_address_dump 

    WHERE region_allocation_base_address <> 0x0

    GROUP BY region_allocation_base_address 

 UNION  

    SELECT CONVERT(VARBINARY, region_size_in_bytes), region_allocation_base_address

    FROM sys.dm_os_virtual_address_dump

    WHERE region_allocation_base_address  = 0x0

)

AS Mem

GROUP BY Size) as AMem

WHERE Free <> 0

 

 


3. The below query will identify the memory reserved by non-BPool components in SQL Server

select SUM(virtual_memory_reserved_kb)/1024 as virtual_memory_reserved_mb from

sys.dm_os_memory_clerks

where type not like '%bufferpool%'

 

4. To identify if any of the space used is by SQL Server procedure cache itself, you can use this query


SELECT SUM(PAGESUSED)*8/1024 'MB of MemToLeave memory consumed by procedures'

FROM MASTER.DBO.SYSCACHEOBJECTS WHERE PAGESUSED >1




DBCC MEMORYSTATUS also has good information on whether the usage from the non-BPool region is coming from SQL or non-SQL components. If it is SQL 2000, check the value of “OS Committed” and it is SQL 2005/2008 look at the value of “MultiPage Allocator” for each memory clerk. Just to re-state my assumption in case you are not sure

1 SQL Server Page = 8 KB –> SinglePage Allocator
> 1 Page or > 8KB –> MultiPage Allocator


Apart from these you need to pay special attention to the following components are all of the below do not use memory from the Buffer Pool region but make direct
VirtualAlloc() calls to reserve memory and then commit them,

1) Any OLE/COM components loaded in SQL Server
2) Extended Stored Procedures (use sys.dm_os_loaded_modules to identify the module loaded in sqlserver process space).
3) SQL Mail components
4) Any XML prepared documents using sp_xml_preparedocument
5) Linked Server Providers
6) Large Plans stored in Procedure Cache
7) Very frequent Backups also may cause MTL area depletion. (Please investigate using the parameters MAXTRANSFERSIZE and BUFFERCOUNT, if this is the case).
8) SQL CLR (recommended to be used on 64-bit SQL Servers)

Above list is certainly not exhaustive, but is more enough to get started in looking at the right areas. While we are on this topic, it is important to understand the difference between a Reserve and a Commit. These are windows concepts are remain the same for SQL Server as well, after all its VirtualAlloc() underneath the covers.

MEM_COMMIT – Region in VAS this is backed by RAM/paging file
MEM_RESERVE – Region in VAS with no actual physical storage either in RAM or in the paging file.

The problems we talked about so far occur when a call to VirtualAlloc() with MEM_RESERVE is made and that “reservation” fails resulting in the errors printed to errorlog. Most times, the call to reserve is subsequently followed by a COMMIT, but its not mandatory. I can reserve now and commit later on using the base address of the reservation. SQL Server is smart enough and during starting reserves a certain region of the address space referred to as MTL or Non-BPool region. It is here the crunch is and it is here the issue needs to be investigated/fixed.

A quick workaround for above issues is to add the startup parameter –FXXX. (Sample usage- -F512)
XXX- is the amount in MB to reserve on startup.