SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[11.5.355 Std] - Fetching schema objects is too frequent
Goto page Previous  1, 2, 3  Next
 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[11.5.355 Std] - Fetching schema objects is too frequent
Author Message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7843

Post Reply with quote
Let's look into the linked server hypothesis. If you have synonyms in your database pointing to remote objects, it may lead to slowdowns while the server is resolving remote object references.

Please try temporarily changing the following in the settings. In the Options dialog select DB Options tab, then select SQL Server in the top-left list. On the right side expand DB Queries section. Change Objects query from "Objects (MSSQL) + Typed Synonyms" to "Objects (MSSQL)", save and test if that helps in your case. Please let us know your results.
Fri Mar 05, 2021 4:39 am View user's profile Send private message
BrendonOSullivan



Joined: 02 Mar 2021
Posts: 24
Country: New Zealand

Post Reply with quote
I tried changing the query used for Objects as you suggested but it makes no difference. It still takes between 40 and 60 seconds to refresh the cache for each database the first time it sees it.

I checked the commands flowing to the server with the profiler and it definitely was changed to the "Options" query instead of previously "Options + Typed Synonyms".

As previously, when I extract the query for "Options" from the profiler and run it in the SQL Management Studio editor the execution time is only 1.5 seconds, not 40 to 60 as when the SQL Assistant runs it.

So fundamentally the query is fine, both for "Options" or "Options + Typed Synonyms.," and should run in only 1 or 2 seconds.

Do you have any more suggestions for how we can troubleshoot the way that SQL Assistant runs it?
Sun Mar 07, 2021 5:21 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7843

Post Reply with quote
In this case i think the easiest workaround is to modify all Objects... queries and add as the first line before SELECT

SET ARITHABORT ON

To help your server choose best query execution plan.
Sun Mar 07, 2021 11:33 am View user's profile Send private message
BrendonOSullivan



Joined: 02 Mar 2021
Posts: 24
Country: New Zealand

Post Reply with quote
Yes, I thought of that as well. I reverted to "Options + Typed Synonyms" and put in "SET ARITHABORT ON" at the top. Then during cache refresh I confirmed with the SQL Profiler that the query was indeed running with ARITHABORT on but it doesn't help. The execution time fluctuates essentially randomly between either 1.5 seconds or 50.

I guess this variability is related to how the query is executed as a remote procedure call but the text of the query is different every time since it contains the name of the database whose objects are queried. The hash of the query will be different each time and therefore it is difficult for the SQL compiler to store a precompiled execution plan against this command.

Our MS SQL version is 2008 R2 which is quite old. We are upgrading to 2017 within hopefully a couple of months so perhaps that will have a better management of the execution plan.

At least now with your tip to register the add-on the delay only happens once per database per session so that is already much better now.
Sun Mar 07, 2021 4:42 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7843

Post Reply with quote
That result is kind of unexpected. The query text is not changing , say if you work with 5 databases, there would be 5 non-changing versions only. The results for each of them should stay in the buffer pool (data cache) unless the server is experiencing severe memory pressure and cannot cache much data. Or sp_executesql call added unnecessarily by ADO.NET driver somehow throws off the previously compiled execution plan.


Please try adding query hints at the end of the Objects... queries to see if recompiling the execution plan every time, or explicitly telling the server to keep it leads to a better result result.

OPTION (RECOMPILE)
or
OPTION (KEEP PLAN)
or
OPTION (KEEPFIXED PLAN)


Hopefully this would provide us with a clue for further tuning.
Mon Mar 08, 2021 4:37 am View user's profile Send private message
BrendonOSullivan



Joined: 02 Mar 2021
Posts: 24
Country: New Zealand

Post Reply with quote
Thanks SysOp, I think the problem is solved now.

All of those options help and after trialling them I think we will use OPTION (KEEP PLAN).

Here is a screenshot using RECOMPILE.


The first few calls are very long (50s) with massive reads, then the system settles down to a consistent 1 or 2 seconds.

Another screenshot showing the effect of KEEP PLAN


KEEPFIXED PLAN was also helpful.

Ultimately I think KEEP PLAN is the best compromise.
Mon Mar 08, 2021 5:33 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7843

Post Reply with quote
Thanks for the status update. Glad we have found an acceptable solution to this issue. Hopefully your pending SQL Server upgrade from 2008 to 2017 will add another significant performance boost.
Mon Mar 08, 2021 8:30 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
I'm not sure those improvements aren't temporary. The results seem very strange to me. Generally, it's the very first call that would be a long one because the required data is most likely not in the memory and has to be read into it first (IO is slooow) and any subsequent calls (yes, even the second one) would be expected to be much quicker. If the long run times return that's normally the sign of memory pressure, that is, the server dumps the data required for the query from the memory because it is needed for something else, and then the data has to be loaded again from the disk. Your results show that's not the case for you, you get bad performance for several calls, and then suddenly it gets better. That's not how it usually works.

I also wouldn't think this query to be an overly complicated one whose compilation should take any significant amount of time (I am talking about milliseconds here), so I'd guess the best option to go with would be the RECOMPILE to avoid side effects of parameter sniffing. But probably even that doesn't matter. Well, not much, at least.

How much memory does that server have? Is it a dedicated SQL server or are there any other apps running on it? Sometimes a lot depends on the current load of the server, other times no matter what you do, things cannot be improved any further. Though running times oscillating between 1-50s are quite disturbing, and the fact it can run in 1 second definitely shows it can do much, much better.

Please, could you post the query and the execution plan here so that I can compare them to those I get from a 2008 R2 server?
Tue Mar 09, 2021 5:35 am View user's profile Send private message
BrendonOSullivan



Joined: 02 Mar 2021
Posts: 24
Country: New Zealand

Post Reply with quote
@gemisigo
you are right the improvements are not permanent. We still see variable run times and frequently long delays to refresh the cache.

The SQL server is a dedicated, physical machine with 24 CPU cores and 64GB memory. The task manager typically shows only around 10% CPU consumption. Memory usage is high at around 90% but I guess that is correct with the SQL Server host caching as much data as possible in memory. So the machine has actually very good performance and nothing else is running there to compete for the resources.

I extracted the query used by SQL Assistant to load "Objects + Typed Synonyms" together with the execution plan. I ran the query in SQL Management studio which takes only two seconds so that might not fully reflect what happens when the SQL Assistant calls it.

Execution Plan:
https://drive.google.com/file/d/1PlY9kZKK0EjbyCHgE488KcBGQRtaKkAy/view?usp=sharing

Query (replace [Eurodat507_EUNZCH] with the name of your database):
Code:
exec sp_executesql N'-- SQL Assistant Objects  + Typed synonyms v10
-- with OPTION (RECOMPILE)     
SET ARITHABORT ON
SELECT DISTINCT
   o.name,
   CASE WHEN o.type = ''SN'' THEN ''^'' +
    CASE CAST(OBJECTPROPERTYEX(o.object_id,''BaseType'') AS VARCHAR)
    WHEN ''AF'' THEN ''F''
    WHEN ''FN'' THEN ''F''
    WHEN ''FS'' THEN ''F''
    WHEN ''FT'' THEN ''U''
    WHEN ''IF'' THEN ''U''
    WHEN ''IT'' THEN ''T''
    WHEN ''P'' THEN ''P''
    WHEN ''PC'' THEN ''P''
    WHEN ''S'' THEN ''T''
    WHEN ''SQ'' THEN ''I''
    WHEN ''TF'' THEN ''U''
    WHEN ''TT'' THEN ''K''
    WHEN ''U'' THEN ''T''
    WHEN ''V'' THEN ''V''
    WHEN ''X'' THEN ''P''
    ELSE '' ''
    END
   ELSE
   CASE
     WHEN p.[object_id] IS NOT NULL THEN ''TP''
     ELSE o.TYPE
   END END,
   o.object_id,
   o.create_date,
   o.modify_date 
from
   [Eurodat507_EUNZCH].sys.all_objects o WITH (NOLOCK)
   LEFT JOIN [Eurodat507_EUNZCH].sys.partitions AS p WITH (NOLOCK)
     ON o.[type] = ''U'' AND p.[object_id] = o.[object_id] AND p.partition_number = 2
where
   type in (''U'',''S'',''V'',''P'',''X'',''RF'',''FN'',''TF'',''IF'',''SN'',''PC'',''FT'',''FS'',''TR'',''SO'')
   and (
       o.schema_id = @b0       
       or (
          1 = @b1       
          and user_name() = ''dbo''
          and o.schema_id = 4
          and o.name in (''sysobjects'',''sysindexes'',''syscolumns'',''systypes'',''syscomments'',''sysfiles1'',''syspermissions'',
              ''sysusers'',''sysproperties'',''sysdepends'',''sysreferences'',''sysfulltextcatalogs'',''sysindexkeys'',
              ''sysforeignkeys'',''sysmembers'',''sysprotects'',''sysfulltextnotify'',''sysfiles'',''sysfilegroups'')
         )
      )
   and not (o.name = ''dtproperties'')
   and not (o.name like ''dt%'' and o.type = ''P'')

union all

SELECT t.NAME + COALESCE(CHAR(0) + t1.name
   + case
   when t1.name in (''varchar'', ''char'', ''binary'', ''varbinary'')
     then ''('' + case t1.max_length when -1 then ''max'' else convert(varchar, t1.max_length) end + '')''
   when t1.name in (''nvarchar'', ''nchar'')
     then ''('' + case t1.max_length when -1 then ''max'' else convert(varchar, t1.max_length / 2) end + '')''
   when t1.name in (''decimal'', ''numeric'')
     then ''('' + convert(varchar, t1.[precision]) + '','' + convert(varchar, t1.scale) + '')''
   else ''''
   END, ''''),
   COALESCE(o.type, ''TD''),
   COALESCE(tt.type_table_object_id, t.user_type_id),
   o.create_date, o.modify_date
FROM [Eurodat507_EUNZCH].sys.types t WITH (NOLOCK)
LEFT JOIN [Eurodat507_EUNZCH].sys.types t1 WITH (NOLOCK) ON t.system_type_id = t1.user_type_id
LEFT JOIN [Eurodat507_EUNZCH].sys.table_types tt WITH (NOLOCK) ON tt.name = t.name
LEFT JOIN [Eurodat507_EUNZCH].sys.objects o WITH (NOLOCK) ON tt.type_table_object_id = o.[object_id]
WHERE t.is_user_defined = 1 and t.schema_id = @b2       

OPTION (RECOMPILE)   
',N'@b0 int,@b1 int,@b2 int',@b0=1,@b1=1,@b2=1

Thu Mar 11, 2021 5:33 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
Thank you very much, Brendon!

The exec plan shows nothing interesting. I've checked the same query on one of our servers and get similar results. Other than your database having a fairly greater number of objects, thus the result set also being larger, I've detected no significant differences.

According to estimated operator costs (which, again, is only an estimate), the most expensive operation is the Sort invoked by the DISTINCT. While my results indicate no differences with or without the DISTINCT, I don't have any database at hand using partitioning to see if it changes anything, so I wouldn't recommend removing it. I guess it's there for a reason. Also, a result set of 12k records is not something an entry-level toaster or coffee machine would have issues sorting nowadays. Nothing a server with those specs and load shouldn't be able to handle in a few hundred milliseconds.

Let me as more questions. Was this query executed in SQL Server Management Studio on the same machine SA is installed on, or on the server itself?
Fri Mar 12, 2021 5:44 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7843

Post Reply with quote
IMHO, a factor here is implicit use of "exec sp_executesql" with parameters. It's something that the ADO.NET driver adds up, not likely the way SSMS does it. As a result, a different query execution plan gets picked up randomly and it's not the plan being looked at. I'm sure in SSMS Brandon executes the same query replacing :SCHEMA_ID with something more predictive like SCHEMA_ID('dbo').

It's my expectation that a server equipped with 24 CPUs and 64GB should be able to execute queries like that in a split second in a database of that size, until something diverts it from executing the correct query plan.


Last edited by SysOp on Fri Mar 12, 2021 12:07 pm; edited 1 time in total
Fri Mar 12, 2021 9:40 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
SysOp wrote:
IMHO, a factor here is implicit use of "exec sp_executesql" with parameters. It's something that the ADO.NET driver adds up, not likely the way for SSMS does it.


Quite possible, yes.

As for the selected execution plan, I doubt it. It would be very hard to create an execution plan for such a simple query that performs so horribly. Executing the same query both in SQL Server Management Studio (on the machine where SA is installed, not on the server) and in SE using different connection types could yield a more detailed picture. Another thing we could check is executing it with client statistics enabled in SSMS and check the values for Client processing time and Wait time on server replies. This kind of deviation in a time a result is delivered might as well be because of network issues. We have a client with a server that delivers results very "slowly" when executing some "long-running" (few seconds) queries in SSMS. When running the same stuff in SE, there is a threshold somewhere around 5-6 seconds. When the server takes more than that to produce the result set, the query never returns in SE. It dies after a while with "The semaphore timeout period has expired". The longest "after a while" I had the patience for was ~40 minutes for a 7second (in SSMS) query.
Fri Mar 12, 2021 10:00 am View user's profile Send private message
BrendonOSullivan



Joined: 02 Mar 2021
Posts: 24
Country: New Zealand

Post Reply with quote
Thanks for your feedback guys.

The query I reported was captured using the SQL Profiler so it is definitely what the SQL Assistant runs when it refreshes the Objects in its cache.

My run-times and execution plan are generated by copying the query into the Query Editor of Microsoft SQL Server Management Studio and there it always runs in 2 seconds or faster. So in principle the query is fine and the compiler also knows how to run it efficiently. When running from SSMS it always appears as "Batch mode", never as an RPC call.

The long runtimes only happen when run by SQL Assistant and fluctuate between either 2 seconds or 60. When run by the assistant it appears as an RPC call, not batch mode.

So there is something wrong with the execution plan in RPC mode. This does cache the execution plans differently since there is no stored procedure and therefore the compiler stores them against a hash of the query text. Since this query contains the name of the database and schema there will be a different hash but at most about 10 different ones for the circa 10 databases on this machine and it should not be slow more than once per database.

More I don't know and the fact that SQL Assistant uses RPC is understandable since the cache refresh must by definition include the name of the database and therefore is dynamically generated text.

I don't know how else to debug this behaviour.
Mon Mar 15, 2021 2:47 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
I do. Or at least I have some ideas. I'm still not convinced this has anything to do with the execution plan. This query, although not the simplest you'll ever see, is not a very complicated one, and while it's possible to do some serious screwups with cached plans, parameter sniffing, etc., neither is the volume of the data of a magnitude that would assist in doing so.

It does not seem to me that the server is struggling with producing the data you request from it. I think it is having problems delivering it to the client. That's why I asked if the SQL Server Management Studio you run the query from is on the server itself or on the same client machine you're running SA from. That might make a significant difference. Running stuff on the server tends to avoid a whole range of network issues. Try checking the wait types for the session when it is seemingly stuck refreshing the cache. You can use the Activity Monitor in SQL Server Management Studio to do that. Tell us if it is ASYNC_NETWORK_IO or something else.
Mon Mar 15, 2021 8:34 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7843

Post Reply with quote
@Brandon, may I ask you to try replacing in the query stored in the settings all references to :SCHEMA_ID with $SCHEMA_ID$ and see if that makes a difference? We think this will eliminate dynamic injection of sp_execsql call.

FYI, :SCHEMA_ID makes SQL Assistant run that query with parameters. ADO internally converts it to sp_execsql call with parameters. $SCHEMA_ID$ is a substitution macro value which is substituted in the query before it's sent to the database. That would make SQL Assistant execute different query for different databases not using parameters in this case, and the query should get executed directly without conversion to sp_execsql procedure call..
Tue Mar 16, 2021 11:29 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Goto page Previous  1, 2, 3  Next
Page 2 of 3

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.