Money Data Type: users

Showing posts with label users. Show all posts

Monday, March 26, 2012

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?
> >> >
> >> >
> >>
> >>
> >
> >
>

More details for: SSPI context / [DBNETLIB][ConnectionRead (recv()).] General network error

Hello together,

since over a week I've been looking to

find out the reason for two error messages I get from our users and

servers, unfortunately without success up to now.
I really hope you can help me.
We are using Win 2k servers with active directory and SQL Server 2000,

clients are all Win XP with SP2. Versions of access are 2002 &

2003. The errors are userspecific and occur in both versions. The

SQL-Servers are accessed with an adp-file in 2002-format.
We have one usergroup which is member of specially many groups. This

affects the size of their windows access token which becomes constantly

larger. In order to enable those users to still access their mailboxes

on our Exchange servers, the DWORD entry "MaxTokenSize" with the

decimal value "65535" was made to the newly created key "Parameters" of

their registry branch

"HKLM\SYSTEM\CurrentControlSet\Control\Lsa\Kerberos ".
Since then those users can not access any of our SQL Servers using the

windows authentification. One of them gets the error

"[DBNETLIB][ConnectionRead(recv().] General network error. Check your

network documentation.", the others the error "Check connection:

Failed: Cannot generate SSPI context".
In case of resetting the registry entries (by deleting them), the one

user receives the same error message as the rest while it doesn't make

any difference to those (but at least they can't connect to their

mailboxes).
After having researched the web, I realised in one of the SQL-Servers

logfiles the entry "Invalid buffer received from client.." which lead

me to start to believe it could have to do something with the kerberos

encryption in the first place. Therefore I asked if changes to the

tokensize had been made. I applied the change with "EXEC sp_configure

'network packet size', 65535 RECONFIGURE" on our testsystem and "EXEC

sp_configure" confirms that the value is run.
Consequence: The entry in the SQL Server log doesn't appear any longer, but the users still receive their error messages.
Do you have any hints?
Your comments will be highly appreciated!
Regards,

caracol

Hi, CaraCol

Your case seems interesting. The error you hit is very general and could has various causes. So, please first check following blogs to see whether they resolved your problem:

http://blogs.msdn.com/sql_protocols/archive/2006/12/02/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections.aspx

http://blogs.msdn.com/sql_protocols/archive/2005/10/15/481297.aspx

http://blogs.msdn.com/sql_protocols/archive/2005/10/19/482782.aspx

http://blogs.msdn.com/sql_protocols/archive/2007/01/02/cannot-generate-sspi-context-error-message-poisoned-dns.aspx

If they not, can you provide more detail answer about your system configuration by fllowing up the guideline:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=362498&SiteID=1

Thanks!

Ming

|||Hi Ming,

thanks for your informationsites. I had already tried out parts of it but reviewed them all the same.

The SPN does work correctly, otherwise our other usersgroups wouldn't be able to connect to our servers. I double checked this by setspn -l domain\accountname and got the desired result on the rigth port.

When turning on Named Pipes and restarting the instance on the serverside while activating NP on the clientside only (cliconfig.exe), we get the error message "Error on testing the connection while initialising the provider. [DBNETLIB][ConnectionOpen (Connect()).] SQL Server does not exist or access denied" (My error messages are translated from our german clients).

We are running SQL Servers 2000 Standard English Edition (8.00.760 SP3) on English W2k servers with SP4 and we use named instances with TCP/IP only.
Our clients are WinXP Pro with SP2 and Access 2002 (MDAC 2.70.7713.0) or Access 2003 (MDAC 2.81.1117.0). Connection is made by Access.
Aliases are not existent.
The service account is a domain administrator.
Service of WinXP SP2 Firewall is deaktivated or the firewall is configured as inactive.
There is no encryption enforcement.
Users are local admins.
We have installed Trend Micro Server Protect / Antivir Corporate Edition 5.58 respectively on every machine. A firewall is not provided by this version.

The Clients are shut down every day, so I doubt deleting the cached credentials when they should be deleted after every restart would make any difference.

Do you have any ohter hints?

Thanks again.

Regards,
caracol|||

Hi, Caracol

Sorry, I am confused the error you came across, is it "Can not generate SSPI Context" or " General Network Error"? If it is the latter, [DBNETLIB][ConnectionRead (recv()) indicates connection failed during your DB operation, so, please try

1) http://blogs.msdn.com/sql_protocols/archive/2006/04/12/574608.aspx

2) Open SQL Server Profile, repro the issue, see which specific client operation caused the failure, also, check server ERRORLOG or EventLog to find clue.

3) What if you enable NP on the SQL Server, and make np connection from client, see whether same issue occured? Or at least whether the "SSPI Context" error persist?, If so, that might due to your domain configuration issue.

BTW, It'd better upgrade your SQL Server 2000 from SP3 to SP4, this might be a known issue in SP3 but fixed in SP4 or SQL 2005.

Good Luck!

Ming.

|||

http://support.microsoft.com/kb/814401

http://www.sqlservercentral.com/columnists/cmiller/cannotgeneratesspicontext.asp

|||Hi Satya,

thanks for your interest, but your links are just some of the standard ones I came over a dozen times.

Regards,
caracol|||Hi Ming,

thanks again for your enthusiasm.

As I described, I got different messages depending on the configuration.

TCP/IP:
Quote:
"Since then those users can not access any of our SQL Servers using the windows authentification. One of them gets the error "[DBNETLIB][ConnectionRead(recv().] General network error. Check your network documentation.", the others the error "Check connection: Failed: Cannot generate SSPI context"."
-> This ist the configuration I started with and where the error occured the first time.

NP:
Quote:
"When turning on Named Pipes and restarting the instance on the serverside while activating NP on the clientside only (cliconfig.exe), we get the error message "Error on testing the connection while initialising the provider. [DBNETLIB][ConnectionOpen (Connect()).] SQL Server does not exist or access denied" (My error messages are translated from our german clients)."
-> This is what I tried later on to see if changing access method might succeed.

I have no idea, why this specific user on TCP/IP gets the error "[DBNETLIB][ConnectionRead(recv().]" while the rest gets
"Check connection: Failed: Cannot generate SSPI context". It must have to do with the profile, though, as it doesn't matter on which client he's logged on.

Quote from your last answer:
{1) http://blogs.msdn.com/sql_protocols/archive/2006/04/12/574608.aspx }
-> I already got over this due to your post from 01-02-2007, it was one of the sublinks over there. Therefore, this info was not helpful this time.

{2) Open SQL Server Profile, repro the issue, see which specific client operation caused the failure, also, check server ERRORLOG or EventLog to find clue.}
-> Standard to revise the logs again, so not helpful. I described that the users can't even log in to SQL server any longer, and that access 2002 / 2003 is this client. It is nothing else than access' standard connection that doesn't work, nothing of specific client operation.

{3) What if you enable NP on the SQL Server, and make np connection from client, see whether same issue occured? Or at least whether the "SSPI Context" error persist?, If so, that might due to your domain configuration issue.}
-> I wrote that I already had tried out NP, again Quote:
"When turning on Named Pipes and restarting the instance on the serverside while activating NP on the clientside only (cliconfig.exe), we get the error message "Error on testing the connection while initialising the provider. [DBNETLIB][ConnectionOpen (Connect()).] SQL Server does not exist or access denied" (My error messages are translated from our german clients)." So, not helpful, as I already decribed which error the users get in this case.

{BTW, It'd better upgrade your SQL Server 2000 from SP3 to SP4, this might be a known issue in SP3 but fixed in SP4 or SQL 2005.}
-> Finally another idea, thanks. As we have one version on every server worldwide, I'm in no possition to just install SP4 on one or a few of them, but I'll make the proposition to upgrade worldwide to SP4. We'll at least try this out in our test environment and see if it is of any help, although {"this might be a known issue in SP3 but fixed in SP4 or SQL 2005"} is not what commercial operations consider to be trustworthy or even professional. (This is meant as hint for MS, not to you personally.)

Nevertheless, I thank you very much for taking your time again to try to assist me.

Regards,
caracol