Mysterious Views

I have two views in a production database that I did not create and I cannot account for. From the names used and the syntax of the views, it seems that the system (SQL Server) generated them.

Name: _hypmv_0_5771
Name: _hypmv_0

The syntax crushes the entire SELECT statement (4357 characters) onto a single line of T-SQL (not something I'm usually in the habit of doing).

I have read on kbAlertz that there was a bug in the original release of SQL server where the Index Tuning Wizard did not always remove hypothetical indices used to calculate performance improvements. I have not found anything related to hypothetical views. I do have indexed views, but none that reference these views.

I have checked dependencies; nothing depends on these views and they, in turn, do not depend on anything else.

Has anyone else encountered this problem? Aside from saving the definitions and then dropping them (just to see what may break), are there any suggestions for how to deal with this?

The database is medium-large. About 25GB.

Thanks,

Hugh ScottHere is the article on hypothetical indexes...

http://support.microsoft.com/?id=290414

my SQL Server is too slow

Hi, All
recently, after I restored production DB (SQL7.0 +sp3)
backup file to my develope DB, my computer (develope DB --
desktop version)is too slow when I access develope DB. It
was fast before and it is slow right now. I didn't change
anything to my computer or server. The SQL server is still
fast, only my computer run the desktop develope DB is
slow. my pc is p4 2.4Ghz and 256 RM 120GB hard drive. I
checked my pc using Norton AntiVirus and non virus found.
Any ideas or suggestions?
Thanks and have a good one
HawkMost likely the 120GB Hard Drive has absolutely no comparison of performance
against the server. Server could have RAID or high cache disk drives. Also,
that may be a small amount of RAM for background services running locally to
your workstation.
Christopher Winn
Business Intelligence Engineer
Edugration, Corp.
"hawk" <abchawk@.hotmail.com> wrote in message
news:11f4201c410ed$bb3b8470$a401280a@.phx
.gbl...
> Hi, All
> recently, after I restored production DB (SQL7.0 +sp3)
> backup file to my develope DB, my computer (develope DB --
> desktop version)is too slow when I access develope DB. It
> was fast before and it is slow right now. I didn't change
> anything to my computer or server. The SQL server is still
> fast, only my computer run the desktop develope DB is
> slow. my pc is p4 2.4Ghz and 256 RM 120GB hard drive. I
> checked my pc using Norton AntiVirus and non virus found.
> Any ideas or suggestions?
> Thanks and have a good one
> Hawk|||To quantify what Chris Winn said, you should run Performance Monitor and
check % Processor Time (Processor), % Disc Time (PhysicalDisc), and %
Committed Bytes In Use (Memory) while you're running your queries. I think
what you'll see is % Disc Time very close to 100, % Processor Time very low,
and % Committed Bytes In Use very high.
"hawk" <abchawk@.hotmail.com> wrote in message
news:11f4201c410ed$bb3b8470$a401280a@.phx
.gbl...
> Hi, All
> recently, after I restored production DB (SQL7.0 +sp3)
> backup file to my develope DB, my computer (develope DB --
> desktop version)is too slow when I access develope DB. It
> was fast before and it is slow right now. I didn't change
> anything to my computer or server. The SQL server is still
> fast, only my computer run the desktop develope DB is
> slow. my pc is p4 2.4Ghz and 256 RM 120GB hard drive. I
> checked my pc using Norton AntiVirus and non virus found.
> Any ideas or suggestions?
> Thanks and have a good one
> Hawk|||Hi, Winn,
I;m sorry. What I want to say is my desktop version SQL
7.0 server is slow. It was fast. right now, it was slow
even i click any item from EM, of couse, the query is slow
too. My desltop version SQL server is develope DB and only
me can access it, none other uses. I try t0 rebuild the
index and to see what happen
my production DB on the win2000 server is still fast. not
problem at all
thanks!
hawk

>--Original Message--
>Most likely the 120GB Hard Drive has absolutely no
comparison of performance
>against the server. Server could have RAID or high cache
disk drives. Also,
>that may be a small amount of RAM for background services
running locally to
>your workstation.
>Christopher Winn
>Business Intelligence Engineer
>Edugration, Corp.
>|||As for the slowness, see if the autoclose database option is on for the
databases. Also, see if ODBC tracing is turned on at the client.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Hawk" <anonymous@.discussions.microsoft.com> wrote in message
news:d76b01c410f8$8e6b5870$a001280a@.phx.gbl...
> Hi, Winn,
> I;m sorry. What I want to say is my desktop version SQL
> 7.0 server is slow. It was fast. right now, it was slow
> even i click any item from EM, of couse, the query is slow
> too. My desltop version SQL server is develope DB and only
> me can access it, none other uses. I try t0 rebuild the
> index and to see what happen
> my production DB on the win2000 server is still fast. not
> problem at all
>
> thanks!
> hawk
>
>
> comparison of performance
> disk drives. Also,
> running locally to
>|||thanks Tibor,
autoclose database is on and i will set it to off.
but how can I know ODBC tracing is turned on at client?
How can I check it ? Could you tell me about it?
Thnaks and have a good day
hawk
>--Original Message--
>As for the slowness, see if the autoclose database option
is on for the
>databases. Also, see if ODBC tracing is turned on at the
client.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>|||You find the ODBC applet in the Administrative Tools program group. Here you
can see if it is running and if so, turn it off.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"hawk" <anonymous@.discussions.microsoft.com> wrote in message
news:f32e01c410ff$18243ef0$a601280a@.phx.gbl...
> thanks Tibor,
> autoclose database is on and i will set it to off.
> but how can I know ODBC tracing is turned on at client?
> How can I check it ? Could you tell me about it?
> Thnaks and have a good day
> hawk
> is on for the
> client.
>

My Server alias has stopped working. in VB.Net

I've set up an alias for my laptop-clone of the production server with
the same name as that server (since I work disconnected most often).
This has worked swimmingly in the past. I went into Client Network
Utilities, "Added" a new alias, set the protocol to TCPIP, specified
an alias with the same name as my production server, and assigned it
to point to my local SQL Server instance.
Suddenly, this has stopped working, for some reason. Oddly, I can
register the server in Enterprise manager, but when I use the
connectionstring:
"Integrated Security=SSPI;Persist Security Info=False;Initial
Catalog=MyDatabase;Data Source=" & SERVERNAME
-- where SERVERNAME is a constant for the alias/production server
name, I get a "SQL Server does not exist or access denied".
I've been racking my brains (both of them) trying to figure out what's
changed in my environment, but am coming up empty. It just doesn't
want to connect now.
Any ideas?
As a solo programmer, the worst thing in the world is sitting down for
a day's work only to spend hours solving basic connectivity issues
totally unrelated to your work product. Gaah.
BLink
Brian Link <blink@.visi.com> wrote in
news:9f5q215937ft3402d28issffja1ufc0ok3@.4ax.com:

> I've set up an alias for my laptop-clone of the production server with
...
> I've been racking my brains (both of them) trying to figure out what's
> changed in my environment, but am coming up empty. It just doesn't
> want to connect now.
> Any ideas?
> As a solo programmer, the worst thing in the world is sitting down for
> a day's work only to spend hours solving basic connectivity issues
> totally unrelated to your work product. Gaah.
...
Brian,
The first thing to do is to validate if this is an issue with the App, or
an issue with the configuration of SQL Server. As I mentioned in a
different group, you want to first make sure that the SQL Server service
isn't running under the local system service account.
First things first, go to the server itself and check and make sure
security is setup. In SQL Server there are "Logins" and there are
"Users". Users follow the database, logins follow the server. And Logins
are associated with Users. If you just loaded a backup of the database,
or just attached it on the new SQL Server, you may have a problem with
disassociated logins and users.
Go to a box with enterprise manager that can talk to the server and open
the server/security section. Locate the login in the list that you are
using to connect to the server via your app. (Is it a Windows User, or
Windows Group? or does it say "Standard".. I.e. What's the "type")
Open up that user and click on the Database Access tab and see what
databases that user is permitted to use and what their role in that
database is. If the database you built is not one that they are permitted
to use (but they should be) try and check the permit option and give them
at least public role. Click on O.K. If you get a message about that user
already existing in that database, you have a problem with disassociated
users and logins.
You can correct these by using a system stored procedure called
sp_change_users_login. You should read about it in the SQL Server Books
Online. The 'Auto_Fix' option may be all you need.
If the Permit box is already checked and looks fine. Then it's not likely
you have such a problem and you can test trying to connect to it. Log
into windows with the user that you want to use windows authentication to
connect to the box.
Then open SQL Server Query Analyzer and when prompted to connect to SQL
Server, choose the option to Connect Using "Windows authentication".
Click o.k. and see if you can connect. If you can't, then we have to dig
into it further. If you can, then try to select your database that you
use from the drop down list at the top of the window. (or type USE
"database name" and press the green arrow) Then try to select records
from one of your tables. If that all works, then the issue isn't with SQL
Server and is somewhere in your app.
Some other things to keep in mind. When you setup a Login, you specify a
"Default Database". By default that often is the MASTER database. Thus if
your app connects with that user account, if it doesn't explictly use the
proper database, it may try to do operations in the MASTER database that
it isn't allowed to or it may try to access objects that don't exist
there. If your app isn't explicitly picking the database, then make sure
the login you create is. (You may be stating this in your Catalog
component of the connection string in the application)
Hope this is of some help.
Mike
|||To troubleshoot, i would recomend deleting the entries in the following
registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Client\SuerSocketNetLib\LastConnect
This is where sql server caches the translation on the aliases.
See if you can still register the server in SQL EM after removing all the
cached entries.
Can you connect to the alias using query analyzer?
hth
Vikram Vamshi
Eclipsys Corporation
"Brian Link" <blink@.visi.com> wrote in message
news:9f5q215937ft3402d28issffja1ufc0ok3@.4ax.com...
> I've set up an alias for my laptop-clone of the production server with
> the same name as that server (since I work disconnected most often).
> This has worked swimmingly in the past. I went into Client Network
> Utilities, "Added" a new alias, set the protocol to TCPIP, specified
> an alias with the same name as my production server, and assigned it
> to point to my local SQL Server instance.
> Suddenly, this has stopped working, for some reason. Oddly, I can
> register the server in Enterprise manager, but when I use the
> connectionstring:
> "Integrated Security=SSPI;Persist Security Info=False;Initial
> Catalog=MyDatabase;Data Source=" & SERVERNAME
> -- where SERVERNAME is a constant for the alias/production server
> name, I get a "SQL Server does not exist or access denied".
> I've been racking my brains (both of them) trying to figure out what's
> changed in my environment, but am coming up empty. It just doesn't
> want to connect now.
> Any ideas?
> As a solo programmer, the worst thing in the world is sitting down for
> a day's work only to spend hours solving basic connectivity issues
> totally unrelated to your work product. Gaah.
> BLink

My Server alias has stopped working. in VB.Net

I've set up an alias for my laptop-clone of the production server with
the same name as that server (since I work disconnected most often).
This has worked swimmingly in the past. I went into Client Network
Utilities, "Added" a new alias, set the protocol to TCPIP, specified
an alias with the same name as my production server, and assigned it
to point to my local SQL Server instance.
Suddenly, this has stopped working, for some reason. Oddly, I can
register the server in Enterprise manager, but when I use the
connectionstring:
"Integrated Security=SSPI;Persist Security Info=False;Initial
Catalog=MyDatabase;Data Source=" & SERVERNAME
-- where SERVERNAME is a constant for the alias/production server
name, I get a "SQL Server does not exist or access denied".
I've been racking my brains (both of them) trying to figure out what's
changed in my environment, but am coming up empty. It just doesn't
want to connect now.
Any ideas?
As a solo programmer, the worst thing in the world is sitting down for
a day's work only to spend hours solving basic connectivity issues
totally unrelated to your work product. Gaah.
BLinkBrian Link <blink@.visi.com> wrote in
news:9f5q215937ft3402d28issffja1ufc0ok3@.
4ax.com:

> I've set up an alias for my laptop-clone of the production server with
...
> I've been racking my brains (both of them) trying to figure out what's
> changed in my environment, but am coming up empty. It just doesn't
> want to connect now.
> Any ideas?
> As a solo programmer, the worst thing in the world is sitting down for
> a day's work only to spend hours solving basic connectivity issues
> totally unrelated to your work product. Gaah.
...
Brian,
The first thing to do is to validate if this is an issue with the App, or
an issue with the configuration of SQL Server. As I mentioned in a
different group, you want to first make sure that the SQL Server service
isn't running under the local system service account.
First things first, go to the server itself and check and make sure
security is setup. In SQL Server there are "Logins" and there are
"Users". Users follow the database, logins follow the server. And Logins
are associated with Users. If you just loaded a backup of the database,
or just attached it on the new SQL Server, you may have a problem with
disassociated logins and users.
Go to a box with enterprise manager that can talk to the server and open
the server/security section. Locate the login in the list that you are
using to connect to the server via your app. (Is it a Windows User, or
Windows Group? or does it say "Standard".. I.e. What's the "type")
Open up that user and click on the Database Access tab and see what
databases that user is permitted to use and what their role in that
database is. If the database you built is not one that they are permitted
to use (but they should be) try and check the permit option and give them
at least public role. Click on O.K. If you get a message about that user
already existing in that database, you have a problem with disassociated
users and logins.
You can correct these by using a system stored procedure called
sp_change_users_login. You should read about it in the SQL Server Books
Online. The 'Auto_Fix' option may be all you need.
If the Permit box is already checked and looks fine. Then it's not likely
you have such a problem and you can test trying to connect to it. Log
into windows with the user that you want to use windows authentication to
connect to the box.
Then open SQL Server Query Analyzer and when prompted to connect to SQL
Server, choose the option to Connect Using "Windows authentication".
Click o.k. and see if you can connect. If you can't, then we have to dig
into it further. If you can, then try to select your database that you
use from the drop down list at the top of the window. (or type USE
"database name" and press the green arrow) Then try to select records
from one of your tables. If that all works, then the issue isn't with SQL
Server and is somewhere in your app.
Some other things to keep in mind. When you setup a Login, you specify a
"Default Database". By default that often is the MASTER database. Thus if
your app connects with that user account, if it doesn't explictly use the
proper database, it may try to do operations in the MASTER database that
it isn't allowed to or it may try to access objects that don't exist
there. If your app isn't explicitly picking the database, then make sure
the login you create is. (You may be stating this in your Catalog
component of the connection string in the application)
Hope this is of some help.
Mike|||To troubleshoot, i would recomend deleting the entries in the following
registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\Client\SuerSocketNetLib\La
stConnect
This is where sql server caches the translation on the aliases.
See if you can still register the server in SQL EM after removing all the
cached entries.
Can you connect to the alias using query analyzer?
hth
--
Vikram Vamshi
Eclipsys Corporation
"Brian Link" <blink@.visi.com> wrote in message
news:9f5q215937ft3402d28issffja1ufc0ok3@.
4ax.com...
> I've set up an alias for my laptop-clone of the production server with
> the same name as that server (since I work disconnected most often).
> This has worked swimmingly in the past. I went into Client Network
> Utilities, "Added" a new alias, set the protocol to TCPIP, specified
> an alias with the same name as my production server, and assigned it
> to point to my local SQL Server instance.
> Suddenly, this has stopped working, for some reason. Oddly, I can
> register the server in Enterprise manager, but when I use the
> connectionstring:
> "Integrated Security=SSPI;Persist Security Info=False;Initial
> Catalog=MyDatabase;Data Source=" & SERVERNAME
> -- where SERVERNAME is a constant for the alias/production server
> name, I get a "SQL Server does not exist or access denied".
> I've been racking my brains (both of them) trying to figure out what's
> changed in my environment, but am coming up empty. It just doesn't
> want to connect now.
> Any ideas?
> As a solo programmer, the worst thing in the world is sitting down for
> a day's work only to spend hours solving basic connectivity issues
> totally unrelated to your work product. Gaah.
> BLink