November 2, 2012 at 2:48 pm
Hi
I am working on a database whose database size is 40 gb and the index size is 1 tb.
so i ran a query to find the unused index and upon team agreement i removed few of them
so in order to still reduce the index size, what can be done
Currently i waas thinking to have included columns
can any suggest me other possible solutions.
Thanks
November 2, 2012 at 2:55 pm
Um, errr, hrm.
You do realize we can't see your tables, your usage history, or your heuristics to make that kind of broad recommendation in either direction, correct?
Including columns in an index will actually increase the size of an index, not reduce it. That said, if you can include columns in one and remove a second one with equivalent leading columns you'll end up with less space used... usually. Though it may cost you performance.
Every index to be removed or modified is a judgement call. Each and every one. There's no broad stroke brush method that I'm aware of that will answer your question without a lot more information about each particular index in question. This is consultant on site for a few weeks kind of work. I'm not sure we can really answer your question here. It's just a lot of grunt work figuring out what calls each index, if they're duplicates of other indexes, what the performance costs are for combining duplicated indexes, things like that. Each index needs to be reviewed individually.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 2, 2012 at 3:08 pm
penu.v8 (11/2/2012)
HiI am working on a database whose database size is 40 gb and the index size is 1 tb.
so i ran a query to find the unused index and upon team agreement i removed few of them
so in order to still reduce the index size, what can be done
Currently i waas thinking to have included columns
can any suggest me other possible solutions.
Thanks
That is a very wide open question and one that requires significantly more details to answer. There are many queries you can find on the internet that will tell you which indexes in the database are unused or used. Remember that many of the DMV's do not survive a SQL Server restart.
Here are a couple by Glenn Berry that detail which indexes experience significantly more writes than reads (so possibly candidates to be dropped) as well as data and indexes broken down by buffer use (indicating that SQL has read the data pages into the buffer and is therefore using them).
-- Possible Bad NC Indexes (writes > reads)
SELECT OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id,
user_updates AS [Total Writes], user_seeks + user_scans + user_lookups AS [Total Reads],
user_updates - (user_seeks + user_scans + user_lookups) AS [Difference]
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
AND s.database_id = DB_ID()
AND user_updates > (user_seeks + user_scans + user_lookups)
AND i.index_id > 1
AND user_seeks + user_scans + user_lookups = 0
ORDER BY 1,[Difference] DESC, [Total Writes] DESC, [Total Reads] ASC;
-- Breaks down buffers used by current database by object (table, index) in the buffer cache
SELECT OBJECT_NAME(p.[object_id]) AS [ObjectName], p.[object_id],
p.index_id, COUNT(*)/128 AS [buffer size(MB)], COUNT(*) AS [buffer_count]
FROM sys.allocation_units AS a
INNER JOIN sys.dm_os_buffer_descriptors AS b
ON a.allocation_unit_id = b.allocation_unit_id
INNER JOIN sys.partitions AS p
ON a.container_id = p.hobt_id
WHERE b.database_id = DB_ID()
AND p.[object_id] > 100
GROUP BY p.[object_id], p.index_id
ORDER BY buffer_count DESC;
Other things to consider are data types. Incorrect usage of data types can lead to bloated indexes with wasted space in them. I have also seen many occurrences of single column indexes on a table when a smaller number of multiple column indexes (sometimes using INCLUDED columns) can perform better.
Here also is a link to an article by Jonathan Kehayias of SQLSkills which will search the plan cache to determine if an index is being used. Again remember that the plan cache is dynamic and items are occasionally pushed out of cache so you will want to find a way to collect the usage data over a period of time to confirm that the index never appears in the plan cache: http://sqlskills.com/blogs/jonathan/post/Finding-what-queries-in-the-plan-cache-use-a-specific-index.aspx.
November 2, 2012 at 5:50 pm
penu.v8 (11/2/2012)
HiI am working on a database whose database size is 40 gb and the index size is 1 tb.
so i ran a query to find the unused index and upon team agreement i removed few of them
so in order to still reduce the index size, what can be done
Currently i waas thinking to have included columns
can any suggest me other possible solutions.
Thanks
Yes. Are you doing regular index maintenance based on both gragmentation levels and % of space used? There's a strong chance that if you aren't doing such a thing would reduce the 1TB of index usage down to a former shell of itself and I'd try that first. I'd probably start by doing a forced rebuild of ALL the indexes just to have a "starting point".
--Jeff Moden
Change is inevitable... Change for the better is not.
November 3, 2012 at 8:19 am
Check out kimberly tripp's posts on duplicate indexes. You may have some dups that can be removed.
November 3, 2012 at 10:01 am
SpringTownDBA (11/3/2012)
Check out kimberly tripp's posts on duplicate indexes. You may have some dups that can be removed.
Got a link?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 3, 2012 at 12:22 pm
November 3, 2012 at 12:40 pm
SpringTownDBA (11/3/2012)
Excellent. Not sure why it gives an error when you click on it but here it is again.
[url]www.sqlskills.com/blogs/kimberly/category/sp_helpindex-rewrites.aspx"> http://www.sqlskills.com/blogs/kimberly/category/sp_helpindex-rewrites.aspx%5B/url%5D%5B/quote%5D
Excellent. Not sure why it gives an error when you click on it but here it is again.
www.sqlskills.com/blogs/kimberly/category/sp_helpindex-rewrites.aspx
{Edit} ... and it still breaks. If someone needs to get to the excellent article that SpringTowwnDBA posted the link for, just copy the following line into your IE address bar and press the "Enter" key...
http://www.sqlskills.com/blogs/kimberly/category/sp_helpindex-rewrites.aspx
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply