Real Time DSS: records

Showing posts with label records. Show all posts

Wednesday, March 28, 2012

Receiving and sending a cursor with(in) a Stored Procedure

Can someone post some code that shows a Stored Procedure receiving a cursor that it can process - lets say a group of order detail records are received that must be saved along with the single Order header record.

And, in another example, a SP returns a result set to the calling program. - For example, a particular sale receipt is pulled up on the screen and the order detail is needed.

Thanks for help on this,

PeterCursors are a really poor choice in MS-SQL. It would appear that you are trying to use Oracle-like logic in SQL Server, which is a receipe for disaster.

Can you explain in a bit more detail what you are planning to do? I suspect that there is a much better way to do the job once we understand what you are trying to accomplish.

-PatP|||Hi Pat:

Maybe I should have said "result set" or "table" - I'm fairly new with SS.

Here's more detail:

A web app has a screen where the user enters order header information - name, address, etc. - this would be the tblOrder - 1 record. Then they enter the order detail - say 6 individual sales items - tblDetail - 6 records. All these entries are done on 1 screen in the web app.

Upon save, a stored procedure is called that will do 2 things.

1. It will save the tblOrder record and generate the PK for this record
2. It will save the 6 records into tblDetail including stuffing the PK from the tblOrder into a FK field within the tblDetail records.

1 and 2 would probably be wrapped in a transaction in case either fails it could Rollback. Otherwise, Commit.

The approach within the SP is basically what I'm after. Does that make sense?

Thanks,

Peter|||Write one stored procedure that saves your order record and returns the generated key value to your interface.

Write a second stored procedure that that save the detail records, including the order key returned by the first procedure.

Your interface should save the order, and then loop through the detail records calling an insert for each one.|||Blindman:

I was hoping for some code to show the passing of the detail record set from the view tier to the SP.

Secondly - I'd rather fail both the Order record creation and Part records creation if either fails.

Wouldn't it be better to have both processes in the same stored procedure? Otherwise, end up with an order and no detail?

Is there anything preventing me from doing both in 1 stored procedure? If not, how do I pass the needed data - that's the key to my question.

Thanks for helping,

Peter|||The detail recored set is just passed to the stored procedure as a set of parameters defined in the procedure's heading.|||Hi - a coded example of the stored procedure would be most helpful to this newbie. I understand that detailed record set is passed as a parameter. My original question would be to see the code of the Stored Procedure handling the receipt and processing of the detailed record set.

Thanks,

Peter|||Create Procedure DetailInsert(@.OrderID as int, @.DetailInfo as varchar(50))
as
begin
insert into DetailTable (OrderID, DetailInfo) values (@.OrderID, @.DetailInfo)
end|||So detailInfo is a record set? It looks like a single field. If it is a record set, are the values comma delimited or is it simply a reference to a record set that was established in the calling program?

This is where I'm confused. I can handle a SP that inserts a single record into a DB. I'm trying to understand a situation where multiple records are submitted at once.

Thanks,

peter|||I have no idea what @.DetailInfo is. That is up to you. It is a dummy parameter that represents all the values you need to submit to the procedure. Submit each value as a separate parameter.

Peter, have you even TRIED to look up how to write Stored Procedure in Books Online?|||Blindman:

I have looked up many things with books online. I thought this might be a place to zero in efficiently for a solution. As someone new, I'm struggling. I apologize that I didn't understand the purpose of this board and to have taken valuable time away from you.

I have numerous stored procedures that work to insert and update single records. I have some stored procedures to query and return a record set. What I haven't had success in is having a stored procedure receive a group of child records along with parent and save both. If either fails, I want to rollback - that's why I thought there would be a benefit to combining the operations into a single SP transaction.|||The answer here is easy.

This is not Oracle

You can not do what you are thinking unless you use bcp or bulk insert.

In either case that means you'd have to create a file, which I don't think is a good idea.

You need to iterate through your rs and make a stored procedure call for each record set.

Now you could put all of the data in to 1 string, pass it to a sproc, the "unstring" it in the sproc...but that would be overkill.

Use 2 sprocs like the blind dude said.

Pass the id as an output variable from the first sproc.

In the second sproc use error checking. If anything fails, perform a delete of the initial record.

Sorry|||You can't set up a stored procedure that will recieve a group of records. They can only accept input parameters.

I recommend that you either change your application design to one that submits new data one record at a time, or look into other options available through your development interface for handling recordset. I'm not much of an interface programmer, so I can't help you with that, but you might check one of the other sections of DB Forums.

Sorry if I was short with you. Been sick today. I readily answer question such as "How do I write a stored procedure to do X", but I don't answer questions like "How do I write a stored procedure."

If you have specific questions, please do post them on this forum and I or somebody else will assist you.|||Thank you Brett and Blindman. I now understand that the best way to handle is from the interface, not at SQL server.

I hope you are feeling better Blindman.

thanks,

Peter|||Sick?

Don't you listen to anything I say?

A bottle of tequila will take care of all those germs|||Inadequate solution.

A bottle of tequila is too large to physically smash the germs, and too small to submerge myself in.

Can you suggest something more scalable?|||Inadequate solution.

A bottle of tequila is too large to physically smash the germs, and too small to submerge myself in.

Can you suggest something more scalable?Have you ever tried using a bottle of Tequila to smash the germs? I'd like the video rights, and would make you a very handsome deal.

Scalable? How about many cases of Tequila?

-PatP

Friday, March 23, 2012

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
>>
>>
>>
>>
>

Saturday, February 25, 2012

Really interesting issue!

Hi All,

When I wanted to see ONLY today’s records on the sql server, it took 5 minutes.

When I wanted to see records including today’s records on the sql server, no problem. (19 Sep. is a past date which is working properly)

It is very interesting it takes 5 minutes to sort the lastday there is nothing wrong with the other days.

I ran DBCC INDEXDEFRAG method but no change anything.

I am wondering about something, everyday at 22:00 pm we are getting backup. Is this process creating a difference between before getting the back up and after got the backups?

It is really interesting issue. I do not know what I can say. It seems there is an issue with today’s records. May be there are some locks on the today’s records. I do not know. Why does not cause any issue for old dates but for current day ?

SELECT DISTINCT CallIDChar, ANI, DNIS, CallerName, AgentName, CallQueue, AgentID, CallType, CallStartTimeStamp, CallEndTimeStamp, CallDuration

FROM (SELECT MAINTBL.*, DRTTABLE.CallDuration AS CallDuration

FROM CallInfo AS MAINTBL
JOIN ( SELECT CallID, DateDiff(second, min(CallStartTimeStamp), max(CallEndTimeStamp)) as CallDuration

FROM CallInfo
GROUP BY CallID
) AS DRTTABLE ON MAINTBL.CallID=DRTTABLE.CallID

) as CallInfo

WHERE CallIDChar = CallIDChar

AND (CallStartTimeStamp >= '2007-09-20T00:00:00')

AND (CallStartTimeStamp <= '2007-09-20T23:59:59')

What can be reason for this issue and how can I solve it ?

Thank you in advance,

Melih

Can you give us the execution plans for these queries? Also, are you sure that that there is an index on CallStartTimeStamp?

|||

ShawnNWF wrote:

Can you give us the execution plans for these queries? Also, are you sure that that there is an index on CallStartTimeStamp?

For not reason another, No execution plan or anything on the db. But, indexes which is working following:

USE [VestelUpgMedia]
GO
/****** Object: Index [PK_CallInfo_RowID] Script Date: 09/20/2007 16:26:42 ******/
ALTER TABLE [dbo].[CallInfo] ADD CONSTRAINT [PK_CallInfo_RowID] PRIMARY KEY CLUSTERED
(
[RowID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

USE [VestelUpgMedia]
GO
/****** Object: Index [PK_ImageInfo_RowID] Script Date: 09/20/2007 16:28:09 ******/
ALTER TABLE [dbo].[ImageInfo] ADD CONSTRAINT [PK_ImageInfo_RowID] PRIMARY KEY CLUSTERED
(
[RowID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

|||

These indexes are only on RowID, but you don't reference RowID anywhere in your query, so these aren't of any use in this case.

/Kenneth

|||

Why are you using this expression in the "where" clause?

> WHERE CallIDChar = CallIDChar

What about having an index by [CallID]?

AMB|||

These indexes are not being used in this query. How many rows are we talking about for this table in total? I am assuming that this is not a small ammoun of data for the query to take 5 mintues.

|||

hunchback wrote:

Why are you using this expression in the "where" clause?
> WHERE CallIDChar = CallIDChar
What about having an index by [CallID]?

AMB

When i run the script below TSQL server turns query form.

Part of code:

Set objImageInfoRS = Server.CreateObject("adodb.recordset")
'Set to use client-side cursor
objImageInfoRS.CursorLocation = 3 'adUseClient
'Set objConn = Server.CreateObject("ADODB.Connection")
'objConn.Open strConnect
strError = openDB(objConn, strConnect)
strSQL = "select distinct CallIDChar, ANI, DNIS, CallerName, AgentName, CallQueue, AgentID, CallType, CallStartTimeStamp, CallEndTimeStamp, CallDuration"
strSQL = strSQL & " from (" & _
"SELECT MAINTBL.*, DRTTABLE.CallDuration AS CallDuration " & _
"FROM #TABLENAME# AS MAINTBL " & _
"JOIN (SELECT CallID, " & _
"DateDiff(second, min(CallStartTimeStamp), max(CallEndTimeStamp)) as CallDuration " & _
"FROM #TABLENAME# " & _
"GROUP BY CallID " & _
") AS DRTTABLE ON MAINTBL.CallID=DRTTABLE.CallID " & _
") as CallInfo where CallIDChar = CallIDChar "
If boolAgent Then
strSQL = strSQL & "AND (AgentID='" & strAgentID & "') "
End If
If bAni Then
strSQL = strSQL & "AND (ANI like '" & strAni & "') "
End If
If bDnis Then
strSQL = strSQL & "AND (DNIS like '" & strDnis & "') "
End If
If boolCallType Then
If strCallType = strScriptCallType Then
strSQL = strSQL & "AND (AgentID = '" & "' AND AgentName = '" & "') "
Else
strSQL = strSQL & "AND (CallType = '" & strCallType & "') "
End If
End If
If bCaller Then
strSQL = strSQL & "AND (CallerName like '" & strCaller & "') "
End If

Dim strFrom, strTo, strTmpDateTill
strDateFrom = Session("formElements")("fromYear") & "-" & Session("formElements")("fromMonth") & "-" & Session("formElements")("fromDay")
strDateTill = Session("formElements")("toYear") & "-" & Session("formElements")("toMonth") & "-" & Session("formElements")("toDay")

strTmpDateTill = mid(strDateTill, 1, 11) & " 23:59:59"
strSQL = strSQL & "AND (CallStartTimeStamp >= '" & getStandardDateTime(((Session("ClientTimeBias") * 60) / 86400) + CDate(strDateFrom)) & "') "
strSQL = strSQL & "AND (CallStartTimeStamp <= '" & getStandardDateTime(((Session("ClientTimeBias") * 60) / 86400) + CDate(strTmpDateTill)) & "') "

strSQL = buildLinkedSQL(strSQL, "CallInfo")

'Get call information from CosmoCorder database and eliminate the second segment if the
'call is being monitored so the second segment won't be displayed in the Call Summary.
'Keep a record of each call being monitored and pass it to the Call Detail page so the
'second segment of the call being monitored can also be view.

Dim colMonitorCall
Set colMonitorCall = Server.CreateObject("Scripting.Dictionary")
colMonitorCall.CompareMode = 1 'Case-insensitive comparisons
objImageInfoRS.Open strSQL, objConn, adOpenStatic, adLockReadOnly

|||

ShawnNWF wrote:

These indexes are not being used in this query. How many rows are we talking about for this table in total? I am assuming that this is not a small ammoun of data for the query to take 5 mintues.

select count(*) from CallInfo

result=150490 rows

note: this database also has the voice logs at the same time (table=ImageInfo)

|||

Melih Araz,

That is really a bad practice, are you aware of SQL Injection?

You are not only opening the doors to let others inject SQL code into your db, if no, you are bombarding the engine with queries with low probability of being able to reuse the execution plan. Try to parameterize the statement you are sending to the engine.

Here is an outstanding article that will help you with this issue.

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

AMB

|||

hunchback,

Thank you for advice, but our application is closed to internet, also users cannot access to internet.

hunchback wrote:

Melih Araz,
That is really a bad practice, are you aware of SQL Injection?
You are not only opening the doors to let others inject SQL code into your db, if no, you are bombarding the engine with queries with low probability of being able to reuse the execution plan. Try to parameterize the statement you are sending to the engine.
Here is an outstanding article that will help you with this issue.
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html

AMB

|||

Who said that SQL Injection just comes from the internet?

AMB

|||

What might be happening is this: There are not enough changes to the table in one day to cause the statistics to be updated. By default, statistics are only updated if a sizeable percentage of rows are inserted. One day in a many-months table is not enough. As as result, when the query optimizer considers the query with today's date, it refers to the statistics and estimates that there are almost zero rows in the table for today's date. Based on this incorrect rowcount estimate, the optimizer chooses a bad query plan.

The optimizer might then decide to begin the query execution by using a nonclustered, noncovering index on CallStartTimeStamp to identify which rows have today's CallStartTimeStamp value. For these rows, a lot of redundant or inefficient work might then be done. If there are only a few rows, it's no big deal.

If it turns out there are many rows for today, the query plan to finish the query may take hundreds, even millions, of times longer than expected, If the optimizer knew there were many rows for today, it would have chosen a more efficient way to handle different parts of the query, because it would be no advantage to filter for CallStartTimeStamp first.

How to fix this? Try running UPDATE STATISTICS on the table. (Your problem may be outdated statistics, not index fragmentation.) You can also try to hide the date strings from SQL Server by making them variables and setting their values before the query.

One more thing. If your data has tenths of a second in the timestamp, you are missing all calls beginning in the last 1/2 second of each day. It is always best to write ranges like this with one >= and one < as follows:

... >= '2007-09-20T00:00:00' AND

... < '2007-09-21T00:00:00'

For the second value, you can also use DATEADD(day,1,<first value>).

Steve Kass

Drew University

http://www.stevekass.com

|||

hunchback wrote:

Who said that SQL Injection just comes from the internet?

AMB

The account which is used in the application does not have writing authorization.
Database is only for to list and listen the voice records.

Issue is that it takes 5 minutes to sort the lastday there is nothing wrong with the other days. Issue is not security.

Regards,

Melih

|||

Steve Kass wrote:

What might be happening is this: There are not enough changes to the table in one day to cause the statistics to be updated. By default, statistics are only updated if a sizeable percentage of rows are inserted. One day in a many-months table is not enough. As as result, when the query optimizer considers the query with today's date, it refers to the statistics and estimates that there are almost zero rows in the table for today's date. Based on this incorrect rowcount estimate, the optimizer chooses a bad query plan.
The optimizer might then decide to begin the query execution by using a nonclustered, noncovering index on CallStartTimeStamp to identify which rows have today's CallStartTimeStamp value. For these rows, a lot of redundant or inefficient work might then be done. If there are only a few rows, it's no big deal.
If it turns out there are many rows for today, the query plan to finish the query may take hundreds, even millions, of times longer than expected, If the optimizer knew there were many rows for today, it would have chosen a more efficient way to handle different parts of the query, because it would be no advantage to filter for CallStartTimeStamp first.
How to fix this? Try running UPDATE STATISTICS on the table. (Your problem may be outdated statistics, not index fragmentation.) You can also try to hide the date strings from SQL Server by making them variables and setting their values before the query.
One more thing. If your data has tenths of a second in the timestamp, you are missing all calls beginning in the last 1/2 second of each day. It is always best to write ranges like this with one >= and one < as follows:
... >= '2007-09-20T00:00:00' AND
... < '2007-09-21T00:00:00'
For the second value, you can also use DATEADD(day,1,<first value>).
Steve Kass
Drew University
http://www.stevekass.com

Steve Kass,

Thank you for detailed and clear information.

I changed the query like below and worked!

SELECT DISTINCT CallIDChar, ANI, DNIS, CallerName, AgentName, CallQueue, AgentID, CallType, CallStartTimeStamp, CallEndTimeStamp, CallDuration

FROM (SELECT MAINTBL.*, DRTTABLE.CallDuration AS CallDuration

FROM CallInfo AS MAINTBL
JOIN ( SELECT CallID, DateDiff(second, min(CallStartTimeStamp), max(CallEndTimeStamp)) as CallDuration

FROM CallInfo

WHERE (CallStartTimeStamp >= '2007-09-20T00:00:00')

AND (CallStartTimeStamp <= '2007-09-20T23:59:59')

GROUP BY CallID
) AS DRTTABLE ON MAINTBL.CallID=DRTTABLE.CallID

) as CallInfo

By the way, how was the execution plan should be done?

Regards,

Melih

|||

Steve Kass wrote:

What might be happening is this: There are not enough changes to the table in one day to cause the statistics to be updated. By default, statistics are only updated if a sizeable percentage of rows are inserted. One day in a many-months table is not enough. As as result, when the query optimizer considers the query with today's date, it refers to the statistics and estimates that there are almost zero rows in the table for today's date. Based on this incorrect rowcount estimate, the optimizer chooses a bad query plan.
The optimizer might then decide to begin the query execution by using a nonclustered, noncovering index on CallStartTimeStamp to identify which rows have today's CallStartTimeStamp value. For these rows, a lot of redundant or inefficient work might then be done. If there are only a few rows, it's no big deal.
If it turns out there are many rows for today, the query plan to finish the query may take hundreds, even millions, of times longer than expected, If the optimizer knew there were many rows for today, it would have chosen a more efficient way to handle different parts of the query, because it would be no advantage to filter for CallStartTimeStamp first.
How to fix this? Try running UPDATE STATISTICS on the table. (Your problem may be outdated statistics, not index fragmentation.) You can also try to hide the date strings from SQL Server by making them variables and setting their values before the query.
One more thing. If your data has tenths of a second in the timestamp, you are missing all calls beginning in the last 1/2 second of each day. It is always best to write ranges like this with one >= and one < as follows:
... >= '2007-09-20T00:00:00' AND
... < '2007-09-21T00:00:00'
For the second value, you can also use DATEADD(day,1,<first value>).
Steve Kass
Drew University
http://www.stevekass.com

Hi Steve Kass,

Great! It worked withal old script. Our problem was outdated statistics.

UPDATE STATISTICS CallInfo

UPDATE STATISTICS ImageInfo

Should I run this comment daily?

Thanks&Regards,

Melih

Wednesday, March 28, 2012

Receiving and sending a cursor with(in) a Stored Procedure

Friday, March 23, 2012

Rebuilding clustered index

Rebuilding clustered index

Saturday, February 25, 2012

Really interesting issue!

Real Time DSS

Blog Archive

About Me