Download to read offline
Uploaded byRahul Singh
Database Performance Tuning| Rahul Gulab Singh
This document discusses database performance tuning. It covers the physical structure of databases, phases of query processing and query plans, indexes and maintenance, monitoring queries, performance issues and resolutions. Specific topics include the purpose of data and log files, separating files, query execution steps, index types (clustered, non-clustered, covering), index maintenance, monitoring tools like Profiler and DMVs, and resolving issues like blocking, deadlocks and high CPU usage. Demos are provided of indexes, monitoring features, and troubleshooting techniques.
Database Performance Tuning| Rahul Gulab Singh
- 1.
- 2. Agenda Physical structureof databases Phases of Query Processing and Query Plan Indexes and Maintenance How to monitor and track queries in the background Sql Server Profiler , Activity Monitor , Sql Server Reports and Extended Events Common Performance Issues and Resolutions
- 3. Physical Structure ofDatabase Demo What is the purpose of Log File and Data File
- 4. Purpose of dataand Log file
- 5. Physical Structure Performance TuningSteps. Separate Data File from Log file Set Initial Size for Data file and log file Tempdb on separate drive
- 6. Phases of QueryProcessing and Query Plan What Happens when Query is executed Step1 – Checks Syntax of the Query Step2 – Check if a Plan is already created Step3 –Creates Plan if not created based on Data , Indexes , Uniqueness , Statistics Step5- Execute the Plan Benefits of Procedure over Adhoc Queries
- 8. Query plan Demo Demo Sys.dm_exec_cache_plan Sys.dm_exec_query_plan() Sys.dm_exec_sql_text()
- 9. Indexes Sql ServerIndex Method Clustered Index --- Scan and Seek Non-Clustered Indexes– Scan and Seek Covering Indexes Filtered Indexes Full text Indexes Spatial Indexes Column Store Indexes
- 10.
- 11.
- 12.
- 13.
- 14. Other Indexes FilteredIndexes Spatial Indexes XML Indexes Fulltext Indexes
- 15. Index Maintenance –FillFactor
- 16.
- 17.
- 18. Index Maintenance FillFactor , Page Split , Linkages moved Alter Rebuild and Re-organize Commands Sys.dm_db_index_physical_stats Sys.dm_db_index_usage_stats Sys.dm_db_missing_index_ Statistics and maintenance Update Statistics tblname with fullscan
- 19. What has beenCovered till Now Physical Structure Query processing Steps , Query Plan Indexes Analysis , Maintenance
- 20. How to monitorand track queries in the background Dynamic Management Views and Functions Information about DB server Various Dimensions namely Hardware , OS , DB Internals various Component s Sp_who2 and Sp_who4 Common DMV used.
- 21. DEMO Sys.dm_exec_query_stats Sys.dm_exec_requests Sys.dm_exec_Sqltext() Plan_Handle and Sql_handle
- 22. Sql Server Profiler When to use Profiler Precautions in using Profiler How to import Profiler data in a Sql server table Demo
- 23. Monitoring Tools ActivityMonitor Demo SQL Server Reports Performance Counters Demo SQl Server Extended Events
- 24. Common Performance Issues andResolutions CPU utilization 100% for DB server Connect to DB server Increase the Timeout to 100 seconds Run Sys.dm_exec_query_stats with sys.dm_exec_sql_text Run Update Statistics with full scan on current wait tables
- 25. Common Performance Issues andResolutions Severe Blocking Happening on server Run Sp_who4 Check the Blocker Head Kill the Blocker Head Still issue persist Run Update Stats
- 26. Common Performance Issues andResolutions Frequent Deadlocks occurring on server Track Deadlocks through extended events Track Deadlocks through DBCC Traceon Steps to reduce Deadlocks With(nolock) , Make transactions Shorter
- 27. Common Performance Issues andResolutions Frequent Performance issues occurring Tried all aspects of indexes and maintenance and still no luck Only Solution Archiving HDFC Site Example
- 28. What we haveCovered Physical structure of databases Phases of Query Processing and Query Plan Indexes and Maintenance How to monitor and track queries in the background Sql Server Profiler , Activity Monitor , Sql Server Reports and Extended Events Common Performance Issues and Resolutions
- 29. Books and Resources Sql server Internals by Kimberlay Query Performance Distilled by Grant Fritchey Inside Microsoft sql server T-sql series by Itzik Ben-Gan Sql server Administrator by Wrox Series