May 17, 2003 at 12:03 am
SQL Server Experts,
I have a 200+ column table that has about 50 indexes but a lot of statistics on the columns. I am running into the 249 non-clustered index limit of SQL Server. Questions are 1. How do I get a count of the non-clustered indexes? and 2. Is there a way to get around this limit? Thanks in advance.
BY
May 19, 2003 at 8:30 am
To know the count of the indexes you can query the table sysindexes and search for the indexes of that table where indid between 2 and 254.
May 19, 2003 at 10:35 am
I'd argue you have too many indexes and need to scale this back.
You could also horizontally parition the table into two. Even use a view to join them back and minimize application changes.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 19, 2003 at 4:34 pm
Agree with Steve Jones. View can take the name of the original table.
May 19, 2003 at 4:44 pm
I have to agree also. Could you post your current DDL for your table and exaplin a bit about the system? Is this a datamart?
May 19, 2003 at 8:12 pm
You can issue the following query to view all the indexes and statistics within a database:
SELECT OBJECT_NAME([id]) AS 'TableName',
'StatType' = CASE WHEN INDEXPROPERTY([id], [name], 'IsStatistics') = 0 THEN 'Table' ELSE 'Stats' END, *
FROM [sysindexes]
WHERE OBJECTPROPERTY(OBJECT_ID([name]), 'IsPrimaryKey') IS NULL
AND [indid] NOT IN (0, 255)
You can further filter it down to a singular table by adding the following to the WHERE clause:
AND OBJECT_NAME([id]) = 'YourTableName'
To answer your second question, 254 indexes and statistics combined is the limit for a single table. Besides removing unused (or seldom used) indexes and statistics on your table, you could *vertically* partition your table. Horizontal partitioning as Steve Jones suggested would not yield the results you desire.
The following are excerpts from Books On-Line:
Horizontal partitioning segments a table into multiple tables, each containing the same number of columns but fewer rows.)
Vertical partitioning segments a table into multiple tables containing fewer columns. The two types of vertical partitioning are normalization and row splitting.
Row splitting divides the original table vertically into tables with fewer columns. Each logical row in a split table matches the same logical row in the others. For example, joining the tenth row from each split table re-creates the original row.
You could then create a view that joins the *vertically* partitioned tables to appear as it did before (but now allowing up to 254 indexes and statistics on each of the split tables).
David R Buckingham, MCDBA,MCSA,MCP
May 20, 2003 at 10:45 am
Thanks for your suggestion Steve, but the creation of the table is not within my control, it is automatically created within an application. I have about 50 non clustered indexes on the columns which I do have control of. I don't have control of the statistics that SQL Server creaates. Removing some of these indexes have caused noticeable degradation in performance.
May 20, 2003 at 2:46 pm
Have you run a Profiler trace and then ran the Index Tuning wizard against the workload for it's recommendations? There may be several indexes that are not being utilized by the Query Optimizer that could safely be removed.
David R Buckingham, MCDBA,MCSA,MCP
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply