April 7, 2015 at 11:57 pm
Hi
I have this query
SELECT top 100 Ltrim([text]),objectid,total_rows,total_logical_reads , execution_count
FROM sys.dm_exec_query_stats AS a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) AS b
where last_execution_time >= '2015-04-07 10:01:01.01'
ORDER BY execution_count DESC
But the result of execution count is from the first. I want to know it only one day.
Thank you
April 8, 2015 at 12:07 am
You would have to filter the result set...
DECLARE @StartDateTime DATETIME,
@EndDateTime DATETIME;
SELECT top 100 Ltrim([text]),objectid,total_rows,total_logical_reads , execution_count
FROM sys.dm_exec_query_stats AS a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) AS b
WHERE last_execution_time >= @StartDateTime AND
last_execution_time <= @EndDateTime
ORDER BY execution_count DESC
April 8, 2015 at 12:17 am
thank you
April 8, 2015 at 4:31 am
But even with that filter, it's still going to show an execution count that is cumulative for the time that the query in question has been in cache. If you really want to know changes day to day, you should store the results in a table and then find the differences. But even that is going to be incomplete. What if a query ages out of cache? What if a query recompiles several times during the day?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 11, 2015 at 4:37 am
Hi
Yes It's right that I should save the daily result for Accurate conclusions.
I started this . But I have a problem .
This is my Query :
SELECT top 100 Ltrim([text]),objectid,total_rows,total_logical_reads , a.execution_count ,*
FROM sys.dm_exec_query_stats AS a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) AS b
ORDER BY execution_count DESC
But in result , Some objects are repeated. For example I have 3 or more records about one Object (Exactly with one ObjectId)
That have the same : Text And ObjectId Columns, But Different in TotalRows,StartOffset,..
why?
April 11, 2015 at 4:48 am
The clue is the start and end offset. They are statements in a single batch. The queries in a batch are stored seperately, but the batch, especially in the sql text, is stored together. You can use the statement start and end offset to pull the individual statement out of the batch. Here's an example:
SELECT SUBSTRING(dest.text, (der.statement_start_offset / 2) + 1,
(der.statement_end_offset - der.statement_start_offset) / 2
+ 1),
LEN(dest.text) AS CharLength,
DATALENGTH(dest.text) AS DLength,
DATALENGTH(dest.text) / 2 AS HalfDLength,
der.statement_start_offset,
der.statement_end_offset
FROM sys.dm_exec_query_stats AS der
CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) AS dest
WHERE der.statement_end_offset > -1 ;
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 11, 2015 at 5:00 am
Sorry !
I didnot get it. Please say it more simple.
Thank you
(In the other hand, my query Result start with sp name or function name and you query result start with Tsql statement)
Thank you again
April 11, 2015 at 6:17 am
I Think I Got it.
Please confirm this :
----------------------------------------------
I Have a Procedure that repeate 3 time in first query .
CREATE PROCEDURE [dbo].[P1]
( @I int, @I2 bit)
As
BEGIN
SELECT * FROM [T1] WHERE (Id = @Id)
IF (@I2 = 1)
Begin
DECLARE @P int
SELECT @P = PId FROM [T1] WHERE (Id = @Id)
SELECT TOP 1 CId FROM [T2] WHERE (PId = @P) ORDER BY [Row]
END
END
-----------------------------------------------------
But in you Query shows 3 Diferent Row with this ObjectId and Text of this 3 are :
1- SELECT * FROM [T1] WHERE (Id = @Id)
2- SELECT @P = PId FROM [T1] WHERE (Id = @Id)
3- SELECT TOP 1 CId FROM [T2] WHERE (PId = @P) ORDER BY [Row]
then Your query shows every statement in the sp. But I think It will dificualt.
I want to Number of execution a objectid Not every statement on it. and our server has thousands of these statemenmt and the result will be very big.
I need a result such as first query but for one time for every object.
thank you
April 11, 2015 at 7:21 am
So, the other problem is, you're going after sys.dm_exec_query_stats. That records the statements. That's what it's for. If you only want to see "objects," and by that definition we mean procedures, you can sys.dm_exec_procedure_stats. That will show the execution for procedures, including their specific object_id so you can just pull back the procedure name.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 12, 2015 at 2:39 am
Yes Ok
This get me a unique record for every Procedure.
a question :
Who do you write the queries in a nice box with different color.
Thank you
April 12, 2015 at 4:15 am
I post the query into the message box. Then, over on the left side of the screen you'll see all the IFCode Shortcuts. Highlight your query and click on the "code=sql" link there and it will put a wrapper around your code.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply