| Author |
Topic |
|
george.greiner
Starting Member
19 Posts |
 Posted - 2010-02-09 : 10:58:48
|
| Hello,I am new to SQL Admin and somewhat experienced when it comes to programming. Currently my small business's SQL server seems to be running slow and I have no idea what steps to take to correct it. I am currently studying for the 70-432 exam but have not gotten far enough or read enough of the battery of books I have to understand how to correct issues regarding server optimization and this needs to be corrected ASAP. If someone could point me to a place or explain what the best steps are for optimizing a small SQl server that would be great. We are running SQL Server 2005 currently.Thanks,George |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-09 : 11:14:11
|
| Rebuild all indexesthenUpdate statistics WITH FULLSCANsee if that cures the problem.Do you have an LDF file that is much bigger than your MDF file? If so you need to put in place Transaction Log backups - if the LDF is massive (compared to MDF) that will probably be hurting you too |
 |
|
|
george.greiner
Starting Member
19 Posts |
Posted - 2010-02-09 : 11:19:16
|
| Thank you for the quick replay Kristen. A quick question: What actually occurs when you rebuild all indexes? I just want to understand exactly what is happening before I execute something. (I already found out how to do it just want to know what actually occurs). Also should I be doing this weekly or monthly?Thanks,George |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-09 : 11:28:03
|
Yes, important question  If your database is in use Rebuilding Indexes will most likely lock all the users out  - at the least run it during the night.Index Rebuild kinda copies all the index entries to a brand new, contiguous, section of the disk / database file. The structure is "perfectly balanced" and thus an index that has grown like topsy (which is inefficient to query) is then much more efficient.There are options to "Defrag" the indexes instead. This can be done whilst the database is in use as it doesn't lock everyone out (but it might slow the system a bit). It doesn't build the perfect index "shape" but my experience is that it is fine - we run our databases 24/7/365 and are never able to use REBUILD (except during scheduled downtime), so they only ever get defragged, and we don't have performance problems.If you have Enterprise version (seems unlikely  ) there is an ONLINE flavour of REBUILD which does not lock everyone out.We have a routine that checks how badly fragmented the indexes are (there is a system table that you can query to get that information) and we rebuild anything that is sufficiently fragemented. That routine runs during our "quiet period" overnight, and runs every night. Some tables get defragged every night.Look at it this way, if a table is badly fragmented why wait until the weekend to improve it - with performance deteriorating all the way through the week?Having said that rebuild all indexes once a week (at the weekend?) is not a bad place to start if you currently have NO rebuilds Other downside is that index rebuild is likely to give you a large amount of transactions, and thus likely to make your LDF file bigger (Index Rebuild is probably the largest single transaction your database will see, so it is likely to be the thing that extents your LDF file) and that in turn will mean large TLog backup files. Just something to be aware of. We increase the frequency of Tlog backups during Index Housekeeping from every-15mins to every-2minsUpdate Statistics is less intrusive and should be fine to do anytime - but that too would best be scheduled during quiet-time. This is probably the more important thing to do for a quick-fix - assuming it isn't currently running as part of some routine housekeeping OR it is running on a SAMPLE of the table, rather than doing a FULLSCAN. Should give you better performance right off the bat.Edit: Lots of typos |
|
|
|
george.greiner
Starting Member
19 Posts |
Posted - 2010-02-09 : 11:38:25
|
| Thanks again for all that information it is greatly appreciated. |
|
|
|
george.greiner
Starting Member
19 Posts |
Posted - 2010-02-09 : 11:57:31
|
| Our .LDF file is by far larger than our .MDF file. .LDF is 1.7 million kb and our .mdf is 3840...I did not actually set this server up nor do I update it (an Access MVP does the updating with new data monthly). Is there a way to fix this issue? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-09 : 12:07:53
|
| LDF is 1.7MB and MDF is 3.8KB ??I doubt you can get anything smaller than that, unless I have misunderstood your units.Your database may be set up as SIMPLE Recovery Model, in which case there is not need to backup the transaction log.However, if it is set up as FULL Recovery Model (have a look at the Properties for the database) then you do need to make Transaction log backups.Check where your Backup files are and see if it looks like you have files for BOTH Full and TLog backups (there is no 100% standard for file-naming, but there is a SQL Command to tell you what type of info is contained in a given Backup fileRESTORE FILELISTONLY FROM DISK = 'x:\Mypath\MyBackupFilename.BAK'RESTORE HEADERONLY FROM DISK = 'x:\Mypath\MyBackupFilename.BAK' |
|
|
|
george.greiner
Starting Member
19 Posts |
Posted - 2010-02-09 : 12:20:38
|
| It is indeed set up as simple recovery model. What is the benefit of a full recovery model and is it necessary if that data does not change outside of a monthly update that creates new tables and deletes the old ones. Outside of that this database is ONLY subject to select queries. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-09 : 13:01:55
|
| Yeah, sounds like SIMPLE Recovery Model is fine. No point doing backups all that frequently either!SQL records Transactions (as data changes) in the Transaction Log (LDF) file.If it is necessary to rollback a transaction (program error, program explicitly asked for a rollback, powercut, etc) the TLog will be used to put the database back to a consistent state (either the whole transaction / transaction block is in the DB, or none of it is present)In SIMPLE Recovery Model the space used by each transaction is made available for reuse as soon as it is committed [slight oversimplification].In FULL Recovery Model the transactions are flagged as being available, but the space is not actually made available for reuse until a Transaction backup is made.The specific benefit of this is that in FULL Recovery Model you can restore a FULL backup and then all the subsequent Transaction Log Backups to any point in time (up to the last transaction log backup that you have available).In SIMPLE Recovery Model all you can restore is "last night's full backup"So ... for example:Transaction log backups are made every 15 minutesSome catastrophic system failure at 16:46Restore the last full backup (e.g. midnight last night), then all Tlog backups (e.g. every 15 minutes) up to 16:45. 1 minute's lost work Or some twit accidentally deletes half the database at 16:44Restore Full backup, all Tlog backups up to 16:45 but using the STOP AT 16:44 command - the restore is just-up-to the accidental deletion.You can also run another server in "warm standby". Each TLog backup is "shipped" to the standby-server, and restored. In the event of a failure of the main server the Standby server is fully-restored (up to last TLog which has been restored) and is ready to be used with just a couple of "activation" commands.Some other uses too ... |
|
|
|
george.greiner
Starting Member
19 Posts |
Posted - 2010-02-09 : 14:01:28
|
| Okay cool thank you so much for explaining that! I just went through and checked all of our databases to see what they were set on as I did not set any of them up but a contractor did when we had the server set up and I now understand why he did what he did! Thanks again for all of your help! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-09 : 14:43:06
|
| "I now understand why he did what he did!"Well if he did what he did in a way that you (now) approve of that's pretty good! |
|
|
|
|
Server Running Slow