I have a 27 million record table that I need to truncate to free up
storage. Will this have any significat impact on what is a pretty busy 40
+GB database?Hard to tell but truncate is minimally logged and is typically extremely
fast.
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Brad" <me@.privacy.net> wrote in message
news:MPG.1a6384c4ae78ffb898bad6@.news...
> I have a 27 million record table that I need to truncate to free up
> storage. Will this have any significat impact on what is a pretty busy 40
> +GB database?|||Truncate is a fast, nonlogged operation. It should have minimal impact =on performance. With that said, I have tried truncating a table that =large. Be careful with truncate! Once the data is gone, it is gone!
From Books Online:
Deleting All Rows Using TRUNCATE TABLE
The TRUNCATE TABLE statement is a fast, nonlogged method of deleting all =rows in a table. It is almost always faster than a DELETE statement with =no conditions because DELETE logs each row deletion, and TRUNCATE TABLE =logs only the deallocation of whole data pages. TRUNCATE TABLE =immediately frees all the space occupied by that table's data and =indexes. The distribution pages for all indexes are also freed.
As with DELETE, the definition of a table emptied using TRUNCATE TABLE =remains in the database, along with its indexes and other associated =objects. The DROP TABLE statement must be used to drop the definition of =the table.
-- Keith
"Brad" <me@.privacy.net> wrote in message =news:MPG.1a6384c4ae78ffb898bad6@.news...
> I have a 27 million record table that I need to truncate to free up > storage. Will this have any significat impact on what is a pretty =busy 40
> +GB database?|||Just a minor correction. Truncate is minimal logged. This is to ensure that
we would be able to rollback a transaction. Here is a quick demo.
select *
into tmp
from Northwind..Orders
go
begin tran
truncate table tmp
rollback tran
go
select count(*) as 'tmp_cnt_after_truncate' from tmp
go
drop table tmp
go
--
-oj
http://www.rac4sql.net
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OVRBue80DHA.2460@.TK2MSFTNGP10.phx.gbl...
Truncate is a fast, nonlogged operation. It should have minimal impact on
performance. With that said, I have tried truncating a table that large.
Be careful with truncate! Once the data is gone, it is gone!
From Books Online:
Deleting All Rows Using TRUNCATE TABLE
The TRUNCATE TABLE statement is a fast, nonlogged method of deleting all
rows in a table. It is almost always faster than a DELETE statement with no
conditions because DELETE logs each row deletion, and TRUNCATE TABLE logs
only the deallocation of whole data pages. TRUNCATE TABLE immediately frees
all the space occupied by that table's data and indexes. The distribution
pages for all indexes are also freed.
As with DELETE, the definition of a table emptied using TRUNCATE TABLE
remains in the database, along with its indexes and other associated
objects. The DROP TABLE statement must be used to drop the definition of the
table.
Keith
"Brad" <me@.privacy.net> wrote in message
news:MPG.1a6384c4ae78ffb898bad6@.news...
> I have a 27 million record table that I need to truncate to free up
> storage. Will this have any significat impact on what is a pretty busy 40
> +GB database?
Showing posts with label million. Show all posts
Showing posts with label million. Show all posts
Friday, March 9, 2012
Wednesday, March 7, 2012
Large table, really slow queries
I'm working with a table with about 60 million records. This monster is growing every minute of the day as well, by 200,000 - 300,000 records/day. It's 11 columns wide, and has one index on a datetime column. My task is to create some custom reports based on three of these columns, including the datetime one.
The problem is response time. Any query executed on this table takes forever--anywhere between 30 seconds and 4 minutes. Queries such as this one below, as simple as it is, can take a minute or more:
select
count(dt_date) as Searches
from
SearchRecords
where
datediff(day,getdate(),dt_date)=0
As the table gets larger and large, the response time is going to get worse and worse. Long story short, what are my options to get the speed of queries down to just a few seconds with a table this big? So far the best I can come up with is index any other appropriate columns (of which there is one for sure, maybe two).
may be you can try table partitioning in sql 2005..............but i am not sure about this....refer,
http://dumthata.com/sqlchn/articles/dpart.htm
|||You should rewrite the query to remove the DATEDIFF function from the WHERE clause. This causes SQL Server to scan the table.
It is better to use WHERE dt_date BETWEEN '2007-07-26' AND '2007-07-27' for example.
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||Wow, what a world of difference it makes if I don't use datediff()!Is there a general rule that will tell me whether the functions I use in my WHERE clause will cause a table scan versus index look-up? I'm not a DBA, just a .NET developer working on the back-end at the moment.
Immensely helpful, thank-you.
|||
Not just a rule of thumb, but an absolute no-no.
If you 'wrap' a field with a function, the query processor MUST apply that function to every row in the table (or index) to determine if the 'functionalized' value meets the criteria. As a result, the query will NOT efficiently utilize any indexing. It will require at least an index scan, and perhaps even a scan of the entire table.
If in a query, all rows newer than seven days ago are sought, you can either add seven days to the stored value and compare to the current datetime, or you can subract seven days from the current datetime and compare to the stored value.
For example, in this WHERE clause, :
WHERE dateadd( day, 7, MyColumn ) >= getdate()
Indexing on MyColumn cannot be properly used, the query will need to do an index scan (if MyColumn is indexed), and a table scan otherwise.
However you can easily use a function on the non-field side of the equality and still use indexes.
WHERE MyColumn <= dateadd( day, -7, getdate() )
large table update
hello, i have a following problem: I have got 130 million row TABLEA
and I need to run this update:
UPDATE TABLEA
SET COLUMNA=(SELECT COLUMNB FROM TABLEB WHERE condition)
I suppose that for every row from TABLEA (80 million times) select statement must be executed, which is terribly slow. Isn't there a better way to write this update? Thank youCould you provide the WHERE clause in detail, pls?|||Originally posted by Coolberg
Could you provide the WHERE clause in detail, pls?
WHERE column from TABLEA=column FROM TABLEB
and I need to run this update:
UPDATE TABLEA
SET COLUMNA=(SELECT COLUMNB FROM TABLEB WHERE condition)
I suppose that for every row from TABLEA (80 million times) select statement must be executed, which is terribly slow. Isn't there a better way to write this update? Thank youCould you provide the WHERE clause in detail, pls?|||Originally posted by Coolberg
Could you provide the WHERE clause in detail, pls?
WHERE column from TABLEA=column FROM TABLEB
Large table schema changes
Quick question:
Does SQL do table/schema changes "in place"?
I've got a large table (140+ million rows of very wide
data) that we want to change the schema on -- basically
to remove a number of the unused data elements that we
don't use.
Anyway, does anyone know if SQL will do an in-place
change, or if it will copy the table to a new table, thereby
increasing my space allocation needs? I'd effectively,
temporarily, need space for two tables while the change
is happening if it copies the table first. This is not good as
I do not have enough available space at the moment.
If you've got pointers to specific MS docs regarding
this issue, please let me have 'em.
Thanks in advance."Sgt. Sausage" <nobody@.nowhere.com> wrote in message
news:402d2d58$0$52159$a0465688@.nnrp.fuse.net...
> Quick question:
> Does SQL do table/schema changes "in place"?
It depends.
If you're adding a nullable column generally it'll do it in place.
If you're adding a non-nullable column generally it'll make a copy.
> I've got a large table (140+ million rows of very wide
> data) that we want to change the schema on -- basically
> to remove a number of the unused data elements that we
> don't use.
If you're dropping columns, I believe it will drop them in place.
> Anyway, does anyone know if SQL will do an in-place
> change, or if it will copy the table to a new table, thereby
> increasing my space allocation needs? I'd effectively,
> temporarily, need space for two tables while the change
> is happening if it copies the table first. This is not good as
> I do not have enough available space at the moment.
> If you've got pointers to specific MS docs regarding
> this issue, please let me have 'em.
> Thanks in advance.|||"Sgt. Sausage" <nobody@.nowhere.com> wrote in message
news:402d2d58$0$52159$a0465688@.nnrp.fuse.net...
> Quick question:
> Does SQL do table/schema changes "in place"?
> I've got a large table (140+ million rows of very wide
> data) that we want to change the schema on -- basically
> to remove a number of the unused data elements that we
> don't use.
> Anyway, does anyone know if SQL will do an in-place
> change, or if it will copy the table to a new table, thereby
> increasing my space allocation needs? I'd effectively,
> temporarily, need space for two tables while the change
> is happening if it copies the table first. This is not good as
> I do not have enough available space at the moment.
> If you've got pointers to specific MS docs regarding
> this issue, please let me have 'em.
> Thanks in advance.
You might want to check out this article by Kalen Delaney, which explains
ALTER TABLE in some detail:
http://www.sqlmag.com/Articles/Inde...ArticleID=40538
Simon
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
Large table -- split into smaller ones?
Hi All,
I have to import about 26 million rows, each row about 1Kb in size into
our sql server. This data will be READONLY and might be updated every
quarter.
I am yet to import it and do some performance testing.
what should be the best approach to handling this much data...
should I
* have one large table?
OR
*Horizontally partition data into multiple tables?
thanks for your time
GKIt's generally best to implement a single table unless you have a
compelling reason to do otherwise. A single large table with
appropriate indexes often performs quite well without the additional
administrative complexity of partitioning.
The main advantages of horizontal partitioning are related to admin
tasks like data loads, bulk deletes and index creates. For example,
you'll need about 30GB of free space to build a clustered index on your
table but only a fraction of that amount if partitioned. On the other
hand, if you can load data sorted in clustered index sequence, you can
load with the index in place and forego the index create entirely.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"GK" <ksgkrishremovethis@.yahoo.co.uk> wrote in message
news:edyKf7gqDHA.2496@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I have to import about 26 million rows, each row about 1Kb in size
into
> our sql server. This data will be READONLY and might be updated every
> quarter.
> I am yet to import it and do some performance testing.
> what should be the best approach to handling this much data...
> should I
> * have one large table?
> OR
> *Horizontally partition data into multiple tables?
> thanks for your time
> GK
>
>|||Thanks Dan,
I understand your point about administrative overheads, but I guess
they are much reduced if I use Partitioned views in SQL server 2000. Would
you advise using that?
As far as loading the data in clustered index sequene is concerned, are you
advising me to, say load data sorted on the clustered index column(s)?
Excuse me if this question is stupid but how do I load with index in place?
Also I didn't understand why it takes so much space to create index for a
large table and less space if the table were to be split up. Does index
creation need thatmuch space just while building the index and the finally
created index take lesser space?
Thank you for your efforts.
GK
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OmoikLmqDHA.2216@.TK2MSFTNGP12.phx.gbl...
> It's generally best to implement a single table unless you have a
> compelling reason to do otherwise. A single large table with
> appropriate indexes often performs quite well without the additional
> administrative complexity of partitioning.
> The main advantages of horizontal partitioning are related to admin
> tasks like data loads, bulk deletes and index creates. For example,
> you'll need about 30GB of free space to build a clustered index on your
> table but only a fraction of that amount if partitioned. On the other
> hand, if you can load data sorted in clustered index sequence, you can
> load with the index in place and forego the index create entirely.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --
> SQL FAQ links (courtesy Neil Pike):
> http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> http://www.sqlserverfaq.com
> http://www.mssqlserver.com/faq
> --
> "GK" <ksgkrishremovethis@.yahoo.co.uk> wrote in message
> news:edyKf7gqDHA.2496@.TK2MSFTNGP09.phx.gbl...
> > Hi All,
> > I have to import about 26 million rows, each row about 1Kb in size
> into
> > our sql server. This data will be READONLY and might be updated every
> > quarter.
> > I am yet to import it and do some performance testing.
> > what should be the best approach to handling this much data...
> > should I
> > * have one large table?
> > OR
> > *Horizontally partition data into multiple tables?
> >
> > thanks for your time
> > GK
> >
> >
> >
>|||Just found this good msdn article on partioning data.
http://msdn.microsoft.com/library/default.asp?URL=/library/techart/PartitionsInDW.htm
and it says...(although they are talking about a data warehouse...mine will
not be a data warehouse exactly but it wont be an OLTP either. It'll be a
readonly table with quarterly updates)
<quote>
Query Speed
Query speed should not be considered a reason to partition the data
warehouse relational database. Query performance is similar for partitioned
and non-partitioned fact tables. When the partitioned database is properly
designed, the relational engine will include in a query plan only the
partition(s) necessary to resolve that query. For example, if the database
is partitioned by month and a query is conditioned on January 2000, the
query plan will include only the partition for January 2000. The resulting
query will perform well against the partitioned table, about the same as
against a properly indexed combined table with a clustered index on the
partitioning key.
</quote>
"GK" <ksgkrishremovethis@.yahoo.co.uk> wrote in message
news:%23%23bRH5oqDHA.2772@.TK2MSFTNGP10.phx.gbl...
> Thanks Dan,
> I understand your point about administrative overheads, but I guess
> they are much reduced if I use Partitioned views in SQL server 2000. Would
> you advise using that?
> As far as loading the data in clustered index sequene is concerned, are
you
> advising me to, say load data sorted on the clustered index column(s)?
> Excuse me if this question is stupid but how do I load with index in
place?
> Also I didn't understand why it takes so much space to create index for a
> large table and less space if the table were to be split up. Does index
> creation need thatmuch space just while building the index and the finally
> created index take lesser space?
> Thank you for your efforts.
> GK
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:OmoikLmqDHA.2216@.TK2MSFTNGP12.phx.gbl...
> > It's generally best to implement a single table unless you have a
> > compelling reason to do otherwise. A single large table with
> > appropriate indexes often performs quite well without the additional
> > administrative complexity of partitioning.
> >
> > The main advantages of horizontal partitioning are related to admin
> > tasks like data loads, bulk deletes and index creates. For example,
> > you'll need about 30GB of free space to build a clustered index on your
> > table but only a fraction of that amount if partitioned. On the other
> > hand, if you can load data sorted in clustered index sequence, you can
> > load with the index in place and forego the index create entirely.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > --
> > SQL FAQ links (courtesy Neil Pike):
> >
> > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > http://www.sqlserverfaq.com
> > http://www.mssqlserver.com/faq
> > --
> >
> > "GK" <ksgkrishremovethis@.yahoo.co.uk> wrote in message
> > news:edyKf7gqDHA.2496@.TK2MSFTNGP09.phx.gbl...
> > > Hi All,
> > > I have to import about 26 million rows, each row about 1Kb in size
> > into
> > > our sql server. This data will be READONLY and might be updated every
> > > quarter.
> > > I am yet to import it and do some performance testing.
> > > what should be the best approach to handling this much data...
> > > should I
> > > * have one large table?
> > > OR
> > > *Horizontally partition data into multiple tables?
> > >
> > > thanks for your time
> > > GK
> > >
> > >
> > >
> >
> >
>|||> I understand your point about administrative overheads, but I guess
> they are much reduced if I use Partitioned views in SQL server 2000.
Would
> you advise using that?
It depends on the problem you are trying to solve. The article you
referenced in your other post does a pretty good job of pointing out the
pros and cons. If your primary concern is query performance, then PVs
aren't the answer. Personally, wouldn't change my design to accommodate
partitioning unless the additional complexity is more than offset by the
benefits of load speed and maintenance. IMHO, your quarterly data load
probably doesn't justify the use of PVs.
> As far as loading the data in clustered index sequene is concerned,
are you
> advising me to, say load data sorted on the clustered index column(s)?
> Excuse me if this question is stupid but how do I load with index in
place?
Just create the table and its clustered index. If you then load (bulk
insert) data in sequence by the clustered index, load performance will
be good and you won't need to create the clustered index afterward.
Non-clustered indexes can be created after the load.
> Also I didn't understand why it takes so much space to create index
for a
> large table and less space if the table were to be split up. Does
index
> creation need thatmuch space just while building the index and the
finally
> created index take lesser space?
The space requirement to build the clustered index is a consideration
with large tables. You need free space of about 120% of the original
table size. This is because the entire table is rebuilt during the
create so space for old and new data is needed plus some sort work
space. After the create, space for the old data is released and the end
result is that slightly more space than the original heap is allocated
to accommodate the non-leaf nodes of the clustered index.
With multiple smaller tables, you can build each clustered index
separately. Consequently, you'll only need free space to accommodate
120% of the largest table in the lot.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"GK" <ksgkrishremovethis@.yahoo.co.uk> wrote in message
news:%23%23bRH5oqDHA.2772@.TK2MSFTNGP10.phx.gbl...
> Thanks Dan,
> I understand your point about administrative overheads, but I
guess
> they are much reduced if I use Partitioned views in SQL server 2000.
Would
> you advise using that?
> As far as loading the data in clustered index sequene is concerned,
are you
> advising me to, say load data sorted on the clustered index column(s)?
> Excuse me if this question is stupid but how do I load with index in
place?
> Also I didn't understand why it takes so much space to create index
for a
> large table and less space if the table were to be split up. Does
index
> creation need thatmuch space just while building the index and the
finally
> created index take lesser space?
> Thank you for your efforts.
> GK
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:OmoikLmqDHA.2216@.TK2MSFTNGP12.phx.gbl...
> > It's generally best to implement a single table unless you have a
> > compelling reason to do otherwise. A single large table with
> > appropriate indexes often performs quite well without the additional
> > administrative complexity of partitioning.
> >
> > The main advantages of horizontal partitioning are related to admin
> > tasks like data loads, bulk deletes and index creates. For example,
> > you'll need about 30GB of free space to build a clustered index on
your
> > table but only a fraction of that amount if partitioned. On the
other
> > hand, if you can load data sorted in clustered index sequence, you
can
> > load with the index in place and forego the index create entirely.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > --
> > SQL FAQ links (courtesy Neil Pike):
> >
> > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > http://www.sqlserverfaq.com
> > http://www.mssqlserver.com/faq
> > --
> >
> > "GK" <ksgkrishremovethis@.yahoo.co.uk> wrote in message
> > news:edyKf7gqDHA.2496@.TK2MSFTNGP09.phx.gbl...
> > > Hi All,
> > > I have to import about 26 million rows, each row about 1Kb in
size
> > into
> > > our sql server. This data will be READONLY and might be updated
every
> > > quarter.
> > > I am yet to import it and do some performance testing.
> > > what should be the best approach to handling this much data...
> > > should I
> > > * have one large table?
> > > OR
> > > *Horizontally partition data into multiple tables?
> > >
> > > thanks for your time
> > > GK
> > >
> > >
> > >
> >
> >
>|||That was very helpful.
Thanks Dan.
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OdauywrqDHA.2568@.TK2MSFTNGP09.phx.gbl...
> > I understand your point about administrative overheads, but I guess
> > they are much reduced if I use Partitioned views in SQL server 2000.
> Would
> > you advise using that?
> It depends on the problem you are trying to solve. The article you
> referenced in your other post does a pretty good job of pointing out the
> pros and cons. If your primary concern is query performance, then PVs
> aren't the answer. Personally, wouldn't change my design to accommodate
> partitioning unless the additional complexity is more than offset by the
> benefits of load speed and maintenance. IMHO, your quarterly data load
> probably doesn't justify the use of PVs.
> > As far as loading the data in clustered index sequene is concerned,
> are you
> > advising me to, say load data sorted on the clustered index column(s)?
> > Excuse me if this question is stupid but how do I load with index in
> place?
> Just create the table and its clustered index. If you then load (bulk
> insert) data in sequence by the clustered index, load performance will
> be good and you won't need to create the clustered index afterward.
> Non-clustered indexes can be created after the load.
> > Also I didn't understand why it takes so much space to create index
> for a
> > large table and less space if the table were to be split up. Does
> index
> > creation need thatmuch space just while building the index and the
> finally
> > created index take lesser space?
> The space requirement to build the clustered index is a consideration
> with large tables. You need free space of about 120% of the original
> table size. This is because the entire table is rebuilt during the
> create so space for old and new data is needed plus some sort work
> space. After the create, space for the old data is released and the end
> result is that slightly more space than the original heap is allocated
> to accommodate the non-leaf nodes of the clustered index.
> With multiple smaller tables, you can build each clustered index
> separately. Consequently, you'll only need free space to accommodate
> 120% of the largest table in the lot.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>
> "GK" <ksgkrishremovethis@.yahoo.co.uk> wrote in message
> news:%23%23bRH5oqDHA.2772@.TK2MSFTNGP10.phx.gbl...
> > Thanks Dan,
> > I understand your point about administrative overheads, but I
> guess
> > they are much reduced if I use Partitioned views in SQL server 2000.
> Would
> > you advise using that?
> >
> > As far as loading the data in clustered index sequene is concerned,
> are you
> > advising me to, say load data sorted on the clustered index column(s)?
> > Excuse me if this question is stupid but how do I load with index in
> place?
> >
> > Also I didn't understand why it takes so much space to create index
> for a
> > large table and less space if the table were to be split up. Does
> index
> > creation need thatmuch space just while building the index and the
> finally
> > created index take lesser space?
> >
> > Thank you for your efforts.
> > GK
> > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > news:OmoikLmqDHA.2216@.TK2MSFTNGP12.phx.gbl...
> > > It's generally best to implement a single table unless you have a
> > > compelling reason to do otherwise. A single large table with
> > > appropriate indexes often performs quite well without the additional
> > > administrative complexity of partitioning.
> > >
> > > The main advantages of horizontal partitioning are related to admin
> > > tasks like data loads, bulk deletes and index creates. For example,
> > > you'll need about 30GB of free space to build a clustered index on
> your
> > > table but only a fraction of that amount if partitioned. On the
> other
> > > hand, if you can load data sorted in clustered index sequence, you
> can
> > > load with the index in place and forego the index create entirely.
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > --
> > > SQL FAQ links (courtesy Neil Pike):
> > >
> > > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > > http://www.sqlserverfaq.com
> > > http://www.mssqlserver.com/faq
> > > --
> > >
> > > "GK" <ksgkrishremovethis@.yahoo.co.uk> wrote in message
> > > news:edyKf7gqDHA.2496@.TK2MSFTNGP09.phx.gbl...
> > > > Hi All,
> > > > I have to import about 26 million rows, each row about 1Kb in
> size
> > > into
> > > > our sql server. This data will be READONLY and might be updated
> every
> > > > quarter.
> > > > I am yet to import it and do some performance testing.
> > > > what should be the best approach to handling this much data...
> > > > should I
> > > > * have one large table?
> > > > OR
> > > > *Horizontally partition data into multiple tables?
> > > >
> > > > thanks for your time
> > > > GK
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
I have to import about 26 million rows, each row about 1Kb in size into
our sql server. This data will be READONLY and might be updated every
quarter.
I am yet to import it and do some performance testing.
what should be the best approach to handling this much data...
should I
* have one large table?
OR
*Horizontally partition data into multiple tables?
thanks for your time
GKIt's generally best to implement a single table unless you have a
compelling reason to do otherwise. A single large table with
appropriate indexes often performs quite well without the additional
administrative complexity of partitioning.
The main advantages of horizontal partitioning are related to admin
tasks like data loads, bulk deletes and index creates. For example,
you'll need about 30GB of free space to build a clustered index on your
table but only a fraction of that amount if partitioned. On the other
hand, if you can load data sorted in clustered index sequence, you can
load with the index in place and forego the index create entirely.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"GK" <ksgkrishremovethis@.yahoo.co.uk> wrote in message
news:edyKf7gqDHA.2496@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I have to import about 26 million rows, each row about 1Kb in size
into
> our sql server. This data will be READONLY and might be updated every
> quarter.
> I am yet to import it and do some performance testing.
> what should be the best approach to handling this much data...
> should I
> * have one large table?
> OR
> *Horizontally partition data into multiple tables?
> thanks for your time
> GK
>
>|||Thanks Dan,
I understand your point about administrative overheads, but I guess
they are much reduced if I use Partitioned views in SQL server 2000. Would
you advise using that?
As far as loading the data in clustered index sequene is concerned, are you
advising me to, say load data sorted on the clustered index column(s)?
Excuse me if this question is stupid but how do I load with index in place?
Also I didn't understand why it takes so much space to create index for a
large table and less space if the table were to be split up. Does index
creation need thatmuch space just while building the index and the finally
created index take lesser space?
Thank you for your efforts.
GK
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OmoikLmqDHA.2216@.TK2MSFTNGP12.phx.gbl...
> It's generally best to implement a single table unless you have a
> compelling reason to do otherwise. A single large table with
> appropriate indexes often performs quite well without the additional
> administrative complexity of partitioning.
> The main advantages of horizontal partitioning are related to admin
> tasks like data loads, bulk deletes and index creates. For example,
> you'll need about 30GB of free space to build a clustered index on your
> table but only a fraction of that amount if partitioned. On the other
> hand, if you can load data sorted in clustered index sequence, you can
> load with the index in place and forego the index create entirely.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --
> SQL FAQ links (courtesy Neil Pike):
> http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> http://www.sqlserverfaq.com
> http://www.mssqlserver.com/faq
> --
> "GK" <ksgkrishremovethis@.yahoo.co.uk> wrote in message
> news:edyKf7gqDHA.2496@.TK2MSFTNGP09.phx.gbl...
> > Hi All,
> > I have to import about 26 million rows, each row about 1Kb in size
> into
> > our sql server. This data will be READONLY and might be updated every
> > quarter.
> > I am yet to import it and do some performance testing.
> > what should be the best approach to handling this much data...
> > should I
> > * have one large table?
> > OR
> > *Horizontally partition data into multiple tables?
> >
> > thanks for your time
> > GK
> >
> >
> >
>|||Just found this good msdn article on partioning data.
http://msdn.microsoft.com/library/default.asp?URL=/library/techart/PartitionsInDW.htm
and it says...(although they are talking about a data warehouse...mine will
not be a data warehouse exactly but it wont be an OLTP either. It'll be a
readonly table with quarterly updates)
<quote>
Query Speed
Query speed should not be considered a reason to partition the data
warehouse relational database. Query performance is similar for partitioned
and non-partitioned fact tables. When the partitioned database is properly
designed, the relational engine will include in a query plan only the
partition(s) necessary to resolve that query. For example, if the database
is partitioned by month and a query is conditioned on January 2000, the
query plan will include only the partition for January 2000. The resulting
query will perform well against the partitioned table, about the same as
against a properly indexed combined table with a clustered index on the
partitioning key.
</quote>
"GK" <ksgkrishremovethis@.yahoo.co.uk> wrote in message
news:%23%23bRH5oqDHA.2772@.TK2MSFTNGP10.phx.gbl...
> Thanks Dan,
> I understand your point about administrative overheads, but I guess
> they are much reduced if I use Partitioned views in SQL server 2000. Would
> you advise using that?
> As far as loading the data in clustered index sequene is concerned, are
you
> advising me to, say load data sorted on the clustered index column(s)?
> Excuse me if this question is stupid but how do I load with index in
place?
> Also I didn't understand why it takes so much space to create index for a
> large table and less space if the table were to be split up. Does index
> creation need thatmuch space just while building the index and the finally
> created index take lesser space?
> Thank you for your efforts.
> GK
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:OmoikLmqDHA.2216@.TK2MSFTNGP12.phx.gbl...
> > It's generally best to implement a single table unless you have a
> > compelling reason to do otherwise. A single large table with
> > appropriate indexes often performs quite well without the additional
> > administrative complexity of partitioning.
> >
> > The main advantages of horizontal partitioning are related to admin
> > tasks like data loads, bulk deletes and index creates. For example,
> > you'll need about 30GB of free space to build a clustered index on your
> > table but only a fraction of that amount if partitioned. On the other
> > hand, if you can load data sorted in clustered index sequence, you can
> > load with the index in place and forego the index create entirely.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > --
> > SQL FAQ links (courtesy Neil Pike):
> >
> > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > http://www.sqlserverfaq.com
> > http://www.mssqlserver.com/faq
> > --
> >
> > "GK" <ksgkrishremovethis@.yahoo.co.uk> wrote in message
> > news:edyKf7gqDHA.2496@.TK2MSFTNGP09.phx.gbl...
> > > Hi All,
> > > I have to import about 26 million rows, each row about 1Kb in size
> > into
> > > our sql server. This data will be READONLY and might be updated every
> > > quarter.
> > > I am yet to import it and do some performance testing.
> > > what should be the best approach to handling this much data...
> > > should I
> > > * have one large table?
> > > OR
> > > *Horizontally partition data into multiple tables?
> > >
> > > thanks for your time
> > > GK
> > >
> > >
> > >
> >
> >
>|||> I understand your point about administrative overheads, but I guess
> they are much reduced if I use Partitioned views in SQL server 2000.
Would
> you advise using that?
It depends on the problem you are trying to solve. The article you
referenced in your other post does a pretty good job of pointing out the
pros and cons. If your primary concern is query performance, then PVs
aren't the answer. Personally, wouldn't change my design to accommodate
partitioning unless the additional complexity is more than offset by the
benefits of load speed and maintenance. IMHO, your quarterly data load
probably doesn't justify the use of PVs.
> As far as loading the data in clustered index sequene is concerned,
are you
> advising me to, say load data sorted on the clustered index column(s)?
> Excuse me if this question is stupid but how do I load with index in
place?
Just create the table and its clustered index. If you then load (bulk
insert) data in sequence by the clustered index, load performance will
be good and you won't need to create the clustered index afterward.
Non-clustered indexes can be created after the load.
> Also I didn't understand why it takes so much space to create index
for a
> large table and less space if the table were to be split up. Does
index
> creation need thatmuch space just while building the index and the
finally
> created index take lesser space?
The space requirement to build the clustered index is a consideration
with large tables. You need free space of about 120% of the original
table size. This is because the entire table is rebuilt during the
create so space for old and new data is needed plus some sort work
space. After the create, space for the old data is released and the end
result is that slightly more space than the original heap is allocated
to accommodate the non-leaf nodes of the clustered index.
With multiple smaller tables, you can build each clustered index
separately. Consequently, you'll only need free space to accommodate
120% of the largest table in the lot.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"GK" <ksgkrishremovethis@.yahoo.co.uk> wrote in message
news:%23%23bRH5oqDHA.2772@.TK2MSFTNGP10.phx.gbl...
> Thanks Dan,
> I understand your point about administrative overheads, but I
guess
> they are much reduced if I use Partitioned views in SQL server 2000.
Would
> you advise using that?
> As far as loading the data in clustered index sequene is concerned,
are you
> advising me to, say load data sorted on the clustered index column(s)?
> Excuse me if this question is stupid but how do I load with index in
place?
> Also I didn't understand why it takes so much space to create index
for a
> large table and less space if the table were to be split up. Does
index
> creation need thatmuch space just while building the index and the
finally
> created index take lesser space?
> Thank you for your efforts.
> GK
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:OmoikLmqDHA.2216@.TK2MSFTNGP12.phx.gbl...
> > It's generally best to implement a single table unless you have a
> > compelling reason to do otherwise. A single large table with
> > appropriate indexes often performs quite well without the additional
> > administrative complexity of partitioning.
> >
> > The main advantages of horizontal partitioning are related to admin
> > tasks like data loads, bulk deletes and index creates. For example,
> > you'll need about 30GB of free space to build a clustered index on
your
> > table but only a fraction of that amount if partitioned. On the
other
> > hand, if you can load data sorted in clustered index sequence, you
can
> > load with the index in place and forego the index create entirely.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > --
> > SQL FAQ links (courtesy Neil Pike):
> >
> > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > http://www.sqlserverfaq.com
> > http://www.mssqlserver.com/faq
> > --
> >
> > "GK" <ksgkrishremovethis@.yahoo.co.uk> wrote in message
> > news:edyKf7gqDHA.2496@.TK2MSFTNGP09.phx.gbl...
> > > Hi All,
> > > I have to import about 26 million rows, each row about 1Kb in
size
> > into
> > > our sql server. This data will be READONLY and might be updated
every
> > > quarter.
> > > I am yet to import it and do some performance testing.
> > > what should be the best approach to handling this much data...
> > > should I
> > > * have one large table?
> > > OR
> > > *Horizontally partition data into multiple tables?
> > >
> > > thanks for your time
> > > GK
> > >
> > >
> > >
> >
> >
>|||That was very helpful.
Thanks Dan.
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OdauywrqDHA.2568@.TK2MSFTNGP09.phx.gbl...
> > I understand your point about administrative overheads, but I guess
> > they are much reduced if I use Partitioned views in SQL server 2000.
> Would
> > you advise using that?
> It depends on the problem you are trying to solve. The article you
> referenced in your other post does a pretty good job of pointing out the
> pros and cons. If your primary concern is query performance, then PVs
> aren't the answer. Personally, wouldn't change my design to accommodate
> partitioning unless the additional complexity is more than offset by the
> benefits of load speed and maintenance. IMHO, your quarterly data load
> probably doesn't justify the use of PVs.
> > As far as loading the data in clustered index sequene is concerned,
> are you
> > advising me to, say load data sorted on the clustered index column(s)?
> > Excuse me if this question is stupid but how do I load with index in
> place?
> Just create the table and its clustered index. If you then load (bulk
> insert) data in sequence by the clustered index, load performance will
> be good and you won't need to create the clustered index afterward.
> Non-clustered indexes can be created after the load.
> > Also I didn't understand why it takes so much space to create index
> for a
> > large table and less space if the table were to be split up. Does
> index
> > creation need thatmuch space just while building the index and the
> finally
> > created index take lesser space?
> The space requirement to build the clustered index is a consideration
> with large tables. You need free space of about 120% of the original
> table size. This is because the entire table is rebuilt during the
> create so space for old and new data is needed plus some sort work
> space. After the create, space for the old data is released and the end
> result is that slightly more space than the original heap is allocated
> to accommodate the non-leaf nodes of the clustered index.
> With multiple smaller tables, you can build each clustered index
> separately. Consequently, you'll only need free space to accommodate
> 120% of the largest table in the lot.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>
> "GK" <ksgkrishremovethis@.yahoo.co.uk> wrote in message
> news:%23%23bRH5oqDHA.2772@.TK2MSFTNGP10.phx.gbl...
> > Thanks Dan,
> > I understand your point about administrative overheads, but I
> guess
> > they are much reduced if I use Partitioned views in SQL server 2000.
> Would
> > you advise using that?
> >
> > As far as loading the data in clustered index sequene is concerned,
> are you
> > advising me to, say load data sorted on the clustered index column(s)?
> > Excuse me if this question is stupid but how do I load with index in
> place?
> >
> > Also I didn't understand why it takes so much space to create index
> for a
> > large table and less space if the table were to be split up. Does
> index
> > creation need thatmuch space just while building the index and the
> finally
> > created index take lesser space?
> >
> > Thank you for your efforts.
> > GK
> > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > news:OmoikLmqDHA.2216@.TK2MSFTNGP12.phx.gbl...
> > > It's generally best to implement a single table unless you have a
> > > compelling reason to do otherwise. A single large table with
> > > appropriate indexes often performs quite well without the additional
> > > administrative complexity of partitioning.
> > >
> > > The main advantages of horizontal partitioning are related to admin
> > > tasks like data loads, bulk deletes and index creates. For example,
> > > you'll need about 30GB of free space to build a clustered index on
> your
> > > table but only a fraction of that amount if partitioned. On the
> other
> > > hand, if you can load data sorted in clustered index sequence, you
> can
> > > load with the index in place and forego the index create entirely.
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > --
> > > SQL FAQ links (courtesy Neil Pike):
> > >
> > > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > > http://www.sqlserverfaq.com
> > > http://www.mssqlserver.com/faq
> > > --
> > >
> > > "GK" <ksgkrishremovethis@.yahoo.co.uk> wrote in message
> > > news:edyKf7gqDHA.2496@.TK2MSFTNGP09.phx.gbl...
> > > > Hi All,
> > > > I have to import about 26 million rows, each row about 1Kb in
> size
> > > into
> > > > our sql server. This data will be READONLY and might be updated
> every
> > > > quarter.
> > > > I am yet to import it and do some performance testing.
> > > > what should be the best approach to handling this much data...
> > > > should I
> > > > * have one large table?
> > > > OR
> > > > *Horizontally partition data into multiple tables?
> > > >
> > > > thanks for your time
> > > > GK
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
Friday, February 24, 2012
Large number of rows.
Hi all,
A select query returns around 1 million rows. The column in the WHERE condition is indexed. This query takes nearly 1 minute for returning the all the records. Is this normal ?
Does the number of records returned affect the performance inspite of the indexing ?
Thanks,
DBLearner
The index simply allows it to find the data it has to retrieve quickly. It then has to:
load that actual record data in from disk. Depending upon how the records are split across datapages this could be a lot of disk access. Then if you have any sorting or grouping/aggregating on the result it has to do this before it can start passing the results back. After that it has to transfer the data that you are retrieving across the link (shared memory if you are running on the server across the network if not). If you are retrieving say 20 bytes per record (quite small: a couple of ints, a bit of text and a real number can be this size or larger easily) then for a million records that is 20 megabytes.|||The major 'chokepoints' will be the quality of the index vs. the WHERE clause criteria, amount of server memory, other activity, CPU power, and disk 'arrangement'. (Having the TempDb database on a dedicated array or LUN, for example.)
You might benefit from some tuning and optimization and perhaps that will increase the query responsiveness. But you definitely need to get some benchmarks in place.
Here is some information about Performance Audits, Monitoring, and Tuning:
Performance Audit
http://www.sql-server-performance.com/articles_audit.asp
http://www.sql-server-performance.com/sql_server_performance_audit10.asp
Performance -Link Server Performance Tips
http://www.sql-server-performance.com/linked_server.asp
Performance Monitoring
http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx Performance WP's
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx Troubleshooting Performance 2005
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp Hardware Performance CheckList
http://www.sql-server-performance.com/ss_performance_monitoring.asp Practical Solution for Monitoring
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
http://support.microsoft.com/?id=271509 Script to Monitor Blocking
Performance Tuning -Articles
http://www.sql-server-performance.com/articles_performance.asp
Performance Tuning –Hardware
http://www.sql-server-performance.com/sg_sql_server_performance_article.asp
Large number of records in MSMerge_GenHistory
My MsMerge_GenHistory has too many records (currently 1.6 million rows) and I
can't get it to go down.
One of my Merge publications accidentally had Subscription expire in XX days
set to 70 days. My MsMerge_Contents and GenHistory filled up to 1.6 Million
and 1.9 Million records before I caught the error. I have corrected the
Parameter and have been able to get my msmerge_contents down to 28 K rows. I
can't get MsMerge_GenHistory to come down.
I don't want to set every publication that I have to reinitialize. The
publication that had the expiration days parameter problem is a remote
subscriber with a laptop in the field and I don't have access to it on a
daily basis. It is causing me problems with other publications with
subscribers getting unable to process GenHistory messages. I have all of my
remote subscribers set with a QueryTimeout of 4000.
What can I do?
Thank You
Steve
One additional thing.
I noticed that most all of the records in msmerge_genhistory have a pubid of
NULL. A few have a with what looks like a valid guid. Are these records the
problem and can I simply delete them?
Thanks
"Steve" wrote:
> My MsMerge_GenHistory has too many records (currently 1.6 million rows) and I
> can't get it to go down.
> One of my Merge publications accidentally had Subscription expire in XX days
> set to 70 days. My MsMerge_Contents and GenHistory filled up to 1.6 Million
> and 1.9 Million records before I caught the error. I have corrected the
> Parameter and have been able to get my msmerge_contents down to 28 K rows. I
> can't get MsMerge_GenHistory to come down.
> I don't want to set every publication that I have to reinitialize. The
> publication that had the expiration days parameter problem is a remote
> subscriber with a laptop in the field and I don't have access to it on a
> daily basis. It is causing me problems with other publications with
> subscribers getting unable to process GenHistory messages. I have all of my
> remote subscribers set with a QueryTimeout of 4000.
> What can I do?
>
> Thank You
> Steve
|||What will happen if I run this with the Reinitialize Subscribers set to
False? I am trying to not reinitialize all of my subscribers.
I have found that most of my genhistory records, the pubid field is NULL.
Are these the problems and can I simply delete these?
I have a second question about a recommended max number of publications. I
have a database with 40 different publications . Each publication has 138
articles. Between all of these articles, there are in the neighborhgood of 1
million total records published. Some publications have multiple subscribers,
most publications have a single subscriber with all columns published and
static row filters. Most of these subscribers are remote users traveling. Is
this too publications? This topology has saved us from a system wide
reinitialize a couple of times when a particular subscriber has trouble or
simply will not sync in a reasonable timeframe (we have most of our
publications set to expire in 20 days).
Thanks for the help!
Steve
"Paul Ibison" wrote:
> Steve,
> have a look in BOL at sp_mergecleanupmetadata - you could run this to do a
> manual cleanup of the metedata.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
can't get it to go down.
One of my Merge publications accidentally had Subscription expire in XX days
set to 70 days. My MsMerge_Contents and GenHistory filled up to 1.6 Million
and 1.9 Million records before I caught the error. I have corrected the
Parameter and have been able to get my msmerge_contents down to 28 K rows. I
can't get MsMerge_GenHistory to come down.
I don't want to set every publication that I have to reinitialize. The
publication that had the expiration days parameter problem is a remote
subscriber with a laptop in the field and I don't have access to it on a
daily basis. It is causing me problems with other publications with
subscribers getting unable to process GenHistory messages. I have all of my
remote subscribers set with a QueryTimeout of 4000.
What can I do?
Thank You
Steve
One additional thing.
I noticed that most all of the records in msmerge_genhistory have a pubid of
NULL. A few have a with what looks like a valid guid. Are these records the
problem and can I simply delete them?
Thanks
"Steve" wrote:
> My MsMerge_GenHistory has too many records (currently 1.6 million rows) and I
> can't get it to go down.
> One of my Merge publications accidentally had Subscription expire in XX days
> set to 70 days. My MsMerge_Contents and GenHistory filled up to 1.6 Million
> and 1.9 Million records before I caught the error. I have corrected the
> Parameter and have been able to get my msmerge_contents down to 28 K rows. I
> can't get MsMerge_GenHistory to come down.
> I don't want to set every publication that I have to reinitialize. The
> publication that had the expiration days parameter problem is a remote
> subscriber with a laptop in the field and I don't have access to it on a
> daily basis. It is causing me problems with other publications with
> subscribers getting unable to process GenHistory messages. I have all of my
> remote subscribers set with a QueryTimeout of 4000.
> What can I do?
>
> Thank You
> Steve
|||What will happen if I run this with the Reinitialize Subscribers set to
False? I am trying to not reinitialize all of my subscribers.
I have found that most of my genhistory records, the pubid field is NULL.
Are these the problems and can I simply delete these?
I have a second question about a recommended max number of publications. I
have a database with 40 different publications . Each publication has 138
articles. Between all of these articles, there are in the neighborhgood of 1
million total records published. Some publications have multiple subscribers,
most publications have a single subscriber with all columns published and
static row filters. Most of these subscribers are remote users traveling. Is
this too publications? This topology has saved us from a system wide
reinitialize a couple of times when a particular subscriber has trouble or
simply will not sync in a reasonable timeframe (we have most of our
publications set to expire in 20 days).
Thanks for the help!
Steve
"Paul Ibison" wrote:
> Steve,
> have a look in BOL at sp_mergecleanupmetadata - you could run this to do a
> manual cleanup of the metedata.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
Large Delete & tran log
I have a table that has 110 million records, I will be deleting over 60 million records, but I dont have enough space to hold all the deletes on the trans log file. Is there a way to delete 1 million record then free the trans action then run another delete in one script.Set up simple model for your database and fire your deletes: 1000000 or less records - transaction log will be freed after every delete.
Monday, February 20, 2012
Large insert, how to switch off transactions?
Hello, I've a database with 8 million rows on my development pc and I've got
about 8 GB free disk space.
I have 3000 duplicate records within the table.
I ran a 'distinct' query and put them into a temp table and I am pulling
them back in again but after 2 hours I have ran out of transaction space. I
still have my records in the p# temp table but can't do an sp_rename on it
(probably because it is temporary).
Any way to run an insert like this but not write a transaction log out?
Thanks, Kevin Munro.
My sql is :
select distinct propertyid,pointid,containerid,value
into #p from propertieslink
truncate table propertieslink
insert propertieslink (propertyid,pointid,containerid,value) select
propertyid,pointid,containerid,value from #pDo not do it using SELECT ... INTO ..., it consumes a lot of resources. Try:
select propertyid, pointid, containerid, value
into #p from propertieslink where 0 = 1
insert into #p
select distinct propertyid, pointid, containerid, value
from propertieslink
truncate table propertieslink
insert propertieslink (propertyid,pointid,containerid,value) select
propertyid,pointid,containerid,value from #p
go
-- or
select propertyid,pointid,containerid,value
into #p from propertieslink where 0 = 1
insert into #p
select propertyid, pointid, containerid, value
from propertieslink
group by
propertyid, pointid, containerid, value
having
count(*) > 1
delete
a
from
propertieslink as a
inner join
#p as b
on a.propertyid = b.propertyid
and a.pointid = b.pointid
and a.containerid = b.containerid
and a.value = b.value
insert propertieslink (propertyid,pointid,containerid,value) select
propertyid,pointid,containerid,value from #p
go
AMB
"Kevin Munro" wrote:
> Hello, I've a database with 8 million rows on my development pc and I've g
ot
> about 8 GB free disk space.
> I have 3000 duplicate records within the table.
> I ran a 'distinct' query and put them into a temp table and I am pulling
> them back in again but after 2 hours I have ran out of transaction space.
I
> still have my records in the p# temp table but can't do an sp_rename on it
> (probably because it is temporary).
> Any way to run an insert like this but not write a transaction log out?
> Thanks, Kevin Munro.
> My sql is :
> select distinct propertyid,pointid,containerid,value
> into #p from propertieslink
> truncate table propertieslink
> insert propertieslink (propertyid,pointid,containerid,value) select
> propertyid,pointid,containerid,value from #p
>
>|||If you are just trying to get rid of the 3000 duplicates, there are easier
ways to do it.
A clever query with HAVING COUNT(*) would do the trick.
Regards
Mike
"Alejandro Mesa" wrote:
> Do not do it using SELECT ... INTO ..., it consumes a lot of resources. Tr
y:
> select propertyid, pointid, containerid, value
> into #p from propertieslink where 0 = 1
> insert into #p
> select distinct propertyid, pointid, containerid, value
> from propertieslink
> truncate table propertieslink
> insert propertieslink (propertyid,pointid,containerid,value) select
> propertyid,pointid,containerid,value from #p
> go
> -- or
> select propertyid,pointid,containerid,value
> into #p from propertieslink where 0 = 1
> insert into #p
> select propertyid, pointid, containerid, value
> from propertieslink
> group by
> propertyid, pointid, containerid, value
> having
> count(*) > 1
> delete
> a
> from
> propertieslink as a
> inner join
> #p as b
> on a.propertyid = b.propertyid
> and a.pointid = b.pointid
> and a.containerid = b.containerid
> and a.value = b.value
> insert propertieslink (propertyid,pointid,containerid,value) select
> propertyid,pointid,containerid,value from #p
> go
> AMB
>
> "Kevin Munro" wrote:
>|||It was included as a possible solution (second one). Your comment is welcome
anyway.
Thanks,
Alejandro Mesa
"Mike Epprecht (SQL MVP)" wrote:
> If you are just trying to get rid of the 3000 duplicates, there are easier
> ways to do it.
> A clever query with HAVING COUNT(*) would do the trick.
> Regards
> Mike
> "Alejandro Mesa" wrote:
>|||Thanks for these suggestions, the one I thought of would have been ok in a
smaller table and I'll do the HAVING COUNT query.
I got around it by dropping the indexes and it only took 5 minutes. I am
now applying the indexes one at at time and this is somewhat
transactionalising it I think.
Kevin.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:590BB8B7-640D-4530-948A-D868A5B42477@.microsoft.com...
> It was included as a possible solution (second one). Your comment is
> welcome
> anyway.
> Thanks,
> Alejandro Mesa
>
> "Mike Epprecht (SQL MVP)" wrote:
>
about 8 GB free disk space.
I have 3000 duplicate records within the table.
I ran a 'distinct' query and put them into a temp table and I am pulling
them back in again but after 2 hours I have ran out of transaction space. I
still have my records in the p# temp table but can't do an sp_rename on it
(probably because it is temporary).
Any way to run an insert like this but not write a transaction log out?
Thanks, Kevin Munro.
My sql is :
select distinct propertyid,pointid,containerid,value
into #p from propertieslink
truncate table propertieslink
insert propertieslink (propertyid,pointid,containerid,value) select
propertyid,pointid,containerid,value from #pDo not do it using SELECT ... INTO ..., it consumes a lot of resources. Try:
select propertyid, pointid, containerid, value
into #p from propertieslink where 0 = 1
insert into #p
select distinct propertyid, pointid, containerid, value
from propertieslink
truncate table propertieslink
insert propertieslink (propertyid,pointid,containerid,value) select
propertyid,pointid,containerid,value from #p
go
-- or
select propertyid,pointid,containerid,value
into #p from propertieslink where 0 = 1
insert into #p
select propertyid, pointid, containerid, value
from propertieslink
group by
propertyid, pointid, containerid, value
having
count(*) > 1
delete
a
from
propertieslink as a
inner join
#p as b
on a.propertyid = b.propertyid
and a.pointid = b.pointid
and a.containerid = b.containerid
and a.value = b.value
insert propertieslink (propertyid,pointid,containerid,value) select
propertyid,pointid,containerid,value from #p
go
AMB
"Kevin Munro" wrote:
> Hello, I've a database with 8 million rows on my development pc and I've g
ot
> about 8 GB free disk space.
> I have 3000 duplicate records within the table.
> I ran a 'distinct' query and put them into a temp table and I am pulling
> them back in again but after 2 hours I have ran out of transaction space.
I
> still have my records in the p# temp table but can't do an sp_rename on it
> (probably because it is temporary).
> Any way to run an insert like this but not write a transaction log out?
> Thanks, Kevin Munro.
> My sql is :
> select distinct propertyid,pointid,containerid,value
> into #p from propertieslink
> truncate table propertieslink
> insert propertieslink (propertyid,pointid,containerid,value) select
> propertyid,pointid,containerid,value from #p
>
>|||If you are just trying to get rid of the 3000 duplicates, there are easier
ways to do it.
A clever query with HAVING COUNT(*) would do the trick.
Regards
Mike
"Alejandro Mesa" wrote:
> Do not do it using SELECT ... INTO ..., it consumes a lot of resources. Tr
y:
> select propertyid, pointid, containerid, value
> into #p from propertieslink where 0 = 1
> insert into #p
> select distinct propertyid, pointid, containerid, value
> from propertieslink
> truncate table propertieslink
> insert propertieslink (propertyid,pointid,containerid,value) select
> propertyid,pointid,containerid,value from #p
> go
> -- or
> select propertyid,pointid,containerid,value
> into #p from propertieslink where 0 = 1
> insert into #p
> select propertyid, pointid, containerid, value
> from propertieslink
> group by
> propertyid, pointid, containerid, value
> having
> count(*) > 1
> delete
> a
> from
> propertieslink as a
> inner join
> #p as b
> on a.propertyid = b.propertyid
> and a.pointid = b.pointid
> and a.containerid = b.containerid
> and a.value = b.value
> insert propertieslink (propertyid,pointid,containerid,value) select
> propertyid,pointid,containerid,value from #p
> go
> AMB
>
> "Kevin Munro" wrote:
>|||It was included as a possible solution (second one). Your comment is welcome
anyway.
Thanks,
Alejandro Mesa
"Mike Epprecht (SQL MVP)" wrote:
> If you are just trying to get rid of the 3000 duplicates, there are easier
> ways to do it.
> A clever query with HAVING COUNT(*) would do the trick.
> Regards
> Mike
> "Alejandro Mesa" wrote:
>|||Thanks for these suggestions, the one I thought of would have been ok in a
smaller table and I'll do the HAVING COUNT query.
I got around it by dropping the indexes and it only took 5 minutes. I am
now applying the indexes one at at time and this is somewhat
transactionalising it I think.
Kevin.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:590BB8B7-640D-4530-948A-D868A5B42477@.microsoft.com...
> It was included as a possible solution (second one). Your comment is
> welcome
> anyway.
> Thanks,
> Alejandro Mesa
>
> "Mike Epprecht (SQL MVP)" wrote:
>
Large FullText tables - slow queries
Hi,
I currently have a large table (35 million rows, over 80GB). I have one varchar(max) column on the table that is used in the fulltext index.
To query the complete index is fast, for example:
SELECT 'ipod', COUNT(*)
FROM CONTAINSTABLE(MyDB.dbo.Contents, [Body], 'ipod') CT
This took 70 seconds (which I can live with). However, I seldom run queries like this, most are more like:
SELECT 'ipod', COUNT(*)
FROM CONTAINSTABLE(MyDB.dbo.Contents, [Body], 'ipod') CT
JOIN Pages ITP ON ITP.PageID = CT.[Key]
JOIN Feeds ITF ON ITP.IPID = ITF.IPID
JOIN Buyers ITB ON ITB.IBID = ITF.IBID
WHERE ITB.ID IN (1342,246)
These queries are much slower (this example took 17 minutes). I understand that FT searches the index and returns all rows that match the query to SQL. SQL then performs the joins and counts only the correct results. (Correct me if I'm wrong here).
One solution I've seen to this to put data or "tags" into the FT column - so my Body column would become something like:
'{ID:1342}' + [Body]
That sounds like a very good idea. I could then change the 2nd query above to be:
SELECT 'ipod', COUNT(*)
FROM CONTAINSTABLE(MyDB.dbo.Contents, [Body], '("ID:1342" OR "ID:246") AND "ipod"') CT
That all works well until I want to select 1000 different ID's because the FT query will become very long and complex. Also I'm only including one column (ID) in this example - but I have about 7 or 8 columns that I would need to include in these "tags". Quering multiple columns become very complex quickly and no doubt I will reach a query limit at somepoint.
If anyone has any other suggestions to the above I'd love to hear them. Another thought I'm having is to partition the table. I can find very little online about how FT behaves on partitioned tables - I fear it behaves exactly the same, what I'd like to think is that I could partition the table on an ID say 100 per partition or something, and then fulltext would only search the relevant partitions. If it behaves like this it may work. If no-one knows then I'll give it ago, but this will take me a while due to the table size - so I'm hoping one of you clever lot know!
Many thanks for any advice.
Simon
I should have said - I'm running 2005 Enterprise SP1 (can upgrade to SP2 though).|||
Hi I am not sure if this can help:
35 Mio. I am looking forward to encounter more soon.
try:
Make sure to use the noiseword concept of FT, get rid of the noice first.
Check how expensiv the joins are. Try with and without, maybe do not join other tables at this stage. If you show 10 results per page, you have joined 9990 for nothing.
Add "Categories". Searching in a certain category will bring less hits. (Add Category to keyword)
Instead of querying n-fields, query only one field. -> Category + Title + Text + other keywords -> Keywords. Query Keywords only
Try to Query also against other fields (not fulltext indexed)
Limit the number of hits in datatable (top n_dt ) and in the fulltext (top n_ft*) this should make it very, very, very fast!
.
*top n_ft - You find it here in this forum how to limit the text-hits. Perhaps I find it again and come back!
Of course database needs field AutoId as Clusterd index.
8GB Memory / 64-bit version of SQL
Disks RPM / Controler Cash ? Raid 10? / SAN / RAM Disk (SSD)
How many keywords per record do you have, how long are they in average?
How big is the Textfile?
What is the CPU?
Thomas
Large FullText tables - slow queries
Hi,
I currently have a large table (35 million rows, over 80GB). I have one varchar(max) column on the table that is used in the fulltext index.
To query the complete index is fast, for example:
SELECT 'ipod', COUNT(*)
FROM CONTAINSTABLE(MyDB.dbo.Contents, [Body], 'ipod') CT
This took 70 seconds (which I can live with). However, I seldom run queries like this, most are more like:
SELECT 'ipod', COUNT(*)
FROM CONTAINSTABLE(MyDB.dbo.Contents, [Body], 'ipod') CT
JOIN Pages ITP ON ITP.PageID = CT.[Key]
JOIN Feeds ITF ON ITP.IPID = ITF.IPID
JOIN Buyers ITB ON ITB.IBID = ITF.IBID
WHERE ITB.ID IN (1342,246)
These queries are much slower (this example took 17 minutes). I understand that FT searches the index and returns all rows that match the query to SQL. SQL then performs the joins and counts only the correct results. (Correct me if I'm wrong here).
One solution I've seen to this to put data or "tags" into the FT column - so my Body column would become something like:
'{ID:1342}' + [Body]
That sounds like a very good idea. I could then change the 2nd query above to be:
SELECT 'ipod', COUNT(*)
FROM CONTAINSTABLE(MyDB.dbo.Contents, [Body], '("ID:1342" OR "ID:246") AND "ipod"') CT
That all works well until I want to select 1000 different ID's because the FT query will become very long and complex. Also I'm only including one column (ID) in this example - but I have about 7 or 8 columns that I would need to include in these "tags". Quering multiple columns become very complex quickly and no doubt I will reach a query limit at somepoint.
If anyone has any other suggestions to the above I'd love to hear them. Another thought I'm having is to partition the table. I can find very little online about how FT behaves on partitioned tables - I fear it behaves exactly the same, what I'd like to think is that I could partition the table on an ID say 100 per partition or something, and then fulltext would only search the relevant partitions. If it behaves like this it may work. If no-one knows then I'll give it ago, but this will take me a while due to the table size - so I'm hoping one of you clever lot know!
Many thanks for any advice.
Simon
I should have said - I'm running 2005 Enterprise SP1 (can upgrade to SP2 though).|||
Hi I am not sure if this can help:
35 Mio. I am looking forward to encounter more soon.
try:
Make sure to use the noiseword concept of FT, get rid of the noice first.
Check how expensiv the joins are. Try with and without, maybe do not join other tables at this stage. If you show 10 results per page, you have joined 9990 for nothing.
Add "Categories". Searching in a certain category will bring less hits. (Add Category to keyword)
Instead of querying n-fields, query only one field. -> Category + Title + Text + other keywords -> Keywords. Query Keywords only
Try to Query also against other fields (not fulltext indexed)
Limit the number of hits in datatable (top n_dt ) and in the fulltext (top n_ft*) this should make it very, very, very fast!
.
*top n_ft - You find it here in this forum how to limit the text-hits. Perhaps I find it again and come back!
Of course database needs field AutoId as Clusterd index.
8GB Memory / 64-bit version of SQL
Disks RPM / Controler Cash ? Raid 10? / SAN / RAM Disk (SSD)
How many keywords per record do you have, how long are they in average?
How big is the Textfile?
What is the CPU?
Thomas
Subscribe to:
Posts (Atom)