December 19, 2012 at 6:29 am
Hi all,
I'm currently fiddling with a query (fired from a Dynamics NAV application) which is like this:
SELECT *,DATALENGTH("Picture")
FROM "Navision"."dbo"."XYZ$Customer"
WHERE "No_">=@P1
ORDER BY "No_"
OPTION (RECOMPILE,OPTIMIZE FOR UNKNOWN)
I know the * is stupid, as is the >= filter, but there's zero option to changes this ....
The table contains a gazillion of records, the PK Clustered is "No_". So far I could not check the actual QEP, but I'm pretty sure this results in a CI Scan ...
Anyway. What worries me is, that I see a huge number of "Reads" (more than 7.5 Mio!), taking forever (63 sec) ... and causing more than 100000 "Writes" ?!
Why are there any "Writes" when just reading with a SELECT? I don't have this with any other query ...
What could cause these "Writes"?
Hope you could enlighten me :blink:
Cheers & Happy Holidays,
Jörg
Jörg A. Stryk
MVP - MS Dynamics NAV
December 19, 2012 at 7:06 am
Really need to see the actual execution plan to help answer this, but a guess would be the writes are to a work table for the ORDER BY clause, even though the clustered index implies that order to begin with. The execution plan would help answer that question.
December 19, 2012 at 7:52 am
Most likely, as suggested, a worktable is being used, either because the dataset exceeds the available space in RAM, or because it has to do something with it that is best done in tempdb, like sorts, hash joins, etc. Would have to see more details of the execution plan to suggest something more specific, but writes to worktables during Select queries aren't uncommon, nor are the usually something to worry about.
The only potential concern I see here is that, if it's due to memory pressure forcing data into tempdb because it can't fit in available RAM, and that happens with any frequency, then it might be worthwhile to invest in a memory upgrade for the server involved.
If it's a query that will only be run infrequently (or even just once), and the server is otherwise doing fine, then it's probably a non-issue and you can ignore the writes.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 19, 2012 at 8:39 am
Thanks for your replies. So far my customer just sent me this brief Profiler Trace; I hope I could log on to the system ASAP so I could check the QEP ...
"Memory Pressure" might be indeed a problem here - luckily a temporary one, since at the moment this customer is running on a small virtual "interims" box ...
But anyway: except for increasing the memory (if possible, have to ask), there's nothing I could actually do to improve this?
Jörg A. Stryk
MVP - MS Dynamics NAV
December 19, 2012 at 8:55 am
If it's not memory pressure, then there may be ways to improve it, or may not. It really depends a lot on the data being queried, and on the complexity of the query itself.
But it may not be something that needs to be improved. First, identify if that's the actual performance bottleneck in the query. More likely, there are issues with the way the query is written, with indexing (missing indexes, indexes with poorly chosen leading edges, non-covering indexes, etc.), with index/table fragmentation, and so on.
99% or more of the time, worrying about the internal engine methodology for dealing with the data is much less productive than reviewing more obvious performance issues.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 19, 2012 at 9:02 am
First, identify if that's the actual performance bottleneck in the query. More likely, there are issues with the way the query is written, with indexing (missing indexes, indexes with poorly chosen leading edges, non-covering indexes, etc.), with index/table fragmentation, and so on.
Yeah, I'll have to look into this more detailed. The annoying thing here is, that with Dynamics NAV queries we have almost no chance to write the query in a better way - NAV automatically "translates" its native programming language into SQL ...
For now I have porposed/prepared an index rebuild on that table to make sure fragmentation is not an issue here ...
As soon as I got the QEP I'll share it here.
Thanks a bunch so far!
Jörg A. Stryk
MVP - MS Dynamics NAV
December 19, 2012 at 9:05 am
Yeah, that's the great thing about any of the Object-Relational mapping tools that write your queries for you. Dynamics NAV, Linq, etc. They are amazing, incredible, wonderful tools, right up till the moment they completely wreck things in ways that can't be fixed at all. Fun!
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 19, 2012 at 9:09 am
The writes could be due to a memory spill
http://www.sqlpassion.at/blog/PermaLink,guid,2a24830a-5cf9-4438-96e9-af0a6d7372c3.aspx
In terms of what else *could* (pure guesswork 🙂 ) be happening here:
The "delay" may not even be in SQL Server, quite often the client app is slow at reading the data.
As you have gazzilions of , presumabley, wide rows this could well be the case.
An easy test is to run the query in SSMS but turn on the "discard results after execution option" , you will probably find a massive improvement 🙂
Use the "Include client statistics" to quantify ( in bytes ) the result set size.
Also this query is *wrong*. Why use option (RECOMPILE) AND OPTIMIZE for Uknown , doesent make sense?!?
Optimize for unknown prevents usage of the varaiable value to build an execution plan but you are taking the hit of recompile anyway so why not use the value.
December 19, 2012 at 9:09 am
It'll likely be the sort spilling to TempDB. If you look in the default trace you'll see the sort warning in there. Sorts very commonly spill, especially if there're on largish resultsets as the memory grant they would require can be quite large (larger than the total size of the resultset)
What's the definition of the pk?
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
December 19, 2012 at 10:22 am
Dave Ballantyne (12/19/2012)
The writes could be due to a memory spillhttp://www.sqlpassion.at/blog/PermaLink,guid,2a24830a-5cf9-4438-96e9-af0a6d7372c3.aspx
In terms of what else *could* (pure guesswork 🙂 ) be happening here:
The "delay" may not even be in SQL Server, quite often the client app is slow at reading the data.
As you have gazzilions of , presumabley, wide rows this could well be the case.
An easy test is to run the query in SSMS but turn on the "discard results after execution option" , you will probably find a massive improvement 🙂
Use the "Include client statistics" to quantify ( in bytes ) the result set size.
Also this query is *wrong*. Why use option (RECOMPILE) AND OPTIMIZE for Uknown , doesent make sense?!?
Optimize for unknown prevents usage of the varaiable value to build an execution plan but you are taking the hit of recompile anyway so why not use the value.
Thanks for the link; I check it out ASAP.
The NAV client for sure is a problem, but on top of that query. That NAV is still 32bit and actually incapable to deal with such a huge amount of records - but again: zero chance to change this (except for upghrading the whole NAV thingy).
Regarding RECOMPILE and OPTIMIZE FOR UNKNOWN:
NAV automatically adds OFU to all SELECT queries ... no chance to change that, but usually it works fine!
On some queries on that table we experienced problems in the past, where SQL Server was doing something "unxexpected" even due to the OFU, so we tried to "overwrite this with a RECOMPILE hint we can only specify these hints on table level (or we would have to use more precise Plan Guides). NAV cannot consolidate this and is firing now both ...
My customer reported, that this problem occurs randomly, it feels like just every two weeks ... obviously it arises as suddenly as it then - somehow? - disappears ... ???
Riddle after riddle ... :crazy:
Jörg A. Stryk
MVP - MS Dynamics NAV
December 19, 2012 at 10:23 am
What's the definition of the pk?
PK and Clustered Index is on this field "No_" (varchar(20))
Jörg A. Stryk
MVP - MS Dynamics NAV
December 19, 2012 at 10:59 am
stryk (12/19/2012)
What's the definition of the pk?
PK and Clustered Index is on this field "No_" (varchar(20))
Please post the definition of the pk (the SQL statement to create it)
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
December 19, 2012 at 2:13 pm
Taken from SSMS script generator:
ALTER TABLE [dbo].[XYZ$Customer] ADD CONSTRAINT [XYZ$Customer$0] PRIMARY KEY CLUSTERED
(
[No_] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [Data Filegroup 1]
GO
Jörg A. Stryk
MVP - MS Dynamics NAV
December 19, 2012 at 2:31 pm
Then that order by shouldn't need a sort. Unless there's maybe parallelism. Really need to see the exec plan
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
December 21, 2012 at 12:35 am
Update:
So far my customer could not provide the QEP and currently I cannot logon to the system (holidays!).
But they ran the Index Rebuild on that table which seems to help a lot! So, for now we will schedule this maintenance daily ...
But this also means, that - for now - we cannot reproduce the problem anymore, so I owe you the QEP ...
Probably we should close this thread - for now. Once the problem returns I'll make sure to get the actual QEP and come back on this again ...
Thanks all for your support!
Happy Holidays,
Jörg
Jörg A. Stryk
MVP - MS Dynamics NAV
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply