+ Post New Thread
Results 1 to 13 of 13
MIS Systems Thread, SIMS SQL Databases and Maintenance Plans in Technical; Hi All, Does anyone have any maintenance plans running on there SIMS Databases at all? Its come to my attention ...
  1. #1
    Olliesaurus's Avatar
    Join Date
    Sep 2012
    Posts
    115
    Thank Post
    23
    Thanked 5 Times in 5 Posts
    Rep Power
    4

    SIMS SQL Databases and Maintenance Plans

    Hi All,

    Does anyone have any maintenance plans running on there SIMS Databases at all?

    Its come to my attention that SIMS has been running slowly over the last month or so, after speaking to Capita and jumping through hoops I have been doing some research and it appears we have no maintenance plans on the databases...

    Wasnt sure whether it would be a good idea to look into these so wanted to check with the masses!

    Thanks,

    Olliesaurus

  2. #2

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,968
    Thank Post
    112
    Thanked 489 Times in 335 Posts
    Blog Entries
    2
    Rep Power
    283
    Slow reports by some chance?

    It's probably worthwhile scripting a reindex of your SIMS database(s).

    But first, you should know the current state. This script will show how the state of your index fragmentation. I've ignored indexes where the fragmentation is less that 25% and the page count is under 10 (these pages are likely to be cached in memory) then ordered by the page size in descending order. If you find a highly fragmented index with a lot of pages >100s then it's probably worth reindexing.

    Usual disclaimers, at your own risk, etc, if you don't know about queries, do not proceed.


    Code:
    use [NameOfYourSIMSDatabase] 
    
    SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level,
    avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count
    FROM sys.dm_db_index_physical_stats
    (DB_ID(), NULL, NULL, NULL , 'SAMPLED')
    
    where avg_fragmentation_in_percent > 25 and page_count > 10
    
    ORDER BY page_count DESC
    Capita supply a patch to reindex (14265), but it's bananas as it changes the recovery model to 'Full' (bye bye differential backups/log shipping backups - you didn;t need them, did you?) and shrinks your log file to minimum (hello autogrow, hello log file fragmentation).
    Last edited by jinnantonnixx; 8th November 2013 at 11:26 AM.

  3. #3
    zag
    zag is offline
    zag's Avatar
    Join Date
    Mar 2007
    Posts
    3,765
    Thank Post
    898
    Thanked 416 Times in 350 Posts
    Blog Entries
    12
    Rep Power
    87
    The Sims database has got very bloated in recent years.

    We are all suffering from slow database access at the moment.

    Lets hope they do something about it. Our database is over 3GB now!!

  4. #4

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,406
    Thank Post
    368
    Thanked 639 Times in 521 Posts
    Rep Power
    158
    @Olliesaurus Capita don't recommend it simply because most of it's customers are primary schools which use SQL Express - so you don't have the SQL Agent which runs the tasks. Bob Carswell @ Cornwall wrote an excellent document that listed all the tasks you should run regularly and how to set them up. Alas he's left, but hopefully you'll be able to find a document.
    @zag - 3GB is nothing really, your best bet would to be go down the legal route - ie you can't keep students details from 10 years ago (or whatever the ruling is). I suspect however your main grip is reporting performance.

  5. #5
    zag
    zag is offline
    zag's Avatar
    Join Date
    Mar 2007
    Posts
    3,765
    Thank Post
    898
    Thanked 416 Times in 350 Posts
    Blog Entries
    12
    Rep Power
    87
    Quote Originally Posted by matt40k View Post
    @zag - 3GB is nothing really, your best bet would to be go down the legal route - ie you can't keep students details from 10 years ago (or whatever the ruling is). I suspect however your main grip is reporting performance.
    Its been slow all over Sims for us. Check out this thread with our new hardware its just as slow as before so I can only think its the database thats causing the issues.

    Sims Server Performance Benchmarks

    I've seen databases with 100s of millions of records take up less than 500mb, I'm not sure what Sims is doing in there to take 3GB

  6. #6
    Olliesaurus's Avatar
    Join Date
    Sep 2012
    Posts
    115
    Thank Post
    23
    Thanked 5 Times in 5 Posts
    Rep Power
    4
    The reports have literally crawled to a stop, it takes at least 30-40 seconds to compile a report, often longer.

    Capita have suggested I cap my SQLs to run at 4GB as opposed to having free roam of the full RAM allocation, I will look into the scheduled maintenance as well,

    Many thanks for your replies though, very helpful!

    Olliesaurus

  7. #7
    zag
    zag is offline
    zag's Avatar
    Join Date
    Mar 2007
    Posts
    3,765
    Thank Post
    898
    Thanked 416 Times in 350 Posts
    Blog Entries
    12
    Rep Power
    87
    Yes if its virtualized then you should limit the RAM otherwise it will SQL server will go nuts and eat everything until things stop

  8. Thanks to zag from:

    Olliesaurus (8th November 2013)

  9. #8

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,406
    Thank Post
    368
    Thanked 639 Times in 521 Posts
    Rep Power
    158
    If I'm being honest @zag, it made me laugh, your reminded me when I said in a BI meeting and the project manager was moaning that a report took a who 40 secs to run, then of the users stated it took over 40 mins in Oracle... when it ran.

    ps: @localzuk hardware is insane for the size of his school!!

  10. #9

    localzuk's Avatar
    Join Date
    Dec 2006
    Location
    Minehead
    Posts
    17,680
    Thank Post
    516
    Thanked 2,451 Times in 1,897 Posts
    Blog Entries
    24
    Rep Power
    832
    I will not deny that. Our servers are awesome. I didn't buy them though, they were bought in my absence and were a nice surprise to return to! Dual Xeon "E5-2690 v1" @ 2.9ghz, 96GB RAM, shared storage is made up of 16x SSDs in RAID 10, 10Gbit ethernet. Way, way, *way* overkill.

  11. #10

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,406
    Thank Post
    368
    Thanked 639 Times in 521 Posts
    Rep Power
    158
    I'd go on away more often if that happened. I go away and people steal so of my storage.

  12. #11

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,851
    Thank Post
    672
    Thanked 1,383 Times in 1,145 Posts
    Rep Power
    350
    The only maintenance plans i have are a couple to back it up, and a weekly integrity check. Also a manual log backup in case it grows and needs shrinking, though i have it in Simple mode lately. @jinnantonnixx - i think the reindex patch puts the db into Simple, not full, then back again, or maybe not even back again.

    SIMS client is rubbish at returning memory and requires frequent closing for a few minutes to claw it back. Is the server doing the same? 5 or more years back a server restart used to fix a lot of issue, not thought about it now as we don't have that many reports that take a long time. I don't think i've had any one complain about SIMS taking a long time for ages. They must just get used to it. The basic daily tasks work fine, a few seconds waiting for a page or search to load are acceptable. People tend not to run big reports.

    The size of the db isn't a big deal, but bear in mind if you store pictures they are pretty bloaty. Especially since SIMS allows you to store photos at much larger resolution than recommended. If you have high turnover or even over a few years this will add up a lot just with the old leavers. My SIMS db is around 4.5GB, i run a separate photo db for idcards, and that includes parents, drivers etc. that is now at 12GB and if you run a select that includes photos, that will lock up for a few mins depending on how many rows are coming back, otherwise it is like lightning cos it was built in-house and we query from php or a dedicated program.

  13. #12

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,968
    Thank Post
    112
    Thanked 489 Times in 335 Posts
    Blog Entries
    2
    Rep Power
    283
    Quote Originally Posted by vikpaw View Post
    @jinnantonnixx - i think the reindex patch puts the db into Simple, not full, then back again, or maybe not even back again.
    Well spotted, Vik - schoolboy error. As you say, it changes the recovery model to 'simple', and leaves it there for your transaction log backups to fail. Nice feature of a reindex patch.

  14. #13

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,968
    Thank Post
    112
    Thanked 489 Times in 335 Posts
    Blog Entries
    2
    Rep Power
    283
    Veering wildly on topic, I'm using a maintenance plan to reoganise indexes on the SIMS databases.

    I've checked the index fragmentation stats after the overnight job ran, and it appears to be effective.

    The maintenance plan wizard will guide you through it.

    I'm also looking at a maintenance plan to update statistics. If you rebuild your indexes, you only need to update column stats. If you reorganise your indexes, you can choose to update stats on indexes and columns.

SHARE:
+ Post New Thread

Similar Threads

  1. Replies: 47
    Last Post: 17th October 2011, 10:26 AM
  2. moved sims sql database but logins not moved
    By Jamie_a in forum MIS Systems
    Replies: 3
    Last Post: 13th November 2010, 10:06 AM
  3. move sims sql database to new partition
    By marekbrad in forum MIS Systems
    Replies: 4
    Last Post: 6th July 2010, 07:45 PM
  4. SIMS SQL access and capita problems
    By the_travisty in forum MIS Systems
    Replies: 6
    Last Post: 29th August 2008, 11:53 AM
  5. SQL 2005 and SIMS FMS Finance
    By thom in forum MIS Systems
    Replies: 2
    Last Post: 30th August 2007, 04:50 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •