January 24, 2011 at 4:13 am
below query needs to be tune . since it is using system tables to how can i add indexes there /
SELECT DISTINCT Name as List_of_jobs_failing
FROM
(
SELECT sv.job_id job_id, sv.name , max(sja.job_history_id) instance_id , max(sja.stop_execution_date) as stop_execution_date
FROM msdb.dbo.sysjobs_view sv WITH (NOLOCK)
INNER JOIN msdb.dbo.sysjobactivity sja WITH (NOLOCK)
ON sv.job_id = sja.job_id and job_history_id IS NOT NULL
GROUP BY sv.job_id , sv.name
) X
INNER JOIN msdb.dbo.sysjobhistory sjh WITH (NOLOCK)
ON X.instance_id = sjh.instance_id + 1
WHERE run_status = 0
AND sjh.step_id = 1
AND cast(convert(varchar(16),x.stop_execution_date ,121) as datetime) BETWEEN DATEADD(dd, -3, getdate()) AND getdate() AND CONVERT (DATETIME, RTRIM(run_date))+ ( run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10 + 25 * run_duration ) / 216e4
BETWEEN DATEADD(dd, -3, getdate()) AND getdate()
i am attaching the exec plan also
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 24, 2011 at 4:19 am
The msdb job tables are not system tables.
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
January 24, 2011 at 4:20 am
most of the timing is being used at index scan. Check how up-to-date your index is?
What I mean is fragmentation of index. Seems like your index is highly fragmented.
----------
Ashish
January 24, 2011 at 4:25 am
Fragmentation will not cause index scans where otherwise there could be an index seek. It would be a poor switch if it could, scans are far more affected by fragmentation than seeks.
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
January 24, 2011 at 5:36 am
GilaMonster (1/24/2011)
The msdb job tables are not system tables.
Should i add some required indexes ? and how these table got populated ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 24, 2011 at 5:55 am
They get populated by SQLAgent (If I'm understanding your question correctly)
It's not generally recommended that you mess with the tables in the system databases. Why is this query a problem?
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
January 24, 2011 at 6:10 am
GilaMonster (1/24/2011)
Why is this query a problem?
the usage of resources if fine as per the query design.but i want to avoid Clus index scan or its cost %. i think query redesign can help here.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 24, 2011 at 9:47 am
Bhuvnesh (1/24/2011)
GilaMonster (1/24/2011)
Why is this query a problem?the usage of resources if fine as per the query design.but i want to avoid Clus index scan or its cost %. i think query redesign can help here.
I'll echo Gail's concerns here. I wouldn't muck with the system defined tables in the system databases any more then you have to. This query runs what, once or twice a day and should only take a second or two.
Are you seeing serious impact from this? If you are, you might want to dump the entire table to a secondary database and goof with them there. I wouldn't adjust the source tables.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 24, 2011 at 6:02 pm
Adding indexes won't help, either. Take a look at your ON and WHERE clauses... pretty sure both are non-SARGable.
ON X.instance_id = sjh.instance_id + 1
WHERE run_status = 0
AND sjh.step_id = 1
AND cast(convert(varchar(16),x.stop_execution_date ,121) as datetime) BETWEEN DATEADD(dd, -3, getdate()) AND getdate() AND CONVERT (DATETIME, RTRIM(run_date))+ ( run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10 + 25 * run_duration ) / 216e4
BETWEEN DATEADD(dd, -3, getdate()) AND getdate()
you have a formula on the "sjh" instance and the BETWEEN has formulas on column on both sides of the equation. You need to fix those to be SARGable before you even think of trying to optimize with indexes because, as the predicates you currently have go, there's just not much of a chance of doing anything with an index other than a SCAN.
I would also take the advice which others have offered that the tables you're working with are "owned" by Microsoft and I wouldn't change them a bit including the addition of indexes. It's kind of like adding your own air-scoop to a car... you might not be changing anything in the engine but you could get rain in the air intake of the engine.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply