Post #81,256
2/12/03 7:57:48 PM
|
MS SQL Server: Logs & performance tuning?
Current gig's using MS SQL Server v8 as a SAS back-end. Result is that we're dumping large numbers (100k - 500k) onto an existing indexed table. Previously, transaction logs have been identified as a bottleneck in terms of both space and performance. We're looking to disable logging as much as possible. \r\n\r\n It appears from Google Groups ("sql server" disable transaction logs) that completely disabling logs is impossible, but there do appear to be some settings to reduce updates when doing bulk loads. Problem is I can't find the !@#$%^&*() controls for these setings. Any SQL Server victims experts here? --\r\n
Karsten M. Self [link|mailto:kmself@ix.netcom.com|kmself@ix.netcom.com]\r\n
[link|http://kmself.home.netcom.com/|http://kmself.home.netcom.com/]\r\n
What part of "gestalt" don't you understand?\r\n
[link|http://twiki.iwethey.org/twiki/bin/view/Main/|TWikIWETHEY] -- an experiment in collective intelligence. Stupidity. Whatever.\r\n
\r\n
Keep software free. Oppose the CBDTPA. Kill S.2048 dead.\r\n[link|http://www.eff.org/alerts/20020322_eff_cbdtpa_alert.html|http://www.eff.org/alerts/20020322_eff_cbdtpa_alert.html]\r\n
|
Post #81,266
2/12/03 8:33:28 PM
|
Two tacts...
Using the SELECT INTO or BULK INSERT will bypass transactions if you have the specific database configured for it (under options for Select Into /Bulk Copy Option). And yes, the SELECT INTO is also another way to bypass the transaction log into a table.
Other than that, the best way to generally bypass the problem is to make sure and use temporary tables for any intermediate table writes. When you drop the temp tables, the associated transactions will also be dropped.
|
Post #81,270
2/12/03 8:43:22 PM
|
Probably shouldn't fight it though
A lot of people look at transactions as a problem on SQLServer and initially try as they might to skirt around them. Generally speaking, though, if you are trying to fight Transactions, you are just fighting the innate nature of the architecture.
Best to work them than against them.
|
Post #81,293
2/12/03 9:53:21 PM
|
Havnt messed with SQLServer yet, question
transaction logs are both usefukl for debugging and transaction rollbacks? If so work with them.
thanx,
bill
will work for cash and other incentives [link|http://home.tampabay.rr.com/boxley/resume/Resume.html|skill set]
questions, help? [link|mailto:pappas@catholic.org|email pappas at catholic.org]
|
Post #81,332
2/13/03 12:06:30 AM
|
SAS rears its ugly head...
Thing is, we're accessing this puppy through SAS, the database\r\nappears as a (fairly) standard SAS library, access is through ODBC. So\r\nmy own control through code to what's going on with the table itself is\r\nrelatively limited. I'm also not able to add/drop indices on the SQL\r\nServer tables from within SAS, so a number of options of r easing the\r\nupdate process are somewhat foregone. \r\n\r\n One option, of course, is that there isn't a good solution to this\r\nand we'll either have to suffer or look for an alternate solution. \r\n\r\n I'll also see what I can find out about the SAS side of affairs and\r\nwhat sort of requests it's generating for the DB. \r\n --\r\n
Karsten M. Self [link|mailto:kmself@ix.netcom.com|kmself@ix.netcom.com]\r\n
[link|http://kmself.home.netcom.com/|http://kmself.home.netcom.com/]\r\n
What part of "gestalt" don't you understand?\r\n
[link|http://twiki.iwethey.org/twiki/bin/view/Main/|TWikIWETHEY] -- an experiment in collective intelligence. Stupidity. Whatever.\r\n
\r\n
Keep software free. Oppose the CBDTPA. Kill S.2048 dead.\r\n[link|http://www.eff.org/alerts/20020322_eff_cbdtpa_alert.html|http://www.eff.org/alerts/20020322_eff_cbdtpa_alert.html]\r\n
|
Post #81,525
2/13/03 5:46:20 PM
|
SAS used to be able to shell out - run a "Load.Bat"?
|
Post #81,612
2/14/03 2:33:27 AM
|
"Tacks", actually. HTH!
|
Post #81,730
2/14/03 5:41:20 PM
|
Import vs. Load? BCP?
I know in DB2 that import use the transaction logs and loads don't.
As such, loads are much faster, but then you have to run some extra steps at the end to ensure referential integrity.
Maybe there is some utility in SQL Server 2000 like LOAD? BCP maybe?
Or some option on a utility?
|
Post #82,273
2/17/03 2:56:59 PM
|
Re: MS SQL Server: Logs & performance tuning?
Quick question for you: "Are you using Enterprise Manager"?
If so, you may want to check the management of the server and see if it gives you any options for the logs. You can disable them from a menu there, but my memory has not been good enough lately to remember which options turn off the logs and I don't have access to SQL Server 2000 (V8) right now to look at it.
Also check out the Administrator Tools for SQL Server, I think one of them does the logs, but I forgot the name of it. SQL Analyzer or something like that.
Sorry best I can do, I may be able to look it up later for you if nobody else can get it for you.
Also for Microsoft products, if you cannot find it in Google, try [link|http://msdn.microsoft.com/|http://msdn.microsoft.com/] it is a free MSDN Web Search. It has saved my can many times, and Microsoft keeps on updating it every day, unlike the CD-ROM version that gets updated every quarter.
I hope I have put you in the right direction to find the way to turn off the logs.
[link|http://pub75.ezboard.com/bantiiwethey|
New and improved, Chicken Delvits!]
|
Post #89,291
3/18/03 9:17:21 PM
|
Non-intuitively, you need to set the backup strategy.
Now, I don't know how much flexibility you have in this regard since I don't have any idea what SAS expects.
How much detail gets put in the logs is determined by the restores that SQL server expects to do. Check BOL `ALTER DATABASE', the option you're looking for is recovery_option.
FAQ! We're scrod!
|
