1 / 22

Performance in SQL Server

Performance in SQL Server. Bulgarian SQL & BI User Group meeting 18 April 2007, Interpred. Our community. www.bgsqlgroup.com Registration, discussions.. Using the site MS Days – preferential invitation for all members of SQL & BI User Group! To register for the event:

anisa
Download Presentation

Performance in SQL Server

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. Performance in SQL Server Bulgarian SQL & BI User Group meeting 18 April 2007, Interpred

  2. Our community • www.bgsqlgroup.com • Registration, discussions.. Using the site • MS Days – preferential invitation for all members of SQL & BI User Group! To register for the event: • SQL Topics on MS Days: • SQL Roadmap • Microsoft BI FrontEnd • LINQ for developers • HA for developers

  3. Topics to discuss • Monitoring Performance problems • Indexes, fragmentation, partitioning and optimizations deep inside • Dynamic SQL stuff

  4. Monitoring Performance Problems in SQL Server 2005 How to monitor and see what and where is the problem How to react

  5. 3 symptoms for slowing down • Resource bottleneck • CPU • Memory • IO Subsystem • Tempdb bottleneck • Queries (workload) • Database design (physical)

  6. Monitoring and troubleshootingGeneral strategy • Know baseline • Drill down to particular resource bottleneck • Try to find out the mechanics of particular resource usage • Watch for changes that may have cause the problem • Possible steps for resoulution

  7. CPU bottleneck • SYSMon • DMVs: • sys.dm_os_schedulers • sys.dm_exec_query_stats • Potential issues • Excessive compilation/recompilation • Inefficient query plans • Intra-query parallelism • Poor cursor usage

  8. demo

  9. Excessive compl/recompl • Monitor set option changes, avoid them in stored procedures • Consider temp tables vs. table variables, KEEP PLAN hint • KEEPFIXED PLAN hint • Consider automatic statistics update OFF vs. ON • Use qualified object names (dbo.TableA vs. TableA) • Do not mix DDL/DML statements • Use DTA: create missing indexes to improve compile time • Consider RECOMPILE hint inside stored procedure

  10. Inefficient query plans • Tune with DTA • Check cardinality • Use hints for this query only if you cannot rewrite it: • Optimize for • Use plan

  11. Intra-query parallelism • Use DTA to opt • Cardinality

  12. Memory botlenecks • Memory pressures: • Internal/external • Physical/virtual • Tools • Memory related DMVs • DBCC MEMORYSTATUS command • Performance counters: performance monitor or DMV for SQL Server specific object • Task Manager • Event viewer: application log, system log

  13. Memory errors • 701 - There is insufficient system memory to run this query • 802 - There is insufficient memory available in the buffer pool • 8628 - A time out occurred while waiting to optimize the query. Rerun the query • 8645 - A time out occurred while waiting for memory resources to execute the query. Rerun the query • 8651 - Could not perform the requested operation because the minimum query memory is not available. Decrease the configured value for the 'min memory per query' server configuration option.

  14. IO Subsystem bottlenecks • SM Counters - PhysicalDisk Object: Avg. Disk Queue Length, Avg. Disk Reads/Sec, Avg. Disk Writes/SecPhysical Disk: %Disk Time • Compare with memory counters • Find waits and corresponding databases • Find IO Intensive execution plans • Run DTA to find out missing indexes

  15. Tempdb • TempDB use: • Explicitly created user objects • SQL Server created internal objects • Features using version store: MARS, online index, triggers and snapshot based isolation levels • Problems: • Running out of TempDB space • Bottleneck in system tables due to excessive DDL operations • Allocation contention

  16. Tempdb tips • TempDB capacity planning • Account for new features that use TempDB • Preallocate space for TempDB • Many TempDB files of equal size (= CPUs) to reduce contention • User objects: identify and eliminate “offenders” if possible • Version store: • Eliminate longest transactions • Account for size = 2 * [version store data generated per min] * [longest runtime of the transaction] • Excessive DDL: • Consider where temp tables are created • Consider query plans that create many internal temp objects and verify if they are efficiently written, rewrite as needed

  17. Start from WAITS • Analyze WAITS at instance level • Correlate with queues • Determine a course of action • Drill down to database/file level • Drill down to process level • Tune indexes/queries

  18. Demo scenario

  19. Blocking • Sys.dm_tran_locks • Monitor: • Execute Sp_configure ‘blocked process threshold’, 200   • Reconfigure with override • If using SQL Trace, use sp_trace_setevent and event_id=137 • In SQL Server Profiler - Blocked Process Report event class • sys.dm_db_index_operational_stats – blocking per object

  20. Index usage and optimizations • Index fragmentation • Index reorganize/rebuild • Partitioning for optimization of large tables • Monitoring: • Sys.dm_index_usage_stats

More Related