[sims] SIMS gradually getting slower and slower...

[jinnantonnixx]

When I was investigating the slow queries, I dug deep into the queries using execution plans. These showed that SIMS uses an awful lot of full table scans, and there were missing statistics for many of these.

Moreover, some of the queries were inefficiency constructed. e.g. When you chose all classes, Profiler showed that rather than a SELECT *, the app constructed a WHERE IN (class1, class2, etc) even when all classes were chosen. Safer coding, but.... well....

 

Anyway, I found this fantastic free PDF book kindly published by RedGate. It's deep, but you'll really understand how SQL ticks.

 

Red Gate book store - SQL Server Execution Plans Second Edition

 

Good write-up, @michael2k6

I'd suggest that the log files are sized correctly at the start, though, and instead of setting a percentage increase for auto-grow, set the auto-grow value to a couple of hundred meg.(10% of not much is still not much, and every auto-grow causes a throttle-back on SQL) You don't want auto-grows following auto-grows in quick succession.

Edited May 2, 2014 by jinnantonnixx

[matt40k]

SELECT *, the app constructed a WHERE IN (class1, class2, etc)

 

Isn't this so you only get current?

[psydii]

One of the good things about cloud is that inefficient queries now cost the supplier money, so they've become incentivised to improve performance.

[matt40k]

Only true cloud, where load is dynamically spread across servers and additional servers are spinned up when required (Arbor). Grey cloud - web based or client\server (like SIMS) you stuffed. You have to hope the guys and gals at the other end know what they're doing and actually are willing resolve it - ie spend the time\money.

[psydii]

Still costs then money.... Running on their servers they have to buy capacity to deliver performance, and once bought it cannot easily be unbought!

 

Let say you need 8 cores and 16gb of ram per customer because you haven't optimised your index and queries... That's several grand of hardware per customer, which could be reduced to say 1 core and 4 gb saving far more than the cost of the developer (for companies with 100+ customers)

 

Also for companies that operate primarily in one geographic market to not have proper cloud (I.e dynamic provisioning) means they will be paying for compute time they are not using (MIS loads 4pm-6am are tiny by comparison to during the school day. Again a driver for improving efficiency of the software.

[zag]

My advice, whack and SSD drive in the server for the database and logging files.

 

Improved our performance no end...

[matt40k]

My advice, whack and SSD drive in the server for the database and logging files.

 

You feeling ok? Sure I didn't see Intel or Samsung being mentioned