Post reply

sp_table_statistics_rowset is called every 5 secs and performs a lot of reads

  • March 28, 2004 at 6:21 pm

    #107595

    Hi All,

    I am looking at performance of a database which talks to other servers through linked servers. Database is running slow. There are a lot of calls of

    sp_table_statistics_rowset

    procedure, called to one of linked servers whereas not to others.

    That proc is called so often and does like 5000 reads every 5 secs.

    It's some supplementary process, should it be called so frequent, is there way to disable it? Why server needs constantly update statistics from another linked server, even linked server is not queried?

    That linked server is used to query another database on the same server, linked server points to itself. Is that a problem?

    Thank you for your help

  • March 30, 2004 at 1:38 am

    #501017

    are you sure thera are not any jobs that Called this

    sp_table_statistics_rowset function?


    Alamir Mohamed
    Alamir_mohamed@yahoo.com

  • May 5, 2004 at 3:31 am

    #505270

    Same problem,

    it locks my server for some 10+ seconds but it runs too much in day. And i saw it on my production server from 2 linked ones. Nothing about it on Books Online, and i met this first on last week.

    Tayfun B.

    silenceofnoise@hotmail.com

  • August 23, 2005 at 9:06 am

    #584236

    It seems nobody has solution to this problem.

    This stored procedure is killing my server. It is executed every time a linked server is accesing my production database. I don't think it has something to do with "auto update statistics" because this was set from the creation of the database and this problem occured only from a few days ago.

  • August 23, 2005 at 1:24 pm

    #584352

    Any recent changes (new programs,...) that could coincide with the start of the occurances?

  • August 24, 2005 at 1:43 am

    #584491

    The developers sad that nothing has changed in the program that acceses the linked server. Here is what the SP look like:

    exec [Database]..sp_table_statistics_rowset;2 N'Database', N'dbo', N'Tablename', NULL, NULL, NULL

    Since this is undocumented I don't think they just put this SP in the program.

  • July 13, 2006 at 12:45 am

    #648874

    I think there is nothing wrong with this procedure being executed on every query on a linked server. I believe now this is normal and I will explain why:

    When a query is executed on a local server, the query optimiser tries in background to find the best indexes to use so the query consumes as less resources. It does this by looking on statistics that are kept in memory. It doesn't need to run dbcc show_statistics every time a query runs on a given object since it knows that nothing changed since the last query.

    When a query is executed on a linked server, the optimiser doesn't know which indexes are best to use, so it runs this stored procedure on this linked server to find statistics.

    The only way to force the optimiser not to look for statistics on a linked server is to specify using hints in the query what indexes the optimiser should use .

  • July 17, 2006 at 2:58 am

    #649478

    Running queries across linked servers will often likely give you problems, it's not best practice for performance. I believe you may need to use Openquery with pass through to resolve your problem.

    I only pass this on as a hint I personally never follow this practice.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply