Defrag/Re-Index
-
My Database hasn't been Defragged or Reindexed in over a year (maybe never) and it is timing out all over the place,so I really need to do this soon! I have been reading about indexes since the begining of the week (I knew nothing till then). I have also read almost everything on this site about them as well. Well it's a lot of info to take in and I have been tooling around a little with some of the recommendations ppl have thrown out there. I used this sp from this site http://www.sqlservercentral.com/columnists/rsharma/defragdb.asp to defrag a copy of my production database. When I compared the amount of indexes using this SP USP_DBShowContig_Single_DB from this link http://www.sqlservercentral.com/columnists/ncain/checkingyourdatabasefragmentationlevel.asp I have more indexes after the defrag. Any idea why?
Obivously my biggest concern is doing something I have no experience with and then creating more problems, so I am trying to do this correctly the first time! The DB indexes don't include any FillFactor Percentages (they are all set a 0). From what I have read with my DB being both Read and Write Intensive (Is there a counter in perfmon that I can view that will tell me read to write ratio?) the "Rule of Thumb" is 80-90%. With that said what would be a good SP that would Re-index (DBCC DBReindex) to incorporate my whole database and all indexes at an 80% FillFactor?
Any suggestions are much appreciated! Thanks
-
A starting point for you could be to run a maintenance plan against this database.
You have the options in the Plan for shrinking the database and defragging existing indexes.
After this is done, then try running profiler for that database ID (select * from master..sysdatabases) with a filter on duration of > 10000 (10 seconds) which should highlight the biggest performance problems you have.
More indexes are not necesarily a bad thing, it really depends on if they are actually used/needed or not. Just make sure that the tables do not have duplicate indexes (which I have seen many times).
The 'Best Practice Analyser' is a good tool from MS to quickly see/check common issues like each table having a primary key etc.
hth
Neil.
-
See, if this helps:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I just relized I posted this in the Yukon section! I have 2000 I guess they are close enough (don't throw anything!)
Thanks for the responses, I'll check into them both. Hopefully all will go well!
-
Good luck, if you need any more scripts or help then feel free to shout.
Sometimes maintenance plans are the way to go, sometimes it's better to do them the old fashioned way.
-
Actually I have another question. Not that I don't like to research myself it's just I am up to my elbows in white papers and other documentation.
I would like to find out how many indexes I have and what their names are for this DB. Is there a system SP that can do that? My concern is when I recreate the indexes through DBCC REINDEX for the whole DB that some don't get recreated thus affecting performance. I would like a list in case I have to manuelly recreate them. Any suggestions?
-
SELECT
*
FROM
sysindexes i
WHERE
i.indid BETWEEN 1 AND 254
AND OBJECTPROPERTY(id, 'IsUserTable') = 1
AND INDEXPROPERTY(id, name, 'IsStatistics') = 0
ORDER BY
OBJECT_NAME(id)
, i.indid DESC
should help
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Frank thanks for the query. I used that in place of my join that I wrote
CREATE VIEW v_INDEXES
(Table_Name,Index_Name,Table_ID,Index_ID,Fill_Factor)
AS
select
o.name,
i.name,
i.id,
i.indid,
i.origfillfactor
from sysobjects o INNER JOIN sysindexes i
ON o.id = i.id where o.type = 'U'
it seems to be more accurate, the one you provided is closer in the amount of Indexes I estimate for the amount of tables I have (170 tables). I don't fully understand where you get the ObjectProperty,IndexProperty and why you chose the INDID between 1 and 254.
My view output over 1000 indexes where yours only outputs 200 or so. I see Indexes with _wa% as the name. What are they for?? Would you mind explaining your query a little more in depth?
-
SELECT
*
FROM
sysindexes i
WHERE
i.indid BETWEEN 1 AND 254
/* Each table can have up to this number of indexes, including the clustered index (indid=1 and some special ones on BLOBs, So, all in all, 254, IIRC. */
AND OBJECTPROPERTY(id, 'IsUserTable') = 1
/* This is to make sure you're only interested in your custom build tables and don't return any system tables */
AND INDEXPROPERTY(id, name, 'IsStatistics') = 0
/* This is to filter out the statistics, your query returns. They are prefixed _wa... What they are used for? See the links below
*/ORDER BY
OBJECT_NAME(id)
, i.indid DESC
Actually no miracle here, INDEXPROPERTY and OBJECTPROPERTY are handy functions to retrieve extended informations about your object in the db. See BOL for what these all can reveal.
As for statistics:
http://www.microsoft.com/sql/techinfo/tips/development/queryopstats.asp
http://support.microsoft.com/default.aspx?scid=kb;EN-US;325024
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/statquery.asp
HTH
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply