Sql Server Monitoring And Performance

Chapter 9: Monitoring and Performance Course 157: SQL Server 2008 Database Administration Presented by Scott Whigham

1

p. 1

• Chapter Introduction • Tools for Monitoring • SQL Server Profiler

Monitoring

Overview

What We’re Going to Cover

• Understanding and Using the DMVs • PerfMon • Data Collector

p. 2

Chapter Introduction

• Chapter 9 is about performance and exception monitoring your server • We’ll start with the basics (using Profiler) and end with new features in SQL Server 2008 (Data Collector)

p. 3

• Chapter Introduction • Tools for Monitoring • SQL Server Profiler

Monitoring

Overview

What We’re Going to Cover

• Understanding and Using the DMVs • PerfMon • Data Collector

p. 4

Tools of Performance Monitoring

• There are many built-in tools to monitor performance and health: – Activity Monitor and sp_who • Covered in Chapter 5!

– SQL Server Profiler • Detailed view of statements issued against instance

– Dynamic Management Views (DMVs) • “Live” view of server health and activity

– Performance counters and objects (“PerfMon”) • “Live” view of server health and activity

– Data Collector • Historical view of server health and activity

p. 5

Tools of Performance Monitoring

“But Mr. Scott, how do I know when to use each one?” Profiler

DMVs

Perf Mon

Data Coll.

Requires manual load

Requires manual load

Requires manual load

Detail

Depends…

Summary

Summary

Low

None

Negligible

Low-Hi

100-300

200-400

200-300

300-500

Provides “live”, real-time reporting? Can do trend analysis and historical reporting? Can view SQL issued? Provides detail-level view or summary? Can be used to diagnose performance problems? Level of impact on server Difficulty level

p. 6

In the next video…

• Let’s play with Profiler!

p. 7

Chapter 9: Monitoring and Performance Course 157: SQL Server 2008 Database Administration Presented by Scott Whigham

8

p. 8

• Chapter Introduction • Tools for Monitoring • SQL Server Profiler

Monitoring

Overview

What We’re Going to Cover

• Understanding and Using the DMVs • PerfMon • Data Collector

p. 9

SQL Server Profiler

• SQL Server Profiler is a tool for monitoring real-time queries issued against your server – “Old Faithful” - been around for years – Requires sysadmin or ALTER TRACE server permission

p. 10

SQL Server Profiler

p. 11

SQL Server Profiler

• DBAs use traces to monitor statements sent to SQL Server – We’ve already seen how SQL Server uses “default traces” in Chapter 7

• Running traces will have an impact on server performance – How much? It depends on how much you trace

p. 12

SQL Server Profiler

• SQL Server Profiler is the GUI for defining, viewing, and replaying traces – It does not have to be running to create a trace – Many DBAs create their traces via Profiler and then script out to jobs

p. 13

SQL Server Profiler

• Running Profiler on a “live” system adds to an already stressful situation – Particularly if run interactively on the server

• If you must run Profiler against a “live” server, run it remotely • Adds network overhead but this is almost never an issue today

p. 14

SQL Server Profiler

• Trace data can be stored: – In a .trc file (fastest) – In a SQL Server table

• .trc files can be configured to rollover to a new file after a certain threshold • Can load .trc file contents into SQL Server table after trace for maximum flexibility!

p. 15

SQL Server Profiler

• After trace is complete, you can export results

p. 16

SQL Server Profiler

• Tips on working with Profiler – Traces can have filters to help minimize the noise • The better you filter, the less of an impact you put on the server

– Being able to write queries against the trace results can help you identify many common trends and problems • This requires you load the trace data into a table!

p. 17

In the next video…

• Let’s play with Profiler!

p. 18

Chapter 9: Monitoring and Performance Course 157: SQL Server 2008 Database Administration Presented by Scott Whigham

19

p. 19

• Chapter Introduction • Tools for Monitoring • SQL Server Profiler

Monitoring

Overview

What We’re Going to Cover

• Understanding and Using the DMVs • PerfMon • Data Collector

p. 20

Dynamic Management Views

• DBAs use Dynamic Management Views (“DMVs”) and Dynamic Management Functions (“DMFs”) to peek under the covers – In the sys schema and begin with dm_ – Used to get up-to-the-second performance information during critical time – Often need to JOIN many together to come up with desired results p. 21

Tools of Performance Monitoring

Reminder… Profiler

DMVs

Perf Mon

Data Coll.

Requires manual load

Requires manual load

Requires manual load

Detail

Depends…

Summary

Summary

Low

None

Negligible

Low-Hi

100-300

300-500

200-300

300-500

Provides “live”, real-time reporting? Can do trend analysis and historical reporting? Can view SQL issued? Provides detail-level view or summary? Can be used to diagnose performance problems? Level of impact on server Difficulty level

p. 22

Dynamic Management Views

• There are too many DMxs to cover each one so we’ll cover concepts and core DMxs – Over 130 DMVs and DMFs in SQL Server 2008!

• It is important for DBAs to maintain a script library

p. 23

Dynamic Management Views

• The easiest way to get to know the DMVs/DMFs is to categorize them – Below are the more popular categories for performance monitoring Category

DMx prefix

Database

sys.dm_db_

Execution/Sessions

sys.dm_exec_

IO

sys.dm_io_

OS

sys.dm_os_

Transactions

sys.dm_tran_

p. 24

Dynamic Management Views

• Intellisense can help you greatly!

p. 25

Dynamic Management Views

• Object Explorer can also help

p. 26

DMV and DMF Query

• You can also query system views for full list SELECT name, type_desc FROM sys.system_objects WHERE schema_id=4 AND name LIKE 'dm_%‘ ORDER BY name p. 27

DMV and DMF Query Results

p. 28

Database Category

• Database category: – 16+ DMVs and DMFs

SELECT name, type_desc FROM sys.system_objects WHERE schema_id=4 AND name LIKE 'dm_db%'

p. 29

In the next video…

• We’ll take a look at a few categories and examples

p. 30

Chapter 9: Monitoring and Performance Course 157: SQL Server 2008 Database Administration Presented by Scott Whigham

31

p. 31

• Chapter Introduction • Tools for Monitoring • SQL Server Profiler

Monitoring

Overview

What We’re Going to Cover

• Understanding and Using the DMVs • PerfMon • Data Collector

p. 32

Dynamic Management Views

• Reminder: the more popular categories for performance monitoring Category

DMx prefix

Database

sys.dm_db_

Execution/Sessions

sys.dm_exec_

IO

sys.dm_io_

OS

sys.dm_os_

Transactions

sys.dm_tran_

p. 33

Database Category

• Database category: – 16+ DMVs and DMFs

SELECT name, type_desc FROM sys.system_objects WHERE schema_id=4 AND name LIKE 'dm_db%‘ ORDER BY name p. 34

Database Category

• From a performance standpoint, you will spend a lot of time in the Database category – Primarily looking at table, index, and partition information • Size, fragmentation • Missing indexes • Index use

p. 35

Database Category

• Common index-related DMVs in the Database Category – sys.dm_db_missing_index_details returns information about indexes that could potentially help speed up the system – sys.dm_db_index_usage_stats can tell you whether an index is being used and, if so, in what way

p. 36

Database Category

• Common index-related DMFs in the Database Category – sys.dm_db_index_physical_stats is one way to identify fragmentation (see Chapter 8!) – sys.dm_db_index_operational_stats is similar to the DMV sys.dm_db_index_usage_stats yet only includes actual execution events instead of plan generation events

p. 37

In the next video…

• We’ll take a look at writing queries using the DMVs and DMFs in the Database category

p. 38

Chapter 9: Monitoring and Performance Course 157: SQL Server 2008 Database Administration Presented by Scott Whigham

39

p. 39

• Chapter Introduction • Tools for Monitoring • SQL Server Profiler

Monitoring

Overview

What We’re Going to Cover

• Understanding and Using the DMVs • PerfMon • Data Collector

p. 40

Dynamic Management Views

• Let’s look at the other categories now… Category

DMx prefix

Database

sys.dm_db_

Execution/Sessions

sys.dm_exec_

IO

sys.dm_io_

OS

sys.dm_os_

Transactions

sys.dm_tran_

p. 41

Execution/Session Category

• Execution/Session category: – 20+ DMVs and DMFs

SELECT name, type_desc FROM sys.system_objects WHERE schema_id=4 AND name LIKE 'dm_exec%'

p. 42

Execution/Session Category

• The Execution/Session category will help you spy on people – That’s what you really want to do, isn’t it? – Can also view specific query information • Plans, text, stats, caching information

p. 43

Execution/Session Category

• There is at least one DMF in this category that you definitely want to know – sys.dm_exec_sql_text allows you to see the query text a user submits • By itself, it is not that useful but combined with other Execution/Session DMVs it is very powerful • Accepts one parameter - the memory location of the query plan – Populated via a column found in other sys.dm_exec_ DMVs

p. 44

Execution/Session Category

• A common DMV in this category is: – sys.dm_exec_query_stats allows you to see

metrics about queries submitted to server • Reads, writes, waits, execution count, etc • plan_handle column is the column to pass to sys.dm_exec_sql_text to return SQL

p. 45

Return 50 most-run queries

SELECT TOP(50) sql.text , qs.last_execution_time , qs.execution_count , qs.last_elapsed_/1000000.0 as ElapsedTimeInSecondstime FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS sql ORDER BY qs.execution_count DESC

p. 46

Return 50 most-run queries

p. 47

Execution/Session Category

• Other common DMVs in this category are – sys.dm_exec_procedure_stats same as query_stats yet only returns stored procedure

info – sys.dm_exec_cached_plans gives you info on

the cached query plans • Is it compiled or adhoc? • How many times has it been used?

p. 48

Execution/Session Category

• Spying on users is easy! – sys.dm_exec_sessions is one row for each

authenticated session

-- Returns all non-internal sessions SELECT * FROM sys.dm_exec_sessions WHERE session_id>50

p. 49

In the next video…

• We’ll write several queries and see the Execution/Session category in action

p. 50

Chapter 9: Monitoring and Performance Course 157: SQL Server 2008 Database Administration Presented by Scott Whigham

51

p. 51

• Chapter Introduction • Tools for Monitoring • SQL Server Profiler

Monitoring

Overview

What We’re Going to Cover

• Understanding and Using the DMVs • PerfMon • Data Collector

p. 52

Dynamic Management Views

• Let’s look at the IO and OS categories now to help diagnose disk pressure Category

DMx prefix

Database

sys.dm_db_

Execution/Sessions

sys.dm_exec_

IO

sys.dm_io_

OS

sys.dm_os_

Transactions

sys.dm_tran_

p. 53

IO Category

• IO category: – 4+ DMVs and DMFs

SELECT name, type_desc FROM sys.system_objects WHERE schema_id=4 AND name LIKE 'dm_io_%‘ ORDER BY name p. 54

IO Category

• The most common item in this category is – sys.dm_io_virtual_file_stats() returns information about usage of the database files • Reads, writes, IO waits, size • Accepts two parameters: – database_id - use NULL for all databases – file_id - the file_id from sys.database_files; use NULL for all files

p. 55

IO Category

• sys.dm_io_virtual_file_stats

returns several columns: Column

Description

sample_ms

Milliseconds since server was restarted

num_of_reads

Physical reads of objects in database file

num_of_bytes_read

Large textual-based columns will affect this greatly

io_stall_read_ms

Total time a user process waited for IO to this file

num_of_writes, num_of_bytes_written, io_stall_write_ms

Database write activity

io_stall

Total amount of waits reading /writing to this file

p. 56

IO Category

• Here are some things to monitor with dm_io_virtual_file_stats() – Look at total reads+writes to find “most active” – Look at io_stall to identify files that might benefit from new disks • All databases have stalls; the important metric is “What percentage of the uptime of the server am I having stalls?” • Compare read/write stalls on log and data file to identify which could benefit from new/faster disks

p. 57

IO DMF -- View IO stats for all DBs on instance: SELECT DB_NAME(mf.database_id) AS DB , mf.physical_name, mf.type_desc , vfs.* FROM sys.dm_io_virtual_file_stats(null,null)vfs JOIN sys.master_files mf ON mf.database_id = vfs.database_id AND mf.file_id = vfs.file_id ORDER BY DB_NAME(mf.database_id)

p. 58

IO DMF

p. 59

IO DMF -- Aggregate total IO since last restart: SELECT DB_NAME(vfs.database_id) AS database_name, CAST(SUM(vfs.num_of_bytes_read + vfs.num_of_bytes_written) / 1048576. AS DECIMAL(12, 2)) AS IO_MB FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs GROUP BY vfs.database_id ORDER BY IO_MB DESC

p. 60

OS Category

• The OS category is one of the largest – 36+ DMVs (no DMFs!)

SELECT name, type_desc FROM sys.system_objects WHERE schema_id=4 AND name LIKE 'dm_os_%‘ ORDER BY name p. 61

OS Category

• You can use this category to find waiting tasks: – sys.dm_os_wait_stats - aggregated info on dozens and dozens of wait types – sys.dm_os_waiting_tasks - detail info on waits for a specific resource • Hint: use dm_os_wait_stats to identify the areas of contention and then dm_os_waiting_tasks to view task information

p. 62

OS Category

• These two DMVs are accumulative – Their statistics are the totals since SQL Server service has been started

• To track effectively over time, it is necessary to reset periodically – Common to store in a table for historical perspective – DBCC SQLPERF(‘’, CLEAR)

p. 63

OS Category -- Current wait info: SELECT wait_type, wait_time_ms / 1000. as wait_time_s FROM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC -- Clear out stats: DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR) -- Run this at a later time to view waits since CLEAR SELECT wait_type, wait_time_ms / 1000. as wait_time_s FROM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC

p. 64

OS Category

p. 65

OS Category

• To identify disk pressure, you can look in sys.dm_os_wait_stats for specific wait types – Order by wait_time_ms DESC to identify most often waits – If anyone of these are in the top two positions, you have disk I/O pressure: • • • • •

ASYNCH_IO_COMPLETION IO_COMPLETION LOGMGR WRITELOG PAGEIOLATCH_x

p. 66

In the next video…

• Let’s look further at the OS category and others

p. 67

Chapter 9: Monitoring and Performance Course 157: SQL Server 2008 Database Administration Presented by Scott Whigham

68

p. 68

• Chapter Introduction • Tools for Monitoring • SQL Server Profiler

Monitoring

Overview

What We’re Going to Cover

• Understanding and Using the DMVs • PerfMon • Data Collector

p. 69

OS Category

• The OS category has lots of info – sys.dm_os_sys_info - info such as number of CPUs, start time of service – sys.dm_os_tasks - lists each running task – sys.dm_os_loaded_modules - lists each loaded DLL that SQL Server is using • • • •

Cryptography Manager Kerberos Security Package Microsoft SQL Server Native Client 10.0 SQL Server Windows 95 Lite SCM

p. 70

OS Category

• Many OS category DMVs can help diagnose memory pressure – Performance Counters can help too – We’ll cover Perf Counters in an upcoming section of this chapter

p. 71

OS Category

• The main areas for memory pressure are: – Database page cache (used to cache table/index) – Query Workspace memory (for Hash and Sort ops) – Plan cache – There are others…

p. 72

OS Category

-- Find out what has the most entries -- in memory SELECT TOP (20)[Name], COUNT(*) AS 'Count' FROM sys.dm_os_memory_cache_entries GROUP BY [Name] ORDER BY COUNT(*) DESC;

p. 73

OS Category

p. 74

OS Category -- Top 10 consumers of Buffer Pool SELECT TOP 10 [type] , SUM(single_pages_kb) AS memory_in_kb FROM sys.dm_os_memory_clerks GROUP BY type ORDER BY SUM(single_pages_kb) DESC;

p. 75

OS Category

p. 76

OS Category

• One area that many servers have trouble with is the TokenAndPermUserStore cache – An internal security system that generates security tokens for sessions – Particularly troublesome area for DBs with lots of adhoc SQL – The problem can be that the TokenAndPermUserStore cache takes up so much memory and is so frequently accessed that when there are a high number of items in the cache, access takes a long time

p. 77

OS Category

1. Queries for diagnosing TokenAndPermUserStore cache

issues: -- What is the size? The larger, the slower! SELECT SUM(single_pages_kb + multi_pages_kb) AS ‘CurrentSizeOfTokenCache(kb)’ FROM sys.dm_os_memory_clerks WHERE name = 'TokenAndPermUserStore'

p. 78

OS Category

2. Queries for diagnosing TokenAndPermUserStore cache

issues: -- How many entries in the -- TokenAndPermUserStore? SELECT TOP (20)[Name], COUNT(*) AS 'Count' FROM sys.dm_os_memory_cache_entries GROUP BY [Name] ORDER BY COUNT(*) DESC;

p. 79

Scary!

p. 80

OS Category

• There are multiple ways to prevent TokenAndPermUserStore issues 1. Minimize ad-hoc SQL 2. Log in as a sysadmin (no tokens need to be generated) 3. Limit the size of the cache via sp_configure using • • •

access check bucket count access check cache quota http://support.microsoft.com/kb/955644

p. 81

OS Category

• There is one way to fix a “live” TokenAndPermUserStore issue – Free the system cache -- WARNING: Do not use on production without -a full understanding! -- After running, there will not be an entry -in sys.dm_os_memory_clerks for TAPUS!

DBCC FREESYSTEMCACHE('TokenAndPermUserStore')

p. 82

In the next video…

• Let’s look at blocking and locking…

p. 92

Chapter 9: Monitoring and Performance Course 157: SQL Server 2008 Database Administration Presented by Scott Whigham

93

p. 93

• Chapter Introduction • Tools for Monitoring • SQL Server Profiler

Monitoring

Overview

What We’re Going to Cover

• Understanding and Using the DMVs • PerfMon • Data Collector

p. 94

Performance Monitor

• To maximize coverage, you want to use both DMVs/DMFs along with Performance Monitor – a.k.a. PerfMon and System Monitor

• PerfMon gathers stats about SQL Server as well as the rest of the environment – Disk subsystems, non-SQL resource usage

p. 95

Performance Monitor

• PerfMon uses objects and counters – Objects are for categorization – Objects contain one or more counters

• When you install SQL Server, it installs a set of instance-specific objects

p. 96

Performance Monitor

• You can view the SQL Server objects and counters using the DMV sys.dm_os_performance_counters – Careful - more than 1,100 rows

p. 97

Performance Monitor

• A few things to know about

sys.dm_os_performance_counters – Only displays SQL Server instance’s counters – Any counter that has “/sec” in the name is cumulative since time SQL Server service was started • Transactions/sec • Bulk copy rows/sec

– To accurately read these values, compare intervals • Compare 0800 reading to 0900 reading, for example

p. 98

Performance Monitor

• Example query to return all deprecated features usage – Many feature usages are actually SQL Server internals and tools! SELECT * FROM sys.dm_os_performance_counters pc WHERE object_name LIKE '%Deprecated Features%' AND cntr_value > 0 ORDER BY counter_name p. 99

Performance Monitor

p. 100

Performance Monitor

• Querying for specific counters allows adhoc views of point-in-time information – But no graphs, charts, pretty colors

• For trend analysis, you have a few choices: – Store the query results in a table for later review – Use PerfMon to store logs for later analysis

p. 101

Performance Monitor

• Some DBAs will store certain values in a table for later querying or for historical perspective – Watch the rest of the chapter prior to attempting this! -- Create a new table (”PerfCounters”) -- and store current counters

SELECT * INTO PerfCounters FROM sys.dm_os_performance_counters

p. 102

Performance Monitor

• All DBAs will use PerfMon

p. 103

In the next video…

• Let’s take a look at specific objects and counters

p. 104

Chapter 9: Monitoring and Performance Course 157: SQL Server 2008 Database Administration Presented by Scott Whigham

105

p. 105

• Chapter Introduction • Tools for Monitoring • SQL Server Profiler

Monitoring

Overview

What We’re Going to Cover

• Understanding and Using the DMVs • PerfMon • Data Collector

p. 106

Performance Monitor

• There are a few specific areas you will want to monitor – Sometimes you can find that “Aha!” moment using ad-hoc monitoring – Sometimes you can only find that “Aha!” moment using trend analysis

p. 107

Performance Monitor

• A few topics before we go further: – PerfMon, counters with “/sec” in the name are not cumulative • Makes it easy to do ad-hoc monitoring

– PerfMon’s GUI defaults to taking measurements every one second • Too often for trend analysis • 30-seconds is generally “good enough” for ad-hoc troubleshooting

p. 108

Performance Monitor

• A few areas DBAs want to monitor: – Disk usage – CPU usage – Memory usage

• Let’s look at each!

p. 109

Performance Monitor

• A few areas DBAs want to monitor: – Disk usage – CPU usage – Memory usage

p. 110

Disk Usage

• Disk usage monitoring – Disk I/O is commonly a performance stealer – While SQL Server manages the “when” and “how often” of disk I/O, it is the operating system that performs I/O – When monitoring disk activity, you need to monitor both SQL Server’s disk activity and the disk activity of the server as a whole

p. 111

Disk Usage

• Disk I/O cannot be isolated as a metric – Example: You are monitoring disk I/O and notice that you have waits for disk reads/writes. Does this always mean that you need faster/more disks? • No! Could be that you are paging constantly and memory is the real bottleneck

p. 112

Disk Usage

• Important system objects and counters: Object

Counter

When to worry

PhysicalDisk

% Disk Time

Consistently > 90%

PhysicalDisk

Current Disk Queue Length

Particularly helpful when “% Disk Time” is high; allows to see how many I/O operations are waiting. Start worrying when this value is more than 1.5x the number of spindles

PhysicalDisk

Avg. Disk Queue Length

In many configs, a disk has 1 spindle. Start worrying when this value is more than 1.5x the number of spindles

Memory

Page faults/sec

“Some” page faults are normal; look for consistently high numbers

p. 113

Disk Usage

• PhysicalDisk: % Disk Time – Tells us how “busy” your array is – With this counter, you want to worry about sustained activity, not bursts – A % Disk Time of more than 75% for several continuous minutes occurring several times a day can likely be resolved: • By adding more disks to the array • By using faster drives

p. 114

Disk Usage

• PhysicalDisk: % Disk Time (cont.) – When you do notice this is high, start looking at the PhsyicalDisk: Current Disk Queue Length • How many disks are in my array? – Tells you how many spindles you have

• How many I/O tasks are waiting?

– If the number of waiting tasks is 1.5 or higher per spindle, you need better I/O performance!

p. 115

Disk Usage

• PhysicalDisk: Avg. Disk Queue Length – Monitor this over time • Business hours • 24 hour period • Peak times

– If you find that it exceeds more than 1.5 waiting tasks per spindle for continuous periods, increase disk performance

p. 116

Disk Usage

• Important SQL Server objects & counters to help diagnose I/O trouble – Object: • SQL Server: Buffer Manager

– Counters: • Page reads/sec • Page writes/sec

p. 117

Disk Usage

• Page reads/sec and Page writes/sec measure physical reads, not logical reads – Not logical reads! – Object: • SQL Server: Buffer Manager

– Counters: • Page reads/sec • Page writes/sec

p. 118

One Caveat

• High disk usage is not always a sign that you need more/faster disks – Sometimes poorly optimized queries can masquerade as disk/memory/CPU issues – Proper indexing and better queries can help eliminate slow queries as the culprit

p. 119

A Final Caveat

• PerfMon is great for monitoring disks directly attached to the server – Good but not great for monitoring SANs – Check with your SAN vendor for suggestions on monitoring disk activity

p. 120

In the next video…

• Let’s play with PerfMon and look at Disk, CPU, and memory counters

p. 121

Chapter 9: Monitoring and Performance Course 157: SQL Server 2008 Database Administration Presented by Scott Whigham

122

p. 122

• Chapter Introduction • Tools for Monitoring • SQL Server Profiler

Monitoring

Overview

What We’re Going to Cover

• Understanding and Using the DMVs • PerfMon • Data Collector

p. 123

Data Collector

• While it is fun and educational to learn the various DMVs and DMFs along with the relationships between various system views, it is a huge time sink – Pro tip: download the SQL Server 2008 System Views map – http://learnitfirst.com/go.aspx?id=5GR5

p. 124

Data Collector

• How to be an unsuccessful DBA: – Try to remember all the DMVs, DMFs, and Performance Counters – Perform all of your performance data collection by running 100+ line queries daily • Bonus points if you are the only person who knows where on the network these queries/files are located

– Spend 20 hours a week writing and running DMVbased queries

p. 125

Data Collector

• How to be an successful DBA: – Learn the most important DMVs, DMFs and Performance Counters for your situation • Every job is different and every database is different • OLTP databases need different metrics than OLAP • DBAs of a “single database only” server are often concerned with different things than DBAs of “20 database” servers

– Make your reporting easy to digest and easy to run • No more 20 hours a week manually writing/running DMVbased queries!

p. 126

Data Collector

• Life as a DBA has traditionally been spent doing a lot of manual collection of performance data 1. 2. 3. 4.

Create a tables to store performance data Write the queries to collect the data Create a job to store query results in tables Write queries against tables to determine trends, etc

p. 127

Data Collector

• This was how things were done for more than a decade – As recent as SQL Server 2000… – SQL Server 2005 debuted “Custom Reports” to help us • Allowed integration of SSMS with Reporting Services to create professional quality reports based on our queries • “SQL Server 2005 Performance Dashboard” reports

p. 128

Data Collector

• SQL Server 2008 includes the Data Collector to save you time – Creates a Management Data Warehouse (MDW) that stores critical tuning, planning, and activity history – Used daily by DBAs to do capacity planning, mitigate risks, free up time otherwise spent trying to write complex DMV/DMF queries!

p. 129

Data Collector

• The Data Collector is turned off by default – Enabling it is not a trivial decision… – Performance data collection will impact your system • It’s the Heisenberg uncertainty principle in action! • How much of an impact depends on what you collect and how often

p. 130

Data Collector

• A disclaimer: – Please test, test, test this prior to deploying in a production environment – This is version 1.0 software – Easy to “over collect” • 30GB of data collection in three days!

p. 131

Data Collector

• Q& for Data Collector – “What type of information can DC collect?” • Traces, PerfMon counters, SQL queries

– “Can I use DC to collect SQL 2005/2000 servers?” • No, SQL Server 2008 only

– “Is this another one of those ‘Enterprise Edition only’ features?” • No - works great in Standard and Workgroup Editions too!

p. 132

Data Collector

• Q& for Data Collector (cont.) – “How much of an impact will this have on my server?” • Microsoft says “5% of CPU and 250-350MB” for the built-in collection sets. Custom collection sets that you create may consume more – More on this later…

p. 133

In the next video…

• Let’s learn a bit more about the Data Collector

p. 134

Chapter 9: Monitoring and Performance Course 157: SQL Server 2008 Database Administration Presented by Scott Whigham

135

p. 135

• Chapter Introduction • Tools for Monitoring • SQL Server Profiler

Monitoring

Overview

What We’re Going to Cover

• Understanding and Using the DMVs • PerfMon • Data Collector

p. 136

Data Collector

• The Data Collector (“DC”) is built into SSMS

p. 137

Data Collector

• You can enable the DC via the Configure Management Data Warehouse Wizard

p. 138

Data Collector

• Configuring the DC is a two-step process: 1. Creating the Management Data Warehouse (the “MDW”) 2. Setting up the actual collection of metrics

p. 139

Data Collector

• Configuring the DC is a two-step process: 1. Creating the Management Data Warehouse (the “MDW”) 2. Setting up the actual collection of metrics

p. 140

Data Collector

• The MDW is a relational data warehouse to store all of your metrics for later analysis

p. 141

Data Collector

• When you create the MDW database, you define the name and physical file location • Placing the MDW database on either a dedicated server or dedicated disks will minimize performance impact to monitored servers

p. 142

Data Collector

• The MDW data can be shared with nonsysadmins – All sysadmins have access – Often want network admins to have access to performance data without having access to specific databases

p. 143

Data Collector

• There are three databases roles you can assign:

p. 144

Data Collector

• Once you decide security, you are ready to build the MDW

p. 145

In the next video…

• Configuring the DC is a two-step process: 1. Creating the Management Data Warehouse (the “MDW”) 2. Setting up the actual collection of metrics

p. 146

Chapter 9: Monitoring and Performance Course 157: SQL Server 2008 Database Administration Presented by Scott Whigham

147

p. 147

• Chapter Introduction • Tools for Monitoring • SQL Server Profiler

Monitoring

Overview

What We’re Going to Cover

• Understanding and Using the DMVs • PerfMon • Data Collector

p. 148

Data Collector

• Configuring the DC is a two-step process: 1. Creating the Management Data Warehouse (the “MDW”) 2. Setting up the actual collection of metrics

p. 149

Data Collector

• Back through the wizard…

p. 150

Data Collector

• The Cache directory is local storage on the monitored machine – Remember that this can be run from a central server

p. 151

Data Collector

• Data collector will create SQL Server jobs to perform data collection • The job has two main components: 1. Collect the info 2. Upload the info to the MDW

• Where you collect the info locally is the cache directory – Generally the %temp% folder

p. 152

Data Collector

• Ready, set, go!

p. 153

Data Collector

• What it should look like

p. 154

Data Collector

• Now comes the work… – You can use the System Data Collection Sets – You can create your own collection sets

• There is no graphical way to create your own collection sets – Must write Transact-SQL

p. 155

In the next video…

• We’ll take a look at enabling the Data Collector and viewing the results

p. 156