#sys.dm_exec_query_stats

[sandeep2363]

Find top CPU consuming SQL Queries in SQL Server

Find top CPU consuming SQL Queries in SQL Server

Check top CPU consuming in SQL Queries in SQL Server ;WITH eqs AS ( SELECT [execution_count] ,[total_worker_time]/1000 AS [TotalCPUTime_ms] ,[total_elapsed_time]/1000 AS [TotalDuration_ms] ,query_hash ,plan_handle ,[sql_handle] FROM sys.dm_exec_query_stats ) SELECT TOP 10 est.[text], eqp.query_plan AS SQLStatement ,eqs.* FROM eqs OUTER APPLY sys.dm_exec_query_plan(eqs.plan_handle) eqp OUTER…

[bananaaccess]

How to solve trial period of redgate sql toolbelt problem

#How to solve trial period of redgate sql toolbelt problem full

For instance, it supports wildcard searches for one ( ?) or more ( *) characters, and ~ can be used to perform a fuzzy search, returning similar words as well as the exact search term.Īrguably some of these modes are more useful for natural language search than for searching for a T-SQL query, and perhaps the most common use case would be where the user already knows the exact query they want to find. Lucene offers several search modes that require special characters.

#How to solve trial period of redgate sql toolbelt problem full

To limit the size of the files and avoid duplicating information, we chose not to store the full text of the query in Lucene instead, we store just an ID that we can use to extract it, and any other information about the query, from our repository. Retrieving the sampled query is then fast. Lucene splits the text of the query into individual words, and we index top queries as they’re sampled, so this relatively intensive operation is carried out up front. We also considered using ElasticSearch or Solr, but either of these would have required the installation of an external service. We considered using SQL Server’s inbuilt Full Text Search functionality but had concerns about performance, and the fact that data would be stored in the repository. Lucene indexes data in the file system and searches those files when you perform a search. SQL Monitor uses Lucene.NET to index query text. It should be make it much easier to find a particular query, or to find all queries referencing a particular table or view or calling a particular function, for example. So even though our 2.12 pm query isn’t in the top queries list, SQL Monitor has sampled it and stored it, so a search will find it. It will return any matching query that was in the top 50 queries at any sampling point in the interval being examined, according to any of the available metrics. SQL Monitor v12 now allows you to search the text of top queries. This repopulates and reorders the list each time, according to the selected metric such as CPU time or Logical reads. Finally, you could try reordering the table by different columns, to find it. For example, a query that ranked 42nd by physical reads at 2:12 pm and was never seen again would be unlikely to make the top 50 when considered over a 24-hour interval. You might also have needed to restrict the time range to a narrow window around when the query ran. You often needed to expand the list to display the top 50 queries instead of just the top 10. If you’re trying to identify which queries are the biggest culprits in a system that’s experiencing generalized performance problems, then the top queries list is a good place to start, since it identifies all the most ‘expensive’ user and system queries that ran on the instance, over the period. However, without a search facility, it was often harder to locate a specific query in the list, if it was not one of the longest running queries during that period. SQL Monitor doesn’t store every single query that is run on a particular instance, only those that exceed certain minimum threshold levels. By default, the list is populated by Duration (ms), meaning that the query with the longest average duration per execution is listed first. It presents this information in the top 10 queries table. SQL Monitor keeps track of what queries are being run by regularly sampling SQL Server’s “query summary” DMV, sys.dm_exec_query_stats, and persisting the query execution statistics to its repository.

[wwwtandemlabtech]

Handy - SQL Scripts to show high CPU usage

The first query will order the results based on the queries that have used the most CPU time since the SQL Server instance has been restarted (or the server has been rebooted). The second query orders the results based upon the average CPU time that each query takes. — Find queries that take the most CPU overall SELECT TOP 50 ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) ,TextData = qt.text ,DiskReads = qs.total_physical_reads --The worst reads, disk reads ,MemoryReads = qs.total_logical_reads -- Logical Reads are memory reads ,Executions = qs.execution_count ,TotalCPUTime = qs.total_worker_time ,AverageCPUTime = qs.total_worker_time/qs.execution_count ,DiskWaitAndCPUTime = qs.total_elapsed_time ,MemoryWrites = qs.max_logical_writes ,DateCached = qs.creation_time ,DatabaseName = DB_Name(qt.dbid) ,LastExecutionTime = qs.last_execution_time FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt ORDER BY qs.total_worker_time DESC— Find queries that have the highest average CPU usage SELECT TOP 50 ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + ‘.’ + OBJECT_NAME(qt.objectid, qt.dbid) ,TextData = qt.text ,DiskReads = qs.total_physical_reads — The worst reads, disk reads ,MemoryReads = qs.total_logical_reads –Logical Reads are memory reads ,Executions = qs.execution_count ,TotalCPUTime = qs.total_worker_time ,AverageCPUTime = qs.total_worker_time/qs.execution_count ,DiskWaitAndCPUTime = qs.total_elapsed_time ,MemoryWrites = qs.max_logical_writes ,DateCached = qs.creation_time ,DatabaseName = DB_Name(qt.dbid) ,LastExecutionTime = qs.last_execution_time FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt ORDER BY qs.total_worker_time/qs.execution_count DESC

[blockcontech-blog]

DMV - SPROC with the highest average CPU

This handy bit of code shows the stored procedures with the highest average CPU time in SQL Server.. SELECT TOP 50 * FROM(SELECT OBJECT_NAME(s2.objectid) AS ProcName,SUM(s1.total_worker_time/s1.execution_count) AS AverageCPUTime,s2.objectid,SUM(execution_count) AS execution_countFROM sys.dm_exec_query_stats AS s1CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2GROUP BY OBJECT_NAME(s2.objectid),objectid) xWHERE OBJECTPROPERTYEX(x.objectid,'IsProcedure') = 1AND EXISTS (SELECT 1 FROM sys.procedures sWHERE s.is_ms_shipped = 0AND s.name = x.ProcName )ORDER BY AverageCPUTime DESC

[deep-context-tech]

Handy - SQL Scripts to show high CPU usage

The first query will order the results based on the queries that have used the most CPU time since the SQL Server instance has been restarted (or the server has been rebooted). The second query orders the results based upon the average CPU time that each query takes. — Find queries that take the most CPU overall SELECT TOP 50 ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) ,TextData = qt.text ,DiskReads = qs.total_physical_reads --The worst reads, disk reads ,MemoryReads = qs.total_logical_reads -- Logical Reads are memory reads ,Executions = qs.execution_count ,TotalCPUTime = qs.total_worker_time ,AverageCPUTime = qs.total_worker_time/qs.execution_count ,DiskWaitAndCPUTime = qs.total_elapsed_time ,MemoryWrites = qs.max_logical_writes ,DateCached = qs.creation_time ,DatabaseName = DB_Name(qt.dbid) ,LastExecutionTime = qs.last_execution_time FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt ORDER BY qs.total_worker_time DESC— Find queries that have the highest average CPU usage SELECT TOP 50 ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + ‘.’ + OBJECT_NAME(qt.objectid, qt.dbid) ,TextData = qt.text ,DiskReads = qs.total_physical_reads — The worst reads, disk reads ,MemoryReads = qs.total_logical_reads –Logical Reads are memory reads ,Executions = qs.execution_count ,TotalCPUTime = qs.total_worker_time ,AverageCPUTime = qs.total_worker_time/qs.execution_count ,DiskWaitAndCPUTime = qs.total_elapsed_time ,MemoryWrites = qs.max_logical_writes ,DateCached = qs.creation_time ,DatabaseName = DB_Name(qt.dbid) ,LastExecutionTime = qs.last_execution_time FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt ORDER BY qs.total_worker_time/qs.execution_count DESC

[sonofaforester]

Tame that query, part 2

In part one, I discussed the performance issues that we were having at KCF with one of our SQL databases. In this post, I’ll discuss how we identified the queries that were most problematic.

Finding problematic queries

The first we needed to do was identify if there 2 or 3 problematic queries that might be causing the bulk of the load on the server. If we could identify a handful of bad queries, we could then either optimize the queries or possibly cache them in Redis.

To find the problem queries, we ran the following SQL query.

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,  ((CASE qs.statement_end_offset     WHEN -1 THEN DATALENGTH(qt.TEXT)     ELSE qs.statement_end_offset  END - qs.statement_start_offset)/2)+1),  qs.execution_count,  qs.total_logical_reads,  qs.last_logical_reads,  qs.total_logical_writes,  qs.last_logical_writes,  qs.total_worker_time,  qs.last_worker_time,  qs.total_elapsed_time/1000000 total_elapsed_time_in_S,  qs.last_elapsed_time/1000000 last_elapsed_time_in_S,  qs.last_execution_time,  qs.creation_time,  DATEDIFF(MINUTE, qs.creation_time, GETDATE()) as plan_age,  qp.query_plan FROM sys.dm_exec_query_stats qs  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt  CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp  ORDER BY qs.total_worker_time DESC

This query will bring back a table that shows the most expensive queries sorted by total_worker_time. Total_worker_time is the total CPU time spent running the query in microseconds. Looking through the list, you'll quickly identify the most expensive queries that you should focus on.

Analyzing the bad queries

If you run the above query in SQL Server Management Studio (SSMS), you’ll see a link in the final column to the actual query plan like so:

By clicking on one of these links, you can see the actual query plan that SQL server will use to execute the query. This is where specific performance issues can be identified. Perhaps I’ll discuss reading query execution plans in another post, but below are a couple things to look for.

SELECT TOP 1000 Hierarchy FROM Groups WHERE Hierarchy LIKE '/a%'

The query plan above shows a query that takes advantage of an index called IX_Hierarchy to return the requested data. Because it is an Index Seek, it uses the index efficiently without any excess disk or CPU I/O. However, an innocent enough change in the query like the following can have significant performance ramifications.

SELECT TOP 1000 Hierarchy FROM Groups WHERE LOWER(Hierarchy) LIKE '/a%'

By simply changing the predicate to have the LOWER() function, we remove the ability of SQL to seek the IX_Hierarchy index, it now must instead do an Index Scan which in this case takes 10 times as long as the seek.

To compare the effects of changes to queries, place the original query and the changed query in a new query window in SSMS and click the Actual Execution Plan button.

After executing a query, the query plans used to execute them will be shown in a new tab in the Results window. This will show the query plans and importantly, the Query cost relative to eachother, a lower cost is better.

Reading and optimizing query plans could be the topic of an entire series of blog posts and spending time understanding them can be quite beneficial.

In the next post, I’ll talk about some things we did at KCF to optimize some of our worst queries.

[aybardumlu]

SQL de neler oluyor? CPU yoran sorguları görüntülemek

Merhaba,

Yazılım veya web sitesi vb. fazla olarak yavaşladı. Çeşitli kontroller yaptınız problem SQL üzerinden görünüyor. CPU %100 olarak çalışıyor. Görev yöneticisinde ilginç bir şeyler var... Bir nedeni de sitenize SQL sorgusu atılıyor olabilir (atak yapılıyor)

Bu tarz problemler yaşadığınızda SQL üzerinden yapacağınız kontrollerin querylerini vereceğim. İlk olarak bunları kontrol etmeniz gerekiyor.

CPU yu en çok yoran 10 sorguyu bulmak için:

SELECT TOP 10

QT.TEXT AS STATEMENT_TEXT,

QP.QUERY_PLAN,

QS.TOTAL_WORKER_TIME AS CPU_TIME

FROM SYS.DM_EXEC_QUERY_STATS QS

CROSS APPLY SYS.DM_EXEC_SQL_TEXT (QS.SQL_HANDLE) AS QT

CROSS APPLY SYS.DM_EXEC_QUERY_PLAN (QS.PLAN_HANDLE) AS QP

ORDER BY TOTAL_WORKER_TIME DESC

CPU yu en çok yoran disk yazma okuma sorgularını bulmak için:

top 10 sorgu

SELECT TOP 10

TOTAL_LOGICAL_READS,

TOTAL_LOGICAL_WRITES,

EXECUTION_COUNT,

TOTAL_LOGICAL_READS+TOTAL_LOGICAL_WRITES AS [IO_TOTAL],

QT.TEXT AS QUERY_TEXT,

DB_NAME(QT.DBID) AS DATABASE_NAME,

QT.OBJECTID AS OBJECT_ID

FROM SYS.DM_EXEC_QUERY_STATS QS

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE) QT

WHERE TOTAL_LOGICAL_READS+TOTAL_LOGICAL_WRITES > 0

ORDER BY [IO_TOTAL] DESC

CPU yu yoran en çok sorgu çalıştıran işlemler

SELECT QS.EXECUTION_COUNT,

QT.TEXT AS QUERY_TEXT,

QT.DBID,

DBNAME= DB_NAME (QT.DBID),

QT.OBJECTID,

QS.TOTAL_ROWS,

QS.LAST_ROWS,

QS.MIN_ROWS,

QS.MAX_ROWS

FROM SYS.DM_EXEC_QUERY_STATS AS QS

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.SQL_HANDLE) AS QT

ORDER BY QS.EXECUTION_COUNT DESC

Umarım işinize yarar. Kolay gelsin.

from Aybar Dumlu - Blog, Friends, Familiar https://ift.tt/2qXGZls via IFTTT

[urbanbjorkman]

Get the latest queries run in an SQL Server database

SELECT TOP 50 * FROM(SELECT COALESCE(OBJECT_NAME(s2.objectid),'Ad-Hoc') AS ProcName,  execution_count,s2.objectid,    (SELECT TOP 1 SUBSTRING(s2.TEXT,statement_start_offset / 2+1 ,      ( (CASE WHEN statement_end_offset = -1  THEN (LEN(CONVERT(NVARCHAR(MAX),s2.TEXT)) * 2) ELSE statement_end_offset END)- statement_start_offset) / 2+1)) AS sql_statement,       last_execution_time FROM sys.dm_exec_query_stats AS s1 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 ) x WHERE sql_statement NOT like 'SELECT TOP 50 * FROM(SELECT %' --and OBJECTPROPERTYEX(x.objectid,'IsProcedure') = 1 ORDER BY last_execution_time DESC

[scarydba]

If you look at the Properties for the first operator of a graphical execution plan, you get all sorts of great information. I’ve talked about the data available there and how important it is in this older post. Checking out the properties of a plan you’re working on is a fundamental part of tuning that plan. What happens when you don’t know which plan you should be working on? What do you do, for example, if you want to see all the plans that are currently using ARITHABORT=FALSE or some other plan affecting setting?

The “easy” answer to this question is to run an XQuery against the XML of the query plan itself. You can identify these properties and retrieve the appropriate values from within the plan. However, XQuery consumes quite a bit of resources and you might not want to run this on a production system that’s already under stress. Now what?

sys.dm_exec_plan_attributes

There is a DMV that isn’t used a lot of the time because the information within it frequently doesn’t have a lot of bearing on solving fundamental query tuning issues such as out of date statistics, bad or missing indexes, or poorly structured T-SQL. This DMV, sys.dm_exec_plan_attributes, contains a bunch of values that are used by the optimizer to identify a plan in cache, such as object_id (if any), database_id, and compatibility level (compat_level). In addition to these clear & easy to understand attributes, there’s one more, set_options, that’s not immediately clear.

set_options

Follow the link about and you’ll find that the set_options column is a bitmask. It contains a number of settings within a single value. I won’t argue that this is a good (or bad) design. That’s what it is. The question is, how do we use it? Here’s a simple query that shows all the queries that have ANSI_WARNINGS set to true:

SELECT detqp.query_plan, depa.attribute, depa.value FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_text_query_plan( deqs.plan_handle, deqs.statement_start_offset, deqs.statement_end_offset ) AS detqp CROSS APPLY sys.dm_exec_plan_attributes(deqs.plan_handle) AS depa WHERE depa.attribute = 'set_options' AND (CAST(depa.value AS INT) & 16) = 16;

If you were looking for queries that didn’t have ANSI_WARNINGS, you could just change the value to 0. Use the values from the documentation link above to look at the various settings based on their bit values.

NOTE: One of the values is ‘Parallel’. When I was investigating this, I became very excited that this would be a way to programmatically identify parallel execution plans. However, it’s an attribute, like that others, that determines how a plan can be, not is, compiled. So looking at the parallel value here would just mean that a given plan could be parallel, not that it is.

Conclusion

You don’t want to be completely dependent on the query plan when it comes to investigation and identifying queries with problems. Instead, you want to be systematic in the approach. Using sys.dm_exec_plan_attributes, you can query for information about your queries.

  The post Data About Execution Plans appeared first on Home Of The Scary DBA.

[simplesqlserver]

Query Stats

DBAs rarely use the full potential of sys.dm_exec_query_stats.  It’s common to see the queries for looking at the most expensive queries according to any of the stats contained within the current cache, which is great to see.  However, if you grab…

View Post

[rg443]

Find Top Most Expensive Cached Queries (sys.dm_exec_query_stats)

[bs url=http://www.codeproject.com/Articles/579593/How-to-Find-the-Top-Most-Expens] sys.dm_exec_query_stats DMV (Dynamic Management View) is described at http://msdn.microsoft.com/en-us/library/ms189741.aspx

Top 10 Total CPU Consuming Queries

SELECT TOP 10 QT.TEXT AS STATEMENT_TEXT, QP.QUERY_PLAN, QS.TOTAL_WORKER_TIME AS CPU_TIME FROM SYS.DM_EXEC_QUERY_STATS QS CROSS APPLY SYS.DM_EXEC_SQL_TEXT…

[rg443]

SQL Server - most costly queries in terms of Total CPU

SQL Server – most costly queries in terms of Total CPU

[bs url=http://www.johnsansom.com/how-to-identify-the-most-costly-sql-server-queries-using-dmvs/]

SELECT TOP 20 qs.sql_handle, qs.execution_count, qs.total_worker_time AS Total_CPU, total_CPU_inSeconds = --Converted from microseconds qs.total_worker_time/1000000, average_CPU_inSeconds = --Converted from microseconds (qs.total_worker_time/1000000) / qs.execution_count, qs.total_elapsed_time,…

[rg443]

List expensive tsql queries (sys.dm_exec_query_stats)

List expensive queries [bs url=http://gallery.technet.microsoft.com/scriptcenter/List-expensive-queries-f6d63ac6] This Transact-SQL script returns the values from DMV sys.dm_exec_query_stats to rate SQL statements by their costs. These “costs” can be “Average CPU execution time “Average logical operations” or the total values of these measures

DECLARE @MinExecutions int; SET @MinExecutions = 5…

[simplesqlserver]

Recently Recompiled Resource Hogs

Blogged: SQL Server Recently Recompiled Resource Hogs

It’s not too uncommon for a query to get a new execution plan that performs a lot worse than it could, and sometimes it’s bad enough to drag the whole server down to a halt.  When it’s something obvious such as a query going from 2 seconds duration to 30…

View Post