| Author |
 Topic  |
|
|
SiSL
Average Member
Turkey
671 Posts |
 Posted - 31 October 2005 : 16:14:46
|
I've been using SQL for long time, however, when seeing peoples response timers here, I would like to learn the main trick...
I have 4 large snitz boards on same server, setup on Windows 2003 standard server, (currently only Snitz are mainly working in SQL), rest was test purposes. All 4 boards are ofcourse at different databases...
Main files of SQL in C:\Program Files\MS sql blah blah..
Database location is at Harddisk D:\ so logs there too...
My server has been set to use 1024 mb's of physical ram "fixed"...
MS Search currently running, DTC running... max worker threads 4096 set in memory, nested triggers on and 2000 query time out stuff..
But still most of the time, my websites having slow generate times.
I'm not sure if this is SQL speed or am I doing really wrong..
|
CHIP Online Forum
My Mods
Select All Code | Fix a vulnerability for your private messages | Avatar Categories W/ Avatar Gallery Mod | Complaint Manager
Admin Level Revisited | Merge Forums | No More Nested Quotes Mod
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
SiSL
Average Member
Turkey
671 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 31 October 2005 : 21:18:10
|
Yeah, I meant transaction logs.
To move the transaction log, I would advise a database detach, the move of the log file to a new destination, and then a database attach. For example, to do this to a database named MySNitz, in Query Analizer you could do this:
sp_detach_db 'MySnitz'
Then with Windows Explorer, move the log file ('MySnitz_log.ldf' in the example) from the usual location (C:\Program Files\Microsoft SQL Server\MSSQL\Data), to the new disk location.
Attaching the DB can be accomplished with this, assuming I'm moving the log to the root of d:
sp_attach_db 'MySnitz', 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\MySnitz_data.mdf', 'd:\MySnitz_log.ldf'
|
Snitz 3.4 Readme | Like the support? Support Snitz too |
 |
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 01 November 2005 : 05:29:12
|
A regular mantra here at HQ is 'do not run IIS and SQL on the same box', so if you can divide the hardware, it may help a lot. RAM - if you can go up to 4G, do so, just as a rule-of-thumb. In fact I think W2K3S can support upto 32G, but that would just be silly in your case ;-)
Which version of W2K3S and SQL SErver are you running? Proper enterprise ones (ie expensive licenses) or developer ones/MSDN ones/whatever? Cheap/free/etc ones are crippled, so you may get some performance gains from license upgrading, talk to MS about it or have a good dig around on microsoft.com.
detaching and reattaching the db is so simple as rui suggests above, or if you're nervous, it's all available through Enterprise Mangler too. Practice with Northwind and/or Pubs db's first, then you'll only have to go offline for maybe 30-seconds per db.
Also, check your logs are truncating OK, or use the detachment/reattachment as an opportunity to rebuild them altogether. |
|
|
|
Podge
Support Moderator
Ireland
3776 Posts |
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 01 November 2005 : 11:28:01
|
quote:
Will having the database file on the same drive (C:) as the operating system incur a performance hit?
Yes, some, but not that much, the important thing is to seperate log and data files, and if using fulltext searching, you should also be careful where you keep the catalogs. Placing the data files on a striped raid set will also give a big performance increase.
quote:
Is there a benefit also in moving the master, tempdb, msdb, model, databases too?
Moving tempdb will give some speed improvements, the others shouldn't make that much difference
|
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 01 November 2005 : 11:34:44
|
quote:
Will having the database file on the same drive (C:) as the operating system incur a performance hit?
Probably, some, but easily mitigated with a bit of RAM. Try it 
quote:
Earlier this year I moved all non system databases files & logfiles to two seperate drives. It certainly made a difference. Is there a benefit also in moving the master, tempdb, msdb, model, databases too?
Master - maybe a little bit, not much at all
tempdb - if you use #temp and ##temp tables, certainly, maybe a bit otherwise, depending on your code
msdb - unlikely
model - no.
Look up 'System Databases and Data' in Books Online (SQL Server Help, by any other name) for details on how the 4 system db's work, and it'll all make sense, or you'll come back and challenge my comments, which is OK too
|
|
|
|
Podge
Support Moderator
Ireland
3776 Posts |
|
|
SiSL
Average Member
Turkey
671 Posts |
|
|
Podge
Support Moderator
Ireland
3776 Posts |
|
|
SiSL
Average Member
Turkey
671 Posts |
|
|
Podge
Support Moderator
Ireland
3776 Posts |
Posted - 01 November 2005 : 17:11:54
|
That means theres another 1GB for IIS & the OS. You should use the task manager to find out how much memory w3wp.exe is using. There may be multiple instances of it.
In the application pool which serves the website for the forums is there a limit set on the amount of memory used?
Open IIS, click the + beside application pools.
Right click the application pool for the corresponding forum website and select properties.
It should tell you if there are any memory limits, request or cpu throttling, etc. Obviously you want to make use of all the available ram either by giving some extra to MSSQL or IIS. |
Podge.
The Hunger Site - Click to donate free food | My Blog | Snitz 3.4.05 AutoInstall (Beta!)
My Mods: CAPTCHA Mod | GateKeeper Mod
Tutorial: Enable subscriptions on your board
Warning: The post above or below may contain nuts. |
|
|
|
SiSL
Average Member
Turkey
671 Posts |
|
| |
Topic |
|
Topic Locked
