Performance really poor if calling data from sql clients

Hello,
I have installed SQL2005 developer edition on a laptop (2 GB RAM), VS2005 on
another computer. The database is quite small (nearly no data or some test
data). The performance is still really bad:
xxx each time you open the server explorer in VS2005 and you use "show table
data" (takes at least 20 sec., sometimes time out).
xxx each time I read from tables within my application (I'm not reading the
full table, I'm selecting the data, number of records < 5)
What can be done in order to determine the performance consuming
element/object? The network the computer are attached consists of less than 5
users (1 GBit backbone).
Are there any configuration aspects which can help to improove the
performance?
Network pack size = 4096
Max. number of concurrent connections = 0
Boost SQL server priority is not checked
Max. server memory = 2 GB
Thanks,
Stephan
I can't vouch for any of the VS components when it comes to performance
since I don't use them for database access. But you should be able to use
profiler to see what is happening on the sql server end. Also what does the
query plan look like? Is it optimal? Did you run sp_updatestats?
Andrew J. Kelly SQL MVP
"Stephan (Germany)" <StephanGermany@.discussions.microsoft.com> wrote in
message news:FB05EA03-F277-4FBE-B1AB-A7C8C767B41D@.microsoft.com...
> Hello,
> I have installed SQL2005 developer edition on a laptop (2 GB RAM), VS2005
> on
> another computer. The database is quite small (nearly no data or some test
> data). The performance is still really bad:
> xxx each time you open the server explorer in VS2005 and you use "show
> table
> data" (takes at least 20 sec., sometimes time out).
> xxx each time I read from tables within my application (I'm not reading
> the
> full table, I'm selecting the data, number of records < 5)
> What can be done in order to determine the performance consuming
> element/object? The network the computer are attached consists of less
> than 5
> users (1 GBit backbone).
> Are there any configuration aspects which can help to improove the
> performance?
> Network pack size = 4096
> Max. number of concurrent connections = 0
> Boost SQL server priority is not checked
> Max. server memory = 2 GB
> Thanks,
> Stephan
|||Hello Andrew,
good hint with the profiler.
You know I'm "fighting" since a while to follow the documented
recommendation of using TCP instead of Named Pipes to get performance.
I see now if I using Named Pipes connection in VS2005, everything is going
faster in VS2005 (and I assume this will also happen for my application if I
do the same).
So, what is the problem with TCP as connection type? Already known issues by
Microsoft SQL developer teams?
Yours
Stephan
"Andrew J. Kelly" wrote:

> I can't vouch for any of the VS components when it comes to performance
> since I don't use them for database access. But you should be able to use
> profiler to see what is happening on the sql server end. Also what does the
> query plan look like? Is it optimal? Did you run sp_updatestats?
> --
> Andrew J. Kelly SQL MVP
>
> "Stephan (Germany)" <StephanGermany@.discussions.microsoft.com> wrote in
> message news:FB05EA03-F277-4FBE-B1AB-A7C8C767B41D@.microsoft.com...
>
>
|||Ok, the performance is not stable:
it is true that with named pipes. it is going a bit faster, but each time I
select another table to be displayed, it is still waiting for something I
cannot see even in the profiler. There is an overhead I can't actually find
out.
Any idea?
Yours
Stephan
"Stephan (Germany)" wrote:
[vbcol=seagreen]
> Hello Andrew,
> good hint with the profiler.
> You know I'm "fighting" since a while to follow the documented
> recommendation of using TCP instead of Named Pipes to get performance.
> I see now if I using Named Pipes connection in VS2005, everything is going
> faster in VS2005 (and I assume this will also happen for my application if I
> do the same).
> So, what is the problem with TCP as connection type? Already known issues by
> Microsoft SQL developer teams?
> Yours
> Stephan
> "Andrew J. Kelly" wrote:
|||Running of sp_updatestats fails. Error: stats_ver_current is not a built-in
function.
"Andrew J. Kelly" wrote:

> I can't vouch for any of the VS components when it comes to performance
> since I don't use them for database access. But you should be able to use
> profiler to see what is happening on the sql server end. Also what does the
> query plan look like? Is it optimal? Did you run sp_updatestats?
> --
> Andrew J. Kelly SQL MVP
>
> "Stephan (Germany)" <StephanGermany@.discussions.microsoft.com> wrote in
> message news:FB05EA03-F277-4FBE-B1AB-A7C8C767B41D@.microsoft.com...
>
>
|||What is the compatibility level of the db? If it was a db that was upgraded
it may be in 80 compatibility mode.
Andrew J. Kelly SQL MVP
"Stephan (Germany)" <StephanGermany@.discussions.microsoft.com> wrote in
message news:19B125B0-21FA-45E4-AD16-E464D6FFC3E6@.microsoft.com...[vbcol=seagreen]
> Running of sp_updatestats fails. Error: stats_ver_current is not a
> built-in
> function.
> "Andrew J. Kelly" wrote:
|||If you run the same query from SSIS on the server does it run fast or slow?
Sounds like network issues or problems with name resolution.
Andrew J. Kelly SQL MVP
"Stephan (Germany)" <StephanGermany@.discussions.microsoft.com> wrote in
message news:E1285720-7132-4B56-B754-B98E9594F466@.microsoft.com...[vbcol=seagreen]
> Ok, the performance is not stable:
> it is true that with named pipes. it is going a bit faster, but each time
> I
> select another table to be displayed, it is still waiting for something I
> cannot see even in the profiler. There is an overhead I can't actually
> find
> out.
> Any idea?
> Yours
> Stephan
> "Stephan (Germany)" wrote:
|||Hello Andrew,
what do you mean with SSIS ?
I have checked DNS, WINS and address resolution. The laptop where the
database is running can be found via ping and nslookup.
If I make another test (creating new logins in SQL management studio), it is
not possible to see any other domain rather than the laptop itself, this is a
bit confusing.
Maybe it has nothing to do with my problem but it is not really good.
"Andrew J. Kelly" wrote:

> If you run the same query from SSIS on the server does it run fast or slow?
> Sounds like network issues or problems with name resolution.
> --
> Andrew J. Kelly SQL MVP
>
> "Stephan (Germany)" <StephanGermany@.discussions.microsoft.com> wrote in
> message news:E1285720-7132-4B56-B754-B98E9594F466@.microsoft.com...
>
>
|||The db was installed completely new. I have uninstalled SQL 2000 first.
But how can I find out finally which compatibility mode the SQL server is
running now? I see no property in management studio nor server configuration
manager.
Yours
Stephan
"Andrew J. Kelly" wrote:

> What is the compatibility level of the db? If it was a db that was upgraded
> it may be in 80 compatibility mode.
> --
> Andrew J. Kelly SQL MVP
>
> "Stephan (Germany)" <StephanGermany@.discussions.microsoft.com> wrote in
> message news:19B125B0-21FA-45E4-AD16-E464D6FFC3E6@.microsoft.com...
>
>
|||Maybe this can help you:
the most time consuming period (waiting time) will arise if I connect the
server. If I pause a while and send another SQL command, the reestablishing
of the connection (same connecting string) needs again a while and then I can
fire different sql commands which will be executed in an acceptable speed.
Yours
Stephan
"Andrew J. Kelly" wrote:

> What is the compatibility level of the db? If it was a db that was upgraded
> it may be in 80 compatibility mode.
> --
> Andrew J. Kelly SQL MVP
>
> "Stephan (Germany)" <StephanGermany@.discussions.microsoft.com> wrote in
> message news:19B125B0-21FA-45E4-AD16-E464D6FFC3E6@.microsoft.com...
>
>
sql

Performance question - Restarting the SQL server resolved my timeout problem

We have an ASP application installed on one powerful Win 2003 and
the SQL Server on another Win 2003.

We have two copies of the application (identical, one for production
and one for test purposes) that are operational. The 2nd application
is connected to the same SQL Server but a test copy of the production
database.

Today, running the application, a place where it shows a list of records,
the ASP application was timing out after 30 seconds for a lot of users.

I backed up the production db and restored it over the test db and tested
the ASP application; within 10-15 seconds the ASP page loaded the list
of the records, so i didn't get any timeout. So i started to think that
maybe
the test db when it was being restored, SQL Server would do some kind of
data cleanup and defragment the new db.

But, then i checked the Task Manager, SQL Server was almost using 1.2GB
of memory and the server had only 100megs available.

As soon as i restarted the SQL Server, the ASP page did no longer timeout
on the production database.

So now i am trying to figure out why the SQL Server restart fixed my
problem?

Unfortunately i did not check if there were a lot of connections in the
Process info
under the Current Activity in SQL EM.

Do you think there were a lot of sessions that weren't killed by the
application?
And that was causing the timeout? But why only for the Production db and not
also
for the test db?

I mean, i am puzzled as to why before restarting the SQL Server, running the
ASP
application connected to the TEST database did not time out!

As always, I appreciate any feedback, comments.

Thank you

I've the same problem.

"serge" <sergea@.nospam.ehmail.com> ha scritto nel messaggio
news:x70cd.60064$3C6.2287125@.news20.bellglobal.com ...
> We have an ASP application installed on one powerful Win 2003 and
> the SQL Server on another Win 2003.
> As always, I appreciate any feedback, comments.
> Thank you

On one machine, sometimes my VB applications get timed out.
The only solution to avoid this is to shut down all the applications running
on the DB,
Stopping the SQL Server service and then restarting it.
After this operation, the applications start to run fine.
I'm sure I've no connection or server side recordset open that could use for
memory leak since I've tested software monitoring every operations and do
not leave any recordset or connection open.
I always destroy them, even in case of system or application errors, there
is always the on error statement calling for the recordset and connection
destruction (the set cn (or rs) = Nothing).
I do not know why, but I suppose is due to ..... lack of memory on the
server (I've to use a 256 MB computer as SQLServer).
Any idea would be very usefull also for me.

performance problem...

hello ng
i've got a problem with my fulltext-index, or with the performance of the
sql-server.
in one database i've installed a fulltext-index. this index based on a often
scheduled job, because it's nessessary to find the entrys directly after the
insert. the schedule has a frequence of 1 minute...
on the same sql-server i've got an other database. in this db i like to
insert many entrys on the same time.
the index works and the other db works too. but the inserts are very slow
when i start the index-job. the sql-server runs on a 4 processor system with
2gb of memory, and the processor has more capacity than i use (max 20%). the
harddisk is also very fast (scsi).
inserts per second: up to 300
inserts per secon with index running: up to 40
what can i do to run both things on same time?
do i have to change some settings for the index-job?
thanx. simon
Simon,
Could you post the full output of the below SQL code as this information is
very helpful in troubleshooting SQL FTS issues?
use <your_database_name>
select @.@.version
select @.@.language
select count(*) from your_FT-enabled_table_name
If you're are using SQL Server 2000, does your FT-enable table have "Change
Tracking" with "Update Index in Background" enabled or are you using a
frequently scheduled Incremental Population? Depending upon the size of your
table and if you're using SQL 2000, it might be best to use CT with UIiB.
Also, could you provide information on where your FT Catalog folder is (what
drive and type of drive, i.e., RAID level) relative to your database files
(*.mdf & *.ldf)?
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Simon Steiner" <anybody@.anonymous.com> wrote in message
news:egYJaMdFFHA.228@.TK2MSFTNGP15.phx.gbl...
> hello ng
> i've got a problem with my fulltext-index, or with the performance of the
> sql-server.
> in one database i've installed a fulltext-index. this index based on a
often
> scheduled job, because it's nessessary to find the entrys directly after
the
> insert. the schedule has a frequence of 1 minute...
> on the same sql-server i've got an other database. in this db i like to
> insert many entrys on the same time.
> the index works and the other db works too. but the inserts are very slow
> when i start the index-job. the sql-server runs on a 4 processor system
with
> 2gb of memory, and the processor has more capacity than i use (max 20%).
the
> harddisk is also very fast (scsi).
> inserts per second: up to 300
> inserts per secon with index running: up to 40
> what can i do to run both things on same time?
> do i have to change some settings for the index-job?
> thanx. simon
>
|||Here's the Output from SQL:
version: Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition
on Windows NT 5.2 (Build 3790: )
language: us_english
row_count: 10137
The catalog is placed on "D:\Program Files\Microsoft SQL
Server\MSSQL\FTDATA" on the same disk like the Server is running...
The size of the catalog is 6MB
I'm using a frequently scheduled Incremental Population. Could that be a
reason to switch to "Update Index in Background"?
Simon
"John Kane" <jt-kane@.comcast.net> schrieb im Newsbeitrag
news:ucYBJXdFFHA.2832@.TK2MSFTNGP14.phx.gbl...
> Simon,
> Could you post the full output of the below SQL code as this information
is
> very helpful in troubleshooting SQL FTS issues?
> use <your_database_name>
> select @.@.version
> select @.@.language
> select count(*) from your_FT-enabled_table_name
> If you're are using SQL Server 2000, does your FT-enable table have
"Change
> Tracking" with "Update Index in Background" enabled or are you using a
> frequently scheduled Incremental Population? Depending upon the size of
your
> table and if you're using SQL 2000, it might be best to use CT with UIiB.
> Also, could you provide information on where your FT Catalog folder is
(what[vbcol=seagreen]
> drive and type of drive, i.e., RAID level) relative to your database files
> (*.mdf & *.ldf)?
> Thanks,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Simon Steiner" <anybody@.anonymous.com> wrote in message
> news:egYJaMdFFHA.228@.TK2MSFTNGP15.phx.gbl...
the[vbcol=seagreen]
> often
> the
slow
> with
> the
>
|||uups.
The Size of the *.ldf-file ist: 235MB and the *.mdf has 27.9MB
The HD-Type is a seagate scsi drive and there is a RAID0 installed... so
there are 2 disks.
simon
"John Kane" <jt-kane@.comcast.net> schrieb im Newsbeitrag
news:ucYBJXdFFHA.2832@.TK2MSFTNGP14.phx.gbl...
> Simon,
> Could you post the full output of the below SQL code as this information
is
> very helpful in troubleshooting SQL FTS issues?
> use <your_database_name>
> select @.@.version
> select @.@.language
> select count(*) from your_FT-enabled_table_name
> If you're are using SQL Server 2000, does your FT-enable table have
"Change
> Tracking" with "Update Index in Background" enabled or are you using a
> frequently scheduled Incremental Population? Depending upon the size of
your
> table and if you're using SQL 2000, it might be best to use CT with UIiB.
> Also, could you provide information on where your FT Catalog folder is
(what[vbcol=seagreen]
> drive and type of drive, i.e., RAID level) relative to your database files
> (*.mdf & *.ldf)?
> Thanks,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Simon Steiner" <anybody@.anonymous.com> wrote in message
> news:egYJaMdFFHA.228@.TK2MSFTNGP15.phx.gbl...
the[vbcol=seagreen]
> often
> the
slow
> with
> the
>
|||Thanks, Simon,
Q. Could that be a reason to switch to "Update Index in Background"?
A. Yes! As you're using SQL 2000 SP3 on Win2003, you can enable CT with UIiB
and disenable or delete your frequently running Incremental Population job
and I'd highly recommend this.
Additionally, you should drop and re-create the FT Catalog and re-create it
on your server's non-system disk drive (usually c:\) and separate from your
database files. So, if you have another drive, say the E:\ and your
databases are on the D:\ (where SQL Server is installed), I'd put the FT
Catalog on the E:\ drive assuming it is the RAID0 drive. The primary reason
for this is to avoid the read/write I/O contention between the FT Catalog
and the database files during FT Indexing.
Hope that helps,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Simon Steiner" <anybody@.anonymous.com> wrote in message
news:OI9NUqdFFHA.3908@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> uups.
> The Size of the *.ldf-file ist: 235MB and the *.mdf has 27.9MB
> The HD-Type is a seagate scsi drive and there is a RAID0 installed... so
> there are 2 disks.
> simon
>
> "John Kane" <jt-kane@.comcast.net> schrieb im Newsbeitrag
> news:ucYBJXdFFHA.2832@.TK2MSFTNGP14.phx.gbl...
> is
> "Change
> your
UIiB.[vbcol=seagreen]
> (what
files[vbcol=seagreen]
> the
after[vbcol=seagreen]
to[vbcol=seagreen]
> slow
system[vbcol=seagreen]
20%).
>

Performance problem with SQL Server Express

My current database is running in MSDE.I setup a new server and installed SQL Server 2005 Express.I then copied the database to the new server and attached it.After testing the new server, I noticed that one of my stored procedures that uses a forward_only keyset cursor is running about ten times slower.I’ve compared all the setting between the two servers and everything looks good.I also installed a trial copy of SQL Server 2005 on a spare PC and ran the same test.Again, the stored procedure is running about ten times slower.I’m not experiencing any other performance problems.

Any help would be appreciated.

Thanks

You might want to ask this question on the SQL Server Database Engine forum. I don't expect the Express edition to be any different from the standard one for cursor functionality.

Thanks
Laurentiu

Performance problem after sql2k sp4 installed

We'w got major performance problems with inserting data into large tables
(more than 25 000 000 rows) after installing the latest sp. The performance
decreased with more than 90%.
Our program takes one row at the time and creates inserts and updates for
aproximatly 7 tables out of this. Everyr read row is encapsuled in an
transaction.
occassionaly we can capture an error during insert. The error is 1222.
Have anybody encounted the same problem an even got any solution.
ThanksHi
1222 = "Lock request time out period exceeded."
Run profiler and see what is happening on your server during the loads.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Stefan W" wrote:

> We'w got major performance problems with inserting data into large tables
> (more than 25 000 000 rows) after installing the latest sp. The performanc
e
> decreased with more than 90%.
> Our program takes one row at the time and creates inserts and updates for
> aproximatly 7 tables out of this. Everyr read row is encapsuled in an
> transaction.
>
> occassionaly we can capture an error during insert. The error is 1222.
>
> Have anybody encounted the same problem an even got any solution.
> Thanks|||Hi!
I've tried to do that, but i cant get information from the particular job.
The job is desiged as a stored procedure that contains a loop that calls
other stored procedures. Iv'e tried using the different templates provided i
n
the profiler but a don't se any of the underlying procedurecalls made in thi
s
job. Do you have any suggestions on how to set up the profiler to get hand o
n
these jobs!
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> 1222 = "Lock request time out period exceeded."
> Run profiler and see what is happening on your server during the loads.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Stefan W" wrote:
>|||I will suggest to roll back to SP3a.
We have have many problems with SP4 as many other people; check other posts
related to SP4 and you will follow my advice.
Cheers, Massimo
Software Engineer
TCF - Amsterdam (NL)
"Stefan W" wrote:
[vbcol=seagreen]
> Hi!
> I've tried to do that, but i cant get information from the particular job.
> The job is desiged as a stored procedure that contains a loop that calls
> other stored procedures. Iv'e tried using the different templates provided
in
> the profiler but a don't se any of the underlying procedurecalls made in t
his
> job. Do you have any suggestions on how to set up the profiler to get hand
on
> these jobs!
>
>
> "Mike Epprecht (SQL MVP)" wrote:
>|||Hi
Massimo, have you raised a case with Microsoft PSS? If not, you should.
There have been a few posts on performance issues, and they had mostly to do
with a change in the way data type precedence is handled in SP4. I have 78
servers that are on SP4 with 2040 hotfix and none exhibit performance
problems.
Looks to be very application specific.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Massimo" <Massimo@.discussions.microsoft.com> wrote in message
news:640255E5-1885-4210-B9A1-32641D9C17A6@.microsoft.com...[vbcol=seagreen]
>I will suggest to roll back to SP3a.
> We have have many problems with SP4 as many other people; check other
> posts
> related to SP4 and you will follow my advice.
> Cheers, Massimo
> --
> Software Engineer
> TCF - Amsterdam (NL)
>
> "Stefan W" wrote:
>

Performance problem after sql2k sp4 installed

We'w got major performance problems with inserting data into large tables
(more than 25 000 000 rows) after installing the latest sp. The performance
decreased with more than 90%.
Our program takes one row at the time and creates inserts and updates for
aproximatly 7 tables out of this. Everyr read row is encapsuled in an
transaction.
occassionaly we can capture an error during insert. The error is 1222.
Have anybody encounted the same problem an even got any solution.
Thanks
Hi
1222 = "Lock request time out period exceeded."
Run profiler and see what is happening on your server during the loads.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Stefan W" wrote:

> We'w got major performance problems with inserting data into large tables
> (more than 25 000 000 rows) after installing the latest sp. The performance
> decreased with more than 90%.
> Our program takes one row at the time and creates inserts and updates for
> aproximatly 7 tables out of this. Everyr read row is encapsuled in an
> transaction.
>
> occassionaly we can capture an error during insert. The error is 1222.
>
> Have anybody encounted the same problem an even got any solution.
> Thanks
|||Hi!
I've tried to do that, but i cant get information from the particular job.
The job is desiged as a stored procedure that contains a loop that calls
other stored procedures. Iv'e tried using the different templates provided in
the profiler but a don't se any of the underlying procedurecalls made in this
job. Do you have any suggestions on how to set up the profiler to get hand on
these jobs!
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> 1222 = "Lock request time out period exceeded."
> Run profiler and see what is happening on your server during the loads.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Stefan W" wrote:
|||I will suggest to roll back to SP3a.
We have have many problems with SP4 as many other people; check other posts
related to SP4 and you will follow my advice.
Cheers, Massimo
Software Engineer
TCF - Amsterdam (NL)
"Stefan W" wrote:
[vbcol=seagreen]
> Hi!
> I've tried to do that, but i cant get information from the particular job.
> The job is desiged as a stored procedure that contains a loop that calls
> other stored procedures. Iv'e tried using the different templates provided in
> the profiler but a don't se any of the underlying procedurecalls made in this
> job. Do you have any suggestions on how to set up the profiler to get hand on
> these jobs!
>
>
> "Mike Epprecht (SQL MVP)" wrote:
|||Hi
Massimo, have you raised a case with Microsoft PSS? If not, you should.
There have been a few posts on performance issues, and they had mostly to do
with a change in the way data type precedence is handled in SP4. I have 78
servers that are on SP4 with 2040 hotfix and none exhibit performance
problems.
Looks to be very application specific.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Massimo" <Massimo@.discussions.microsoft.com> wrote in message
news:640255E5-1885-4210-B9A1-32641D9C17A6@.microsoft.com...[vbcol=seagreen]
>I will suggest to roll back to SP3a.
> We have have many problems with SP4 as many other people; check other
> posts
> related to SP4 and you will follow my advice.
> Cheers, Massimo
> --
> Software Engineer
> TCF - Amsterdam (NL)
>
> "Stefan W" wrote:

Performance problem after sql2k sp4 installed

We'w got major performance problems with inserting data into large tables
(more than 25 000 000 rows) after installing the latest sp. The performance
decreased with more than 90%.
Our program takes one row at the time and creates inserts and updates for
aproximatly 7 tables out of this. Everyr read row is encapsuled in an
transaction.
occassionaly we can capture an error during insert. The error is 1222.
Have anybody encounted the same problem an even got any solution.
ThanksHi
1222 = "Lock request time out period exceeded."
Run profiler and see what is happening on your server during the loads.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Stefan W" wrote:
> We'w got major performance problems with inserting data into large tables
> (more than 25 000 000 rows) after installing the latest sp. The performance
> decreased with more than 90%.
> Our program takes one row at the time and creates inserts and updates for
> aproximatly 7 tables out of this. Everyr read row is encapsuled in an
> transaction.
>
> occassionaly we can capture an error during insert. The error is 1222.
>
> Have anybody encounted the same problem an even got any solution.
> Thanks|||Hi!
I've tried to do that, but i cant get information from the particular job.
The job is desiged as a stored procedure that contains a loop that calls
other stored procedures. Iv'e tried using the different templates provided in
the profiler but a don't se any of the underlying procedurecalls made in this
job. Do you have any suggestions on how to set up the profiler to get hand on
these jobs!
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> 1222 = "Lock request time out period exceeded."
> Run profiler and see what is happening on your server during the loads.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Stefan W" wrote:
> > We'w got major performance problems with inserting data into large tables
> > (more than 25 000 000 rows) after installing the latest sp. The performance
> > decreased with more than 90%.
> >
> > Our program takes one row at the time and creates inserts and updates for
> > aproximatly 7 tables out of this. Everyr read row is encapsuled in an
> > transaction.
> >
> >
> > occassionaly we can capture an error during insert. The error is 1222.
> >
> >
> > Have anybody encounted the same problem an even got any solution.
> >
> > Thanks|||I will suggest to roll back to SP3a.
We have have many problems with SP4 as many other people; check other posts
related to SP4 and you will follow my advice.
Cheers, Massimo
--
Software Engineer
TCF - Amsterdam (NL)
"Stefan W" wrote:
> Hi!
> I've tried to do that, but i cant get information from the particular job.
> The job is desiged as a stored procedure that contains a loop that calls
> other stored procedures. Iv'e tried using the different templates provided in
> the profiler but a don't se any of the underlying procedurecalls made in this
> job. Do you have any suggestions on how to set up the profiler to get hand on
> these jobs!
>
>
> "Mike Epprecht (SQL MVP)" wrote:
> > Hi
> >
> > 1222 = "Lock request time out period exceeded."
> >
> > Run profiler and see what is happening on your server during the loads.
> >
> > Regards
> > --
> > Mike Epprecht, Microsoft SQL Server MVP
> > Zurich, Switzerland
> >
> > MVP Program: http://www.microsoft.com/mvp
> >
> > Blog: http://www.msmvps.com/epprecht/
> >
> >
> >
> > "Stefan W" wrote:
> >
> > > We'w got major performance problems with inserting data into large tables
> > > (more than 25 000 000 rows) after installing the latest sp. The performance
> > > decreased with more than 90%.
> > >
> > > Our program takes one row at the time and creates inserts and updates for
> > > aproximatly 7 tables out of this. Everyr read row is encapsuled in an
> > > transaction.
> > >
> > >
> > > occassionaly we can capture an error during insert. The error is 1222.
> > >
> > >
> > > Have anybody encounted the same problem an even got any solution.
> > >
> > > Thanks|||Hi
Massimo, have you raised a case with Microsoft PSS? If not, you should.
There have been a few posts on performance issues, and they had mostly to do
with a change in the way data type precedence is handled in SP4. I have 78
servers that are on SP4 with 2040 hotfix and none exhibit performance
problems.
Looks to be very application specific.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Massimo" <Massimo@.discussions.microsoft.com> wrote in message
news:640255E5-1885-4210-B9A1-32641D9C17A6@.microsoft.com...
>I will suggest to roll back to SP3a.
> We have have many problems with SP4 as many other people; check other
> posts
> related to SP4 and you will follow my advice.
> Cheers, Massimo
> --
> Software Engineer
> TCF - Amsterdam (NL)
>
> "Stefan W" wrote:
>> Hi!
>> I've tried to do that, but i cant get information from the particular
>> job.
>> The job is desiged as a stored procedure that contains a loop that calls
>> other stored procedures. Iv'e tried using the different templates
>> provided in
>> the profiler but a don't se any of the underlying procedurecalls made in
>> this
>> job. Do you have any suggestions on how to set up the profiler to get
>> hand on
>> these jobs!
>>
>>
>> "Mike Epprecht (SQL MVP)" wrote:
>> > Hi
>> >
>> > 1222 = "Lock request time out period exceeded."
>> >
>> > Run profiler and see what is happening on your server during the loads.
>> >
>> > Regards
>> > --
>> > Mike Epprecht, Microsoft SQL Server MVP
>> > Zurich, Switzerland
>> >
>> > MVP Program: http://www.microsoft.com/mvp
>> >
>> > Blog: http://www.msmvps.com/epprecht/
>> >
>> >
>> >
>> > "Stefan W" wrote:
>> >
>> > > We'w got major performance problems with inserting data into large
>> > > tables
>> > > (more than 25 000 000 rows) after installing the latest sp. The
>> > > performance
>> > > decreased with more than 90%.
>> > >
>> > > Our program takes one row at the time and creates inserts and updates
>> > > for
>> > > aproximatly 7 tables out of this. Everyr read row is encapsuled in an
>> > > transaction.
>> > >
>> > >
>> > > occassionaly we can capture an error during insert. The error is
>> > > 1222.
>> > >
>> > >
>> > > Have anybody encounted the same problem an even got any solution.
>> > >
>> > > Thanks

Performance on Windows 2003 Standard w/ SQL SERVER 2000 Enterprise

I have SQL SERVER 2K Enterprise SP4 installed on a Windows 2003 standard box
with 4G of memory and 2 XEON CPUs with hyperthreading. All the SQL SERVER
configuration is set to use all available resource on the box. But no matter
what we throw at it. It's using only 25% of CPU resource and the memory
usuage is never over 1.7G. The installation is definately Enterprise and not
standard. What am I missing?
Thanks!WD wrote:
> I have SQL SERVER 2K Enterprise SP4 installed on a Windows 2003 standard box
> with 4G of memory and 2 XEON CPUs with hyperthreading. All the SQL SERVER
> configuration is set to use all available resource on the box. But no matter
> what we throw at it. It's using only 25% of CPU resource and the memory
> usuage is never over 1.7G. The installation is definately Enterprise and not
> standard. What am I missing?
> Thanks!
The CPU utilization may mean nothing. Did you set the BOOT.INI switches
for the additional memory?
http://support.microsoft.com/kb/274750/
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Yes, I read the support article before, but because my installation is on a
windows 2003 server. The article indicates that it already supports up to 4
GB for Windows 2003 server and only need to modify the boot.ini to use more
than 4GB. Am I wrong in reading it that way. Thanks.
"Tracy McKibben" wrote:
> WD wrote:
> > I have SQL SERVER 2K Enterprise SP4 installed on a Windows 2003 standard box
> > with 4G of memory and 2 XEON CPUs with hyperthreading. All the SQL SERVER
> > configuration is set to use all available resource on the box. But no matter
> > what we throw at it. It's using only 25% of CPU resource and the memory
> > usuage is never over 1.7G. The installation is definately Enterprise and not
> > standard. What am I missing?
> >
> > Thanks!
> The CPU utilization may mean nothing. Did you set the BOOT.INI switches
> for the additional memory?
> http://support.microsoft.com/kb/274750/
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

Performance on Windows 2003 Standard w/ SQL SERVER 2000 Enterprise

I have SQL SERVER 2K Enterprise SP4 installed on a Windows 2003 standard box
with 4G of memory and 2 XEON CPUs with hyperthreading. All the SQL SERVER
configuration is set to use all available resource on the box. But no matter
what we throw at it. It's using only 25% of CPU resource and the memory
usuage is never over 1.7G. The installation is definately Enterprise and not
standard. What am I missing?
Thanks!WD wrote:
> I have SQL SERVER 2K Enterprise SP4 installed on a Windows 2003 standard b
ox
> with 4G of memory and 2 XEON CPUs with hyperthreading. All the SQL SERVER
> configuration is set to use all available resource on the box. But no matt
er
> what we throw at it. It's using only 25% of CPU resource and the memory
> usuage is never over 1.7G. The installation is definately Enterprise and n
ot
> standard. What am I missing?
> Thanks!
The CPU utilization may mean nothing. Did you set the BOOT.INI switches
for the additional memory?
http://support.microsoft.com/kb/274750/
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Performance object list is missing

Hi, After i installed SP3 the list of Performance Object of the Performance console is missing and the logs of SQL show "Performance monitor shared memory setup failed: -1".
Best Regards
Manuel
FIX: "Performance Monitor Shared Memory Setup Failed: -1" Error Message When
You Start SQL Server
http://support.microsoft.com/default...812915&sd=tech
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Manuel" <Manuel@.discussions.microsoft.com> wrote in message
news:81930B3B-A7FB-4118-A6F6-FA3E15B6154A@.microsoft.com...
> Hi, After i installed SP3 the list of Performance Object of the
Performance console is missing and the logs of SQL show "Performance monitor
shared memory setup failed: -1".
> --
> Best Regards
> Manuel
|||Hi, i forgot to tell that i have a virtual instance of SQL and the fix you tell me doesn't work for this issue.
Thanks
Manuel
Best Regards
Manuel
"Jasper Smith" wrote:

> FIX: "Performance Monitor Shared Memory Setup Failed: -1" Error Message When
> You Start SQL Server
> http://support.microsoft.com/default...812915&sd=tech
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Manuel" <Manuel@.discussions.microsoft.com> wrote in message
> news:81930B3B-A7FB-4118-A6F6-FA3E15B6154A@.microsoft.com...
> Performance console is missing and the logs of SQL show "Performance monitor
> shared memory setup failed: -1".
>
>

Performance object list is missing

Hi, After i installed SP3 the list of Performance Object of the Performance console is missing and the logs of SQL show "Performance monitor shared memory setup failed: -1".
--
Best Regards
ManuelFIX: "Performance Monitor Shared Memory Setup Failed: -1" Error Message When
You Start SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;812915&sd=tech
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Manuel" <Manuel@.discussions.microsoft.com> wrote in message
news:81930B3B-A7FB-4118-A6F6-FA3E15B6154A@.microsoft.com...
> Hi, After i installed SP3 the list of Performance Object of the
Performance console is missing and the logs of SQL show "Performance monitor
shared memory setup failed: -1".
> --
> Best Regards
> Manuel

Performance object list is missing

Hi, After i installed SP3 the list of Performance Object of the Performance
console is missing and the logs of SQL show "Performance monitor shared memo
ry setup failed: -1".
--
Best Regards
ManuelFIX: "Performance Monitor Shared Memory Setup Failed: -1" Error Message When
You Start SQL Server
http://support.microsoft.com/defaul...;812915&sd=tech
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Manuel" <Manuel@.discussions.microsoft.com> wrote in message
news:81930B3B-A7FB-4118-A6F6-FA3E15B6154A@.microsoft.com...
> Hi, After i installed SP3 the list of Performance Object of the
Performance console is missing and the logs of SQL show "Performance monitor
shared memory setup failed: -1".
> --
> Best Regards
> Manuel|||Hi, i forgot to tell that i have a virtual instance of SQL and the fix you t
ell me doesn't work for this issue.
Thanks
Manuel
Best Regards
Manuel
"Jasper Smith" wrote:

> FIX: "Performance Monitor Shared Memory Setup Failed: -1" Error Message Wh
en
> You Start SQL Server
> http://support.microsoft.com/defaul...;812915&sd=tech
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Manuel" <Manuel@.discussions.microsoft.com> wrote in message
> news:81930B3B-A7FB-4118-A6F6-FA3E15B6154A@.microsoft.com...
> Performance console is missing and the logs of SQL show "Performance monit
or
> shared memory setup failed: -1".
>
>

Performance monitor How to change server

I'm using SQL Server 2000.
On my desktop is Win XP Pro
I have Enterprise manager, Performance Monitor and SQL profiler
installed.
Each time I start performance monitor, the server monitored is the
local server.
How do I change the server I want to monitor? I've searched all the
menus, did right clicks, etc. and I can't change the server.
Thanks,
DWUnderneth the graph there is a histogram showing what you
are watching, it has the menu Color, Scale, Counter,
Instance ect.
Right Click on it and select 'Add Counter'
After that its fairly intuitive ;)
Peter
peternolan67REMOVETHIS@.hotmail.com
"I may be drunk, Miss, but in the morning I will be sober
and you will still be ugly."
Winston Churchill
>--Original Message--
>I'm using SQL Server 2000.
>On my desktop is Win XP Pro
>I have Enterprise manager, Performance Monitor and SQL
profiler
>installed.
>Each time I start performance monitor, the server
monitored is the
>local server.
>How do I change the server I want to monitor? I've
searched all the
>menus, did right clicks, etc. and I can't change the
server.
>Thanks,
>DW
>.
>