Quite a good video describing how to setup SQL Server 2012 Availability Group using the new “Always On” technology:
Manually Remove Log Shipping
On the primary server, execute sp_delete_log_shipping_primary_secondary to delete the information about the secondary database from the primary server:
EXEC master.dbo.sp_delete_log_shipping_primary_secondary
@primary_database = N’AdventureWorks’
,@secondary_server = N’LogShippingServer’
,@secondary_database = N’LogShipAdventureWorks’
GO
Then execute sp_delete_log_shipping_primary_database to delete information about the log shipping configuration from the primary server. This also deletes the backup job:
sp_delete_log_shipping_primary_database N’AdventureWorks’
On the secondary server, execute sp_delete_log_shipping_secondary_database to delete the secondary database.
sp_delete_log_shipping_secondary_database N’LogShipAdventureWorks’
Script to check for Full Table Scans through Search Cached Plans DMV
CREATE PROC [dbo].[dba_SearchCachedPlans]
@StringToSearchFor VARCHAR(255)
AS
/*———————————————————————-
Example Usage:
1. exec dbo.dba_SearchCachedPlans ‘%
2. exec dbo.dba_SearchCachedPlans ‘%
3. exec dbo.dba_SearchCachedPlans ‘%
———————————————————————–*/
BEGIN
– Do not lock anything, and do not get held up by any locks.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
st.text AS [SQL]
, cp.cacheobjtype
, cp.objtype
, DB_NAME(st.dbid)AS [DatabaseName]
, cp.usecounts AS [Plan usage]
, qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE CAST(qp.query_plan AS NVARCHAR(MAX))LIKE @StringToSearchFor
ORDER BY cp.usecounts DESC
END
exec dbo.dba_SearchCachedPlans ‘%
SQL Server 2005 Recommended Performance Counters and Values
Memory
Available Mbytes
> 100MB
Paging File
%Usage
< 70%
Process (sqlservr)
%Privileged Time
< 30% of %Processor Time (sqlservr)
Processor
%Privileged Time
< 30% of Total %Processor Time
PhysicalDisk
Avg. Disk Sec/Read
< 8ms
PhysicalDisk
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
Fix Performance Dashboard for SQL 2008
Microsoft Article for fixing up Performance Dashboard (2005) so that it works properly for SQL 2008:
TSQL Generate Missing Index Report from DMV
The SQL 2005 Performance dashboard had an awesome “missing index” report which was no longer (immediately) available in SQL 2008.
To retrieve the missing indexes in the same useful “create index statement” format from the report, you can use the following T-SQL;
SELECT
migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
‘CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
+ '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']‘
+ ‘ ON ‘ + mid.statement
+ ‘ (‘ + ISNULL (mid.equality_columns,”)
+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ‘,’ ELSE ” END
+ ISNULL (mid.inequality_columns, ”)
+ ‘)’
+ ISNULL (‘ INCLUDE (‘ + mid.included_columns + ‘)’, ”) AS create_index_statement,
migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
… but keep in mind the following limitations when using this;
-
It’s not as smart as the Database Engine Tuning Advisor. If you have identified a query that you know is expensive and needs some help, don’t pass up DTA just because the missing index DMVs didn’t have any suggestions. DTA might still be able to help.
-
The missing index DMVs don’t take into account the overhead that new indexes can create (extra disk space, slight impact on insert/delete perf, etc). DTA does take this into account, however.
-
The “improvement_measure” column in this query’s output is a rough indicator of the (estimated) improvement that might be seen if the index was created. This is a unitless number, and has meaning only relative the same number for other indexes. (It’s a combination of the avg_total_user_cost, avg_user_impact, user_seeks, and user_scans columns in sys.dm_db_missing_index_group_stats.)
-
The missing index DMVs don’t make recommendation about whether a proposed index should be clustered or nonclustered. This has workload-wide ramifications, while these DMVs focus only on the indexes that would benefit individual queries. (DTA can do this, however.)
-
Won’t recommend partitioning.
-
It’s possible that the DMVs may not recommend the ideal column order for multi-column indexes.
-
The DMV tracks information on no more than 500 missing indexes.
How to Check for SQL Logins, with NO Database Mapping
USE MASTER;
CREATE TABLE #dbusers (
sid VARBINARY(85))
EXEC sp_MSforeachdb
'insert #dbusers select sid from [?].sys.database_principals where type != ''R'''
SELECT name
FROM sys.server_principals
WHERE sid IN (SELECT sid
FROM sys.server_principals
WHERE TYPE != 'R'
AND name NOT LIKE ('##%##')
EXCEPT
SELECT DISTINCT sid
FROM #dbusers)
GO
DROP TABLE #dbusers
How to Grant Read-only Access to SQL Activity Monitor
use [master]
GO
GRANT VIEW SERVER STATE TO [Domain\Username]
GO
List the database name and file locations
List the database name, logical names and locations of the DB log and database files;
EXECUTE sp_msforeachdb ‘use ?; select DB_NAME(); select name, filename from [?].sys.sysfiles’
Lock Pages in Memory Option
With specific reference to SQL 2008 R2, however the use of the lock pages in memory rule generally states;
“The Windows policy Lock Pages in Memory option is disabled by default. This privilege must be enabled to configure Address Windowing Extensions (AWE). This policy determines which accounts can use a process to keep data in physical memory, preventing the system from paging the data to virtual memory on disk. On 32-bit operating systems, setting this privilege when not using AWE can significantly impair system performance. Locking pages in memory is not required on 64-bit operating systems.”
See the official Microsoft Article at; http://msdn.microsoft.com/en-us/library/ms190730.aspx