August 21, 2012 at 10:42 am
i have a proc who on occasion it's execution plan goes to hell and it uses 11 million reads to finish. I think it's because there is a check in the where that says user.isInactiveDate is null however i'm not certain cause it doesn't always happen.
as a result i'm trying to create a script that will give me all the proc calls who have logical reads over, say, 10,000.
I see how i can get the reads however not how i can get the proc name.
August 21, 2012 at 10:44 am
Can you post some code? Are you reading through the DMV for execution plans or something else?
August 21, 2012 at 10:46 am
BaldingLoopMan (8/21/2012)
i have a proc who on occasion it's execution plan goes to hell and it uses 11 million reads to finish. I think it's because there is a check in the where that says user.isInactiveDate is null however i'm not certain cause it doesn't always happen.as a result i'm trying to create a script that will give me all the proc calls who have logical reads over, say, 10,000.
I see how i can get the reads however not how i can get the proc name.
If you show us what you have so far, we can help you a lot better.
Jared
CE - Microsoft
August 21, 2012 at 10:49 am
thats the thing. The proc only does it sometimes. i dont know why. i assume it has something to do w the indexes being rebuild however havent proven that.
Right now its fine. it litterally goes from 11 million logical reads to 3.
heres the code.
ALTER PROCEDURE [dbo].[usp_tbl_staff_assisted_getbyuser]
@col_staff_username CHAR(20),
@usertype CHAR(1)
AS
SET NOCOUNT ON
--------------------------------------------------------------------------------------------------------------------------------
Select TOP 5
SAss.col_id,
SAss.col_staff_username,
SAss.col_assist_username,
SAss.col_assist_date,
U.col_partial_save,
U.col_loginname,
U.col_userid
From
tbl_assisted AS SAss With (NoLock)
Inner Join usr AS U With (NoLock) On U.username = SAss.col_assist_username AND SAss.col_staff_username = @col_staff_username
Where
SAss.col_staff_username = @col_staff_username
And U.usertype = @usertype
AND U.inactivedate IS NULL
Order By
SAss.col_assist_date desc
August 21, 2012 at 10:53 am
BaldingLoopMan (8/21/2012)
thats the thing. The proc only does it sometimes. i dont know why. i assume it has something to do w the indexes being rebuild however havent proven that.Right now its fine. it litterally goes from 11 million logical reads to 3.
heres the code.
ALTER PROCEDURE [dbo].[usp_tbl_staff_assisted_getbyuser]
@col_staff_username CHAR(20),
@usertype CHAR(1)
AS
SET NOCOUNT ON
--------------------------------------------------------------------------------------------------------------------------------
Select TOP 5
SAss.col_id,
SAss.col_staff_username,
SAss.col_assist_username,
SAss.col_assist_date,
U.col_partial_save,
U.col_loginname,
U.col_userid
From
tbl_assisted AS SAss With (NoLock)
Inner Join usr AS U With (NoLock) On U.username = SAss.col_assist_username AND SAss.col_staff_username = @col_staff_username
Where
SAss.col_staff_username = @col_staff_username
And U.usertype = @usertype
AND U.inactivedate IS NULL
Order By
SAss.col_assist_date desc
Do you want help with this stored proc, or the query you are using to find all those with reads over 10,000?
Jared
CE - Microsoft
August 21, 2012 at 11:09 am
Well other than the nolock hints I don't see anything obviously wrong with that.
Parameter sniffing problems perhaps? Can you post the execution plan (actual plan) of an execution with the 11 million reads?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 21, 2012 at 11:14 am
Can you post some ddl? how many rows are in these tables? It would be helpful to understand your indexes, contraints, etc.
-- Itzik Ben-Gan 2001
August 21, 2012 at 12:17 pm
Hmm... My thought was that the OP wanted to know how to take information from sys.dm_exec_procedure_stats, and either cross apply it or join it to get the name of the actual stored proc. I am having trouble with this myself right now, as I am trying to really learn the dmv's.
Jared
CE - Microsoft
August 21, 2012 at 12:20 pm
Cross apply to sys.dm_exec_sql_text (on the plan_handle). One of the resultant columns is Object_ID.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 21, 2012 at 12:24 pm
GilaMonster (8/21/2012)
Cross apply to sys.dm_exec_sql_text (on the plan_handle). One of the resultant columns is Object_ID.
Ok, but if I run this:
SELECT ps.object_id, s.objectid
FROM sys.dm_exec_procedure_stats ps
CROSS APPLY sys.dm_exec_sql_text(sql_handle) s I see that the columns match. These id's do not exist in any of the other dmv's "that I have looked at" (such as sys.all_objects) at least not as "object_id" where I can tie it to a name. This is where I am stuck.
Jared
CE - Microsoft
August 21, 2012 at 12:32 pm
SQLKnowItAll (8/21/2012)
GilaMonster (8/21/2012)
Cross apply to sys.dm_exec_sql_text (on the plan_handle). One of the resultant columns is Object_ID.Ok, but if I run this:
SELECT ps.object_id, s.objectidFROM sys.dm_exec_procedure_stats ps
CROSS APPLY sys.dm_exec_sql_text(sql_handle) s I see that the columns match. These id's do not exist in any of the other dmv's "that I have looked at" (such as sys.all_objects) at least not as "object_id" where I can tie it to a name. This is where I am stuck.
A HA! I see my issue. The code I am running (not for anything but learning the views right now) is this:
SELECT DB_NAME(ps.database_id), object_name(ps.object_id), ps.max_logical_reads, ps.max_physical_reads, s.text
FROM sys.dm_exec_procedure_stats ps
CROSS APPLY sys.dm_exec_sql_text(sql_handle) s
For this to return a name for the stored proc, it must be run in the context of the database that contains the stored proc. All others turn out NULL. Is there a way around this? i.e. if I wanted to see all stored procs in all databases?
Jared
CE - Microsoft
August 21, 2012 at 12:33 pm
Object_id is used to join to sys.objects (or sys.procedures if you prefer). Or you can use Object_name and pass it the object_id and database_id.
Note that sys.objects is database-specific and sys.dm_exec_procedure_stats is server-wide.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 21, 2012 at 12:37 pm
GilaMonster (8/21/2012)
Object_id is used to join to sys.objects (or sys.procedures if you prefer). Or you can use Object_name and pass it the object_id and database_id.Note that sys.objects is database-specific and sys.dm_exec_procedure_stats is server-wide.
Thanks Gail, I think the problem was that I did not know that I could execute the OBJECT_NAME with 2 arguments; i.e. OBJECT_NAME(object_id, database_id). That gave the the desired results and should help me a lot moving forward.
Jared
CE - Microsoft
August 21, 2012 at 3:28 pm
With no execution plan to see what is going on, here is one possible idea for you to try:
ALTER PROCEDURE [dbo].[usp_tbl_staff_assisted_getbyuser]
@col_staff_username CHAR(20),
@usertype CHAR(1)
AS
SET NOCOUNT ON
--------------------------------------------------------------------------------------------------------------------------------
Select TOP 5
SAss.col_id,
SAss.col_staff_username,
SAss.col_assist_username,
SAss.col_assist_date,
U.col_partial_save,
U.col_loginname,
U.col_userid
From
tbl_assisted AS SAss
Inner Join usr AS U
On U.username = SAss.col_assist_username -- AND SAss.col_staff_username = @col_staff_username
Where
SAss.col_staff_username = @col_staff_username
And U.usertype = @usertype
--AND U.inactivedate IS NULL
Order By
SAss.col_assist_date desc;
GO
-- Create a filtered covering index on usr for the query.
create index idx_active
on usr (
usertype,
username
)
include (
col_partial_save,
col_loginname,
col_userid
)
where inactivedate is null;
go
August 22, 2012 at 8:27 am
Going to try and answer everyone here.
1) I didn't save the execution plan when it was doing 11 million reads. I wasn’t expecting it to all the sudden fix itself. I do have spotlight however so I may be able to get it.
2)The issue at hand is why is this execution plan for this proc going to 11 million reads sometimes. Not all the time. One of our prod boxes pegs at 100 cpu during the hours or 1030 to around 1130. I traced it and found a the high read proc issue. so I wrote a version of the proc wrapping the isInactiveDate in an isnull and the plan was fine. I’ve had date issues in the past "param sniffin" where I was defaulting the date to -1 and it jacked the plan up. I digress, while I was developing my version of the proc w the isnull the original must have recompiled and stated running fine. Regardless, my version went to prod yesterday and I was thinking. when I was doing the compare between my version and the bad version, even though I was clicking generate execution plan it was still pulling the high read cached execution plan. which is another discussion all together. in my opinion if imp in ent mngr and physically click generate query exec plan it should damn well generate the plan. not pull the cached plan if it exists. therefore my change probably won’t do anything. I have to assume whatever anomaly that is occurring will negatively affect my version as well.
3) yesterday the same thing happened w/ another proc which happens to have the same query in it. I haven’t checked however I will assume this query is probably in several procs.
Therefore, here’s my plan.
1) I’m going to build a process to monitor the box. If it find queries w/ extremely high reads then either drop and recreate of recompile the proc then notify us that it did so and to which proc. This should help short term and give me a better idea as to when this anomaly occurs. This is why I was asking for the read query which “SQLKnowItAll” provided which I have to say is exacltly what I needed. So thanks!
2) identify what is causing this. I have to assume it has something to do w/ either a massive data insert of change of stats rebuild or index rebuild. One I find this I’ll be clear and the people I can sleep at night. Have any of you run into this sort of thing before?
I’m going to try and get the plan and if I do I’ll pass all the indexes and sample data and schemas too.
side note:
The DB is over 2 terabytes and the prod box has 80 cores. it's an absolute monster!!! sql server 2008 rs ent. Typically runs at under 10 cpu and what i'm most impressed w is it on average does 1.2 mill logical reads per ms and 1.1 thousand physical reads per ms. lol. 500 gigs of cache. so basically everything is running from cache. that's insane. When the issue occured and it pegged at 100% cpu there was 8 million reads per ms.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply