Results of sys.dm_exec_procedure_stats are disordered

Question

Post reply

Results of sys.dm_exec_procedure_stats are disordered

MotivateMan1394

Hall of Fame

Points: 3876
More actions
April 27, 2016 at 5:51 am

#318557

Hi
I'v daily saved result of sys.dm_exec_procedure_stats in order to monitor number of execution of storedProcedures . (By subtraction Of today value and Prevday)
I know that this results may reset if server resets.
all things were ok . It means the number of execution had been increased On a regular basis. And if I saw a disorder it related to server reset.
But
about one month all results are untidy. For instance Number of sp's execution in last 5 days :
2526084
1119703
34017
911389
1211944
729778
...
!!!!!!!!!!
another sps have this type of results .
I dont know why ?
;With Q
As
(
SELECT Ltrim([text]) TextCommand,objectid,total_logical_reads , execution_count , last_execution_time , GetDate() As InfoDate
FROM sys.dm_exec_procedure_stats AS a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) AS b
)
Insert into DB_Dba.DBO.DM_Most_Executed_Procedure
select Top 1000 TextCommand,objectid,total_logical_reads , execution_count , last_execution_time , InfoDate
from Q
ORDER BY execution_count DESC

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

is this on a cluster, where it might be failing over or toggling to the other node?

is it possible someone is downing the server for maintenance?

did you check the SQL logs to see if there are shutdowns, startups or something that might tie to what you are seeing?

could it be because you are not selecting ALL data, but select Top 1000 ?

it might be that at the time of running, the procedures you want to watch are not in the top 1000?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

MotivateMan1394 Hall of Fame Points: 3876 More actions · Answer 2

is this on a cluster, where it might be failing over or toggling to the other node?

No it isn't. we don't have clustering.

is it possible someone is downing the server for maintenance?

I am DBA. No if any one from networking want to rest it , it Will be coordinated with me.

did you check the SQL logs to see if there are shutdowns, startups or something that might tie to what you are seeing?

I run This query :

EXEC sys.sp_readerrorlog @p1 = 0

and this is the first record : It means the server restarted in this date -- 2016-03-09 05:17:33.660

could it be because you are not selecting ALL data, but select Top 1000 ?
it might be that at the time of running, the procedures you want to watch are not in the top 1000?

I check this result for some SPs which executed everyday And have been in our result every days.

Lowell

tripleAxe SSCertifiable Points: 5706 More actions · Answer 3

tripleAxe

SSCertifiable

Points: 5706

April 27, 2016 at 6:52 am

#1874916

has anyone run DBCC FREEPROCCACHE ?

MotivateMan1394 Hall of Fame Points: 3876 More actions · Answer 4

No

I checked all jobs, SPs and functions . I didnt find any thing that envolves FREEPROCCACHE or SP_Configure .

I think something unpleasant occurred. For example a sort of chache reset or some think like this. But I cant Find it. OR a very bad query that use all memory and cause of chache reset ? I dont know ?

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 5

I might be missing something (probably am, since others have already suggested things to check, indicating they've understood what the problem is), but what exactly is the problem? I haven't quite figured out what the problem is supposed to be 🙂

Also, it's not just a server restart that could distort these sorts of results. If a particular plan gets removed from the cache (for any reason, aged out, recompiled because of stats changes, etc.), then it'll start with fresh numbers when it gets added back in.

Independently of whatever this problem is supposed to be, when you're capturing the delta, you should also check to make sure that the cached time in procedure_stats for the post snapshot is the same as the cached time for the pre snapshot.

Otherwise the comparison doesn't really mean anything.

Cheers!

MotivateMan1394 Hall of Fame Points: 3876 More actions · Answer 6

Hi

Thanks a lot

I Read cached_time field beside another fields. The result was frustrated. :crying:

These are the result for one stored procedure :

execution_count--- SelectTime ----------------- - cached_time

96344----------- - 2016-05-01 11:15:51.470- 2016-05-01 10:57:03.880

5528------------ - 2016-05-01 11:40:05.033- 2016-05-01 11:39:01.923

27829----------- - 2016-05-01 11:44:39.920- 2016-05-01 11:39:01.923

76949----------- - 2016-05-01 12:02:08.993- 2016-05-01 11:48:26.157

It means that in average 10 minutes, plans of our stored procedures are removed from memory ?

What should I do ?

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

MotivateMan1394 (4/27/2016)
...about one month all results are untidy...

Just exactly does "untidy" mean here? Do you simply mean that they're not in descending order?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MotivateMan1394 Hall of Fame Points: 3876 More actions · Answer 8

No , The opposite . I expected see the execution count on the ascending Order.

It means every day should be more than prev day. Because value of execution count is Cumulative.

(But I see untidy in the results. I guess it relates to a memory problem. I am really worry about frequently reset of cached_time. )

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 9

MotivateMan1394 (5/1/2016)
No , The opposite . I expected see the execution count on the ascending Order.
It means every day should be more than prev day. Because value of execution count is Cumulative.
(But I see untidy in the results. I guess it relates to a memory problem. I am really worry about frequently reset of cached_time. )

Ok, it seems there are 3 different concepts here that are all getting jumbled together, but are really quite separate.

1) Your thought that the execution count of a particular plan should always increase, because it's cumulative.

2) The sort order of your query results.

3) Whether or not you have an issue with churn in the plan cache.

I addressed item 1 previously. If the plan stays in cache, then yes, the execution count will at least stay the same. The trick is that there are a lot of reasons a plan might not stay in cache from one day to the next, so it's not surprising if the assumption that execution counts for plans will be higher from day to day does not always/generally hold for your environment.

As for item 2, the only way to guarantee a sort order for a query is to use ORDER BY. You use an ORDER BY for the INSERT into your table, but that doesn't mean subsequent SELECTs from that table will use any particular sort order. What is the query you are using to pull data from DM_Most_Executed_Procedure, and how are you comparing data from day to day?

For item 3, at least from the very limited sample results you showed, it does look like some plans don't live in the cache for very long. That might be a bad thing, or it might be a good thing. If the distribution of your data is frequently changing, it might be good that queries get new plans frequently. Of course, you might also just have too many plans, or too little memory allocated to SQL Server to handle the number of plans you generate.

Either way, that one will require a close look at how many plans you have, what kind of plans they are, how often they're used, and why they have to get recompiled.