Transcript Folie 1 - SQLBits

TOP10 DEV SKILLS TO
MAKE YOUR DBA HAPPY
Kevin Kline
•
•
•
•
Director of Engineering Services, SQL Sentry
SQL Server MVP since 2004
Twitter, FB, LI: KEKline
Blog: http://KevinEKline.com, http://ForITPros.com
Agenda
Happy & Helpful DBA!
Tools
Techniques
Process,
Patterns &
Practices
SQLSentry.net
#1. Tools: Everybody’s Best Friend
• Books Online
• Regular updates available
• Microsoft® SQL Server™ Management Studio (SSMS)
•
•
•
•
Query plans
Object scripting
Quick and dirty data browsing
Good source control integration
SQLSentry.net
#2. Execution Plans and Traces
• SQL Server processes a query through an execution plan aka a
query plan
• Learn how to compare in SSMS
• Learn how to watch it happen with SQL Profiler
• Query performance may be misleading
• Should evaluate on comparable
data sizes
• Different CPU, Disk, caching, etc.
• Evaluate the query “Cost”
SQLSentry.net
TECHNIQUES
SQLSentry.net
#3. Test Harness & Testing
• Clear your caches
• Measure:
• Total elapsed time
• Individual statement time
• IO load
• Facilitates automation of execution plans
• Get to know the DMV sys.dm_exec_query_stats
SQLSentry.net
#4. Query Performance Tips
• Indexes! Statistics!
• WHERE clauses:
• Don’t put a function around an indexed column
• Can ensure or prevent index usage
• Use EXISTS to check for existence
• Be careful:
• NOT IN clauses
• Temp variables
• User-defined functions
SQLSentry.net
#5. Sets Versus Rows
• Microsoft® Visual C#®, Visual Basic®, Visual C++®,
etc. are procedural languages
• Tell the computer what to do
• SQL is a declarative language
• Tell the computer what you want
• Learn to think in terms of sets:
• One pass through a table
• Let the computer process joins and filters
• Loops and cursors for specific use-cases only
SQLSentry.net
#6. Connecting to SQL Server
• Use the SqlClient namespace in Microsoft® .NET
• Use Connection Pooling
• On by default in .NET
• Set Application Name in connection string
• Helps troubleshooting and monitoring
• Slightly degrades connection pooling
• SqlDataReaders are much faster than DataTables
• Update through stored procedures or SQL statements
SQLSentry.net
Example Code to Set the App Name
SqlConnectionStringBuilder csb = new
SqlConnectionStringBuilder();
csb.DataSource = “L40\YUKON”;
csb.IntegratedSecurity = true;
csb.InitialCatalog = "AdventureWorks";
csb.ApplicationName = "MyDemoApp";
string connectionString = csb.ToString();
SqlConnection conn = new SqlConnection();
conn.ConnectionString = @“Data Source=L40\YUKON;
Initial Catalog=AdventureWorks;
Integrated Security=True;
Application Name=MyDemoApp”;
SQLSentry.net
#7. Handling Nulls
• NULLs = ?
• ANSI SQL-92 requires any comparison to a
NULL to fail (i.e. false)
• This is SET ANSI_NULLS ON default setting
• NULL = NULL returns false
• NULL < > NULL returns false
How do I know
when I don’t know
what I know,
y’know?
• ANSI_NULLS OFF
• WHERE NULL = NULL returns true
SQLSentry.net
Handling Nulls
• Use
• WHERE ColumnName IS NULL
• Avoid
• WHERE ColumnName = NULL
• WHERE Col=COALESCE(@Var, Col)
• WHERE (@Var IS NULL OR
Column=@var)
SQLSentry.net
Nulls on the Client
• Basic types (int, string, etc.) don’t handle Null
• SqlDataReader
• IsDBNull tests whether a column is null
• DataColumn
• AllowDBNull property
• System.DBNull.Value
• Populate SqlParameters
• Nullable types (int?, string?, etc.) do handle Null
• Really just Nullable<int>
• Any type can be declared nullable
SQLSentry.net
Nullable Types
• Classes (types) that support Null
string? s;
Console.WriteLine(s.HasValue.ToString());
s = “Test”;
string? x = null;
• Use IsDBNull to check database for NULL
SQLSentry.net
#8. Transactions and Error Handling
• Ability to consume T-SQL errors
• Can nest TRY…CATCH blocks
• Used in the CATCH block
• Preventing Errors
• Use EXISTS() to check for rows
• Use a return code to signify failure
SQLSentry.net
Client-Side Error Handling
• Catch a SqlException
• Check the Errors collection for multiple errors
try
{
SqlDataReader r = cmd.ExecuteReader();
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
Console.WriteLine(ex.Errors.Count.ToString());
}
SQLSentry.net
Transactions
• COMMIT TRAN matches BEGIN TRAN
• ROLLBACK TRAN cancels ALL transactions
• Always BEGIN, COMMIT, and ROLLBACK
transactions at a consistent level
• Always test @@TRANCOUNT prior to COMMIT or
ROLLBACK
SQLSentry.net
Client-Side Transactions
SqlConnection conn = new SqlConnection(“…") ;
conn.Open();
SqlTransaction trans = conn.BeginTransaction();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.Transaction = trans;
try
{
// do some stuff
trans.Commit();
}
catch (SqlException)
{
trans.Rollback();
}
SQLSentry.net
PATTERNS & PRACTICES
SQLSentry.net
#9. Security is NOT an Afterthought
• SQL Injection is ________________________________.
• Plan ahead of time to minimize issues:
• Ensure the least privileges principle for applications running
on your servers
• How much surface area do your servers expose?
• Who has access to your servers?
• How do you find out the who, what, and when of a breach?
SQLSentry.net
#10. Change Management is RISK
Management
• Understand what the job entails and what makes a DBA successful!
• Change control is important! Without it, DBAs face:
• Changes that leave things worse than they started
• Piecemeal rollbacks that cripple applications
• Inconsistent support across applications and servers
• Proper change management means:
• Processed by a “change management board” composed of all key
stakeholders
• Performed at pre-planned times and within a defined time limit
• Change is tested and verified to have no effect or positive effect on
production environment
• Changes are isolated, atomic, and reversible
SQLSentry.net
21
Additional Resources
• Plan Explorer Free: http://sqlsentry.net/plan-explorer/sqlserver-query-view.asp
• Twitter and #SQLHelp
• SQLCAT.com
• SQLSkills.com
• Community Sites:
•
•
•
•
SQLPASS.org
SQLServerCentral.com
SQLBlog.com
SQLServerFAQ.com
SQLSentry.net
SUMMARY
SQLSentry.net
Q&A
•
•
•
•
Send questions to me at: [email protected]
Twitter, Facebook, LinkedIn at KEKline
Slides at http://KevinEKline.com/Slides/
IT Leadership content at http://ForITPros.com
• THANK YOU!
SQLSentry.net