1 Looking at Indexes from Multiple Perspectives#604 | Madison | April 8, 2017

2 Who is this guy? …and why listen to him? John Eisbrener@johnedba DBA: Default Blame Assignee/Acceptor DBA for over 10 years MSSQL, Oracle, Greenplum, Postgres Owner/Principal Consultant of a boutique consulting firm, DB Atlas I have the microphone John Eisbrener – DB Atlas

3 Agenda Index Basics Index Advanced Topics Index Design ConsiderationsIndex Misconceptions Additional Resources Supplemental Queries Questions John Eisbrener – DB Atlas

4 Index Basics – Section OverviewWhat is an index? Difference between Rowstore and Columnstore Indexes B+ Trees Heap Tables vs. Clustered Tables Clustered Index vs. Nonclustered Index (Rowstore) Primary Key vs. Clustered Key Covering Index Clustered Index vs. Noncluster Index (Columnstore) Trivia John Eisbrener – DB Atlas

5 What is an Index? Structure that contains data or pointers to dataDesigned in such a way as to search for data efficiently Designed to perform to scale as a database grows in size The type of index determines how data is stored on disk Highly customizable Columnstore indexes were introduced in SQL Server 2012 https://technet.microsoft.com/en-us/library/jj835095(v=sql.110).aspx John Eisbrener – DB Atlas

6 Difference between Rowstore and Columnstore IndexesStored in a row-wise format mimicking table definition Compression is optional and can be either Page or Row-based Pulls all columns defined within Index Optimal for Transactional /Normalized workloads Takes advantage of Batch Mode Processing with a Dummy NCCI Stored in a column-wise format via Rowgroups Compression required, and can be default or Archive (slow) Pulls only columns needed by query Optimal for Analytical /Denormalized workloads Takes advantage of Batch Mode Processing w/o a hack https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview John Eisbrener – DB Atlas

7 Difference between Rowstore and Columnstore Indexes (continued)SELECT Col01, Col02 FROM Table Row-Wise Storage: Full Table Pulled Column-Wise Storage: Only Col01 & Col Pulled John Eisbrener – DB Atlas

8 B+ Trees Variant of a B Tree, also known as a Balanced TreeOnly Leaf Level Contains Data or Pointers to Data Designed to be very shallow and very wide Highly Scalable and Performant Only used for Rowstore Indexes or Delta Rowgroups https://en.wikipedia.org/wiki/B%2B_tree John Eisbrener – DB Atlas

9 B+ Trees (cont’d) https://en.wikipedia.org/wiki/B%2B_tree#/media/File:Bplustree.png John Eisbrener – DB Atlas

10 Heap Table vs. Clustered TableData is ordered implicitly by the order it was inserted Quick for Inserts Consumes more space when not truncated/reloaded/rebuilt Best for staging tables Data is ordered explicitly as defined by the clustered key Quick for Selects, Updates, and Deletes Reuses empty space efficiently Best for transactional tables https://technet.microsoft.com/en-us/library/cc aspx John Eisbrener – DB Atlas

11 Clustered vs. Nonclustered IndexClustered Key defines the order of the data within the table Data is stored at the leaf level of the index Index is the data Can enforce Uniqueness Does not require a Primary Key Index Key defines order of data within the index Pointers to the data are stored at the leaf level of the index Index is a copy of the data Can enforce Uniqueness Does not require a Primary Key https://docs.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described John Eisbrener – DB Atlas

12 Primary Key vs. Clustered KeyOne per table Can be defined for any Index Can be multiple columns Must Uniquely Identify a single record Used for Referential Integrity (RI) / FKEY relationships Determines data order only if it is also the Clustered Key One per table Can be defined for any Index Can be multiple columns Does not need to uniquely define a single record Unless defined as a PKEY or UNIQUE cannot be used for RI Always Determines Sort Order of Data within the table https://docs.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described John Eisbrener – DB Atlas

13 Covering Index Contains all Columns referenced in a QueryNonclustered Indexes Will avoid row-identifier (RID) lookups against the heap or clustered table INCLUDE Keyword Optionally Used For Columns Not in the Predicate Contains a Copy of column(s) in addition to the Index Key Reduces Disk I/O Smaller/Narrower than Clustered Index https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-indexes-with-included-columns John Eisbrener – DB Atlas

14 Covering Index (continued)SELECT Col08 FROM TableA WHERE Col02 = 5 AND Col17 = 99 CREATE NONCLUSTERED INDEX [IX_TableA_Alpha] ON [dbo].[TableA] ( Col02, Col17, Col22 ) INCLUDE ( Col08, Col09, Col18 ) ( Col02, Col17) INCLUDE ( Col08 ) ( Col02, Col17, Col08) SELECT Col08, Col09, Col17, Col18 FROM TableA WHERE Col02 = 5 AND Col17 = 98 AND Col22 < 5 John Eisbrener – DB Atlas

15 Clustered Index vs. Noncluster Index (Columnstore)Clustered Columstore Index (CCI) Nonclustered Columnstore Index (NCCI) One Per Table Converts table data to column- wise storage format Significant Table Compression Use for Analytical Workloads such as Fact Tables Uses a Delta Rowgroup Cannot define a filter One Per Table Sits on top of Heap or Clustered (Rowstore) Index Copy of Data; uses more space Used for Hybrid Transactional /Analytical Processing (HTAP) Uses a Delta Rowgroup (2016+) Can define a filter https://blogs.sentryone.com/melissacoates/overview-columnstore-indexes-sql-server/ John Eisbrener – DB Atlas

16 Trivia Time – Which Index is Faster?Table containing 10 columns (Col01 – Col10) Clustered Index with Primary Key defined on Col01 Nonclustered Index defined on Col01 and Col02 Columns Col03 and Col04 are INCLUDEd Table contains 21.7 Million Records Execute Query SELECT Col03, Col04 FROM Table WHERE Col01 BETWEEN 1000 AND 30000 Nonclustered Covering Index is Faster (less pages to pull from disk for same data) John Eisbrener – DB Atlas

17 Index Advanced Topics – Section OverviewWhen is an Index Used? Seeks vs. Scans Range Scans Columnstore Scans Unique Constraint/Index Filtered Indexes John Eisbrener – DB Atlas

18 When is an Index Used? An index is generally used when less than 33% of a table’s records would be returned Known as the Tipping Point This percentage can be far lower for Nonclustered Indexes that are NOT COVERING Indexes Performance: Seeks > Range Scans > Index Scans > Table/Heap Scans Covering Indexes will be used first if available as they are faster than Table Scans Columnstore Indexes will be considered prior to a table scan, but will not always used by the Query engine… It depends… John Eisbrener – DB Atlas

19 Seeks vs. Scans Seek ScanOnly a subset of the Index is loaded into memory Traverses the Index starting from the root node More efficient for small result sets In most cases an entire Index is loaded into memory and traversed Traverses the index leaf nodes only More efficient for large result sets John Eisbrener – DB Atlas

20 Seeks John Eisbrener – DB Atlas

21 Scans John Eisbrener – DB Atlas

22 Range Scan John Eisbrener – DB Atlas

23 Columnstore Scans Clustered (CCI) and NonClustered (NCCI) Columnstore Indexes are only ever Scanned (No Seeks) but remain performant because: Data is Highly Compressed Larger amount of similar data/patterns when stored in column-wise manner Utilize Batch Execution Mode Ability to process up to 900 records per operation instead of row-based per normal Column Elimination Only columns referenced in query are accessed Rowgroup Elimination Conceptually similar to Partition Elimination in Partitioned Tables Data must be sorted NCCI on top of Clustered Table CCI loaded with MAXDOP=1 (but rebuilds required as order isn’t guaranteed for new data) https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-query-performance https://blogs.msdn.microsoft.com/sql_server_team/columnstore-index-performance-rowgroup-elimination/ John Eisbrener – DB Atlas

24 Columnstore Scans (continued)John Eisbrener – DB Atlas

25 Unique Constraint/IndexA Unique Constraint automatically creates a corresponding Unique Index Can be defined on multiple columns Can have multiple per table Will allow one NULL (unlike PKEYs where NULLS are not allowed) Can be referenced by a Foreign Key https://docs.microsoft.com/en-us/sql/relational-databases/tables/unique-constraints-and-check-constraints John Eisbrener – DB Atlas

26 Filtered Indexes Nonclustered Index which only covers a subset of records within a table Smaller than a traditional Nonclustered Index Less Storage Less Maintenance Best used on well-defined subsets of data Use the OPTION (RECOMPILE) hint when writing queries that will touch Filtered Indexes and INCLUDE filtered columns in Index https://msdn.microsoft.com/en-us/library/cc280372(v=sql.105).aspx John Eisbrener – DB Atlas

27 Index Design ConsiderationsFPOC Design Column Ordering Bulk Loading Scenarios Unused (Superfluous) Indexes Identifying Missing Indexes When to use Columnstore Indexes Other Design Considerations John Eisbrener – DB Atlas

28 FPOC Design Term Coined by Itzik Ben-Gan when indexing for Windowing Functions Useful method to identify which columns to specify within your index Filter – Columns in the Predicate (e.g. WHERE and ON clauses) Partition – The Partition Field(s) for any partitioned table Order – Any Field(s) within the ORDER BY clause Covering – Any remaining fields within the SELECT clause, not already included FPO columns comprise your index key Order by Most to Least Selective if possible C columns are specified within an INCLUDE clause John Eisbrener – DB Atlas

29 Column Ordering Column ordering in a Query does not matterColumn ordering within an Index Definition does matter Pick the most selective column first Selectivity: (Number of Unique Values) / (Number of Records in a Table) Queries that use the leading fields in the column definition can utilize the index for faster query performance John Eisbrener – DB Atlas

30 Column Ordering (continued)Index A: Index Key - Col01, Col02, Col03 Index B: Index Key - Col02, Col01, Col03 Statement 1: SELECT Col03 FROM Z WHERE Col01 = 1 Statement 2: SELECT Col03 FROM Z WHERE Col02 = 2 Statement 3: SELECT Col03 FROM Z WHERE Col01 = AND Col02 = 2 Statement 4: SELECT Col01 FROM X WHERE Col03 = 3 Statement 1 – Uses Index A Statement 2 – Uses Index B Statement 3 – Uses either Index A or Index B (selectivity of leading column will determine which one) Statement 4 – Will revert to a Table Scan John Eisbrener – DB Atlas

31 Bulk Loading ScenariosIndexes are automatically maintained anytime a record is Inserted, Deleted, or Updated Disable All Nonclustered, Nonunique Indexes prior to load Rebuild disabled Indexes on Table after data load to re-enable Generally results in Lower Net Operations Tipping point depends on percentage of data to import and number of existing indexes, but don’t perform if less than 25% of data is being imported Potentially Alter DB Recovery Model to Bulk-Logged to further reduce I/O as BULK INSERT is a minimally logged command Order to switch over Create TLog Backup Alter Recovery Model to Bulk-Logged Perform Bulk Load Operation(s) Alter Recovery Model to Full Use TABLOCK hint https://technet.microsoft.com/en-us/library/ms191244(v=sql.105).aspx https://technet.microsoft.com/en-us/library/ms177445(v=sql.105).aspx John Eisbrener – DB Atlas

32 Unused (Superfluous) IndexesIndexes are automatically maintained anytime a record is Inserted, Deleted, or Updated Drop/Disable under-utilized Indexes DMVs sys.dm_db_index_usage_stats sys.dm_db_partition_stats SELECT TableName = OBJECT_SCHEMA_NAME(s.OBJECT_ID, db_id()) + '.' + OBJECT_NAME(s.OBJECT_ID) , IndexName = i.NAME , READS = user_seeks + user_scans + user_lookups , WRITES = user_updates , p.ROWS , k.partition_number , IndexSizeMB = (k.used_page_count * 8) / 1024 FROM sys.dm_db_index_usage_stats s INNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.OBJECT_ID = i.OBJECT_ID INNER JOIN sys.partitions p ON p.index_id = s.index_id AND s.OBJECT_ID = p.OBJECT_ID INNER JOIN sys.dm_db_partition_stats k ON k.index_id = i.index_id AND k.OBJECT_ID = i.OBJECT_ID WHERE OBJECTPROPERTY(s.OBJECT_ID, 'IsUserTable') = 1 AND s.database_id = DB_ID() AND i.type_desc = 'nonclustered' AND i.is_primary_key = 0 AND i.is_unique_constraint = 0 AND p.ROWS > 10000 AND user_seeks + user_scans + user_lookups = 0 ORDER BY READS, WRITES DESC, ROWS DESC John Eisbrener – DB Atlas

33 Identifying Missing IndexesReview Queries Actively in Cache Review Queries that are hitting the largest Objects in Cache What Indexes can be extended to cover more queries? Run through these exercises at different times of the day/month/business cycle John Eisbrener – DB Atlas

34 When to use Columnstore IndexesTable has more than 1 million records (ideally many more) Use Clustered Columnstore Indexes (CCI) on Star/Snowflake Schemas as an alternative to SSAS cubes Use NonClustered Columnstore Indexes (NCCI) on Tables that support transactional workloads but also require Real-Time analytics Use Filtered NCCIs for Warm Transactional Data to minimize maintenance needs Ensure SET Options are configured properly https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-data-warehouse https://docs.microsoft.com/en-us/sql/relational-databases/indexes/get-started-with-columnstore-for-real-time-operational-analytics https://docs.microsoft.com/en-us/sql/t-sql/statements/create-columnstore-index-transact-sql John Eisbrener – DB Atlas

35 When to use Columnstore Indexes (continued)Use COMPRESSION_DELAY to minimize NCCI fragmentation for tables with heavy DML operations (e.g. UPDATE/DELETE/INSERT) Can specify a value up to 7 days (i.e. 10,080 minutes) Offload Analytical Queries against NCCI/CCI columns to Read-Only secondary if using AGs and running Enterprise Edition Use empty (filtered) NCCIs to allow Rowstore Indexes to take advantage of Batch-Execution mode (rarely will be needed) Frameless Aggregate Window Functions, such as: SELECT actid, tranid, val, SUM(val) OVER(PARTITION BY actid) AS acttotal FROM dbo.TransactionsDCS; Improve Query Permformance against Entity-Attribute-Value Data Model https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/03/06/real-time-operational-analytics-compression-delay-option-for-nonclustered-columnstore-index-ncci/ John Eisbrener – DB Atlas

36 Other Design ConsiderationsEnterprise Edition Considerations if not running SQL 2016 SP1 or later Utilize Page or Row compression in larger tables Take advantage of In-Memory Tables Run complex queries through the Database Tuning Advisor Test out Missing Index recommendations that are displayed within execution plan (SET SHOWPLAN_XML ON) https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-server-2016-service-pack-1-sp1-released/ John Eisbrener – DB Atlas

37 Upgrade to SQL Server 2016 SP1https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-server-2016-service-pack-1-sp1-released/ John Eisbrener – DB Atlas

38 Index Misconceptions Helpful Tools to Identify Missing IndexesIndex/Table Padding (Whitespace) Index Fragmentation Self-Managing Statistics DBCC CHECKDB using REPAIR_ALLOW_DATA_LOSS Columnstore Indexes John Eisbrener – DB Atlas

39 Misconception – Helpful Tools to Identify Missing IndexesProfiler Traces/Extended Events This information already exists in cache or the Query Store Requires additional overhead for existing functionality Missing Index DMVs (sys.dm_db_missing_index_details) Often poor recommendations Will recommend nearly-duplicate index definitions No way to identify why the recommendation is made improvement_measure field is arbitrary and does not indicate saved CPU cycles, reduced I/O, etc. Instead: Identify Missing Indexes by the methods described earlier John Eisbrener – DB Atlas

40 Misconception – Index/Table Padding (Whitespace)FILLFACTOR keyword in Index Definition Represents the Percentage each Leaf-Level Page will be filled to when creating a new page or rebuilding the index Actually Makes Indexes and Tables Larger on Disk and Memory Often set in an attempt to minimize Index/Table Fragmentation Provides no value for Tables that append data to the end of the table (e.g. PKEY is defined on an IDENTITY field) Instead: Don’t specify FILLFACTOR, leave it at the default setting of 0 https://docs.microsoft.com/en-us/sql/relational-databases/indexes/specify-fill-factor-for-an-index John Eisbrener – DB Atlas

41 Misconception – Index FragmentationFragmentation exists when indexes have pages where the logical ordering, based on the key value, does not match the physical ordering inside the data file Low Fragmentation does not represent data being stored contiguously on disk, only in a sequential order Disk Subsystems may purposely fragment the data on disk for redundancy purposes (e.g. RAIDed volumes, SANs, DAS) May only be of any concern with Locally managed disk Fragmented Indexes perform the same once in Memory as their Defragmented counterparts https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes John Eisbrener – DB Atlas

42 Misconception – Index Fragmentation (continued)Will cause more read-aheads when pulling pages from disk Read-ahead anticipates the data and index pages needed to fulfill a query execution plan and brings the pages into the buffer cache before they are actually used by the query. Will become less costly as the index stays in memory longer Rebuilding/Reorganizing of large or multiple Indexes can quickly purge your cache Instead: Rebuild Indexes based on average freespace per page for the 0- level of the index (avg_page_space_used_in_percent column in sys.dm_db_index_physical_stats) as a Rebuild/Reorg is the only way to reduce whitespace within an index Must use sys.dm_db_index_physical_stats using either DEFAULT, SAMPLED, or FULL scan level https://technet.microsoft.com/en-us/library/ms191475(v=sql.105).aspx https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql John Eisbrener – DB Atlas

43 Misconception - Self-Managing StatisticsWhen Auto-Update Statistics is enabled, stats only auto-update when at least 500 records plus another 20% of records within a table are changed The 20% requirement does not change as a table grows in size by default Instead: Update Statistics manually and frequently (based on modification_counter field in sys.dm_db_stats_properties DMV). Instead: Enable Trace flag 2371 on SQL Server 2014 and earlier (automatically enabled in SQL Server 2016). This flag reduces the 20% threshold as the table record count increases. https://msdn.microsoft.com/en-us/library/jj aspx https://support.microsoft.com/en-us/help/ /controlling-autostat-auto-update-statistics-behavior-in-sql-server https://www.simple-talk.com/sql/performance/sql-server-statistics-questions-we-were-too-shy-to-ask John Eisbrener – DB Atlas

44 Misconception - DBCC CHECKDB using REPAIR_ALLOW_DATA_LOSSThe REPAIR_ALLOW_DATA_LOSS functionality is lazy. If direct paths to the data do not exist, this option drops pointers to the data and allows garbage collection to purge the data This option can and likely will break Referential Integrity within a database Instead: If a Nonclustered Index is corrupted, Drop/Recreate or Rebuild it Instead: If a Clustered Index or Heap Table is corrupted, access the data via a Nonclustered Index and write the data out to a separate table John Eisbrener – DB Atlas

45 Clustered Index Nonclustered Index John Eisbrener – DB Atlas

46 Misconception - Columnstore IndexesColumnstore Indexes are finally fully fledged as of SQL 2016 2014 – Beta Release 2012 – Alpha Release Check Dates on Blog Posts regarding Columnstore Indexes Articles written in Q3 of 2016 and later are likely safe Reorganize operations should be ran twice, when ran in general First to compress rows into the rowstore ALTER INDEX CCI_name ON Table REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON) Second to combine small rowstores together ALTER INDEX CCI_name ON Table REORGANIZE Use sys.dm_db_column_store_row_group_physical_stats DMV to monitor No longer an Enterprise Only Feature (SQL 2016 SP1 and later) https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-defragmentation https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/03/07/columnstore-index-defragmentation-using-reorganize-command/ John Eisbrener – DB Atlas

47 Supplemental Queries Cache Used by DB Cache Used by Index within DBCached Queries Using Table/View Most Costly Queries in Cache John Eisbrener – DB Atlas

48 Cache Used by DB DECLARE @total_buffer INT;= cntr_value FROM sys.dm_os_performance_counters WHERE RTRIM([object_name]) LIKE '%Buffer Manager' AND counter_name IN ('Total Pages', 'Database Pages'); ;WITH src AS ( SELECT database_id, db_buffer_pages = COUNT_BIG(*) FROM sys.dm_os_buffer_descriptors GROUP BY database_id ) [db_name] = CASE [database_id] WHEN 32767 THEN 'Resource DB' ELSE DB_NAME([database_id]) END, db_buffer_pages, db_buffer_MB = db_buffer_pages / 128, db_buffer_percent = CONVERT(DECIMAL(6,3), db_buffer_pages * FROM src ORDER BY db_buffer_MB DESC; John Eisbrener – DB Atlas

49 Cache Used by Index within DBUSE [DB_Name]; WHERE GO au.[type] IN (1,2,3) AND o.is_ms_shipped = 0 ) ;WITH src AS ( src.[Object], SELECT src.[Type], [Object] = o.name, src.[Index], [Type] = o.type_desc, src.Index_Type, [Index] = COALESCE(i.name, ''), buffer_pages = COUNT_BIG(b.page_id), [Index_Type] = i.type_desc, buffer_mb = COUNT_BIG(b.page_id) / 128 p.[object_id], p.index_id, src au.allocation_unit_id FROM sys.dm_os_buffer_descriptors AS b sys.partitions AS p ON src.allocation_unit_id = b.allocation_unit_id INNER JOIN sys.allocation_units AS au b.database_id = DB_ID() ON p.hobt_id = au.container_id GROUP BY sys.objects AS o ON p.[object_id] = o.[object_id] src.Index_Type sys.indexes AS i ORDER BY ON o.[object_id] = i.[object_id] 1, buffer_pages DESC; AND p.index_id = i.index_id John Eisbrener – DB Atlas

50 Cached Queries Using Table/ViewSELECT DISTINCT TOP 100 s.total_logical_reads/s.execution_count AS UsageScore, SUBSTRING(t .text, (s.statement_start_offset/ 2)+1 , ((CASE s.statement_end_offset WHEN - 1 THEN DATALENGTH(t.text) ELSE s.statement_end_offset END - s.statement_start_offset )/2) + 1 ) AS statement_text, s.execution_count AS ExecutionCount, s.max_elapsed_time AS MaxElapsedTime, ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime, s.creation_time AS LogCreatedOn, * FROM sys.dm_exec_query_stats s CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t WHERE text like '%ObjectName%' -- Enter Table/View name here and text not like '%sys.dm_exec_query_stats s%' ORDER BY s.total_logical_reads/s.execution_count DESC /* -- Pull Execution Plan SELECT query_plan FROM sys.dm_exec_query_plan(plan_handle_from_results) */ John Eisbrener – DB Atlas

51 Most Costly Queries in CacheSELECT TOP 100 SUBSTRING(t.text, (s .statement_start_offset/ 2)+1 , ((CASE s.statement_end_offset WHEN - 1 THEN DATALENGTH(t.text) ELSE s.statement_end_offset END - s.statement_start_offset )/2) + 1 ) AS statement_text , DB_NAME (t.dbid) AS [db_name] , 1.00* s.total_elapsed_time / DATEDIFF (second, s.creation_time, GETDATE ()) / 1 AS percent_used_time , DATEDIFF (HOUR, s.creation_time, GETDATE()) AS cached_hours , s.execution_count , 1.00* s.execution_count / ( 1+DATEDIFF (HOUR, s.creation_time, GETDATE())) AS executions_per_hour , s.max_elapsed_time , ISNULL (s.total_elapsed_time / s.execution_count, 0) AS avg_elapsed_time , s.total_elapsed_time , s.creation_time , s.last_execution_time , s.sql_handle , s.plan_handle , c.most_recent_session_id , c.parent_connection_id , c.session_id FROM sys .dm_exec_query_stats s CROSS APPLY sys. dm_exec_sql_text( s .sql_handle ) t LEFT JOIN sys. dm_exec_connections c ON c. most_recent_sql_handle = s .sql_handle WHERE s. execution_count > 1 AND DATEDIFF (second, creation_time,GETDATE ())>0 ORDER BY 9 DESC --total_elapsed_time /* -- Pull Execution Plan SELECT query_plan FROM sys.dm_exec_query_plan(s.plan_handle) */ John Eisbrener – DB Atlas

52 Additional Resources Stack Exchange: http://dba.stackexchange.com/Lazy SQL Server DBA Google Group: https://groups.google.com/forum/#!forum/lazy-sql-server-dbas Slack: https://sqlcommunity.slack.com/ Technet Forums: https://social.technet.microsoft.com/Forums MS Connect: https://connect.microsoft.com/ Twitter Hashtag: #sqlhelp John Eisbrener – DB Atlas

53 Questions? John Eisbrener – DB Atlas