Monitoring SQL Server with DMVs

Monitoring SQL Server with DMVs

• Dr Greg Low
• Sunday @ 9am

What we will cover

• DMVs Introduced
• A New Insight Into Existing Technologies
• An Insight Into Newer Technologies
• Usimg DMVs in Custom Reports

DMVs Introduced

• SQL Server 2005+
• Internal state/helath of server
• Previously used system tables, DBCC, Profiler, Perfmon
• Diagnose problems, tune performance
• DMVs and DMFs

Scope and Permissions

• Require SELECt permission plus:
• Server scope – VIEW SERVER STATE
• Database scope – VIEW DATABASE STATE
• Create user in master and DENY to restrict across database
• Sys schema and dm_* naming

A New Insight Into Existing Technologies

• O/S
  • Sys.dm_os_performance_counters
• DB
  • Sys.dm_db_partition_stats
  • Sys.dm_db_index_usage_stats
  • Sys.dm_db_physical_stats
  • Sys.dm_db_operational_stats
  • Sys.dm_db_missing_index_details
  • Sys.dm_db_missing_index_columns
• Statistics
  • Sp_helpstats ‘Production.Product’;
  • CREATE STATISTICS ColorStats ON Production.Product(Color) WITH FULLSCAN;
  • DBCC SHOW_STATISTICS(‘Production.Product’,ColorStats);
• Server
  • Sys.dm_exec_sessions
  • Sys.dm_exec_requests
  • Sys.dm_exec_sql_text(…)
  • Sys.dm_exec_query_status
  • Sys.dm_exec_cached_plans
• Legacy
  • EXEC sp_who2
  • Master..sysprocesses
  • Master..syscacheobjects

An Insight into Newer Technologies

• sys.dm_clr_properties
• sys.dm_clr_appdomains
• sys.dm_clr_tasks
• sys.dm_db_mirroring_connections
• sys.dm_broker_connections
• sys.dm_broker_queue_monitors
• sys.dm_tran_top_version_generators
• sys.dm_tran_version_store

Using DMV’s in Custom Reports

• Look at Standard Reports to learn how to use DMVs
• Run a Custom report (generated in SSRS)
• Warning message: Trojan reports!
• Custom report runs in the context of the currently selected database – might not be the appropriate context
• Running the report once throws it into the drop down of recently used reports
• Added in SQL Server 2005 Service Pack 2
• Same familiar RDL format
• Object-Related Reports - Using existing object context
• SSMS 2008 can’t run SSRS 2008 reports! Must be SSRS 2005.

Learning to Use DMVs

• Report Samples are shipped with the product
• Good examples of end-to-end user of DMVs and DMFs
• Buck Woody – blogs

Top 15 DBA Tasks SQL 2005/2008

Top 15 DBA Tasks SQL 2005/2008
• Adam Cogan / Justin King
• www.SSW.com.au

Our tips (from the floor)
• scripting backups, index, reorgs
• SQL Agent jobs
• Using MSX
• Maintenance plans
• Emails for low disk space
• Polling for deadlocks
• Using SMO to generate scripts
• SCOM
• Send email on restart

Tools available (also from the floor)
• SQL Backup from Red-gate (works on all versions and not just Enterprise)
• Spotlight – alerts for email
• HybridX
• SQL Delta www.sqldelta.com (like red-gate)
• Idera
• Data Dude – Team System – AKA Microsoft Visual Studio 2008 Team Edition for Database Professionals
• Toad for SQL (an Oracle GUI)

Agenda
• Automating Alerts

Fear of the new century: no email
• people fear going without email

How do you do it?
• Automation is your friend
• You should automate all ordinary tasks
• This frees up time for you to perform “fun” tasks

1. Do you measure uptime?
• Measuring downtime to impress the boss
• Monitor Uptime
• Monitor Performance
• 2000 – How
o MOM
o 3rd parties
o Roll your own; generate Monthly report
• 2008
o Monitor servers – SCOM 2007 with SQL Server Management Pack
o Management Data Warehouse (MDW)

2. Are you up to date?
• Patching/Service packs
• SELECT @@VERSION (from Registered Servers folder)
• NetPing
• SQL Ping www.sqlsecurity.com/Tools/FreeTools/tabid/65/Default.aspx by Chip Pearson - Free
• SQL Squirrel by NGS www.ngssoftware.cm/products/database-security/ngs-squirrel-sql.php

3. Do you script everything?
• Manual + SQLCMD
• Powershell + SMO (was DMO)
• Data dude
o GDR
• SSW SQL Deploy

4. Are you using a Domain Account?
• Configure to run as a domain account

5. Don’t run as an Administrator(?)
• Grant admin privs or lose functionality
• Missing BUILTIN\Administrators account
• ShellRunas by Marc Ruvonovich


7. Have you Turned on the Default Alerts?
• Severity Level 19 and above
• Other errors
o Developers want an email when a problem
o Network Admins don’t want junk in the Event Log
o SQL2000 – mail was unreliable

8. Have You Created Your Own Alerts?
• You can create application-specific alerts
• RAISERROR WITH LOG
• Use Database Mail in the proc – Async, Queued

9. Alerting Based on Perfmon Counters
• Configure through same UI as Transact-SQL…
• Monitor critical information not otherwise easy to get at: Memory Usage, Database Size, Tempdb usage
• Note that this is NOT an expensive operation…

A cool tool…
• TeraCopy
• O for awesome

Management of data warehouse (performance)

Management of data warehouse (performance)

• Greg Low (again)

Agenda

• Show
  • Definition of the new tools and features
  • Demonstrations
• Flow
  • Explanation of how the features work
  • Demonstration of the features components
• Know and Go
  • Internals and architecture
  • Planning and Implementation

SQL Sever 2008 Performance Monitoring Tools

• Data Collector
• Management Data Warehouse
• Performance and Configuration* Reports
• Activity Monitor
• Graphical Showplan*
• SQL Profiler*
• Dynamic Management Views*
• Resource Governor (Enterprise Only)
• XEvents
• Database Engine Tuning Advisor (Enterprise Only)*

*=Enhanced Feature Take a snapshot (copy) of data

• from system data management views
• into a static copy
• e.g. sys.dm_exec_sessions

Activity Monitor (is really sweet)

• Processes
• Resource Waits
• Data File I/O
• Recent Expensive Queries

Data Collection and Evaluation Components

• SQL2K8 Engine Windows Service (Data Collector)
• SQL2K8 msdb
• Etc (missed it)

Data Collector Internals and Architecture

• Data Provider (Collection Item)
  • TSQL
  • Performance Counters (e.g. sys.dm_os_performance_counters)
  • Trace Definition
• Collection Set
  • Msdb Database tables
  • Linked to Agent jobs
• SSIS
• Management Data Warehouse

Planning Tips and Tricks

• Think Central
  • Collecting to a central MDW keeps you from monitoring the collection itself
  • Using a “Central Management Server” makes…
• Watch the Clock
• Space, the Final Frontier

Performance Monitoring Tools Roadmap

• Data Collection
  • Data Collection Sets
• Performance and Diagnostics Monitoring
  • System Collection Sets Reports
• Historical and baseline comparisons
  • Management Data Warehouse going forward
• Trouble-shooting and Tuning
  • Policy based management

Answering the Queries Your Users Really Want to Ask

Answering the Queries Your Users Really Want to Ask

• Dr Greg Low
• glow@solidq.com
• SolidQ Australia

When querying a database, what do users want?

1. They’re not sure
2. They don’t want to be precise
3. They do want to use their own terminology
4. The want the answers fast

What do we give them?

1. Really limited choices
2. Really strict terminology
3. …

What we will cover

• What can you do for me?

Can’t we just use LIKE?

• WHERE Description LIKE ‘%hockey%’

Strings vs Words

• Search for: Pen
• Get Back: Pencil = fail
• Or Open, pendulum, penis

CONTAINS

• AND
• OR
• (‘”exact match”’)
• FORMSOF(INFLECTIONAL, word)
• FORMSOF(THESAURUS, word)

FREETEXT

• search for a sentence
• with ranking

CONTAINSTABLE

• ISABOUT (with weighting)

For true location of where to place your personalised thesaurus:

• don’t go to the Books Online site
• Instead read Greg’s blog here: http://sqlblog.com/blogs/greg_low
• SQLDATA\MSSQL10.MSSQLSERVER\MSSQL\...

Upgrade Options

• New Index Structure (the structure has been brought internal to the database engine)
• Upgrade options:
  1. Import(default)
  2. Rebuild
  3. Reset
• Possible Upgrade methods
  1. In place
  2. Restore/attach

Potential Extensions

• New data types
  1. XML data types
  2. CLR UDT
• Extend the IFTS feature set
  1. snippets with hit-highlights
  2. field weighted relevance
  3. customizable tokenizing
  4. customizable proximity operator
  5. property level search
• Be heard now!

Summary

• IFTS can add significant value
• Implementation -> straightforward
• Management -> straightforward
• Users love this
• SQL Server 2008 has changed the game!

SQL 2008 – TSQL Enhancements

SQL 2008 – TSQL Enhancements

• Justin King
• Architect SSW
• http://kingjustin.com/
• http://www.ssw.com.au/

Agenda

1. Platform changes
2. XML Enhancements
3. Compound Operators
4. Row Constructors
5. MERGE keyword
6. Table Valued Parameters
7. Sparse Columns
8. Date Time enhancements
9. Filestream
10. Spatial data
11. Hierarchy Data Type

Platform Changes (Justin’s top 8)

1. Database Mirroring enhancements
2. Policy Based Management
3. Auditing
4. Data Compression
5. BACKUP Compression
6. Powershell Integration
7. Transparent Database Encryption
8. Change tracking on databases

XML Enhancements

• Improved schema validation capabilities
• Enhancements to XQuery support
• Enhanced functionality for performing XML data manipulation language (DML) insertions
• Full timezone support for data/time types (including xs:date, xs:time)
• http://www.microsoft.com/sqlserve/2008/en/us/wp-sql-2008-whats-new-xml.aspx

Compound Operators

• Can now declare and initialize variables in the same statement
• +=
• -=
• /=
• *=
• %=

Row Constructors

• Values clause returns relational table with multiple rows
• Use with INSERT statement to insert multiple rows as an atomic operation;

Change Tracking

• Version of the data
• Most commonly used for entity data models

MERGE Keyword

• UPSERT
• Atomic statement combining INSERT, UPDATE and DELETE operations based on conditional logic
• Set-based operation; more efficient than multiple separate operations
• MERGE is defined by ANSI SQL; you will find it in other database platforms as well e.g. Oracle
• Useful in both OLTP and Data Warehouse environments
  • OLTP: merging recent info from external source
  • DW: incremental updates of fact, slowly changing dimensions

Table Valued Parameters

• CREATE TYPE [typeName] AS TABLE
• Great for passing in table parameters to a stored proc
• Strongly type variables
• Helps address the need to pass array elements to stored proc/functions

Sparse Columns

• Ordinary Columns optimised for NULL values
• No space used unless data added (4 byes extra if you do use)
• Behaves the same for end user

Date Time enhancements

• New date and time types!
• DATE – ANSI compliant
• TIME – ANSI compliant
• DATETIMEOFFSET – TZ aware DateTime
• DATETIME2 – DateTime with variable precision and larger range support

Filestream

• Stores binary files on file system
• Can’t use with MIRROR-ed databases
• Varbinary(max) – gets over 2GB limit
• Not accessible by files only by SQL Server
• Reduce size of database and backups

Spatial Data

• Two new system CLR data types
• GEOMETRY
• GEOGRAPHY

Hierarchy Data Type

• New system CLR type supporting trees
• Internally stored as Varbinary <= 900 bytes
• Holds a path that provides…

Resources

• Books Online
• TechEd DVD’s
• PDC Videos
• MSDN Webcasts
• Greg Low SQL Downunder podcast

Preparing a Business Case Upgrade to present to the Boss

Preparing a Business Case Upgrade to present to the Boss

• Darryl Burling
• Product Manager, SQL Server
• Microsoft

Agenda

• Fake Company
• Fake Scenario requirements
• 8 Business Drivers
• Justifying an upgrade

Company Overview

• Supplies, sells and maintains farm equipment for rural customers (farmers, etc)
• Customers across New Zealand
• 400 staff
• Sales reps based in North and South Islands
• Annual Revenue $125m
• Customers on 3 year supply agreements and some on no agreements (cash basis)

Scenario Requirements

• Customer Management
• Used by 350 of 400 staff in the company
• Must be highly available
• Deployment config.
  • Dual CPU Primary Server
  • Single CPU Mirror Server
  • Single CPU DR Server
• Requires support
• Features required
  • Snapshots
  • Partitioning
  • Reporting
  • OLAP
  • Geographic data
  • Photo storage
  • Compression
  • Encryption (incl. backup)

The Contenders

• MySQL (by Sun)
• Oracle
• Microsoft SQL Server 2008

1. Tangible Costs
   1. MySQL

i. $0 upfront costs ii. $53,910 for 3 years iii. Support and maintenance iv. NZ$101,835.99

1. 1. Oracle 11g Enterprise Edition

i. Enterprise license $47,500 per processor ii. Snapshots $5,800 iii. Partitioning $11,500 iv. Reporting $23,000 v. OLAP $23,000 vi. Geographic data (locator now built in) vii. Etc… viii. Total product $244,600 ix. Maintenance $53,912 per annum x. Total over 3 years $406,036 xi. NZ$767,002

1. 1. SQL Server 2008 Enterprises

i. Total product $113,275 ii. Software Assurance $28,318 iii. Total over 3 years NZ$198,232.27

1. Skills Availability
   1. How easy is it to get people to work on it? Available workforce.
   2. How much do these people costs?
   3. How easy is it to solve short term skills shortages?
   4. Do people want to work with the product?
   5. What are the up-skill requirements if someone leaves?

Salary Comparison (itsalaries.co.nz – results based on a search in Wellington for a DBA by keyword)

• Oracle
  • Median Base Salary $93,250
  • Upper quartile $99,500
  • Lower quartile $85,000
• SQL Server
  • Median $78,000
  • Upper quartile $110,500
  • Lower quartile $70,500
• MySQL/Open Source DBA
  • Median $0

1. Customer requirements
   1. Will it meet the customer requirements?
   2. Watch out for unspoken requirements
   3. If it doesn’t fit, what will it cost to make it meet the requirements?

Customer requirements

• MySQL doesn’t meeting requirements

1. Third party products and services
   1. How many companies are extending this product?
   2. Are there any specialist partners…?

1. Standards Compliance
   1. ANSI
   2. WS*

Considerations for business methodology

• Microsoft shop or heterogeneous?
• Existing skills or hire in new ones?
• Existing installations or green fields?
• Interoperability – will it work with existing systems?

1. User functionality requirements
   1. User friendliness
   2. Discoverability
   3. Consistent user experience
   4. Think users – not necessarily customer
   5. Users should not hate the product!

i. Should be easier to use, faster to get results

1. 1. Ease of administration

Functionality issues – Oracle

• Growth by acquisition
• Add on products
• Add hoc user experience
• Add hoc management tools
• Some management tools are poor quality (apparently)

Functionality issues – SQL Server

• Integration at each version
• Included in product
• Integrates with other products

1. Timeliness
   1. If I order it today, how long until users can be productive on it?
   2. If I need to extend it – how much ability do I have to modify the solution?
   3. How quickly can problems be solved?
   4. It may not be a good fit if: it looks complex; it suffers from poor quality.

1. Life expectancy
   1. How long will the vendor be around
   2. How long will they support the product for?
   3. When is the next version likely to be out?
   4. Is there a migration path for the next version?
   5. How easy is it to upgrade to the next version?

Summary

1. Tangible costs: $622k vs 200k
2. Skills availability: Good vs great
3. Customer requirements: met – complex vs simple
4. Third party products and services: good
5. Standards and methodology fit: Good vs great
6. User Functionality requirements: YMMV
7. Timeliness: Ok vs good
8. Life expectancy: OK vs good

Why move from SQL 2000 to 2008?

• Reporting
  • Out of the box
  • Rich controls
  • End user report builder
  • Scale out capabilities
• Security
  • Encryption (column and database)
  • External Key management
  • SDLC
• Storage efficiency
  • Filestream
  • Sparse columns
  • Compression
• Integration server
  • High performance
  • Large scale ETL
  • Scheduled jobs
• Performance
  • 20-35% improvement
• Management
  • Policy based management
  • Configuration servers
  • SCOM (system centre operations manager) monitoring
  • Powershell
  • Online indexing
• Auditing
  • Compliance management
• Partitioning
  • Table partitioning (05)
  • Index and view partitioning (08)
• Analysis Server
  • You get it

Migrating DTS to SSIS 2008

Migrating DTS to SSIS 2008

• Myles Matheson
• Solution Specialist
• Microsoft New Zealand

Session Objectives

• Explain the migration story for SSIS 2008
• Describe tools and practices for migration
• Provide guidance for current engagements

Key takeaways

• We will not break existing installs
• This is not the next version of DTS – it’s the first version of a whole new product
• Migration is not perfect
• Redesign is a better option

Agenda

• From DTS to SSIS
• Upgrade Experience
• Support for Migration
• Migrating Packages

Simple Data Transformation

• DTS Data Transformation and SSIS Data Flow objects models don’t map 1-1.
• Goal: Migrate all packages created by the Import/Export Wizard and any others with equivalent functionality

Quiz:

1. What’s the first thing to do before a migration? Run Upgrade Advisor
2. What happens when SSIS Migration Wizard can’t upgrade a DTS package? It will be encapsulated and called back from a legacy DTS runtime.
3. What else should be first? Get funding/permission!