My Query looks like this.
select * from table
where field = 'abc'
In SQL Server 2005 I see that there is some new data retrieval mechanism in
the query analyser tool so that results are streamed in. What I mean is that
if I do a query on my large table I get first 1000 rows in about 1 seconds,
and next 1000 in the next 1s and this continues right up to about 2 mins ,
where I have about 29000 rows sitting in query analyser, but it hasn't
finished yet, it sits there churning for another 10 mins before it finishes
and the total row count is 29052 records.
I don't understand why 99% of results are returned quickly (15% of the time)
and then the last 1% of the results are returned in a huge amount of time
(85%)
In SQL 2000 doing this query has query analyser sitting there bored for
quite a while.
I have changed the query to return a smaller set of data like 15000 records
and the same thing happens where it returns 99% of records quite quickly but
the last 1% of data still takes about 10 mins to return.
I have tried creating a stored procedure to do this thinking it might work
better but no, and have tried doing a top 100 PERCENT with no success either.
It's difficult to speculate without knowing what your query and data look
like. A likely possibility is you're returning 29000 records out of a few
million and the records you selected are toward the front of the table scan
order. When you retrieve that many records the query processor likely will
be doing a table scan and unless there's something in the query to indicate
when it's done, every row in the table will be read. Looking at the query
plan will help you understand what the query processor is doing.
How are you determining how many records have been returned? Are you
actually scrolling through the data so you know the records have been
returned and rendered in the UI? Do you get different results if you
display the results as text?
I have to ask - do you really read 29000 records in Management Studio? Even
if you read a record a second that's almost an hour of reading. If you're
pulling back that many records to page through a look at a few records, you
using a lot of processing power, memory, and bandwidth on the server for no
reason.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:4F777E81-4736-4E8F-BDFF-C203CE50A6D2@.microsoft.com...
> My Query looks like this.
> select * from table
> where field = 'abc'
> In SQL Server 2005 I see that there is some new data retrieval mechanism
> in
> the query analyser tool so that results are streamed in. What I mean is
> that
> if I do a query on my large table I get first 1000 rows in about 1
> seconds,
> and next 1000 in the next 1s and this continues right up to about 2 mins ,
> where I have about 29000 rows sitting in query analyser, but it hasn't
> finished yet, it sits there churning for another 10 mins before it
> finishes
> and the total row count is 29052 records.
> I don't understand why 99% of results are returned quickly (15% of the
> time)
> and then the last 1% of the results are returned in a huge amount of time
> (85%)
> In SQL 2000 doing this query has query analyser sitting there bored for
> quite a while.
> I have changed the query to return a smaller set of data like 15000
> records
> and the same thing happens where it returns 99% of records quite quickly
> but
> the last 1% of data still takes about 10 mins to return.
> I have tried creating a stored procedure to do this thinking it might work
> better but no, and have tried doing a top 100 PERCENT with no success
> either.
|||Thanks for the reply, I agree ... I think it is doing a full table scan.
I am determining the number of records being returned by looking at the
bottom of the management studio where it tells you how many records are
returned, also when it has a record id column, which I see increments for
each new row of data is returned. Yes I am scrolling through the data.
Display the results as text? Not sure what you mean there sorry.
I am not reading the records. Just wanting to see how long it takes to
retrieve different sets of data from this table.
"Roger Wolter[MSFT]" wrote:
> It's difficult to speculate without knowing what your query and data look
> like. A likely possibility is you're returning 29000 records out of a few
> million and the records you selected are toward the front of the table scan
> order. When you retrieve that many records the query processor likely will
> be doing a table scan and unless there's something in the query to indicate
> when it's done, every row in the table will be read. Looking at the query
> plan will help you understand what the query processor is doing.
> How are you determining how many records have been returned? Are you
> actually scrolling through the data so you know the records have been
> returned and rendered in the UI? Do you get different results if you
> display the results as text?
> I have to ask - do you really read 29000 records in Management Studio? Even
> if you read a record a second that's almost an hour of reading. If you're
> pulling back that many records to page through a look at a few records, you
> using a lot of processing power, memory, and bandwidth on the server for no
> reason.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:4F777E81-4736-4E8F-BDFF-C203CE50A6D2@.microsoft.com...
>
|||If you're not actually looking at the results as they come in, you may be
seeing a UI painting delay. The results may all be retrieved and in memory
but not rendered in the grid yet. If you look at the Management Studio
options and change the result display option to text or a file instead of
grid, the results will appear significantly faster. Bottom line is that
with that many results, creating and formatting the grid cells generally
takes more time than retrieving the results.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:89E01783-910F-4549-94BB-1567701DB175@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply, I agree ... I think it is doing a full table scan.
> I am determining the number of records being returned by looking at the
> bottom of the management studio where it tells you how many records are
> returned, also when it has a record id column, which I see increments for
> each new row of data is returned. Yes I am scrolling through the data.
> Display the results as text? Not sure what you mean there sorry.
> I am not reading the records. Just wanting to see how long it takes to
> retrieve different sets of data from this table.
> "Roger Wolter[MSFT]" wrote:
Showing posts with label quickly. Show all posts
Showing posts with label quickly. Show all posts
Wednesday, March 7, 2012
Large table query 99% results streamed quickly, huge pause for las
My Query looks like this.
select * from table
where field = 'abc'
In SQL Server 2005 I see that there is some new data retrieval mechanism in
the query analyser tool so that results are streamed in. What I mean is that
if I do a query on my large table I get first 1000 rows in about 1 seconds,
and next 1000 in the next 1s and this continues right up to about 2 mins ,
where I have about 29000 rows sitting in query analyser, but it hasn't
finished yet, it sits there churning for another 10 mins before it finishes
and the total row count is 29052 records.
I don't understand why 99% of results are returned quickly (15% of the time)
and then the last 1% of the results are returned in a huge amount of time
(85%)
In SQL 2000 doing this query has query analyser sitting there bored for
quite a while.
I have changed the query to return a smaller set of data like 15000 records
and the same thing happens where it returns 99% of records quite quickly but
the last 1% of data still takes about 10 mins to return.
I have tried creating a stored procedure to do this thinking it might work
better but no, and have tried doing a top 100 PERCENT with no success either.It's difficult to speculate without knowing what your query and data look
like. A likely possibility is you're returning 29000 records out of a few
million and the records you selected are toward the front of the table scan
order. When you retrieve that many records the query processor likely will
be doing a table scan and unless there's something in the query to indicate
when it's done, every row in the table will be read. Looking at the query
plan will help you understand what the query processor is doing.
How are you determining how many records have been returned? Are you
actually scrolling through the data so you know the records have been
returned and rendered in the UI? Do you get different results if you
display the results as text?
I have to ask - do you really read 29000 records in Management Studio? Even
if you read a record a second that's almost an hour of reading. If you're
pulling back that many records to page through a look at a few records, you
using a lot of processing power, memory, and bandwidth on the server for no
reason.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:4F777E81-4736-4E8F-BDFF-C203CE50A6D2@.microsoft.com...
> My Query looks like this.
> select * from table
> where field = 'abc'
> In SQL Server 2005 I see that there is some new data retrieval mechanism
> in
> the query analyser tool so that results are streamed in. What I mean is
> that
> if I do a query on my large table I get first 1000 rows in about 1
> seconds,
> and next 1000 in the next 1s and this continues right up to about 2 mins ,
> where I have about 29000 rows sitting in query analyser, but it hasn't
> finished yet, it sits there churning for another 10 mins before it
> finishes
> and the total row count is 29052 records.
> I don't understand why 99% of results are returned quickly (15% of the
> time)
> and then the last 1% of the results are returned in a huge amount of time
> (85%)
> In SQL 2000 doing this query has query analyser sitting there bored for
> quite a while.
> I have changed the query to return a smaller set of data like 15000
> records
> and the same thing happens where it returns 99% of records quite quickly
> but
> the last 1% of data still takes about 10 mins to return.
> I have tried creating a stored procedure to do this thinking it might work
> better but no, and have tried doing a top 100 PERCENT with no success
> either.|||Thanks for the reply, I agree ... I think it is doing a full table scan.
I am determining the number of records being returned by looking at the
bottom of the management studio where it tells you how many records are
returned, also when it has a record id column, which I see increments for
each new row of data is returned. Yes I am scrolling through the data.
Display the results as text? Not sure what you mean there sorry.
I am not reading the records. Just wanting to see how long it takes to
retrieve different sets of data from this table.
"Roger Wolter[MSFT]" wrote:
> It's difficult to speculate without knowing what your query and data look
> like. A likely possibility is you're returning 29000 records out of a few
> million and the records you selected are toward the front of the table scan
> order. When you retrieve that many records the query processor likely will
> be doing a table scan and unless there's something in the query to indicate
> when it's done, every row in the table will be read. Looking at the query
> plan will help you understand what the query processor is doing.
> How are you determining how many records have been returned? Are you
> actually scrolling through the data so you know the records have been
> returned and rendered in the UI? Do you get different results if you
> display the results as text?
> I have to ask - do you really read 29000 records in Management Studio? Even
> if you read a record a second that's almost an hour of reading. If you're
> pulling back that many records to page through a look at a few records, you
> using a lot of processing power, memory, and bandwidth on the server for no
> reason.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:4F777E81-4736-4E8F-BDFF-C203CE50A6D2@.microsoft.com...
> > My Query looks like this.
> > select * from table
> > where field = 'abc'
> >
> > In SQL Server 2005 I see that there is some new data retrieval mechanism
> > in
> > the query analyser tool so that results are streamed in. What I mean is
> > that
> > if I do a query on my large table I get first 1000 rows in about 1
> > seconds,
> > and next 1000 in the next 1s and this continues right up to about 2 mins ,
> > where I have about 29000 rows sitting in query analyser, but it hasn't
> > finished yet, it sits there churning for another 10 mins before it
> > finishes
> > and the total row count is 29052 records.
> >
> > I don't understand why 99% of results are returned quickly (15% of the
> > time)
> > and then the last 1% of the results are returned in a huge amount of time
> > (85%)
> >
> > In SQL 2000 doing this query has query analyser sitting there bored for
> > quite a while.
> >
> > I have changed the query to return a smaller set of data like 15000
> > records
> > and the same thing happens where it returns 99% of records quite quickly
> > but
> > the last 1% of data still takes about 10 mins to return.
> >
> > I have tried creating a stored procedure to do this thinking it might work
> > better but no, and have tried doing a top 100 PERCENT with no success
> > either.
>|||If you're not actually looking at the results as they come in, you may be
seeing a UI painting delay. The results may all be retrieved and in memory
but not rendered in the grid yet. If you look at the Management Studio
options and change the result display option to text or a file instead of
grid, the results will appear significantly faster. Bottom line is that
with that many results, creating and formatting the grid cells generally
takes more time than retrieving the results.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:89E01783-910F-4549-94BB-1567701DB175@.microsoft.com...
> Thanks for the reply, I agree ... I think it is doing a full table scan.
> I am determining the number of records being returned by looking at the
> bottom of the management studio where it tells you how many records are
> returned, also when it has a record id column, which I see increments for
> each new row of data is returned. Yes I am scrolling through the data.
> Display the results as text? Not sure what you mean there sorry.
> I am not reading the records. Just wanting to see how long it takes to
> retrieve different sets of data from this table.
> "Roger Wolter[MSFT]" wrote:
>> It's difficult to speculate without knowing what your query and data look
>> like. A likely possibility is you're returning 29000 records out of a
>> few
>> million and the records you selected are toward the front of the table
>> scan
>> order. When you retrieve that many records the query processor likely
>> will
>> be doing a table scan and unless there's something in the query to
>> indicate
>> when it's done, every row in the table will be read. Looking at the
>> query
>> plan will help you understand what the query processor is doing.
>> How are you determining how many records have been returned? Are you
>> actually scrolling through the data so you know the records have been
>> returned and rendered in the UI? Do you get different results if you
>> display the results as text?
>> I have to ask - do you really read 29000 records in Management Studio?
>> Even
>> if you read a record a second that's almost an hour of reading. If
>> you're
>> pulling back that many records to page through a look at a few records,
>> you
>> using a lot of processing power, memory, and bandwidth on the server for
>> no
>> reason.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "Peter" <Peter@.discussions.microsoft.com> wrote in message
>> news:4F777E81-4736-4E8F-BDFF-C203CE50A6D2@.microsoft.com...
>> > My Query looks like this.
>> > select * from table
>> > where field = 'abc'
>> >
>> > In SQL Server 2005 I see that there is some new data retrieval
>> > mechanism
>> > in
>> > the query analyser tool so that results are streamed in. What I mean
>> > is
>> > that
>> > if I do a query on my large table I get first 1000 rows in about 1
>> > seconds,
>> > and next 1000 in the next 1s and this continues right up to about 2
>> > mins ,
>> > where I have about 29000 rows sitting in query analyser, but it hasn't
>> > finished yet, it sits there churning for another 10 mins before it
>> > finishes
>> > and the total row count is 29052 records.
>> >
>> > I don't understand why 99% of results are returned quickly (15% of the
>> > time)
>> > and then the last 1% of the results are returned in a huge amount of
>> > time
>> > (85%)
>> >
>> > In SQL 2000 doing this query has query analyser sitting there bored for
>> > quite a while.
>> >
>> > I have changed the query to return a smaller set of data like 15000
>> > records
>> > and the same thing happens where it returns 99% of records quite
>> > quickly
>> > but
>> > the last 1% of data still takes about 10 mins to return.
>> >
>> > I have tried creating a stored procedure to do this thinking it might
>> > work
>> > better but no, and have tried doing a top 100 PERCENT with no success
>> > either.
select * from table
where field = 'abc'
In SQL Server 2005 I see that there is some new data retrieval mechanism in
the query analyser tool so that results are streamed in. What I mean is that
if I do a query on my large table I get first 1000 rows in about 1 seconds,
and next 1000 in the next 1s and this continues right up to about 2 mins ,
where I have about 29000 rows sitting in query analyser, but it hasn't
finished yet, it sits there churning for another 10 mins before it finishes
and the total row count is 29052 records.
I don't understand why 99% of results are returned quickly (15% of the time)
and then the last 1% of the results are returned in a huge amount of time
(85%)
In SQL 2000 doing this query has query analyser sitting there bored for
quite a while.
I have changed the query to return a smaller set of data like 15000 records
and the same thing happens where it returns 99% of records quite quickly but
the last 1% of data still takes about 10 mins to return.
I have tried creating a stored procedure to do this thinking it might work
better but no, and have tried doing a top 100 PERCENT with no success either.It's difficult to speculate without knowing what your query and data look
like. A likely possibility is you're returning 29000 records out of a few
million and the records you selected are toward the front of the table scan
order. When you retrieve that many records the query processor likely will
be doing a table scan and unless there's something in the query to indicate
when it's done, every row in the table will be read. Looking at the query
plan will help you understand what the query processor is doing.
How are you determining how many records have been returned? Are you
actually scrolling through the data so you know the records have been
returned and rendered in the UI? Do you get different results if you
display the results as text?
I have to ask - do you really read 29000 records in Management Studio? Even
if you read a record a second that's almost an hour of reading. If you're
pulling back that many records to page through a look at a few records, you
using a lot of processing power, memory, and bandwidth on the server for no
reason.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:4F777E81-4736-4E8F-BDFF-C203CE50A6D2@.microsoft.com...
> My Query looks like this.
> select * from table
> where field = 'abc'
> In SQL Server 2005 I see that there is some new data retrieval mechanism
> in
> the query analyser tool so that results are streamed in. What I mean is
> that
> if I do a query on my large table I get first 1000 rows in about 1
> seconds,
> and next 1000 in the next 1s and this continues right up to about 2 mins ,
> where I have about 29000 rows sitting in query analyser, but it hasn't
> finished yet, it sits there churning for another 10 mins before it
> finishes
> and the total row count is 29052 records.
> I don't understand why 99% of results are returned quickly (15% of the
> time)
> and then the last 1% of the results are returned in a huge amount of time
> (85%)
> In SQL 2000 doing this query has query analyser sitting there bored for
> quite a while.
> I have changed the query to return a smaller set of data like 15000
> records
> and the same thing happens where it returns 99% of records quite quickly
> but
> the last 1% of data still takes about 10 mins to return.
> I have tried creating a stored procedure to do this thinking it might work
> better but no, and have tried doing a top 100 PERCENT with no success
> either.|||Thanks for the reply, I agree ... I think it is doing a full table scan.
I am determining the number of records being returned by looking at the
bottom of the management studio where it tells you how many records are
returned, also when it has a record id column, which I see increments for
each new row of data is returned. Yes I am scrolling through the data.
Display the results as text? Not sure what you mean there sorry.
I am not reading the records. Just wanting to see how long it takes to
retrieve different sets of data from this table.
"Roger Wolter[MSFT]" wrote:
> It's difficult to speculate without knowing what your query and data look
> like. A likely possibility is you're returning 29000 records out of a few
> million and the records you selected are toward the front of the table scan
> order. When you retrieve that many records the query processor likely will
> be doing a table scan and unless there's something in the query to indicate
> when it's done, every row in the table will be read. Looking at the query
> plan will help you understand what the query processor is doing.
> How are you determining how many records have been returned? Are you
> actually scrolling through the data so you know the records have been
> returned and rendered in the UI? Do you get different results if you
> display the results as text?
> I have to ask - do you really read 29000 records in Management Studio? Even
> if you read a record a second that's almost an hour of reading. If you're
> pulling back that many records to page through a look at a few records, you
> using a lot of processing power, memory, and bandwidth on the server for no
> reason.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:4F777E81-4736-4E8F-BDFF-C203CE50A6D2@.microsoft.com...
> > My Query looks like this.
> > select * from table
> > where field = 'abc'
> >
> > In SQL Server 2005 I see that there is some new data retrieval mechanism
> > in
> > the query analyser tool so that results are streamed in. What I mean is
> > that
> > if I do a query on my large table I get first 1000 rows in about 1
> > seconds,
> > and next 1000 in the next 1s and this continues right up to about 2 mins ,
> > where I have about 29000 rows sitting in query analyser, but it hasn't
> > finished yet, it sits there churning for another 10 mins before it
> > finishes
> > and the total row count is 29052 records.
> >
> > I don't understand why 99% of results are returned quickly (15% of the
> > time)
> > and then the last 1% of the results are returned in a huge amount of time
> > (85%)
> >
> > In SQL 2000 doing this query has query analyser sitting there bored for
> > quite a while.
> >
> > I have changed the query to return a smaller set of data like 15000
> > records
> > and the same thing happens where it returns 99% of records quite quickly
> > but
> > the last 1% of data still takes about 10 mins to return.
> >
> > I have tried creating a stored procedure to do this thinking it might work
> > better but no, and have tried doing a top 100 PERCENT with no success
> > either.
>|||If you're not actually looking at the results as they come in, you may be
seeing a UI painting delay. The results may all be retrieved and in memory
but not rendered in the grid yet. If you look at the Management Studio
options and change the result display option to text or a file instead of
grid, the results will appear significantly faster. Bottom line is that
with that many results, creating and formatting the grid cells generally
takes more time than retrieving the results.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:89E01783-910F-4549-94BB-1567701DB175@.microsoft.com...
> Thanks for the reply, I agree ... I think it is doing a full table scan.
> I am determining the number of records being returned by looking at the
> bottom of the management studio where it tells you how many records are
> returned, also when it has a record id column, which I see increments for
> each new row of data is returned. Yes I am scrolling through the data.
> Display the results as text? Not sure what you mean there sorry.
> I am not reading the records. Just wanting to see how long it takes to
> retrieve different sets of data from this table.
> "Roger Wolter[MSFT]" wrote:
>> It's difficult to speculate without knowing what your query and data look
>> like. A likely possibility is you're returning 29000 records out of a
>> few
>> million and the records you selected are toward the front of the table
>> scan
>> order. When you retrieve that many records the query processor likely
>> will
>> be doing a table scan and unless there's something in the query to
>> indicate
>> when it's done, every row in the table will be read. Looking at the
>> query
>> plan will help you understand what the query processor is doing.
>> How are you determining how many records have been returned? Are you
>> actually scrolling through the data so you know the records have been
>> returned and rendered in the UI? Do you get different results if you
>> display the results as text?
>> I have to ask - do you really read 29000 records in Management Studio?
>> Even
>> if you read a record a second that's almost an hour of reading. If
>> you're
>> pulling back that many records to page through a look at a few records,
>> you
>> using a lot of processing power, memory, and bandwidth on the server for
>> no
>> reason.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "Peter" <Peter@.discussions.microsoft.com> wrote in message
>> news:4F777E81-4736-4E8F-BDFF-C203CE50A6D2@.microsoft.com...
>> > My Query looks like this.
>> > select * from table
>> > where field = 'abc'
>> >
>> > In SQL Server 2005 I see that there is some new data retrieval
>> > mechanism
>> > in
>> > the query analyser tool so that results are streamed in. What I mean
>> > is
>> > that
>> > if I do a query on my large table I get first 1000 rows in about 1
>> > seconds,
>> > and next 1000 in the next 1s and this continues right up to about 2
>> > mins ,
>> > where I have about 29000 rows sitting in query analyser, but it hasn't
>> > finished yet, it sits there churning for another 10 mins before it
>> > finishes
>> > and the total row count is 29052 records.
>> >
>> > I don't understand why 99% of results are returned quickly (15% of the
>> > time)
>> > and then the last 1% of the results are returned in a huge amount of
>> > time
>> > (85%)
>> >
>> > In SQL 2000 doing this query has query analyser sitting there bored for
>> > quite a while.
>> >
>> > I have changed the query to return a smaller set of data like 15000
>> > records
>> > and the same thing happens where it returns 99% of records quite
>> > quickly
>> > but
>> > the last 1% of data still takes about 10 mins to return.
>> >
>> > I have tried creating a stored procedure to do this thinking it might
>> > work
>> > better but no, and have tried doing a top 100 PERCENT with no success
>> > either.
Large table query 99% results streamed quickly, huge pause for las
My Query looks like this.
select * from table
where field = 'abc'
In SQL Server 2005 I see that there is some new data retrieval mechanism in
the query analyser tool so that results are streamed in. What I mean is tha
t
if I do a query on my large table I get first 1000 rows in about 1 seconds,
and next 1000 in the next 1s and this continues right up to about 2 mins ,
where I have about 29000 rows sitting in query analyser, but it hasn't
finished yet, it sits there churning for another 10 mins before it finishes
and the total row count is 29052 records.
I don't understand why 99% of results are returned quickly (15% of the time)
and then the last 1% of the results are returned in a huge amount of time
(85%)
In SQL 2000 doing this query has query analyser sitting there bored for
quite a while.
I have changed the query to return a smaller set of data like 15000 records
and the same thing happens where it returns 99% of records quite quickly but
the last 1% of data still takes about 10 mins to return.
I have tried creating a stored procedure to do this thinking it might work
better but no, and have tried doing a top 100 PERCENT with no success either
.It's difficult to speculate without knowing what your query and data look
like. A likely possibility is you're returning 29000 records out of a few
million and the records you selected are toward the front of the table scan
order. When you retrieve that many records the query processor likely will
be doing a table scan and unless there's something in the query to indicate
when it's done, every row in the table will be read. Looking at the query
plan will help you understand what the query processor is doing.
How are you determining how many records have been returned? Are you
actually scrolling through the data so you know the records have been
returned and rendered in the UI? Do you get different results if you
display the results as text?
I have to ask - do you really read 29000 records in Management Studio? Even
if you read a record a second that's almost an hour of reading. If you're
pulling back that many records to page through a look at a few records, you
using a lot of processing power, memory, and bandwidth on the server for no
reason.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:4F777E81-4736-4E8F-BDFF-C203CE50A6D2@.microsoft.com...
> My Query looks like this.
> select * from table
> where field = 'abc'
> In SQL Server 2005 I see that there is some new data retrieval mechanism
> in
> the query analyser tool so that results are streamed in. What I mean is
> that
> if I do a query on my large table I get first 1000 rows in about 1
> seconds,
> and next 1000 in the next 1s and this continues right up to about 2 mins ,
> where I have about 29000 rows sitting in query analyser, but it hasn't
> finished yet, it sits there churning for another 10 mins before it
> finishes
> and the total row count is 29052 records.
> I don't understand why 99% of results are returned quickly (15% of the
> time)
> and then the last 1% of the results are returned in a huge amount of time
> (85%)
> In SQL 2000 doing this query has query analyser sitting there bored for
> quite a while.
> I have changed the query to return a smaller set of data like 15000
> records
> and the same thing happens where it returns 99% of records quite quickly
> but
> the last 1% of data still takes about 10 mins to return.
> I have tried creating a stored procedure to do this thinking it might work
> better but no, and have tried doing a top 100 PERCENT with no success
> either.
select * from table
where field = 'abc'
In SQL Server 2005 I see that there is some new data retrieval mechanism in
the query analyser tool so that results are streamed in. What I mean is tha
t
if I do a query on my large table I get first 1000 rows in about 1 seconds,
and next 1000 in the next 1s and this continues right up to about 2 mins ,
where I have about 29000 rows sitting in query analyser, but it hasn't
finished yet, it sits there churning for another 10 mins before it finishes
and the total row count is 29052 records.
I don't understand why 99% of results are returned quickly (15% of the time)
and then the last 1% of the results are returned in a huge amount of time
(85%)
In SQL 2000 doing this query has query analyser sitting there bored for
quite a while.
I have changed the query to return a smaller set of data like 15000 records
and the same thing happens where it returns 99% of records quite quickly but
the last 1% of data still takes about 10 mins to return.
I have tried creating a stored procedure to do this thinking it might work
better but no, and have tried doing a top 100 PERCENT with no success either
.It's difficult to speculate without knowing what your query and data look
like. A likely possibility is you're returning 29000 records out of a few
million and the records you selected are toward the front of the table scan
order. When you retrieve that many records the query processor likely will
be doing a table scan and unless there's something in the query to indicate
when it's done, every row in the table will be read. Looking at the query
plan will help you understand what the query processor is doing.
How are you determining how many records have been returned? Are you
actually scrolling through the data so you know the records have been
returned and rendered in the UI? Do you get different results if you
display the results as text?
I have to ask - do you really read 29000 records in Management Studio? Even
if you read a record a second that's almost an hour of reading. If you're
pulling back that many records to page through a look at a few records, you
using a lot of processing power, memory, and bandwidth on the server for no
reason.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:4F777E81-4736-4E8F-BDFF-C203CE50A6D2@.microsoft.com...
> My Query looks like this.
> select * from table
> where field = 'abc'
> In SQL Server 2005 I see that there is some new data retrieval mechanism
> in
> the query analyser tool so that results are streamed in. What I mean is
> that
> if I do a query on my large table I get first 1000 rows in about 1
> seconds,
> and next 1000 in the next 1s and this continues right up to about 2 mins ,
> where I have about 29000 rows sitting in query analyser, but it hasn't
> finished yet, it sits there churning for another 10 mins before it
> finishes
> and the total row count is 29052 records.
> I don't understand why 99% of results are returned quickly (15% of the
> time)
> and then the last 1% of the results are returned in a huge amount of time
> (85%)
> In SQL 2000 doing this query has query analyser sitting there bored for
> quite a while.
> I have changed the query to return a smaller set of data like 15000
> records
> and the same thing happens where it returns 99% of records quite quickly
> but
> the last 1% of data still takes about 10 mins to return.
> I have tried creating a stored procedure to do this thinking it might work
> better but no, and have tried doing a top 100 PERCENT with no success
> either.
Large Table Maintenance with BLOB data
I am quickly running out of room on my sqlserver2000
database. I have a 300gb or so table on a 500gb server
and am considering how to keep the table within my space
constraints.
My requirements/issues are:
1. win2k server running sqlserver2000
2. each row contains the IMAGE data type
3. The database resides on a SCSI RAID disk drive array.
4. I don't have enough room on anything to back up the
database or make a copy.
5. Uptime is of the essence as this db is used heavily
during business hours.
Can someone provide recommendations on what would be the
best way to keep this database manageable?
I want to drop any rows older than a certain date to get
rid of old images first.
Thanks,
TobinYou got big troubles.
Your first problem is to make management aware of the serious availability
problems you are facing. If this is a business critical system, they MUST
free up some resources ($$$) to handle backups and maintenance tasks OR deal
with the outage times. Document the backup and recovery strategy, warts and
all, and present it to your boss. Same for maintenance issues. Start
reporting database space ona weekly basis with projections of when it gets
90% full and is effectively used up. Be sure and note that certain
maintenance tasks may no longer be possible (DBCC DBREINDEX) given the tight
space constraints. You don't have to fix all the problems now, but you do
have to make your company's management aware of them so you don't get
blasted when this system blows up. Be sure and have a plan ready to fix the
shortcomings in case management gets scared and demands something be done
NOW.
As for the short term problem, I take older data and DTS it out to another
database. I use views to link it back to the original database. Once the
data is out, you can backup the new DB once and then lock it read-only.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Tobin" <tobin@.computac.com> wrote in message
news:f76001c3f181$3f324480$a601280a@.phx.gbl...
> I am quickly running out of room on my sqlserver2000
> database. I have a 300gb or so table on a 500gb server
> and am considering how to keep the table within my space
> constraints.
> My requirements/issues are:
> 1. win2k server running sqlserver2000
> 2. each row contains the IMAGE data type
> 3. The database resides on a SCSI RAID disk drive array.
> 4. I don't have enough room on anything to back up the
> database or make a copy.
> 5. Uptime is of the essence as this db is used heavily
> during business hours.
> Can someone provide recommendations on what would be the
> best way to keep this database manageable?
> I want to drop any rows older than a certain date to get
> rid of old images first.
> Thanks,
> Tobin|||Thanks for the advice. A question on something you said:
"Once the data is out, you can backup the new DB once and
then lock it read-only."
At the current time I can't backup the database. I am a
beginner when it comes to sqlserver. I understand that if
I just drop the old data the pages the data is stored on
might not be contiguous and I'll need to reindex the db.
Is my only solution to dts the data out or delete the rows
and then reindex to keep the database a reasonable size?
>--Original Message--
>You got big troubles.
>Your first problem is to make management aware of the
serious availability
>problems you are facing. If this is a business critical
system, they MUST
>free up some resources ($$$) to handle backups and
maintenance tasks OR deal
>with the outage times. Document the backup and recovery
strategy, warts and
>all, and present it to your boss. Same for maintenance
issues. Start
>reporting database space ona weekly basis with
projections of when it gets
>90% full and is effectively used up. Be sure and note
that certain
>maintenance tasks may no longer be possible (DBCC
DBREINDEX) given the tight
>space constraints. You don't have to fix all the
problems now, but you do
>have to make your company's management aware of them so
you don't get
>blasted when this system blows up. Be sure and have a
plan ready to fix the
>shortcomings in case management gets scared and demands
something be done
>NOW.
>As for the short term problem, I take older data and DTS
it out to another
>database. I use views to link it back to the original
database. Once the
>data is out, you can backup the new DB once and then lock
it read-only.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"Tobin" <tobin@.computac.com> wrote in message
>news:f76001c3f181$3f324480$a601280a@.phx.gbl...
array.
>
>.
>|||Reindexing and space are only part of your problem. Not having a backup is
a huge issue. Not having space for basic maintenance is just as bad.
Seriously, get your management involved and aware of the problems. If you
don't know much about SQL, get some books and some training. If they won't
listen, get your resume updated. You will need it when the system blows up.
When you DTS the data, where will you put it. My recommendation is to DTS
to another database and link the information back using a view. After it is
all done, you can set the DTS target database read-only. Use multiple
target databases (perhaps one per Month or Quarter) and you may get this
down to a more managable size.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
<anonymous@.discussions.microsoft.com> wrote in message
news:fa0a01c3f1a4$2ebf37c0$a501280a@.phx.gbl...
> Thanks for the advice. A question on something you said:
> "Once the data is out, you can backup the new DB once and
> then lock it read-only."
> At the current time I can't backup the database. I am a
> beginner when it comes to sqlserver. I understand that if
> I just drop the old data the pages the data is stored on
> might not be contiguous and I'll need to reindex the db.
> Is my only solution to dts the data out or delete the rows
> and then reindex to keep the database a reasonable size?
>
> serious availability
> system, they MUST
> maintenance tasks OR deal
> strategy, warts and
> issues. Start
> projections of when it gets
> that certain
> DBREINDEX) given the tight
> problems now, but you do
> you don't get
> plan ready to fix the
> something be done
> it out to another
> database. Once the
> it read-only.
> array.
database. I have a 300gb or so table on a 500gb server
and am considering how to keep the table within my space
constraints.
My requirements/issues are:
1. win2k server running sqlserver2000
2. each row contains the IMAGE data type
3. The database resides on a SCSI RAID disk drive array.
4. I don't have enough room on anything to back up the
database or make a copy.
5. Uptime is of the essence as this db is used heavily
during business hours.
Can someone provide recommendations on what would be the
best way to keep this database manageable?
I want to drop any rows older than a certain date to get
rid of old images first.
Thanks,
TobinYou got big troubles.
Your first problem is to make management aware of the serious availability
problems you are facing. If this is a business critical system, they MUST
free up some resources ($$$) to handle backups and maintenance tasks OR deal
with the outage times. Document the backup and recovery strategy, warts and
all, and present it to your boss. Same for maintenance issues. Start
reporting database space ona weekly basis with projections of when it gets
90% full and is effectively used up. Be sure and note that certain
maintenance tasks may no longer be possible (DBCC DBREINDEX) given the tight
space constraints. You don't have to fix all the problems now, but you do
have to make your company's management aware of them so you don't get
blasted when this system blows up. Be sure and have a plan ready to fix the
shortcomings in case management gets scared and demands something be done
NOW.
As for the short term problem, I take older data and DTS it out to another
database. I use views to link it back to the original database. Once the
data is out, you can backup the new DB once and then lock it read-only.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Tobin" <tobin@.computac.com> wrote in message
news:f76001c3f181$3f324480$a601280a@.phx.gbl...
> I am quickly running out of room on my sqlserver2000
> database. I have a 300gb or so table on a 500gb server
> and am considering how to keep the table within my space
> constraints.
> My requirements/issues are:
> 1. win2k server running sqlserver2000
> 2. each row contains the IMAGE data type
> 3. The database resides on a SCSI RAID disk drive array.
> 4. I don't have enough room on anything to back up the
> database or make a copy.
> 5. Uptime is of the essence as this db is used heavily
> during business hours.
> Can someone provide recommendations on what would be the
> best way to keep this database manageable?
> I want to drop any rows older than a certain date to get
> rid of old images first.
> Thanks,
> Tobin|||Thanks for the advice. A question on something you said:
"Once the data is out, you can backup the new DB once and
then lock it read-only."
At the current time I can't backup the database. I am a
beginner when it comes to sqlserver. I understand that if
I just drop the old data the pages the data is stored on
might not be contiguous and I'll need to reindex the db.
Is my only solution to dts the data out or delete the rows
and then reindex to keep the database a reasonable size?
>--Original Message--
>You got big troubles.
>Your first problem is to make management aware of the
serious availability
>problems you are facing. If this is a business critical
system, they MUST
>free up some resources ($$$) to handle backups and
maintenance tasks OR deal
>with the outage times. Document the backup and recovery
strategy, warts and
>all, and present it to your boss. Same for maintenance
issues. Start
>reporting database space ona weekly basis with
projections of when it gets
>90% full and is effectively used up. Be sure and note
that certain
>maintenance tasks may no longer be possible (DBCC
DBREINDEX) given the tight
>space constraints. You don't have to fix all the
problems now, but you do
>have to make your company's management aware of them so
you don't get
>blasted when this system blows up. Be sure and have a
plan ready to fix the
>shortcomings in case management gets scared and demands
something be done
>NOW.
>As for the short term problem, I take older data and DTS
it out to another
>database. I use views to link it back to the original
database. Once the
>data is out, you can backup the new DB once and then lock
it read-only.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"Tobin" <tobin@.computac.com> wrote in message
>news:f76001c3f181$3f324480$a601280a@.phx.gbl...
array.
>
>.
>|||Reindexing and space are only part of your problem. Not having a backup is
a huge issue. Not having space for basic maintenance is just as bad.
Seriously, get your management involved and aware of the problems. If you
don't know much about SQL, get some books and some training. If they won't
listen, get your resume updated. You will need it when the system blows up.
When you DTS the data, where will you put it. My recommendation is to DTS
to another database and link the information back using a view. After it is
all done, you can set the DTS target database read-only. Use multiple
target databases (perhaps one per Month or Quarter) and you may get this
down to a more managable size.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
<anonymous@.discussions.microsoft.com> wrote in message
news:fa0a01c3f1a4$2ebf37c0$a501280a@.phx.gbl...
> Thanks for the advice. A question on something you said:
> "Once the data is out, you can backup the new DB once and
> then lock it read-only."
> At the current time I can't backup the database. I am a
> beginner when it comes to sqlserver. I understand that if
> I just drop the old data the pages the data is stored on
> might not be contiguous and I'll need to reindex the db.
> Is my only solution to dts the data out or delete the rows
> and then reindex to keep the database a reasonable size?
>
> serious availability
> system, they MUST
> maintenance tasks OR deal
> strategy, warts and
> issues. Start
> projections of when it gets
> that certain
> DBREINDEX) given the tight
> problems now, but you do
> you don't get
> plan ready to fix the
> something be done
> it out to another
> database. Once the
> it read-only.
> array.
Large Table Maintenance with BLOB data
I am quickly running out of room on my sqlserver2000
database. I have a 300gb or so table on a 500gb server
and am considering how to keep the table within my space
constraints.
My requirements/issues are:
1. win2k server running sqlserver2000
2. each row contains the IMAGE data type
3. The database resides on a SCSI RAID disk drive array.
4. I don't have enough room on anything to back up the
database or make a copy.
5. Uptime is of the essence as this db is used heavily
during business hours.
Can someone provide recommendations on what would be the
best way to keep this database manageable?
I want to drop any rows older than a certain date to get
rid of old images first.
Thanks,
TobinYou got big troubles.
Your first problem is to make management aware of the serious availability
problems you are facing. If this is a business critical system, they MUST
free up some resources ($$$) to handle backups and maintenance tasks OR deal
with the outage times. Document the backup and recovery strategy, warts and
all, and present it to your boss. Same for maintenance issues. Start
reporting database space ona weekly basis with projections of when it gets
90% full and is effectively used up. Be sure and note that certain
maintenance tasks may no longer be possible (DBCC DBREINDEX) given the tight
space constraints. You don't have to fix all the problems now, but you do
have to make your company's management aware of them so you don't get
blasted when this system blows up. Be sure and have a plan ready to fix the
shortcomings in case management gets scared and demands something be done
NOW.
As for the short term problem, I take older data and DTS it out to another
database. I use views to link it back to the original database. Once the
data is out, you can backup the new DB once and then lock it read-only.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Tobin" <tobin@.computac.com> wrote in message
news:f76001c3f181$3f324480$a601280a@.phx.gbl...
> I am quickly running out of room on my sqlserver2000
> database. I have a 300gb or so table on a 500gb server
> and am considering how to keep the table within my space
> constraints.
> My requirements/issues are:
> 1. win2k server running sqlserver2000
> 2. each row contains the IMAGE data type
> 3. The database resides on a SCSI RAID disk drive array.
> 4. I don't have enough room on anything to back up the
> database or make a copy.
> 5. Uptime is of the essence as this db is used heavily
> during business hours.
> Can someone provide recommendations on what would be the
> best way to keep this database manageable?
> I want to drop any rows older than a certain date to get
> rid of old images first.
> Thanks,
> Tobin|||Thanks for the advice. A question on something you said:
"Once the data is out, you can backup the new DB once and
then lock it read-only."
At the current time I can't backup the database. I am a
beginner when it comes to sqlserver. I understand that if
I just drop the old data the pages the data is stored on
might not be contiguous and I'll need to reindex the db.
Is my only solution to dts the data out or delete the rows
and then reindex to keep the database a reasonable size?
>--Original Message--
>You got big troubles.
>Your first problem is to make management aware of the
serious availability
>problems you are facing. If this is a business critical
system, they MUST
>free up some resources ($$$) to handle backups and
maintenance tasks OR deal
>with the outage times. Document the backup and recovery
strategy, warts and
>all, and present it to your boss. Same for maintenance
issues. Start
>reporting database space ona weekly basis with
projections of when it gets
>90% full and is effectively used up. Be sure and note
that certain
>maintenance tasks may no longer be possible (DBCC
DBREINDEX) given the tight
>space constraints. You don't have to fix all the
problems now, but you do
>have to make your company's management aware of them so
you don't get
>blasted when this system blows up. Be sure and have a
plan ready to fix the
>shortcomings in case management gets scared and demands
something be done
>NOW.
>As for the short term problem, I take older data and DTS
it out to another
>database. I use views to link it back to the original
database. Once the
>data is out, you can backup the new DB once and then lock
it read-only.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"Tobin" <tobin@.computac.com> wrote in message
>news:f76001c3f181$3f324480$a601280a@.phx.gbl...
>> I am quickly running out of room on my sqlserver2000
>> database. I have a 300gb or so table on a 500gb server
>> and am considering how to keep the table within my space
>> constraints.
>> My requirements/issues are:
>> 1. win2k server running sqlserver2000
>> 2. each row contains the IMAGE data type
>> 3. The database resides on a SCSI RAID disk drive
array.
>> 4. I don't have enough room on anything to back up the
>> database or make a copy.
>> 5. Uptime is of the essence as this db is used heavily
>> during business hours.
>> Can someone provide recommendations on what would be the
>> best way to keep this database manageable?
>> I want to drop any rows older than a certain date to get
>> rid of old images first.
>> Thanks,
>> Tobin
>
>.
>|||Reindexing and space are only part of your problem. Not having a backup is
a huge issue. Not having space for basic maintenance is just as bad.
Seriously, get your management involved and aware of the problems. If you
don't know much about SQL, get some books and some training. If they won't
listen, get your resume updated. You will need it when the system blows up.
When you DTS the data, where will you put it. My recommendation is to DTS
to another database and link the information back using a view. After it is
all done, you can set the DTS target database read-only. Use multiple
target databases (perhaps one per Month or Quarter) and you may get this
down to a more managable size.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
<anonymous@.discussions.microsoft.com> wrote in message
news:fa0a01c3f1a4$2ebf37c0$a501280a@.phx.gbl...
> Thanks for the advice. A question on something you said:
> "Once the data is out, you can backup the new DB once and
> then lock it read-only."
> At the current time I can't backup the database. I am a
> beginner when it comes to sqlserver. I understand that if
> I just drop the old data the pages the data is stored on
> might not be contiguous and I'll need to reindex the db.
> Is my only solution to dts the data out or delete the rows
> and then reindex to keep the database a reasonable size?
> >--Original Message--
> >You got big troubles.
> >
> >Your first problem is to make management aware of the
> serious availability
> >problems you are facing. If this is a business critical
> system, they MUST
> >free up some resources ($$$) to handle backups and
> maintenance tasks OR deal
> >with the outage times. Document the backup and recovery
> strategy, warts and
> >all, and present it to your boss. Same for maintenance
> issues. Start
> >reporting database space ona weekly basis with
> projections of when it gets
> >90% full and is effectively used up. Be sure and note
> that certain
> >maintenance tasks may no longer be possible (DBCC
> DBREINDEX) given the tight
> >space constraints. You don't have to fix all the
> problems now, but you do
> >have to make your company's management aware of them so
> you don't get
> >blasted when this system blows up. Be sure and have a
> plan ready to fix the
> >shortcomings in case management gets scared and demands
> something be done
> >NOW.
> >
> >As for the short term problem, I take older data and DTS
> it out to another
> >database. I use views to link it back to the original
> database. Once the
> >data is out, you can backup the new DB once and then lock
> it read-only.
> >
> >--
> >Geoff N. Hiten
> >Microsoft SQL Server MVP
> >Senior Database Administrator
> >Careerbuilder.com
> >
> >I support the Professional Association for SQL Server
> >www.sqlpass.org
> >
> >"Tobin" <tobin@.computac.com> wrote in message
> >news:f76001c3f181$3f324480$a601280a@.phx.gbl...
> >> I am quickly running out of room on my sqlserver2000
> >> database. I have a 300gb or so table on a 500gb server
> >> and am considering how to keep the table within my space
> >> constraints.
> >>
> >> My requirements/issues are:
> >>
> >> 1. win2k server running sqlserver2000
> >> 2. each row contains the IMAGE data type
> >> 3. The database resides on a SCSI RAID disk drive
> array.
> >> 4. I don't have enough room on anything to back up the
> >> database or make a copy.
> >> 5. Uptime is of the essence as this db is used heavily
> >> during business hours.
> >>
> >> Can someone provide recommendations on what would be the
> >> best way to keep this database manageable?
> >>
> >> I want to drop any rows older than a certain date to get
> >> rid of old images first.
> >>
> >> Thanks,
> >>
> >> Tobin
> >
> >
> >.
> >
database. I have a 300gb or so table on a 500gb server
and am considering how to keep the table within my space
constraints.
My requirements/issues are:
1. win2k server running sqlserver2000
2. each row contains the IMAGE data type
3. The database resides on a SCSI RAID disk drive array.
4. I don't have enough room on anything to back up the
database or make a copy.
5. Uptime is of the essence as this db is used heavily
during business hours.
Can someone provide recommendations on what would be the
best way to keep this database manageable?
I want to drop any rows older than a certain date to get
rid of old images first.
Thanks,
TobinYou got big troubles.
Your first problem is to make management aware of the serious availability
problems you are facing. If this is a business critical system, they MUST
free up some resources ($$$) to handle backups and maintenance tasks OR deal
with the outage times. Document the backup and recovery strategy, warts and
all, and present it to your boss. Same for maintenance issues. Start
reporting database space ona weekly basis with projections of when it gets
90% full and is effectively used up. Be sure and note that certain
maintenance tasks may no longer be possible (DBCC DBREINDEX) given the tight
space constraints. You don't have to fix all the problems now, but you do
have to make your company's management aware of them so you don't get
blasted when this system blows up. Be sure and have a plan ready to fix the
shortcomings in case management gets scared and demands something be done
NOW.
As for the short term problem, I take older data and DTS it out to another
database. I use views to link it back to the original database. Once the
data is out, you can backup the new DB once and then lock it read-only.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Tobin" <tobin@.computac.com> wrote in message
news:f76001c3f181$3f324480$a601280a@.phx.gbl...
> I am quickly running out of room on my sqlserver2000
> database. I have a 300gb or so table on a 500gb server
> and am considering how to keep the table within my space
> constraints.
> My requirements/issues are:
> 1. win2k server running sqlserver2000
> 2. each row contains the IMAGE data type
> 3. The database resides on a SCSI RAID disk drive array.
> 4. I don't have enough room on anything to back up the
> database or make a copy.
> 5. Uptime is of the essence as this db is used heavily
> during business hours.
> Can someone provide recommendations on what would be the
> best way to keep this database manageable?
> I want to drop any rows older than a certain date to get
> rid of old images first.
> Thanks,
> Tobin|||Thanks for the advice. A question on something you said:
"Once the data is out, you can backup the new DB once and
then lock it read-only."
At the current time I can't backup the database. I am a
beginner when it comes to sqlserver. I understand that if
I just drop the old data the pages the data is stored on
might not be contiguous and I'll need to reindex the db.
Is my only solution to dts the data out or delete the rows
and then reindex to keep the database a reasonable size?
>--Original Message--
>You got big troubles.
>Your first problem is to make management aware of the
serious availability
>problems you are facing. If this is a business critical
system, they MUST
>free up some resources ($$$) to handle backups and
maintenance tasks OR deal
>with the outage times. Document the backup and recovery
strategy, warts and
>all, and present it to your boss. Same for maintenance
issues. Start
>reporting database space ona weekly basis with
projections of when it gets
>90% full and is effectively used up. Be sure and note
that certain
>maintenance tasks may no longer be possible (DBCC
DBREINDEX) given the tight
>space constraints. You don't have to fix all the
problems now, but you do
>have to make your company's management aware of them so
you don't get
>blasted when this system blows up. Be sure and have a
plan ready to fix the
>shortcomings in case management gets scared and demands
something be done
>NOW.
>As for the short term problem, I take older data and DTS
it out to another
>database. I use views to link it back to the original
database. Once the
>data is out, you can backup the new DB once and then lock
it read-only.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"Tobin" <tobin@.computac.com> wrote in message
>news:f76001c3f181$3f324480$a601280a@.phx.gbl...
>> I am quickly running out of room on my sqlserver2000
>> database. I have a 300gb or so table on a 500gb server
>> and am considering how to keep the table within my space
>> constraints.
>> My requirements/issues are:
>> 1. win2k server running sqlserver2000
>> 2. each row contains the IMAGE data type
>> 3. The database resides on a SCSI RAID disk drive
array.
>> 4. I don't have enough room on anything to back up the
>> database or make a copy.
>> 5. Uptime is of the essence as this db is used heavily
>> during business hours.
>> Can someone provide recommendations on what would be the
>> best way to keep this database manageable?
>> I want to drop any rows older than a certain date to get
>> rid of old images first.
>> Thanks,
>> Tobin
>
>.
>|||Reindexing and space are only part of your problem. Not having a backup is
a huge issue. Not having space for basic maintenance is just as bad.
Seriously, get your management involved and aware of the problems. If you
don't know much about SQL, get some books and some training. If they won't
listen, get your resume updated. You will need it when the system blows up.
When you DTS the data, where will you put it. My recommendation is to DTS
to another database and link the information back using a view. After it is
all done, you can set the DTS target database read-only. Use multiple
target databases (perhaps one per Month or Quarter) and you may get this
down to a more managable size.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
<anonymous@.discussions.microsoft.com> wrote in message
news:fa0a01c3f1a4$2ebf37c0$a501280a@.phx.gbl...
> Thanks for the advice. A question on something you said:
> "Once the data is out, you can backup the new DB once and
> then lock it read-only."
> At the current time I can't backup the database. I am a
> beginner when it comes to sqlserver. I understand that if
> I just drop the old data the pages the data is stored on
> might not be contiguous and I'll need to reindex the db.
> Is my only solution to dts the data out or delete the rows
> and then reindex to keep the database a reasonable size?
> >--Original Message--
> >You got big troubles.
> >
> >Your first problem is to make management aware of the
> serious availability
> >problems you are facing. If this is a business critical
> system, they MUST
> >free up some resources ($$$) to handle backups and
> maintenance tasks OR deal
> >with the outage times. Document the backup and recovery
> strategy, warts and
> >all, and present it to your boss. Same for maintenance
> issues. Start
> >reporting database space ona weekly basis with
> projections of when it gets
> >90% full and is effectively used up. Be sure and note
> that certain
> >maintenance tasks may no longer be possible (DBCC
> DBREINDEX) given the tight
> >space constraints. You don't have to fix all the
> problems now, but you do
> >have to make your company's management aware of them so
> you don't get
> >blasted when this system blows up. Be sure and have a
> plan ready to fix the
> >shortcomings in case management gets scared and demands
> something be done
> >NOW.
> >
> >As for the short term problem, I take older data and DTS
> it out to another
> >database. I use views to link it back to the original
> database. Once the
> >data is out, you can backup the new DB once and then lock
> it read-only.
> >
> >--
> >Geoff N. Hiten
> >Microsoft SQL Server MVP
> >Senior Database Administrator
> >Careerbuilder.com
> >
> >I support the Professional Association for SQL Server
> >www.sqlpass.org
> >
> >"Tobin" <tobin@.computac.com> wrote in message
> >news:f76001c3f181$3f324480$a601280a@.phx.gbl...
> >> I am quickly running out of room on my sqlserver2000
> >> database. I have a 300gb or so table on a 500gb server
> >> and am considering how to keep the table within my space
> >> constraints.
> >>
> >> My requirements/issues are:
> >>
> >> 1. win2k server running sqlserver2000
> >> 2. each row contains the IMAGE data type
> >> 3. The database resides on a SCSI RAID disk drive
> array.
> >> 4. I don't have enough room on anything to back up the
> >> database or make a copy.
> >> 5. Uptime is of the essence as this db is used heavily
> >> during business hours.
> >>
> >> Can someone provide recommendations on what would be the
> >> best way to keep this database manageable?
> >>
> >> I want to drop any rows older than a certain date to get
> >> rid of old images first.
> >>
> >> Thanks,
> >>
> >> Tobin
> >
> >
> >.
> >
Large Table & Merge
I added a large table to our merge pub, 3.4 million rows. Snapshot ran
rather fast, merge agents pushed rows out to 7 subscribers quickly. All
looking ok. Begin testing. Inserts at subscriber not being replicated. Look
at merge agents, change profile to Verbose. They are all displaying 0
inserts, 100 updates, 0 deletes, 0 conflicts.
Run profiler at subscriber and see the following being executed by merge
agent:
exec sp_MSproxiedmetadata 1708016, 'EACC4DE0-9FAC-420A-A4C4-1569768E01CC',
0x3CE7F05C01000000FF, 0x3CE7F05C010000003CE7F05C01...
Is this part of the synch process? Is a 3.4 mill row table WAY TOO BIG to
add to merge?
Any help much appreciated.
Thanks.
Chris
Yes it is. Basically this is the option to minimize network traffic by using
additional storage at the publisher. The publisher tracks where each row has
come from and has to tag the msmerge_contents table with this info.
However, I think you need to use the conflict viewer to understand what has
happened to your inserts.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:E058091C-50EB-4D09-8758-2F6A6E7D9785@.microsoft.com...
> I added a large table to our merge pub, 3.4 million rows. Snapshot ran
> rather fast, merge agents pushed rows out to 7 subscribers quickly. All
> looking ok. Begin testing. Inserts at subscriber not being replicated.
> Look
> at merge agents, change profile to Verbose. They are all displaying 0
> inserts, 100 updates, 0 deletes, 0 conflicts.
> Run profiler at subscriber and see the following being executed by merge
> agent:
> exec sp_MSproxiedmetadata 1708016, 'EACC4DE0-9FAC-420A-A4C4-1569768E01CC',
> 0x3CE7F05C01000000FF, 0x3CE7F05C010000003CE7F05C01...
> Is this part of the synch process? Is a 3.4 mill row table WAY TOO BIG to
> add to merge?
> Any help much appreciated.
> Thanks.
> Chris
rather fast, merge agents pushed rows out to 7 subscribers quickly. All
looking ok. Begin testing. Inserts at subscriber not being replicated. Look
at merge agents, change profile to Verbose. They are all displaying 0
inserts, 100 updates, 0 deletes, 0 conflicts.
Run profiler at subscriber and see the following being executed by merge
agent:
exec sp_MSproxiedmetadata 1708016, 'EACC4DE0-9FAC-420A-A4C4-1569768E01CC',
0x3CE7F05C01000000FF, 0x3CE7F05C010000003CE7F05C01...
Is this part of the synch process? Is a 3.4 mill row table WAY TOO BIG to
add to merge?
Any help much appreciated.
Thanks.
Chris
Yes it is. Basically this is the option to minimize network traffic by using
additional storage at the publisher. The publisher tracks where each row has
come from and has to tag the msmerge_contents table with this info.
However, I think you need to use the conflict viewer to understand what has
happened to your inserts.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:E058091C-50EB-4D09-8758-2F6A6E7D9785@.microsoft.com...
> I added a large table to our merge pub, 3.4 million rows. Snapshot ran
> rather fast, merge agents pushed rows out to 7 subscribers quickly. All
> looking ok. Begin testing. Inserts at subscriber not being replicated.
> Look
> at merge agents, change profile to Verbose. They are all displaying 0
> inserts, 100 updates, 0 deletes, 0 conflicts.
> Run profiler at subscriber and see the following being executed by merge
> agent:
> exec sp_MSproxiedmetadata 1708016, 'EACC4DE0-9FAC-420A-A4C4-1569768E01CC',
> 0x3CE7F05C01000000FF, 0x3CE7F05C010000003CE7F05C01...
> Is this part of the synch process? Is a 3.4 mill row table WAY TOO BIG to
> add to merge?
> Any help much appreciated.
> Thanks.
> Chris
Subscribe to:
Posts (Atom)