SQL Server 2005 - Rowsize effect for query performance?

Question

I am trying to compress extra performance when searching a table with many rows. My current reasoning is that if I can throw some of the rarely used item out of the lookup table, thereby reducing the number of rows in the row, and therefore IO should drop, giving an advantage when the data starts to leak from memory.

Any good resource describing such effects? Any experiences?

Thank.

Answer 1

Setting the row size is only a serious problem, if the DBMS performs a full scan of the row table, if your query can select rows using only indexes, then the row size is less important (if you do not return a very large number of rows, where the IO to return the actual result is significant )

If you are doing a full table scan or a partial scan of a large number of rows, because you have predicates that don't use indexes, then primaryness can be a major factor. One example that I remember in a table of the order of 100,000,000 rows dividing large columns of “data” into another table from the columns used for queries led to some improvement in the performance of some queries.

, .

Answer 2

, , , . , . . , .

, , iostatistics. . - > .

Answer 3

sql- - , , , , , , .

, : :)

, , , ..

, sql scna ( , ), io- . , ( ).

Answer 4

, , . .

- , .

, 1: M, , , , , . , , . , .

Answer 5

- , , . IO, , , , , .
, , HW, , , , SQL
< /" > - SQL Server . - , . - , :

.
(RAID).
RAID, .
, .
, / .

Microsoft SQL Server - Microsoft Windows . SQL Server , -, Windows -. , I/O, .

-. , .

Answer 6

, , , ; , ( SQL Server 2005 - ), , ​​( ).

, ( ) , , :

SET STATISTICS IO ON
GO


-- Execute your query here


SET STATISTICS IO OFF
GO

( , ). ? , . - , ( ), . , .

Answer 7

I think that you are going to move forward using standard optimization methods first - check your execution plan, profiler trace, etc. and see if you need to customize indexes, create statistics, etc. - before looking at the physical structure of your table.