1 / 17

The Essentials: DMV’s and T-SQL for the DBA

The Essentials: DMV’s and T-SQL for the DBA. About Me. Over a decade with SQL Server Employee, Contractor, Consultant Principal Consultant @ DataRealized Blog sqlPerspectives.wordpress.com DataRealized.wordpress.com Speaker Dev Connections, SSWUG v-Conference, User Groups.

derex
Download Presentation

The Essentials: DMV’s and T-SQL for the DBA

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. The Essentials: DMV’s and T-SQL for the DBA Rocky Mountain Tech Tri-Fecta

  2. About Me • Over a decade with SQL Server • Employee, Contractor, Consultant • Principal Consultant @DataRealized • Blog • sqlPerspectives.wordpress.com • DataRealized.wordpress.com • Speaker • Dev Connections, SSWUG v-Conference, User Groups Rocky Mountain Tech Tri-Fecta

  3. The Essentials: DMV’s and T-SQL for the DBA • Agenda • DMV’s • Categories • Dive in and try them out • T-SQL for internals / DMV’s • Utilizing DMV’s • How DMV’s can work for you • DDL Triggers • Plan Guides Rocky Mountain Tech Tri-Fecta

  4. DMV’s • DMV • Definition • Dynamic Management Views (and functions) • Return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.* MSFT – 2008 BOL Rocky Mountain Tech Tri-Fecta

  5. DMV Categories • Change Data Capture Related Dynamic Management Views • Query Notifications Related Dynamic Management Views • Common Language Runtime Related Dynamic Management Views • Replication Related Dynamic Management Views • Database Mirroring Related Dynamic Management Views • Resource Governor Dynamic Management Views • Database Related Dynamic Management Views • Service Broker Related Dynamic Management Views • Execution Related Dynamic Management Views and Functions • SQL Server Extended Events Dynamic Management Views • Full-Text Search Related Dynamic Management Views • SQL Server Operating System Related Dynamic Management Views • Index Related Dynamic Management Views and Functions • Transaction Related Dynamic Management Views and Functions • I/O Related Dynamic Management Views and Functions • Security Related Dynamic Management Views • Object Related Dynamic Management Views and Functions Rocky Mountain Tech Tri-Fecta

  6. DMV’s • Challenge • Increasingly large environments • Increasingly complex environments • Hardware / Database consolidation • SQL Server “Sprawl” • Toolset knowledge needed… increasing, quickly • Revamped DTS to SSIS • Revamped SSRS • Improved SSAS • CLR, FileStream, CTE’s, Spatial Datatypes, compression, ABC’s, XYZ’s Rocky Mountain Tech Tri-Fecta

  7. DMV’s • DMV - The toolset you can’t live without! • Two scopes: • Server & Database • Live in the sys Schema • Queries for nearly everything • Tuning, Performance related issues, Backup history, Index analysis, Memory, Disk and CPU analysis • SQL Server 2008 • Additional 46 DMV’s Rocky Mountain Tech Tri-Fecta

  8. Key DMV’s • Sys.dm_os_sys_info • General Server Info • Sys.dm_exec_requests • Sp_who(2) +++ • Sys.dm_db_index_operational_stats • Sp_lock +++ • Sys.dm_db_index_usage_stats • Base DMV for Index Analysis and Recommendations • Msdb.dbo.backupset • Base table for backup history Rocky Mountain Tech Tri-Fecta

  9. Key DMV’s • Sys.dm_exec_cached_plans • Shows query plans that are cached by SQL Server • For query plans, this DMV maps to the syscacheobjects table in SQL Server 2000 • Sys.dm_exec_query_stats • Performance statistics for cached query plans • Top 10 … • Sys.dm_io_virtual_file_stats • I/O stats for data and log files • Sys.dm_os_memory_pools • Monitors Cache memory Rocky Mountain Tech Tri-Fecta

  10. Key DMV’s • Sys.dm_exec_sql_text • Returns the text of the SQL batch that is identified by the specified sql_handle. • This table-valued function replaces the system function fn_get_sql • Obtained from: • Sys.dm_exec_query_stats • Sys.dm_exec_requests • Sys.dm_exec_cursors • Sys.dm_exec_xml_handles • Sys.dm_exec_query_memory_grants • Sys.dm_exec_connections Rocky Mountain Tech Tri-Fecta

  11. DMV Demo • Putting it together… • Physical Environment • CPU, Memory, SQL Server start time • Backup History • Server, Database, Size, Duration etc… • SP_who…? • Tons of great information • Indexing • Usage, Analysis, Good, Bad… • T-SQL statements • Good or Bad, how can you tell? Rocky Mountain Tech Tri-Fecta

  12. DMV Demo • Putting it together… • Cache • Adhoc / dynamic • Re-using of cache plans? • Memory breakdown of all compiled objects • Set Options • Which ones? • Top 10 • Physical Reads • Logical Reads Rocky Mountain Tech Tri-Fecta

  13. DDL Triggers • DDL Triggers • GREAT for auditing changes • What changed? • Who changed it? • When did it change? • Quick Demo • *note the Create_Statistics criteria in the Create Trigger Rocky Mountain Tech Tri-Fecta

  14. Plan Guides Demo • Putting it together… • Cache • Things to look for : • Determine if the adhoc or dynamic statements are generating plans that are re-used, or in this case, not being re-used. select Count (*), refcounts, usecounts, objtype, left(text,50) from sys.dm_Exec_cached_plans cross apply sys.dm_exec_sql_text(plan_handle) where cacheobjtype = 'Compiled Plan’ and objtype = 'Adhoc' and usecounts = 1 group by refcounts, usecounts, objtype, left(text,50) order by 1 desc Count RefcountUsecountobjtypesql – first 50 characters…. 1670 2 1 Adhoc Select DISTINCT TOP 100 W………….. 1603 2 1 Adhoc SELECT TOP 1000 W………. 1538 2 1 Adhoc select distinct top 100 v.W……… • Means that there are 4811 plans for three nearly identical statements • Significant memory required for caching plans • Finite amount of cache for plans Rocky Mountain Tech Tri-Fecta

  15. The Essentials – DMV’s and T-SQL for the DBA • Summary • DMV’s • Meta-Data for your enterprise • Help yourself, your team and your management • DDL Trigger • Consistency, consistency, consistency • Great audit tool • Plan Guides • DMV Meta-Data drives exact prescription • HAVE FUN! Rocky Mountain Tech Tri-Fecta

  16. The Essentials – DMV’s and T-SQL for the DBA • Resources • Rocky Mountain Tri-Fecta! • Local User Group • SQL Server • Books On Line • SQL Server 2008 Performance Studio • https://msevents.microsoft.com/cui/WebCastEventDetails.aspx?culture=en-US&EventID=1032349947&CountryCode=US • http://www.microsoft.com/midsizebusiness/business-goals/business-operations/data-management-tools.mspx • http://www.codeproject.com/KB/database/Dynamic_Management_Views.aspx • http://msdn.microsoft.com/en-us/library/dd939169.aspx Rocky Mountain Tech Tri-Fecta

  17. The Essentials – DMV’s and T-SQL for the DBA • Thank you! • Questions • Contact • @DataRealized • Jeremy@DataRealized.com • DataRealized.com http://datarealized.wordpress.com • http://sqlPerspectives.wordpress.com Rocky Mountain Tech Tri-Fecta

More Related