Transcript New in - Microsoft

Agenda
Microsoft and Oracle Partnership Update
SQL 2005 in the Enterprise
Top SQL 2005 Features for JDE
64 –bit Platform Support
Data Partitioning
Index, Cursor and Query Enhancements
DMV, Plan Guides
Database Mirroring
SQL 2005 Resources
Q&A
Microsoft & Oracle
Partnership
Business Development
Look for areas where we can collaborate
Facilitate the business and technical dialog
Technical Collaboration
Oracle database and applications
Microsoft platform and tools
Communication channels open at various levels in the
organizations
Joint support contacts for customer escalations
Conference calls, briefings, etc.
Co-Marketing and Events
PDC, TechEd, IOUG, UKOUG
Oracle OpenWorld Gold Sponsor
1st Generation
2nd Generation
SQL Server
6.0/6.5



SQL Server
7.0
Differentiation from
Sybase SQL Server
Windows integration
First to include
Replication



Cross-release
objectives


Re-architecture of
relational server
Extensive auto
resource
management
First to include
OLAP & ETL
3rd Generation
SQL Server
2000




Performance,
scalability focus
XML support
First to include
Notification
First to include Data
Mining & Reporting
Reliability and Security
Integrated Business Intelligence


SQL Server
2005





High availability
Security
Developer
productivity
Native XML
First to include
Enterprise ETL and
Deep Data Mining
Lowest TCO
Automatic Tuning
Increased
Productivity
• Up to 40% faster development time w/ VS & .NET integration
• Up to 60% faster building distributed apps w/ Service Broker
• Elimination of routine tasks thru new automatable tools
Better
Business
Insight
•
•
•
•
2x faster performance on large scale OLAP Dimensions
New End User Reporting Tool
New SSIS enabling high scale data integration
Easier access to information thru MS Office
Increased
Mission
Critical
Support
•
•
•
•
35% faster transaction processing
5x faster failover enabling 99.999% availability
50% faster performance for ETL operations
New Native Encryption protects business data
SQL Server in the Enterprise
JD Edwards Support on SQL Server 2005
Supported EnterpriseOne Tools version
Planned as a postrelease patch of 8.96
Supported App versions
8.9
8.10
8.11
8.12
Supported platforms
32-bit
X64 (AMD Opteron and Intel EM64T)
SQL Server 2005 Upgrade Advisor
 Analyze all database and services
running on a server
 Identify potential risks and issues
 Suggest solutions and fixes
 Advise when action should be taken
Upgrading to SQL Server 2005
You can reduce upgrade down time
Pre-install Setup pre-requisites
Microsoft .NET Framework 2.0
Microsoft SQL Native Client
Setup support files
Upgrade Path
In-place upgrade
Least complicated for upgrade, but requires more planning for
fallback
Side-by-side upgrade
Install a new SQL Server 2005 instance
Attach or restore JD Edwards database after installed
Easy fallback should problems occurs
Upgrading to SQL Server 2005
after the upgrade
Set JD Edwards database compatibility level to 90
Run update statistics – extremely important
SQL2K statistics stale after upgrade, update statistics will
kick in automatically upon first execution of queries
Manually update statistics after upgrade. Use full scan if
possible.
Run surface area configuration manager after
upgrade
May not recognize an important component is off by
default: name pipes, Service Broker, CLR, FTS, Dedicated
Administration Connection, etc.
SQL Server 2000 vs. SQL Server 2005
Scalability Comparison
Joint Study by Network Appliance and Microsoft
Significant query performance improvements in SQL Server 2005
For complete study, go to http://www.netapp.com/partners/docs/msftsqlserver2005.pdf
New in
Database Engine
 Service Broker
 HTTP Access
 Database Tuning Advisor
 Enhanced Read ahead and scan
 Indexes
with Included Columns
 Multiple Active Result Sets
 Persisted Computed Columns
 Try/Catch in T-SQL statements
 Common Table Expressions
 Server Events
 Snapshot Isolation Level
 Partitioning
 Synonyms
 Dynamic Management Views
.NET Framework
 Common Language Runtime Integration
 CLR-based Types, Functions, and Triggers
 SQL Server .NET Data Provider
Data Types
 CLR-based Data Types
 VARCHAR(MAX), VARBINARY(MAX)
 XML Datatype
Database Failure and Redundancy
 Fail-over Clustering (up to 8 node)
 Database Mirroring
 Database Snapshots
 Enhanced Multi-instance Support
XML
 New XML data type
 XML Indexes
 XQUERY Support
 XML Schema (XSD) support
 FOR XML PATH
 XML Data Manipulation Language
 SQLXML 4.0
Database Maintenance
 Backup and Restore Enhancements
 Checksum Integrity Checks
 Dedicated Administrator Connection
 Dynamic Configuration AWE
 Highly-available Upgrade
 Online Index Operations
 Online Restore
Management Tools
 MDX and XML/A Query Editor
 Maintenance Plan Designer
 Source Control Support
 Profiler access to non-sa
 SQLCMD Command Line Tool
 Database Mail
Performance Tuning
 Profiling Analysis Services
 Exportable Showplan and Deadlocks
 Profiler Enhancements
 New Trace Events
Full-text Search
 Backup/Restore includes FT catalogs
 Multi-instance service
SQL Client .NET Data Provider
 Server Cursor Support
 Multiple Active Result Sets
Security
 Catalog and meta-data security
 Password policy enforcement
 Fine Grain Administration Rights
 Separation of Users and Schema
 Surface Area Configuration
Notification Services
 Embed NS in existing application
 User-defined match logic
 Analysis Services Event Provider
Replication
 Seamless DDL replication
 Merge Web Sync
 Oracle Publication
 Peer to Peer Transactional replication
 Merge replication perf and scalability
 New monitor and improved UI
Analysis Services and Data Mining
 Analysis Management Objects
 Windows Integrated Backup and Restore
 Web Service/XML for Analysis
 Integration Services and DM Integration
 Eight new Data Mining algorithms
 Auto Packaging and Deployment
 Migration Wizard
Integration Services
 New high performance architecture
 Visual design and debugging environment
 Extensible with custom code and scripts
 XML task and data source
 SAP connectivity
 Integrated data cleansing and text mining
 Slowly changing dimension wizard
 Improved flow control
 Integration with other BI products
Reporting Services
 Report Builder
 Analysis Services Query Designer
 Enhanced Expression Editor
 Multi-valued Parameters
 Date Picker
 Sharepoint Web Parts
 Floating Headers
 Custom Report Items
 XML Data Provider
Top SQL Server 2005 Features
for JDE
•
•
•
•
•
•
•
•
64-bit Platform Support
Data Partitioning
Index Enhancements
Cursor Enhancements
Enhanced Query Processing
Plan Stability
Dynamic Management View
Database Mirroring
64-bit Platform Support
SQL 2005 x64 provides native support for x64
platforms (from AMD and Intel) and runs on
Windows 2003 x64
Nearly unlimited Virtual Address Space (VAS)
Same price point as 32-bit machines
Best price/performance platform
Virtualized Licensing
Per processor instead of per core licensing
Reduce database cost by a factor of 3 or more
Is 64-bit for you?
32-bit vs. 64-bit
32-bit
Higher clock speed
Smaller memory footprint
Limited to 3GB VAS, AWE helps, but…
64-bit
Flat memory, nearly unlimited VAS
Supports up to 64 processors
Lower clock speed, larger memory footprint
When should you switch to 64-bit for JD Edwards
Need more memory for more connections, cursors
High compiles/sec due to memory recycling
High disk access due to low memory
High sort/hashing activities in tempdb
Data Partitioning
Separate data in a table into multiple manageable pieces
The row is the unit of partitioning
Support single key range partitioning
Instantaneous Split, Merge, Switch Partitions
Sliding window scenario
Transparent to the application
Allow easy management of very large tables and indexes
Fast Insert or Delete of large quantities of data (per-partition)
Index defragmentation or rebuild on one partition using ALTER
INDEX … PARTITION (<num>)
Delete Data From Unpartitioned Table
Delete from BigTable where OrderYear < 1995
Delete index
entries
Delete data
rows
Deletion of data
rows means to
maintain the index
tree, as well as the
data pages which
might take minutes
or even hours on
very big tables (
depending on how
many rows will be
removed )
Delete Data From Partitioned Table
Alter table BigTable switch partition 1 to NewTable
NewTable
BigTable
Data won‘t be
physically moved.
It‘s just a „logical“
move by changing
meta data in the
system tables of
the database
server.
1994
1995
1996
1997
1998
1999
Index Enhancements
Index issues
JD Edwards ships one schema for all verticals/components
 not all shipped indexes are used
Unused indexes create performance / on-disk storage
overhead
Index rebuild requires “long-term” table lock
SQL Server 2005
Disable index feature removes the need for maintaining
unused indexes while preserving the index definition
Online index rebuild, reorganize and drop allow index
access during maintenance
Cursor Enhancements
SQL 2000 / JDBC 2000 Cursor issues
JDBC 2000 supports dynamic cursor only
Dynamic cursor has plan limitations
Cursor scalability limited by 3GB virtual address space
SQL Server 2005 / JDBC 2005
JDBC 2005 supports all cursor types
Reduced cursor memory footprint
No more cursor memory limitation on 64-bit
Enhanced Query Processing
Statement Level Recompile
Plans for stored procedures and batches no longer
recompile the whole.
Reduced compile time and resources.
Forced Parameterization
Database level option to enable parameterization for all
queries automatically without application code changes.
Asynchronous Statistics Calculation
Statistics recalculation no longer blocks compilation or
recompilation
Dynamic Management Views
(DMVs)
Reflect what’s going on inside the server process
itself or across all sessions in the server.
Used for diagnostics, memory and process
tuning, and monitoring potentially across all
sessions in the server
More than 80 DMVs, For example
dm_exec_*
dm_tran_*
dm_os_*
dm_io_*
dm_db_*
dm_repl_*
dm_broker_*
…
Query execution and connections
Transactions and isolation
Memory, locking and scheduling
IO on network and disk
Databases and db objects
Replication
Service Broker
DMV
Sample Questions DMV Answers
“sys.dm_exec_query_stats” – what’s the top 5 query
with the most io?
“sys.dm_exec_query_plan” – what execution plan
was used to run the query last time
“sys.dm_db_index_usage_stats” – what are the
rare/never used indexes?
“sys.dm_db_index_operational_stats” – how are the
indexes being accessed
“sys.dm_os_schedulers” – is the system under CPU
pressure
“sys.dm_os_memory_clerks” – which component is
consuming the most memory
DMV Example
Top CPU usage by statement
SQL Server 2000
Profiler Statement level trace
Must capture real time
SQL Server 2005 – (DMVs)
SELECT TOP 5
qs.total_worker_time/qs.execution_count as [Avg CPU Time],
SUBSTRING(qt.text,qs.statement_start_offset/2,
(case when qs.statement_end_offset = -1
then len(convert(nvarchar(max), qt.text)) * 2
else qs.statement_end_offset end qs.statement_start_offset)/2)
as query_text,
qt.dbid,
qt.objectid
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY
[Avg CPU Time] DESC
Plan Stability
Plan in cache is not optimal
JD Edwards queries are dynamically generate by
application, DBAs can’t change the application
99%+ of queries run well, but …
DBAs need more control of optimization than just
creating / dropping indexes or updating statistics
Solution: “out-of-band” query hints
Plan Guide
Plan Guide
Allow controlling optimization with query
hints when the application cannot be changed
Matching During module (sp, trigger, tvf) compilation / batch
compilation
sp_create_plan_guide [ @name = ] N'plan_guide_name'
, [ @stmt = ] N'statement_text'
, [ @type = ] N' { OBJECT | SQL | TEMPLATE }'
, [ @module_or_batch = ]
{
N'[ schema_name.]object_name'
| N'batch_text'
| NULL
}
, [ @params = ] { N'@parameter_name data_type [,…n ]' | NULL }
, [ @hints = ] { N'OPTION ( query_hint [,…n ] )' | NULL }
Plan Guide Scenarios
Force use of a specific JOIN type for a specific query
Force optimizer to always use a particular parameter
value
Force compilation sharing for queries with same
format but different constant literals
Force the use of a specific plan for a manually
parameterized query
Database Mirroring
Synchronization can be synchronous or asynchronous
Zero to minimal impact on transaction throughput
Instantaneous failover
Works on standard hardware with no shared storage
components
Automatic, transparent client redirect
Snapshot on mirror provides reporting capability
Database Mirroring
Two copies of the same database:
Principal: This copy is accessible. Applications connect to
it.
Mirror: Always in the “Restoring” state. Applications
cannot connect to it.
Transaction log records are transferred from the
principal to the mirror continuously to keep the
mirror up-to-date.
Optionally, A third SQL Server instance can be used
as Witness
Required for automatic failover
The granularity of database mirroring is a database.
Multiple databases in an instance can be mirrored.
Basic Principle of Mirroring
(synchronous mode)
Acknowledge
Acknowledge
Commit
Constantly
Redoing on
Mirror
Transmit to Mirror
Write to
Local
Log
DB
Committed
in Log
Log
Write to
Remote
Log
Log
DB
Additional Information
SQL Server 2005 - Books Online
http://www.microsoft.com/sql/2005/default.msp
x
http://msdn.microsoft.com/SQL/2005/default.asp
x
http://www.microsoft.com/technet/prodtechnol/s
ql/2005/default.mspx