Post reply

Update statistics only to specific tables and columns

  • April 27, 2009 at 1:19 pm

    #130889

    I have 2-3 stored procs, which involves few tables. Now we want to update statistics only to those tables and columns involved in those stored procs. Basically, we are suspecting that SQL is using bad query plans. Is it possible to code in such a way? If any one has got any such script, can you share?

    and also, many number of transactions takes place during certain time and we are using Max Degree of parallelism 4 using select statement in a server where number of servers are 8, is this fine?

    Thanks,

    Sudhie.

  • April 27, 2009 at 1:41 pm

    #984634

    For statistics, http://msdn.microsoft.com/en-us/library/ms187348.aspx

    You can do a specific table or index.

    For the MDOP, what do you mean 8 servers?

  • April 27, 2009 at 1:42 pm

    #984635

    Since you know what tables and columns are involved, just do an explicit UPDATE STATISTICS for the tables and the stats that you want to update.

    UPDATE STATISTICS <Table Name> <Statistics name>;

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    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

  • April 27, 2009 at 1:45 pm

    #984638

    I should have said, 8 Processors 😛

    Thanks for the link, Yes i think Update statistics individually will be good.

  • April 27, 2009 at 1:55 pm

    #984654

    Sometimes Parameter Sniffing can cause poor execution plans in stored procedures. Most of the time it is a good thing, but sometimes it can cause performance issues if the query optimizer produces a poor query plan based on certian parameters.

    http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html

Viewing 5 posts - 1 through 4 (of 4 total)

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