on 04-02-2014 9:46 AM
Hi All,
I read within IMDB docs that temporary databases will highly benefit of this feature.
I tested this with user temporary db and it worked with substantial improvements.
But I could not manage to put dml_logging minimal on standard tempdb database , with or without 'dbo use only' , with another sarole bound to another tempdb . Everytime I get 'database in use' and I cannot apply this option. I tried with putting model db with this option and restart , without success.
Is anyone who tried this and succeeded ?
I think I will ask support.
regards
Marc
There is a documentation error, CR 762224. This dml_logging option cannot be set on the system tempdb.
-bret
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Bret,
thanks for your answer.
I am aware of that, this KBA was created following my questions and an error in documentation.
But here, I have put dml_logging on user created tempdbs, not system tempdb. It has worked, I have sysmon reports with ML discards , but now, perhaps due to some config parameter , I can not get discards again, and when enabling diganostics , I get this message.
This msg is about tempdb , but tempdb is not not with option dml_logging minimal...
That's why I ask someone for an idea about this strange behaviour.
regards
Hi All,
I have some news after testing :
-- with same config an temporary imdb is OK, i got discards
Minimally Logged DMLs
by ULC Discards 230.4 87.8 1843 99.6 %
-- a new db , not temporary, not imdb , created with durability = no_recovery , dml_logging = minimal
I got discards, too
-- a NEW temporary, not imdb, AND created with durability = no_recovery , dml_logging = minimal
I got discards , too
-- Attempting to apply dml_logging option to an existing tempdb (with dbo use only as explained in doc) -> I never got discards
And , when executing sp_helpdb , you can notice something new for me , look at tempdbtest :
tempdb_imdb | user created temp db, minimal dml logging, in-memory, allow wide dol rows |
tempdb_user | user created temp db, minimal dml logging, allow wide dol rows |
tempdbtest | user-created enhanced performance temp db, minimal dml logging, allow wide dol rows |
In 15.5 , I succeeded to get discards; without need of recreating from scratch my temporary db, and I don't rember this "enhanced performance tempdb".
???
Regards
Marc
sp_helpdb was enhanced in 15.7 SP50 and SP100 to output this "user-created enhanced performance temp db" status. Prior to this change the durability setting still caused a difference in the sysdatabases.status4 field, sp_helpdb just didn't reveal the difference.
Select name from sysdatabases where status4 & 8192 = 8192 will show the databases where the durability level of "no_recovery" was explicitly set when the database was created.
Yes, I have seen that in sp_helpdb code.
Just to mention we have "dml_logging minimal" and "no_recovery" displayed , but only tempdbs created with no_recovery -- enhanced performance -- , will really use minimally logging, for others it is silently ignored.
Why accept "alter database set dml_logging = minimal" in this case ? Better to clarify this db must have been created with explicit no-recovery. It would have been less confusing.
regards
Marc
Hello All,
I eventually put dml_logging on some user temporary created database , but I could not notice discards in sysmon output.
After some searches within documentation, I managed to activate diagnostics about why not using minimally dml and here is what I got :
"Database 'tempdb' is not eligible for IMDB performance features, possibly due to inactive license. Use FULL logging"
1) my licence seems OK , no msg from sysam in errorlog and , sp_ehlpdb shows dm_logging minimal
2) It's not tempdb which has dml_logging but some others databases whose name begins with tempdb...
Has someone an idea ?
I succeeded to get discards but now I don't know why I no longer get them.
Regards
Marc
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Marc,
The documented say that you need to have dbo-use only for temporary databases and single user mode.
The message say some one is use this temporaty database.
You can alter the default logging mode for a database only from the master database. In addition, the database you are altering must:
If the database is not already in the required mode, the server tries to put the database in that mode. If the attempt is unsuccessful, the server raises an error, prompting the user to explicitly put the database in its correct mode.
Could you provide the ASE version and the output from sp_tempdb.
Niclas
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
HI Niclas,
Thanks for your interest and reply .
Here is some requested information.
I am alone in dataserver .
4> select @@version
5> go
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Adaptive Server Enterprise/15.7/EBF 21707 SMP SP110 /P/ia64/HP-UX B.11.31/ase157sp11x/3546/64-bit/FBO/Fri Nov 8 08:40:39 2013
(1 row affected)
1> sp_tempdb 'show'
2> go
Temporary Database Groups
---------------------------
default
(1 row affected)
Database GroupName
------------ -----------
tempdb default
tempdbsarole
(2 rows affected)
Login Application Group Database Hardness
------- ------------- ---------------- ------------ -------------------
sa NULL NULL tempdbsarole SOFT
sarole NULL NULL tempdbsarole SOFT
(2 rows affected)
(return status = 0)
1> sp_helpdb
2> go
name db_size owner dbid created durability lobcomplvl inrowlen status
-------------- ------------- ----- ----- ------------ ----------- ---------- -------- ---------------------------------------------------------------------------------------------------------------
master 250.0 MB sa 1 Mar 17, 2014 full 0 NULL mixed log and data
model 12.0 MB sa 3 Mar 17, 2014 full 0 NULL mixed log and data
sybsystemdb 92.0 MB sa 31513 Mar 17, 2014 full 0 NULL mixed log and data
sybsystemprocs 250.0 MB sa 31514 Mar 17, 2014 full 0 NULL trunc log on chkpt, mixed log and data
tempdb 8012.0 MB sa 2 Apr 01, 2014 no_recovery 0 NULL select into/bulkcopy/pllsort, trunc log on chkpt, dbo use only, mixed log and data, allow wide dol rows
tempdbsarole 200.0 MB sa 6 Apr 02, 2014 no_recovery 0 NULL select into/bulkcopy/pllsort, trunc log on chkpt, mixed log and data, user created temp db, allow wide dol rows
(1 row affected)
(return status = 0)
1> alter database tempdb set dml_logging = minimal
2> go
Msg 16052, Level 16, State 2:
Server 'AMD2_PAR_IST_SQL', Line 1:
Cannot change the DML_LOGGING option of the database 'tempdb' because the database is in use. Retry the command when there are no users in the database, or use sp_dboption 'tempdb', 'dbo use only', 'true' to put the database in 'dbo use only' mode.
Regards
Marc
HI All ,
I asked support and they cannot manage to apply what is written in documentation.
So , we have to cope with the fact it's currently not possible to get minimal dml_logging for system tempdb , and then we have to manage user tempdbs and groups and binds to take advantage of this feature.
If someone has other news, welcome !!
Regards
Marc
Hi Marc,
Follow up to what Simon said about CR 727562. "moving the system tempdb
to be IMDB by default",
I not sure the dml_logging as an option for a tempdb will give you a big performance
improvement because already in version 15.0.2 we reduced logging for tempdb.
Adaptive
Server Enterprise 15.7 SP100 > New Features Summary Adaptive Server Enterprise 15.7 SP100 > Version 15.0.2 > Improved Performance for Data Insertion
Improving Throughput of tempdbTransactions
Earlier versions of Adaptive Server flushed the data
pages and single log records (SLRs) because crash recovery was not supported
for tempdb or any databases not
requiring recovery.
SLRs are log records that force a flush of the user log
cache (ULC) to syslogs
immediately after the record is logged. SLRs are created for OAM modifications,
and Adaptive Server creates log records affecting allocation pages in a mixed
log and data database as SLRs.
A ULC flush avoids the
deadlock caused by buffer pinning. Because Adaptive Server does not pin the
buffers for databases that do not need recovery, it avoids this deadlock and
does not have to flush the ULC for SLRs.
We also have added the session tempdb
log cache size in ASE 15.7.
What I mean with this is that tempdb as IMDB will give you performance improvement
Simon maybe can give more number about that !! but I do not think that dml_logging is the
major factor for this improvement.
Niclas
First - one needs to remember that there have been a lot of tempdb optimizations that have already been applied - so.....many of the RDDB options may not be all that applicable....
For example, you need to remember, that tempdb already has a ton of stuff to optimize it....going back to the 12.5's, if you did a select * into #temp and the select could use parallel worker threads, ASE would partition the #temp under the covers and do the bulk inserts in parallel and then unpartition the table (there was a trace flag that a lot of sites enable to prevent this unpartitioning due to perception of system table contention at the time...while it did add to it, the partitioning so rarely happened it wasn't the main cause). Then in 15.0.x, ASE added lazy IO's......and then delayed commit was essentially enabled by default for tempdb. Then 'session tempdb log cache' was added - which avoids logging altogether if the txn log records fit within the session tempdb ULC size - something most DBA's forget to enable. I don't understand that....it is kinda like throwing away free money.
The problem with RDDB and minimal logging is that it makes it IMPOSSIBLE to rollback transactions correctly. There are times when this is necessary.....remember, tempdb is used for a lot of system stuff other than default for #temps. For example, fully prepared statements are in tempdb - as are work tables/store indexes, etc. Also, you need to be really careful - think of what would happen if tempdb was only an IMDB and it filled up.....how would you fix it??? Remember, if tempdb fills, even common procs such as sp_who stop working - so you couldn't even get a list of spids to kill hoping to clean up #temp tables.
However, of course, you *can* create your own user defined tempdb that is both IMDB and minimal_logging if you wish. Creating your own tempdb really isn't that hard to do. SAP does this by default - they create a saptempdb for all users and leave tempdb just for the system stuff and LWP's that are created from all the prepared statements. It really is much much safer that way...and something I encourage. Leave tempdb for system use and sa_role stuff when critical recovery is necessary - create your own tempdb (or more than one) and bind users to it. It really isn't all that difficult.
I think the reasoning behind the request to allow tempdb to be in-memory is two fold.
The process of application login creation for a dataserver is already fairly arduous with a ton of audit requirements and sign-off before approval is granted. Amending this process to also include step to configure & bind to specific temporary databases is technically straightforward, but a bureaucratic nightmare.
If there was an option to NOT bind certain logins/applicationsthat would make maintenance much more straightforward (and may even negate the need for the imdb tempdb?) I.e, bind everything apart from sa_role logins etc.
On a given dataserver there may be 10000 logins and a thousand applications. Many legacy or tactical apps are difficult to track down to particular groups and establishing clear ownership and responsibility is not always straightforward. Whilst I was working on your side, I too, always used to say, it isn't difficult 🙂 It's the rigmarole around it, particularly when there are ever-tightening controls that make things difficult.
As you say, there is still always going to be activity on the system tempdb, in which case how should it be sized assuming you did move everything you could across to imdbs? As for lwps, they are assigned a database id of 2, but are they really 'in' tempdb, in as much as taking space in the system segment? The plan will be in the proc cache and there will be an object structure but I don't think it'son-disk. (?). I guess if tempdb were imdb, then as you say there would have to be a mechanism to be able to extend it.
Hello Everybody,
Thanks for your interest for this subject. Finally , I decided for user temporary dbs, either RDDB or IMDB depending of RAM amount I can afford, as I really saw with sysmon a big number of log access avoided and ulc flushes skipped. I fortunately found two appnames which will allow me to avoid managing several hundreds of logins.
I can understand risks by putting system tempdb as imdb , perhaps the ability to bind a group (here users group) , added to login and application name, to user tempdb (or tempdbs group) will make easier unsing this feature.
User | Count |
---|---|
84 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.