Search This Blog

Monday, April 27, 2009

Manually Add Full Text Search Resources For A SQL 2005 Failover Cluster

Reciently, I Faced this issue where the customer did not install the FT search initially and later on they came for help since it was a I thought to share it here .Others are most welcome to Edit or Add ..

-> First you need to check if FT search service is installed (in case its there and someone deleted the resource from cluadmin)...if its not there in services or configuration manager , install it through the setup .

->In the Cluster Administrator, right-click on 'Resources' and choose New -> Resource

->In the New Resource dialog, choose 'Generic Service' for the type and give it a
name like SQL FULLTEXT. Any name is fine. Choose the desired group for the resource.

->In the next screen choose the nodes that the Full-text resource can run on.These should be the same as your SQL Server resource.

->In the next screen don't do anything. Full-text search doesn't need any dependencies.

->In the Generic Service Parameters screen type msftesql$ in the
'Service Name' box, where is the Virtual Server name for the SQL
Server. You can verify this name by opening Services.msc (Start -> Run -> type
'Services.msc' -> OK) and right-clicking on the SQL ServerFulltext Search service
and choosing properities. Us the Service Name in the properties dialog as the
Service Name in the New Resource dialog.

->In the 'Startup Parameters' box, enter the parameters shown in the service
property dialog (Start -> Run -> type 'Services.msc' -> OK -> right-click on SQL
ServerFulltext Search service -> choose 'Properties) at the end of the path in the
'Path to executable' box. For example: -s:MSSQL.2 -f:INST2

-> In the next screen don't do anything. Leave the 'Registry Replication'
information blank.

-> Click finish. The Full-text resource should be visible in your SQL Server group
in the Cluster Administrator.

-> Test by bringing the Full-text resource online and failing it over to the other


Friday, April 10, 2009

Perfmon Counter recommended values for SQL Server 2005

Below are the perfmon counter benchmarks that I generally refer to :

Available Mbytes
> 100MB

Paging File
< 70%

Process (sqlservr)
%Privileged Time
< 30% of %Processor Time (sqlservr)

%Privileged Time
< 30% of Total %Processor Time

Avg. Disk Sec/Read
< 8ms

Avg. Disk sec/Write
< 8ms (non cached) < 1ms (cached)

SQLServer:Access Methods
Forwarded Records/sec
< 10 per 100 Batch Requests/Sec

SQLServer:Access Methods
FreeSpace Scans/sec
<10 per 100 Batch Requests/Sec

SQLServer:Access Methods
Full Scans / sec
(Index Searches/sec)/(Full Scans/sec) > 1000

SQLServer:Access Methods
Workfiles Created/Sec
< 20 per 100 Batch Requests/Sec

SQLServer:Access Methods
Worktables Created/Sec
< 20 per 100 Batch Requests/Sec

SQL Server:Buffer Manager
Buffer Cache hit ratio
> 90%

SQL Server:Buffer Manager
Free list stalls/sec
< 2

SQL Server:Buffer Manager
Lazy Writes/Sec
< 20

SQL Server:Buffer Manager
Page Life Expectancy
> 300

SQLServer:Buffer Manager
Page lookups/sec
(Page lookups/sec) / (Batch Requests/sec) < 100

SQL Server:Locks
Lock Requests/sec
(Lock Request/sec)/(Batch Requests/sec) < 500

SQLServer:SQL Statistics
SQL Compilations/sec
< 10% of the number of Batch Requests/Sec

SQLServer:SQL Statistics
SQL Re-Compilations/sec
< 10% of the number of SQL Compilations

How to find how Cache/Bpool is being used and what objects are using the cache/Bpool ..

1: To determine what plans are in the cache and how often they're used we can use
sys.dm_os_memory_cache_counters dm view .

LEFT([name], 20) as [name],
LEFT([type], 20) as [type],
[single_pages_kb] + [multi_pages_kb] AS cache_kb,
FROM sys.dm_os_memory_cache_counters
order by single_pages_kb + multi_pages_kb DESC

Here :

CACHESTORE_OBJCP are compiled plans for stored procedures, functions and triggers.

CACHESTORE_SQLCP are cached SQL statements or batches that aren't in stored procedures, functions and triggers.This includes any dynamic SQL or raw SELECT statements sent to the server.

CACHESTORE_PHDR These are algebrizer trees for views, constraints and defaults. An algebrizer tree is the parsed SQL text that resolves the table and column names.

You will find these counters in DBCC Memorystatus as well.Infact DBCC Memory Status uses this dmv.This will give us idea about which cache is getting filled up .

Generally, you will find that CACHESTORE_SQLCP > CACHESTORE_OBJCP(because we cannot frame everything in to SPs) , but if the ratio of one to another is very high then we can say that there are more adhoc plans being run then Stored procedures.

You can also monitor the number of data pages in the plan cache using Performance Monitor (PerfMon) using SQLServer:Plan Cache object with the Cache Pages counter.There are instances for SQL Plans (CACHESTORE_SQLCP), Object Plans (CACHESTORE_OBJCP) and Bound Trees (CACHESTORE_PHDR). This will give you the same picture ..for e.g. under bound tree : multiply cache pages by 8. you will get the same output as in dbcc memorystatus and the dm we used above.

2 : After this to know the querry are being cached and used , we can use
sys.dm_exec_cached_plans and sys.dm_exec_sql_text dm views.

select TOP 50
p.size_in_bytes/1024 'IN KB',
LEFT([sql].[text], 100) as [text]
from sys.dm_exec_cached_plans p
outer apply sys.dm_exec_sql_text (p.plan_handle) sql
ORDER BY usecounts DESC

and then we can check the query plans(and size) for the one we have some doubt .

3: To find what tables and indexes are in the buffer memory of your server you can use sys.dm_os_buffer_descriptors DMV.The query below can give us total currrent size of buffer pool .

select count(*) AS Buffered_Page_Count
,count(*) * 8192 / (1024 * 1024) as Buffer_Pool_MB
from sys.dm_os_buffer_descriptors

After we have found the Bufferpool size , we can see which database is using more memory by runnig the query below .

SELECT LEFT(CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END, 20) AS Database_Name,
count(*)AS Buffered_Page_Count,
count(*) * 8192 / (1024 * 1024) as Buffer_Pool_MB
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY Buffered_Page_Count DESC

And then we can go further at object level to see what all objects are consuming memory (and how much) .We can use the query below in each database we wish to :

count(*)AS Buffered_Page_Count ,
count(*) * 8192 / (1024 * 1024) as Buffer_MB
-- , ,obj.index_id, i.[name]
FROM sys.dm_os_buffer_descriptors AS bd
SELECT object_name(object_id) AS name
,index_id ,allocation_unit_id, object_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
SELECT object_name(object_id) AS name
,index_id, allocation_unit_id, object_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
LEFT JOIN sys.indexes i on i.object_id = obj.object_id AND i.index_id = obj.index_id
WHERE database_id = db_id()
GROUP BY, obj.index_id , i.[name],i.[type_desc]
ORDER BY Buffered_Page_Count DESC