[sims] SIMS SQL Databases and Maintenance Plans

[Olliesaurus]

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

[jinnantonnixx]

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.

 

 

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).

Edited November 8, 2013 by jinnantonnixx

[zag]

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!!

[matt40k]

@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.

[zag]

@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.

 

http://www.edugeek.net/forums/mis-systems/126457-sims-server-performance-benchmarks.html

 

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

[Olliesaurus]

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

[zag]

Yes if its virtualized then you should limit the RAM otherwise it will SQL server will go nuts and eat everything until things stop

[matt40k]

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!!

[localzuk]

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.

[matt40k]

I'd go on away more often if that happened. I go away and people steal so of my storage.

[vikpaw]

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.

[jinnantonnixx]

@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.

[jinnantonnixx]

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.