2

I have a table with 100m+ rows. We're seeing very bad performance on queries as the data grows. I noticed that the row size is pretty big (10190) and I'm thinking that this is affecting indexes/index performance.

the table has a bunch of columns set to wrong data types (lots of ints where tinyints are more appropriate, etc). I went and updated the table to change what I could.

Original row size is 10190 and I was able to get it down to 10090 by adjusting ints to smallint or tinyint.

There are two columns set to varchar(2048). I changed those to varchar(max) and the row size went down to about 6000.

I used the queries found here http://www.sqlservercentral.com/Forums/Topic226699-5-1.aspx to get the row size.

My question is: Does changing the varchar(2048) columns to varchar(max) help indexing/performance when those columns are not used often? What about getting row size below 8000?

Titan2782
  • 135
  • 7
  • Are you querying against the clustered index, or the nonclustered index? Can you post the execution plan? – mrdenny Apr 22 '11 at 00:00
  • I have all index seeks, no table/index scans. Just a few joins in the query. SQL is using parallel execution. It's using both clustered and non clustered indexes. – Titan2782 Apr 22 '11 at 15:12
  • Would this not be better on http://dba.stackexchange.com/ ? – Orbling Apr 22 '11 at 23:51

3 Answers3

2

There is basically no difference between VARCHAR(2048) and VARCHAR(MAX). One is subject to overflow into the 'row overflow' allocation unit, the other is subject to overflow into the BLOB allocation unit, see Table and Index Organization. The default setting of the large value types out of row table option is 0 so, unless it was changed, the VARCHAR(MAX) will stay in row if possible, just like VARCHAR(2048) would.

I would recommend running sys.dm_db_index_physical_stats and getting the actual max, min and avg row size, as well as avg_page_space_used_in_percent. This will give a more accurate picture of the true row size(s), rather than the theoretical declared size.

Remus Rusanu
  • 8,283
  • 1
  • 21
  • 23
1

First thing I would check is the clustered index. It should be set to something narrow, one column or as few columns as possible if using a composite index. Ideally, it should be set to something that can be sequential, such as bigint, not uniqueIdentifier. If using a uniqueIdentifier clustered index, some people see performance improvements by adding a bigint clustered index and keeping the uniqueIdentifier as a unique index.

SSMS sometimes has useful information where indexes may be missing:
http://msdn.microsoft.com/en-us/library/ms345524%28v=SQL.100%29.aspx

Next thing would be analyzing the queries. Find out which queries are consuming the most time and determine if they are hitting covering indexes or are performing table scans. You may want to post some of the sql queries and details of the existing indexes.

Greg Askew
  • 35,880
  • 5
  • 54
  • 82
  • Clustered index is a single column already narrow. Seeing a lot of logical and physical reads. Server has 48GB of memory and 6x 4 core processors. Thats why I'm thinking it's a page problem. – Titan2782 Apr 21 '11 at 20:14
  • Have you identified query(s) that produce the symptom? If so, and you run it in SSMS, profile the display the query plan, it should show you where it is spending the time. For example, it may show table scans instead of seeks. Scans on a huge db are a huge performance hit. There could of course be other issues. Update activity occurring during the queries, indexes that need to be updated as well during an update, locking... When you find an offending query, check if there are a covering index. You may also want to post one of the offending queries. – Greg Askew Apr 23 '11 at 02:01
0

Are the queries written so that they only request the data that they need, i.e. no SELECT *?

I would check the overall setup of the SQL server, verify that the configuration follows recommended practices (see Brent Ozar's excellent checklist ); then maybe run some perfmon to see where your bottlenecks are, check disk queue length first.

SqlACID
  • 2,176
  • 18
  • 18