Little SQL help is needed...

vkaart
vkaart Member Posts: 6
edited 2007-04-11 in SQL Performance
Hello!

One of our clients is running navision on SQL 2005 and its database is too big to reindex it by one night. So I'd like to reindex the hot tables only (G/L entry, value entry table etc). But I didn't manage to configure the T-SQL in maintenance plan setup. I followed instructions from the help, but it didn't help. Maybe some of you can help me.

Thanks in advance

Comments

  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    Please visit our website at www.sqlperform.com or send me an email We have just the tools you need.

    Sorry for the advertisement. :oops:
    Marije Brummel | Freelance Business Central Specialist
  • Waldo
    Waldo Member Posts: 3,412
    Nice tools indeed, but there is another way (sorry Mark :wink: ).

    You could create one extra table with the fields:
    - table name
    - group ID

    You can create a stored procedure which reads this table, filters on the group and execute a reindex for these tables.

    e.g.:
    If you define: 
    Table Name              | Group ID
Cronus$Customer Ledger Entry | 1
Cronus$Item Ledger Entry     | 1
Cronus$Value Entry           | 2
    You could schedule a daily stored procedure like this: 
    sp_reindex 1
    And a weekly stored procedure like this: 
    sp_reindex 2

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • Waldo
    Waldo Member Posts: 3,412
    By the way ... createstats 'indexonly' is good enough

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • vkaart
    vkaart Member Posts: 6
    thanks Waldo. I'll try to do so.

Categories