Common SQL Performance Issues

SQL Performance
1.0x

Common SQL Performance Issues

Created 3 years ago

Duration 0:00:00
lesson view count 52
Select the file type you wish to download
Slide Content
  1. Common SQL Performance  Issues

    Slide 1 - Common SQL Performance Issues

    • And how to Avoid or fix them
  2. Common SQL Performance Issues

    Slide 2 - Common SQL Performance Issues

    • End users writing poor queries
    • Power users writing poor queries
    • Junior developers writing poor queries
    • Senior developers writing poor queries
    • Software architects writing poor queries.
  3. Query related issues

    Slide 3 - Query related issues

    • Transact-SQL Performance Issueshttp://msdn.microsoft.com/en-us/library/dd172117(v=vs.100).aspx
    • SR0004: Avoid using columns that do not have indexes as test expressions in IN predicateshttp://msdn.microsoft.com/en-us/library/dd193249(v=vs.100).aspxMissing indexes are the most common problem.
    • SR0005: Avoid using patterns that start with “%” in LIKE predicateshttp://msdn.microsoft.com/en-us/library/dd193273(v=vs.100).aspxAvoid these or use full-text indexes for these fields.
    • SR0006: Move a column reference to one side of a comparison operator to use a column indexhttp://msdn.microsoft.com/en-us/library/dd193264(v=vs.100).aspxDo not do calculations on table column values if you can help it to avoid table scans
    • SR0007: Use ISNULL(column, default value) on nullable columns in expressionshttp://msdn.microsoft.com/en-us/library/dd193267(v=vs.100).aspxAlways provide a default in a comparison on a nullable field, unless specifically checking for NULL.
    • SR0015: Extract deterministic function calls from WHERE predicateshttp://msdn.microsoft.com/en-us/library/dd193285(v=vs.100).aspxBasically calculate your variables before adding them to the WHERE clause. Think in sets and remove extra calculations from each loop of the set. If you only run it once, then build your set it will be a lot faster.
  4. Query related issues

    Slide 4 - Query related issues

    • Chapter 14 — Improving SQL Server Performance (outdated but still good to know)http://msdn.microsoft.com/en-us/library/ff647793.aspx
    • SQL Magazine article on perf tuninghttp://sqlmag.com/database-performance-tuning/troubleshooting-common-sql-server-problems
  5. SQLCop

    Slide 5 - SQLCop

    • SQLCop is a little test app. I have most of the tests as sql files so you can run them in a CI build, or you can use the free tool from here:http://sqlcop.lessthandot.com/
  6. Entity Framework Issues

    Slide 6 - Entity Framework Issues

    • Troubleshooting Performance Problems in Entity Framework Applicationshttp://sqlmag.com/entity-framework/troubleshooting-performance-problems-entity-framework-applications
    • Performance Considerations for Entity Framework 4, 5, and 6http://msdn.microsoft.com/en-us/data/hh949853.aspx
    • EF6 Performance Issueshttp://blogs.msdn.com/b/adonet/archive/2013/10/31/ef6-performance-issues.aspx
    • Use .AsNoTracking() when you are not going to update the object.http://stackoverflow.com/questions/7403149/entity-framework-performance-issue
  7. The Top 5 Most Common SQL Server Performance Problems

    Slide 7 - The Top 5 Most Common SQL Server Performance Problems

    • Memory Pressure
    • Query TimeoutMisunderstood "Timeout expired prior to the completion of..."
    • CXPACKET Wait Type
    • Expecting Auto Update Statistics to Keep Statistics Updated
    • TempDb PAGELATCH Contention
    • http://blogs.sqlsentry.com/jasonhall/my-perspective-the-top-5-most-common-sql-server-performance-problems/
    • http://seniordba.wordpress.com/2014/07/23/5-most-common-sql-server-performance-problems/
  8. How To…

    Slide 8 - How To…

    • How to troubleshoot SQL Server performance issues with simple tools (Part 1: How to collect a detailed Perfmon trace)http://blogs.msdn.com/b/john_daskalakis/archive/2013/10/07/how-to-troubleshoot-sql-server-performance-issues-with-simple-tools-part-1-how-to-collect-a-detailed-perfmon-trace.aspx
    • How to use SQL tests in your builds with TeamCity or similar CI toolshttps://www.simple-talk.com/sql/sql-tools/using-sql-test-database-unit-testing-with-teamcity-continuous-integration/