June 1, 2009 at 6:11 am
I've written a query which churns away for 20mins and then deliveres the data, however, whilst churning away for 20mins it basically maxxes the server out - it sits at near 100% CPU for most of the 20mins - with no one else running queries on the box either.
I intended to process this overnight so time is not really an issue - is there anything I can do to make the job (I'll create a job that just selects * from a view) only consume say 10% CPU or something - and just run for longer?
June 1, 2009 at 6:16 am
You can tune your SQL so it can run much faster, if this is what you are asking, can you post your SQL that takes 20 minutes and consumes 100% cpu, as well as your SQL server configuration, guys will be happy to have a look.
By the mean time you can use Resource governor to restrict resource on 2008
June 1, 2009 at 6:17 am
Rob SQL2008 has an actual CPU governor for that situation...preventing any one query frome ating all resources...it's not in 2005.
a 20 minute query is unusual, unless you are threshing thru a terabyte or more of data. if You post the query and a copy of the actual execution plan, we can offer some great pointers on making it perform better; perhaps changing it to sub-second results instead of multi minute.
Lowell
June 1, 2009 at 6:18 am
We're on 2005 at the moment, but plan to move to 2008 at some point.
I know what's slowing the query down - it's some beasty sub queries that churn away on massive sets of data for ages, just not sure of a way around them.
I'm going to clobber a couple of guys internally to have a look later today but if not I'll try to post some examples later on.
June 1, 2009 at 6:21 am
Lowell (6/1/2009)
Rob SQL2008 has an actual CPU governor for that situation...preventing any one query frome ating all resources...it's not in 2005.a 20 minute query is unusual, unless you are threshing thru a terabyte or more of data. if You post the query and a copy of the actual execution plan, we can offer some great pointers on making it perform better; perhaps changing it to sub-second results instead of multi minute.
I can see how to generate the execution plan, however, how to I post it?! - it' bigger than my screen (omg, what a novice 'where's my mouse pointer' type question!) - I see I can save it as a .SQLPlan...
June 1, 2009 at 6:22 am
save it as a SQLplan...that's just xml. you can throw it in a zip file , and then attach it here to a followup post.
when we open it in SSMS, it gives the same graphical info that you see, without having to have a copy of the database and it's objects....
excellent diagnostic tool
Lowell
June 1, 2009 at 7:30 am
See attached for the execution plan.
I know it's the sub queries killing it, but that's always how I've done this kind of thing.
Any help appreciated!
June 1, 2009 at 7:48 am
Can you post the query, the table definitions and the index definitions as well please?
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
June 1, 2009 at 8:54 am
ok here's a best guess;
the execution plan has a portion of the query in it..something like the first 4200 characters or so, so i didn't have all the joins...i inveted a table called "Some Table" which is aliased as "AF" in your query.
take a look at this...basically, most of your subqeries can be replaced with a sum() of a case statement, instead of a subquery gfor each of the 8 items i've seen so far;
if you get a chance, post or attach the whole query.
let us know if this helps at all:
--Comm counts with the contact
SELECT X.*,
SUM(CASE WHEN ct.communication_type = 2 THEN 1 ELSE 0 END) AS Total_Calls_Contact,
SUM(CASE WHEN ct.communication_type = 1 THEN 1 ELSE 0 END) AS Total_Emails_Contact,
SUM(CASE WHEN ct.communication_type = 2 and ct.communication_date>= x.[Date Registered] THEN 1 ELSE 0 END) AS Total_Calls_Registered_Date,
SUM(CASE WHEN ct.communication_type = 1 and ct.communication_date>= x.[Date Registered] THEN 1 ELSE 0 END) AS Total_Emails_Registered_Date,
--Comm counts with the firm
SUM(CASE WHEN ct.communication_by = X.Assigned_To AND cn.firm_id = X.firm_id and ct.communication_type = 2 and ct.communication_date>= dateadd(mm, -3, getdate()) THEN 1 ELSE 0 END) AS Total_Calls_Firm_3Months,
SUM(CASE WHEN ct.communication_by = X.Assigned_To AND cn.firm_id = X.firm_id and ct.communication_type = 1 and ct.communication_date>= dateadd(mm, -6, getdate()) THEN 1 ELSE 0 END) AS Total_Emails_Firm_3Months,
SUM(CASE WHEN ct.communication_by = X.Assigned_To AND cn.firm_id = X.firm_id and ct.communication_type = 2 and ct.communication_date>= dateadd(mm, -3, getdate()) THEN 1 ELSE 0 END) AS Total_Calls_Firm_6Months,
SUM(CASE WHEN ct.communication_by = X.Assigned_To AND cn.firm_id = X.firm_id and ct.communication_type = 1 and ct.communication_date>= dateadd(mm, -6, getdate()) THEN 1 ELSE 0 END) AS Total_Emails_Firm_6Months,
AF.Sales_Person as Firm_Assigned_To
FROM tblcommunication ct
INNER JOIN tblcontact cn on cn.contact_id = ct.contact_id
INNER JOIN [Some Table] AF on cn.contact_id = AF.contact_id --**Wild Guess!?!?! Missing from what can be grabbed in execution plan*
LEFT OUTER JOIN
(
SELECT
a.user_id,
c.contact_title,
c.contact_firstname,
c.contact_surname,
c.contact_jobtitle,
f.firm_id,
f.firm_name,
ft.firm_type,
a.date_registered [Date Registered],
a.last_accessed [Last Accessed],
--Product Trials--
SUM(CASE WHEN p.product_type = 'Service' and p.free = 0 and p.asset_class = 'PE' THEN 1 ELSE 0 END ) as [PE Trial],
SUM(CASE WHEN p.product_type = 'Service' and p.free = 0 and p.asset_class = 'RE' THEN 1 ELSE 0 END ) as [RE Trial],
SUM(CASE WHEN p.product_type = 'Service' and p.free = 0 and p.asset_class = 'HF' THEN 1 ELSE 0 END ) as [HF Trial],
SUM(CASE WHEN p.product_type = 'Service' and p.free = 0 and p.asset_class = 'INF' THEN 1 ELSE 0 END ) as [INF Trial],
--Newsletters
SUM(CASE WHEN p.product_type = 'newsletter' and p.asset_class = 'PE' THEN 1 ELSE 0 END ) as [PE Spot],
SUM(CASE WHEN p.product_type = 'newsletter' and p.asset_class = 'RE' THEN 1 ELSE 0 END ) as [RE Spot],
SUM(CASE WHEN p.product_type = 'newsletter' and p.asset_class = 'HF' THEN 1 ELSE 0 END ) as [HF Spot],
SUM(CASE WHEN p.product_type = 'newsletter' and p.asset_class = 'INF' THEN 1 ELSE 0 END ) as [INF Spot]
FROM tbluser_subscription x
INNER JOIN tblpei_product p on p.product_id = x.product_id
WHERE x.user_id = a.user_id
GROUP BY
a.user_id,
c.contact_title,
c.contact_firstname,
c.contact_surname,
c.contact_jobtitle,
f.firm_id,
f.firm_name,
ft.firm_type,
a.date_registered ,
a.last_accessed )X
ON cn.contact_id = X.userId
Lowell
June 1, 2009 at 9:04 am
will also need to see the query as mentioned above but I have noticed that the table:
"tblPEI_product" has many many Clustered Index Scans.
there might be a way for us to narrow that down but it all depends on the actual query it's self...
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 2, 2009 at 1:56 am
Thanks for the comments guys. I had spoken to a colleague before coming back on here - his advice was that a lot of the sub queries I was using could be run against pre processed tables with indexes.
So, taking this on board I had a stab and managed to reduce the time from 19mins to 44seconds
For example:
SELECT count(communication_id) FROM tblcommunication ct WHERE ct.communication_by = X.Assigned_To AND ct.contact_id = x.contact_id and ct.communication_type = 2) Total_Calls_Contact,
Looks at tblcommunication which has 12 columns - one being a varchar(500), one a varchar(50), one a text field etc - creating a new #tmp table first which had only the required fields took seconds and made that sub query speed up dramatically.
19mins to 44 seconds keeps me happy 😀
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply