How to find time it took to failover SQL Server cluster from SQL Server error log

First run following query to read current error log. Please change date range to fit the time when failover happened

USE Master

GO

Exec xp_ReadErrorLog 0, 1, 'The NETBIOS name of the local node that is running the server is', NULL, '2012-03-21', '2012-03-29', 'desc'

After the failover new error logs are created. So go to the prevous error log and run the same script. You might want to change start and end time according to your error log retention setting

Following are parameters details for xp_readerrorlog

1. Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...
2. Log file type: 1 or NULL = error log, 2 = SQL Agent log
3. Search string 1: String one you want to search for
4. Search string 2: String two you want to search for to further refine the results
5. Search from start time  
6. Search to end time
7. Sort order for results: N'asc' = ascending, N'desc' = descending

SQL Server - How to Grant Read Access to ALL databases to a Login?

Results of this script should be run on destination server to grant read access to a login to all databases on a server. System databases are skipped

select 'USE ['+ name +  ']' + char(10) + 'GO' + char(10) +
'CREATE USER [ssrsuser] FOR LOGIN [ssrsuser]' + char(10) +
'GO' + char(10) +
'USE [' + name + ']' + char(10) +
'GO' + char(10) +
'EXEC sp_addrolemember N''db_datareader'', N''ssrsuser''' + char(10) + 'GO'
from sys.databases
where database_id > 4
order by name

Handling ad hoc workload using optimize for ad hoc workloads

How to find out if you need to enable “optimize for ad hoc workloads”

1. Plan cache size:

SQL Server 2008 is calculated as following

% Memory of target (GB)	Total GB	SERVER1
75% visible target memory from 0-4GB	4	3
10% visible target memory from 4-64GB	56	5.6
5% visible target memory > 64GB	0	0
		8.6

2. SERVER1 stats
   
   

Run following query on the server to get the stats

-- Do not run this TSQL until SQL Server has been running for at least 3 hours

SET NOCOUNT ON

SELECT objtype AS [Cache Store Type],

        COUNT_BIG(*) AS [Total Num Of Plans],

        SUM(CAST(size_in_bytes as decimal(14,2))) / 1048576 AS [Total Size In MB],

        AVG(usecounts) AS [All Plans - Ave Use Count],

        SUM(CAST((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(14,2)))/ 1048576

        AS [Size in MB of plans with a Use count = 1],

        SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Number of of plans with a Use count = 1]

       

        FROM sys.dm_exec_cached_plans

        GROUP BY objtype

        ORDER BY [Size in MB of plans with a Use count = 1] DESC

DECLARE @AdHocSizeInMB decimal (14,2), @TotalSizeInMB decimal (14,2)

SELECT @AdHocSizeInMB = SUM(CAST((CASE WHEN usecounts = 1 AND LOWER(objtype) = 'adhoc'

THEN size_in_bytes ELSE 0 END) as decimal(14,2))) / 1048576,

        @TotalSizeInMB = SUM (CAST (size_in_bytes as decimal (14,2))) / 1048576

        FROM sys.dm_exec_cached_plans

SELECT @AdHocSizeInMB as [Current memory occupied by adhoc plans only used once (MB)],

         @TotalSizeInMB as [Total cache plan size (MB)],

         CAST((@AdHocSizeInMB / @TotalSizeInMB) * 100 as decimal(14,2))

         as [% of total cache plan occupied by adhoc plans only used once]

IF  @AdHocSizeInMB > 200 or ((@AdHocSizeInMB / @TotalSizeInMB) * 100) > 25  -- 200MB or > 25%

        SELECT 'Switch on Optimize for ad hoc workloads as it will make a significant difference' as [Recommendation]

ELSE

        SELECT 'Setting Optimize for ad hoc workloads will make little difference' as [Recommendation]

GO

On an average 24% of cache is used by ad-hoc plans which are used once

CacheType	Total Plans	Total MBs	Avg Use Count	Total MBs - USE Count 1	Total Plans - USE Count 1
Adhoc	14004	4711.320312	37	1658.414062	2902
Proc	3812	2138.3125	306590	970.523437	1184
Prepared	570	52.882812	3755	14.898437	84
Trigger	7	1.101562	57	0.085937	1
UsrTab	2	0.4375	16	0	0
View	796	74.242187	50	0	0
Check	21	0.585937	75	0	0

Current memory occupied by adhoc plans only used once (MB)	Total cache plan size (MB)	% of total cache plan occupied by adhoc plans only used once
1666.85	6994.29	23.83

So on SERVER1 8.6 GB of cache is available to SQL and about 2GB is being used by ad-hoc workload.

3. Advantage:

It is recommended that if more than 25% of procedure cache is used by ad-hoc queries then it is advisable to turn on. When this option is turned on, once the database engine has compiled a batch for the first time, instead of saving the full compiled plan (potentially of several tens of kilobytes) it instead saves just a tiny 18 byte “stub”. This saves considerable space in the procedure cache.(Ref http://sqlserverperformance.idera.com/memory/optimize-ad-hoc-workloads-option-sql-server-2008/). In our case we will save about 2 GB memory if we turn on this option

4. Disadvantages:

Even though batch is ad-hoc there is possibility that it will need to run again. In such case plan will be compiled twice. Hence we run into risk of over compilation.

5. Conclusion:

We are at the borderline of threshold (25%) so at this time it will not make much difference if we turn on this option. Since we are in process of adding more memory to the server we should revisit this at that time. At this time I would focus more on reducing the number of ad-hoc plans by reviewing SPs for parameterization.

In case you decide to enable this option; here is the script to do so

sp_configure 'show advanced options', 1

RECONFIGURE WITH OVERRIDE

GO

sp_configure 'optimize for ad hoc workloads', 1

RECONFIGURE WITH OVERRIDE

GO

sp_configure 'show advanced options', 0

RECONFIGURE WITH OVERRIDE

GO