Starting SQL Server

From Command prompt:
Start an instance of SQL Server or the SQL Server Agent service from a command prompt by typing:
net start mssqlserver or sqlservr, or net start SQLServerAgent or by running SQLSERVR.EXE. If you are referring to a named instance of SQL Server, you must specify mssql$instancename or SQLAgent$instancename.

SQL Server TRANSACTION ISOLATION LEVEL

TRANSACTION ISOLATION LEVEL
READ UNCOMMITTED
- can read rows that have been modified by other transactions but not yet committed
- do not issue shared locks to prevent other transactions from modifying data read by the current transaction.
READ COMMITTED
- cannot read data that has been modified but not committed by other transactions. This prevents dirty reads.
- phantom data [...]

Performance Counters

SQL Server 2005 Database Engine Performance Counters

SQLServer:Access Methods
SQLServer:Buffer Manager
SQLServer:Buffer Node
SQLServer:Buffer Partition

AU cleanup batches/sec                          
AWE lookup maps/sec                       
Database pages                                         
Free list empty/sec                               

AU cleanups/sec                                      
AWE stolen maps/sec                        
Foreign pages                                             
Free list requests/sec                          

By-reference [...]

sp_send_dbmail - database mail

EXEC msdb.dbo.sp_send_dbmail
@recipients = ‘me@myemail.com’,
@query = ’sp_configure ”max server memory”
GO
sp_configure ”min server memory”
GO
select left(counter_name,50),cntr_value from sys.dm_os_performance_counters
where counter_name in (”Target Server Memory (KB)”,”Total Server Memory (KB)”)
and object_name = ”SQLServer:Memory Manager”’,
@subject = ‘DEMO - MEMORY UPGRADE’,
@attach_query_result_as_file = 1 ;

FIND LAST CLEAN DBCC CHECKDB RAN DATE

–====================================================
–==    FIND LAST CLEAN DBCC CHECKDB RAN DATE        ==
–====================================================
CREATE TABLE #temp (
Id INT IDENTITY(1,1),
ParentObject VARCHAR(255),
[Object] VARCHAR(255),
Field VARCHAR(255),
[Value] VARCHAR(255)
)
INSERT INTO #temp
EXECUTE SP_MSFOREACHDB’DBCC DBINFO ( ”?”) WITH TABLERESULTS’;
;WITH CHECKDB1 AS
(
SELECT [Value],ROW_NUMBER() OVER (ORDER BY ID) AS rn1 FROM #temp WHERE Field IN (’dbi_dbname’))
,CHECKDB2 AS ( SELECT [Value], ROW_NUMBER() OVER (ORDER BY ID) AS rn2 FROM #temp [...]

New in SQL Server 2008 - optimize for ad hoc workloads - Option

The optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches. When this option is set to 1, the Database Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first [...]

How to clear cache for specific database?

select * from master..sysdatabases
DBCC FLUSHPROCINDB (@intDBID)
DBCC FLUSHPROCINDB (10)

How to find missing indexes in SQL Server 2005 and 2008

SELECT
[Impact] = (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans),
[Table] = [statement],
[CreateIndexStatement] = ‘CREATE NONCLUSTERED INDEX ix_’
+ sys.objects.name COLLATE DATABASE_DEFAULT
+ ‘_’
+ REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns,”)+ISNULL(mid.inequality_columns,”), ‘[', ''), ']‘,”), ‘, ‘,’_’)
+ ‘ ON ‘
+ [statement]
+ ‘ ( ‘ + IsNull(mid.equality_columns, ”)
+ CASE WHEN mid.inequality_columns IS NULL THEN ” ELSE
CASE WHEN mid.equality_columns IS NULL THEN ” ELSE ‘,’ END
+ mid.inequality_columns END [...]

CLEAN UP ONLY ADHOC SQL CACHE

————————————
–    CLEAN UP ONLY ADHOC SQL CACHE
———————————–
declare @size_in_MBs int
select @size_in_MBs = (select size_in_MBs from
(select
objtype,
count(*)as number_of_plans,
sum(cast(size_in_bytes as bigint))/1024/1024 as size_in_MBs,
avg(usecounts)as avg_use_count
from sys.dm_exec_cached_plans
group by objtype) as AXA1
where objtype = ‘Adhoc’)
select @size_in_MBs
if @size_in_MBs > 200
dbcc freesystemcache (’SQL Plans’)

Cache and Performance - AdHoc Queries and Proc Cache

—————————————
–    STORED PROCEDURE CACHE ALLOCATION
—————————————
SELECT CAST(name AS VARCHAR(28)) AS ‘Object’,
single_pages_kb AS ‘Cache in Kb’,
entries_count AS ‘Num of entries’
FROM sys.dm_os_memory_cache_counters
WHERE type in (’CACHESTORE_SQLCP’, ‘CACHESTORE_OBJCP’, ‘CACHESTORE_PHDR’, ‘CACHESTORE_XPROC’)
—————————————
–    DATABASE BUFFER CACHE ALLOCATION
—————————————
SELECT count(*) AS cached_pages_count
,CASE database_id
WHEN 32767 THEN ‘ResourceDb’
ELSE db_name(database_id)
END AS Database_name
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY cached_pages_count DESC;
—————————————
–    CACHED PAGES FOR CURRENT DATABASE
—————————————
SELECT count(*)AS cached_pages_count
,name ,index_id
FROM sys.dm_os_buffer_descriptors [...]