More dumb questions

Still learning so bear with me
1) Say I have a complicated select query (many joins) that I'd like users
to run. Which typically
performs better and why: creating a view with the select query or creating
a stored procedure with
the select query. Assume users don't care which mechanism is used.
2) I read where the rows column in sysindexes shows the number of rows in a
table and is a good
alternative to select count(*). The same article went on to state that this
figure can become inaccurate.
What can cause this figure to not match what a select count(*) would bring
back?
3) with select statements with many left outer joins used, do the order of
the joins matter? Do the joins
filter as they go along or does sql server figure it all out?
4) what is an IX lock? I can't figure it out from my reading. How is it
different from the X lock? Is there
a relationship?1. No telling which will be more efficient. You need to compare both
execution plans to determine that. They may be the same, and maybe not.
2. The values in sysindexes can get 'de-synchronized from the actual
table.row count as the result of bulk insert operations, deleting large
blocks of rows, etc. Re-synchronize by update statistics.
3. Order of JOINs 'can' matter. SQL will do it's best to 'figure it out',
but perhaps not the way you intended.
4. IX Lock =Intent Exclusive. Next in line to have an Exclusive (X) lock. IX
lock is marking one's position in the queue to place an exclusive lock (X)
as soon as any preceding shared locks are removed.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dodo Lurker" <none@.noemailplease> wrote in message
news:bPadneyD79wGI3LZnZ2dnUVZ_qGdnZ2d@.comcast.com...
> Still learning so bear with me
> 1) Say I have a complicated select query (many joins) that I'd like users
> to run. Which typically
> performs better and why: creating a view with the select query or
> creating
> a stored procedure with
> the select query. Assume users don't care which mechanism is used.
> 2) I read where the rows column in sysindexes shows the number of rows in
> a
> table and is a good
> alternative to select count(*). The same article went on to state that
> this
> figure can become inaccurate.
> What can cause this figure to not match what a select count(*) would
> bring
> back?
> 3) with select statements with many left outer joins used, do the order
> of
> the joins matter? Do the joins
> filter as they go along or does sql server figure it all out?
> 4) what is an IX lock? I can't figure it out from my reading. How is it
> different from the X lock? Is there
> a relationship?
>|||The decision between a view and a stored procedure is usually based on how
the data will be used. A view can be used like a table, but a stored
procedure cannot. A stored procedure can take parameters, and include other
statements, and error checking. There is lots of info available about
optimizing stored procedures so search the Knowledgebase at
http://support.microsoft.com/search/?adv=1 It's impossible to say which
will perform better. It completely depends on what you're doing, what your
parameters are, how the query or proc is called, etc, etc.
The values in sysindexes can get out of date when you do certain bulk
operations. SQL Server doesn't always update the counts for all bulk inserts
and truncates. You can run DBCC UPDATEUSAGE to correct the values. UPDATE
STATISTICS will not do it.
The order of joins should not matter. The query is optimized as a whole and
may be completely rewritten internally.
An IX lock means INTENT-EXCLUSIVE, and is mainly a protection mechanism. It
does not mean a process is waiting for an X lock; if it were waiting, it
would be blocked, it doesn't get a special kind of lock to indicate waiting.
An intent lock is acquired a higher granularities when a regular lock is
acquired on a lower granularity unit. For example, if you have an exclusive
lock on a row, you will get an IX lock on the page and another IX lock on
the table, which will keep other processes from getting a lock on the whole
page or table. So yes, there is a relationship between X and IX.
Two IX locks are compatible with each other (if two processes each have X
locks on separate rows in the same table, they will each have IX locks on
the table itself), but X and IX are not compatible. You can read about "Lock
Compatibility" in the Books Online.
I suggest if you need to follow up on any of these questions, you start a
separate thread. It's a bit confusing to have so many separate topics in a
single message. They are not dumb questions, and each topic is certainly
worth of its own discussion. There is more info you can read about each of
these topics. Once you've done that, feel free to post follow-up questions.
--
HTH
Kalen Delaney, SQL Server MVP
"Dodo Lurker" <none@.noemailplease> wrote in message
news:bPadneyD79wGI3LZnZ2dnUVZ_qGdnZ2d@.comcast.com...
> Still learning so bear with me
> 1) Say I have a complicated select query (many joins) that I'd like users
> to run. Which typically
> performs better and why: creating a view with the select query or
> creating
> a stored procedure with
> the select query. Assume users don't care which mechanism is used.
> 2) I read where the rows column in sysindexes shows the number of rows in
> a
> table and is a good
> alternative to select count(*). The same article went on to state that
> this
> figure can become inaccurate.
> What can cause this figure to not match what a select count(*) would
> bring
> back?
> 3) with select statements with many left outer joins used, do the order
> of
> the joins matter? Do the joins
> filter as they go along or does sql server figure it all out?
> 4) what is an IX lock? I can't figure it out from my reading. How is it
> different from the X lock? Is there
> a relationship?
>|||Thank you. From now on, I'll start separate threads. Sorry for these
questions. I am VB developer
who's been tabbed to be the "database guy" since the company does not want
to hire a real
dba *shrug*.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OBeH6WNyGHA.2572@.TK2MSFTNGP06.phx.gbl...
> The decision between a view and a stored procedure is usually based on how
> the data will be used. A view can be used like a table, but a stored
> procedure cannot. A stored procedure can take parameters, and include
other
> statements, and error checking. There is lots of info available about
> optimizing stored procedures so search the Knowledgebase at
> http://support.microsoft.com/search/?adv=1 It's impossible to say which
> will perform better. It completely depends on what you're doing, what your
> parameters are, how the query or proc is called, etc, etc.
> The values in sysindexes can get out of date when you do certain bulk
> operations. SQL Server doesn't always update the counts for all bulk
inserts
> and truncates. You can run DBCC UPDATEUSAGE to correct the values. UPDATE
> STATISTICS will not do it.
> The order of joins should not matter. The query is optimized as a whole
and
> may be completely rewritten internally.
> An IX lock means INTENT-EXCLUSIVE, and is mainly a protection mechanism.
It
> does not mean a process is waiting for an X lock; if it were waiting, it
> would be blocked, it doesn't get a special kind of lock to indicate
waiting.
> An intent lock is acquired a higher granularities when a regular lock is
> acquired on a lower granularity unit. For example, if you have an
exclusive
> lock on a row, you will get an IX lock on the page and another IX lock on
> the table, which will keep other processes from getting a lock on the
whole
> page or table. So yes, there is a relationship between X and IX.
> Two IX locks are compatible with each other (if two processes each have X
> locks on separate rows in the same table, they will each have IX locks on
> the table itself), but X and IX are not compatible. You can read about
"Lock
> Compatibility" in the Books Online.
> I suggest if you need to follow up on any of these questions, you start a
> separate thread. It's a bit confusing to have so many separate topics in a
> single message. They are not dumb questions, and each topic is certainly
> worth of its own discussion. There is more info you can read about each of
> these topics. Once you've done that, feel free to post follow-up
questions.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Dodo Lurker" <none@.noemailplease> wrote in message
> news:bPadneyD79wGI3LZnZ2dnUVZ_qGdnZ2d@.comcast.com...
> > Still learning so bear with me
> >
> > 1) Say I have a complicated select query (many joins) that I'd like
users
> > to run. Which typically
> > performs better and why: creating a view with the select query or
> > creating
> > a stored procedure with
> > the select query. Assume users don't care which mechanism is used.
> >
> > 2) I read where the rows column in sysindexes shows the number of rows
in
> > a
> > table and is a good
> > alternative to select count(*). The same article went on to state that
> > this
> > figure can become inaccurate.
> > What can cause this figure to not match what a select count(*) would
> > bring
> > back?
> >
> > 3) with select statements with many left outer joins used, do the order
> > of
> > the joins matter? Do the joins
> > filter as they go along or does sql server figure it all out?
> >
> > 4) what is an IX lock? I can't figure it out from my reading. How is
it
> > different from the X lock? Is there
> > a relationship?
> >
> >
>|||Don't worry, they're all valid questions.
Do you have access to the documentation (Books Online) that comes with SQL
Server? If you can't find it, that should be your first question.
--
HTH
Kalen Delaney, SQL Server MVP
"Dodo Lurker" <none@.noemailplease> wrote in message
news:J_6dnTrx3rlQ2W3ZnZ2dnUVZ_qGdnZ2d@.comcast.com...
> Thank you. From now on, I'll start separate threads. Sorry for these
> questions. I am VB developer
> who's been tabbed to be the "database guy" since the company does not want
> to hire a real
> dba *shrug*.
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OBeH6WNyGHA.2572@.TK2MSFTNGP06.phx.gbl...
>> The decision between a view and a stored procedure is usually based on
>> how
>> the data will be used. A view can be used like a table, but a stored
>> procedure cannot. A stored procedure can take parameters, and include
> other
>> statements, and error checking. There is lots of info available about
>> optimizing stored procedures so search the Knowledgebase at
>> http://support.microsoft.com/search/?adv=1 It's impossible to say which
>> will perform better. It completely depends on what you're doing, what
>> your
>> parameters are, how the query or proc is called, etc, etc.
>> The values in sysindexes can get out of date when you do certain bulk
>> operations. SQL Server doesn't always update the counts for all bulk
> inserts
>> and truncates. You can run DBCC UPDATEUSAGE to correct the values. UPDATE
>> STATISTICS will not do it.
>> The order of joins should not matter. The query is optimized as a whole
> and
>> may be completely rewritten internally.
>> An IX lock means INTENT-EXCLUSIVE, and is mainly a protection mechanism.
> It
>> does not mean a process is waiting for an X lock; if it were waiting, it
>> would be blocked, it doesn't get a special kind of lock to indicate
> waiting.
>> An intent lock is acquired a higher granularities when a regular lock is
>> acquired on a lower granularity unit. For example, if you have an
> exclusive
>> lock on a row, you will get an IX lock on the page and another IX lock on
>> the table, which will keep other processes from getting a lock on the
> whole
>> page or table. So yes, there is a relationship between X and IX.
>> Two IX locks are compatible with each other (if two processes each have X
>> locks on separate rows in the same table, they will each have IX locks on
>> the table itself), but X and IX are not compatible. You can read about
> "Lock
>> Compatibility" in the Books Online.
>> I suggest if you need to follow up on any of these questions, you start a
>> separate thread. It's a bit confusing to have so many separate topics in
>> a
>> single message. They are not dumb questions, and each topic is certainly
>> worth of its own discussion. There is more info you can read about each
>> of
>> these topics. Once you've done that, feel free to post follow-up
> questions.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> "Dodo Lurker" <none@.noemailplease> wrote in message
>> news:bPadneyD79wGI3LZnZ2dnUVZ_qGdnZ2d@.comcast.com...
>> > Still learning so bear with me
>> >
>> > 1) Say I have a complicated select query (many joins) that I'd like
> users
>> > to run. Which typically
>> > performs better and why: creating a view with the select query or
>> > creating
>> > a stored procedure with
>> > the select query. Assume users don't care which mechanism is used.
>> >
>> > 2) I read where the rows column in sysindexes shows the number of rows
> in
>> > a
>> > table and is a good
>> > alternative to select count(*). The same article went on to state that
>> > this
>> > figure can become inaccurate.
>> > What can cause this figure to not match what a select count(*) would
>> > bring
>> > back?
>> >
>> > 3) with select statements with many left outer joins used, do the
>> > order
>> > of
>> > the joins matter? Do the joins
>> > filter as they go along or does sql server figure it all out?
>> >
>> > 4) what is an IX lock? I can't figure it out from my reading. How is
> it
>> > different from the X lock? Is there
>> > a relationship?
>> >
>> >
>>
>|||I do and I did read them. I just can't seem to get my arms around some of
the topics so
I look for a layman's explanation.
Thank you for your help!
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:#MVNBRSyGHA.5048@.TK2MSFTNGP05.phx.gbl...
> Don't worry, they're all valid questions.
> Do you have access to the documentation (Books Online) that comes with SQL
> Server? If you can't find it, that should be your first question.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Dodo Lurker" <none@.noemailplease> wrote in message
> news:J_6dnTrx3rlQ2W3ZnZ2dnUVZ_qGdnZ2d@.comcast.com...
> > Thank you. From now on, I'll start separate threads. Sorry for these
> > questions. I am VB developer
> > who's been tabbed to be the "database guy" since the company does not
want
> > to hire a real
> > dba *shrug*.
> >
> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > news:OBeH6WNyGHA.2572@.TK2MSFTNGP06.phx.gbl...
> >> The decision between a view and a stored procedure is usually based on
> >> how
> >> the data will be used. A view can be used like a table, but a stored
> >> procedure cannot. A stored procedure can take parameters, and include
> > other
> >> statements, and error checking. There is lots of info available about
> >> optimizing stored procedures so search the Knowledgebase at
> >> http://support.microsoft.com/search/?adv=1 It's impossible to say
which
> >> will perform better. It completely depends on what you're doing, what
> >> your
> >> parameters are, how the query or proc is called, etc, etc.
> >>
> >> The values in sysindexes can get out of date when you do certain bulk
> >> operations. SQL Server doesn't always update the counts for all bulk
> > inserts
> >> and truncates. You can run DBCC UPDATEUSAGE to correct the values.
UPDATE
> >> STATISTICS will not do it.
> >>
> >> The order of joins should not matter. The query is optimized as a whole
> > and
> >> may be completely rewritten internally.
> >>
> >> An IX lock means INTENT-EXCLUSIVE, and is mainly a protection
mechanism.
> > It
> >> does not mean a process is waiting for an X lock; if it were waiting,
it
> >> would be blocked, it doesn't get a special kind of lock to indicate
> > waiting.
> >> An intent lock is acquired a higher granularities when a regular lock
is
> >> acquired on a lower granularity unit. For example, if you have an
> > exclusive
> >> lock on a row, you will get an IX lock on the page and another IX lock
on
> >> the table, which will keep other processes from getting a lock on the
> > whole
> >> page or table. So yes, there is a relationship between X and IX.
> >>
> >> Two IX locks are compatible with each other (if two processes each have
X
> >> locks on separate rows in the same table, they will each have IX locks
on
> >> the table itself), but X and IX are not compatible. You can read about
> > "Lock
> >> Compatibility" in the Books Online.
> >>
> >> I suggest if you need to follow up on any of these questions, you start
a
> >> separate thread. It's a bit confusing to have so many separate topics
in
> >> a
> >> single message. They are not dumb questions, and each topic is
certainly
> >> worth of its own discussion. There is more info you can read about each
> >> of
> >> these topics. Once you've done that, feel free to post follow-up
> > questions.
> >>
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >>
> >>
> >> "Dodo Lurker" <none@.noemailplease> wrote in message
> >> news:bPadneyD79wGI3LZnZ2dnUVZ_qGdnZ2d@.comcast.com...
> >> > Still learning so bear with me
> >> >
> >> > 1) Say I have a complicated select query (many joins) that I'd like
> > users
> >> > to run. Which typically
> >> > performs better and why: creating a view with the select query or
> >> > creating
> >> > a stored procedure with
> >> > the select query. Assume users don't care which mechanism is used.
> >> >
> >> > 2) I read where the rows column in sysindexes shows the number of
rows
> > in
> >> > a
> >> > table and is a good
> >> > alternative to select count(*). The same article went on to state
that
> >> > this
> >> > figure can become inaccurate.
> >> > What can cause this figure to not match what a select count(*) would
> >> > bring
> >> > back?
> >> >
> >> > 3) with select statements with many left outer joins used, do the
> >> > order
> >> > of
> >> > the joins matter? Do the joins
> >> > filter as they go along or does sql server figure it all out?
> >> >
> >> > 4) what is an IX lock? I can't figure it out from my reading. How
is
> > it
> >> > different from the X lock? Is there
> >> > a relationship?
> >> >
> >> >
> >>
> >>
> >
> >
>