InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 14:10:45 GMT20http://www.sqlservercentral.com/Forums/Topic1398358-391-1.aspxOK, I'll try that! Thanks for the advice!Fri, 21 Dec 2012 04:16:39 GMTstrykhttp://www.sqlservercentral.com/Forums/Topic1398358-391-1.aspx[quote][b]stryk (12/21/2012)[/b][hr]We do a Full Scan - using the MP - every weekend (because we have plenty of time to do this); every day we just do a sp_updatestats (because we don't have the time for a full run) plus a sp_createstats 'indexonlx' ...[/quote]So full scan on the weekend, sampled during the week. I would suggest this table gets a full scan daily.If an index rebuild fixed the problem, 99% chance it's either stale stats or a bad exec plan getting into cache somehow.Fri, 21 Dec 2012 03:42:24 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1398358-391-1.aspxWe do a Full Scan - using the MP - every weekend (because we have plenty of time to do this); every day we just do a sp_updatestats (because we don't have the time for a full run) plus a sp_createstats 'indexonlx' ...But in case of this special table I wonder how these stats should get "outdated"- the affected "Customer" table is just master data, so once a record was inserted (sequentially, ordered by that "No_") there are not many changes ... for the same reason IMHO "fragmentation" should not be a problem here ... especially not on the "Clustered Index" ...It seems like this query was fired from a NAV GUI (so called "Form") and it was declaring a "Fast Forward Cursor" to load all that data ... I don't no for sure how big that table currently is, so I could guess "memory pressure" might be the problem ... but if that's the case: why does this problem not happen ALL the times? It's still the same 16GB of RAM (SQL Server Max. 14GB) ...The more I think about it, the more I have to scratch my head ...I'm afraid we are stalled here now, since my customer just went to X-Mas vacation.I almost hope this problem returns :Whistling:, because I'm really curious about the QEP and to find out what happens here ...Fri, 21 Dec 2012 02:35:45 GMTstrykhttp://www.sqlservercentral.com/Forums/Topic1398358-391-1.aspx[quote][b]stryk (12/21/2012)[/b][hr]We already have a complete statistic update up running every day ...[/quote]Fullscan or sampled?Index rebuilds seldom fix performance problems. The stats updates they do however fix many problemFri, 21 Dec 2012 00:57:54 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1398358-391-1.aspxWe already have a complete statistic update up running every day ...Fri, 21 Dec 2012 00:48:40 GMTstrykhttp://www.sqlservercentral.com/Forums/Topic1398358-391-1.aspx[quote][b]stryk (12/21/2012)[/b][hr]But they ran the Index Rebuild on that table which seems to help a lot! So, for now we will schedule this maintenance daily ...[/quote]No, don't. Run an Update Statistics on that table daily.Fri, 21 Dec 2012 00:44:31 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1398358-391-1.aspxUpdate: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örgFri, 21 Dec 2012 00:35:06 GMTstrykhttp://www.sqlservercentral.com/Forums/Topic1398358-391-1.aspxThen that order by shouldn't need a sort. Unless there's maybe parallelism. Really need to see the exec planWed, 19 Dec 2012 14:31:22 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1398358-391-1.aspxTaken from SSMS script generator:[code="sql"]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[/code]Wed, 19 Dec 2012 14:13:24 GMTstrykhttp://www.sqlservercentral.com/Forums/Topic1398358-391-1.aspx[quote][b]stryk (12/19/2012)[/b][hr][quote]What's the definition of the pk?[/quote]PK and Clustered Index is on this field "No_" (varchar(20))[/quote]Please post the definition of the pk (the SQL statement to create it)Wed, 19 Dec 2012 10:59:42 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1398358-391-1.aspx[quote]What's the definition of the pk?[/quote]PK and Clustered Index is on this field "No_" (varchar(20))Wed, 19 Dec 2012 10:23:35 GMTstrykhttp://www.sqlservercentral.com/Forums/Topic1398358-391-1.aspx[quote][b]Dave Ballantyne (12/19/2012)[/b][hr]The writes could be due to a memory spill [url]http://www.sqlpassion.at/blog/PermaLink,guid,2a24830a-5cf9-4438-96e9-af0a6d7372c3.aspx[/url]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.[/quote]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:Wed, 19 Dec 2012 10:22:26 GMTstrykhttp://www.sqlservercentral.com/Forums/Topic1398358-391-1.aspxIt'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?Wed, 19 Dec 2012 09:09:36 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1398358-391-1.aspxThe writes could be due to a memory spill [url]http://www.sqlpassion.at/blog/PermaLink,guid,2a24830a-5cf9-4438-96e9-af0a6d7372c3.aspx[/url]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.Wed, 19 Dec 2012 09:09:10 GMTDave Ballantynehttp://www.sqlservercentral.com/Forums/Topic1398358-391-1.aspxYeah, 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!Wed, 19 Dec 2012 09:05:42 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1398358-391-1.aspx[quote] 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.[/quote]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!Wed, 19 Dec 2012 09:02:02 GMTstrykhttp://www.sqlservercentral.com/Forums/Topic1398358-391-1.aspxIf 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.Wed, 19 Dec 2012 08:55:26 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1398358-391-1.aspxThanks 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?Wed, 19 Dec 2012 08:39:12 GMTstrykhttp://www.sqlservercentral.com/Forums/Topic1398358-391-1.aspxMost 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.Wed, 19 Dec 2012 07:52:27 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1398358-391-1.aspxReally 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.Wed, 19 Dec 2012 07:06:45 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1398358-391-1.aspxHi all,I'm currently fiddling with a query (fired from a Dynamics NAV application) which is like this:[code="sql"]SELECT *,DATALENGTH("Picture") FROM "Navision"."dbo"."XYZ$Customer" WHERE "No_">=@P1 ORDER BY "No_" OPTION (RECOMPILE,OPTIMIZE FOR UNKNOWN)[/code]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 [b]more than 100000 "Writes"[/b] ?!Why are there any "[i]Writes[/i]" 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örgWed, 19 Dec 2012 06:29:15 GMTstryk