what to do if index size larger than data size
Question
Monday, June 8, 2009 3:15 AM
what to do if index size larger than data size
here is a big table, say 80,000,000 rows, with six or seven indexes,
data size is 30G or so,
indexes size is about 50G
I want to search fast on six or seven columns, but the index is too large. and insert or update is slow.
what shall I do ?
hello
All replies (11)
Monday, June 8, 2009 9:14 AM âś…Answered
Hi Merrica,
Considering the size of your database, I think that partitioning would be a really good idea if you got SQL Server Enterprise Edition.
You can create indexes which are partitioned as well.
I think considering pretty much everything is in varchar, that your best candidate would be the ID.
Do you have to archive data out of this table or do you simply keep everything?
I have had some experience using partitioning and even though the first time was pretty hard to grasp and accommodate, I got some pretty good procedures to manage them now, including sliding window partition tables.
Also, if you do very large insert/update/deletes on this table, it might be worth re-calculating the statistics on them. It changed my life (67 millions rows inserted daily on a partitioned table).
Nico
Monday, June 8, 2009 3:43 AM
You can find the unused indexes in sql server 2005 by querying DMVs. Check this http://www.mssqltips.com/tip.asp?tip=1545. If the indexes are not being used, then drop that
MadhuMCITP, MCTS, MCDBA,MCP-- Blog : http://experiencing-sql-server-2008.blogspot.com/
Monday, June 8, 2009 4:02 AM
no unused indexes,
there're many dynamic search on six or seven columnshello
Monday, June 8, 2009 4:19 AM
Merrica,
You are NOT giving much to advice better. Can you share the schema and the indexes on this table? Usually sometimes ppl create duplicate or overlapping composite indexes, some of them could be dropped after careful analysis.
Which version of SQL server are you using? If you are using 2005 or higher, have you thought of partitioning the data?
| Sankar Reddy | http://sankarreddy.spaces.live.com/ |
Monday, June 8, 2009 6:32 AM
2005sp2. partitioning tried. no good experence. ( partitioning not working well with fulltxt-index?)
actually it's someone else's table. and it's said to be slow.
I don't how to deal with such a huge table.
so,
there's a big table,
and a webpage to build dynamic query for all/some of the columns.
so there must be some indexes.
creating a separate index for every single column ?
or one index containing every column in the table ?
/****** Object: Table [dbo].[Details] Script Date: 06/08/2009 14:12:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Details](
[ID] [bigint] NOT NULL,
[ActualPage] [bit] NOT NULL,
[Country] [varchar](20) NULL,
[ProductInfo] [char](20) NOT NULL,
[Areas] [nvarchar](20) NOT NULL,
[CustomerId] [varchar](20) NOT NULL,
[StoreId] [varchar](20) NOT NULL,
[Trials] [varchar](20) NULL,
[InterStore] [varchar](20) NOT NULL,
[Values] [nvarchar](20) NULL,
[TimeFirst] [varchar](20) NULL,
[TimeLast] [varchar](20) NULL,
[ForceBegin] [varchar](20) NULL,
[ForceEnd] [varchar](20) NULL,
CONSTRAINT [PK_Details] PRIMARY KEY CLUSTERED
( [ID] DESC
)
GO
/****** Object: Index [Details_A] Script Date: 06/08/2009 14:14:12 ******/
CREATE NONCLUSTERED INDEX [Details_A] ON [dbo].[Detail]
(
[Areas] ASC
)
GO
/****** Object: Index [Details_C] Script Date: 06/08/2009 14:14:12 ******/
CREATE NONCLUSTERED INDEX [Details_C] ON [dbo].[Detail]
(
[CustomerId] ASC
)
GO
/****** Object: Index [Details_CSPL] Script Date: 06/08/2009 14:14:12 ******/
CREATE NONCLUSTERED INDEX [Details_CSPL] ON [dbo].[Detail]
(
[Country] ASC,
[StoreId] ASC,
[ProductInfo] ASC,
[ID] DESC
)
GO
/****** Object: Index [Details_PA] Script Date: 06/08/2009 14:14:12 ******/
CREATE NONCLUSTERED INDEX [Details_PA] ON [dbo].[Detail]
(
[ProductInfo] ASC,
[Areas] ASC
)
GO
/****** Object: Index [Details_STI] Script Date: 06/08/2009 14:14:12 ******/
CREATE NONCLUSTERED INDEX [Details_STI] ON [dbo].[Detail]
(
[StoreId] ASC,
[Trials] ASC,
[InterStore] ASC
)
GO
/****** Object: Index [Details_TB] Script Date: 06/08/2009 14:14:12 ******/
CREATE NONCLUSTERED INDEX [Details_TB] ON [dbo].[Detail]
(
[TimeFirst] ASC
)
GO
/****** Object: Index [Details_TE] Script Date: 06/08/2009 14:14:12 ******/
CREATE NONCLUSTERED INDEX [Details_TE] ON [dbo].[Detail]
(
[TimeLast] ASC
)
GO
hello
Monday, June 8, 2009 7:36 AM
Hi Merrica,
Below is the actual index created in your env.
| Index_name | Description | index_keys |
| Details_A | nonclustered located on PRIMARY | Areas |
| Details_C | nonclustered located on PRIMARY | CustomerId |
| Details_CSPL | nonclustered located on PRIMARY | Country, StoreId, ProductInfo, ID(-) |
| Details_PA | nonclustered located on PRIMARY | ProductInfo, Areas |
| Details_STI | nonclustered located on PRIMARY | StoreId, Trials, InterStore |
| Details_TB | nonclustered located on PRIMARY | TimeFirst |
| Details_TE | nonclustered located on PRIMARY | TimeLast |
| PK_Details | clustered, unique, primary key located on PRIMARY | ID(-) |
Column ID,ProductionInfo,StoreId,Areas is repeated, these repeated index increases the size of the index and is of nouse.
As Sanker suggested u have created duplicate and overlapping composite indexes.
I suggest you to drop repeated columns from the indexes.
As u are searching record using column Areas and CustomerId
Drop the existing index on the column Area and CustomerId , Create an index using both the columns (Areas,CustomerID).
Hope this help.
Mohd Sufian www.sqlship.wordpress.com
Monday, June 8, 2009 8:45 AM
though Column ID,ProductionInfo,StoreId,Areas is repeated , there maybe some benifits in different search conditions
if I
Drop the existing index on the column Area and CustomerId , Create an index using both the columns (Areas,CustomerID).
then no index when searching only CustomerId .
actually we often search only CustomerId
actually we often search in all different combinations.
hello
Monday, June 8, 2009 8:49 AM
I think there may be many fragments in all those indexes after so many inserts\updates\deletes
and I don't think it a good way to defragment all these indexes in a huge table every day.
hello
Monday, June 8, 2009 8:54 AM
Hi
I have one query, Suppose today my query is using one index if any new query is created by developer and that query wants to use another indexes as few column does not exists in existing index. DMV's is also suggesting new index apart of existing index but it also includes those columns which are already being in used by first query, so here my question is that should i create a new index suggested by dmv's or should i alter the existing index?
Raj
Monday, June 8, 2009 10:47 AM
capture few queries using the table and see the execution plan and check how indexes on the tables are used.
SET SHOWPLAN ON
Dont drop the indexes forever, recreat them excluding the columns which are repeated.
Mohd Sufian www.sqlship.wordpress.com
Monday, June 8, 2009 10:48 AM
Alter the existing index this will be much faster and will cost less to the resources.Mohd Sufian www.sqlship.wordpress.com