Showing posts with label experiencing. Show all posts
Showing posts with label experiencing. Show all posts

Thursday, March 29, 2012

Database Status = Loading

A coworker is repeatedly experiencing problems restoring database backups on
a new sql virtual server (clustered). He has restored a database backup
using enterprise manager. Enterprise manager told him that the restore has
completed without giving any errors. We can not access the database and
enterprise manager shows the status of the database as "Loading". I ran the
command RESTORE DATABASE <databasename> WITH RECOVERY and received an error
basically saying that the database was not completely loaded or the restore
did not complete. Can anyone tell me what the problem is or tell me how to
avoid this scenario?
-SJHi
Have you checked the SQL Server log and the System Event log to see if there
is any additional information in them?
It is usually a good idea to post the exact error numbers and messages.
John
"SJ" wrote:

> A coworker is repeatedly experiencing problems restoring database backups
on
> a new sql virtual server (clustered). He has restored a database backup
> using enterprise manager. Enterprise manager told him that the restore ha
s
> completed without giving any errors. We can not access the database and
> enterprise manager shows the status of the database as "Loading". I ran t
he
> command RESTORE DATABASE <databasename> WITH RECOVERY and received an erro
r
> basically saying that the database was not completely loaded or the restor
e
> did not complete. Can anyone tell me what the problem is or tell me how t
o
> avoid this scenario?
> --
> -SJ|||Here is the error:
File '<logical file name>' was only partially restored by a database or file
restore. The entire file must be successfully restored before applying the
log.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Enterprise manager told me "Restore of database '<databasename>' completed
successfully."
-SJ
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Have you checked the SQL Server log and the System Event log to see if the
re
> is any additional information in them?
> It is usually a good idea to post the exact error numbers and messages.
> John
> "SJ" wrote:
>|||Hi SJ
Check out:
http://groups.google.com/group/micr...27357de5278e8bd
John
"SJ" wrote:
[vbcol=seagreen]
> Here is the error:
> File '<logical file name>' was only partially restored by a database or fi
le
> restore. The entire file must be successfully restored before applying the
> log.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> Enterprise manager told me "Restore of database '<databasename>' completed
> successfully."
> -SJ
> "John Bell" wrote:
>

Database Status = Loading

A coworker is repeatedly experiencing problems restoring database backups on
a new sql virtual server (clustered). He has restored a database backup
using enterprise manager. Enterprise manager told him that the restore has
completed without giving any errors. We can not access the database and
enterprise manager shows the status of the database as "Loading". I ran the
command RESTORE DATABASE <databasename> WITH RECOVERY and received an error
basically saying that the database was not completely loaded or the restore
did not complete. Can anyone tell me what the problem is or tell me how to
avoid this scenario?
-SJ
Hi
Have you checked the SQL Server log and the System Event log to see if there
is any additional information in them?
It is usually a good idea to post the exact error numbers and messages.
John
"SJ" wrote:

> A coworker is repeatedly experiencing problems restoring database backups on
> a new sql virtual server (clustered). He has restored a database backup
> using enterprise manager. Enterprise manager told him that the restore has
> completed without giving any errors. We can not access the database and
> enterprise manager shows the status of the database as "Loading". I ran the
> command RESTORE DATABASE <databasename> WITH RECOVERY and received an error
> basically saying that the database was not completely loaded or the restore
> did not complete. Can anyone tell me what the problem is or tell me how to
> avoid this scenario?
> --
> -SJ
|||Here is the error:
File '<logical file name>' was only partially restored by a database or file
restore. The entire file must be successfully restored before applying the
log.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Enterprise manager told me "Restore of database '<databasename>' completed
successfully."
-SJ
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Have you checked the SQL Server log and the System Event log to see if there
> is any additional information in them?
> It is usually a good idea to post the exact error numbers and messages.
> John
> "SJ" wrote:
|||Hi SJ
Check out:
http://groups.google.com/group/micro...7357de5278e8bd
John
"SJ" wrote:
[vbcol=seagreen]
> Here is the error:
> File '<logical file name>' was only partially restored by a database or file
> restore. The entire file must be successfully restored before applying the
> log.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> Enterprise manager told me "Restore of database '<databasename>' completed
> successfully."
> -SJ
> "John Bell" wrote:

Database Status = Loading

A coworker is repeatedly experiencing problems restoring database backups on
a new sql virtual server (clustered). He has restored a database backup
using enterprise manager. Enterprise manager told him that the restore has
completed without giving any errors. We can not access the database and
enterprise manager shows the status of the database as "Loading". I ran the
command RESTORE DATABASE <databasename> WITH RECOVERY and received an error
basically saying that the database was not completely loaded or the restore
did not complete. Can anyone tell me what the problem is or tell me how to
avoid this scenario?
--
-SJHi
Have you checked the SQL Server log and the System Event log to see if there
is any additional information in them?
It is usually a good idea to post the exact error numbers and messages.
John
"SJ" wrote:
> A coworker is repeatedly experiencing problems restoring database backups on
> a new sql virtual server (clustered). He has restored a database backup
> using enterprise manager. Enterprise manager told him that the restore has
> completed without giving any errors. We can not access the database and
> enterprise manager shows the status of the database as "Loading". I ran the
> command RESTORE DATABASE <databasename> WITH RECOVERY and received an error
> basically saying that the database was not completely loaded or the restore
> did not complete. Can anyone tell me what the problem is or tell me how to
> avoid this scenario?
> --
> -SJ|||Here is the error:
File '<logical file name>' was only partially restored by a database or file
restore. The entire file must be successfully restored before applying the
log.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Enterprise manager told me "Restore of database '<databasename>' completed
successfully."
-SJ
"John Bell" wrote:
> Hi
> Have you checked the SQL Server log and the System Event log to see if there
> is any additional information in them?
> It is usually a good idea to post the exact error numbers and messages.
> John
> "SJ" wrote:
> > A coworker is repeatedly experiencing problems restoring database backups on
> > a new sql virtual server (clustered). He has restored a database backup
> > using enterprise manager. Enterprise manager told him that the restore has
> > completed without giving any errors. We can not access the database and
> > enterprise manager shows the status of the database as "Loading". I ran the
> > command RESTORE DATABASE <databasename> WITH RECOVERY and received an error
> > basically saying that the database was not completely loaded or the restore
> > did not complete. Can anyone tell me what the problem is or tell me how to
> > avoid this scenario?
> >
> > --
> > -SJ|||Hi SJ
Check out:
http://groups.google.com/group/microsoft.public.sqlserver.clients/browse_frm/thread/b5447588fe32acf0/527357de5278e8bd#527357de5278e8bd
John
"SJ" wrote:
> Here is the error:
> File '<logical file name>' was only partially restored by a database or file
> restore. The entire file must be successfully restored before applying the
> log.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> Enterprise manager told me "Restore of database '<databasename>' completed
> successfully."
> -SJ
> "John Bell" wrote:
> > Hi
> >
> > Have you checked the SQL Server log and the System Event log to see if there
> > is any additional information in them?
> >
> > It is usually a good idea to post the exact error numbers and messages.
> >
> > John
> >
> > "SJ" wrote:
> >
> > > A coworker is repeatedly experiencing problems restoring database backups on
> > > a new sql virtual server (clustered). He has restored a database backup
> > > using enterprise manager. Enterprise manager told him that the restore has
> > > completed without giving any errors. We can not access the database and
> > > enterprise manager shows the status of the database as "Loading". I ran the
> > > command RESTORE DATABASE <databasename> WITH RECOVERY and received an error
> > > basically saying that the database was not completely loaded or the restore
> > > did not complete. Can anyone tell me what the problem is or tell me how to
> > > avoid this scenario?
> > >
> > > --
> > > -SJ

Tuesday, March 27, 2012

Database Snapshots Performance

Hi,
I'm developing a Data Mart and i'm experiencing a performance gap between my fact table and its snapshot.
I create snapshot with the istruction:

CREATE DATABASE DB_SNAP ON

( NAME = DB_SNAP_Data, FILENAME =

'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DB_SNAP_Data.ss' )

AS SNAPSHOT OF DB;

And it works. But executing queries on the snapshot result very "low".
Can anyone tell me why?

Tanks.

F.

There is a section in Books Online titled "How Database Snapshots Work" which shows the extra level of redirection for snapshots. For a newly created snapshot, the data will not be in cache so it might take a little while to pull the data into the buffer pool. Once it is "warmed up" though, the performance should not be that different.

You can look at the sys.dm_db_index_operational_stats and sys.dm_io_virtual_file_stats DMVs to try to determine where the issues are.

|||Yes, but I always have a "newly created snapshot". Infact, to update the snapshot, i need to drop and re-create. And i do this every day at least.|||

So, do the performance problems persist, or are they temporary until the cache is populated?

This will help narrow down where the problem may be.

Database Snapshots Performance

Hi,
I'm developing a Data Mart and i'm experiencing a performance gap between my fact table and its snapshot.
I create snapshot with the istruction:

CREATE DATABASE DB_SNAP ON

( NAME = DB_SNAP_Data, FILENAME =

'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DB_SNAP_Data.ss' )

AS SNAPSHOT OF DB;

And it works. But executing queries on the snapshot result very "low".
Can anyone tell me why?

Tanks.

F.

There is a section in Books Online titled "How Database Snapshots Work" which shows the extra level of redirection for snapshots. For a newly created snapshot, the data will not be in cache so it might take a little while to pull the data into the buffer pool. Once it is "warmed up" though, the performance should not be that different.

You can look at the sys.dm_db_index_operational_stats and sys.dm_io_virtual_file_stats DMVs to try to determine where the issues are.

|||Yes, but I always have a "newly created snapshot". Infact, to update the snapshot, i need to drop and re-create. And i do this every day at least.|||

So, do the performance problems persist, or are they temporary until the cache is populated?

This will help narrow down where the problem may be.

Database Snapshots

Hi,
I'm developing a Data Mart and i'm experiencing a performance gap between my fact table and its snapshot.
I create snapshot with the istruction:

CREATE DATABASE DB_SNAP ON

( NAME = DB_SNAP_Data, FILENAME =

'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DB_SNAP_Data.ss' )

AS SNAPSHOT OF DB;

And it works. But executing queries on the snapshot result very "low".
Can anyone tell me why?

Tanks.

F.Can you please explain the term about 'low', is it the data size or data?|||

Satya SKJ wrote:

Can you please explain the term about 'low', is it the data size or data?

It was "Slow" and not "low". Sorry.
I mean the time to execute a query.|||

It is by design@.

Performance is reduced, due to increased I/O on the source database resulting from a copy-on-write operation to the snapshot every time a page is updated.

|||

Satya SKJ wrote:

It is by design@.

Performance is reduced, due to increased I/O on the source database resulting from a copy-on-write operation to the snapshot every time a page is updated.

Ok. I read that note.
But what does it mean "Performance is reduced"?
Query on the normal table executed in about 40 seconds; on the snapshot it's over 8 minutes...

Database Snapshot Performance

Hi,
I'm developing a Data Mart and i'm experiencing a performance gap
between my fact table and its snapshot.
I create snapshot with the istruction:
CREATE DATABASE DB_SNAP ON ( NAME = DB_SNAP_Data, FILENAME = 'C:
\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
\DB_SNAP_Data.ss' ) AS SNAPSHOT OF DB;
And it works. But executing queries on the snapshot result very
"Slow".
Can anyone tell me why?
Tanks.
F.
A snapshot is slow because it makes a copy of of modified data in tempdb.
Thus reads are scattered all over. The real question is why would you NEED
a snapshot of a fact table? This is non-standard DW practice AFAIK.
TheSQLGuru
President
Indicium Resources, Inc.
"Johnny" <xxx.johnny@.gmail.com> wrote in message
news:1171290125.043815.129030@.v33g2000cwv.googlegr oups.com...
> Hi,
> I'm developing a Data Mart and i'm experiencing a performance gap
> between my fact table and its snapshot.
> I create snapshot with the istruction:
> CREATE DATABASE DB_SNAP ON ( NAME = DB_SNAP_Data, FILENAME = 'C:
> \Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
> \DB_SNAP_Data.ss' ) AS SNAPSHOT OF DB;
> And it works. But executing queries on the snapshot result very
> "Slow".
> Can anyone tell me why?
> Tanks.
> F.
>

Database Snapshot Performance

Hi,
I'm developing a Data Mart and i'm experiencing a performance gap
between my fact table and its snapshot.
I create snapshot with the istruction:
CREATE DATABASE DB_SNAP ON ( NAME = DB_SNAP_Data, FILENAME = 'C:
\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
\DB_SNAP_Data.ss' ) AS SNAPSHOT OF DB;
And it works. But executing queries on the snapshot result very
"Slow".
Can anyone tell me why?
Tanks.
F.A snapshot is slow because it makes a copy of of modified data in tempdb.
Thus reads are scattered all over. The real question is why would you NEED
a snapshot of a fact table? This is non-standard DW practice AFAIK.
TheSQLGuru
President
Indicium Resources, Inc.
"Johnny" <xxx.johnny@.gmail.com> wrote in message
news:1171290125.043815.129030@.v33g2000cwv.googlegroups.com...
> Hi,
> I'm developing a Data Mart and i'm experiencing a performance gap
> between my fact table and its snapshot.
> I create snapshot with the istruction:
> CREATE DATABASE DB_SNAP ON ( NAME = DB_SNAP_Data, FILENAME = 'C:
> \Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
> \DB_SNAP_Data.ss' ) AS SNAPSHOT OF DB;
> And it works. But executing queries on the snapshot result very
> "Slow".
> Can anyone tell me why?
> Tanks.
> F.
>

Friday, February 17, 2012

Database Performance

Hello there,
I am experiencing performance problems with an aplication running over a SQL
Server 2000 Database. The server that holds SQL Server has 2.8 GB of RAM
memory. When there are just on or two users working, the aplication
performance is suportable, but when more users are using the aplication,
there picks when its almost impossible work normaly.
My question is: what can possible cause this loss of performance?
All workstations have an ODBC connection with 'sa' user. Can it be
important?
What can I do to troubleshoot this problem?
The are serveral important tables with many fields and many records. I think
that I have created indexes correctly.
Can anybody help me? Thanks.
Regards,
Marco Pais
Usually the causes are bad design, poorly written queries, and
poor/inadequate indexing. BOL has a lot of trouble-shooting information
under the topic "Troubleshooting". You probably want to look at blocking
first.
"Marco Pais" <mpaisIGNORE@.foruminformatica.pt> wrote in message
news:ebcvgSTBFHA.3644@.TK2MSFTNGP15.phx.gbl...
> Hello there,
> I am experiencing performance problems with an aplication running over a
SQL
> Server 2000 Database. The server that holds SQL Server has 2.8 GB of RAM
> memory. When there are just on or two users working, the aplication
> performance is suportable, but when more users are using the aplication,
> there picks when its almost impossible work normaly.
> My question is: what can possible cause this loss of performance?
> All workstations have an ODBC connection with 'sa' user. Can it be
> important?
> What can I do to troubleshoot this problem?
> The are serveral important tables with many fields and many records. I
think
> that I have created indexes correctly.
> Can anybody help me? Thanks.
> Regards,
> Marco Pais
>
|||There are lots of things it can be and you need to narrow down the
possibilities. Blocking does indeed sound like a good possibility. Try
running sp_who2 when you have multiple users in the slow state and see if
any show up with a spid in the Blcked by column. These articles should help
to get you going in the right direction.
http://www.microsoft.com/sql/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Marco Pais" <mpaisIGNORE@.foruminformatica.pt> wrote in message
news:ebcvgSTBFHA.3644@.TK2MSFTNGP15.phx.gbl...
> Hello there,
> I am experiencing performance problems with an aplication running over a
> SQL Server 2000 Database. The server that holds SQL Server has 2.8 GB of
> RAM memory. When there are just on or two users working, the aplication
> performance is suportable, but when more users are using the aplication,
> there picks when its almost impossible work normaly.
> My question is: what can possible cause this loss of performance?
> All workstations have an ODBC connection with 'sa' user. Can it be
> important?
> What can I do to troubleshoot this problem?
> The are serveral important tables with many fields and many records. I
> think that I have created indexes correctly.
> Can anybody help me? Thanks.
> Regards,
> Marco Pais
>

Database Performance

Hello there,
I am experiencing performance problems with an aplication running over a SQL
Server 2000 Database. The server that holds SQL Server has 2.8 GB of RAM
memory. When there are just on or two users working, the aplication
performance is suportable, but when more users are using the aplication,
there picks when its almost impossible work normaly.
My question is: what can possible cause this loss of performance?
All workstations have an ODBC connection with 'sa' user. Can it be
important?
What can I do to troubleshoot this problem?
The are serveral important tables with many fields and many records. I think
that I have created indexes correctly.
Can anybody help me? Thanks.
Regards,
Marco Pais
Please don't post independently in separate newsgroups. You can add
multiple newsgroups to the header and then all the answers appear as one.
See my reply in the other newsgroup.
Andrew J. Kelly SQL MVP
"Marco Pais" <mpaisIGNORE@.foruminformatica.pt> wrote in message
news:u$%23YoSTBFHA.3592@.TK2MSFTNGP09.phx.gbl...
> Hello there,
> I am experiencing performance problems with an aplication running over a
> SQL Server 2000 Database. The server that holds SQL Server has 2.8 GB of
> RAM memory. When there are just on or two users working, the aplication
> performance is suportable, but when more users are using the aplication,
> there picks when its almost impossible work normaly.
> My question is: what can possible cause this loss of performance?
> All workstations have an ODBC connection with 'sa' user. Can it be
> important?
> What can I do to troubleshoot this problem?
> The are serveral important tables with many fields and many records. I
> think that I have created indexes correctly.
> Can anybody help me? Thanks.
> Regards,
> Marco Pais
>
|||Considering that the problem seems to occur as more users login, first read
up on:
sp_who2 and blocking SPIDs
transaction isolation level
To optimize the runtime of queries:
Analyzing Execution Plans
Also, go to msdn.com and search on "sql server" and "optimization"
"Marco Pais" <mpaisIGNORE@.foruminformatica.pt> wrote in message
news:u$%23YoSTBFHA.3592@.TK2MSFTNGP09.phx.gbl...
> Hello there,
> I am experiencing performance problems with an aplication running over a
SQL
> Server 2000 Database. The server that holds SQL Server has 2.8 GB of RAM
> memory. When there are just on or two users working, the aplication
> performance is suportable, but when more users are using the aplication,
> there picks when its almost impossible work normaly.
> My question is: what can possible cause this loss of performance?
> All workstations have an ODBC connection with 'sa' user. Can it be
> important?
> What can I do to troubleshoot this problem?
> The are serveral important tables with many fields and many records. I
think
> that I have created indexes correctly.
> Can anybody help me? Thanks.
> Regards,
> Marco Pais
>

Database performance

Hello there,
I am experiencing performance problems with an aplication running over a SQL
Server 2000 Database. The server that holds SQL Server has 2.8 GB of RAM
memory. When there are just on or two users working, the aplication
performance is suportable, but when more users are using the aplication,
there picks when its almost impossible work normaly.
My question is: what can possible cause this loss of performance?
All workstations have an ODBC connection with 'sa' user. Can it be
important?
What can I do to troubleshoot this problem?
The are serveral important tables with many fields and many records. I think
that I have created indexes correctly.
Can anybody help me? Thanks.
Regards,
Marco Pais
Take a look here:
http://support.microsoft.com/default...b;en-us;224587

> All workstations have an ODBC connection with 'sa' user. Can it be
> important?
Yes it's very important, although it has nothing to do with
performance. If user applications connect as a system administrator
then security is non-existent - one rogue user, faulty piece of code or
malicious hack could destroy your database and your server.
David Portas
SQL Server MVP
|||"Marco Pais" <mpaisIGNORE@.foruminformatica.pt> wrote in message
news:OVwy8STBFHA.2012@.TK2MSFTNGP15.phx.gbl...
> Hello there,
> I am experiencing performance problems with an aplication running over a
SQL
> Server 2000 Database. The server that holds SQL Server has 2.8 GB of RAM
> memory. When there are just on or two users working, the aplication
> performance is suportable, but when more users are using the aplication,
> there picks when its almost impossible work normaly.
This is almost certainly a code problem. It could be the application is
keeping locks for far longer than necessary. It could be your queries have
changed enough that your indices don't work as well.

> My question is: what can possible cause this loss of performance?
> All workstations have an ODBC connection with 'sa' user. Can it be
> important?
As David mentions, this is a HUGE security risk. Not only can users do
anything to the db, they can do anything to the OS.
With the XP_CMDSHELL command they can create domain users, add them to the
domain admin group and take over your system.

> What can I do to troubleshoot this problem?
Simplest, look at output of sp_who2 active and look for blocking. If it's
there consistently, that's your biggest problem. Eliminate it.
Use dbcc inputbuffer(spid) on the blocking spid to see what the code looks
like.
If this doesn't help, time to break out profiler.

> The are serveral important tables with many fields and many records. I
think
> that I have created indexes correctly.
> Can anybody help me? Thanks.
> Regards,
> Marco Pais
>

Database Performance

Hello there,
I am experiencing performance problems with an aplication running over a SQL
Server 2000 Database. The server that holds SQL Server has 2.8 GB of RAM
memory. When there are just on or two users working, the aplication
performance is suportable, but when more users are using the aplication,
there picks when its almost impossible work normaly.
My question is: what can possible cause this loss of performance?
All workstations have an ODBC connection with 'sa' user. Can it be
important?
What can I do to troubleshoot this problem?
The are serveral important tables with many fields and many records. I think
that I have created indexes correctly.
Can anybody help me? Thanks.
Regards,
Marco PaisPlease don't post independently in separate newsgroups. You can add
multiple newsgroups to the header and then all the answers appear as one.
See my reply in the other newsgroup.
Andrew J. Kelly SQL MVP
"Marco Pais" <mpaisIGNORE@.foruminformatica.pt> wrote in message
news:u$%23YoSTBFHA.3592@.TK2MSFTNGP09.phx.gbl...
> Hello there,
> I am experiencing performance problems with an aplication running over a
> SQL Server 2000 Database. The server that holds SQL Server has 2.8 GB of
> RAM memory. When there are just on or two users working, the aplication
> performance is suportable, but when more users are using the aplication,
> there picks when its almost impossible work normaly.
> My question is: what can possible cause this loss of performance?
> All workstations have an ODBC connection with 'sa' user. Can it be
> important?
> What can I do to troubleshoot this problem?
> The are serveral important tables with many fields and many records. I
> think that I have created indexes correctly.
> Can anybody help me? Thanks.
> Regards,
> Marco Pais
>|||Considering that the problem seems to occur as more users login, first read
up on:
sp_who2 and blocking SPIDs
transaction isolation level
To optimize the runtime of queries:
Analyzing Execution Plans
Also, go to msdn.com and search on "sql server" and "optimization"
"Marco Pais" <mpaisIGNORE@.foruminformatica.pt> wrote in message
news:u$%23YoSTBFHA.3592@.TK2MSFTNGP09.phx.gbl...
> Hello there,
> I am experiencing performance problems with an aplication running over a
SQL
> Server 2000 Database. The server that holds SQL Server has 2.8 GB of RAM
> memory. When there are just on or two users working, the aplication
> performance is suportable, but when more users are using the aplication,
> there picks when its almost impossible work normaly.
> My question is: what can possible cause this loss of performance?
> All workstations have an ODBC connection with 'sa' user. Can it be
> important?
> What can I do to troubleshoot this problem?
> The are serveral important tables with many fields and many records. I
think
> that I have created indexes correctly.
> Can anybody help me? Thanks.
> Regards,
> Marco Pais
>

Database performance

Hello there,
I am experiencing performance problems with an aplication running over a SQL
Server 2000 Database. The server that holds SQL Server has 2.8 GB of RAM
memory. When there are just on or two users working, the aplication
performance is suportable, but when more users are using the aplication,
there picks when its almost impossible work normaly.
My question is: what can possible cause this loss of performance?
All workstations have an ODBC connection with 'sa' user. Can it be
important?
What can I do to troubleshoot this problem?
The are serveral important tables with many fields and many records. I think
that I have created indexes correctly.
Can anybody help me? Thanks.
Regards,
Marco PaisTake a look here:
http://support.microsoft.com/default.aspx?scid=kb;en-us;224587
> All workstations have an ODBC connection with 'sa' user. Can it be
> important?
Yes it's very important, although it has nothing to do with
performance. If user applications connect as a system administrator
then security is non-existent - one rogue user, faulty piece of code or
malicious hack could destroy your database and your server.
--
David Portas
SQL Server MVP
--|||"Marco Pais" <mpaisIGNORE@.foruminformatica.pt> wrote in message
news:OVwy8STBFHA.2012@.TK2MSFTNGP15.phx.gbl...
> Hello there,
> I am experiencing performance problems with an aplication running over a
SQL
> Server 2000 Database. The server that holds SQL Server has 2.8 GB of RAM
> memory. When there are just on or two users working, the aplication
> performance is suportable, but when more users are using the aplication,
> there picks when its almost impossible work normaly.
This is almost certainly a code problem. It could be the application is
keeping locks for far longer than necessary. It could be your queries have
changed enough that your indices don't work as well.
> My question is: what can possible cause this loss of performance?
> All workstations have an ODBC connection with 'sa' user. Can it be
> important?
As David mentions, this is a HUGE security risk. Not only can users do
anything to the db, they can do anything to the OS.
With the XP_CMDSHELL command they can create domain users, add them to the
domain admin group and take over your system.
> What can I do to troubleshoot this problem?
Simplest, look at output of sp_who2 active and look for blocking. If it's
there consistently, that's your biggest problem. Eliminate it.
Use dbcc inputbuffer(spid) on the blocking spid to see what the code looks
like.
If this doesn't help, time to break out profiler.
> The are serveral important tables with many fields and many records. I
think
> that I have created indexes correctly.
> Can anybody help me? Thanks.
> Regards,
> Marco Pais
>

Database performance

Hello there,
I am experiencing performance problems with an aplication running over a SQL
Server 2000 Database. The server that holds SQL Server has 2.8 GB of RAM
memory. When there are just on or two users working, the aplication
performance is suportable, but when more users are using the aplication,
there picks when its almost impossible work normaly.
My question is: what can possible cause this loss of performance?
All workstations have an ODBC connection with 'sa' user. Can it be
important?
What can I do to troubleshoot this problem?
The are serveral important tables with many fields and many records. I think
that I have created indexes correctly.
Can anybody help me? Thanks.
Regards,
Marco Pais
Giving them "sa" access unless it is "absolutely" necessary
is just plain insane from a security perspective. However,
that doesn't address your problem.
Can you give us some guidance on whether you have
indexed your tables, how large they are, when was the
last time the hard drive was defragged, when was the
last time you reindexed the indexes.
There could be a ton of things that need to be addressed.
Sounds like you are a bit of a beginner to managing SQL
Server databases, these free flash video presentations might
help:
http://www.eggheadcafe.com/videodemo/
2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net
http://www.mastervb.net
"Marco Pais" <mpaisIGNORE@.foruminformatica.pt> wrote in message
news:%23UPi1STBFHA.1656@.TK2MSFTNGP11.phx.gbl...
> Hello there,
> I am experiencing performance problems with an aplication running over a
> SQL Server 2000 Database. The server that holds SQL Server has 2.8 GB of
> RAM memory. When there are just on or two users working, the aplication
> performance is suportable, but when more users are using the aplication,
> there picks when its almost impossible work normaly.
> My question is: what can possible cause this loss of performance?
> All workstations have an ODBC connection with 'sa' user. Can it be
> important?
> What can I do to troubleshoot this problem?
> The are serveral important tables with many fields and many records. I
> think that I have created indexes correctly.
> Can anybody help me? Thanks.
> Regards,
> Marco Pais
>

Database performance

Hello there,
I am experiencing performance problems with an aplication running over a SQL
Server 2000 Database. The server that holds SQL Server has 2.8 GB of RAM
memory. When there are just on or two users working, the aplication
performance is suportable, but when more users are using the aplication,
there picks when its almost impossible work normaly.
My question is: what can possible cause this loss of performance?
All workstations have an ODBC connection with 'sa' user. Can it be
important?
What can I do to troubleshoot this problem?
The are serveral important tables with many fields and many records. I think
that I have created indexes correctly.
Can anybody help me? Thanks.
Regards,
Marco PaisGiving them "sa" access unless it is "absolutely" necessary
is just plain insane from a security perspective. However,
that doesn't address your problem.
Can you give us some guidance on whether you have
indexed your tables, how large they are, when was the
last time the hard drive was defragged, when was the
last time you reindexed the indexes.
There could be a ton of things that need to be addressed.
Sounds like you are a bit of a beginner to managing SQL
Server databases, these free flash video presentations might
help:
http://www.eggheadcafe.com/videodemo/
2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net
http://www.mastervb.net
"Marco Pais" <mpaisIGNORE@.foruminformatica.pt> wrote in message
news:%23UPi1STBFHA.1656@.TK2MSFTNGP11.phx.gbl...
> Hello there,
> I am experiencing performance problems with an aplication running over a
> SQL Server 2000 Database. The server that holds SQL Server has 2.8 GB of
> RAM memory. When there are just on or two users working, the aplication
> performance is suportable, but when more users are using the aplication,
> there picks when its almost impossible work normaly.
> My question is: what can possible cause this loss of performance?
> All workstations have an ODBC connection with 'sa' user. Can it be
> important?
> What can I do to troubleshoot this problem?
> The are serveral important tables with many fields and many records. I
> think that I have created indexes correctly.
> Can anybody help me? Thanks.
> Regards,
> Marco Pais
>