September 25, 2015 at 8:33 am
Hi guys
I'm running a simple select query that is running slowly. I've reproduced the performance problem (query takes around 50 seconds). However, if I open another query window and run the exact same query it finishes in less than a second.
I set statistics on for both sessions and got the same stat results:
Table 'XXXX'. Scan count 18345, logical reads 55365, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'XX'. Scan count 2, logical reads 100962, physical reads 0, read-ahead reads 0, lob logical reads 300, lob physical reads 0, lob read-ahead reads 0.
So if the issue isn't the stats or the execution plan, I'm wondering why else I'd be getting such different results.
Regards
September 25, 2015 at 8:49 am
mitzyturbo (9/25/2015)
Hi guysI'm running a simple select query that is running slowly. I've reproduced the performance problem (query takes around 50 seconds). However, if I open another query window and run the exact same query it finishes in less than a second.
I set statistics on for both sessions and got the same stat results:
Table 'XXXX'. Scan count 18345, logical reads 55365, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'XX'. Scan count 2, logical reads 100962, physical reads 0, read-ahead reads 0, lob logical reads 300, lob physical reads 0, lob read-ahead reads 0.
So if the issue isn't the stats or the execution plan, I'm wondering why else I'd be getting such different results.
Regards
Blocking?
You mentioned the execution plans – did you physically compare them and find them to be identical?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 25, 2015 at 9:12 am
Hi Phil
No blocking on the db, from what I've compared on an Index Seek (looking at the operation with the highest cost 88%), the numbers look more or less the same for estimated IO cost, estimated operator cost etc.
If there was an issue on the server I'd be expecting it to hit both queries the same way.
Strangely enough, just as I'm writing this, the slow query is now running at the same speed as the other.
September 25, 2015 at 9:36 am
I don't fully understand MAXDOP but I do vaguely remember an issue in a previous job where the MAXDOP was the cause of a similar issue.
I don't know the processor set up on the server (no access) but I'm just wondering if this might cause this kind of behaviour
September 25, 2015 at 9:50 am
I'd expect the 2nd execution of the query to be faster, even with the same plan and number of logical reads, because it's re-using pages from the buffer cache that were read from disk to memory by the 1st execution.
To compare the performance of two queries, whether identical or different, use the following DBCC commands to free the procedure cache (plans) and buffer cache (data pages). Do this before each execution.
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 25, 2015 at 9:53 am
Thanks Eric, I'll try that the next time the problem arises
Unfortunately I can't reproduce it all the time to test this, but if I can get it running slowly again I'll test that
September 25, 2015 at 11:14 am
mitzyturbo (9/25/2015)
Thanks Eric, I'll try that the next time the problem arisesUnfortunately I can't reproduce it all the time to test this, but if I can get it running slowly again I'll test that
Clearing out the page buffer cache should consistently reproduce the slow runtime, because it's forcing the query to read all it's pages from disk instead of memory.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 29, 2015 at 10:26 am
Just want to clarify something:
I was looking at a previous post:
http://www.sqlservercentral.com/Forums/Topic415829-338-4.aspx
and I'm thinking that the way in which this query is coded is the most likely answer for the skewed performance.
The query is selecting from a view which performs a left outer join on another view which contains a cte. This cte contains thousands of rows (18,352 to be exact, 6 columns, mixture of int's and varchars).
I'm wondering if the execution plan is returning identical stats as it isn't including how the cte is being used / referenced. As Grant says in the other post about table variables, I'm wondering if cte's perform the same:
Grant Fritchey (10/29/2007)
Everyone else has covered this for the most part, but I don't mind adding one bit of information. Table variables, unlike temporary tables, do not have statistics created on them. This means that they are treated as if they have one row for any joins or searches done against them. When they only have a few rows, this doesn't matter, but when they have hundreds or thousands of rows, it makes a huge difference in the execution plans and the performance. It's just something to keep in mind.
Below is an outline of the code:
--Query
SELECT ID
, Account
, ParentAccount
FROM FirstView
WHERE (
((ID = 1)
AND (Account = 2))
)
--First View
SELECT i.ID ,
i.Account ,
i.ParentAccount ,
--10 other columns selected from Table, no functions or strange calculations
ih.Level AS Level
FROM
FirstTable I
LEFT OUTER JOIN SecondView ih ON ih.id = i.id AND ih.Account = i.Account
--Second View
WITH cteFirst (ID, Account, ParentAccount, [Level])
AS
(
-- Anchor member definition
SELECT
i.id, i.Account, i.ParentAccount
, 1 AS [Level]
FROM
[dbo].[FirstTable] i
WHERE
i.ParentAccount IS NULL
UNION ALL
-- Recursive member definition
SELECT
i.id, i.Account, i.ParentAccount
, [Level] + 1
FROM
[dbo].[FirstTable] i
INNER JOIN cteFirst AS parent ON parent.id = i.id AND i.ParentAccount = parent.Account
)
SELECT * FROM cteFirst
Just as an FYI, we're looking to remove the code altogether as from what I can see it makes no use of the recursion select it runs i.e. the Level value. A straight select off the table (FirstTable) will return the same data.
But I just want to know why the performance is so skewed before I start submitting change requests.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply