quote:

---

Originally posted by tkizer

Although your table is small, if the performance is not acceptable then I'd suggest partitioning. I can't imagine that the performance is that slow on such a small table though.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

---

quote:

---

Have you seen any write performance issue with the 30 indexes? What about storage?

---

quote:

---

I'd probably do partitioning right off the bat if you think it'll reach a few hundred million rows or bigger. It's easier to add it now than to wait until you need it.

---

quote:

---

Originally posted by SwePeso

Even if there are 50 rows in each page, you have a total of 200,000 pages.
Who will ever look through all of them?

---

quote:

---

Originally posted by SwePeso

There is a cap for 24(?) cores.

---

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

---

quote:

---

Originally posted by ferrethouse

quote:

---

I'd probably do partitioning right off the bat if you think it'll reach a few hundred million rows or bigger. It's easier to add it now than to wait until you need it.

---

I just did the math on Enterprise Edition. Our SQL server has 32 cores. That's $220,000 up front plus whatever the annual maintenance is! Yikes.

---

quote:

---

Originally posted by tkizer

Enterprise edition supports the operating system's maximum.

---

(50 row(s) affected)
Table 'Students'. Scan count 0, logical reads 212, physical reads 0.
Table 'Sorting'. Scan count 1, logical reads 5, physical reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 81 ms.

name      rows       reserved     data         index_size  unused
Students   10000000  11472664 KB  11428576 KB  42664 KB    1424 KB
Sorting   300000000   7738416 KB   7717104 KB  21240 KB      72 KB

SELECT		s.StudentID,
		s.Col01,
		s.Col02,
		s.Col03,
		s.Col04,
		s.Col05,
		s.Col06,
		s.Col07,
		s.Col08,
		s.Col09,
		s.Col10,
		s.Col11,
		s.Col12,
		s.Col13,
		s.Col14,
		s.Col15,
		s.Col16,
		s.Col17,
		s.Col18,
		s.Col19,
		s.Col20,
		s.Col21,
		s.Col22,
		s.Col22,
		s.Col23,
		s.Col24,
		s.Col25,
		s.Col26,
		s.Col27,
		s.Col28,
		s.Col29,
		s.Col30
FROM		dbo.Students AS s 
INNER JOIN	dbo.Sorting AS w ON w.SortColumn = 16
			AND PageNum = 1971
			AND w.StudentID = s.StudentID
ORDER BY	w.RowNum;

quote:

---

Originally posted by SwePeso

And here is the actual query.

SELECT		s.StudentID,
		s.Col01,
		s.Col02,
		s.Col03,
		s.Col04,
		s.Col05,
		s.Col06,
		s.Col07,
		s.Col08,
		s.Col09,
		s.Col10,
		s.Col11,
		s.Col12,
		s.Col13,
		s.Col14,
		s.Col15,
		s.Col16,
		s.Col17,
		s.Col18,
		s.Col19,
		s.Col20,
		s.Col21,
		s.Col22,
		s.Col22,
		s.Col23,
		s.Col24,
		s.Col25,
		s.Col26,
		s.Col27,
		s.Col28,
		s.Col29,
		s.Col30
FROM		dbo.Students AS s 
INNER JOIN	dbo.Sorting AS w ON w.SortColumn = 'Col16'
			AND PageNum = 19712
			AND w.StudentID = s.StudentID
ORDER BY	w.RowNum;

---

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

---

-- Create an environment for testing
SET NOCOUNT ON;
GO

USE TempDB;
GO

IF OBJECT_ID('dbo.Sorting') IS NOT NULL
	DROP TABLE	dbo.Sorting;
GO

IF OBJECT_ID('dbo.Students') IS NOT NULL
	DROP TABLE	dbo.Students;
GO

CREATE TABLE	dbo.Students
		(
			StudentID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
			Col01 CHAR(36) NOT NULL,
			Col02 CHAR(36) NOT NULL,
			Col03 CHAR(36) NOT NULL,
			Col04 CHAR(36) NOT NULL,
			Col05 CHAR(36) NOT NULL,
			Col06 CHAR(36) NOT NULL,
			Col07 CHAR(36) NOT NULL,
			Col08 CHAR(36) NOT NULL,
			Col09 CHAR(36) NOT NULL,
			Col10 CHAR(36) NOT NULL,
			Col11 CHAR(36) NOT NULL,
			Col12 CHAR(36) NOT NULL,
			Col13 CHAR(36) NOT NULL,
			Col14 CHAR(36) NOT NULL,
			Col15 CHAR(36) NOT NULL,
			Col16 CHAR(36) NOT NULL,
			Col17 CHAR(36) NOT NULL,
			Col18 CHAR(36) NOT NULL,
			Col19 CHAR(36) NOT NULL,
			Col20 CHAR(36) NOT NULL,
			Col21 CHAR(36) NOT NULL,
			Col22 CHAR(36) NOT NULL,
			Col23 CHAR(36) NOT NULL,
			Col24 CHAR(36) NOT NULL,
			Col25 CHAR(36) NOT NULL,
			Col26 CHAR(36) NOT NULL,
			Col27 CHAR(36) NOT NULL,
			Col28 CHAR(36) NOT NULL,
			Col29 CHAR(36) NOT NULL,
			Col30 CHAR(36) NOT NULL
		);
GO

INSERT		dbo.Students
		(
			Col01,
			Col02,
			Col03,
			Col04,
			Col05,
			Col06,
			Col07,
			Col08,
			Col09,
			Col10,
			Col11,
			Col12,
			Col13,
			Col14,
			Col15,
			Col16,
			Col17,
			Col18,
			Col19,
			Col20,
			Col21,
			Col22,
			Col23,
			Col24,
			Col25,
			Col26,
			Col27,
			Col28,
			Col29,
			Col30
		)
SELECT		NEWID() AS Col01,
		NEWID() AS Col02,
		NEWID() AS Col03,
		NEWID() AS Col04,
		NEWID() AS Col05,
		NEWID() AS Col06,
		NEWID() AS Col07,
		NEWID() AS Col08,
		NEWID() AS Col09,
		NEWID() AS Col10,
		NEWID() AS Col11,
		NEWID() AS Col12,
		NEWID() AS Col13,
		NEWID() AS Col14,
		NEWID() AS Col15,
		NEWID() AS Col16,
		NEWID() AS Col17,
		NEWID() AS Col18,
		NEWID() AS Col19,
		NEWID() AS Col20,
		NEWID() AS Col21,
		NEWID() AS Col22,
		NEWID() AS Col23,
		NEWID() AS Col24,
		NEWID() AS Col25,
		NEWID() AS Col26,
		NEWID() AS Col27,
		NEWID() AS Col28,
		NEWID() AS Col29,
		NEWID() AS Col30
FROM		master.dbo.spt_values AS a
INNER JOIN	master.dbo.spt_values AS b ON b.Type = 'P'
			AND b.Number BETWEEN 1 AND 100
INNER JOIN	master.dbo.spt_values AS c ON c.Type = 'P'
			AND c.Number BETWEEN 1 AND 10
WHERE		a.Type = 'P'
		AND a.Number BETWEEN 1 AND 100;
GO

CREATE TABLE	dbo.Sorting
		(
			SortColumn TINYINT NOT NULL,
			PageNum INT NOT NULL,
			StudentID INT NOT NULL,
			RowNum TINYINT NOT NULL
		);
GO

INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT  1 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col01) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT  2 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col02) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT  3 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col03) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT  4 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col04) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT  5 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col05) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT  6 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col06) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT  7 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col07) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT  8 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col08) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT  9 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col09) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT 10 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col10) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT 11 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col11) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT 12 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col12) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT 13 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col13) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT 14 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col14) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT 15 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col15) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT 16 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col16) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT 17 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col17) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT 18 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col18) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT 19 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col19) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT 20 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col20) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT 21 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col21) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT 22 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col22) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT 23 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col23) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT 24 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col24) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT 25 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col25) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT 26 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col26) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT 27 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col27) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT 28 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col28) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT 29 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col29) - 1 AS RowNum FROM dbo.Students) AS d;
GO
INSERT dbo.Sorting (SortColumn, PageNum, RowNum, StudentID) SELECT 30 AS SortColumn, 1 + RowNum / 50 AS PageNum, 1 + RowNum % 50 AS RowNum, StudentID FROM (SELECT StudentID, ROW_NUMBER() OVER (ORDER BY Col30) - 1 AS RowNum FROM dbo.Students) AS d;
GO

CREATE UNIQUE CLUSTERED INDEX UCX_Sorting ON dbo.Sorting (SortColumn, PageNum, StudentID);
GO

ALTER TABLE	dbo.Sorting
WITH CHECK
ADD CONSTRAINT	FK_Sorting_Students
FOREIGN KEY	(
			StudentID
		)
REFERENCES	dbo.Students
		(
			StudentID
		);
GO