Transcript Building Blocks for High Performance SQL

© 2014 Plex Systems Proprietary + Confidential 7/18/2015 | Slide 1
Building Blocks for High
Performance SQL
Corey Dollman-Jersey
About Me
•Software Engineer at Plex systems
•Microsoft Certified IT Professional for SQL Server 2008
•Worked on SQL Server since ‘SQL Server 2005’
•My primary focus is system performance
•First time presenter at SPID (Please bear with me!)
[email protected]
© 2014 Plex Systems Proprietary + Confidential 7/18/2015 | Slide 2
About Plex
•
•
•
•
Plex Systems (@plexsystems) was founded in 1995
Web based software for Enterprise Resource Planning (ERP)
20 million lines of code – 7.7 million of that is SQL
Schema:
• 5,300 tables
• 48,000 stored procedures and views
• Data:
• 36 TB of hot data
• 400 TB of total managed data
• 48 billion rows
• 1.6 billion rows added monthly
• 450 billion page lookups per day (3.4PB)
• 3.0 billion SQL Statements executed per day (an average of 34,000 SQL
Statements/second)
© 2014 Plex Systems Proprietary + Confidential 7/18/2015 | Slide 3
About Plex
12,000
10,000
8,000
6,000
4,000
2,000
Jan-11
Mar-11
May-11
Jul-11
Sep-11
Nov-11
Jan-12
Mar-12
May-12
Jul-12
Sep-12
Nov-12
Jan-13
Mar-13
May-13
Jul-13
Sep-13
Nov-13
Jan-14
Mar-14
May-14
Jul-14
Sep-14
Nov-14
0
Batches/Second
Expon. (Batches/Second)
© 2014 Plex Systems Proprietary + Confidential 7/18/2015 | Slide 4
Purpose
• This session covers some standards and guidelines Plex uses when developing
SQL code to deliver optimal performance.
• These will be mostly developer oriented.
• Basic T-SQL is a prerequisite.
© 2014 Plex Systems Proprietary + Confidential 7/18/2015 | Slide 5
Why have development
standards?
© 2014 Plex Systems Proprietary + Confidential 7/18/2015 | Slide 6
Why have development standards?
“The moral [programming] virtues, then, are engendered in us neither by
nor contrary to nature…their full development in us is due to habit.
Anything that we have to learn to do we learn by the actual doing of it.
Men will become good builders as a result of building well and bad ones
as a result of building badly. So it is a matter of no little importance what
sorts of habits we form from the earliest age – it makes a vast difference,
or rather, all the difference in the world.”
-- Aristotle
© 2014 Plex Systems Proprietary + Confidential 7/18/2015 | Slide 7
Why have development standards?
Motivations for Source Code Standards
1. System Performance
2. Technical Issues (caching, security, etc)
3. Consistency
4. Reusability / Structure
5. Maintainability / Cross-Training
6. Readability / Understandability
7. Error Handling / Fault Tolerance
8. Simplicity (versus complexity)
9. Speed/ease of typing
10. Normal Industry practices
11. Consistency with outside standards/guidelines (W3, ECMA, XHTML)
© 2014 Plex Systems Proprietary + Confidential 7/18/2015 | Slide 8
*** It Depends
© 2014 Plex Systems Proprietary + Confidential 7/18/2015 | Slide 9
Plex Standards
1.
Avoid ‘*’ when returning data. Use ‘*’ with EXISTS.
© 2014 Plex Systems Proprietary + Confidential 7/18/2015 | Slide 10
Do Not Use ‘*’
• Returning unnecessary columns creates additional work at every step of the
query.
• More page reads
• Larger streams of data at each operation in the execution.
• Increased network traffic
• This leads to sub-optimal execution plans.
• Good index strategies are much more difficult.
© 2014 Plex Systems Proprietary + Confidential 7/18/2015 | Slide 11
Use ‘*’ For EXISTS Clauses
EXISTS
(
SELECT *
FROM dbo.Table
…
)
In this case, we are giving freedom to the optimizer in terms of index choices.
© 2014 Plex Systems Proprietary + Confidential 7/18/2015 | Slide 12
Plex Standards
2.
Set NOCOUNT to “on”.
© 2014 Plex Systems Proprietary + Confidential 7/18/2015 | Slide 13
NOCOUNT Option
SET NOCOUNT ON; is required to be the first statement in all stored procedures.
This suppresses the DONE_IN_PROC messages from being sent back to the
client.
In SQL Server management studio, this is seen in the messages pane:
(1 row(s) affected)
© 2014 Plex Systems Proprietary + Confidential 7/18/2015 | Slide 14
Plex Standards
3.
Don’t include the PRINT command in production code.
© 2014 Plex Systems Proprietary + Confidential 7/18/2015 | Slide 15
PRINT Command
The data returned to the client from a PRINT command can prevent the desired
timeout behavior.
© 2014 Plex Systems Proprietary + Confidential 7/18/2015 | Slide 16
Plex Standards
4.
Qualify object references with owner.
© 2014 Plex Systems Proprietary + Confidential 7/18/2015 | Slide 17
Specify Object Owner
Include the schema the object belongs to when referencing an object.
dbo.Table
This eliminates any additional lookups to find the object. From BOL:
Note
When database objects are referenced by using a one-part name, SQL Server
first looks in the user's default schema. If the object is not found there, SQL
Server looks next in the dbo schema. If the object is not in the dbo schema, an
error is returned.
(http://msdn.microsoft.com/en-us/library/bb669061%28v=vs.110%29.aspx)
© 2014 Plex Systems Proprietary + Confidential 7/18/2015 | Slide 18
Specify Object Owner
Demo
© 2014 Plex Systems Proprietary + Confidential 7/18/2015 | Slide 19
Plex Standards
5.
Don’t use the “sp_” prefix on stored procedures.
© 2014 Plex Systems Proprietary + Confidential 7/18/2015 | Slide 20
Don’t Use the “sp_” Prefix on Stored Procedures
Microsoft suggests this as well:
It is strongly recommended that you do not create any stored procedures
using sp_ as a prefix. SQL Server always looks for a stored procedure beginning
with sp_ in this order:
1. The stored procedure in the master database.
2. The stored procedure based on any qualifiers provided (database name or
owner).
3. The stored procedure using dbo as the owner, if one is not specified.
(http://technet.microsoft.com/en-us/library/aa214379%28v=sql.80%29.aspx)
© 2014 Plex Systems Proprietary + Confidential 7/18/2015 | Slide 21
Plex Standards
6.
Avoid using scalar valued or multi-statement table valued UDFs.
© 2014 Plex Systems Proprietary + Confidential 7/18/2015 | Slide 22
User Defined Functions (UDFs)
Functions in SQL Server come in three flavors:
• Scalar valued UDFs
• Multi-statement table valued UDFs
• Single-statement table valued UDFs.
Encapsulation is great.
Performance is not so great.
© 2014 Plex Systems Proprietary + Confidential 7/18/2015 | Slide 23
User Defined Functions (UDFs)
Demo
© 2014 Plex Systems Proprietary + Confidential 7/18/2015 | Slide 24
Plex Standards
7.
Parameterize D-SQL and include a source marker.
© 2014 Plex Systems Proprietary + Confidential 7/18/2015 | Slide 25
Dynamic SQL
• Proper parameterization of D-SQL prevent unnecessary compilations and filling
the plan cache with worthless plans.
• The source marker allows for better analysis of executions or execution plans
built from dynamic SQL.
© 2014 Plex Systems Proprietary + Confidential 7/18/2015 | Slide 26
Dynamic SQL
Demo
© 2014 Plex Systems Proprietary + Confidential 7/18/2015 | Slide 27
Plex Standards
8.
Terminate trigger execution when no rows are modified.
© 2014 Plex Systems Proprietary + Confidential 7/18/2015 | Slide 28
Terminate Trigger Execution
Triggers will still execute when there are 0 rows yielded by the DML statement.
IF @@ROWCOUNT = 0 RETURN;
SET NOCOUNT ON;
© 2014 Plex Systems Proprietary + Confidential 7/18/2015 | Slide 29
Plex Standards
9.
Move LOB columns to a side table.
© 2014 Plex Systems Proprietary + Confidential 7/18/2015 | Slide 30
LOB Columns
• By separating the LOB data from the rest of the data, we keep page density
high on the main table.
• Page splits on the main table can be reduced.
• The impact on ONLINE index operations is reduced.
© 2014 Plex Systems Proprietary + Confidential 7/18/2015 | Slide 31
Plex Standards
10. Specify a fill factor.
© 2014 Plex Systems Proprietary + Confidential 7/18/2015 | Slide 32
Use an Explicit Fill Factor
The goal is to reduce page splits and not waste a lot of space.
Plex bases this standard around the size of the table.
• Bigger tables -> Records are getting inserted quickly.
• Smaller tables -> Records are getting inserted less quickly.
If row count is < 1,000,000 then use fill factor of 95.
Else use fill factor of 90.
© 2014 Plex Systems Proprietary + Confidential 7/18/2015 | Slide 33
Plex Standards
11. If temp storage is needed, use a table variable for small data sets and temp
tables for larger ones.
© 2014 Plex Systems Proprietary + Confidential 7/18/2015 | Slide 34
Table Variable vs. Temp Table
The best type of temporary storage depends on the size of the data.
If less then 50 rows, a table variable will perform well.
Else, a temporary table should be used.
Plex customers will sometimes find “unique” ways of using the system. When in
doubt, use a temporary table.
© 2014 Plex Systems Proprietary + Confidential 7/18/2015 | Slide 35
Plex Standards
12. Allow dirty reads.
© 2014 Plex Systems Proprietary + Confidential 7/18/2015 | Slide 36
Transaction Isolation Level
SET
SET
SET
SET
SET
TRANSACTION
TRANSACTION
TRANSACTION
TRANSACTION
TRANSACTION
ISOLATION
ISOLATION
ISOLATION
ISOLATION
ISOLATION
LEVEL
LEVEL
LEVEL
LEVEL
LEVEL
READ UNCOMMITTED;
READ COMMITTED;
REPEATABLE READ;
SNAPSHOT;
SERIALIZABLE;
© 2014 Plex Systems Proprietary + Confidential 7/18/2015 | Slide 37
Transaction Isolation Level
We require “Read Uncommitted” (Dirty reads)
• To deliver the necessary throughput with our level of concurrency, we need to
allow dirty reads.
• This does create the possibility for erroneous results.
© 2014 Plex Systems Proprietary + Confidential 7/18/2015 | Slide 38
Plex Standards
13. Use stored procedures.
© 2014 Plex Systems Proprietary + Confidential 7/18/2015 | Slide 39
Data Access Methods – Factors to Weigh and Consider
Method
Does this
method
perform well?
Is the
application
agnostic to
schema?
Is code and
logic
centralized?
Is this simple
to maintain?
In-Line SQL/
Ad-Hoc SQL




Object
Relational
Mapping


- Framework
- Schema

Stored
Procedures



?
© 2014 Plex Systems Proprietary + Confidential 7/18/2015 | Slide 40
Plex Standards - Recap
Avoid ‘*’ when returning data. Use ‘*’ with EXISTS.
Set NOCOUNT to “on”.
Don’t include the PRINT command in production code.
Qualify object references with owner.
Don’t use the “sp_” prefix on stored procedures.
Avoid using scalar valued or multi-statement table valued UDFs.
Parameterize D-SQL and include a source marker.
Terminate trigger execution when no rows are modified.
Move LOB columns to a side table.
Specify a fill factor.
If temp storage is needed, use a table variable for small data sets and temp
tables for larger ones.
12. Allow dirty reads.
13. Use stored procedures.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
© 2014 Plex Systems Proprietary + Confidential 7/18/2015 | Slide 41
© 2014 Plex Systems Proprietary + Confidential 7/18/2015 | Slide 42
Questions?
Answer: “It depends, <insert
answer here>.”