Transcript Amit Shukla, Michael Wang SQL Server Engineering Team Microsoft Session Code: DAT201 Agenda Best practices Connect with SQL Server product team SQL Server Futures.

Amit Shukla, Michael Wang
SQL Server Engineering Team
Microsoft
Session Code: DAT201
Agenda
Best practices
Connect with SQL Server product team
SQL Server Futures
Part I: Building great solutions using an
increasingly feature rich product is hard!
You are looking for guidance on how to
best architect your applications
difficult to pick the right technologies for your
application
Learn how to better integrate SQL Server 2008 features
into common scenarios
Turn on Data Compression
Row
Compression white paper
Page
The general guideline is to use
“ROW” compression for OLTP
2% - 5% CPU
20% Compression
Compression white paper
The general guideline is to use
“PAGE” compression for Data
Warehousing
10% - 15% CPU
60% - 70% Compression
Compression white paper
Data Warehousing white paper
Backup compression
6
5
4
No Compression
3
Compression
2
1
0
Backup Size
Backup Compression
Backup Time
Restore Time
Use Resource Governor to prevent
runaway queries
Backup
OLTP Activity
Admin Tasks
High
Admin Workload
Min Memory 10%
Max Memory 20%
Max CPU 20%
Admin Pool
Resource Governor by Example
OLTP Workload
Max CPU 90%
Application Pool
Use MERGE for ETL
SQL Server 2005
SQL Server 2008
IF StockTable contains stock
UPDATE quantity from TradesTable
ELSE
INSERT row into StockTable
MERGE StockTable st
USING TradesTable tt
ON st.stock = tt.stock
WHEN MATCHED THEN
UPDATE SET
st.quantity += tt.quantity
WHEN NOT MATCHED THEN
INSERT (stock, quantity)
VALUES (tt.stock, tt.quantity)
;
How to use MERGE
Predictable Performance and Concurrency
Lock Escalation
Plan Freezing
Ad hoc workloads
Partition level lock escalation
Details of lock escalation
Use improved Plan Freezing
mechanisms
Understanding plan guides
Use improved Plan Freezing
mechanisms
Understanding plan guides
Use improved Plan Freezing
mechanisms
Understanding plan guides
Optimize SQL Server 2008 for ad hoc
workloads
Use Sparse Columns to store data
efficiently
Optimize
NULLs
White paper
Use Filtered Indexes to improve query
performance by indexing efficiently
White paper
Use TVP to perform large data set
loading
Batching
White paper
Use the best practices site to learn how
to take advantage of new features
http://technet.microsoft.com/en-us/sqlserver/bb331794.aspx
The site contains whitepapers
More white papers at:
http://www.microsoft.com/sqlserver/2008/en/us/white-papers.aspx
http://sqlcat.com/whitepapers/default.aspx
Part II: How can I get SQL Server to
help me?
The product team seems to operate
in isolation
Use some of the large variety of ways to
communicate with the product team
Enhancements to the OVER clause are popular
Use Other Feedback Channels
TLC is the feedback mechanism at TechEd
Ask your questions using MSDN Forums
Use Formal Feedback Channels
Visit our customer lab
Get invited to a System Design Review
Our automated data gathering
identifies feature usage and bugs
You can opt-in for anonymous &
automated gathering of feature
usage
Some 2008 feature usage collected:
Editions and hardware configuration
SQL Server 2008: Data type usage
Date
Geography
Geometry
Time
Sparse
SQL Server 2008: Objects per
database
Number of objects per database
(Enterprise edition servers, with >10GB of data)
250
200
150
100
50
0
You can opt-in to report crashes and
unexpected errors
Bugs are automatically filed by Watson
and tracked by bucket and IP address
Bugs appear in Product team’s bug
database automatically
You can now declare and set a
variable
OLD
DECLARE @v INT;
SET @v = 5;
NEW
DECLARE @v INT = 5;
You now have to type fewer characters
when adding a variable to itself
OLD
UPDATE Tbl
SET c1 = c1 + 5
WHERE c1 < 10;
NEW
UPDATE Tbl
SET c1 += 5
WHERE c1 < 10;
This also works for *, -, /
We made inserting multiple rows
into a table much easier
OLD
NEW
INSERT INTO @customers (custid,name)
VALUES (1, 'cust 1');
INSERT INTO @customers (custid,name)
VALUES (2, 'cust 2');
INSERT INTO @customers (custid,name)
VALUES (3, 'cust 3');
INSERT INTO @customers (custid,name)
VALUES (4, 'cust 4');
INSERT INTO @customers (custid,name)
VALUES (5, 'cust 5');
INSERT INTO @customers (custid, name)
VALUES
(1, 'cust 1'),
(2, 'cust 2'),
(3, 'cust 3'),
(4, 'cust 4'),
(5, 'cust 5');
We made using a constant table
super easy as well!
OLD
NEW
DECLARE @customers TABLE
(custid INT, name VARCHAR(20))
SELECT *
FROM (
VALUES
(1, 'cust 1'),
(2, 'cust 2'),
(3, 'cust 3'),
(4, 'cust 4'),
(5, 'cust 5')
) MyCustomers(custid, name);
INSERT INTO @customers (custid,name)
VALUES (1, 'cust 1');
INSERT INTO @customers (custid,name)
VALUES (2, 'cust 2');
INSERT INTO @customers (custid,name)
VALUES (3, 'cust 3');
INSERT INTO @customers (custid,name)
VALUES (4, 'cust 4');
INSERT INTO @customers (custid,name)
VALUES (5, 'cust 5');
SELECT *
FROM @customers;
Part III: Give you a glimpse into what
is coming up in SQL Server 2008 R2
2008 R2 will have support for 256
logical processors
2008 R2 will support Unicode
compression
Compress
Themes That Remain Unchanged
From Release to Release
Enterprise
data
platform
Dynamic
development
Beyond
Relational
Pervasive
Insight
Summary
Best practices
Connect with SQL Server product team
SQL Server Futures
Call to Action
Get in touch with the SQL Development Team
Leverage the mechanisms available to provide
feedback to Microsoft
Participate in the Customer Experience
Improvement Program, Error Reporting
Visit TLC – we want to hear from you!
Leverage the “Delighters” in SQL Server 2008
Help us make SQL Server even better.
Help us make SQL Server work for you!
SQL Server Word of the Day
Monday, May 11
POLICY-BASED
MANAGEMENT
*Game cards may be picked up at the SQL Server booths in the TLC
SQL Server Community Resources
The Professional Association for SQL Server (PASS) is an independent,
not-for-profit association, dedicated to supporting, educating, and
promoting the Microsoft SQL Server community.
• Connect: Local Chapters, Special Interest Groups, Online Community
• Share: PASSPort Social Networking, Community Connection Event
• Learn: PASS Summit Annual Conference, Technical Articles, Webcasts
Become
a FREE
PASSorganization
Member: www.sqlpass.org/RegisterforSQLPASS.aspx
• More
about
the PASS
www.sqlpass.org/
Learn more about the PASS organization www.sqlpass.org/
Additional Community Resources
SQL Server Community Center
www.microsoft.com/sqlserver/2008/en/us/community-center.aspx
TechNet Community for IT Professionals
http://technet.microsoft.com/en-us/sqlserver/bb671048.aspx
Developer Center
http://msdn.microsoft.com/en-us/sqlserver/bb671064.aspx
SQL Server 2008 Learning Portal
http://www.microsoft.com/learning/sql/2008/default.mspx
Additional Resources
External Resources
http://sqlcat.com
http://blogs.msdn.com/sqlcat
http://blogs.msdn.com/mssqlisv
http://technet.microsoft.com/en-us/sqlserver/bb331794.aspx
www.sqlserverinternals.com/books.html
SQL Server 2008 Business Value Calculator:
www.moresqlserver.com
Related Content
DAT320-Breakout: Optimizing Microsoft SQL Server 2008 Applications Using Table Valued
Parameters, XML, and MERGE
DAT04-TLC: Using the HIERARCHYID Datatype in Microsoft SQL Server 2008 to Maintain
and Query Hierarchies
DAT313-Breakout: Inside T-SQL: 2008 Enhancements, Techniques, Tips & Tricks
DAT304-Breakout: Auditing in Microsoft SQL Server 2008
DAT321-Breakout: Taking Your Database beyond Relations with Microsoft SQL Server
2008
DAT302-Breakout: All You Need to Know about Microsoft SQL Server 2008 Failover
Clusters
DAT317-Breakout: Microsoft SQL Server 2008 Data Warehousing by Demonstration
Resources
www.microsoft.com/teched
www.microsoft.com/learning
Sessions On-Demand & Community
Microsoft Certification & Training Resources
http://microsoft.com/technet
http://microsoft.com/msdn
Resources for IT Professionals
Resources for Developers
www.microsoft.com/learning
Microsoft Certification and Training Resources
Complete an
evaluation on
CommNet and
enter to win!
© 2009 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.
The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should
not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS,
IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.