Memory Monitoring with Profiler

Set up a custom trace to monitor:


Execution Warnings

What it monitors: Before a query can be run it must have memory, If the server has no memory to give for the query then the Execution Warning event will be fired.

What you want to see: No execution warnings happening at all.


Sort Warnings

What it monitors: When a query asks for a ORDER BY query. SQL Will try to run this in memory. If the query results are large the data might be written to TempDB instead of memory, This courses I/O which will slow down the query.


Server Memory change

What it Monitors: When the SQL Server increases or decreases by either 1mb or 5% of the maximum memory (Whichever is larger).

What you want to see: After the server has been running for a period you would hope that the memory does not change much. If it does it indicates that SQL Server is fighting with either another instance or the OS.

Memory Monitoring with Perfmon

Memory Object: Pages/Sec

What it monitors: Pages per second taken from RAM to Disk, higher the number the more I/O and possible performance issues for SQL .

What you want to see: Avg of 0-20, spikes should be things like backups, restores


Memory Object: Available Bytes

What it monitors: How much memory Is available
What you want to see: Anything greater than 5mb. With default configuration of SQL Memory settings. SQL “should” leave 4-10 mb of memory for the OS. If we see less than 5 then something in SQL is coursing problems


SQL Server: Memory Manager: Total Server Memory
SQL Server: Memory Manager: Target Server Memory

What it monitors: Total Server Memory covers how much memory is currently being used by SQL Server buffer pool. Target Server Memory is how much it would like to use.

What you want to see: If Target Server Memory is higher than Total then we need to upgrade memory


SQL Server Buffer Mgr: Lazy Writes/Sec

What it Monitors: Tracks how many times a second the LazyWriter process moves dirty pages from the buffer to disk in order to free up buffer space.

What you want to see: Should be close to zero. If it is exactly zero, this indicates that SQL Server's buffer cache is sufficiently large and SQL Server does not have to free up dirty pages, and will instead check the status at regular intervals. If the counter value is high more memory is required


Buffer Manager\Page Life Expectancy

What it Monitors: Amount of time in seconds that SQL expects a page to stay in the bufferpool.

What you want to see: MS recommends 300 PLE but large systems can be a lot higher

Moving System Databases

~~ Backup both System Databases and user Databases before starting ~~

Move TempDB

1: Find location of TempDB, Naming of files (This is more for peace of mind than a nessacry step)

use TempDB
go
sp_helpfile
go

2: Run the below code: Changes the location  SQL Server will look for the .mdf and ldf file locations

use master
go
Alter database tempdb modify file (name = tempdev, filename = 'E:\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'E:\templog.ldf')
Go

Restart SQL Service (Below is the command to stop the Default Instance of SQL Server service. You can also do it via the SQL Server Configuration Manager or Services)

NET STOP MSSQLSERVER

Start the SQL Service up again and check the location of the files using sp_helpfile again to confirm the move worked.

70-450 Design for CPU, memory, and storage capacity requirements

Design for CPU, memory, and storage capacity requirements:
This objective may include but is not limited to

RAID:
http://en.wikipedia.org/wiki/RAID

Calculating table size:
http://msdn.microsoft.com/en-us/library/ms189124(v=SQL.100).aspx

IO throughput & Transaction per second:
Linchi Shea SQL Server Backup IO Performance
http://sqlblog.com/blogs/linchi_shea/archive/2007/03/30/sql-server-backup-i-o-performance.aspx
Brent Ozar - SAN Best practices
http://www.brentozar.com/sql/sql-server-san-best-practices/
Jose Barreto's Blog - IO Performance
http://blogs.technet.com/b/josebda/archive/2009/03/31/sql-server-2008-i-o-performance.aspx
Rod Colledge - High Performance Storage systems for SQL
http://www.simple-talk.com/sql/performance/high-performance-storage-systems-for-sql-server/
Brent Ozar - SAN Performance Tuning with SQLIO
http://sqlserverpedia.com/wiki/SAN_Performance_Tuning_with_SQLIO

Data compression:
http://www.sqlservercentral.com/articles/SQL+Server+2008/65292/
http://sqlblog.com/blogs/linchi_shea/archive/2008/06/20/sql-server-2008-data-compression-impact-of-data-distribution.aspx
http://www.sql-server-performance.com/articles/dba/Data_Compression_in_SQL_Server_2008_p1.aspx
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2007/11/12/types-of-data-compression-in-sql-server-2008.aspx

Non-uniform memory access (NUMA):
http://msdn.microsoft.com/en-us/library/ms180954.aspx
http://msdn.microsoft.com/en-us/library/ms178144.aspx
http://msdn.microsoft.com/en-us/library/ms345357.aspx
http://blogs.msdn.com/b/manisblog/archive/2007/09/17/scale-up-with-sql-server-2005-sql-server-2008-numa.aspx
http://blogs.technet.com/b/beatrice/archive/2008/04/21/to-numa-or-not-to-numa.aspx
http://blogs.msdn.com/b/sqlserverfaq/archive/2010/06/28/how-to-set-soft-numa-for-sql-server-2008-r2.aspx

Tempdb capacity:
http://msdn.microsoft.com/en-us/library/ms345368.aspx
http://www.mssqltips.com/tip.asp?tip=1432
http://sqlcat.com/whitepapers/archive/2007/11/20/tempdb-capacity-planning-and-concurrency-considerations-for-index-create-and-rebuild.aspx
http://sql.dotnetbob.com/?p=140
http://blogs.technet.com/b/josebda/archive/2009/04/03/sql-server-2008-tempdb.aspx
http://blog.sqlauthority.com/2007/04/01/sql-server-tempdb-is-full-move-tempdb-from-one-drive-to-another-drive/

Design SQL Server instances:
This objective may include but is not limited to:

Instance configuration:
http://msdn.microsoft.com/en-us/library/ms143531.aspx

Surface area configuration:

CPU affinity:
http://msdn.microsoft.com/en-us/library/ms187104.aspx
http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/85a21a35-99c2-4633-912e-33f5ca1406b1
http://msdn.microsoft.com/es-es/library/ms187104.aspx

Memory allocation
http://msdn.microsoft.com/en-us/library/ms178145.aspx
http://technet.microsoft.com/en-us/magazine/dd370685.aspx
http://msdn.microsoft.com/en-us/library/ms190673.aspx
http://www.mydigitallife.info/2008/06/07/optimize-sql-server-2000-2005-or-2008-in-large-ram-system-by-locking-pages-in-memory-and-awe/
http://netindonesia.net/blogs/kasim.wirama/archive/2009/03/17/sql-server-2008-administration-best-practice.aspx

Max degree of parallelism (MAXDOP):
http://msdn.microsoft.com/en-us/library/ms181007.aspx
http://sqlblog.com/blogs/adam_machanic/archive/2010/05/24/sql-university-parallelism-week-introduction.aspx
http://sqlblog.com/blogs/adam_machanic/archive/2010/05/26/sql-university-parallelism-week-part-2-query-processing.aspx
http://sqlblog.com/blogs/adam_machanic/archive/2010/05/28/sql-university-parallelism-week-part-3-settings-and-options.aspx

Collation
http://msdn.microsoft.com/en-us/library/ms144260.aspx
http://msdn.microsoft.com/en-us/library/ms144250.aspx

Design physical database and object placement.
This objective may include but is not limited to:

Heap and Index Placement:
http://blog.sqlauthority.com/2008/03/30/sql-server-introduction-to-heap-structure-what-is-heap/

http://searchsqlserver.techtarget.com/tip/Clustered-and-non-clustered-indexes-in-SQL-Server

Filestream:
http://technet.microsoft.com/en-us/library/bb933993.aspx
http://msdn.microsoft.com/en-us/library/cc949109(v=sql.100).aspx
http://www.sql-server-performance.com/articles/dba/Configure_Filestream_in_SQL_Server_2008_p1.aspx

Summary:
Filestream data is backed up when the database is backed up
DB CANNOT use mirroring when Filestream is enabled on it
Snapshots DO NOT snapshot the Filestream data

Filegroups:
http://msdn.microsoft.com/en-us/library/ms179316.aspx
http://www.sql-server-performance.com/tips/filegroups_p1.aspx

Partition Placement:
http://sqlblog.com/blogs/erin_welker/archive/2008/02/10/partitioning-enhancements-in-sql-server-2008.aspx
http://www.sqlservercentral.com/articles/partition/64740/
http://blogs.technet.com/b/josebda/archive/2009/03/19/sql-server-2008-partitioning.aspx

Full Text Catalog
http://blog.sqlauthority.com/2008/09/05/sql-server-creating-full-text-catalog-and-index/

http://www.simple-talk.com/sql/learn-sql-server/understanding-full-text-indexing-in-sql-server/
http://www.sql-server-performance.com/articles/per/full_text_search_2008_p1.aspx
http://msdn.microsoft.com/en-us/library/ms142571.aspx
http://technet.microsoft.com/en-us/library/cc721269(SQL.100).aspx

Design a migration, consolidation, and upgrade strategy:
This objective may include but is not limited to:

Multi-instance Considerations:
http://www.mssqltips.com/tip.asp?tip=1048

SQL Server version upgrade:
http://msdn.microsoft.com/en-us/library/ms143393.aspx

Instance and database collation:
http://msdn.microsoft.com/en-us/library/ms179254.aspx

Server-level and instance-level objects,

???

Service pack application

???