I wish to execute a query like this:
SELECT name FROM Owner WHERE ownerID IN (@.ownerIDs)
Is there any way to accomplish that? I'm using ADO.NET to execute this..
Thanks,
Pablo
--
Sleep till the end, true soul and sweet!
Nothing comes to thee new or strange.
Sleep full of rest from head to feet;
Lie still, dry dust, secure of change.
-- Alfred Tennyson (1809-1892)
-- To J. S.
Pablo Montilla
www.odyssey.com.uyCheck out http://www.sommarskog.se/dynamic_sql.html#List
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Pablo Montilla" <melkor@.odyssey.com.uy> wrote in message
news:op.typkr1dkcj6shk@.chimera.odyssey.com.uy...
>I wish to execute a query like this:
> SELECT name FROM Owner WHERE ownerID IN (@.ownerIDs)
> Is there any way to accomplish that? I'm using ADO.NET to execute this..
> Thanks,
> Pablo
> --
>
> Sleep till the end, true soul and sweet!
> Nothing comes to thee new or strange.
> Sleep full of rest from head to feet;
> Lie still, dry dust, secure of change.
> -- Alfred Tennyson (1809-1892)
> -- To J. S.
> Pablo Montilla
> www.odyssey.com.uy|||Pablo,
I think Tibor meant:
Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
AMB
"Pablo Montilla" wrote:
> I wish to execute a query like this:
> SELECT name FROM Owner WHERE ownerID IN (@.ownerIDs)
> Is there any way to accomplish that? I'm using ADO.NET to execute this..
> Thanks,
> Pablo
> --
>
> Sleep till the end, true soul and sweet!
> Nothing comes to thee new or strange.
> Sleep full of rest from head to feet;
> Lie still, dry dust, secure of change.
> -- Alfred Tennyson (1809-1892)
> -- To J. S.
> Pablo Montilla
> www.odyssey.com.uy
>|||On Sat, 15 Sep 2007 20:22:00 -0300, Alejandro Mesa
<AlejandroMesa@.discussions.microsoft.com> wrote:
> Pablo,
> I think Tibor meant:
> Arrays and Lists in SQL Server
> http://www.sommarskog.se/arrays-in-sql.html
> AMB
>
Many thanks for the links!
Regards,
Pablo
--
Don't vote. The government will get in.
-- Anarchist's Slogan
Pablo Montilla
www.odyssey.com.uy
2012年3月22日星期四
2012年3月20日星期二
Any way to query EXECUTE perms on stored procs?
I've recently been tasked with duplicating the permissions from one
account to another. We have a development, system test, and production
SQL Server, and approximately 35 databases in each. We use a fine
level of control on this particular account because it's what the
applications use to log in.
I've granted datareader/datawriter to the new account for all the dbs.
Each db, however, has a ton of stored procedures. Not all of them are
executable by the original account. I found a script that grants
access to all user stored procs, which is ok and will save me a few
days worth of point-and-click - but I'd be a happier DBA if I could
find a way to query the execute permissions on the stored procs to copy
them to the new account.
Any ideas? Below is the script I've used so far on the dev server.
SELECT 'GRANT EXECUTE ON ' + NAME + ' TO MyLogin' -- Replace MyLogin
with the name of your new Login
FROM SYSOBJECTS
WHERE TYPE = 'P'
AND LEFT(NAME,2) <> 'sp' -- system procs
AND LEFT(NAME,2) <> 'dt' -- VSS procs
Thanks,
Bill
A.K.A. PSPDBAThe PERMISSIONS system function can help you identify permission for the
current user:
http://msdn.microsoft.com/library/d...br />
6f78.asp
You could use it in combination with the SETUSER function:
http://msdn.microsoft.com/library/d...br />
6f78.asp
ML
http://milambda.blogspot.com/|||Ok, the sp_helprotect gives me back a list of the execute permissions
for the first user. I don't see how SETUSER is going to help me..|||PSPDBA (williambr@.state.pa.us) writes:
> Ok, the sp_helprotect gives me back a list of the execute permissions
> for the first user. I don't see how SETUSER is going to help me..
I guess the idea is:
SETUSER 'accountunderinvestigation'
go
SELECT name
FROM sysobjects
WHERE xtype = 'P'
AND permissions(id) & 32 = 1
go
SETUSER
That would list all the procedures that the account has permissions to
execute.
I should not that this solution contains three elements that are
deprecated in SQL 2005:
* The SETUSER command (Use EXECUTE AS instead)
* sysobjects (use sys.procedures instead)
* permissions(). (Use fn_my_permissions instead).
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
account to another. We have a development, system test, and production
SQL Server, and approximately 35 databases in each. We use a fine
level of control on this particular account because it's what the
applications use to log in.
I've granted datareader/datawriter to the new account for all the dbs.
Each db, however, has a ton of stored procedures. Not all of them are
executable by the original account. I found a script that grants
access to all user stored procs, which is ok and will save me a few
days worth of point-and-click - but I'd be a happier DBA if I could
find a way to query the execute permissions on the stored procs to copy
them to the new account.
Any ideas? Below is the script I've used so far on the dev server.
SELECT 'GRANT EXECUTE ON ' + NAME + ' TO MyLogin' -- Replace MyLogin
with the name of your new Login
FROM SYSOBJECTS
WHERE TYPE = 'P'
AND LEFT(NAME,2) <> 'sp' -- system procs
AND LEFT(NAME,2) <> 'dt' -- VSS procs
Thanks,
Bill
A.K.A. PSPDBAThe PERMISSIONS system function can help you identify permission for the
current user:
http://msdn.microsoft.com/library/d...br />
6f78.asp
You could use it in combination with the SETUSER function:
http://msdn.microsoft.com/library/d...br />
6f78.asp
ML
http://milambda.blogspot.com/|||Ok, the sp_helprotect gives me back a list of the execute permissions
for the first user. I don't see how SETUSER is going to help me..|||PSPDBA (williambr@.state.pa.us) writes:
> Ok, the sp_helprotect gives me back a list of the execute permissions
> for the first user. I don't see how SETUSER is going to help me..
I guess the idea is:
SETUSER 'accountunderinvestigation'
go
SELECT name
FROM sysobjects
WHERE xtype = 'P'
AND permissions(id) & 32 = 1
go
SETUSER
That would list all the procedures that the account has permissions to
execute.
I should not that this solution contains three elements that are
deprecated in SQL 2005:
* The SETUSER command (Use EXECUTE AS instead)
* sysobjects (use sys.procedures instead)
* permissions(). (Use fn_my_permissions instead).
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
2012年3月11日星期日
Any there nicer sentence?
If I execute like this
update tcoba
set jml=5,total=jml*5000
So value of field "total" is null. In fact I wish to be its value become
25000.
may be, Any there nicer sentence to solve problem? If can, just one
statement. But not like it:
update tcoba
set jml=5,total=5*5000Since you know that you are setting jml to 5, why can't you use 5 instead of
jml while setting the value for total?
"Use total=5*5000" instead of "total=jml*5000"
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:e5qYjv2lFHA.576@.TK2MSFTNGP15.phx.gbl...
If I execute like this
update tcoba
set jml=5,total=jml*5000
So value of field "total" is null. In fact I wish to be its value become
25000.
may be, Any there nicer sentence to solve problem? If can, just one
statement. But not like it:
update tcoba
set jml=5,total=5*5000|||In the query below, [total] will be multiplied by the original value of
[jml], not the new value of 5. One option is to set a variable to the value
of 5. For example:
declare @.x as int
set @.x = 5
update tcoba set jml=@.x,total=@.x*5000
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:e5qYjv2lFHA.576@.TK2MSFTNGP15.phx.gbl...
> If I execute like this
> update tcoba
> set jml=5,total=jml*5000
> So value of field "total" is null. In fact I wish to be its value become
> 25000.
> may be, Any there nicer sentence to solve problem? If can, just one
> statement. But not like it:
> update tcoba
> set jml=5,total=5*5000
>|||the result you are getting in your column "Total" is because your
column contains NULL initially, and though you have placed jml for
updation before the total, it won't actually update jml first and
update Total after it. But it is good example to know how Update query
works!|||The UPDATE assigns a whole row at a time; it does not work left to
right like a procedural language. Google some of my old posting about
the semantics of UPDATE.
update tcoba
set jml=5,total=jml*5000
So value of field "total" is null. In fact I wish to be its value become
25000.
may be, Any there nicer sentence to solve problem? If can, just one
statement. But not like it:
update tcoba
set jml=5,total=5*5000Since you know that you are setting jml to 5, why can't you use 5 instead of
jml while setting the value for total?
"Use total=5*5000" instead of "total=jml*5000"
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:e5qYjv2lFHA.576@.TK2MSFTNGP15.phx.gbl...
If I execute like this
update tcoba
set jml=5,total=jml*5000
So value of field "total" is null. In fact I wish to be its value become
25000.
may be, Any there nicer sentence to solve problem? If can, just one
statement. But not like it:
update tcoba
set jml=5,total=5*5000|||In the query below, [total] will be multiplied by the original value of
[jml], not the new value of 5. One option is to set a variable to the value
of 5. For example:
declare @.x as int
set @.x = 5
update tcoba set jml=@.x,total=@.x*5000
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:e5qYjv2lFHA.576@.TK2MSFTNGP15.phx.gbl...
> If I execute like this
> update tcoba
> set jml=5,total=jml*5000
> So value of field "total" is null. In fact I wish to be its value become
> 25000.
> may be, Any there nicer sentence to solve problem? If can, just one
> statement. But not like it:
> update tcoba
> set jml=5,total=5*5000
>|||the result you are getting in your column "Total" is because your
column contains NULL initially, and though you have placed jml for
updation before the total, it won't actually update jml first and
update Total after it. But it is good example to know how Update query
works!|||The UPDATE assigns a whole row at a time; it does not work left to
right like a procedural language. Google some of my old posting about
the semantics of UPDATE.
2012年2月23日星期四
Any inventive ways to DBREINDEX and minimize blocking
I would like to come up with a routine in which I could execute a DBREINDEX
without taking downtime in my application. The tables that I want to
REINDEX are pretty small (the reindexing should take no more than 10-15
seconds).
The problem is that if the table i want to REINDEX has an IX lock on the
table, then the SPID trying to runs the REINDEX waits with a Sch-M lock on
that table. The Sch-M lock in turn then blocks everyone else from obtaining
an IX lock. So if I had some busy process that was running for a long time
holding a IX lock (let's say 30 seconds), the reindex would be blocked for
up to 30 seconds. No big deal to me if the reindex spid gets blocked but
what I am concerned about it the blocking that it will in turn do to other
spids waiting for an IX lock.
I was hoping that I could define some process like:
WHILE 1=1
BEGIN
(If an Sch-M lock is available without actually taking it)
BEGIN
DBCC DBREINDEX table
RETURN
END
END
Any comments would be greatly appreciated.Why are you DBREINDEXing instead of INDEXDEFRAGing?
The latter is an online operation.
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:u163wdnbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> I would like to come up with a routine in which I could execute a
DBREINDEX
> without taking downtime in my application. The tables that I want to
> REINDEX are pretty small (the reindexing should take no more than 10-15
> seconds).
> The problem is that if the table i want to REINDEX has an IX lock on the
> table, then the SPID trying to runs the REINDEX waits with a Sch-M lock on
> that table. The Sch-M lock in turn then blocks everyone else from
obtaining
> an IX lock. So if I had some busy process that was running for a long
time
> holding a IX lock (let's say 30 seconds), the reindex would be blocked for
> up to 30 seconds. No big deal to me if the reindex spid gets blocked but
> what I am concerned about it the blocking that it will in turn do to other
> spids waiting for an IX lock.
> I was hoping that I could define some process like:
> WHILE 1=1
> BEGIN
> (If an Sch-M lock is available without actually taking it)
> BEGIN
> DBCC DBREINDEX table
> RETURN
> END
> END
> Any comments would be greatly appreciated.
>|||I've seen a few instances where INDEXDEFRAG does not bring the Scan Density
to 100% like the DBREINDEX does.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uvT5alnbEHA.3944@.tk2msftngp13.phx.gbl...
> Why are you DBREINDEXing instead of INDEXDEFRAGing?
> The latter is an online operation.
>
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:u163wdnbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> > I would like to come up with a routine in which I could execute a
> DBREINDEX
> > without taking downtime in my application. The tables that I want to
> > REINDEX are pretty small (the reindexing should take no more than 10-15
> > seconds).
> >
> > The problem is that if the table i want to REINDEX has an IX lock on the
> > table, then the SPID trying to runs the REINDEX waits with a Sch-M lock
on
> > that table. The Sch-M lock in turn then blocks everyone else from
> obtaining
> > an IX lock. So if I had some busy process that was running for a long
> time
> > holding a IX lock (let's say 30 seconds), the reindex would be blocked
for
> > up to 30 seconds. No big deal to me if the reindex spid gets blocked
but
> > what I am concerned about it the blocking that it will in turn do to
other
> > spids waiting for an IX lock.
> >
> > I was hoping that I could define some process like:
> > WHILE 1=1
> > BEGIN
> > (If an Sch-M lock is available without actually taking it)
> > BEGIN
> > DBCC DBREINDEX table
> > RETURN
> > END
> > END
> >
> > Any comments would be greatly appreciated.
> >
> >
>|||That's because it's not as thorough as the DBREINDEX. It's used in
situations where db's cannot be brought down (ie 24x7 shops). Among
the things it doesn't defrag are extent hops.
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O3DZ7znbEHA.2572@.tk2msftngp13.phx.gbl...
> I've seen a few instances where INDEXDEFRAG does not bring the Scan
Density
> to 100% like the DBREINDEX does.
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uvT5alnbEHA.3944@.tk2msftngp13.phx.gbl...
> > Why are you DBREINDEXing instead of INDEXDEFRAGing?
> >
> > The latter is an online operation.
> >
> >
> > "TJTODD" <tjtodd@.anonymous.com> wrote in message
> > news:u163wdnbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> > > I would like to come up with a routine in which I could execute a
> > DBREINDEX
> > > without taking downtime in my application. The tables that I want to
> > > REINDEX are pretty small (the reindexing should take no more than
10-15
> > > seconds).
> > >
> > > The problem is that if the table i want to REINDEX has an IX lock on
the
> > > table, then the SPID trying to runs the REINDEX waits with a Sch-M
lock
> on
> > > that table. The Sch-M lock in turn then blocks everyone else from
> > obtaining
> > > an IX lock. So if I had some busy process that was running for a long
> > time
> > > holding a IX lock (let's say 30 seconds), the reindex would be blocked
> for
> > > up to 30 seconds. No big deal to me if the reindex spid gets blocked
> but
> > > what I am concerned about it the blocking that it will in turn do to
> other
> > > spids waiting for an IX lock.
> > >
> > > I was hoping that I could define some process like:
> > > WHILE 1=1
> > > BEGIN
> > > (If an Sch-M lock is available without actually taking it)
> > > BEGIN
> > > DBCC DBREINDEX table
> > > RETURN
> > > END
> > > END
> > >
> > > Any comments would be greatly appreciated.
> > >
> > >
> >
> >
>|||"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O3DZ7znbEHA.2572@.tk2msftngp13.phx.gbl...
> I've seen a few instances where INDEXDEFRAG does not bring the Scan
Density
> to 100% like the DBREINDEX does.
But does that really matter? You have a requirement of staying online,
obviously, so you kind of have to take whatever you can get... How much of a
performance impact, especially given your small tables, do you expect from
90% or 95% density vs. 100%?|||Scan Density is mostly irrelevant, as explained in Books OnLine. Please read
the whitepaper below which gives all the details.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O3DZ7znbEHA.2572@.tk2msftngp13.phx.gbl...
> I've seen a few instances where INDEXDEFRAG does not bring the Scan
Density
> to 100% like the DBREINDEX does.
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uvT5alnbEHA.3944@.tk2msftngp13.phx.gbl...
> > Why are you DBREINDEXing instead of INDEXDEFRAGing?
> >
> > The latter is an online operation.
> >
> >
> > "TJTODD" <tjtodd@.anonymous.com> wrote in message
> > news:u163wdnbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> > > I would like to come up with a routine in which I could execute a
> > DBREINDEX
> > > without taking downtime in my application. The tables that I want to
> > > REINDEX are pretty small (the reindexing should take no more than
10-15
> > > seconds).
> > >
> > > The problem is that if the table i want to REINDEX has an IX lock on
the
> > > table, then the SPID trying to runs the REINDEX waits with a Sch-M
lock
> on
> > > that table. The Sch-M lock in turn then blocks everyone else from
> > obtaining
> > > an IX lock. So if I had some busy process that was running for a long
> > time
> > > holding a IX lock (let's say 30 seconds), the reindex would be blocked
> for
> > > up to 30 seconds. No big deal to me if the reindex spid gets blocked
> but
> > > what I am concerned about it the blocking that it will in turn do to
> other
> > > spids waiting for an IX lock.
> > >
> > > I was hoping that I could define some process like:
> > > WHILE 1=1
> > > BEGIN
> > > (If an Sch-M lock is available without actually taking it)
> > > BEGIN
> > > DBCC DBREINDEX table
> > > RETURN
> > > END
> > > END
> > >
> > > Any comments would be greatly appreciated.
> > >
> > >
> >
> >
>|||Scan density should not be the factory you are looking at in most cases
anyway. Logical frag is the first thing you should seek. If you have more
than 1 file in your file group extent switching and all that goes with it is
not an accurate indicator in showcontig and should not be used for
determining fragmentation status in general. Bottom line is DBREINDEX is an
entirely off line operational and if you can't afford that you have to use
INDEXDEFRAG until Yukon comes out. This is a must read:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Andrew J. Kelly SQL MVP
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O3DZ7znbEHA.2572@.tk2msftngp13.phx.gbl...
> I've seen a few instances where INDEXDEFRAG does not bring the Scan
Density
> to 100% like the DBREINDEX does.
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uvT5alnbEHA.3944@.tk2msftngp13.phx.gbl...
> > Why are you DBREINDEXing instead of INDEXDEFRAGing?
> >
> > The latter is an online operation.
> >
> >
> > "TJTODD" <tjtodd@.anonymous.com> wrote in message
> > news:u163wdnbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> > > I would like to come up with a routine in which I could execute a
> > DBREINDEX
> > > without taking downtime in my application. The tables that I want to
> > > REINDEX are pretty small (the reindexing should take no more than
10-15
> > > seconds).
> > >
> > > The problem is that if the table i want to REINDEX has an IX lock on
the
> > > table, then the SPID trying to runs the REINDEX waits with a Sch-M
lock
> on
> > > that table. The Sch-M lock in turn then blocks everyone else from
> > obtaining
> > > an IX lock. So if I had some busy process that was running for a long
> > time
> > > holding a IX lock (let's say 30 seconds), the reindex would be blocked
> for
> > > up to 30 seconds. No big deal to me if the reindex spid gets blocked
> but
> > > what I am concerned about it the blocking that it will in turn do to
> other
> > > spids waiting for an IX lock.
> > >
> > > I was hoping that I could define some process like:
> > > WHILE 1=1
> > > BEGIN
> > > (If an Sch-M lock is available without actually taking it)
> > > BEGIN
> > > DBCC DBREINDEX table
> > > RETURN
> > > END
> > > END
> > >
> > > Any comments would be greatly appreciated.
> > >
> > >
> >
> >
>
without taking downtime in my application. The tables that I want to
REINDEX are pretty small (the reindexing should take no more than 10-15
seconds).
The problem is that if the table i want to REINDEX has an IX lock on the
table, then the SPID trying to runs the REINDEX waits with a Sch-M lock on
that table. The Sch-M lock in turn then blocks everyone else from obtaining
an IX lock. So if I had some busy process that was running for a long time
holding a IX lock (let's say 30 seconds), the reindex would be blocked for
up to 30 seconds. No big deal to me if the reindex spid gets blocked but
what I am concerned about it the blocking that it will in turn do to other
spids waiting for an IX lock.
I was hoping that I could define some process like:
WHILE 1=1
BEGIN
(If an Sch-M lock is available without actually taking it)
BEGIN
DBCC DBREINDEX table
RETURN
END
END
Any comments would be greatly appreciated.Why are you DBREINDEXing instead of INDEXDEFRAGing?
The latter is an online operation.
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:u163wdnbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> I would like to come up with a routine in which I could execute a
DBREINDEX
> without taking downtime in my application. The tables that I want to
> REINDEX are pretty small (the reindexing should take no more than 10-15
> seconds).
> The problem is that if the table i want to REINDEX has an IX lock on the
> table, then the SPID trying to runs the REINDEX waits with a Sch-M lock on
> that table. The Sch-M lock in turn then blocks everyone else from
obtaining
> an IX lock. So if I had some busy process that was running for a long
time
> holding a IX lock (let's say 30 seconds), the reindex would be blocked for
> up to 30 seconds. No big deal to me if the reindex spid gets blocked but
> what I am concerned about it the blocking that it will in turn do to other
> spids waiting for an IX lock.
> I was hoping that I could define some process like:
> WHILE 1=1
> BEGIN
> (If an Sch-M lock is available without actually taking it)
> BEGIN
> DBCC DBREINDEX table
> RETURN
> END
> END
> Any comments would be greatly appreciated.
>|||I've seen a few instances where INDEXDEFRAG does not bring the Scan Density
to 100% like the DBREINDEX does.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uvT5alnbEHA.3944@.tk2msftngp13.phx.gbl...
> Why are you DBREINDEXing instead of INDEXDEFRAGing?
> The latter is an online operation.
>
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:u163wdnbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> > I would like to come up with a routine in which I could execute a
> DBREINDEX
> > without taking downtime in my application. The tables that I want to
> > REINDEX are pretty small (the reindexing should take no more than 10-15
> > seconds).
> >
> > The problem is that if the table i want to REINDEX has an IX lock on the
> > table, then the SPID trying to runs the REINDEX waits with a Sch-M lock
on
> > that table. The Sch-M lock in turn then blocks everyone else from
> obtaining
> > an IX lock. So if I had some busy process that was running for a long
> time
> > holding a IX lock (let's say 30 seconds), the reindex would be blocked
for
> > up to 30 seconds. No big deal to me if the reindex spid gets blocked
but
> > what I am concerned about it the blocking that it will in turn do to
other
> > spids waiting for an IX lock.
> >
> > I was hoping that I could define some process like:
> > WHILE 1=1
> > BEGIN
> > (If an Sch-M lock is available without actually taking it)
> > BEGIN
> > DBCC DBREINDEX table
> > RETURN
> > END
> > END
> >
> > Any comments would be greatly appreciated.
> >
> >
>|||That's because it's not as thorough as the DBREINDEX. It's used in
situations where db's cannot be brought down (ie 24x7 shops). Among
the things it doesn't defrag are extent hops.
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O3DZ7znbEHA.2572@.tk2msftngp13.phx.gbl...
> I've seen a few instances where INDEXDEFRAG does not bring the Scan
Density
> to 100% like the DBREINDEX does.
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uvT5alnbEHA.3944@.tk2msftngp13.phx.gbl...
> > Why are you DBREINDEXing instead of INDEXDEFRAGing?
> >
> > The latter is an online operation.
> >
> >
> > "TJTODD" <tjtodd@.anonymous.com> wrote in message
> > news:u163wdnbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> > > I would like to come up with a routine in which I could execute a
> > DBREINDEX
> > > without taking downtime in my application. The tables that I want to
> > > REINDEX are pretty small (the reindexing should take no more than
10-15
> > > seconds).
> > >
> > > The problem is that if the table i want to REINDEX has an IX lock on
the
> > > table, then the SPID trying to runs the REINDEX waits with a Sch-M
lock
> on
> > > that table. The Sch-M lock in turn then blocks everyone else from
> > obtaining
> > > an IX lock. So if I had some busy process that was running for a long
> > time
> > > holding a IX lock (let's say 30 seconds), the reindex would be blocked
> for
> > > up to 30 seconds. No big deal to me if the reindex spid gets blocked
> but
> > > what I am concerned about it the blocking that it will in turn do to
> other
> > > spids waiting for an IX lock.
> > >
> > > I was hoping that I could define some process like:
> > > WHILE 1=1
> > > BEGIN
> > > (If an Sch-M lock is available without actually taking it)
> > > BEGIN
> > > DBCC DBREINDEX table
> > > RETURN
> > > END
> > > END
> > >
> > > Any comments would be greatly appreciated.
> > >
> > >
> >
> >
>|||"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O3DZ7znbEHA.2572@.tk2msftngp13.phx.gbl...
> I've seen a few instances where INDEXDEFRAG does not bring the Scan
Density
> to 100% like the DBREINDEX does.
But does that really matter? You have a requirement of staying online,
obviously, so you kind of have to take whatever you can get... How much of a
performance impact, especially given your small tables, do you expect from
90% or 95% density vs. 100%?|||Scan Density is mostly irrelevant, as explained in Books OnLine. Please read
the whitepaper below which gives all the details.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O3DZ7znbEHA.2572@.tk2msftngp13.phx.gbl...
> I've seen a few instances where INDEXDEFRAG does not bring the Scan
Density
> to 100% like the DBREINDEX does.
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uvT5alnbEHA.3944@.tk2msftngp13.phx.gbl...
> > Why are you DBREINDEXing instead of INDEXDEFRAGing?
> >
> > The latter is an online operation.
> >
> >
> > "TJTODD" <tjtodd@.anonymous.com> wrote in message
> > news:u163wdnbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> > > I would like to come up with a routine in which I could execute a
> > DBREINDEX
> > > without taking downtime in my application. The tables that I want to
> > > REINDEX are pretty small (the reindexing should take no more than
10-15
> > > seconds).
> > >
> > > The problem is that if the table i want to REINDEX has an IX lock on
the
> > > table, then the SPID trying to runs the REINDEX waits with a Sch-M
lock
> on
> > > that table. The Sch-M lock in turn then blocks everyone else from
> > obtaining
> > > an IX lock. So if I had some busy process that was running for a long
> > time
> > > holding a IX lock (let's say 30 seconds), the reindex would be blocked
> for
> > > up to 30 seconds. No big deal to me if the reindex spid gets blocked
> but
> > > what I am concerned about it the blocking that it will in turn do to
> other
> > > spids waiting for an IX lock.
> > >
> > > I was hoping that I could define some process like:
> > > WHILE 1=1
> > > BEGIN
> > > (If an Sch-M lock is available without actually taking it)
> > > BEGIN
> > > DBCC DBREINDEX table
> > > RETURN
> > > END
> > > END
> > >
> > > Any comments would be greatly appreciated.
> > >
> > >
> >
> >
>|||Scan density should not be the factory you are looking at in most cases
anyway. Logical frag is the first thing you should seek. If you have more
than 1 file in your file group extent switching and all that goes with it is
not an accurate indicator in showcontig and should not be used for
determining fragmentation status in general. Bottom line is DBREINDEX is an
entirely off line operational and if you can't afford that you have to use
INDEXDEFRAG until Yukon comes out. This is a must read:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Andrew J. Kelly SQL MVP
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O3DZ7znbEHA.2572@.tk2msftngp13.phx.gbl...
> I've seen a few instances where INDEXDEFRAG does not bring the Scan
Density
> to 100% like the DBREINDEX does.
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uvT5alnbEHA.3944@.tk2msftngp13.phx.gbl...
> > Why are you DBREINDEXing instead of INDEXDEFRAGing?
> >
> > The latter is an online operation.
> >
> >
> > "TJTODD" <tjtodd@.anonymous.com> wrote in message
> > news:u163wdnbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> > > I would like to come up with a routine in which I could execute a
> > DBREINDEX
> > > without taking downtime in my application. The tables that I want to
> > > REINDEX are pretty small (the reindexing should take no more than
10-15
> > > seconds).
> > >
> > > The problem is that if the table i want to REINDEX has an IX lock on
the
> > > table, then the SPID trying to runs the REINDEX waits with a Sch-M
lock
> on
> > > that table. The Sch-M lock in turn then blocks everyone else from
> > obtaining
> > > an IX lock. So if I had some busy process that was running for a long
> > time
> > > holding a IX lock (let's say 30 seconds), the reindex would be blocked
> for
> > > up to 30 seconds. No big deal to me if the reindex spid gets blocked
> but
> > > what I am concerned about it the blocking that it will in turn do to
> other
> > > spids waiting for an IX lock.
> > >
> > > I was hoping that I could define some process like:
> > > WHILE 1=1
> > > BEGIN
> > > (If an Sch-M lock is available without actually taking it)
> > > BEGIN
> > > DBCC DBREINDEX table
> > > RETURN
> > > END
> > > END
> > >
> > > Any comments would be greatly appreciated.
> > >
> > >
> >
> >
>
Any inventive ways to DBREINDEX and minimize blocking
I would like to come up with a routine in which I could execute a DBREINDEX
without taking downtime in my application. The tables that I want to
REINDEX are pretty small (the reindexing should take no more than 10-15
seconds).
The problem is that if the table i want to REINDEX has an IX lock on the
table, then the SPID trying to runs the REINDEX waits with a Sch-M lock on
that table. The Sch-M lock in turn then blocks everyone else from obtaining
an IX lock. So if I had some busy process that was running for a long time
holding a IX lock (let's say 30 seconds), the reindex would be blocked for
up to 30 seconds. No big deal to me if the reindex spid gets blocked but
what I am concerned about it the blocking that it will in turn do to other
spids waiting for an IX lock.
I was hoping that I could define some process like:
WHILE 1=1
BEGIN
(If an Sch-M lock is available without actually taking it)
BEGIN
DBCC DBREINDEX table
RETURN
END
END
Any comments would be greatly appreciated.
Why are you DBREINDEXing instead of INDEXDEFRAGing?
The latter is an online operation.
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:u163wdnbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> I would like to come up with a routine in which I could execute a
DBREINDEX
> without taking downtime in my application. The tables that I want to
> REINDEX are pretty small (the reindexing should take no more than 10-15
> seconds).
> The problem is that if the table i want to REINDEX has an IX lock on the
> table, then the SPID trying to runs the REINDEX waits with a Sch-M lock on
> that table. The Sch-M lock in turn then blocks everyone else from
obtaining
> an IX lock. So if I had some busy process that was running for a long
time
> holding a IX lock (let's say 30 seconds), the reindex would be blocked for
> up to 30 seconds. No big deal to me if the reindex spid gets blocked but
> what I am concerned about it the blocking that it will in turn do to other
> spids waiting for an IX lock.
> I was hoping that I could define some process like:
> WHILE 1=1
> BEGIN
> (If an Sch-M lock is available without actually taking it)
> BEGIN
> DBCC DBREINDEX table
> RETURN
> END
> END
> Any comments would be greatly appreciated.
>
|||I've seen a few instances where INDEXDEFRAG does not bring the Scan Density
to 100% like the DBREINDEX does.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uvT5alnbEHA.3944@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Why are you DBREINDEXing instead of INDEXDEFRAGing?
> The latter is an online operation.
>
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:u163wdnbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> DBREINDEX
on[vbcol=seagreen]
> obtaining
> time
for[vbcol=seagreen]
but[vbcol=seagreen]
other
>
|||That's because it's not as thorough as the DBREINDEX. It's used in
situations where db's cannot be brought down (ie 24x7 shops). Among
the things it doesn't defrag are extent hops.
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O3DZ7znbEHA.2572@.tk2msftngp13.phx.gbl...
> I've seen a few instances where INDEXDEFRAG does not bring the Scan
Density[vbcol=seagreen]
> to 100% like the DBREINDEX does.
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uvT5alnbEHA.3944@.tk2msftngp13.phx.gbl...
10-15[vbcol=seagreen]
the[vbcol=seagreen]
lock
> on
> for
> but
> other
>
|||"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O3DZ7znbEHA.2572@.tk2msftngp13.phx.gbl...
> I've seen a few instances where INDEXDEFRAG does not bring the Scan
Density
> to 100% like the DBREINDEX does.
But does that really matter? You have a requirement of staying online,
obviously, so you kind of have to take whatever you can get... How much of a
performance impact, especially given your small tables, do you expect from
90% or 95% density vs. 100%?
|||Scan Density is mostly irrelevant, as explained in Books OnLine. Please read
the whitepaper below which gives all the details.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O3DZ7znbEHA.2572@.tk2msftngp13.phx.gbl...
> I've seen a few instances where INDEXDEFRAG does not bring the Scan
Density[vbcol=seagreen]
> to 100% like the DBREINDEX does.
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uvT5alnbEHA.3944@.tk2msftngp13.phx.gbl...
10-15[vbcol=seagreen]
the[vbcol=seagreen]
lock
> on
> for
> but
> other
>
|||Scan density should not be the factory you are looking at in most cases
anyway. Logical frag is the first thing you should seek. If you have more
than 1 file in your file group extent switching and all that goes with it is
not an accurate indicator in showcontig and should not be used for
determining fragmentation status in general. Bottom line is DBREINDEX is an
entirely off line operational and if you can't afford that you have to use
INDEXDEFRAG until Yukon comes out. This is a must read:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Andrew J. Kelly SQL MVP
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O3DZ7znbEHA.2572@.tk2msftngp13.phx.gbl...
> I've seen a few instances where INDEXDEFRAG does not bring the Scan
Density[vbcol=seagreen]
> to 100% like the DBREINDEX does.
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uvT5alnbEHA.3944@.tk2msftngp13.phx.gbl...
10-15[vbcol=seagreen]
the[vbcol=seagreen]
lock
> on
> for
> but
> other
>
without taking downtime in my application. The tables that I want to
REINDEX are pretty small (the reindexing should take no more than 10-15
seconds).
The problem is that if the table i want to REINDEX has an IX lock on the
table, then the SPID trying to runs the REINDEX waits with a Sch-M lock on
that table. The Sch-M lock in turn then blocks everyone else from obtaining
an IX lock. So if I had some busy process that was running for a long time
holding a IX lock (let's say 30 seconds), the reindex would be blocked for
up to 30 seconds. No big deal to me if the reindex spid gets blocked but
what I am concerned about it the blocking that it will in turn do to other
spids waiting for an IX lock.
I was hoping that I could define some process like:
WHILE 1=1
BEGIN
(If an Sch-M lock is available without actually taking it)
BEGIN
DBCC DBREINDEX table
RETURN
END
END
Any comments would be greatly appreciated.
Why are you DBREINDEXing instead of INDEXDEFRAGing?
The latter is an online operation.
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:u163wdnbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> I would like to come up with a routine in which I could execute a
DBREINDEX
> without taking downtime in my application. The tables that I want to
> REINDEX are pretty small (the reindexing should take no more than 10-15
> seconds).
> The problem is that if the table i want to REINDEX has an IX lock on the
> table, then the SPID trying to runs the REINDEX waits with a Sch-M lock on
> that table. The Sch-M lock in turn then blocks everyone else from
obtaining
> an IX lock. So if I had some busy process that was running for a long
time
> holding a IX lock (let's say 30 seconds), the reindex would be blocked for
> up to 30 seconds. No big deal to me if the reindex spid gets blocked but
> what I am concerned about it the blocking that it will in turn do to other
> spids waiting for an IX lock.
> I was hoping that I could define some process like:
> WHILE 1=1
> BEGIN
> (If an Sch-M lock is available without actually taking it)
> BEGIN
> DBCC DBREINDEX table
> RETURN
> END
> END
> Any comments would be greatly appreciated.
>
|||I've seen a few instances where INDEXDEFRAG does not bring the Scan Density
to 100% like the DBREINDEX does.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uvT5alnbEHA.3944@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Why are you DBREINDEXing instead of INDEXDEFRAGing?
> The latter is an online operation.
>
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:u163wdnbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> DBREINDEX
on[vbcol=seagreen]
> obtaining
> time
for[vbcol=seagreen]
but[vbcol=seagreen]
other
>
|||That's because it's not as thorough as the DBREINDEX. It's used in
situations where db's cannot be brought down (ie 24x7 shops). Among
the things it doesn't defrag are extent hops.
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O3DZ7znbEHA.2572@.tk2msftngp13.phx.gbl...
> I've seen a few instances where INDEXDEFRAG does not bring the Scan
Density[vbcol=seagreen]
> to 100% like the DBREINDEX does.
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uvT5alnbEHA.3944@.tk2msftngp13.phx.gbl...
10-15[vbcol=seagreen]
the[vbcol=seagreen]
lock
> on
> for
> but
> other
>
|||"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O3DZ7znbEHA.2572@.tk2msftngp13.phx.gbl...
> I've seen a few instances where INDEXDEFRAG does not bring the Scan
Density
> to 100% like the DBREINDEX does.
But does that really matter? You have a requirement of staying online,
obviously, so you kind of have to take whatever you can get... How much of a
performance impact, especially given your small tables, do you expect from
90% or 95% density vs. 100%?
|||Scan Density is mostly irrelevant, as explained in Books OnLine. Please read
the whitepaper below which gives all the details.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O3DZ7znbEHA.2572@.tk2msftngp13.phx.gbl...
> I've seen a few instances where INDEXDEFRAG does not bring the Scan
Density[vbcol=seagreen]
> to 100% like the DBREINDEX does.
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uvT5alnbEHA.3944@.tk2msftngp13.phx.gbl...
10-15[vbcol=seagreen]
the[vbcol=seagreen]
lock
> on
> for
> but
> other
>
|||Scan density should not be the factory you are looking at in most cases
anyway. Logical frag is the first thing you should seek. If you have more
than 1 file in your file group extent switching and all that goes with it is
not an accurate indicator in showcontig and should not be used for
determining fragmentation status in general. Bottom line is DBREINDEX is an
entirely off line operational and if you can't afford that you have to use
INDEXDEFRAG until Yukon comes out. This is a must read:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Andrew J. Kelly SQL MVP
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O3DZ7znbEHA.2572@.tk2msftngp13.phx.gbl...
> I've seen a few instances where INDEXDEFRAG does not bring the Scan
Density[vbcol=seagreen]
> to 100% like the DBREINDEX does.
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uvT5alnbEHA.3944@.tk2msftngp13.phx.gbl...
10-15[vbcol=seagreen]
the[vbcol=seagreen]
lock
> on
> for
> but
> other
>
Any inventive ways to DBREINDEX and minimize blocking
I would like to come up with a routine in which I could execute a DBREINDEX
without taking downtime in my application. The tables that I want to
REINDEX are pretty small (the reindexing should take no more than 10-15
seconds).
The problem is that if the table i want to REINDEX has an IX lock on the
table, then the SPID trying to runs the REINDEX waits with a Sch-M lock on
that table. The Sch-M lock in turn then blocks everyone else from obtaining
an IX lock. So if I had some busy process that was running for a long time
holding a IX lock (let's say 30 seconds), the reindex would be blocked for
up to 30 seconds. No big deal to me if the reindex spid gets blocked but
what I am concerned about it the blocking that it will in turn do to other
spids waiting for an IX lock.
I was hoping that I could define some process like:
WHILE 1=1
BEGIN
(If an Sch-M lock is available without actually taking it)
BEGIN
DBCC DBREINDEX table
RETURN
END
END
Any comments would be greatly appreciated.Why are you DBREINDEXing instead of INDEXDEFRAGing?
The latter is an online operation.
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:u163wdnbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> I would like to come up with a routine in which I could execute a
DBREINDEX
> without taking downtime in my application. The tables that I want to
> REINDEX are pretty small (the reindexing should take no more than 10-15
> seconds).
> The problem is that if the table i want to REINDEX has an IX lock on the
> table, then the SPID trying to runs the REINDEX waits with a Sch-M lock on
> that table. The Sch-M lock in turn then blocks everyone else from
obtaining
> an IX lock. So if I had some busy process that was running for a long
time
> holding a IX lock (let's say 30 seconds), the reindex would be blocked for
> up to 30 seconds. No big deal to me if the reindex spid gets blocked but
> what I am concerned about it the blocking that it will in turn do to other
> spids waiting for an IX lock.
> I was hoping that I could define some process like:
> WHILE 1=1
> BEGIN
> (If an Sch-M lock is available without actually taking it)
> BEGIN
> DBCC DBREINDEX table
> RETURN
> END
> END
> Any comments would be greatly appreciated.
>|||I've seen a few instances where INDEXDEFRAG does not bring the Scan Density
to 100% like the DBREINDEX does.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uvT5alnbEHA.3944@.tk2msftngp13.phx.gbl...
> Why are you DBREINDEXing instead of INDEXDEFRAGing?
> The latter is an online operation.
>
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:u163wdnbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> DBREINDEX
on[vbcol=seagreen]
> obtaining
> time
for[vbcol=seagreen]
but[vbcol=seagreen]
other[vbcol=seagreen]
>|||That's because it's not as thorough as the DBREINDEX. It's used in
situations where db's cannot be brought down (ie 24x7 shops). Among
the things it doesn't defrag are extent hops.
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O3DZ7znbEHA.2572@.tk2msftngp13.phx.gbl...
> I've seen a few instances where INDEXDEFRAG does not bring the Scan
Density
> to 100% like the DBREINDEX does.
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uvT5alnbEHA.3944@.tk2msftngp13.phx.gbl...
10-15[vbcol=seagreen]
the[vbcol=seagreen]
lock[vbcol=seagreen]
> on
> for
> but
> other
>|||"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O3DZ7znbEHA.2572@.tk2msftngp13.phx.gbl...
> I've seen a few instances where INDEXDEFRAG does not bring the Scan
Density
> to 100% like the DBREINDEX does.
But does that really matter? You have a requirement of staying online,
obviously, so you kind of have to take whatever you can get... How much of a
performance impact, especially given your small tables, do you expect from
90% or 95% density vs. 100%?|||Scan Density is mostly irrelevant, as explained in Books OnLine. Please read
the whitepaper below which gives all the details.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O3DZ7znbEHA.2572@.tk2msftngp13.phx.gbl...
> I've seen a few instances where INDEXDEFRAG does not bring the Scan
Density
> to 100% like the DBREINDEX does.
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uvT5alnbEHA.3944@.tk2msftngp13.phx.gbl...
10-15[vbcol=seagreen]
the[vbcol=seagreen]
lock[vbcol=seagreen]
> on
> for
> but
> other
>|||Scan density should not be the factory you are looking at in most cases
anyway. Logical frag is the first thing you should seek. If you have more
than 1 file in your file group extent switching and all that goes with it is
not an accurate indicator in showcontig and should not be used for
determining fragmentation status in general. Bottom line is DBREINDEX is an
entirely off line operational and if you can't afford that you have to use
INDEXDEFRAG until Yukon comes out. This is a must read:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Andrew J. Kelly SQL MVP
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O3DZ7znbEHA.2572@.tk2msftngp13.phx.gbl...
> I've seen a few instances where INDEXDEFRAG does not bring the Scan
Density
> to 100% like the DBREINDEX does.
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uvT5alnbEHA.3944@.tk2msftngp13.phx.gbl...
10-15[vbcol=seagreen]
the[vbcol=seagreen]
lock[vbcol=seagreen]
> on
> for
> but
> other
>
without taking downtime in my application. The tables that I want to
REINDEX are pretty small (the reindexing should take no more than 10-15
seconds).
The problem is that if the table i want to REINDEX has an IX lock on the
table, then the SPID trying to runs the REINDEX waits with a Sch-M lock on
that table. The Sch-M lock in turn then blocks everyone else from obtaining
an IX lock. So if I had some busy process that was running for a long time
holding a IX lock (let's say 30 seconds), the reindex would be blocked for
up to 30 seconds. No big deal to me if the reindex spid gets blocked but
what I am concerned about it the blocking that it will in turn do to other
spids waiting for an IX lock.
I was hoping that I could define some process like:
WHILE 1=1
BEGIN
(If an Sch-M lock is available without actually taking it)
BEGIN
DBCC DBREINDEX table
RETURN
END
END
Any comments would be greatly appreciated.Why are you DBREINDEXing instead of INDEXDEFRAGing?
The latter is an online operation.
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:u163wdnbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> I would like to come up with a routine in which I could execute a
DBREINDEX
> without taking downtime in my application. The tables that I want to
> REINDEX are pretty small (the reindexing should take no more than 10-15
> seconds).
> The problem is that if the table i want to REINDEX has an IX lock on the
> table, then the SPID trying to runs the REINDEX waits with a Sch-M lock on
> that table. The Sch-M lock in turn then blocks everyone else from
obtaining
> an IX lock. So if I had some busy process that was running for a long
time
> holding a IX lock (let's say 30 seconds), the reindex would be blocked for
> up to 30 seconds. No big deal to me if the reindex spid gets blocked but
> what I am concerned about it the blocking that it will in turn do to other
> spids waiting for an IX lock.
> I was hoping that I could define some process like:
> WHILE 1=1
> BEGIN
> (If an Sch-M lock is available without actually taking it)
> BEGIN
> DBCC DBREINDEX table
> RETURN
> END
> END
> Any comments would be greatly appreciated.
>|||I've seen a few instances where INDEXDEFRAG does not bring the Scan Density
to 100% like the DBREINDEX does.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uvT5alnbEHA.3944@.tk2msftngp13.phx.gbl...
> Why are you DBREINDEXing instead of INDEXDEFRAGing?
> The latter is an online operation.
>
> "TJTODD" <tjtodd@.anonymous.com> wrote in message
> news:u163wdnbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> DBREINDEX
on[vbcol=seagreen]
> obtaining
> time
for[vbcol=seagreen]
but[vbcol=seagreen]
other[vbcol=seagreen]
>|||That's because it's not as thorough as the DBREINDEX. It's used in
situations where db's cannot be brought down (ie 24x7 shops). Among
the things it doesn't defrag are extent hops.
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O3DZ7znbEHA.2572@.tk2msftngp13.phx.gbl...
> I've seen a few instances where INDEXDEFRAG does not bring the Scan
Density
> to 100% like the DBREINDEX does.
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uvT5alnbEHA.3944@.tk2msftngp13.phx.gbl...
10-15[vbcol=seagreen]
the[vbcol=seagreen]
lock[vbcol=seagreen]
> on
> for
> but
> other
>|||"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O3DZ7znbEHA.2572@.tk2msftngp13.phx.gbl...
> I've seen a few instances where INDEXDEFRAG does not bring the Scan
Density
> to 100% like the DBREINDEX does.
But does that really matter? You have a requirement of staying online,
obviously, so you kind of have to take whatever you can get... How much of a
performance impact, especially given your small tables, do you expect from
90% or 95% density vs. 100%?|||Scan Density is mostly irrelevant, as explained in Books OnLine. Please read
the whitepaper below which gives all the details.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O3DZ7znbEHA.2572@.tk2msftngp13.phx.gbl...
> I've seen a few instances where INDEXDEFRAG does not bring the Scan
Density
> to 100% like the DBREINDEX does.
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uvT5alnbEHA.3944@.tk2msftngp13.phx.gbl...
10-15[vbcol=seagreen]
the[vbcol=seagreen]
lock[vbcol=seagreen]
> on
> for
> but
> other
>|||Scan density should not be the factory you are looking at in most cases
anyway. Logical frag is the first thing you should seek. If you have more
than 1 file in your file group extent switching and all that goes with it is
not an accurate indicator in showcontig and should not be used for
determining fragmentation status in general. Bottom line is DBREINDEX is an
entirely off line operational and if you can't afford that you have to use
INDEXDEFRAG until Yukon comes out. This is a must read:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Andrew J. Kelly SQL MVP
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:O3DZ7znbEHA.2572@.tk2msftngp13.phx.gbl...
> I've seen a few instances where INDEXDEFRAG does not bring the Scan
Density
> to 100% like the DBREINDEX does.
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uvT5alnbEHA.3944@.tk2msftngp13.phx.gbl...
10-15[vbcol=seagreen]
the[vbcol=seagreen]
lock[vbcol=seagreen]
> on
> for
> but
> other
>
Any idea on this!
How can execute sqlscript stored in a folder.
i.e D:\SQLSCRIPT\cs_posting_exc.PRC
What can I write in query analyzer so that I can run that procedure in that
folder is a specified server and database.Have you looked into using osql.exe with xp_cmdshell? See SQL Server Books
Online for more details & post back if you have questions.
Anith
i.e D:\SQLSCRIPT\cs_posting_exc.PRC
What can I write in query analyzer so that I can run that procedure in that
folder is a specified server and database.Have you looked into using osql.exe with xp_cmdshell? See SQL Server Books
Online for more details & post back if you have questions.
Anith
2012年2月18日星期六
any good way to solve the daylight saving problem
i wanna show CST time in the report which are later than UTC time by 6 hours.SO we used dateADD(hh,-6,getUTSdate())
but the CST time will execute day light saving.
we r not sure where the server locates finally.
anyone kows some good ways to solve this issue?
Move the server to Arizona
{Just kidding 
You could check the registry for the Keys:
ActiveTimeBias,
Bias
located at:
|||HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\TimeZoneInformation
As Arnie said we need to fetch few data from the Registry.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones here you can find all the timezones and the corresponding offset(Standard & Daylight) values.
If your program written in .NET then its simple to call these objects (note: the regeistry hold few binary information which can deserilaized as objcet, it is responsible to convert the given UTC time to standard/daylight time). You have to use P/Invoke to call collect these infomarion.
|||Take a look at this and see if it might address your problem:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=28712
|||In 2005, you can use the CLR functions to deal with this. In a blog a while back (http://drsql.spaces.live.com/Blog/cns!80677FB08B3162E4!1311.entry) I posted a solution to the problem of UTC to server time. It is basically using the Date type in VB.NET to do the conversion. As I recall, you can also specify the time zone on the date type as well.
订阅：
博文 (Atom)