cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Will limiting the CAS DB for TempDB speed up DC RUM?

brett_b2
Inactive

Hello,

We use a FusionIO drive, mounted as E:\, for our DBs on each CAS. The CAS DB is taking 99% of the hard drive at 1.2TB. If I were to drop the max DB size to 1.1TB and give more space for tempdb, which is only 40GB right now, would it improve the overall performance of the CAS?

I'm not exactly sure how the CAS uses these databases.

Thanks!

9 REPLIES 9

ulf_thornander3
Inactive

That wouldn't change the amount of data that has to pass the bus, so no, it wouldn't improve the speed.

If you have PLENTY of HW, you can always try to use a RAM disk. Yes - they do still exist and RAM has become quite cheap. The only challenge there is the amount of RAM you can jam into your server.

That would remove the I/O to the TEMPDB from the PCI bus and pour it over the RAM instead.

That makes sense, Ulf. Because of the large investment in FusionIO drives, I don't see us moving from them. RAM disk's speeds are quite impressive, though!

david_alonso
Dynatrace Pro
Dynatrace Pro

well.....you are telling us that you CAS Database is taking 1.1TB.....And all this space is full?Are the CAS DB taking 1.1TB?. Well The biggest I've seen was a CAS with 650GB, because it has a storage period of 70 days.....If you are getting performance problems on your CAS now I think is for the size of your Database Ok see some common use of the tempdb:

http://dbadiaries.com/sql-server-tempdb-whats-it-f...

The thing I'm wondering is if your daily and weekly mainteneces plan are excuting and finishing on time with this small tempdb and so huge database, as well as the periodical index reconstruction (each 3H executed on the modifyed tables).

Yeah, David - we have a pretty crazy configuration at this account. Actually, we have 4 CASs in a single cluster and all 4 of them have maxed 1.2TB hard drives. So about 5TB of data in that one cluster. We're going to be expanding to 6 CASs, for that reason. We only have 4 days of storage because of the throughput and the fact that we don't aggregate any client or server IP addresses. I know...against all of the best practices.

Sometimes they don't finish in time, but generally they do in 3.5 hours.

Ok in this case your TEMPDB is enought if you don't have problem in any of the maintenaces task.

I'll not ask you why you have such crazy configuration, because I know the reply, business requirement 🙂

I'll suggest you to reduce the time of the index adjustments task of the modified tables. Maybe it will get the things worst, but I believe that it will improve your queries because in such environment with so may records being inserted and deleted, the index can become useless really fast.

I hope it helps

Hah, yeah, a crazy business requirement 🙂

Interesting suggestion! I'll try it and see what happens

chris_v
Dynatrace Pro
Dynatrace Pro

TempDB is primarily used as staging for the bulk inserts - so once the CAS has collected and processed the data from the AMDs, it dumps a bunch of text files to disk for SQL to pick up, these are imported into a temporary table in the tempdb, then migrated into the proper tables. NO real performance gain to be found by sacrificing space to one or the other - they're both required.

On a (non-supported) comment, one of my customers who's a good DBA spent some time anlayzing the index behaviour of the CAS and came up with some new index definitions which did improve performance for them. We submitted this back to development for analysis and possible inclusion in future versions.

Hey Chris,

Thanks for the response. That makes sense!

Hmm, that sounds intriguing, although I would be worried to deviate from the standard release and then not now how to fix an issue. I'd only trust that with a good DBA 🙂

brett_b2
Inactive

Hello all,

On a somewhat related question - is there a recommendation for tempdb size vs cas db size? Ex: If I have 100% of a drive, should 90% be for the CAS DB and 10% be for tempdb? I want to make sure tempdb has enough buffer room while also giving the CAS as much space as possible.

Thanks,

-Brett