December 17, 2007 at 11:25 am
Hi to all. We are in the process of migrating away from a Unix Platform to a Windows / SQL 2005 environment. I was looking at configuring maintenance plans using the wizard in SQL 2005 and have a couple of questions for you gurus.
1)
Database Backups. If I chose options:
Back up Database (Full) / Shrink Database / Rebuild Index / Reorganize Index is there a preferred order these tasks should be run in? Should I include more tasks?
2)
Transaction Log Backups
I chose to create a Maintenance Plan for the transaction logs. 'Define Back Up Database' menu 'Database' when expanded only displayed 'Model'?
Maybe it is me!!
Any other tips for best practice from those with real world experience would be warmly received.
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
December 17, 2007 at 11:57 am
Phillip,
1.
Rebuilding/reorganizing indexs everday is not usually required. Index rebuilding really is really benificial when the database has moderate to heavy fragmentation. When you rebuild the indexes there is no longer a need to reorganize them, as rebuilding removes the fragmentation by dropping and creating the indexs. You can use the sys.dm_db_index_physical_stats to determine where you fragmentation level is at.
If I were to include the rebuild or reorganize in the same maintenance plan, I would choose to perform this before the backup.
2.
I am guessing the reason you do not see the other databaes in the drop down is because they are not in the full recovery mode. Can you confirm this? Right-click on the database and choose properties and the go to the options tab.
December 17, 2007 at 12:10 pm
Thanks Adam I will take a look at the recovery mode tomorrow. Pretty sure they were set to Full unless the developers have been having a play!! I will implement your suggestions. Maybe have a separate plan for rebuilding the indexes say once a month or once per quarter?
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
December 17, 2007 at 12:25 pm
Rebuild your index according to how much the are fragmented. You can do this by using the new dynamic management views to determine what level they are fragmented. One you know about how long it takes for them to become fragmented, schedule the plan to run then.
You should note that your fragmentation will depend on the fill factor you have specified for the database. If you have a 90% fill factore then you are leaving 10% available for indexes to grow. Once the fill factor "fills up" pages start to split and performance degrades. On another note: if this is a OLTP database and there are lots of deletes, you can have "internal fragmentation", which causes empty space in pages. While this form of fragmentation is less degrading to performance, it should still be addressed frequently to keep performance optimal.
Like I said, indexing is dependant on your environment.
December 17, 2007 at 12:29 pm
I pulled these scripts from the Microsoft 70-431 training kit:
This script allows you to view the fragmentation
DECLARE @dbname VARCHAR(20)
SET @dbname = 'AdventureWorks'
SELECT object_name(dt.object_id),si.name,
dt.avg_fragmentation_in_percent, dt.avg_page_space_used_in_percent
FROM
(SELECT object_id, index_id, avg_fragmentation_in_percent, avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats (db_id(@dbname),null,null,null,'DETAILED')
WHERE index_id <> 0
)as dt --it does not return info about heaps
INNER JOIN sys.indexes si
ON si.object_id = dt.object_id
AND si.index_id = dt.index_id
--If avg_fragmentation_in_percent > 10 then the database has External Fragmentation
--If avg_page_space_used_in_percent < 75 then the database has Internal Fragmentation
This script determines if you should rebuild or reorganize based off the authors
recommendation. You can adjust the values.
DECLARE @dbname VARCHAR(20)
SET @dbname = 'AdventureWorks'
--These indexes should be reorganized, not rebuilt
-- Alter Index ... Reorganize
select *
from sys.dm_db_index_physical_stats (db_id(@dbname),null,null,null,'DETAILED')
where avg_page_space_used_in_percent 10 and
avg_fragmentation_in_percent < 15
--These indexes should be rebuilt, not reorganized
-- Alter Index ... Rebuild
select *
from sys.dm_db_index_physical_stats (db_id(@dbname),null,null,null,'DETAILED')
where avg_page_space_used_in_percent 15
December 17, 2007 at 12:32 pm
Adam many thanks for your efforts. The DB is not OLTP. I will make use of your advice as the project progresses.
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
December 17, 2007 at 1:17 pm
One more question! I would like a maintenance plan to delete .bak & Log files older than say 1 week. Is this achieved via 'Clean Up History' 'Maintenance Plan History'?
Update:
I created a new query with the the script you provided to determine fragmentation level? I take it that was what I was supposed to do? I ran it and it ran OK, returned 'No Rows Affected'.
Thanks,
Phil.
PS: You were correct regards 'Recovery Model'. The developers must have been playing.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
December 17, 2007 at 2:03 pm
Philip Horan (12/17/2007)
Back up Database (Full) / Shrink Database / Rebuild Index / Reorganize Index
Don't shrink your production databases. It's a waste of time and resources and it scrambles your indexes. If you shrink then rebuild indexes, the rebuild will likely require the DB to grow again. Repeated shrink/grow can also cause external (file-level) fragmentation of your data files.
Read this[/url], especially the 2 links towards the bottom
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 17, 2007 at 2:24 pm
The clean up task is exactly what you need 😉
December 17, 2007 at 2:26 pm
Update:
I created a new query with the the script you provided to determine fragmentation level? I take it that was what I was supposed to do? I ran it and it ran OK, returned 'No Rows Affected'.
This means that no of your indexes met the criteria specified by the query. You can adjust the numbers or remove the filter to see where you actually stand.
December 18, 2007 at 12:11 am
Thanks guys. The shrink database option was initially set up by the application vendor, although they are not SQL specialists (I do have one coming in January).
I will have to get reading.
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply