Online Indexing

Question

Online Indexing

Viewing 5 posts - 31 through 34 (of 34 total)

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

Regarding Paul Randall's quote: "Update statistics for all of the non-indexed columns", it would seem that there is a flaw in that statement, since statistics are only kept on search columns, which by definition, should be indexed, if you want to avoid table scans. I'm not sure I understand his recommendation.

At least with that statement you are admitting you don't know things. And yet you still say you think there is a flaw with what he says. I sure would be careful saying something is flawed when I don't understand things, ESPECIALLY when the person making the statement is Paul Randall and the topic is SQL Server!!

1) Statististics are not "kept only on search columns". They are auto created wherever the engine feels they are needed (and also where we explicitly create them). One common and important place is on joined columns.

2) So, you think an index should be used for EVERY search, and that this will be more efficient than a table scan? That means that even if a WHERE clause is supposed to return 100% of the rows in a table it should use an index seek and bookmark lookup to "avoid table scan". Well, that is a lack of knowledge (a very common one actually). Another place where a nonclustered index won't help is on really small tables.

Here is a quick sample for you to prove how bad index seeks can be with large percentage of rows returned:

Use tempdb

set nocount on

go

CREATE TABLE dbo.#Customers (

customer_nbr INT NOT NULL PRIMARY KEY,

first_name VARCHAR(35) NOT NULL,

last_name VARCHAR(35) NOT NULL);

INSERT INTO dbo.#Customers VALUES(1, 'Jeff', 'Hull');

INSERT INTO dbo.#Customers VALUES(2, 'George', 'Brown');

INSERT INTO dbo.#Customers VALUES(3, 'Peter', 'Green');

INSERT INTO dbo.#Customers VALUES(4, 'Dona', 'Johnson');

INSERT INTO dbo.#Customers VALUES(5, 'Kevin', 'Boles');

insert dbo.#Customers

select number, 'asdf', 'zzzz'

from KGBTools.dbo.BigNumbers --use your numbers table here

where number between 6 and 50000

CREATE NONCLUSTERED INDEX ix_customer_last_name

ON dbo.#Customers (last_name);

SET STATISTICS IO ON --show actual execution plan too

--let the optimizer do what it thinks is best

SELECT first_name, last_name

FROM dbo.#Customers

WHERE last_name = 'zzzz';

--clustered index scan, .182 query cost, 169 IO

--force the optimizer do what you think is best

SELECT first_name, last_name

FROM dbo.#Customers WITH (INDEX=ix_customer_last_name)

WHERE last_name = 'zzzz';

--index seek-bookmark lookup plan, 8.76 query cost, 103218 IO

drop table dbo.#Customers

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Gail Wanabee SSCrazy Eights Points: 8471 More actions · Answer 2

Gail Wanabee

SSCrazy Eights

Points: 8471

April 25, 2011 at 6:28 pm

#1315660

Kevin,

Thank you.

LC

SanDroid SSChampion Points: 10068 More actions · Answer 3

I see a lot of churn, but no messages from Iianvh on if anything at all that anyone has recomended is working or a good fit for him.

Has anyone else posting here heard from the person asking for help? :w00t:

Gail Wanabee SSCrazy Eights Points: 8471 More actions · Answer 4

lianvh,

I have posted some solutions to online indexing problems I've faced and solved, including the context of the problems and the solutions.

Have my ideas and/or solutions been helpful? Do you need clarification or additional information?

LC

Gail Wanabee SSCrazy Eights Points: 8471 More actions · Answer 5

lianvh (4/15/2011)
Good Day ,
We deployed Online Indexing on SQLS erver 2005 Enterprise Edition . I was rather disapppointed when I noticed that this caused locks on the system and a serious degradation in performance . We work in an envirnement where Online Indexing should have been the big saviour as our server run jobs 24 x 7 and there is no quiet time. Any ideas ?

lianvh,

You haven't responded in a while so I have no way of knowing if our posts have been helpful or not. I'll have one last go at this:

First, are you absolutely certain that the performance issues you are having related to online indexing are related to locks? If so, how do you know this?

Second, there is so much about your server, your SAN, and your production environment that we do not know, that it is difficult for us to offer well tailored suggestions. Can you tell us more about these things?

1. Total number of CPU cores?

2. Total RAM?

3. MAXDOP setting for SQL Server?

4. Brand of SAN?

5. Number of physical TempDB data files?

6. The lowest observed Page Life Expectancy values on your server while online indexing is executing?

7. The peak levels of disk activity (reads and writes per second) that your SAN is experiencing during index rebuilding operations?

8. What is the production environment for your server? OLTP? OLAP? Reporting? Other? Some combination of them?

9. Database sizes?

10. Number of indexes per database?

The answers to those questions would be helpful.

Additionally, if you have a Compellent SAN, I can offer qualified assistance. There may be some configuration settings that you could change that could make a significant difference in your server's performance.

LC