To get the query memory usage of currently executing queries run the following: [cc lang=”sql”] SELECT TEXT ,query_plan ,requested_memory_kb ,granted_memory_kb ,used_memory_kb FROM sys.dm_exec_query_memory_grants emg CROSS APPLY sys.dm_exec_sql_text(sql_handle) CROSS APPLY sys.dm_exec_query_plan(emg.plan_handle) ORDER BY emg.requested_memory_kb DESC [/cc]
Continue reading ...The following DMV query retrieves the usage statistics for existing indexes. User Seeks – A high number indicates a well utilized index. User Scans – Number of times the index has been scanned. Could indicate improper ordering of the composite columns User Lookups – Indicates a different index was used for criteria and the actual data was looked up from this index for the select list User Updates – Number of times the index was updated with additional records [cc lang=”sql”] SELECT ObjectName = object_schema_name(idx.object_id) + ‘.’ + object_name(idx.object_id) ,IndexName = idx.name ,IndexType = CASE WHEN is_unique = 1 THEN ‘UNIQUE ‘ ELSE ” END + idx.type_desc ,User_Seeks = us.user_seeks ,User_Scans = us.user_scans ,User_Lookups = us.user_lookups ,User_Updates = us.user_updates FROM sys.indexes idx LEFT JOIN sys.dm_db_index_usage_stats us ON idx.object_id = us.object_id AND idx.index_id = us.index_id AND us.database_id = db_id() WHERE object_schema_name(idx.object_id) != ‘sys’ ORDER BY us.user_seeks + us.user_scans + us.user_lookups DESC [/cc]
Continue reading ...One of the most common database performance issue most companies face is the improper choice of indexes or the lack of indexes. Most essential, is the clustered index. A well-chosen clustered index will save CPU, Memory, and IO. Choosing the clustered index for a table goes hand-in-hand with the writing of the queries for a table. It is best to design the queries and the index simultaneously to keep consistency. The majority of the time this is not an option. When we have not written the queries, the best way to understand the optimal clustered index is to study the queries that are run against the table. Once you understand the queries that run against a table, it is helpful to think of each of these queries broken down into three parts: The select list – Be on the lookout for the queries that return the most columns from the table. The join columns – The most common columns used to join other tables against the table. The where clause – The most common columns used to filter the result sets. Avoiding Fragmentation Before going into depth in choosing a clustered index we need to know what causes fragmentation. We ideally want to answer no to the following: Will the clustered value(s) be updated? Can new rows be inserted between already inserted clustered rows? The reason for answering no above is because we do not want fragmentation at the clustered level. The clustered index is like the foundation of a […]
Continue reading ...[cc lang=”sql”] SELECT DISTINCT objname = object_name(p.object_id) FROM sys.partitions p JOIN sys.dm_tran_locks t1 ON p.hobt_id = t1.resource_associated_entity_id [/cc]
Continue reading ...This DMV will show the actual queries sorted by highest execution count since the last time SQL Server was restarted. These queries could be either adhoc or queries that reside within stored procedures. These are important because often times optimizing these will yield huge results in performance. In order to find specific procedures that have been executed a lot, refer to the Most Executed Procedures DMV. [cc lang=”sql”] SELECT TOP 50 qs.execution_count ,OBJECT_NAME(objectid) ,query_text = SUBSTRING( qt.text, qs.statement_start_offset/2, (CASE WHEN qs.statement_end_offset = -1 THEN len(convert(nvarchar(max), qt.text)) * 2 ELSE qs.statement_end_offset END – qs.statement_start_offset)/2) ,qt.dbid ,dbname = db_name(qt.dbid) ,qt.objectid FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt ORDER BY qs.execution_count DESC [/cc]
Continue reading ...
