locked
Poor Performance of SMO Prefetch on 2016 Server RRS feed

  • Question

  • I use the SMO library in order to script the db objects. Recently an upgrade of sql server took place, i.e. from 2008 R2 to 2016 SP1 and the same scripting method is not completed due to timeout issues.

    Specifically the above query of SMO causes the timeout during the prefetch of the tables:

    exec sp_executesql N'SELECT
    SCHEMA_NAME(tbl.schema_id) AS [Table_Schema],
    tbl.name AS [Table_Name],
    i.name AS [Index_Name],
    p.name AS [Name],
    CAST(p.value AS sql_variant) AS [Value]
    FROM
    sys.tables AS tbl
    LEFT OUTER JOIN sys.periods as periods ON periods.object_id = tbl.object_id
    LEFT OUTER JOIN sys.tables as historyTable ON historyTable.object_id = tbl.history_table_id
    INNER JOIN sys.indexes AS i ON (i.index_id > @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tbl.object_id)
    LEFT OUTER JOIN sys.key_constraints AS k ON k.parent_object_id = i.object_id AND k.unique_index_id = i.index_id
    INNER JOIN sys.extended_properties AS p ON p.major_id=CASE (i.is_primary_key + 2*i.is_unique_constraint) WHEN 0 THEN i.object_id ELSE k.object_id END AND p.minor_id=CASE (i.is_primary_key + 2*i.is_unique_constraint) WHEN 0 THEN CAST(i.index_id AS int) ELSE 0 END AND p.class=CASE (i.is_primary_key + 2*i.is_unique_constraint) WHEN 0 THEN 7 ELSE 1 END
    ORDER BY
    [Table_Schema] ASC,[Table_Name] ASC,[Index_Name] ASC,[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'0'

    I have updated the smo references in order the correct dll to be used but the issue remains. From the above query the join with the extended_properties causes the biggest delay. 

    How I can overcome this issue? 

    Regards,

    Xenia

    Tuesday, September 5, 2017 1:01 PM

All replies

  • Hi xmaroulakou,

    Have you run the code in SQL Server Management Studio (SSMS)? This will help us judging which has caused the poor performance.

     

    If this query runs fast in SSMS, this can be a problem of SMO, I suggest you using SQL Server Profiler to monitor it, then change some setting of application based on the result.

     

    If this query still runs slow in SSMS, this can be a performance issue of query. Upgrading from SQL Server 2008 R2 to SQL Server 2016 has jumped a lot of versions, many features has changed a lot. We can use SQL Server Profiler to create a trace (https://docs.microsoft.com/en-us/sql/tools/sql-server-profiler/create-a-trace-sql-server-profiler) , then using Database Engine Tuning Advisor.

     

    Besides, make sure you have update your statistics and look into the Query Store feature: https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store

     

    Best Regards,

    Teige

     


    MSDN Community Support<br/> Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact <a href="mailto:MSDNFSF@microsoft.com">MSDNFSF@microsoft.com</a>.

    Wednesday, September 6, 2017 8:02 AM
  • Another thought is that the SQL is generated by the SMO DLL, and perhaps a more recent version of that DLL will generate more efficient SQL?

    I know that several operations in SSMS were very slow in version 16 of SSMS. Many of these things were over time identified by MS and corrected. It would be interesting to use most recent version of SSMS (the SMO which is installed by SSMS) and see if this is improved...


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, September 6, 2017 12:14 PM
  • Hello,

    I have execute the above query to SQL Management Studio and the behavior is exactly the same.

    The statistics are up to date and I tried additional the following.

    I change the DB configuration in order to use the legacy Cardinality Estimator and with this scenario the prefetch of the tables was successfully.

    The query from my first post in this case has been executed at 00:00:57. If I switch off the legal Cardinality Estimator then the same query does not end ever, i killed it after 1hour (Profiler trace: cpu = 3059344, Reads=3842268903, Duration=3152559)

    Unfortunately,  I do not have the ability to tune this query (this query is executed from the smo api when the prefetch of tables is requested).

    Regarding the version of the SMO I am using the most recent version of SSMS(v17.0) but I will check if the latest nuget package of SMO resolves the issue.

    Thanks,

    Xenia

    Wednesday, September 6, 2017 1:07 PM
  • I change the DB configuration in order to use the legacy Cardinality Estimator and with this scenario the prefetch of the tables was successfully.

    The query from my first post in this case has been executed at 00:00:57. If I switch off the legal Cardinality Estimator then the same query does not end ever, i killed it after 1hour (Profiler trace: cpu = 3059344, Reads=3842268903, Duration=3152559)

    Unfortunately,  I do not have the ability to tune this query (this query is executed from the smo api when the prefetch of tables is requested).

    Regarding the version of the SMO I am using the most recent version of SSMS(v17.0) but I will check if the latest nuget package of SMO resolves the issue.

    Rather than NuGet, try the latest SSMS version v17.2 to see if the problem persists. If so. I suggest you file a bug report on Connect (https://connect.microsoft.com/SQLServer).


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com


    Wednesday, September 6, 2017 2:10 PM
  • Unfortunately,  I do not have the ability to tune this query (this query is executed from the smo api when the prefetch of tables is requested).

    Actually, you have thanks to the new fantastic invention in SQL 2016: the Query Store. You can enable the Query Store for the database, run the query with the legacy cardinality estimator, and then use query store to force that plan.

    One can of course argue that having to use Query Store to save Microsoft's own tools should be necessary, but this may still be the best option.

    I would not really expect SSMS 17.2 to change the scene, as the query itself is probably sound. I ran it in database with lots of table and stored procedures, and response was immediate. Then again, that database has no extended properties at all.

    Since someone mentioned it: they query is not the same as with SQL 2008, since it has been augmented to deal with temporal tables.

    • Proposed as answer by Teige Gao Thursday, September 7, 2017 1:31 AM
    Wednesday, September 6, 2017 9:14 PM
  • One can of course argue that having to use Query Store to save Microsoft's own tools should be necessary, but this may still be the best option.

    Good idea about the Query Store.  I still think this should be reported as a Connect bug. The root cause looks to be the new cardinality estimator and improving that will make the world a better place beyond SMO query.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Thursday, September 7, 2017 12:29 PM
  • Good idea about the Query Store.  I still think this should be reported as a Connect bug. The root cause looks to be the new cardinality estimator and improving that will make the world a better place beyond SMO query.

    I think for a Connect item to be useful, Microsoft would need a repro. That should however, be relatively simple to achieve. Xenia could use DBCC CLONEDATABASE to create a copy of the database without any data in it, but only the schema. I would expect that all metadata is retained, although I am uncertain about the extended properties, which appears to be the key item here.

    Hopefully a compressed backup of the clone is small enough to be attachable to the Connect item (I think the limit is 50 MB.)

    Thursday, September 7, 2017 12:45 PM
  • The SSMS 17.2 has not impact in the behavior and as a work around I will try the query store. 

    In any case I will reported it as a Connect bug since the issue is caused from the cardinality estimator or the smo api does not handle with the most efficient way the prefetch of objects.

    Thank you all for your help

    Thursday, September 7, 2017 3:48 PM
  • Hi xmaroulakou,

    Could you please share the link of this connect, then someone who met the similar problem will vote this connect.

    Best Regards,

    Teige


    MSDN Community Support<br/> Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact <a href="mailto:MSDNFSF@microsoft.com">MSDNFSF@microsoft.com</a>.

    Monday, September 11, 2017 8:04 AM
  • Hi xmaroulakou,

    Could you please share the link of this connect, then someone who met the similar problem will vote this connect.

    Best Regards,

    Teige


    MSDN Community Support<br/> Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact <a href="mailto:MSDNFSF@microsoft.com">MSDNFSF@microsoft.com</a>.

    I share the link with the connect feedback ID 3140807

    Best Regards,

    Xenia

    Monday, September 11, 2017 4:28 PM
  • I share the link with the connect feedback ID 3140807

    I suggest you either add a repo script like Erland suggested or add details that will help Microsoft recreate the issue. I couldn't recreated the problem on my system it might be due to differences, like many extended properties so some such. I'll upvote if you do that.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com


    Monday, September 11, 2017 6:33 PM