RECEIVE vs RECEIVE TOP(1)

In working through some examples, sometimes I will see this pattern for receiving messages: What is the purpose of the "nested" WAITFOR (RECEIVE? What is this actually doing? Is it receiving the same message in both RECEIVE?

WAITFOR (

RECEIVE @.dh = [conversation_handle],

@.message_type = [message_type_name],

@.message_body = CAST([message_body] AS NVARCHAR(4000))

FROM [Queue]), TIMEOUT 1000;

WHILE @.dh IS NOT NULL

BEGIN

IF @.message_type = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'

BEGIN

RAISERROR (N'Received error %s from service [Target]', 10, 1, @.message_body) WITH LOG;

END

END CONVERSATION @.dh;

COMMIT;

SELECT @.dh = NULL;

BEGIN TRANSACTION;

WAITFOR (

RECEIVE @.dh = [conversation_handle],

@.message_type = [message_type_name],

@.message_body = CAST([message_body] AS NVARCHAR(4000))

FROM [Queue]), TIMEOUT 1000;

END

COMMIT;

Other times I will see this pattern for receiving messages: Why do a RECEIVE TOP(1) instead of just a RECEIVE?

WAITFOR(RECEIVE TOP(1)

@.conversationHandle = conversation_handle,

@.messageTypeName = message_type_name,

@.messageBody = message_body

FROM [Queue]), TIMEOUT 1000;

And other times I will see this pattern for receiving messages: What is the purpose of RECEIVING into an in-memory table when you can just process the message directly?

WAITFOR(RECEIVE

queuing_order,

conversation_handle,

message_type_name,

message_body

FROM [Queue]

INTO @.tableMessages), TIMEOUT 1000;

IF (@.@.ROWCOUNT = 0)

BEGIN

COMMIT;

BREAK;

END

What is the difference between the three approaches from an architectural and performance perspective? I need to process messages as fast as possible and I'm not sure why or when each should be used. Also, does the timeout have any impact on how FAST messages will be processed, or is it exactly what it says - a timeout - if a message is not found within the period then the procedure will break?

The samples all use RECEIVE TOP(1) because it's much easier code to write and explain. Receiving all the messages available in a conversation group is much more efficient but it makes for more complex code that's harder to understand so I don't usually write samples that way. Remus has some great tips for writing efficient services here: http://blogs.msdn.com/remusrusanu/archive/2006/10/14/writing-service-broker-procedures.aspx|||

Well, I've reviewed that blog from Remus and actually ran the exact benchmarks and its true that I get pretty good performance. However, let's say that you have thousands of messages coming in per second and each message needs to be processed with some logic - what is the best way to do that using service broker. It seems counter-productive to load them into a temporary table as the example illustrates (but the example is only trying to show the capacity to clear the queue). Also, simply loading a conversation group could be unpredictable if you are recycling dialogs (every message would be a part of the same or a few dialogs). I see alot of benefit to the technology and I'm trying to use it in a very high transaction environment, but some of the advice seems to go against other recommendations and/or the examples are limited to single message processing. How about a boilerplate example of what one should strive for in the activated stored procedure when expecting the kind of load I mentioned above (thousands/sec).

|||

Well, if you trust your judgement more than the espertise of the guys who wrote Service Broker, I suupose you're free to make those judgements. The best way to scale to thousands of messages is going to depend on how big the messages are, how the dialogs are structured, etc. You may find that receiving a bunch of messages into a recordset in a .Net application running on a seperate server scales best for you if there's a significant amount of comutationally intensive processing for each message. A receive top(1) works just like a select top(1) - the whole query is executed and then after the first result is retreived the query is cancelled. If there are a lot of messages in a conversation group, this can be pretty inefficient but if each conversation group has only one message, it doesn't really matter whether you do a top(1) or not. Here are some scaleability thoughts I posted on another thread last week:

Beginning and ending a conversation takes longer than sending a message so starting up a new dialog for every message is pretty inefficient. Some thoughts here: http://blogs.msdn.com/rogerwolterblog/archive/2006/05/20/602938.aspx

Receiving one message at a time and commiting after each message is not a good way to scale an SSB application. Processing multiple messages per transaction makes error handling complex and receiving many messages per Receive statement is harder to do but if performance is a major issue, you need to do it. http://blogs.msdn.com/remusrusanu/archive/2006/10/14/writing-service-broker-procedures.aspx

If you have to do a lot of processing per message - complex XML manipulation, serializing and deserial,izing, etc. A stored procedure probably isn't a good option. Moving the messaging processing off the database server can help scaleability.

Here's a discussion of some of the things I've learned about SSB applications: http://msdn2.microsoft.com/en-us/library/aa964144.aspx

The key to understanding total throughput is understanding what it means to process a message. I had one customer complain that he could only process 300 messages a second but when I asked for his code I found out processing a message involved doing 40 or 50 inserts into the database so his 300 messages a second translated to 15,000 inserts a second. It's not unusuall to see Service Broker process a couple thousand messages a second but not on a server that's also doing a thousand order-entry transactions a second.

|||

Well, of course I trust the expertise of the guys who wrote Service Broker. My message size is configurable because they are essentially batches of data - so I can configure them based on the performance of the processing procedures. So I guess my question still remains...how best to process multiple messages using RECEIVE. Since it works just like SELECT, do I have to create some sort of cursor or does service broker offer a better pattern or syntax for processing each message in a RECEIVE without having to create a table to hold the data? Can I do some sort of join or apply or something? A simple code sample illustrating would be great. I have read those articles and links many times, but they don't quite answer the question.

|||Have a look at my blog at http://blogs.msdn.com/remusrusanu/archive/2006/10/14/writing-service-broker-procedures.aspx, it covers many of the questions you ask.sql

RECEIVE in transactions

Hello!
I have an an event notifications audit solution that I'm working on. I am
using this activation procedure.
CREATE PROCEDURE dbo.AuditReceive
AS
DECLARE @.AuditMessage TABLE (EventData xml);
RECEIVECAST(message_body AS XML)
FROM AuditQueue
INTO @.AuditMessage
INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerName,
DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
SELECTCAST(EventData.query('data(//PostTime)') AS varchar(23)),
CAST(EventData.query('data(//EventType)') AS sysname),
CAST(EventData.query('data(//LoginName)') AS sysname),
CAST(EventData.query('data(//UserName)') AS sysname),
CAST(EventData.query('data(//ServerName)') AS sysname),
CAST(EventData.query('data(//DatabaseName)') AS sysname),
CAST(EventData.query('data(//ObjectType)') AS sysname),
CAST(EventData.query('data(//SchemaName)') AS sysname),
CAST(EventData.query('data(//ObjectName)') AS sysname),
EventData
FROM @.AuditMessage
It's working nicely, but there is one weakness. I would like to have the
RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
not possible to use RECEIVE in transactions?
Best regards
Ola Hallengren
the books on line tell:
"The RECEIVE statement removes received messages from the queue unless the
queue specifies message retention. When the RETENTION setting for the queue
is ON, the RECEIVE statement updates the status column to 1 and leaves the
messages in the queue. When a transaction that contains a RECEIVE statement
rolls back, all changes to the queue within the transaction are also rolled
back, returning messages to the queue."
Could you expose the source code of the transaction you tried? there's
something wrong but i cannot imagine what without seeing an example.
Gilberto Zampatti
"Ola Hallengren" wrote:

> Hello!
> I have an an event notifications audit solution that I'm working on. I am
> using this activation procedure.
> CREATE PROCEDURE dbo.AuditReceive
> AS
> DECLARE @.AuditMessage TABLE (EventData xml);
> RECEIVECAST(message_body AS XML)
> FROM AuditQueue
> INTO @.AuditMessage
> INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerName,
> DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
> SELECTCAST(EventData.query('data(//PostTime)') AS varchar(23)),
> CAST(EventData.query('data(//EventType)') AS sysname),
> CAST(EventData.query('data(//LoginName)') AS sysname),
> CAST(EventData.query('data(//UserName)') AS sysname),
> CAST(EventData.query('data(//ServerName)') AS sysname),
> CAST(EventData.query('data(//DatabaseName)') AS sysname),
> CAST(EventData.query('data(//ObjectType)') AS sysname),
> CAST(EventData.query('data(//SchemaName)') AS sysname),
> CAST(EventData.query('data(//ObjectName)') AS sysname),
> EventData
> FROM @.AuditMessage
> It's working nicely, but there is one weakness. I would like to have the
> RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
> not possible to use RECEIVE in transactions?
> Best regards
> Ola Hallengren
|||> I would like to have the
> RECEIVE and the INSERT as a transaction, but I can't get it working.
In what way doesn't it work? Error messages? Something else?

> Is it
> not possible to use RECEIVE in transactions?
Yes, but you want to be careful. Say you determine that you can't process this message so you
rollback. Since messages are received in order, you will just read the same message next time.
Essentially, you end up in an endless loop. This is called a "poison message" and after 5 rollbacks
in a row, SB will disable the queue. I strongly suggest you get Roger Wolter's book on SB, this
topic is handled in chapter 8.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:38D8CD3E-9452-4EAE-A300-DB0F9B3A6F53@.microsoft.com...
> Hello!
> I have an an event notifications audit solution that I'm working on. I am
> using this activation procedure.
> CREATE PROCEDURE dbo.AuditReceive
> AS
> DECLARE @.AuditMessage TABLE (EventData xml);
> RECEIVE CAST(message_body AS XML)
> FROM AuditQueue
> INTO @.AuditMessage
> INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerName,
> DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
> SELECT CAST(EventData.query('data(//PostTime)') AS varchar(23)),
> CAST(EventData.query('data(//EventType)') AS sysname),
> CAST(EventData.query('data(//LoginName)') AS sysname),
> CAST(EventData.query('data(//UserName)') AS sysname),
> CAST(EventData.query('data(//ServerName)') AS sysname),
> CAST(EventData.query('data(//DatabaseName)') AS sysname),
> CAST(EventData.query('data(//ObjectType)') AS sysname),
> CAST(EventData.query('data(//SchemaName)') AS sysname),
> CAST(EventData.query('data(//ObjectName)') AS sysname),
> EventData
> FROM @.AuditMessage
> It's working nicely, but there is one weakness. I would like to have the
> RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
> not possible to use RECEIVE in transactions?
> Best regards
> Ola Hallengren
|||I overlooked one comment in Books Online"
"If the RECEIVE statement is not the first statement in a batch or stored
procedure, the preceding statement must be terminated with a semicolon (;),
the Transact-SQL statement terminator."
Now it's working.
About poisonous messages I have been doing some testing on that by changing
the datatypes in my auditing table, so that the insert fails. The queue then
gets deactivated as you write.
Now it's not so likely that the insert fails, since I have matching
datatypes. I mostly would like to have a transaction in case of a server
crash.
I have ordered the Roger Walters book today.
Thanks for you help.
/Ola
"Tibor Karaszi" wrote:

> In what way doesn't it work? Error messages? Something else?
>
> Yes, but you want to be careful. Say you determine that you can't process this message so you
> rollback. Since messages are received in order, you will just read the same message next time.
> Essentially, you end up in an endless loop. This is called a "poison message" and after 5 rollbacks
> in a row, SB will disable the queue. I strongly suggest you get Roger Wolter's book on SB, this
> topic is handled in chapter 8.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
> news:38D8CD3E-9452-4EAE-A300-DB0F9B3A6F53@.microsoft.com...
>

RECEIVE in transactions

Hello!
I have an an event notifications audit solution that I'm working on. I am
using this activation procedure.
CREATE PROCEDURE dbo.AuditReceive
AS
DECLARE @.AuditMessage TABLE (EventData xml);
RECEIVE CAST(message_body AS XML)
FROM AuditQueue
INTO @.AuditMessage
INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerName,
DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
SELECT CAST(EventData.query('data(//PostTime)') AS varchar(23)),
CAST(EventData.query('data(//EventType)') AS sysname),
CAST(EventData.query('data(//LoginName)') AS sysname),
CAST(EventData.query('data(//UserName)') AS sysname),
CAST(EventData.query('data(//ServerName)') AS sysname),
CAST(EventData.query('data(//DatabaseName)') AS sysname),
CAST(EventData.query('data(//ObjectType)') AS sysname),
CAST(EventData.query('data(//SchemaName)') AS sysname),
CAST(EventData.query('data(//ObjectName)') AS sysname),
EventData
FROM @.AuditMessage
It's working nicely, but there is one weakness. I would like to have the
RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
not possible to use RECEIVE in transactions?
Best regards
Ola Hallengrenthe books on line tell:
"The RECEIVE statement removes received messages from the queue unless the
queue specifies message retention. When the RETENTION setting for the queue
is ON, the RECEIVE statement updates the status column to 1 and leaves the
messages in the queue. When a transaction that contains a RECEIVE statement
rolls back, all changes to the queue within the transaction are also rolled
back, returning messages to the queue."
Could you expose the source code of the transaction you tried? there's
something wrong but i cannot imagine what without seeing an example.
Gilberto Zampatti
"Ola Hallengren" wrote:
> Hello!
> I have an an event notifications audit solution that I'm working on. I am
> using this activation procedure.
> CREATE PROCEDURE dbo.AuditReceive
> AS
> DECLARE @.AuditMessage TABLE (EventData xml);
> RECEIVE CAST(message_body AS XML)
> FROM AuditQueue
> INTO @.AuditMessage
> INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerName,
> DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
> SELECT CAST(EventData.query('data(//PostTime)') AS varchar(23)),
> CAST(EventData.query('data(//EventType)') AS sysname),
> CAST(EventData.query('data(//LoginName)') AS sysname),
> CAST(EventData.query('data(//UserName)') AS sysname),
> CAST(EventData.query('data(//ServerName)') AS sysname),
> CAST(EventData.query('data(//DatabaseName)') AS sysname),
> CAST(EventData.query('data(//ObjectType)') AS sysname),
> CAST(EventData.query('data(//SchemaName)') AS sysname),
> CAST(EventData.query('data(//ObjectName)') AS sysname),
> EventData
> FROM @.AuditMessage
> It's working nicely, but there is one weakness. I would like to have the
> RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
> not possible to use RECEIVE in transactions?
> Best regards
> Ola Hallengren|||> I would like to have the
> RECEIVE and the INSERT as a transaction, but I can't get it working.
In what way doesn't it work? Error messages? Something else?
> Is it
> not possible to use RECEIVE in transactions?
Yes, but you want to be careful. Say you determine that you can't process this message so you
rollback. Since messages are received in order, you will just read the same message next time.
Essentially, you end up in an endless loop. This is called a "poison message" and after 5 rollbacks
in a row, SB will disable the queue. I strongly suggest you get Roger Wolter's book on SB, this
topic is handled in chapter 8.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:38D8CD3E-9452-4EAE-A300-DB0F9B3A6F53@.microsoft.com...
> Hello!
> I have an an event notifications audit solution that I'm working on. I am
> using this activation procedure.
> CREATE PROCEDURE dbo.AuditReceive
> AS
> DECLARE @.AuditMessage TABLE (EventData xml);
> RECEIVE CAST(message_body AS XML)
> FROM AuditQueue
> INTO @.AuditMessage
> INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerName,
> DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
> SELECT CAST(EventData.query('data(//PostTime)') AS varchar(23)),
> CAST(EventData.query('data(//EventType)') AS sysname),
> CAST(EventData.query('data(//LoginName)') AS sysname),
> CAST(EventData.query('data(//UserName)') AS sysname),
> CAST(EventData.query('data(//ServerName)') AS sysname),
> CAST(EventData.query('data(//DatabaseName)') AS sysname),
> CAST(EventData.query('data(//ObjectType)') AS sysname),
> CAST(EventData.query('data(//SchemaName)') AS sysname),
> CAST(EventData.query('data(//ObjectName)') AS sysname),
> EventData
> FROM @.AuditMessage
> It's working nicely, but there is one weakness. I would like to have the
> RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
> not possible to use RECEIVE in transactions?
> Best regards
> Ola Hallengren|||I overlooked one comment in Books Online"
"If the RECEIVE statement is not the first statement in a batch or stored
procedure, the preceding statement must be terminated with a semicolon (;),
the Transact-SQL statement terminator."
Now it's working.
About poisonous messages I have been doing some testing on that by changing
the datatypes in my auditing table, so that the insert fails. The queue then
gets deactivated as you write.
Now it's not so likely that the insert fails, since I have matching
datatypes. I mostly would like to have a transaction in case of a server
crash.
I have ordered the Roger Walters book today.
Thanks for you help.
/Ola
"Tibor Karaszi" wrote:
> > I would like to have the
> > RECEIVE and the INSERT as a transaction, but I can't get it working.
> In what way doesn't it work? Error messages? Something else?
>
> > Is it
> > not possible to use RECEIVE in transactions?
> Yes, but you want to be careful. Say you determine that you can't process this message so you
> rollback. Since messages are received in order, you will just read the same message next time.
> Essentially, you end up in an endless loop. This is called a "poison message" and after 5 rollbacks
> in a row, SB will disable the queue. I strongly suggest you get Roger Wolter's book on SB, this
> topic is handled in chapter 8.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
> news:38D8CD3E-9452-4EAE-A300-DB0F9B3A6F53@.microsoft.com...
> > Hello!
> >
> > I have an an event notifications audit solution that I'm working on. I am
> > using this activation procedure.
> >
> > CREATE PROCEDURE dbo.AuditReceive
> > AS
> > DECLARE @.AuditMessage TABLE (EventData xml);
> >
> > RECEIVE CAST(message_body AS XML)
> > FROM AuditQueue
> > INTO @.AuditMessage
> >
> > INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerName,
> > DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
> > SELECT CAST(EventData.query('data(//PostTime)') AS varchar(23)),
> > CAST(EventData.query('data(//EventType)') AS sysname),
> > CAST(EventData.query('data(//LoginName)') AS sysname),
> > CAST(EventData.query('data(//UserName)') AS sysname),
> > CAST(EventData.query('data(//ServerName)') AS sysname),
> > CAST(EventData.query('data(//DatabaseName)') AS sysname),
> > CAST(EventData.query('data(//ObjectType)') AS sysname),
> > CAST(EventData.query('data(//SchemaName)') AS sysname),
> > CAST(EventData.query('data(//ObjectName)') AS sysname),
> > EventData
> > FROM @.AuditMessage
> >
> > It's working nicely, but there is one weakness. I would like to have the
> > RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
> > not possible to use RECEIVE in transactions?
> >
> > Best regards
> >
> > Ola Hallengren
>sql

RECEIVE in transactions

Hello!
I have an an event notifications audit solution that I'm working on. I am
using this activation procedure.
CREATE PROCEDURE dbo.AuditReceive
AS
DECLARE @.AuditMessage TABLE (EventData xml);
RECEIVE CAST(message_body AS XML)
FROM AuditQueue
INTO @.AuditMessage
INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerName,
DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
SELECT CAST(EventData.query('data(//PostTime)') AS varchar(23)),
CAST(EventData.query('data(//EventType)') AS sysname),
CAST(EventData.query('data(//LoginName)') AS sysname),
CAST(EventData.query('data(//UserName)') AS sysname),
CAST(EventData.query('data(//ServerName)') AS sysname),
CAST(EventData.query('data(//DatabaseName)') AS sysname),
CAST(EventData.query('data(//ObjectType)') AS sysname),
CAST(EventData.query('data(//SchemaName)') AS sysname),
CAST(EventData.query('data(//ObjectName)') AS sysname),
EventData
FROM @.AuditMessage
It's working nicely, but there is one weakness. I would like to have the
RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
not possible to use RECEIVE in transactions?
Best regards
Ola Hallengrenthe books on line tell:
"The RECEIVE statement removes received messages from the queue unless the
queue specifies message retention. When the RETENTION setting for the queue
is ON, the RECEIVE statement updates the status column to 1 and leaves the
messages in the queue. When a transaction that contains a RECEIVE statement
rolls back, all changes to the queue within the transaction are also rolled
back, returning messages to the queue."
Could you expose the source code of the transaction you tried? there's
something wrong but i cannot imagine what without seeing an example.
Gilberto Zampatti
"Ola Hallengren" wrote:

> Hello!
> I have an an event notifications audit solution that I'm working on. I am
> using this activation procedure.
> CREATE PROCEDURE dbo.AuditReceive
> AS
> DECLARE @.AuditMessage TABLE (EventData xml);
> RECEIVE CAST(message_body AS XML)
> FROM AuditQueue
> INTO @.AuditMessage
> INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerNam
e,
> DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
> SELECT CAST(EventData.query('data(//PostTime)') AS varchar(23)),
> CAST(EventData.query('data(//EventType)') AS sysname),
> CAST(EventData.query('data(//LoginName)') AS sysname),
> CAST(EventData.query('data(//UserName)') AS sysname),
> CAST(EventData.query('data(//ServerName)') AS sysname),
> CAST(EventData.query('data(//DatabaseName)') AS sysname),
> CAST(EventData.query('data(//ObjectType)') AS sysname),
> CAST(EventData.query('data(//SchemaName)') AS sysname),
> CAST(EventData.query('data(//ObjectName)') AS sysname),
> EventData
> FROM @.AuditMessage
> It's working nicely, but there is one weakness. I would like to have the
> RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
> not possible to use RECEIVE in transactions?
> Best regards
> Ola Hallengren|||> I would like to have the
> RECEIVE and the INSERT as a transaction, but I can't get it working.
In what way doesn't it work? Error messages? Something else?

> Is it
> not possible to use RECEIVE in transactions?
Yes, but you want to be careful. Say you determine that you can't process th
is message so you
rollback. Since messages are received in order, you will just read the same
message next time.
Essentially, you end up in an endless loop. This is called a "poison message
" and after 5 rollbacks
in a row, SB will disable the queue. I strongly suggest you get Roger Wolter
's book on SB, this
topic is handled in chapter 8.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:38D8CD3E-9452-4EAE-A300-DB0F9B3A6F53@.microsoft.com...
> Hello!
> I have an an event notifications audit solution that I'm working on. I am
> using this activation procedure.
> CREATE PROCEDURE dbo.AuditReceive
> AS
> DECLARE @.AuditMessage TABLE (EventData xml);
> RECEIVE CAST(message_body AS XML)
> FROM AuditQueue
> INTO @.AuditMessage
> INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerNam
e,
> DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
> SELECT CAST(EventData.query('data(//PostTime)') AS varchar(23)),
> CAST(EventData.query('data(//EventType)') AS sysname),
> CAST(EventData.query('data(//LoginName)') AS sysname),
> CAST(EventData.query('data(//UserName)') AS sysname),
> CAST(EventData.query('data(//ServerName)') AS sysname),
> CAST(EventData.query('data(//DatabaseName)') AS sysname),
> CAST(EventData.query('data(//ObjectType)') AS sysname),
> CAST(EventData.query('data(//SchemaName)') AS sysname),
> CAST(EventData.query('data(//ObjectName)') AS sysname),
> EventData
> FROM @.AuditMessage
> It's working nicely, but there is one weakness. I would like to have the
> RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
> not possible to use RECEIVE in transactions?
> Best regards
> Ola Hallengren|||I overlooked one comment in Books Online"
"If the RECEIVE statement is not the first statement in a batch or stored
procedure, the preceding statement must be terminated with a semicolon (;),
the Transact-SQL statement terminator."
Now it's working.
About poisonous messages I have been doing some testing on that by changing
the datatypes in my auditing table, so that the insert fails. The queue then
gets deactivated as you write.
Now it's not so likely that the insert fails, since I have matching
datatypes. I mostly would like to have a transaction in case of a server
crash.
I have ordered the Roger Walters book today.
Thanks for you help.
/Ola
"Tibor Karaszi" wrote:

> In what way doesn't it work? Error messages? Something else?
>
> Yes, but you want to be careful. Say you determine that you can't process
this message so you
> rollback. Since messages are received in order, you will just read the sam
e message next time.
> Essentially, you end up in an endless loop. This is called a "poison messa
ge" and after 5 rollbacks
> in a row, SB will disable the queue. I strongly suggest you get Roger Wolt
er's book on SB, this
> topic is handled in chapter 8.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in messag
e
> news:38D8CD3E-9452-4EAE-A300-DB0F9B3A6F53@.microsoft.com...
>

RebuildLog

hi friends,

one of my database corrupted , after setting that database into emergency mode , dbcc Rebuildlog was not working in sql server 2005, so how to proceed further?Hello yasodhakalimuthu. I must tell you that you have mistakenly posted in the Articles section of the SQL Server Group. The SQL Server help forum is here.

Thanks for joining. I hope that you find your way around the site.

Rebuilding clustered index

Working on a vendor database upgrade. They want the clustered index to be
rebuilt. I have huge table with more than 10 million records....have a
clustered index and 10 non-clustered indexes...
what are my options,
As an example lets take Orders table and CIX_Orders is the clustered index.
1. DBCC DBReindex ('Northwind.dbo.Orders', CIX_Orders)
2.create clustered index [CIX_Orders] on [dbo].[Orders_Temp](
1;OrderID])
with drop_existing
How does the above two differ?
Thanks very muchOn SQL 2000 and 2005 these two should be the same.
HTH
Kalen Delaney, SQL Server MVP
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:5DF73102-DD50-4635-B712-C7FDA3609F9F@.microsoft.com...
> Working on a vendor database upgrade. They want the clustered index to be
> rebuilt. I have huge table with more than 10 million records....have a
> clustered index and 10 non-clustered indexes...
> what are my options,
> As an example lets take Orders table and CIX_Orders is the clustered
> index.
> 1. DBCC DBReindex ('Northwind.dbo.Orders', CIX_Orders)
> 2.create clustered index [CIX_Orders] on [dbo].[Orders_Temp](&
#91;OrderID])
> with drop_existing
> How does the above two differ?
> Thanks very much|||Thanks Kalen...
I am using SQL 2000
My questions is will my non-clustered indexes if I rebuild my clustered
index ?
"Kalen Delaney" wrote:

> On SQL 2000 and 2005 these two should be the same.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
> news:5DF73102-DD50-4635-B712-C7FDA3609F9F@.microsoft.com...
>
>|||Hi,
Yes it will rebuild all the non-clustered indexes if you rebuild the
clustered.
This is because the nonclustered index contains the keys of clustered
index. So to retake the new set of clustered keys; non clustered index is
rebuild.
Thanks
Hari
SQL Server MVP
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:7E10DBD5-A7CF-47C8-BC8F-6063AE24F4A2@.microsoft.com...[vbcol=seagreen]
> Thanks Kalen...
> I am using SQL 2000
> My questions is will my non-clustered indexes if I rebuild my clustered
> index ?
>
> "Kalen Delaney" wrote:
>|||Not if you use one of these two methods, and you aren't redefining anything
about the indexes. If you are just rebuilding the indexes exactly as they
were for the purpose of removing fragmentation, the nonclustered indexes
will not have to be touched. That is why these two methods are preferred
over a separate drop index and create index.
HTH
Kalen Delaney, SQL Server MVP
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:7E10DBD5-A7CF-47C8-BC8F-6063AE24F4A2@.microsoft.com...[vbcol=seagreen]
> Thanks Kalen...
> I am using SQL 2000
> My questions is will my non-clustered indexes if I rebuild my clustered
> index ?
>
> "Kalen Delaney" wrote:
>|||That is only true if the Clustered index is not unique. If it is unique and
you rebuild the clustered index it does not automatically rebuild the
non-clustered using DBREINDEX. In 2005 it does not matter if the clustered
index is unique or not since they handle the uniquifier in a much better
manor that does not change the uniquifier values when reindexed.
Andrew J. Kelly SQL MVP
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23iVBCS%23xGHA.4840@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Yes it will rebuild all the non-clustered indexes if you rebuild the
> clustered.
> This is because the nonclustered index contains the keys of clustered
> index. So to retake the new set of clustered keys; non clustered index is
> rebuild.
> Thanks
> Hari
> SQL Server MVP
> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
> news:7E10DBD5-A7CF-47C8-BC8F-6063AE24F4A2@.microsoft.com...
>|||Hi Ranga
It is usually more important for the non-clustered indexes to be rebuilt
than clustered indexes. The non-clustered indexes purely provide support for
query performance whilst clustered indexes are really the table storage
structure, so non-clustered indexes always play a performance role whilst
clustered indexes only provide performance support sometimes. Given the
specialised role of non-clustered indexes, it's critical that they be
re-built if you're doing this for performance reasons. We often rebuild our
non-clustered indexes many times between clustered index rebuilds..
Regards,
Greg Linwood
SQL Server MVP
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:7E10DBD5-A7CF-47C8-BC8F-6063AE24F4A2@.microsoft.com...[vbcol=seagreen]
> Thanks Kalen...
> I am using SQL 2000
> My questions is will my non-clustered indexes if I rebuild my clustered
> index ?
>
> "Kalen Delaney" wrote:
>|||Hi Greg
Can you elaborate on this?
While I agree that nonclustered index have more of a performance support
role in more cases, I don't see what that has to do with rebuilding them.
How exactly are you finding that rebuilding helps with the performance
support of nonclustered indexes?
In particular, if the statistics are up to date, and you're using the nc
index to find just a few rows, why is rebuilding a necessary thing?
Since clustered indexes are the table storage, any scan or partial scan of
the data is impacted by the fragmentation of the clustered index, making it
imperative that the clustered index be rebuilt.
HTH
Kalen Delaney, SQL Server MVP
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:u%239yjE$xGHA.1936@.TK2MSFTNGP06.phx.gbl...
> Hi Ranga
> It is usually more important for the non-clustered indexes to be rebuilt
> than clustered indexes. The non-clustered indexes purely provide support
> for query performance whilst clustered indexes are really the table
> storage structure, so non-clustered indexes always play a performance role
> whilst clustered indexes only provide performance support sometimes. Given
> the specialised role of non-clustered indexes, it's critical that they be
> re-built if you're doing this for performance reasons. We often rebuild
> our non-clustered indexes many times between clustered index rebuilds..
> Regards,
> Greg Linwood
> SQL Server MVP
> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
> news:7E10DBD5-A7CF-47C8-BC8F-6063AE24F4A2@.microsoft.com...
>|||Hi Kalen
Re> "and you're using the nc index to find just a few rows". This isn't a
good assumption b/c in most OLTPs, ncix's are range-scanned as much as
they're seek'd. Any range or full scan should ideally occur within a ncix
where page density is higher & read io is therefore more efficient than it
can ever be in a cix.. Any range or full scan that occurs within a cix will
always be less efficient other than in the obscure scenario where all
columns in a table are actually required by the query.
Re> Since clustered indexes are the table storage, any scan or partial scan
of the data is impacted by the fragmentation.
Not if the ncix covers the query. In this case, the fragmentation of the
ncix is all that matters & fragmentation in the cix is immaterial. Ideally,
all performance critical queries should be covered by ncixs, so this is
fairly important.
Re>How exactly are you finding that rebuilding helps with the performance
support of nonclustered indexes?
We have empirically measured proof and documented user feedback that
rebuilding ncixs alone usually improves the overall performance of otherwise
well configured oltp system. Happy to show you the data next time you're out
here too (c:
Regards,
Greg Linwood
SQL Server MVP
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23B3nhY$xGHA.4524@.TK2MSFTNGP04.phx.gbl...
> Hi Greg
> Can you elaborate on this?
> While I agree that nonclustered index have more of a performance support
> role in more cases, I don't see what that has to do with rebuilding them.
> How exactly are you finding that rebuilding helps with the performance
> support of nonclustered indexes?
> In particular, if the statistics are up to date, and you're using the nc
> index to find just a few rows, why is rebuilding a necessary thing?
> Since clustered indexes are the table storage, any scan or partial scan of
> the data is impacted by the fragmentation of the clustered index, making
> it imperative that the clustered index be rebuilt.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:u%239yjE$xGHA.1936@.TK2MSFTNGP06.phx.gbl...
>|||Thanks everyone for their feedback..this is a very interesting topic...I hav
e
another Q..If the clustered index is part of PK constraint, what will the
exact syntax to rebuild it using the both methods (DBReindex and Create with
Drop_Existing)
Thanks,
Ranga
"Greg Linwood" wrote:

> Hi Kalen
> Re> "and you're using the nc index to find just a few rows". This isn't a
> good assumption b/c in most OLTPs, ncix's are range-scanned as much as
> they're seek'd. Any range or full scan should ideally occur within a ncix
> where page density is higher & read io is therefore more efficient than it
> can ever be in a cix.. Any range or full scan that occurs within a cix wil
l
> always be less efficient other than in the obscure scenario where all
> columns in a table are actually required by the query.
> Re> Since clustered indexes are the table storage, any scan or partial sca
n
> of the data is impacted by the fragmentation.
> Not if the ncix covers the query. In this case, the fragmentation of the
> ncix is all that matters & fragmentation in the cix is immaterial. Ideally
,
> all performance critical queries should be covered by ncixs, so this is
> fairly important.
> Re>How exactly are you finding that rebuilding helps with the performance
> support of nonclustered indexes?
> We have empirically measured proof and documented user feedback that
> rebuilding ncixs alone usually improves the overall performance of otherwi
se
> well configured oltp system. Happy to show you the data next time you're o
ut
> here too (c:
> Regards,
> Greg Linwood
> SQL Server MVP
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23B3nhY$xGHA.4524@.TK2MSFTNGP04.phx.gbl...
>
>

Rebuilding clustered index

Working on a vendor database upgrade. They want the clustered index to be
rebuilt. I have huge table with more than 10 million records....have a
clustered index and 10 non-clustered indexes...
what are my options,
As an example lets take Orders table and CIX_Orders is the clustered index.
1. DBCC DBReindex ('Northwind.dbo.Orders', CIX_Orders)
2.create clustered index [CIX_Orders] on [dbo].[Orders_Temp]([OrderID])
with drop_existing
How does the above two differ?
Thanks very muchOn SQL 2000 and 2005 these two should be the same.
--
HTH
Kalen Delaney, SQL Server MVP
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:5DF73102-DD50-4635-B712-C7FDA3609F9F@.microsoft.com...
> Working on a vendor database upgrade. They want the clustered index to be
> rebuilt. I have huge table with more than 10 million records....have a
> clustered index and 10 non-clustered indexes...
> what are my options,
> As an example lets take Orders table and CIX_Orders is the clustered
> index.
> 1. DBCC DBReindex ('Northwind.dbo.Orders', CIX_Orders)
> 2.create clustered index [CIX_Orders] on [dbo].[Orders_Temp]([OrderID])
> with drop_existing
> How does the above two differ?
> Thanks very much|||Thanks Kalen...
I am using SQL 2000
My questions is will my non-clustered indexes if I rebuild my clustered
index ?
"Kalen Delaney" wrote:
> On SQL 2000 and 2005 these two should be the same.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
> news:5DF73102-DD50-4635-B712-C7FDA3609F9F@.microsoft.com...
> > Working on a vendor database upgrade. They want the clustered index to be
> > rebuilt. I have huge table with more than 10 million records....have a
> > clustered index and 10 non-clustered indexes...
> >
> > what are my options,
> >
> > As an example lets take Orders table and CIX_Orders is the clustered
> > index.
> >
> > 1. DBCC DBReindex ('Northwind.dbo.Orders', CIX_Orders)
> >
> > 2.create clustered index [CIX_Orders] on [dbo].[Orders_Temp]([OrderID])
> > with drop_existing
> >
> > How does the above two differ?
> >
> > Thanks very much
>
>|||Hi,
Yes it will rebuild all the non-clustered indexes if you rebuild the
clustered.
This is because the nonclustered index contains the keys of clustered
index. So to retake the new set of clustered keys; non clustered index is
rebuild.
Thanks
Hari
SQL Server MVP
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:7E10DBD5-A7CF-47C8-BC8F-6063AE24F4A2@.microsoft.com...
> Thanks Kalen...
> I am using SQL 2000
> My questions is will my non-clustered indexes if I rebuild my clustered
> index ?
>
> "Kalen Delaney" wrote:
>> On SQL 2000 and 2005 these two should be the same.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
>> news:5DF73102-DD50-4635-B712-C7FDA3609F9F@.microsoft.com...
>> > Working on a vendor database upgrade. They want the clustered index to
>> > be
>> > rebuilt. I have huge table with more than 10 million records....have a
>> > clustered index and 10 non-clustered indexes...
>> >
>> > what are my options,
>> >
>> > As an example lets take Orders table and CIX_Orders is the clustered
>> > index.
>> >
>> > 1. DBCC DBReindex ('Northwind.dbo.Orders', CIX_Orders)
>> >
>> > 2.create clustered index [CIX_Orders] on [dbo].[Orders_Temp]([OrderID])
>> > with drop_existing
>> >
>> > How does the above two differ?
>> >
>> > Thanks very much
>>|||Not if you use one of these two methods, and you aren't redefining anything
about the indexes. If you are just rebuilding the indexes exactly as they
were for the purpose of removing fragmentation, the nonclustered indexes
will not have to be touched. That is why these two methods are preferred
over a separate drop index and create index.
--
HTH
Kalen Delaney, SQL Server MVP
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:7E10DBD5-A7CF-47C8-BC8F-6063AE24F4A2@.microsoft.com...
> Thanks Kalen...
> I am using SQL 2000
> My questions is will my non-clustered indexes if I rebuild my clustered
> index ?
>
> "Kalen Delaney" wrote:
>> On SQL 2000 and 2005 these two should be the same.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
>> news:5DF73102-DD50-4635-B712-C7FDA3609F9F@.microsoft.com...
>> > Working on a vendor database upgrade. They want the clustered index to
>> > be
>> > rebuilt. I have huge table with more than 10 million records....have a
>> > clustered index and 10 non-clustered indexes...
>> >
>> > what are my options,
>> >
>> > As an example lets take Orders table and CIX_Orders is the clustered
>> > index.
>> >
>> > 1. DBCC DBReindex ('Northwind.dbo.Orders', CIX_Orders)
>> >
>> > 2.create clustered index [CIX_Orders] on [dbo].[Orders_Temp]([OrderID])
>> > with drop_existing
>> >
>> > How does the above two differ?
>> >
>> > Thanks very much
>>|||That is only true if the Clustered index is not unique. If it is unique and
you rebuild the clustered index it does not automatically rebuild the
non-clustered using DBREINDEX. In 2005 it does not matter if the clustered
index is unique or not since they handle the uniquifier in a much better
manor that does not change the uniquifier values when reindexed.
--
Andrew J. Kelly SQL MVP
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23iVBCS%23xGHA.4840@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Yes it will rebuild all the non-clustered indexes if you rebuild the
> clustered.
> This is because the nonclustered index contains the keys of clustered
> index. So to retake the new set of clustered keys; non clustered index is
> rebuild.
> Thanks
> Hari
> SQL Server MVP
> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
> news:7E10DBD5-A7CF-47C8-BC8F-6063AE24F4A2@.microsoft.com...
>> Thanks Kalen...
>> I am using SQL 2000
>> My questions is will my non-clustered indexes if I rebuild my clustered
>> index ?
>>
>> "Kalen Delaney" wrote:
>> On SQL 2000 and 2005 these two should be the same.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
>> news:5DF73102-DD50-4635-B712-C7FDA3609F9F@.microsoft.com...
>> > Working on a vendor database upgrade. They want the clustered index to
>> > be
>> > rebuilt. I have huge table with more than 10 million records....have
>> > a
>> > clustered index and 10 non-clustered indexes...
>> >
>> > what are my options,
>> >
>> > As an example lets take Orders table and CIX_Orders is the clustered
>> > index.
>> >
>> > 1. DBCC DBReindex ('Northwind.dbo.Orders', CIX_Orders)
>> >
>> > 2.create clustered index [CIX_Orders] on
>> > [dbo].[Orders_Temp]([OrderID])
>> > with drop_existing
>> >
>> > How does the above two differ?
>> >
>> > Thanks very much
>>
>|||Hi Ranga
It is usually more important for the non-clustered indexes to be rebuilt
than clustered indexes. The non-clustered indexes purely provide support for
query performance whilst clustered indexes are really the table storage
structure, so non-clustered indexes always play a performance role whilst
clustered indexes only provide performance support sometimes. Given the
specialised role of non-clustered indexes, it's critical that they be
re-built if you're doing this for performance reasons. We often rebuild our
non-clustered indexes many times between clustered index rebuilds..
Regards,
Greg Linwood
SQL Server MVP
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:7E10DBD5-A7CF-47C8-BC8F-6063AE24F4A2@.microsoft.com...
> Thanks Kalen...
> I am using SQL 2000
> My questions is will my non-clustered indexes if I rebuild my clustered
> index ?
>
> "Kalen Delaney" wrote:
>> On SQL 2000 and 2005 these two should be the same.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
>> news:5DF73102-DD50-4635-B712-C7FDA3609F9F@.microsoft.com...
>> > Working on a vendor database upgrade. They want the clustered index to
>> > be
>> > rebuilt. I have huge table with more than 10 million records....have a
>> > clustered index and 10 non-clustered indexes...
>> >
>> > what are my options,
>> >
>> > As an example lets take Orders table and CIX_Orders is the clustered
>> > index.
>> >
>> > 1. DBCC DBReindex ('Northwind.dbo.Orders', CIX_Orders)
>> >
>> > 2.create clustered index [CIX_Orders] on [dbo].[Orders_Temp]([OrderID])
>> > with drop_existing
>> >
>> > How does the above two differ?
>> >
>> > Thanks very much
>>|||Hi Greg
Can you elaborate on this?
While I agree that nonclustered index have more of a performance support
role in more cases, I don't see what that has to do with rebuilding them.
How exactly are you finding that rebuilding helps with the performance
support of nonclustered indexes?
In particular, if the statistics are up to date, and you're using the nc
index to find just a few rows, why is rebuilding a necessary thing?
Since clustered indexes are the table storage, any scan or partial scan of
the data is impacted by the fragmentation of the clustered index, making it
imperative that the clustered index be rebuilt.
--
HTH
Kalen Delaney, SQL Server MVP
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:u%239yjE$xGHA.1936@.TK2MSFTNGP06.phx.gbl...
> Hi Ranga
> It is usually more important for the non-clustered indexes to be rebuilt
> than clustered indexes. The non-clustered indexes purely provide support
> for query performance whilst clustered indexes are really the table
> storage structure, so non-clustered indexes always play a performance role
> whilst clustered indexes only provide performance support sometimes. Given
> the specialised role of non-clustered indexes, it's critical that they be
> re-built if you're doing this for performance reasons. We often rebuild
> our non-clustered indexes many times between clustered index rebuilds..
> Regards,
> Greg Linwood
> SQL Server MVP
> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
> news:7E10DBD5-A7CF-47C8-BC8F-6063AE24F4A2@.microsoft.com...
>> Thanks Kalen...
>> I am using SQL 2000
>> My questions is will my non-clustered indexes if I rebuild my clustered
>> index ?
>>
>> "Kalen Delaney" wrote:
>> On SQL 2000 and 2005 these two should be the same.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
>> news:5DF73102-DD50-4635-B712-C7FDA3609F9F@.microsoft.com...
>> > Working on a vendor database upgrade. They want the clustered index to
>> > be
>> > rebuilt. I have huge table with more than 10 million records....have
>> > a
>> > clustered index and 10 non-clustered indexes...
>> >
>> > what are my options,
>> >
>> > As an example lets take Orders table and CIX_Orders is the clustered
>> > index.
>> >
>> > 1. DBCC DBReindex ('Northwind.dbo.Orders', CIX_Orders)
>> >
>> > 2.create clustered index [CIX_Orders] on
>> > [dbo].[Orders_Temp]([OrderID])
>> > with drop_existing
>> >
>> > How does the above two differ?
>> >
>> > Thanks very much
>>
>|||Hi Kalen
Re> "and you're using the nc index to find just a few rows". This isn't a
good assumption b/c in most OLTPs, ncix's are range-scanned as much as
they're seek'd. Any range or full scan should ideally occur within a ncix
where page density is higher & read io is therefore more efficient than it
can ever be in a cix.. Any range or full scan that occurs within a cix will
always be less efficient other than in the obscure scenario where all
columns in a table are actually required by the query.
Re> Since clustered indexes are the table storage, any scan or partial scan
of the data is impacted by the fragmentation.
Not if the ncix covers the query. In this case, the fragmentation of the
ncix is all that matters & fragmentation in the cix is immaterial. Ideally,
all performance critical queries should be covered by ncixs, so this is
fairly important.
Re>How exactly are you finding that rebuilding helps with the performance
support of nonclustered indexes?
We have empirically measured proof and documented user feedback that
rebuilding ncixs alone usually improves the overall performance of otherwise
well configured oltp system. Happy to show you the data next time you're out
here too (c:
Regards,
Greg Linwood
SQL Server MVP
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23B3nhY$xGHA.4524@.TK2MSFTNGP04.phx.gbl...
> Hi Greg
> Can you elaborate on this?
> While I agree that nonclustered index have more of a performance support
> role in more cases, I don't see what that has to do with rebuilding them.
> How exactly are you finding that rebuilding helps with the performance
> support of nonclustered indexes?
> In particular, if the statistics are up to date, and you're using the nc
> index to find just a few rows, why is rebuilding a necessary thing?
> Since clustered indexes are the table storage, any scan or partial scan of
> the data is impacted by the fragmentation of the clustered index, making
> it imperative that the clustered index be rebuilt.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:u%239yjE$xGHA.1936@.TK2MSFTNGP06.phx.gbl...
>> Hi Ranga
>> It is usually more important for the non-clustered indexes to be rebuilt
>> than clustered indexes. The non-clustered indexes purely provide support
>> for query performance whilst clustered indexes are really the table
>> storage structure, so non-clustered indexes always play a performance
>> role whilst clustered indexes only provide performance support sometimes.
>> Given the specialised role of non-clustered indexes, it's critical that
>> they be re-built if you're doing this for performance reasons. We often
>> rebuild our non-clustered indexes many times between clustered index
>> rebuilds..
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
>> news:7E10DBD5-A7CF-47C8-BC8F-6063AE24F4A2@.microsoft.com...
>> Thanks Kalen...
>> I am using SQL 2000
>> My questions is will my non-clustered indexes if I rebuild my clustered
>> index ?
>>
>> "Kalen Delaney" wrote:
>> On SQL 2000 and 2005 these two should be the same.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
>> news:5DF73102-DD50-4635-B712-C7FDA3609F9F@.microsoft.com...
>> > Working on a vendor database upgrade. They want the clustered index
>> > to be
>> > rebuilt. I have huge table with more than 10 million records....have
>> > a
>> > clustered index and 10 non-clustered indexes...
>> >
>> > what are my options,
>> >
>> > As an example lets take Orders table and CIX_Orders is the clustered
>> > index.
>> >
>> > 1. DBCC DBReindex ('Northwind.dbo.Orders', CIX_Orders)
>> >
>> > 2.create clustered index [CIX_Orders] on
>> > [dbo].[Orders_Temp]([OrderID])
>> > with drop_existing
>> >
>> > How does the above two differ?
>> >
>> > Thanks very much
>>
>>
>|||Thanks everyone for their feedback..this is a very interesting topic...I have
another Q..If the clustered index is part of PK constraint, what will the
exact syntax to rebuild it using the both methods (DBReindex and Create with
Drop_Existing)
Thanks,
Ranga
"Greg Linwood" wrote:
> Hi Kalen
> Re> "and you're using the nc index to find just a few rows". This isn't a
> good assumption b/c in most OLTPs, ncix's are range-scanned as much as
> they're seek'd. Any range or full scan should ideally occur within a ncix
> where page density is higher & read io is therefore more efficient than it
> can ever be in a cix.. Any range or full scan that occurs within a cix will
> always be less efficient other than in the obscure scenario where all
> columns in a table are actually required by the query.
> Re> Since clustered indexes are the table storage, any scan or partial scan
> of the data is impacted by the fragmentation.
> Not if the ncix covers the query. In this case, the fragmentation of the
> ncix is all that matters & fragmentation in the cix is immaterial. Ideally,
> all performance critical queries should be covered by ncixs, so this is
> fairly important.
> Re>How exactly are you finding that rebuilding helps with the performance
> support of nonclustered indexes?
> We have empirically measured proof and documented user feedback that
> rebuilding ncixs alone usually improves the overall performance of otherwise
> well configured oltp system. Happy to show you the data next time you're out
> here too (c:
> Regards,
> Greg Linwood
> SQL Server MVP
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23B3nhY$xGHA.4524@.TK2MSFTNGP04.phx.gbl...
> > Hi Greg
> >
> > Can you elaborate on this?
> >
> > While I agree that nonclustered index have more of a performance support
> > role in more cases, I don't see what that has to do with rebuilding them.
> >
> > How exactly are you finding that rebuilding helps with the performance
> > support of nonclustered indexes?
> >
> > In particular, if the statistics are up to date, and you're using the nc
> > index to find just a few rows, why is rebuilding a necessary thing?
> >
> > Since clustered indexes are the table storage, any scan or partial scan of
> > the data is impacted by the fragmentation of the clustered index, making
> > it imperative that the clustered index be rebuilt.
> >
> > --
> > HTH
> > Kalen Delaney, SQL Server MVP
> >
> >
> > "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> > news:u%239yjE$xGHA.1936@.TK2MSFTNGP06.phx.gbl...
> >> Hi Ranga
> >>
> >> It is usually more important for the non-clustered indexes to be rebuilt
> >> than clustered indexes. The non-clustered indexes purely provide support
> >> for query performance whilst clustered indexes are really the table
> >> storage structure, so non-clustered indexes always play a performance
> >> role whilst clustered indexes only provide performance support sometimes.
> >> Given the specialised role of non-clustered indexes, it's critical that
> >> they be re-built if you're doing this for performance reasons. We often
> >> rebuild our non-clustered indexes many times between clustered index
> >> rebuilds..
> >>
> >> Regards,
> >> Greg Linwood
> >> SQL Server MVP
> >>
> >> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
> >> news:7E10DBD5-A7CF-47C8-BC8F-6063AE24F4A2@.microsoft.com...
> >> Thanks Kalen...
> >> I am using SQL 2000
> >> My questions is will my non-clustered indexes if I rebuild my clustered
> >> index ?
> >>
> >>
> >>
> >> "Kalen Delaney" wrote:
> >>
> >> On SQL 2000 and 2005 these two should be the same.
> >>
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >>
> >>
> >> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
> >> news:5DF73102-DD50-4635-B712-C7FDA3609F9F@.microsoft.com...
> >> > Working on a vendor database upgrade. They want the clustered index
> >> > to be
> >> > rebuilt. I have huge table with more than 10 million records....have
> >> > a
> >> > clustered index and 10 non-clustered indexes...
> >> >
> >> > what are my options,
> >> >
> >> > As an example lets take Orders table and CIX_Orders is the clustered
> >> > index.
> >> >
> >> > 1. DBCC DBReindex ('Northwind.dbo.Orders', CIX_Orders)
> >> >
> >> > 2.create clustered index [CIX_Orders] on
> >> > [dbo].[Orders_Temp]([OrderID])
> >> > with drop_existing
> >> >
> >> > How does the above two differ?
> >> >
> >> > Thanks very much
> >>
> >>
> >>
> >>
> >>
> >
> >
>
>|||Hi Greg
Thanks for the detailed response. These are some very interesting points to
think about. However, you said:
> Re> Since clustered indexes are the table storage, any scan or partial
> scan of the data is impacted by the fragmentation.
> Not if the ncix covers the query.
But...if the ncix covers the query, then you don't have a scan of the data
level.
Which is what I was referring to.
:-)
--
HTH
Kalen Delaney, SQL Server MVP
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:OfDqnrAyGHA.4548@.TK2MSFTNGP05.phx.gbl...
> Hi Kalen
> Re> "and you're using the nc index to find just a few rows". This isn't a
> good assumption b/c in most OLTPs, ncix's are range-scanned as much as
> they're seek'd. Any range or full scan should ideally occur within a ncix
> where page density is higher & read io is therefore more efficient than it
> can ever be in a cix.. Any range or full scan that occurs within a cix
> will always be less efficient other than in the obscure scenario where all
> columns in a table are actually required by the query.
> Re> Since clustered indexes are the table storage, any scan or partial
> scan of the data is impacted by the fragmentation.
> Not if the ncix covers the query. In this case, the fragmentation of the
> ncix is all that matters & fragmentation in the cix is immaterial.
> Ideally, all performance critical queries should be covered by ncixs, so
> this is fairly important.
> Re>How exactly are you finding that rebuilding helps with the performance
> support of nonclustered indexes?
> We have empirically measured proof and documented user feedback that
> rebuilding ncixs alone usually improves the overall performance of
> otherwise well configured oltp system. Happy to show you the data next
> time you're out here too (c:
> Regards,
> Greg Linwood
> SQL Server MVP
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23B3nhY$xGHA.4524@.TK2MSFTNGP04.phx.gbl...
>> Hi Greg
>> Can you elaborate on this?
>> While I agree that nonclustered index have more of a performance support
>> role in more cases, I don't see what that has to do with rebuilding them.
>> How exactly are you finding that rebuilding helps with the performance
>> support of nonclustered indexes?
>> In particular, if the statistics are up to date, and you're using the nc
>> index to find just a few rows, why is rebuilding a necessary thing?
>> Since clustered indexes are the table storage, any scan or partial scan
>> of the data is impacted by the fragmentation of the clustered index,
>> making it imperative that the clustered index be rebuilt.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
>> news:u%239yjE$xGHA.1936@.TK2MSFTNGP06.phx.gbl...
>> Hi Ranga
>> It is usually more important for the non-clustered indexes to be rebuilt
>> than clustered indexes. The non-clustered indexes purely provide support
>> for query performance whilst clustered indexes are really the table
>> storage structure, so non-clustered indexes always play a performance
>> role whilst clustered indexes only provide performance support
>> sometimes. Given the specialised role of non-clustered indexes, it's
>> critical that they be re-built if you're doing this for performance
>> reasons. We often rebuild our non-clustered indexes many times between
>> clustered index rebuilds..
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
>> news:7E10DBD5-A7CF-47C8-BC8F-6063AE24F4A2@.microsoft.com...
>> Thanks Kalen...
>> I am using SQL 2000
>> My questions is will my non-clustered indexes if I rebuild my clustered
>> index ?
>>
>> "Kalen Delaney" wrote:
>> On SQL 2000 and 2005 these two should be the same.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
>> news:5DF73102-DD50-4635-B712-C7FDA3609F9F@.microsoft.com...
>> > Working on a vendor database upgrade. They want the clustered index
>> > to be
>> > rebuilt. I have huge table with more than 10 million
>> > records....have a
>> > clustered index and 10 non-clustered indexes...
>> >
>> > what are my options,
>> >
>> > As an example lets take Orders table and CIX_Orders is the clustered
>> > index.
>> >
>> > 1. DBCC DBReindex ('Northwind.dbo.Orders', CIX_Orders)
>> >
>> > 2.create clustered index [CIX_Orders] on
>> > [dbo].[Orders_Temp]([OrderID])
>> > with drop_existing
>> >
>> > How does the above two differ?
>> >
>> > Thanks very much
>>
>>
>>
>|||Kalen,
If the clustered index is built on a indentity column which is is always in
a sequence and ordered, will there be any need to rebuild the clustered index
?
Thanks,
Ranga
"Kalen Delaney" wrote:
> Hi Greg
> Thanks for the detailed response. These are some very interesting points to
> think about. However, you said:
> > Re> Since clustered indexes are the table storage, any scan or partial
> > scan of the data is impacted by the fragmentation.
> > Not if the ncix covers the query.
> But...if the ncix covers the query, then you don't have a scan of the data
> level.
> Which is what I was referring to.
> :-)
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:OfDqnrAyGHA.4548@.TK2MSFTNGP05.phx.gbl...
> > Hi Kalen
> >
> > Re> "and you're using the nc index to find just a few rows". This isn't a
> > good assumption b/c in most OLTPs, ncix's are range-scanned as much as
> > they're seek'd. Any range or full scan should ideally occur within a ncix
> > where page density is higher & read io is therefore more efficient than it
> > can ever be in a cix.. Any range or full scan that occurs within a cix
> > will always be less efficient other than in the obscure scenario where all
> > columns in a table are actually required by the query.
> >
> > Re> Since clustered indexes are the table storage, any scan or partial
> > scan of the data is impacted by the fragmentation.
> > Not if the ncix covers the query. In this case, the fragmentation of the
> > ncix is all that matters & fragmentation in the cix is immaterial.
> > Ideally, all performance critical queries should be covered by ncixs, so
> > this is fairly important.
> >
> > Re>How exactly are you finding that rebuilding helps with the performance
> > support of nonclustered indexes?
> > We have empirically measured proof and documented user feedback that
> > rebuilding ncixs alone usually improves the overall performance of
> > otherwise well configured oltp system. Happy to show you the data next
> > time you're out here too (c:
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > news:%23B3nhY$xGHA.4524@.TK2MSFTNGP04.phx.gbl...
> >> Hi Greg
> >>
> >> Can you elaborate on this?
> >>
> >> While I agree that nonclustered index have more of a performance support
> >> role in more cases, I don't see what that has to do with rebuilding them.
> >>
> >> How exactly are you finding that rebuilding helps with the performance
> >> support of nonclustered indexes?
> >>
> >> In particular, if the statistics are up to date, and you're using the nc
> >> index to find just a few rows, why is rebuilding a necessary thing?
> >>
> >> Since clustered indexes are the table storage, any scan or partial scan
> >> of the data is impacted by the fragmentation of the clustered index,
> >> making it imperative that the clustered index be rebuilt.
> >>
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >>
> >>
> >> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> >> news:u%239yjE$xGHA.1936@.TK2MSFTNGP06.phx.gbl...
> >> Hi Ranga
> >>
> >> It is usually more important for the non-clustered indexes to be rebuilt
> >> than clustered indexes. The non-clustered indexes purely provide support
> >> for query performance whilst clustered indexes are really the table
> >> storage structure, so non-clustered indexes always play a performance
> >> role whilst clustered indexes only provide performance support
> >> sometimes. Given the specialised role of non-clustered indexes, it's
> >> critical that they be re-built if you're doing this for performance
> >> reasons. We often rebuild our non-clustered indexes many times between
> >> clustered index rebuilds..
> >>
> >> Regards,
> >> Greg Linwood
> >> SQL Server MVP
> >>
> >> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
> >> news:7E10DBD5-A7CF-47C8-BC8F-6063AE24F4A2@.microsoft.com...
> >> Thanks Kalen...
> >> I am using SQL 2000
> >> My questions is will my non-clustered indexes if I rebuild my clustered
> >> index ?
> >>
> >>
> >>
> >> "Kalen Delaney" wrote:
> >>
> >> On SQL 2000 and 2005 these two should be the same.
> >>
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >>
> >>
> >> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
> >> news:5DF73102-DD50-4635-B712-C7FDA3609F9F@.microsoft.com...
> >> > Working on a vendor database upgrade. They want the clustered index
> >> > to be
> >> > rebuilt. I have huge table with more than 10 million
> >> > records....have a
> >> > clustered index and 10 non-clustered indexes...
> >> >
> >> > what are my options,
> >> >
> >> > As an example lets take Orders table and CIX_Orders is the clustered
> >> > index.
> >> >
> >> > 1. DBCC DBReindex ('Northwind.dbo.Orders', CIX_Orders)
> >> >
> >> > 2.create clustered index [CIX_Orders] on
> >> > [dbo].[Orders_Temp]([OrderID])
> >> > with drop_existing
> >> >
> >> > How does the above two differ?
> >> >
> >> > Thanks very much
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
>
>|||Are you ever updating any of the rows?
--
HTH
Kalen Delaney, SQL Server MVP
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:A8FA0118-2C2A-439F-8B68-2EC595253BC9@.microsoft.com...
> Kalen,
> If the clustered index is built on a indentity column which is is always
> in
> a sequence and ordered, will there be any need to rebuild the clustered
> index
> ?
> Thanks,
> Ranga
> "Kalen Delaney" wrote:
>> Hi Greg
>> Thanks for the detailed response. These are some very interesting points
>> to
>> think about. However, you said:
>> > Re> Since clustered indexes are the table storage, any scan or partial
>> > scan of the data is impacted by the fragmentation.
>> > Not if the ncix covers the query.
>> But...if the ncix covers the query, then you don't have a scan of the
>> data
>> level.
>> Which is what I was referring to.
>> :-)
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
>> news:OfDqnrAyGHA.4548@.TK2MSFTNGP05.phx.gbl...
>> > Hi Kalen
>> >
>> > Re> "and you're using the nc index to find just a few rows". This isn't
>> > a
>> > good assumption b/c in most OLTPs, ncix's are range-scanned as much as
>> > they're seek'd. Any range or full scan should ideally occur within a
>> > ncix
>> > where page density is higher & read io is therefore more efficient than
>> > it
>> > can ever be in a cix.. Any range or full scan that occurs within a cix
>> > will always be less efficient other than in the obscure scenario where
>> > all
>> > columns in a table are actually required by the query.
>> >
>> > Re> Since clustered indexes are the table storage, any scan or partial
>> > scan of the data is impacted by the fragmentation.
>> > Not if the ncix covers the query. In this case, the fragmentation of
>> > the
>> > ncix is all that matters & fragmentation in the cix is immaterial.
>> > Ideally, all performance critical queries should be covered by ncixs,
>> > so
>> > this is fairly important.
>> >
>> > Re>How exactly are you finding that rebuilding helps with the
>> > performance
>> > support of nonclustered indexes?
>> > We have empirically measured proof and documented user feedback that
>> > rebuilding ncixs alone usually improves the overall performance of
>> > otherwise well configured oltp system. Happy to show you the data next
>> > time you're out here too (c:
>> >
>> > Regards,
>> > Greg Linwood
>> > SQL Server MVP
>> >
>> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> > news:%23B3nhY$xGHA.4524@.TK2MSFTNGP04.phx.gbl...
>> >> Hi Greg
>> >>
>> >> Can you elaborate on this?
>> >>
>> >> While I agree that nonclustered index have more of a performance
>> >> support
>> >> role in more cases, I don't see what that has to do with rebuilding
>> >> them.
>> >>
>> >> How exactly are you finding that rebuilding helps with the performance
>> >> support of nonclustered indexes?
>> >>
>> >> In particular, if the statistics are up to date, and you're using the
>> >> nc
>> >> index to find just a few rows, why is rebuilding a necessary thing?
>> >>
>> >> Since clustered indexes are the table storage, any scan or partial
>> >> scan
>> >> of the data is impacted by the fragmentation of the clustered index,
>> >> making it imperative that the clustered index be rebuilt.
>> >>
>> >> --
>> >> HTH
>> >> Kalen Delaney, SQL Server MVP
>> >>
>> >>
>> >> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
>> >> news:u%239yjE$xGHA.1936@.TK2MSFTNGP06.phx.gbl...
>> >> Hi Ranga
>> >>
>> >> It is usually more important for the non-clustered indexes to be
>> >> rebuilt
>> >> than clustered indexes. The non-clustered indexes purely provide
>> >> support
>> >> for query performance whilst clustered indexes are really the table
>> >> storage structure, so non-clustered indexes always play a performance
>> >> role whilst clustered indexes only provide performance support
>> >> sometimes. Given the specialised role of non-clustered indexes, it's
>> >> critical that they be re-built if you're doing this for performance
>> >> reasons. We often rebuild our non-clustered indexes many times
>> >> between
>> >> clustered index rebuilds..
>> >>
>> >> Regards,
>> >> Greg Linwood
>> >> SQL Server MVP
>> >>
>> >> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
>> >> news:7E10DBD5-A7CF-47C8-BC8F-6063AE24F4A2@.microsoft.com...
>> >> Thanks Kalen...
>> >> I am using SQL 2000
>> >> My questions is will my non-clustered indexes if I rebuild my
>> >> clustered
>> >> index ?
>> >>
>> >>
>> >>
>> >> "Kalen Delaney" wrote:
>> >>
>> >> On SQL 2000 and 2005 these two should be the same.
>> >>
>> >> --
>> >> HTH
>> >> Kalen Delaney, SQL Server MVP
>> >>
>> >>
>> >> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
>> >> news:5DF73102-DD50-4635-B712-C7FDA3609F9F@.microsoft.com...
>> >> > Working on a vendor database upgrade. They want the clustered
>> >> > index
>> >> > to be
>> >> > rebuilt. I have huge table with more than 10 million
>> >> > records....have a
>> >> > clustered index and 10 non-clustered indexes...
>> >> >
>> >> > what are my options,
>> >> >
>> >> > As an example lets take Orders table and CIX_Orders is the
>> >> > clustered
>> >> > index.
>> >> >
>> >> > 1. DBCC DBReindex ('Northwind.dbo.Orders', CIX_Orders)
>> >> >
>> >> > 2.create clustered index [CIX_Orders] on
>> >> > [dbo].[Orders_Temp]([OrderID])
>> >> > with drop_existing
>> >> >
>> >> > How does the above two differ?
>> >> >
>> >> > Thanks very much
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>|||Yes...
"Kalen Delaney" wrote:
> Are you ever updating any of the rows?
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
> news:A8FA0118-2C2A-439F-8B68-2EC595253BC9@.microsoft.com...
> > Kalen,
> > If the clustered index is built on a indentity column which is is always
> > in
> > a sequence and ordered, will there be any need to rebuild the clustered
> > index
> > ?
> >
> > Thanks,
> > Ranga
> >
> > "Kalen Delaney" wrote:
> >
> >> Hi Greg
> >>
> >> Thanks for the detailed response. These are some very interesting points
> >> to
> >> think about. However, you said:
> >>
> >> > Re> Since clustered indexes are the table storage, any scan or partial
> >> > scan of the data is impacted by the fragmentation.
> >> > Not if the ncix covers the query.
> >>
> >> But...if the ncix covers the query, then you don't have a scan of the
> >> data
> >> level.
> >> Which is what I was referring to.
> >> :-)
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >>
> >>
> >> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> >> news:OfDqnrAyGHA.4548@.TK2MSFTNGP05.phx.gbl...
> >> > Hi Kalen
> >> >
> >> > Re> "and you're using the nc index to find just a few rows". This isn't
> >> > a
> >> > good assumption b/c in most OLTPs, ncix's are range-scanned as much as
> >> > they're seek'd. Any range or full scan should ideally occur within a
> >> > ncix
> >> > where page density is higher & read io is therefore more efficient than
> >> > it
> >> > can ever be in a cix.. Any range or full scan that occurs within a cix
> >> > will always be less efficient other than in the obscure scenario where
> >> > all
> >> > columns in a table are actually required by the query.
> >> >
> >> > Re> Since clustered indexes are the table storage, any scan or partial
> >> > scan of the data is impacted by the fragmentation.
> >> > Not if the ncix covers the query. In this case, the fragmentation of
> >> > the
> >> > ncix is all that matters & fragmentation in the cix is immaterial.
> >> > Ideally, all performance critical queries should be covered by ncixs,
> >> > so
> >> > this is fairly important.
> >> >
> >> > Re>How exactly are you finding that rebuilding helps with the
> >> > performance
> >> > support of nonclustered indexes?
> >> > We have empirically measured proof and documented user feedback that
> >> > rebuilding ncixs alone usually improves the overall performance of
> >> > otherwise well configured oltp system. Happy to show you the data next
> >> > time you're out here too (c:
> >> >
> >> > Regards,
> >> > Greg Linwood
> >> > SQL Server MVP
> >> >
> >> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> >> > news:%23B3nhY$xGHA.4524@.TK2MSFTNGP04.phx.gbl...
> >> >> Hi Greg
> >> >>
> >> >> Can you elaborate on this?
> >> >>
> >> >> While I agree that nonclustered index have more of a performance
> >> >> support
> >> >> role in more cases, I don't see what that has to do with rebuilding
> >> >> them.
> >> >>
> >> >> How exactly are you finding that rebuilding helps with the performance
> >> >> support of nonclustered indexes?
> >> >>
> >> >> In particular, if the statistics are up to date, and you're using the
> >> >> nc
> >> >> index to find just a few rows, why is rebuilding a necessary thing?
> >> >>
> >> >> Since clustered indexes are the table storage, any scan or partial
> >> >> scan
> >> >> of the data is impacted by the fragmentation of the clustered index,
> >> >> making it imperative that the clustered index be rebuilt.
> >> >>
> >> >> --
> >> >> HTH
> >> >> Kalen Delaney, SQL Server MVP
> >> >>
> >> >>
> >> >> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> >> >> news:u%239yjE$xGHA.1936@.TK2MSFTNGP06.phx.gbl...
> >> >> Hi Ranga
> >> >>
> >> >> It is usually more important for the non-clustered indexes to be
> >> >> rebuilt
> >> >> than clustered indexes. The non-clustered indexes purely provide
> >> >> support
> >> >> for query performance whilst clustered indexes are really the table
> >> >> storage structure, so non-clustered indexes always play a performance
> >> >> role whilst clustered indexes only provide performance support
> >> >> sometimes. Given the specialised role of non-clustered indexes, it's
> >> >> critical that they be re-built if you're doing this for performance
> >> >> reasons. We often rebuild our non-clustered indexes many times
> >> >> between
> >> >> clustered index rebuilds..
> >> >>
> >> >> Regards,
> >> >> Greg Linwood
> >> >> SQL Server MVP
> >> >>
> >> >> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
> >> >> news:7E10DBD5-A7CF-47C8-BC8F-6063AE24F4A2@.microsoft.com...
> >> >> Thanks Kalen...
> >> >> I am using SQL 2000
> >> >> My questions is will my non-clustered indexes if I rebuild my
> >> >> clustered
> >> >> index ?
> >> >>
> >> >>
> >> >>
> >> >> "Kalen Delaney" wrote:
> >> >>
> >> >> On SQL 2000 and 2005 these two should be the same.
> >> >>
> >> >> --
> >> >> HTH
> >> >> Kalen Delaney, SQL Server MVP
> >> >>
> >> >>
> >> >> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
> >> >> news:5DF73102-DD50-4635-B712-C7FDA3609F9F@.microsoft.com...
> >> >> > Working on a vendor database upgrade. They want the clustered
> >> >> > index
> >> >> > to be
> >> >> > rebuilt. I have huge table with more than 10 million
> >> >> > records....have a
> >> >> > clustered index and 10 non-clustered indexes...
> >> >> >
> >> >> > what are my options,
> >> >> >
> >> >> > As an example lets take Orders table and CIX_Orders is the
> >> >> > clustered
> >> >> > index.
> >> >> >
> >> >> > 1. DBCC DBReindex ('Northwind.dbo.Orders', CIX_Orders)
> >> >> >
> >> >> > 2.create clustered index [CIX_Orders] on
> >> >> > [dbo].[Orders_Temp]([OrderID])
> >> >> > with drop_existing
> >> >> >
> >> >> > How does the above two differ?
> >> >> >
> >> >> > Thanks very much
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >>
>
>|||Updates sometimes requires that rows have to split, if you change the
clustered key, or if the row becomes larger, and page splitting causes
fragmentation. So you might need to rebuild your clustered index.
--
HTH
Kalen Delaney, SQL Server MVP
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:E547F7AF-D4F5-4F3D-A8D1-634B8FE65513@.microsoft.com...
> Yes...
> "Kalen Delaney" wrote:
>> Are you ever updating any of the rows?
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
>> news:A8FA0118-2C2A-439F-8B68-2EC595253BC9@.microsoft.com...
>> > Kalen,
>> > If the clustered index is built on a indentity column which is is
>> > always
>> > in
>> > a sequence and ordered, will there be any need to rebuild the clustered
>> > index
>> > ?
>> >
>> > Thanks,
>> > Ranga
>> >
>> > "Kalen Delaney" wrote:
>> >
>> >> Hi Greg
>> >>
>> >> Thanks for the detailed response. These are some very interesting
>> >> points
>> >> to
>> >> think about. However, you said:
>> >>
>> >> > Re> Since clustered indexes are the table storage, any scan or
>> >> > partial
>> >> > scan of the data is impacted by the fragmentation.
>> >> > Not if the ncix covers the query.
>> >>
>> >> But...if the ncix covers the query, then you don't have a scan of the
>> >> data
>> >> level.
>> >> Which is what I was referring to.
>> >> :-)
>> >> --
>> >> HTH
>> >> Kalen Delaney, SQL Server MVP
>> >>
>> >>
>> >> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
>> >> news:OfDqnrAyGHA.4548@.TK2MSFTNGP05.phx.gbl...
>> >> > Hi Kalen
>> >> >
>> >> > Re> "and you're using the nc index to find just a few rows". This
>> >> > isn't
>> >> > a
>> >> > good assumption b/c in most OLTPs, ncix's are range-scanned as much
>> >> > as
>> >> > they're seek'd. Any range or full scan should ideally occur within a
>> >> > ncix
>> >> > where page density is higher & read io is therefore more efficient
>> >> > than
>> >> > it
>> >> > can ever be in a cix.. Any range or full scan that occurs within a
>> >> > cix
>> >> > will always be less efficient other than in the obscure scenario
>> >> > where
>> >> > all
>> >> > columns in a table are actually required by the query.
>> >> >
>> >> > Re> Since clustered indexes are the table storage, any scan or
>> >> > partial
>> >> > scan of the data is impacted by the fragmentation.
>> >> > Not if the ncix covers the query. In this case, the fragmentation of
>> >> > the
>> >> > ncix is all that matters & fragmentation in the cix is immaterial.
>> >> > Ideally, all performance critical queries should be covered by
>> >> > ncixs,
>> >> > so
>> >> > this is fairly important.
>> >> >
>> >> > Re>How exactly are you finding that rebuilding helps with the
>> >> > performance
>> >> > support of nonclustered indexes?
>> >> > We have empirically measured proof and documented user feedback that
>> >> > rebuilding ncixs alone usually improves the overall performance of
>> >> > otherwise well configured oltp system. Happy to show you the data
>> >> > next
>> >> > time you're out here too (c:
>> >> >
>> >> > Regards,
>> >> > Greg Linwood
>> >> > SQL Server MVP
>> >> >
>> >> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> >> > news:%23B3nhY$xGHA.4524@.TK2MSFTNGP04.phx.gbl...
>> >> >> Hi Greg
>> >> >>
>> >> >> Can you elaborate on this?
>> >> >>
>> >> >> While I agree that nonclustered index have more of a performance
>> >> >> support
>> >> >> role in more cases, I don't see what that has to do with rebuilding
>> >> >> them.
>> >> >>
>> >> >> How exactly are you finding that rebuilding helps with the
>> >> >> performance
>> >> >> support of nonclustered indexes?
>> >> >>
>> >> >> In particular, if the statistics are up to date, and you're using
>> >> >> the
>> >> >> nc
>> >> >> index to find just a few rows, why is rebuilding a necessary thing?
>> >> >>
>> >> >> Since clustered indexes are the table storage, any scan or partial
>> >> >> scan
>> >> >> of the data is impacted by the fragmentation of the clustered
>> >> >> index,
>> >> >> making it imperative that the clustered index be rebuilt.
>> >> >>
>> >> >> --
>> >> >> HTH
>> >> >> Kalen Delaney, SQL Server MVP
>> >> >>
>> >> >>
>> >> >> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
>> >> >> news:u%239yjE$xGHA.1936@.TK2MSFTNGP06.phx.gbl...
>> >> >> Hi Ranga
>> >> >>
>> >> >> It is usually more important for the non-clustered indexes to be
>> >> >> rebuilt
>> >> >> than clustered indexes. The non-clustered indexes purely provide
>> >> >> support
>> >> >> for query performance whilst clustered indexes are really the
>> >> >> table
>> >> >> storage structure, so non-clustered indexes always play a
>> >> >> performance
>> >> >> role whilst clustered indexes only provide performance support
>> >> >> sometimes. Given the specialised role of non-clustered indexes,
>> >> >> it's
>> >> >> critical that they be re-built if you're doing this for
>> >> >> performance
>> >> >> reasons. We often rebuild our non-clustered indexes many times
>> >> >> between
>> >> >> clustered index rebuilds..
>> >> >>
>> >> >> Regards,
>> >> >> Greg Linwood
>> >> >> SQL Server MVP
>> >> >>
>> >> >> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
>> >> >> news:7E10DBD5-A7CF-47C8-BC8F-6063AE24F4A2@.microsoft.com...
>> >> >> Thanks Kalen...
>> >> >> I am using SQL 2000
>> >> >> My questions is will my non-clustered indexes if I rebuild my
>> >> >> clustered
>> >> >> index ?
>> >> >>
>> >> >>
>> >> >>
>> >> >> "Kalen Delaney" wrote:
>> >> >>
>> >> >> On SQL 2000 and 2005 these two should be the same.
>> >> >>
>> >> >> --
>> >> >> HTH
>> >> >> Kalen Delaney, SQL Server MVP
>> >> >>
>> >> >>
>> >> >> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
>> >> >> news:5DF73102-DD50-4635-B712-C7FDA3609F9F@.microsoft.com...
>> >> >> > Working on a vendor database upgrade. They want the clustered
>> >> >> > index
>> >> >> > to be
>> >> >> > rebuilt. I have huge table with more than 10 million
>> >> >> > records....have a
>> >> >> > clustered index and 10 non-clustered indexes...
>> >> >> >
>> >> >> > what are my options,
>> >> >> >
>> >> >> > As an example lets take Orders table and CIX_Orders is the
>> >> >> > clustered
>> >> >> > index.
>> >> >> >
>> >> >> > 1. DBCC DBReindex ('Northwind.dbo.Orders', CIX_Orders)
>> >> >> >
>> >> >> > 2.create clustered index [CIX_Orders] on
>> >> >> > [dbo].[Orders_Temp]([OrderID])
>> >> >> > with drop_existing
>> >> >> >
>> >> >> > How does the above two differ?
>> >> >> >
>> >> >> > Thanks very much
>> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>|||I'm really trying to point out to Ranga that he's on the wrong path in
trying to avoid rebuilding his non-clustered indexes whilst rebuilding his
clustered indexes. This is a very common trap for inexperienced DBAs or
software vendors (as appears to be the case this time) who don't work with
indexes a lot - they often miss the point that performace is mainly governed
by non-clustered indexes, not clustered indexes & that there's usually far
less to be gained from rebuilding CIXs than NCIXs. You'll usually get far
more performance improvement from rebuilding your NCIXs than your CIXs -
there are very good reasons for rebuildinig NCIXs without rebuilding CIXs,
but usually not the other way around..
Regards,
Greg Linwood
SQL Server MVP
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:u05NTXGyGHA.3440@.TK2MSFTNGP06.phx.gbl...
> Hi Greg
> Thanks for the detailed response. These are some very interesting points
> to think about. However, you said:
>> Re> Since clustered indexes are the table storage, any scan or partial
>> scan of the data is impacted by the fragmentation.
>> Not if the ncix covers the query.
> But...if the ncix covers the query, then you don't have a scan of the data
> level.
> Which is what I was referring to.
> :-)
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:OfDqnrAyGHA.4548@.TK2MSFTNGP05.phx.gbl...
>> Hi Kalen
>> Re> "and you're using the nc index to find just a few rows". This isn't a
>> good assumption b/c in most OLTPs, ncix's are range-scanned as much as
>> they're seek'd. Any range or full scan should ideally occur within a ncix
>> where page density is higher & read io is therefore more efficient than
>> it can ever be in a cix.. Any range or full scan that occurs within a cix
>> will always be less efficient other than in the obscure scenario where
>> all columns in a table are actually required by the query.
>> Re> Since clustered indexes are the table storage, any scan or partial
>> scan of the data is impacted by the fragmentation.
>> Not if the ncix covers the query. In this case, the fragmentation of the
>> ncix is all that matters & fragmentation in the cix is immaterial.
>> Ideally, all performance critical queries should be covered by ncixs, so
>> this is fairly important.
>> Re>How exactly are you finding that rebuilding helps with the performance
>> support of nonclustered indexes?
>> We have empirically measured proof and documented user feedback that
>> rebuilding ncixs alone usually improves the overall performance of
>> otherwise well configured oltp system. Happy to show you the data next
>> time you're out here too (c:
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:%23B3nhY$xGHA.4524@.TK2MSFTNGP04.phx.gbl...
>> Hi Greg
>> Can you elaborate on this?
>> While I agree that nonclustered index have more of a performance support
>> role in more cases, I don't see what that has to do with rebuilding
>> them.
>> How exactly are you finding that rebuilding helps with the performance
>> support of nonclustered indexes?
>> In particular, if the statistics are up to date, and you're using the nc
>> index to find just a few rows, why is rebuilding a necessary thing?
>> Since clustered indexes are the table storage, any scan or partial scan
>> of the data is impacted by the fragmentation of the clustered index,
>> making it imperative that the clustered index be rebuilt.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
>> news:u%239yjE$xGHA.1936@.TK2MSFTNGP06.phx.gbl...
>> Hi Ranga
>> It is usually more important for the non-clustered indexes to be
>> rebuilt than clustered indexes. The non-clustered indexes purely
>> provide support for query performance whilst clustered indexes are
>> really the table storage structure, so non-clustered indexes always
>> play a performance role whilst clustered indexes only provide
>> performance support sometimes. Given the specialised role of
>> non-clustered indexes, it's critical that they be re-built if you're
>> doing this for performance reasons. We often rebuild our non-clustered
>> indexes many times between clustered index rebuilds..
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
>> news:7E10DBD5-A7CF-47C8-BC8F-6063AE24F4A2@.microsoft.com...
>> Thanks Kalen...
>> I am using SQL 2000
>> My questions is will my non-clustered indexes if I rebuild my
>> clustered
>> index ?
>>
>> "Kalen Delaney" wrote:
>> On SQL 2000 and 2005 these two should be the same.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
>> news:5DF73102-DD50-4635-B712-C7FDA3609F9F@.microsoft.com...
>> > Working on a vendor database upgrade. They want the clustered index
>> > to be
>> > rebuilt. I have huge table with more than 10 million
>> > records....have a
>> > clustered index and 10 non-clustered indexes...
>> >
>> > what are my options,
>> >
>> > As an example lets take Orders table and CIX_Orders is the
>> > clustered
>> > index.
>> >
>> > 1. DBCC DBReindex ('Northwind.dbo.Orders', CIX_Orders)
>> >
>> > 2.create clustered index [CIX_Orders] on
>> > [dbo].[Orders_Temp]([OrderID])
>> > with drop_existing
>> >
>> > How does the above two differ?
>> >
>> > Thanks very much
>>
>>
>>
>>
>