April 25, 2011 at 3:00 pm
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
April 25, 2011 at 6:28 pm
Kevin,
Thank you.
LC
April 26, 2011 at 8:01 am
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:
April 26, 2011 at 5:31 pm
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
April 27, 2011 at 12:54 pm
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
Viewing 5 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply