Indexes and Statistics

Question

Indexes and Statistics

byip

SSC Rookie

Points: 41
More actions
May 17, 2003 at 12:03 am

#159601

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 1

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.

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 2

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

sjones@sqlservercentral.com

http://www.sqlservercentral.com/columnists/sjones

http://www.dkranch.net

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 3

Agree with Steve Jones. View can take the name of the original table.

Antares686 SSC Guru Points: 125444 More actions · Answer 4

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?

DRBuckingham SSC Eights! Points: 996 More actions · Answer 5

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

byip SSC Rookie Points: 41 More actions · Answer 6

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.

DRBuckingham SSC Eights! Points: 996 More actions · Answer 7

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