Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

SELECT query with "Writes" ?! Expand / Collapse
Author
Message
Posted Wednesday, December 19, 2012 6:29 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, August 10, 2014 8:35 AM
Points: 32, Visits: 1,215
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

Cheers & Happy Holidays,
Jörg


Jörg A. Stryk
MVP - MS Dynamics NAV
Post #1398358
Posted Wednesday, December 19, 2012 7:06 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:34 PM
Points: 23,302, Visits: 32,056
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1398383
Posted Wednesday, December 19, 2012 7:52 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1398422
Posted Wednesday, December 19, 2012 8:39 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, August 10, 2014 8:35 AM
Points: 32, Visits: 1,215
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
Post #1398460
Posted Wednesday, December 19, 2012 8:55 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1398472
Posted Wednesday, December 19, 2012 9:02 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, August 10, 2014 8:35 AM
Points: 32, Visits: 1,215
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
Post #1398478
Posted Wednesday, December 19, 2012 9:05 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1398482
Posted Wednesday, December 19, 2012 9:09 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:50 AM
Points: 1,949, Visits: 8,315
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.







Clear Sky SQL
My Blog
Kent user group
Post #1398488
Posted Wednesday, December 19, 2012 9:09 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:05 AM
Points: 42,829, Visits: 35,961
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1398490
Posted Wednesday, December 19, 2012 10:22 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, August 10, 2014 8:35 AM
Points: 32, Visits: 1,215
Dave Ballantyne (12/19/2012)
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.


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 ...


Jörg A. Stryk
MVP - MS Dynamics NAV
Post #1398536
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse