1 / 26

ISV Proposition

ISV Proposition. Keith Burns Data Architect Microsoft UK keith.burns@microsoft.com. Data Profiling Star Join Enterprise Reporting Engine Internet Report Deployment Block Computations Scale out Analysis BI Platform Management Export to Word and Excel

elton-vincent
Download Presentation

ISV Proposition

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ISV Proposition Keith Burns Data Architect Microsoft UK keith.burns@microsoft.com

  2. Data Profiling • Star Join • Enterprise Reporting Engine • Internet Report Deployment • Block Computations • Scale out Analysis • BI Platform Management • Export to Word and Excel • Author reports in Word and Excel • Report Builder Enhancements • TABLIX • Rich Formatted Data • Personalized Perspectives • … and many more SQL Server Change Tracking Synchronized Programming Model Visual Studio Support SQL Server Conflict Detection FILESTREAM data type Integrated Full Text Search Sparse Columns Large User Defined Types Date/Time Data Type LOCATION data type SPATIAL data type Virtual Earth Integration Partitioned Table Parallelism Query Optimizations Persistent Lookups Change Data Capture Backup Compression MERGE SQL Statement • Transparent Data Encryption • External Key Management • Data Auditing • Pluggable CPU • Transparent Failover for Database Mirroring • Policy Management • Server Group Management • Streamlined Installation • Enterprise System Management • Performance Data Collection • System Analysis • Data Compression • Query Optimization Modes • Resource Governor • Entity Data Model • LINQ • Visual Entity Designer • Entity Aware Adapters

  3. Top ISV Features in SQL Server 2008 • Lock Escalation • Plan Freezing • Optimise for Ad-hoc Workloads Option • Resource Governor • Logging enhancements • Sparse Data and Indexes • Hierarchy Datatype • Spatial Data • Filestream • Security (Auditing & Transparent Database Encryption (TDE)

  4. Pain Points Lock escalation causes performance problems (blocking); cannot be controlled on per table basis Traceflags -T1211 & -T1224 used to disable lock escalation can help, but often result in other problems SQL Server 2008 disable lock escalation at a table level ALTER TABLE TabA SET (LOCK_ESCALATION = DISABLE); For partitioned tables, results in lock being escalated to the partition, instead of to the table level as in SQL Server 2005 Lock Escalation

  5. Pain Points Difficult to tune query plan when T-SQL code not accessible No easy way of ensuring plan consistency across upgrades SQL Server 2005 introduced Plan Guides and USE PLAN hint; but these were difficult to use SQL Server 2008 Plan Freezing builds on plan guides framework and introduces easier creation process based on plan cache entries (sp_create_plan_guide_from_cache) Full DML support INSERT, DELETE, UPDATE supported New function sys.fn_validate_plan_guide introduced to validate existing SQL 2005 plan guides Plan Freezing

  6. Pain Points Single use ad hoc batches can flood the procedure cache Using Forced Parameterization option for such scenarios can result in adverse side-effects SQL Server 2008 New ‘optimize for ad hoc workloads’ server option Set using sp_configure, or SQL Server Management Studio Stores stub for adhoc compiled plan after first execution, replaces with actual query plan if the query is re-executed Improves efficiency of plan cache Optimize for Ad hoc Workloads Option

  7. SQL Server 2008 RG is designed for workload balance in database engine Configurable at connection level; Online change of group/pool allowed Definition of group/pool transparent to application Users can control server level setting (MAXDOP) at workgroup level Prevent run-away queries Resource Governor Connections SQL Server Classifier Function Executive Reports OLTP Activity Backup Admin Tasks Ad-hoc Reports Report Workload High OLTP Workload Admin Workload Min Memory 10% Max Memory 20% Max CPU 20% Max CPU 90% Application Pool Admin Pool

  8. Logging Enhancements • SQL Server 2008 • INSERT into table supports minimal logging • 3X-5X Performance Boost over fully logged INSERT Run Time

  9. Sparse Column Storage • The problem • Need to store sparse data • Possibly 100’s of columns • Typically only few % are populated Typical Solution Desired schema

  10. Sparse Columns • “Sparse” as a storage attribute on a column • 0 bytes for a NULL, 4 byte overhead for non-NULL • No change in Query/DML behavior • Same limitations as normal tables eg 1024 columns • Wide Table / defining a “Sparse Column Set” • Column set columns can still be individually specified in SQL statements • Select * returns all non-sparse-columns + sparse column set as XML • 30,000 sparse columns allowed in a table (2Gb), 1000 indexes // Sparse as a storage attibute in Create/Alter table statements Create Table Products(Id int, Type nvarchar(16)…, Resolution int SPARSE, ZoomLengthint SPARSE); // Create a sparse column set Create Table Products(Id int, Type nvarchar(16)…, Resolution int SPARSE, ZoomLengthint SPARSE, Properties XML COLUMN_SET FOR ALL_SPARSE_COLUMNS);

  11. Filtered Indexes • Filtered Indexes and Statistics • Indexing a portion of the data in a table • Filtered/co-related statistics creation and usage • Query/DML Optimization to use Filtered indexes and Statistics • Restricted to non-clustered indexes • Benefits • Lower storage and maintenance costs for large number of indexes • Query/DML Performance Benefits: IO only for qualifying rows // Create a Filtered Indexes Create Index ZoomIdx on Products(ZoomLength) where Type = ‘Camera’; // Optimizer will pick the filtered index when query predicates match Select ProductId, Type, Resolution, ZoomLength where Type = ‘Camera’’

  12. HierarchyID • Keyproperties • Logically encodes the path from the root of the tree to a node • Rich built-in methods for manipulating hierarchies • Simplifies storage and querying of hierarchical data • Comparison a<b is in depth-first order • Support for arbitrary insertions and deletions • Potential Applications • Forum and mailing list threads • Business organization charts • Content management categories • Product categories • Files/folders management

  13. Storage and retrieval of spatial data using standard SQL syntax New Spatial Data Types (geometry, geography) New Spatial Methods (intersects, buffer, etc.) New Spatial Indexes Offers full set of Open Geospatial Consortium components (OGC/SQL MM, ISO 19125) Integration with Virtual Earth SpatialDataOverview

  14. FILESTREAM Store BLOBs in DB + File System • Storage Attribute on VARBINARY(MAX) • Works with integrated FTS • Unstructured data stored directly in the file system (requires NTFS) • Dual Programming Model • TSQL (Same as SQL BLOB) • Win32 Streaming APIs with T-SQL transactional semantics • Data Consistency • Integrated Manageability • Back Up / Restore • Administration • Size limit is the file system volume size • SQL Server Security Stack Application BLOB DB

  15. TRUSTED, SCALABLE PLATFORM IT & DEVELOPER EFFICIENCY MANAGED SELF-SERVICE BI PowerPivot / SharePoint 2010 Data Warehouse Edition Enterprise security and scalability Data consistency across heterogeneous systems High-scale, complex event processing Multi-server management Virtualization & Live Migration Accelerated development& deployment Self-service analytics Self-service reporting Streamlined collaboration& management Report Builder 3.0 Master Data Services Data Tier Applications Stream Insight

  16. Master Data Management ROLE SECURITY VERSIONING WORKFLOW APPROVAL HIERARCHY MGMT CRM Purchasing DB HR Doc ERP Asset Mgmt

  17. Latency Stream InsightScenarios for Event Processing Relational Database Applications CEP Target Scenarios Operational Analytics Applications, e.g., Logistics, etc. Data Warehousing Applications Web Analytics Applications Manufacturing Applications Financial trading Applications Monitoring Applications Aggregate Data Rate (Events/sec.)

  18. Stream Insight Event Complex Event Processing (CEP) is the continuous and incremental processing of event streams from multiple sources based on declarativequery and pattern specifications with near-zero latency. request output stream input stream response

  19. StreamInsight PLATFORM FOR KEY MARKET OPPORTUNITIES KEY SOLUTION REQUIREMENTS • Manufacturing: Process monitoring i.e. scheduling, control, inventory • Financial: algorithmic trading, compliance monitoring, fraud detection • Web: Click stream analysis ,ad-delivery model • Utility: Grid monitoring • Healthcare: Patient Monitoring • Networking: traffic monitoring ,security intrusion detection • Telecom: Network monitoring • Oil and Gas: Geological data analysis, process control • Event driven • Low latency processing (< milliseconds) • High volume (>100,000 events /second) • Correlated events from multiple sources • Time interval oriented standing queries

  20. Increasing Availability Live Migration Virtual Machine Virtual Machine MEMORY SYNC MEMORY CONTENT CONFIGURATION DATA Cluster Node 1 Cluster Node 2 User VHD Network Storage

  21. Additional SQL Server 2008 R2 report features • Mapping support • Report Builder 3.0 • Report Parts • Export to data feed

  22. SQL Server 2008 has many features designed specifically for ISV workloads; zero or minimal application change required There are literally 100s of new and improved features – this session only highlights some of the ones most popular with ISVs Understanding what problem each feature resolves as well as it’s performance, insights and limitations helps more appropriate usage Summary

More Related