+ Post New Thread
Page 3 of 4 FirstFirst 1234 LastLast
Results 31 to 45 of 57
MIS Systems Thread, SIMS gradually getting slower and slower... in Technical; Originally Posted by siuko I am wondering if we could get an Ok you can do this from @ PhilNeal ...
  1. #31

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,386
    Thank Post
    368
    Thanked 637 Times in 519 Posts
    Rep Power
    158
    Quote Originally Posted by siuko View Post
    I am wondering if we could get an Ok you can do this from @PhilNeal ?

    The script does sound like it would be helpful - but I really don't want to be doing anything with the sims database without capita oking it first.... (yes I'm a chicken... buck buck)
    He won't. Reindexing is a most basic DBA task, it's not rocket science to do - however it assumes a number of things - like it's done out of hours, that you have a correct backup before you start, that if your system is in a poor state at the start, you have a pragmatic approach to tackling it - ie not doing everything at once. You might find that your log file grows to the point that it fills your disc, so SQL won't start, or if you've been silly enough to put SQL onto your OS drive, stop your computer from booting.

    The safe option is to hire a DBA to do it for you.

    Performance wise it really depends. If it's in a pretty poor state you'll is a absolute massive increase, well worth any risk.

  2. #32
    spr
    spr is offline

    Join Date
    Jan 2014
    Location
    Blackpool
    Posts
    5
    Thank Post
    0
    Thanked 2 Times in 2 Posts
    Rep Power
    0
    Hi just to report back, did this on our SIMS db last night and for example on a small report I ran before took around 4 minutes and 1 minutes 30 seconds after reindexing.

    Thankyou!

  3. Thanks to spr from:

    zag (29th January 2014)

  4. #33
    mrbios's Avatar
    Join Date
    Jun 2007
    Location
    Stroud, Gloucestershire
    Posts
    2,456
    Thank Post
    349
    Thanked 255 Times in 210 Posts
    Rep Power
    98
    The reindexing patch also fixed the issue (somewhat) for myself quite a while back. However SIMS Discover is starting to take a very long time to do transfers again....so question is should we be running this patch every 6-12 months or something to keep things fast?

  5. #34
    zag
    zag is offline
    zag's Avatar
    Join Date
    Mar 2007
    Posts
    3,762
    Thank Post
    897
    Thanked 416 Times in 350 Posts
    Blog Entries
    12
    Rep Power
    86
    Quote Originally Posted by spr View Post
    Hi just to report back, did this on our SIMS db last night and for example on a small report I ran before took around 4 minutes and 1 minutes 30 seconds after reindexing.

    Thankyou!
    Thats good enough for me!

    Will give it a go now.

  6. #35

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,954
    Thank Post
    109
    Thanked 482 Times in 331 Posts
    Blog Entries
    2
    Rep Power
    281
    Quote Originally Posted by spr View Post
    Hi just to report back, did this on our SIMS db last night and for example on a small report I ran before took around 4 minutes and 1 minutes 30 seconds after reindexing.

    Thankyou!
    Did you reindex (with the SIMS patch) or rebuild (with the maintenance plan)?

  7. #36
    spr
    spr is offline

    Join Date
    Jan 2014
    Location
    Blackpool
    Posts
    5
    Thank Post
    0
    Thanked 2 Times in 2 Posts
    Rep Power
    0
    Quote Originally Posted by jinnantonnixx View Post
    Did you reindex (with the SIMS patch) or rebuild (with the maintenance plan)?
    Maintenance plan

  8. Thanks to spr from:

    jinnantonnixx (29th January 2014)

  9. #37
    zag
    zag is offline
    zag's Avatar
    Join Date
    Mar 2007
    Posts
    3,762
    Thank Post
    897
    Thanked 416 Times in 350 Posts
    Blog Entries
    12
    Rep Power
    86
    Quote Originally Posted by jinnantonnixx View Post
    In the 'Define Reuild Index task' window, choose your SIMS database(s) to reindex.
    For 'object', choose 'tables and views'
    On the same window, check the box that says 'sort results in tempdb', keep the box that says 'keep index online...' unchecked.
    I'm using SQL server 2012 and don't have the 'sort results in tempdb' check box or 'keep index online'. Will that make any difference?

    sims-reindex.jpg

  10. #38

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,954
    Thank Post
    109
    Thanked 482 Times in 331 Posts
    Blog Entries
    2
    Rep Power
    281
    You've chosen the 'Reorganise Index' task. Try the 'Rebuild Index' task instead. This will ensure that the query optimiser has the best stats.

  11. #39
    spr
    spr is offline

    Join Date
    Jan 2014
    Location
    Blackpool
    Posts
    5
    Thank Post
    0
    Thanked 2 Times in 2 Posts
    Rep Power
    0
    Well my success was short lived, 3 hours in and performance seems to have taken a nose dive when the admin girls are running big reports, they were all running pretty speedily first thing, meh! Server looks to be ticking over not doing alot, anyone have any advice on any tell tale signs it's a server performance issue?

  12. #40

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,954
    Thank Post
    109
    Thanked 482 Times in 331 Posts
    Blog Entries
    2
    Rep Power
    281
    Oh no!

    See if there's anything useful here. Get to know SQL Activity Monitor.

    Speeding up SIMS
    Last edited by jinnantonnixx; 29th January 2014 at 11:20 AM.

  13. #41
    zag
    zag is offline
    zag's Avatar
    Join Date
    Mar 2007
    Posts
    3,762
    Thank Post
    897
    Thanked 416 Times in 350 Posts
    Blog Entries
    12
    Rep Power
    86
    Quote Originally Posted by jinnantonnixx View Post
    You've chosen the 'Reorganise Index' task. Try the 'Rebuild Index' task instead. This will ensure that the query optimiser has the best stats.
    Thanks, I've set it up properly now

  14. #42
    Olliesaurus's Avatar
    Join Date
    Sep 2012
    Posts
    115
    Thank Post
    23
    Thanked 5 Times in 5 Posts
    Rep Power
    4
    My SIMS manager had a huge great big go at Capita regarding a load of cases that werent being followed up, one of them being the speed issue.

    They have connected through and have setup a trace report through SQL profiler and have said that they will send it off to the developers to see if there are any issues, I dont know if its worth anyone else pushing for one of these, if the issue appears to be the same then they might do something about it.

  15. #43
    simpsonj's Avatar
    Join Date
    Apr 2009
    Location
    Oxford
    Posts
    381
    Thank Post
    159
    Thanked 64 Times in 53 Posts
    Blog Entries
    8
    Rep Power
    21
    Apologies for raising this thread from the dead, but I thought it as worth mentioning that our simple reports (Name, reg, address block, previous school for one year group) would time out and not run at all. Ran the 'Rebuild index' as stated by @jinnantonnixx and they now run within a few minutes. Still warns the report will take a long time to run, but at least I'm getting the reports now!

  16. #44

    Join Date
    Sep 2006
    Location
    London
    Posts
    1,318
    Thank Post
    35
    Thanked 351 Times in 237 Posts
    Rep Power
    78
    We've done some work on the report generator (included in summer) that will speed up areas that have been reported as slow.

  17. 2 Thanks to PhilNeal:

    Steven_Cleaver (1st May 2014), zag (2nd May 2014)

  18. #45

    Join Date
    Jun 2007
    Location
    Wakefield, West Yorkshire
    Posts
    621
    Thank Post
    95
    Thanked 129 Times in 100 Posts
    Rep Power
    66
    Hi all,

    I've not used SIMS, nor am I familiar with implementation, recommended specs / configuration etc, and I currently (recently) work for an alternative MIS, so I can't comment on SIMS technically, but I've got a bucket of SQL experience, so I've popped up to help out if it's possible. Obviously, the usual caveats, it's at your own risk, don't do anything that Capita do not approve of, back it all up, twice, etc. This is none MIS related general SQL advice. If anyone has an issue with anything in the post please let me know and I'll change / remove it.

    I've seen performance issues before with SQL databases and the replies here don't surprise me. What I have noticed previously though is that rebuilding or reorganising indexes will help to a point but when you've used the same software with a slowly growing db over so many years, and not done that previously, it can't hurt to do it, but a solution like that sometimes masks an issue rather than solves it.

    SQL will cache as much data as it can (including whole DB's) to RAM, where the RAM is available and the O/S isn't sending out pressure warnings for memory to be released. Have a look at performance monitor and look at the page life expectancy counter. The higher the number the better, essentially, the higher the number, the longer a piece of data can be cached in RAM. I'm keeping this fairly basic as I have no idea how big these databases are or can grow to.

    Something that might be common across these issues is that I've spotted a few virtual machines running on various types of host. In these cases, where data is stored on a SAN, look at disk contention. Where are you storing the db's? Ideally, you should be storing them away from anything with high disk contention - don't bang em on the same RAID 5 as your file storage on a different partition, for example. Ideally, store them on their own disks, and on a different LUN. Even more ideally, store tempdb on a different LUN to your user databases, but I think that's probably overkill for this use.

    The fastest cheapest way people tend to achieve that is to add a set of local disks in a RAID 5 to the host machine and move all databases to that (including system DB's). Obviously in the greater realms of database tuning, you ideally need to keep tempdb away from the user databases, and look at keeping log files (if you're using full recovery model) on a different drive to the database files - more on log files in a moment. You can then look at storing tables and views in different mdf's and storing them separately, usually based on their usage (amount, and read / write). Again, all of this is probably overkill for this kind of purpose. But I can fully recommend putting them on their own set of disks, if you have that luxury, and seeing if it drastically improves performance.

    Pay particular attention to your log files. Again, not knowing the SIMS architecture, I don't know whether the database is setup in full or simple recovery model.
    A log file isn't a never ending log of what's happened in the database. The log file processes all queries to the database, and if they are successful, they remain and are shifted to the mdf. If they are unsuccessful they are rolled back and never make it to the mdf. By default a database commit is made at each checkpoint or where a specific query is written ending COMMIT TRANSACTION. So a hell of a lot depends on the ldf file. Many people think it's some sort of audit trail in case someone deletes a student, for example, which is not the case.
    A database has a few different options for recovery model. Full and Simple are the most common. When set to full, the LDF file grows until it you backup and shrink it. You cannot shrink without a backup. Usually where that's the case it's built into a maintenance plan. The other option is simple. Simple looks empty data from the LDF at each check point. It does not release that free space back to the filesystem unless 'auto shrink' is checked. So that means that if may be 2gb in size, for example, but only 1gb is in use, so the next 50% of the log file already has space allocated on disk, it's just not using it yet.
    If we begin simple and consider the impact that it will have on a single disk, it becomes obvious quickly that if you have the mdf and ldf (or multiples) of your user database on the same disk, and you also have your tempdb on there, and potentially other databases too (which are all using the same tempdb), that disk is going to be crazy busy. Then consider that when it needs to grab some space for db1 it does so at whatever rate it sets. Then the same for db2. Etc. Data very quickly becomes fragmented across a disk. Not only does it become fragmented across a disk but it becomes fragmented within its own pages. More on that in a moment, lets stick to disks for now. A log file writes serially. So if a log file is on it's own disk, the read/write heads stay in the same place, if it's on a disk contended with the other files, the heads are all over the place.
    So if you have your db set to full, and the log file is forever growing, you have the data getting further apart and becoming more fragmented. If you set it to simple it will use quite a bit of resources constantly clearing it, but then at least it is using the same free space over and over (unless you've got it set to shrink, or it needs to grow beyond its currently allocated space).

    So now we've been over disks and fragmentation of db files over the disks and disk contention, the other thing to consider if the fragmentation of the indexes themselves. Within Consider a dictionary. The dictionary is index, the bookshelf is the hard drive or array. It's in alphabetical order, if you're looking for N, it's between M and O, regardless of what page that's on you find it quickly. If we suddenly think of some new N words, we cant bang in a new word onto a full page. So it creates a new page for it on the end. That's where the index fragmentation comes from. So you end up with free space then on that new page too.
    When you rebuild an index, it will do what it says on the tin. Which is why it's important. The pages then all end up in order. What you have to consider at the point of rebuild is the fill factor. It's 1 - 100 (warning - 0 is unlimited and same as 100) and used as a percentage. If you set it to 0 it will fill every page to its capacity. If you set it to 50, it will half fill each page. Which means when it needs to add something, it's got room to add it. The downside of that is you end up with a load of free space.
    So... most issues here are with running reports, which is a read activity. The fastest read activity is going to be where those pages are full, so i'd say use 0 and fill every page. If you set it to 50, for example, the read performance may degrade by two times as it's looking through all the free space. But the writes will be improved.
    So that's why rebuilding / recreating your indexes is helping.

    A tip for those creating new databases (away from SANs) is to defrag the disks first, then have the initial size of the database around 150% of the necessary size do that for the log file too. As it will grab the free space at that time, it will be contiguous on the disk.

    Another tip is ensure that the log file is set to grow by the necessary rate. If it's set to grow 1mb at a time for example, in heavy use, it may be growing, waiting, growing, waiting, etc. Use a % instead, usually about 10.
    Also check that there isn't a size limit on the log file. If it reaches a limit in full recovery model, it will simply stop working. If it reaches a limit in simple recovery model it will usually perform a commit and gain enough free space in time for the query to be successful, but it will slow things down considerably. Limits to log files were necessary in the days of smaller disks but these days and on databases of these sizes its a bit mad to apply one. Monitor it by all means, but don't limit it.

    As I said at the start of the thread I hope I'm not treading on anyone's toes or going against anything Capita would advise, in which case, definitely take their instruction over mine! My knowledge is indeed limited and more from the times of SQL 2005 into 2008 than 2012. But if any bit of it helps one school, it was worth writing.
    But that did take quite a bit of writing and I do feel like I deserve a corona, so that's where I'll be if anyone wants me!

    There are more things springing to mind, but I think that will do for tonight. Especially for this size of implementation, it's probably overkill as it is. As usual, if anyone has any questions or wants to chat further you're welcome to PM me.

    Mic

  19. 8 Thanks to michael2k6:

    Bananas (2nd May 2014), GREED (1st May 2014), IWDave (7th May 2014), Jobos (1st May 2014), ninjabeaver (12th June 2014), pcstru (1st May 2014), psydii (2nd May 2014), zag (2nd May 2014)

SHARE:
+ Post New Thread
Page 3 of 4 FirstFirst 1234 LastLast

Similar Threads

  1. [SIMS] SIMS .net Default Username and Password?
    By jmahone in forum MIS Systems
    Replies: 5
    Last Post: 7th March 2013, 07:43 PM
  2. Vista getting slower and slower then freezing...
    By boomam in forum Windows Vista
    Replies: 5
    Last Post: 23rd October 2008, 03:23 PM
  3. Can't get mysql and PHP to play together on IIS
    By dagza in forum Web Development
    Replies: 5
    Last Post: 20th January 2008, 03:38 PM
  4. Replies: 5
    Last Post: 16th December 2007, 06:26 PM
  5. Replies: 2
    Last Post: 25th October 2007, 11:54 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
  •