November 19, 2014 at 4:19 am
Hi everyone
I am a bit stumped. We have a pretty decent server, but some queries still take some time to execute. I am pretty sure it should be able to do it faster, even with the amounts of data. The current setup is as follows:
2 Sockets, 12 Cores, 24 Logical Processors
2 NUMA nodes with 12 CPU's in each
80GB RAM (60GB Assigned to SQL Server) The Commit is standing at 10GB
LDF, MDF and TEMPDB are all located on seperate SSD drives.
Cost Threshold for Parallelism is at 50 and MAXDOP is at 8.
When I run a query and look at the Performance monitor, everything looks fine?
Disc Total I/O on each drive is less than 100MB/s
CPU stays < 10% on average, jumps up to about 28% now and then
Memory stays at 10GB
But the query still takes about 20 seconds to run. I have run the queries with Execution Plans and all the Indexes etc are in place.
Is there something else I can look at?
November 19, 2014 at 4:33 am
Can you post the query, execution plan and statistics IO/TIME output?
How many rows is the query returning?
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
November 19, 2014 at 4:36 am
Without the queryplan there is not much we can do. Is the query itself well written? Is the query using the existing indexes in a efficient way (like: seek instead of scan).
Please post the query plan (and the query itself).
November 19, 2014 at 5:03 am
ok, query is as follows
SELECT
DS.ProductId
, PD.Saturday TransactionDate
, SUM(Quantity) QTY
, SUM(Value) VAL
FROM DailySales DS
INNER JOIN ProcessingDates PD
ON DS.TransactionDate BETWEEN PD.Sunday AND PD.Saturday
WHERE DS.TransactionDate BETWEEN 'May 11 2014 12:00AM' AND 'Nov 22 2014 12:00AM'
GROUP BY
DS.ProductId
, PD.Saturday
ORDER BY SUM(Value) DESC
it returns just under 1 million records.
I have attached the execution plan. The problem seems to be with the TransactionDate joining.
I am doing that join to a get a Saturday for each week as the client's data don't always come on a daily basis and I have to group them by a comparable date.
November 19, 2014 at 5:29 am
First thing, a million rows isn't free to return. That will take time in and of itself, after the query has run.
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
November 19, 2014 at 6:09 am
Hi Gila
When I remove the ProcessingDate join, the query barely registers a time.
Will try and find a work around for it.
November 19, 2014 at 2:40 pm
Yeah, that join is not being processed well.
First, try just forcing a HASH join, i.e. writing "INNER HASH JOIN" instead of just INNER JOIN: that should be better here than a LOOP join.
Also, try clustering ProcessingDates on ( Sunday, Saturday ), and see if it's any better (or adding a covering index keyed on both ( Sunday, Saturday )).
Btw, I'm not seeing any conversion on TransactionDate, so I'm assuming that "Sunday" and "Saturday" are the same data type, which is best here.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 19, 2014 at 3:00 pm
D'OH, got interrupted by work, forgot one thing.
Let's see if we can also reduce the number rows in the dates side of the JOIN. I'm assuming here that "Sunday"/"Saturday" represents a one-week range, so you'll have to verify that this logic is valid. I use 10 days around the date rather than 7 just "to be sure".
FROM DailySales DS
INNER JOIN (
SELECT Sunday, Saturday
FROM ProcessingDates
WHERE
Sunday BETWEEN DATEADD(DAY, -10, 'May 11 2014 12:00AM') AND
DATEADD(DAY, +10, 'Nov 22 2014 12:00AM')
) AS DS ON DS.TransactionDate BETWEEN PD.Sunday AND PD.Saturday
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply