If I issue a Rowlock and holdlock statements in my select statement, would it
still escalate to pagelock or tablelock or it would keep the rowlock until
the transaction is done ?.
Thanks.
First off you can not limit the lock to a row by specifying the hint. That
only tells it to start there but it is still free to escalate up to a table
if the conditions are right. Locks never escalate from row to page, they
always go straight to table if they escalate at all. Adding HOLDLOCK to a
select does little or nothing to the way the locks are done. By default SQL
Server will lock the row as it is reading it and you don't need a hint to do
that. It releases it when it is done reading the row. HOLDLOCK is usually
used to hold the locks until the end of a transaction that was started with
a BEGIN TRAN and has multiple statements in it. What is the intended purpose
of the hint and why are you worried about it escalating? If you have proper
indexes and a proper WHERE clause it should never escalate unless you are
trying to touch a major portion of the total rows.
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
> If I issue a Rowlock and holdlock statements in my select statement, would
> it
> still escalate to pagelock or tablelock or it would keep the rowlock until
> the transaction is done ?.
> Thanks.
|||Thanks for the info.......
We have a table with 15 columns. The first column is 'username' that has a
clustered index. 12 of the rest of the columns are deleted/inserted/updated
by individual users. If the users are running the same process at the same
time, each user inserts ~30000 rows into the table after deleting the rows
that are belong to them.
Out of the profiler, I have seen a few lock escalations. What is the best
way to index these columns ?
username column is like below;
username
user1
user1
user1
user1
user1
user1
user2
user2
user2
user2
user2
user3
user3
user3
user3
"Andrew J. Kelly" wrote:
> First off you can not limit the lock to a row by specifying the hint. That
> only tells it to start there but it is still free to escalate up to a table
> if the conditions are right. Locks never escalate from row to page, they
> always go straight to table if they escalate at all. Adding HOLDLOCK to a
> select does little or nothing to the way the locks are done. By default SQL
> Server will lock the row as it is reading it and you don't need a hint to do
> that. It releases it when it is done reading the row. HOLDLOCK is usually
> used to hold the locks until the end of a transaction that was started with
> a BEGIN TRAN and has multiple statements in it. What is the intended purpose
> of the hint and why are you worried about it escalating? If you have proper
> indexes and a proper WHERE clause it should never escalate unless you are
> trying to touch a major portion of the total rows.
> --
> Andrew J. Kelly SQL MVP
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
>
>
|||That doesn't make a lot of sense. You can't Delete or Insert a column. I
assume you mean the users Insert, Update or Delete the rows. How many rows
in the whole table? If you attempt to Delete 30K rows in a relatively small
table then SQL Server will most likely try to take out a table level lock.
Does the DELETE include the clustered column in the WHERE clause? You can
stop escalation to the table level by always having at least one shared lock
in the table. But if you delete the rows in smaller batches you won't
escalate as long as they are not all wrapped in a single transaction.
SET ROWCOUNT 5000 -- or some amount that does not cause escalation and is
efficient.
WHILE 1 = 1
BEGIN
DELETE FROM YourTable WHERE UserName = xxx
IF @.@.ROWCOUNT = 0
BREAK
END
SET ROWCOUNT 0
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...[vbcol=seagreen]
> Thanks for the info.......
> We have a table with 15 columns. The first column is 'username' that has a
> clustered index. 12 of the rest of the columns are
> deleted/inserted/updated
> by individual users. If the users are running the same process at the same
> time, each user inserts ~30000 rows into the table after deleting the rows
> that are belong to them.
> Out of the profiler, I have seen a few lock escalations. What is the best
> way to index these columns ?
> username column is like below;
> username
> user1
> user1
> user1
> user1
> user1
> user1
> user2
> user2
> user2
> user2
> user2
> user3
> user3
> user3
> user3
>
> "Andrew J. Kelly" wrote:
|||Yes, the users are doing the insert/update/delete through the application
which executes the stored procs.
I think that delete is not the problem but the update and the insert. First,
the particular use's name (username) is deleted from the table (all 30000
rows) but that is relatively quick. Then, the new 30000 rows are inserted to
the table. As the last step, the values are updated at the table after
certain calculations (Other 12 column)
thanks.
"Andrew J. Kelly" wrote:
> That doesn't make a lot of sense. You can't Delete or Insert a column. I
> assume you mean the users Insert, Update or Delete the rows. How many rows
> in the whole table? If you attempt to Delete 30K rows in a relatively small
> table then SQL Server will most likely try to take out a table level lock.
> Does the DELETE include the clustered column in the WHERE clause? You can
> stop escalation to the table level by always having at least one shared lock
> in the table. But if you delete the rows in smaller batches you won't
> escalate as long as they are not all wrapped in a single transaction.
>
> SET ROWCOUNT 5000 -- or some amount that does not cause escalation and is
> efficient.
> WHILE 1 = 1
> BEGIN
> DELETE FROM YourTable WHERE UserName = xxx
> IF @.@.ROWCOUNT = 0
> BREAK
> END
> SET ROWCOUNT 0
>
>
> --
> Andrew J. Kelly SQL MVP
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...
>
>
|||Why not prep the values before you insert them so you don't have to make
several passes? How are these 30K rows inserted? Are they one by one or
are you using a bulk load process?
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:68BAEDEC-AEE5-4CE8-80C8-E1D4CC5573BA@.microsoft.com...[vbcol=seagreen]
> Yes, the users are doing the insert/update/delete through the application
> which executes the stored procs.
> I think that delete is not the problem but the update and the insert.
> First,
> the particular use's name (username) is deleted from the table (all 30000
> rows) but that is relatively quick. Then, the new 30000 rows are inserted
> to
> the table. As the last step, the values are updated at the table after
> certain calculations (Other 12 column)
> thanks.
>
> "Andrew J. Kelly" wrote:
Showing posts with label hold. Show all posts
Showing posts with label hold. Show all posts
Wednesday, March 21, 2012
Row lock - Hold Lock
If I issue a Rowlock and holdlock statements in my select statement, would it
still escalate to pagelock or tablelock or it would keep the rowlock until
the transaction is done ?.
Thanks.First off you can not limit the lock to a row by specifying the hint. That
only tells it to start there but it is still free to escalate up to a table
if the conditions are right. Locks never escalate from row to page, they
always go straight to table if they escalate at all. Adding HOLDLOCK to a
select does little or nothing to the way the locks are done. By default SQL
Server will lock the row as it is reading it and you don't need a hint to do
that. It releases it when it is done reading the row. HOLDLOCK is usually
used to hold the locks until the end of a transaction that was started with
a BEGIN TRAN and has multiple statements in it. What is the intended purpose
of the hint and why are you worried about it escalating? If you have proper
indexes and a proper WHERE clause it should never escalate unless you are
trying to touch a major portion of the total rows.
--
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
> If I issue a Rowlock and holdlock statements in my select statement, would
> it
> still escalate to pagelock or tablelock or it would keep the rowlock until
> the transaction is done ?.
> Thanks.|||Thanks for the info.......
We have a table with 15 columns. The first column is 'username' that has a
clustered index. 12 of the rest of the columns are deleted/inserted/updated
by individual users. If the users are running the same process at the same
time, each user inserts ~30000 rows into the table after deleting the rows
that are belong to them.
Out of the profiler, I have seen a few lock escalations. What is the best
way to index these columns ?
username column is like below;
username
user1
user1
user1
user1
user1
user1
user2
user2
user2
user2
user2
user3
user3
user3
user3
"Andrew J. Kelly" wrote:
> First off you can not limit the lock to a row by specifying the hint. That
> only tells it to start there but it is still free to escalate up to a table
> if the conditions are right. Locks never escalate from row to page, they
> always go straight to table if they escalate at all. Adding HOLDLOCK to a
> select does little or nothing to the way the locks are done. By default SQL
> Server will lock the row as it is reading it and you don't need a hint to do
> that. It releases it when it is done reading the row. HOLDLOCK is usually
> used to hold the locks until the end of a transaction that was started with
> a BEGIN TRAN and has multiple statements in it. What is the intended purpose
> of the hint and why are you worried about it escalating? If you have proper
> indexes and a proper WHERE clause it should never escalate unless you are
> trying to touch a major portion of the total rows.
> --
> Andrew J. Kelly SQL MVP
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
> > If I issue a Rowlock and holdlock statements in my select statement, would
> > it
> > still escalate to pagelock or tablelock or it would keep the rowlock until
> > the transaction is done ?.
> >
> > Thanks.
>
>|||That doesn't make a lot of sense. You can't Delete or Insert a column. I
assume you mean the users Insert, Update or Delete the rows. How many rows
in the whole table? If you attempt to Delete 30K rows in a relatively small
table then SQL Server will most likely try to take out a table level lock.
Does the DELETE include the clustered column in the WHERE clause? You can
stop escalation to the table level by always having at least one shared lock
in the table. But if you delete the rows in smaller batches you won't
escalate as long as they are not all wrapped in a single transaction.
SET ROWCOUNT 5000 -- or some amount that does not cause escalation and is
efficient.
WHILE 1 = 1
BEGIN
DELETE FROM YourTable WHERE UserName = xxx
IF @.@.ROWCOUNT = 0
BREAK
END
SET ROWCOUNT 0
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...
> Thanks for the info.......
> We have a table with 15 columns. The first column is 'username' that has a
> clustered index. 12 of the rest of the columns are
> deleted/inserted/updated
> by individual users. If the users are running the same process at the same
> time, each user inserts ~30000 rows into the table after deleting the rows
> that are belong to them.
> Out of the profiler, I have seen a few lock escalations. What is the best
> way to index these columns ?
> username column is like below;
> username
> user1
> user1
> user1
> user1
> user1
> user1
> user2
> user2
> user2
> user2
> user2
> user3
> user3
> user3
> user3
>
> "Andrew J. Kelly" wrote:
>> First off you can not limit the lock to a row by specifying the hint.
>> That
>> only tells it to start there but it is still free to escalate up to a
>> table
>> if the conditions are right. Locks never escalate from row to page, they
>> always go straight to table if they escalate at all. Adding HOLDLOCK to
>> a
>> select does little or nothing to the way the locks are done. By default
>> SQL
>> Server will lock the row as it is reading it and you don't need a hint to
>> do
>> that. It releases it when it is done reading the row. HOLDLOCK is
>> usually
>> used to hold the locks until the end of a transaction that was started
>> with
>> a BEGIN TRAN and has multiple statements in it. What is the intended
>> purpose
>> of the hint and why are you worried about it escalating? If you have
>> proper
>> indexes and a proper WHERE clause it should never escalate unless you are
>> trying to touch a major portion of the total rows.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "DXC" <DXC@.discussions.microsoft.com> wrote in message
>> news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
>> > If I issue a Rowlock and holdlock statements in my select statement,
>> > would
>> > it
>> > still escalate to pagelock or tablelock or it would keep the rowlock
>> > until
>> > the transaction is done ?.
>> >
>> > Thanks.
>>|||Yes, the users are doing the insert/update/delete through the application
which executes the stored procs.
I think that delete is not the problem but the update and the insert. First,
the particular use's name (username) is deleted from the table (all 30000
rows) but that is relatively quick. Then, the new 30000 rows are inserted to
the table. As the last step, the values are updated at the table after
certain calculations (Other 12 column)
thanks.
"Andrew J. Kelly" wrote:
> That doesn't make a lot of sense. You can't Delete or Insert a column. I
> assume you mean the users Insert, Update or Delete the rows. How many rows
> in the whole table? If you attempt to Delete 30K rows in a relatively small
> table then SQL Server will most likely try to take out a table level lock.
> Does the DELETE include the clustered column in the WHERE clause? You can
> stop escalation to the table level by always having at least one shared lock
> in the table. But if you delete the rows in smaller batches you won't
> escalate as long as they are not all wrapped in a single transaction.
>
> SET ROWCOUNT 5000 -- or some amount that does not cause escalation and is
> efficient.
> WHILE 1 = 1
> BEGIN
> DELETE FROM YourTable WHERE UserName = xxx
> IF @.@.ROWCOUNT = 0
> BREAK
> END
> SET ROWCOUNT 0
>
>
> --
> Andrew J. Kelly SQL MVP
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...
> > Thanks for the info.......
> >
> > We have a table with 15 columns. The first column is 'username' that has a
> > clustered index. 12 of the rest of the columns are
> > deleted/inserted/updated
> > by individual users. If the users are running the same process at the same
> > time, each user inserts ~30000 rows into the table after deleting the rows
> > that are belong to them.
> >
> > Out of the profiler, I have seen a few lock escalations. What is the best
> > way to index these columns ?
> >
> > username column is like below;
> >
> > username
> >
> > user1
> > user1
> > user1
> > user1
> > user1
> > user1
> > user2
> > user2
> > user2
> > user2
> > user2
> > user3
> > user3
> > user3
> > user3
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> First off you can not limit the lock to a row by specifying the hint.
> >> That
> >> only tells it to start there but it is still free to escalate up to a
> >> table
> >> if the conditions are right. Locks never escalate from row to page, they
> >> always go straight to table if they escalate at all. Adding HOLDLOCK to
> >> a
> >> select does little or nothing to the way the locks are done. By default
> >> SQL
> >> Server will lock the row as it is reading it and you don't need a hint to
> >> do
> >> that. It releases it when it is done reading the row. HOLDLOCK is
> >> usually
> >> used to hold the locks until the end of a transaction that was started
> >> with
> >> a BEGIN TRAN and has multiple statements in it. What is the intended
> >> purpose
> >> of the hint and why are you worried about it escalating? If you have
> >> proper
> >> indexes and a proper WHERE clause it should never escalate unless you are
> >> trying to touch a major portion of the total rows.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> >> news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
> >> > If I issue a Rowlock and holdlock statements in my select statement,
> >> > would
> >> > it
> >> > still escalate to pagelock or tablelock or it would keep the rowlock
> >> > until
> >> > the transaction is done ?.
> >> >
> >> > Thanks.
> >>
> >>
> >>
>
>|||Why not prep the values before you insert them so you don't have to make
several passes? How are these 30K rows inserted? Are they one by one or
are you using a bulk load process?
--
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:68BAEDEC-AEE5-4CE8-80C8-E1D4CC5573BA@.microsoft.com...
> Yes, the users are doing the insert/update/delete through the application
> which executes the stored procs.
> I think that delete is not the problem but the update and the insert.
> First,
> the particular use's name (username) is deleted from the table (all 30000
> rows) but that is relatively quick. Then, the new 30000 rows are inserted
> to
> the table. As the last step, the values are updated at the table after
> certain calculations (Other 12 column)
> thanks.
>
> "Andrew J. Kelly" wrote:
>> That doesn't make a lot of sense. You can't Delete or Insert a column.
>> I
>> assume you mean the users Insert, Update or Delete the rows. How many
>> rows
>> in the whole table? If you attempt to Delete 30K rows in a relatively
>> small
>> table then SQL Server will most likely try to take out a table level
>> lock.
>> Does the DELETE include the clustered column in the WHERE clause? You
>> can
>> stop escalation to the table level by always having at least one shared
>> lock
>> in the table. But if you delete the rows in smaller batches you won't
>> escalate as long as they are not all wrapped in a single transaction.
>>
>> SET ROWCOUNT 5000 -- or some amount that does not cause escalation and
>> is
>> efficient.
>> WHILE 1 = 1
>> BEGIN
>> DELETE FROM YourTable WHERE UserName = xxx
>> IF @.@.ROWCOUNT = 0
>> BREAK
>> END
>> SET ROWCOUNT 0
>>
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "DXC" <DXC@.discussions.microsoft.com> wrote in message
>> news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...
>> > Thanks for the info.......
>> >
>> > We have a table with 15 columns. The first column is 'username' that
>> > has a
>> > clustered index. 12 of the rest of the columns are
>> > deleted/inserted/updated
>> > by individual users. If the users are running the same process at the
>> > same
>> > time, each user inserts ~30000 rows into the table after deleting the
>> > rows
>> > that are belong to them.
>> >
>> > Out of the profiler, I have seen a few lock escalations. What is the
>> > best
>> > way to index these columns ?
>> >
>> > username column is like below;
>> >
>> > username
>> >
>> > user1
>> > user1
>> > user1
>> > user1
>> > user1
>> > user1
>> > user2
>> > user2
>> > user2
>> > user2
>> > user2
>> > user3
>> > user3
>> > user3
>> > user3
>> >
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> First off you can not limit the lock to a row by specifying the hint.
>> >> That
>> >> only tells it to start there but it is still free to escalate up to a
>> >> table
>> >> if the conditions are right. Locks never escalate from row to page,
>> >> they
>> >> always go straight to table if they escalate at all. Adding HOLDLOCK
>> >> to
>> >> a
>> >> select does little or nothing to the way the locks are done. By
>> >> default
>> >> SQL
>> >> Server will lock the row as it is reading it and you don't need a hint
>> >> to
>> >> do
>> >> that. It releases it when it is done reading the row. HOLDLOCK is
>> >> usually
>> >> used to hold the locks until the end of a transaction that was started
>> >> with
>> >> a BEGIN TRAN and has multiple statements in it. What is the intended
>> >> purpose
>> >> of the hint and why are you worried about it escalating? If you have
>> >> proper
>> >> indexes and a proper WHERE clause it should never escalate unless you
>> >> are
>> >> trying to touch a major portion of the total rows.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "DXC" <DXC@.discussions.microsoft.com> wrote in message
>> >> news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
>> >> > If I issue a Rowlock and holdlock statements in my select statement,
>> >> > would
>> >> > it
>> >> > still escalate to pagelock or tablelock or it would keep the rowlock
>> >> > until
>> >> > the transaction is done ?.
>> >> >
>> >> > Thanks.
>> >>
>> >>
>> >>
>>sql
still escalate to pagelock or tablelock or it would keep the rowlock until
the transaction is done ?.
Thanks.First off you can not limit the lock to a row by specifying the hint. That
only tells it to start there but it is still free to escalate up to a table
if the conditions are right. Locks never escalate from row to page, they
always go straight to table if they escalate at all. Adding HOLDLOCK to a
select does little or nothing to the way the locks are done. By default SQL
Server will lock the row as it is reading it and you don't need a hint to do
that. It releases it when it is done reading the row. HOLDLOCK is usually
used to hold the locks until the end of a transaction that was started with
a BEGIN TRAN and has multiple statements in it. What is the intended purpose
of the hint and why are you worried about it escalating? If you have proper
indexes and a proper WHERE clause it should never escalate unless you are
trying to touch a major portion of the total rows.
--
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
> If I issue a Rowlock and holdlock statements in my select statement, would
> it
> still escalate to pagelock or tablelock or it would keep the rowlock until
> the transaction is done ?.
> Thanks.|||Thanks for the info.......
We have a table with 15 columns. The first column is 'username' that has a
clustered index. 12 of the rest of the columns are deleted/inserted/updated
by individual users. If the users are running the same process at the same
time, each user inserts ~30000 rows into the table after deleting the rows
that are belong to them.
Out of the profiler, I have seen a few lock escalations. What is the best
way to index these columns ?
username column is like below;
username
user1
user1
user1
user1
user1
user1
user2
user2
user2
user2
user2
user3
user3
user3
user3
"Andrew J. Kelly" wrote:
> First off you can not limit the lock to a row by specifying the hint. That
> only tells it to start there but it is still free to escalate up to a table
> if the conditions are right. Locks never escalate from row to page, they
> always go straight to table if they escalate at all. Adding HOLDLOCK to a
> select does little or nothing to the way the locks are done. By default SQL
> Server will lock the row as it is reading it and you don't need a hint to do
> that. It releases it when it is done reading the row. HOLDLOCK is usually
> used to hold the locks until the end of a transaction that was started with
> a BEGIN TRAN and has multiple statements in it. What is the intended purpose
> of the hint and why are you worried about it escalating? If you have proper
> indexes and a proper WHERE clause it should never escalate unless you are
> trying to touch a major portion of the total rows.
> --
> Andrew J. Kelly SQL MVP
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
> > If I issue a Rowlock and holdlock statements in my select statement, would
> > it
> > still escalate to pagelock or tablelock or it would keep the rowlock until
> > the transaction is done ?.
> >
> > Thanks.
>
>|||That doesn't make a lot of sense. You can't Delete or Insert a column. I
assume you mean the users Insert, Update or Delete the rows. How many rows
in the whole table? If you attempt to Delete 30K rows in a relatively small
table then SQL Server will most likely try to take out a table level lock.
Does the DELETE include the clustered column in the WHERE clause? You can
stop escalation to the table level by always having at least one shared lock
in the table. But if you delete the rows in smaller batches you won't
escalate as long as they are not all wrapped in a single transaction.
SET ROWCOUNT 5000 -- or some amount that does not cause escalation and is
efficient.
WHILE 1 = 1
BEGIN
DELETE FROM YourTable WHERE UserName = xxx
IF @.@.ROWCOUNT = 0
BREAK
END
SET ROWCOUNT 0
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...
> Thanks for the info.......
> We have a table with 15 columns. The first column is 'username' that has a
> clustered index. 12 of the rest of the columns are
> deleted/inserted/updated
> by individual users. If the users are running the same process at the same
> time, each user inserts ~30000 rows into the table after deleting the rows
> that are belong to them.
> Out of the profiler, I have seen a few lock escalations. What is the best
> way to index these columns ?
> username column is like below;
> username
> user1
> user1
> user1
> user1
> user1
> user1
> user2
> user2
> user2
> user2
> user2
> user3
> user3
> user3
> user3
>
> "Andrew J. Kelly" wrote:
>> First off you can not limit the lock to a row by specifying the hint.
>> That
>> only tells it to start there but it is still free to escalate up to a
>> table
>> if the conditions are right. Locks never escalate from row to page, they
>> always go straight to table if they escalate at all. Adding HOLDLOCK to
>> a
>> select does little or nothing to the way the locks are done. By default
>> SQL
>> Server will lock the row as it is reading it and you don't need a hint to
>> do
>> that. It releases it when it is done reading the row. HOLDLOCK is
>> usually
>> used to hold the locks until the end of a transaction that was started
>> with
>> a BEGIN TRAN and has multiple statements in it. What is the intended
>> purpose
>> of the hint and why are you worried about it escalating? If you have
>> proper
>> indexes and a proper WHERE clause it should never escalate unless you are
>> trying to touch a major portion of the total rows.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "DXC" <DXC@.discussions.microsoft.com> wrote in message
>> news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
>> > If I issue a Rowlock and holdlock statements in my select statement,
>> > would
>> > it
>> > still escalate to pagelock or tablelock or it would keep the rowlock
>> > until
>> > the transaction is done ?.
>> >
>> > Thanks.
>>|||Yes, the users are doing the insert/update/delete through the application
which executes the stored procs.
I think that delete is not the problem but the update and the insert. First,
the particular use's name (username) is deleted from the table (all 30000
rows) but that is relatively quick. Then, the new 30000 rows are inserted to
the table. As the last step, the values are updated at the table after
certain calculations (Other 12 column)
thanks.
"Andrew J. Kelly" wrote:
> That doesn't make a lot of sense. You can't Delete or Insert a column. I
> assume you mean the users Insert, Update or Delete the rows. How many rows
> in the whole table? If you attempt to Delete 30K rows in a relatively small
> table then SQL Server will most likely try to take out a table level lock.
> Does the DELETE include the clustered column in the WHERE clause? You can
> stop escalation to the table level by always having at least one shared lock
> in the table. But if you delete the rows in smaller batches you won't
> escalate as long as they are not all wrapped in a single transaction.
>
> SET ROWCOUNT 5000 -- or some amount that does not cause escalation and is
> efficient.
> WHILE 1 = 1
> BEGIN
> DELETE FROM YourTable WHERE UserName = xxx
> IF @.@.ROWCOUNT = 0
> BREAK
> END
> SET ROWCOUNT 0
>
>
> --
> Andrew J. Kelly SQL MVP
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...
> > Thanks for the info.......
> >
> > We have a table with 15 columns. The first column is 'username' that has a
> > clustered index. 12 of the rest of the columns are
> > deleted/inserted/updated
> > by individual users. If the users are running the same process at the same
> > time, each user inserts ~30000 rows into the table after deleting the rows
> > that are belong to them.
> >
> > Out of the profiler, I have seen a few lock escalations. What is the best
> > way to index these columns ?
> >
> > username column is like below;
> >
> > username
> >
> > user1
> > user1
> > user1
> > user1
> > user1
> > user1
> > user2
> > user2
> > user2
> > user2
> > user2
> > user3
> > user3
> > user3
> > user3
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> First off you can not limit the lock to a row by specifying the hint.
> >> That
> >> only tells it to start there but it is still free to escalate up to a
> >> table
> >> if the conditions are right. Locks never escalate from row to page, they
> >> always go straight to table if they escalate at all. Adding HOLDLOCK to
> >> a
> >> select does little or nothing to the way the locks are done. By default
> >> SQL
> >> Server will lock the row as it is reading it and you don't need a hint to
> >> do
> >> that. It releases it when it is done reading the row. HOLDLOCK is
> >> usually
> >> used to hold the locks until the end of a transaction that was started
> >> with
> >> a BEGIN TRAN and has multiple statements in it. What is the intended
> >> purpose
> >> of the hint and why are you worried about it escalating? If you have
> >> proper
> >> indexes and a proper WHERE clause it should never escalate unless you are
> >> trying to touch a major portion of the total rows.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> >> news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
> >> > If I issue a Rowlock and holdlock statements in my select statement,
> >> > would
> >> > it
> >> > still escalate to pagelock or tablelock or it would keep the rowlock
> >> > until
> >> > the transaction is done ?.
> >> >
> >> > Thanks.
> >>
> >>
> >>
>
>|||Why not prep the values before you insert them so you don't have to make
several passes? How are these 30K rows inserted? Are they one by one or
are you using a bulk load process?
--
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:68BAEDEC-AEE5-4CE8-80C8-E1D4CC5573BA@.microsoft.com...
> Yes, the users are doing the insert/update/delete through the application
> which executes the stored procs.
> I think that delete is not the problem but the update and the insert.
> First,
> the particular use's name (username) is deleted from the table (all 30000
> rows) but that is relatively quick. Then, the new 30000 rows are inserted
> to
> the table. As the last step, the values are updated at the table after
> certain calculations (Other 12 column)
> thanks.
>
> "Andrew J. Kelly" wrote:
>> That doesn't make a lot of sense. You can't Delete or Insert a column.
>> I
>> assume you mean the users Insert, Update or Delete the rows. How many
>> rows
>> in the whole table? If you attempt to Delete 30K rows in a relatively
>> small
>> table then SQL Server will most likely try to take out a table level
>> lock.
>> Does the DELETE include the clustered column in the WHERE clause? You
>> can
>> stop escalation to the table level by always having at least one shared
>> lock
>> in the table. But if you delete the rows in smaller batches you won't
>> escalate as long as they are not all wrapped in a single transaction.
>>
>> SET ROWCOUNT 5000 -- or some amount that does not cause escalation and
>> is
>> efficient.
>> WHILE 1 = 1
>> BEGIN
>> DELETE FROM YourTable WHERE UserName = xxx
>> IF @.@.ROWCOUNT = 0
>> BREAK
>> END
>> SET ROWCOUNT 0
>>
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "DXC" <DXC@.discussions.microsoft.com> wrote in message
>> news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...
>> > Thanks for the info.......
>> >
>> > We have a table with 15 columns. The first column is 'username' that
>> > has a
>> > clustered index. 12 of the rest of the columns are
>> > deleted/inserted/updated
>> > by individual users. If the users are running the same process at the
>> > same
>> > time, each user inserts ~30000 rows into the table after deleting the
>> > rows
>> > that are belong to them.
>> >
>> > Out of the profiler, I have seen a few lock escalations. What is the
>> > best
>> > way to index these columns ?
>> >
>> > username column is like below;
>> >
>> > username
>> >
>> > user1
>> > user1
>> > user1
>> > user1
>> > user1
>> > user1
>> > user2
>> > user2
>> > user2
>> > user2
>> > user2
>> > user3
>> > user3
>> > user3
>> > user3
>> >
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> First off you can not limit the lock to a row by specifying the hint.
>> >> That
>> >> only tells it to start there but it is still free to escalate up to a
>> >> table
>> >> if the conditions are right. Locks never escalate from row to page,
>> >> they
>> >> always go straight to table if they escalate at all. Adding HOLDLOCK
>> >> to
>> >> a
>> >> select does little or nothing to the way the locks are done. By
>> >> default
>> >> SQL
>> >> Server will lock the row as it is reading it and you don't need a hint
>> >> to
>> >> do
>> >> that. It releases it when it is done reading the row. HOLDLOCK is
>> >> usually
>> >> used to hold the locks until the end of a transaction that was started
>> >> with
>> >> a BEGIN TRAN and has multiple statements in it. What is the intended
>> >> purpose
>> >> of the hint and why are you worried about it escalating? If you have
>> >> proper
>> >> indexes and a proper WHERE clause it should never escalate unless you
>> >> are
>> >> trying to touch a major portion of the total rows.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >>
>> >>
>> >> "DXC" <DXC@.discussions.microsoft.com> wrote in message
>> >> news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
>> >> > If I issue a Rowlock and holdlock statements in my select statement,
>> >> > would
>> >> > it
>> >> > still escalate to pagelock or tablelock or it would keep the rowlock
>> >> > until
>> >> > the transaction is done ?.
>> >> >
>> >> > Thanks.
>> >>
>> >>
>> >>
>>sql
Row lock - Hold Lock
If I issue a Rowlock and holdlock statements in my select statement, would i
t
still escalate to pagelock or tablelock or it would keep the rowlock until
the transaction is done ?.
Thanks.First off you can not limit the lock to a row by specifying the hint. That
only tells it to start there but it is still free to escalate up to a table
if the conditions are right. Locks never escalate from row to page, they
always go straight to table if they escalate at all. Adding HOLDLOCK to a
select does little or nothing to the way the locks are done. By default SQL
Server will lock the row as it is reading it and you don't need a hint to do
that. It releases it when it is done reading the row. HOLDLOCK is usually
used to hold the locks until the end of a transaction that was started with
a BEGIN TRAN and has multiple statements in it. What is the intended purpose
of the hint and why are you worried about it escalating? If you have proper
indexes and a proper WHERE clause it should never escalate unless you are
trying to touch a major portion of the total rows.
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
> If I issue a Rowlock and holdlock statements in my select statement, would
> it
> still escalate to pagelock or tablelock or it would keep the rowlock until
> the transaction is done ?.
> Thanks.|||Thanks for the info.......
We have a table with 15 columns. The first column is 'username' that has a
clustered index. 12 of the rest of the columns are deleted/inserted/updated
by individual users. If the users are running the same process at the same
time, each user inserts ~30000 rows into the table after deleting the rows
that are belong to them.
Out of the profiler, I have seen a few lock escalations. What is the best
way to index these columns ?
username column is like below;
username
user1
user1
user1
user1
user1
user1
user2
user2
user2
user2
user2
user3
user3
user3
user3
"Andrew J. Kelly" wrote:
> First off you can not limit the lock to a row by specifying the hint. That
> only tells it to start there but it is still free to escalate up to a tabl
e
> if the conditions are right. Locks never escalate from row to page, they
> always go straight to table if they escalate at all. Adding HOLDLOCK to a
> select does little or nothing to the way the locks are done. By default S
QL
> Server will lock the row as it is reading it and you don't need a hint to
do
> that. It releases it when it is done reading the row. HOLDLOCK is usually
> used to hold the locks until the end of a transaction that was started wit
h
> a BEGIN TRAN and has multiple statements in it. What is the intended purpo
se
> of the hint and why are you worried about it escalating? If you have prop
er
> indexes and a proper WHERE clause it should never escalate unless you are
> trying to touch a major portion of the total rows.
> --
> Andrew J. Kelly SQL MVP
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
>
>|||That doesn't make a lot of sense. You can't Delete or Insert a column. I
assume you mean the users Insert, Update or Delete the rows. How many rows
in the whole table? If you attempt to Delete 30K rows in a relatively small
table then SQL Server will most likely try to take out a table level lock.
Does the DELETE include the clustered column in the WHERE clause? You can
stop escalation to the table level by always having at least one shared lock
in the table. But if you delete the rows in smaller batches you won't
escalate as long as they are not all wrapped in a single transaction.
SET ROWCOUNT 5000 -- or some amount that does not cause escalation and is
efficient.
WHILE 1 = 1
BEGIN
DELETE FROM YourTable WHERE UserName = xxx
IF @.@.ROWCOUNT = 0
BREAK
END
SET ROWCOUNT 0
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...[vbcol=seagreen]
> Thanks for the info.......
> We have a table with 15 columns. The first column is 'username' that has a
> clustered index. 12 of the rest of the columns are
> deleted/inserted/updated
> by individual users. If the users are running the same process at the same
> time, each user inserts ~30000 rows into the table after deleting the rows
> that are belong to them.
> Out of the profiler, I have seen a few lock escalations. What is the best
> way to index these columns ?
> username column is like below;
> username
> user1
> user1
> user1
> user1
> user1
> user1
> user2
> user2
> user2
> user2
> user2
> user3
> user3
> user3
> user3
>
> "Andrew J. Kelly" wrote:
>|||Yes, the users are doing the insert/update/delete through the application
which executes the stored procs.
I think that delete is not the problem but the update and the insert. First,
the particular use's name (username) is deleted from the table (all 30000
rows) but that is relatively quick. Then, the new 30000 rows are inserted to
the table. As the last step, the values are updated at the table after
certain calculations (Other 12 column)
thanks.
"Andrew J. Kelly" wrote:
> That doesn't make a lot of sense. You can't Delete or Insert a column. I
> assume you mean the users Insert, Update or Delete the rows. How many row
s
> in the whole table? If you attempt to Delete 30K rows in a relatively sma
ll
> table then SQL Server will most likely try to take out a table level lock.
> Does the DELETE include the clustered column in the WHERE clause? You can
> stop escalation to the table level by always having at least one shared lo
ck
> in the table. But if you delete the rows in smaller batches you won't
> escalate as long as they are not all wrapped in a single transaction.
>
> SET ROWCOUNT 5000 -- or some amount that does not cause escalation and is
> efficient.
> WHILE 1 = 1
> BEGIN
> DELETE FROM YourTable WHERE UserName = xxx
> IF @.@.ROWCOUNT = 0
> BREAK
> END
> SET ROWCOUNT 0
>
>
> --
> Andrew J. Kelly SQL MVP
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...
>
>|||Why not prep the values before you insert them so you don't have to make
several passes? How are these 30K rows inserted? Are they one by one or
are you using a bulk load process?
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:68BAEDEC-AEE5-4CE8-80C8-E1D4CC5573BA@.microsoft.com...[vbcol=seagreen]
> Yes, the users are doing the insert/update/delete through the application
> which executes the stored procs.
> I think that delete is not the problem but the update and the insert.
> First,
> the particular use's name (username) is deleted from the table (all 30000
> rows) but that is relatively quick. Then, the new 30000 rows are inserted
> to
> the table. As the last step, the values are updated at the table after
> certain calculations (Other 12 column)
> thanks.
>
> "Andrew J. Kelly" wrote:
>
t
still escalate to pagelock or tablelock or it would keep the rowlock until
the transaction is done ?.
Thanks.First off you can not limit the lock to a row by specifying the hint. That
only tells it to start there but it is still free to escalate up to a table
if the conditions are right. Locks never escalate from row to page, they
always go straight to table if they escalate at all. Adding HOLDLOCK to a
select does little or nothing to the way the locks are done. By default SQL
Server will lock the row as it is reading it and you don't need a hint to do
that. It releases it when it is done reading the row. HOLDLOCK is usually
used to hold the locks until the end of a transaction that was started with
a BEGIN TRAN and has multiple statements in it. What is the intended purpose
of the hint and why are you worried about it escalating? If you have proper
indexes and a proper WHERE clause it should never escalate unless you are
trying to touch a major portion of the total rows.
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
> If I issue a Rowlock and holdlock statements in my select statement, would
> it
> still escalate to pagelock or tablelock or it would keep the rowlock until
> the transaction is done ?.
> Thanks.|||Thanks for the info.......
We have a table with 15 columns. The first column is 'username' that has a
clustered index. 12 of the rest of the columns are deleted/inserted/updated
by individual users. If the users are running the same process at the same
time, each user inserts ~30000 rows into the table after deleting the rows
that are belong to them.
Out of the profiler, I have seen a few lock escalations. What is the best
way to index these columns ?
username column is like below;
username
user1
user1
user1
user1
user1
user1
user2
user2
user2
user2
user2
user3
user3
user3
user3
"Andrew J. Kelly" wrote:
> First off you can not limit the lock to a row by specifying the hint. That
> only tells it to start there but it is still free to escalate up to a tabl
e
> if the conditions are right. Locks never escalate from row to page, they
> always go straight to table if they escalate at all. Adding HOLDLOCK to a
> select does little or nothing to the way the locks are done. By default S
QL
> Server will lock the row as it is reading it and you don't need a hint to
do
> that. It releases it when it is done reading the row. HOLDLOCK is usually
> used to hold the locks until the end of a transaction that was started wit
h
> a BEGIN TRAN and has multiple statements in it. What is the intended purpo
se
> of the hint and why are you worried about it escalating? If you have prop
er
> indexes and a proper WHERE clause it should never escalate unless you are
> trying to touch a major portion of the total rows.
> --
> Andrew J. Kelly SQL MVP
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:C60DB011-3F36-4ED7-A337-10E6225C6946@.microsoft.com...
>
>|||That doesn't make a lot of sense. You can't Delete or Insert a column. I
assume you mean the users Insert, Update or Delete the rows. How many rows
in the whole table? If you attempt to Delete 30K rows in a relatively small
table then SQL Server will most likely try to take out a table level lock.
Does the DELETE include the clustered column in the WHERE clause? You can
stop escalation to the table level by always having at least one shared lock
in the table. But if you delete the rows in smaller batches you won't
escalate as long as they are not all wrapped in a single transaction.
SET ROWCOUNT 5000 -- or some amount that does not cause escalation and is
efficient.
WHILE 1 = 1
BEGIN
DELETE FROM YourTable WHERE UserName = xxx
IF @.@.ROWCOUNT = 0
BREAK
END
SET ROWCOUNT 0
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...[vbcol=seagreen]
> Thanks for the info.......
> We have a table with 15 columns. The first column is 'username' that has a
> clustered index. 12 of the rest of the columns are
> deleted/inserted/updated
> by individual users. If the users are running the same process at the same
> time, each user inserts ~30000 rows into the table after deleting the rows
> that are belong to them.
> Out of the profiler, I have seen a few lock escalations. What is the best
> way to index these columns ?
> username column is like below;
> username
> user1
> user1
> user1
> user1
> user1
> user1
> user2
> user2
> user2
> user2
> user2
> user3
> user3
> user3
> user3
>
> "Andrew J. Kelly" wrote:
>|||Yes, the users are doing the insert/update/delete through the application
which executes the stored procs.
I think that delete is not the problem but the update and the insert. First,
the particular use's name (username) is deleted from the table (all 30000
rows) but that is relatively quick. Then, the new 30000 rows are inserted to
the table. As the last step, the values are updated at the table after
certain calculations (Other 12 column)
thanks.
"Andrew J. Kelly" wrote:
> That doesn't make a lot of sense. You can't Delete or Insert a column. I
> assume you mean the users Insert, Update or Delete the rows. How many row
s
> in the whole table? If you attempt to Delete 30K rows in a relatively sma
ll
> table then SQL Server will most likely try to take out a table level lock.
> Does the DELETE include the clustered column in the WHERE clause? You can
> stop escalation to the table level by always having at least one shared lo
ck
> in the table. But if you delete the rows in smaller batches you won't
> escalate as long as they are not all wrapped in a single transaction.
>
> SET ROWCOUNT 5000 -- or some amount that does not cause escalation and is
> efficient.
> WHILE 1 = 1
> BEGIN
> DELETE FROM YourTable WHERE UserName = xxx
> IF @.@.ROWCOUNT = 0
> BREAK
> END
> SET ROWCOUNT 0
>
>
> --
> Andrew J. Kelly SQL MVP
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:438BBA09-0C4F-4BDA-9D92-64EDB7557826@.microsoft.com...
>
>|||Why not prep the values before you insert them so you don't have to make
several passes? How are these 30K rows inserted? Are they one by one or
are you using a bulk load process?
Andrew J. Kelly SQL MVP
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:68BAEDEC-AEE5-4CE8-80C8-E1D4CC5573BA@.microsoft.com...[vbcol=seagreen]
> Yes, the users are doing the insert/update/delete through the application
> which executes the stored procs.
> I think that delete is not the problem but the update and the insert.
> First,
> the particular use's name (username) is deleted from the table (all 30000
> rows) but that is relatively quick. Then, the new 30000 rows are inserted
> to
> the table. As the last step, the values are updated at the table after
> certain calculations (Other 12 column)
> thanks.
>
> "Andrew J. Kelly" wrote:
>
Tuesday, March 20, 2012
Row limit
Can a MS Sql table hold 5 million records
Yes.
You will need adequate hardware and good structure and indexes, but 5M rows
is well within SQL maximum capacity limits.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"MikeB" <mbercu@.g1sg.com> wrote in message
news:D7E7356A-921E-4817-8DED-8B82ACFB7A81@.microsoft.com...
> Can a MS Sql table hold 5 million records
|||It can hold waaaaay more than that. :-)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"MikeB" <mbercu@.g1sg.com> wrote in message
news:D7E7356A-921E-4817-8DED-8B82ACFB7A81@.microsoft.com...
Can a MS Sql table hold 5 million records
|||SQL Server does not have a row limit... this will be based mostly on your
hardware. Please read http://www.aspfaq.com/2345
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"MikeB" <mbercu@.g1sg.com> wrote in message
news:D7E7356A-921E-4817-8DED-8B82ACFB7A81@.microsoft.com...
> Can a MS Sql table hold 5 million records
|||We have 2.5 million in a health claim table - back in the mainframe days
reports could run for hours. SQL handles queries against this table in
seconds. We stayed on the Digital VAX mainframe till SQL2K came along.
Whenever a query doesn't work the way we want, we look at index issues -
that has always corrected odd behavior.
We have used INDEX HINTS in some queries - and they also help...
Even queries not against indexed scans still work in under a minute.
"MikeB" <mbercu@.g1sg.com> wrote in message
news:D7E7356A-921E-4817-8DED-8B82ACFB7A81@.microsoft.com...
> Can a MS Sql table hold 5 million records
|||Hi,
There is no limitation of ROWS in SQL Server. But to store this you should
have adequate hard disk space.
As well as during retrieval you must have adequate Indexes to make the
retrieval faster.
Note:
If you have mutiple disk controllers you can create filegroups in which data
is ponted towards one disk controller and index against other. This will
reduce the I/O and executes the query faster.
Thanks
Hari
MCDBA
"MikeB" <mbercu@.g1sg.com> wrote in message
news:D7E7356A-921E-4817-8DED-8B82ACFB7A81@.microsoft.com...
> Can a MS Sql table hold 5 million records
Yes.
You will need adequate hardware and good structure and indexes, but 5M rows
is well within SQL maximum capacity limits.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"MikeB" <mbercu@.g1sg.com> wrote in message
news:D7E7356A-921E-4817-8DED-8B82ACFB7A81@.microsoft.com...
> Can a MS Sql table hold 5 million records
|||It can hold waaaaay more than that. :-)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"MikeB" <mbercu@.g1sg.com> wrote in message
news:D7E7356A-921E-4817-8DED-8B82ACFB7A81@.microsoft.com...
Can a MS Sql table hold 5 million records
|||SQL Server does not have a row limit... this will be based mostly on your
hardware. Please read http://www.aspfaq.com/2345
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"MikeB" <mbercu@.g1sg.com> wrote in message
news:D7E7356A-921E-4817-8DED-8B82ACFB7A81@.microsoft.com...
> Can a MS Sql table hold 5 million records
|||We have 2.5 million in a health claim table - back in the mainframe days
reports could run for hours. SQL handles queries against this table in
seconds. We stayed on the Digital VAX mainframe till SQL2K came along.
Whenever a query doesn't work the way we want, we look at index issues -
that has always corrected odd behavior.
We have used INDEX HINTS in some queries - and they also help...
Even queries not against indexed scans still work in under a minute.
"MikeB" <mbercu@.g1sg.com> wrote in message
news:D7E7356A-921E-4817-8DED-8B82ACFB7A81@.microsoft.com...
> Can a MS Sql table hold 5 million records
|||Hi,
There is no limitation of ROWS in SQL Server. But to store this you should
have adequate hard disk space.
As well as during retrieval you must have adequate Indexes to make the
retrieval faster.
Note:
If you have mutiple disk controllers you can create filegroups in which data
is ponted towards one disk controller and index against other. This will
reduce the I/O and executes the query faster.
Thanks
Hari
MCDBA
"MikeB" <mbercu@.g1sg.com> wrote in message
news:D7E7356A-921E-4817-8DED-8B82ACFB7A81@.microsoft.com...
> Can a MS Sql table hold 5 million records
Row limit
Can a MS Sql table hold 5 million recordsYes.
You will need adequate hardware and good structure and indexes, but 5M rows
is well within SQL maximum capacity limits.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"MikeB" <mbercu@.g1sg.com> wrote in message
news:D7E7356A-921E-4817-8DED-8B82ACFB7A81@.microsoft.com...
> Can a MS Sql table hold 5 million records|||It can hold waaaaay more than that. :-)
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"MikeB" <mbercu@.g1sg.com> wrote in message
news:D7E7356A-921E-4817-8DED-8B82ACFB7A81@.microsoft.com...
Can a MS Sql table hold 5 million records|||SQL Server does not have a row limit... this will be based mostly on your
hardware. Please read http://www.aspfaq.com/2345
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"MikeB" <mbercu@.g1sg.com> wrote in message
news:D7E7356A-921E-4817-8DED-8B82ACFB7A81@.microsoft.com...
> Can a MS Sql table hold 5 million records|||We have 2.5 million in a health claim table - back in the mainframe days
reports could run for hours. SQL handles queries against this table in
seconds. We stayed on the Digital VAX mainframe till SQL2K came along.
Whenever a query doesn't work the way we want, we look at index issues -
that has always corrected odd behavior.
We have used INDEX HINTS in some queries - and they also help...
Even queries not against indexed scans still work in under a minute.
"MikeB" <mbercu@.g1sg.com> wrote in message
news:D7E7356A-921E-4817-8DED-8B82ACFB7A81@.microsoft.com...
> Can a MS Sql table hold 5 million records|||Hi,
There is no limitation of ROWS in SQL Server. But to store this you should
have adequate hard disk space.
As well as during retrieval you must have adequate Indexes to make the
retrieval faster.
Note:
If you have mutiple disk controllers you can create filegroups in which data
is ponted towards one disk controller and index against other. This will
reduce the I/O and executes the query faster.
Thanks
Hari
MCDBA
"MikeB" <mbercu@.g1sg.com> wrote in message
news:D7E7356A-921E-4817-8DED-8B82ACFB7A81@.microsoft.com...
> Can a MS Sql table hold 5 million records
You will need adequate hardware and good structure and indexes, but 5M rows
is well within SQL maximum capacity limits.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"MikeB" <mbercu@.g1sg.com> wrote in message
news:D7E7356A-921E-4817-8DED-8B82ACFB7A81@.microsoft.com...
> Can a MS Sql table hold 5 million records|||It can hold waaaaay more than that. :-)
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"MikeB" <mbercu@.g1sg.com> wrote in message
news:D7E7356A-921E-4817-8DED-8B82ACFB7A81@.microsoft.com...
Can a MS Sql table hold 5 million records|||SQL Server does not have a row limit... this will be based mostly on your
hardware. Please read http://www.aspfaq.com/2345
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"MikeB" <mbercu@.g1sg.com> wrote in message
news:D7E7356A-921E-4817-8DED-8B82ACFB7A81@.microsoft.com...
> Can a MS Sql table hold 5 million records|||We have 2.5 million in a health claim table - back in the mainframe days
reports could run for hours. SQL handles queries against this table in
seconds. We stayed on the Digital VAX mainframe till SQL2K came along.
Whenever a query doesn't work the way we want, we look at index issues -
that has always corrected odd behavior.
We have used INDEX HINTS in some queries - and they also help...
Even queries not against indexed scans still work in under a minute.
"MikeB" <mbercu@.g1sg.com> wrote in message
news:D7E7356A-921E-4817-8DED-8B82ACFB7A81@.microsoft.com...
> Can a MS Sql table hold 5 million records|||Hi,
There is no limitation of ROWS in SQL Server. But to store this you should
have adequate hard disk space.
As well as during retrieval you must have adequate Indexes to make the
retrieval faster.
Note:
If you have mutiple disk controllers you can create filegroups in which data
is ponted towards one disk controller and index against other. This will
reduce the I/O and executes the query faster.
Thanks
Hari
MCDBA
"MikeB" <mbercu@.g1sg.com> wrote in message
news:D7E7356A-921E-4817-8DED-8B82ACFB7A81@.microsoft.com...
> Can a MS Sql table hold 5 million records
Subscribe to:
Posts (Atom)