performance monitoring: application

Showing posts with label application. Show all posts

Friday, March 30, 2012

Performance Tweaks

I have a client that is having some performance issues with sql 2000
database. The application seems to move slow on the server as well as the
workstations so it does seem like a network or workstation issue. It seems
to be a sql issue. The server is a decent server with high P3 processor, 1
gig of memory, RAID 5 scsi drive array running 10k rpm. At this point they
are only "testing" this application, only a hand full of users are using
app. Any suggestions on performance tweaks would be great. They had a
puesdo-administrator that forced sql to use 500 megs of the 1 gig in the
server... other than that its all default.
thanks for the help,
rob
MOve the log files to somewhere which is mirrored and on a different
physical drive than the data...Also the Data files for your database should
be on a different physical drive than master, model, tempdb.
This sets you up in a position where you can get up to the minute recovery
with no loss of data (using the backup with notruncate option.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Microsoft" <temp@.dstek.com> wrote in message
news:e%23Yb%23b6VEHA.3120@.TK2MSFTNGP12.phx.gbl...
> I have a client that is having some performance issues with sql 2000
> database. The application seems to move slow on the server as well as the
> workstations so it does seem like a network or workstation issue. It
seems
> to be a sql issue. The server is a decent server with high P3 processor,
1
> gig of memory, RAID 5 scsi drive array running 10k rpm. At this point
they
> are only "testing" this application, only a hand full of users are using
> app. Any suggestions on performance tweaks would be great. They had a
> puesdo-administrator that forced sql to use 500 megs of the 1 gig in the
> server... other than that its all default.
> thanks for the help,
> rob
>
|||maybe these links could help him in troubleshooting --
SQL Server 7.0 Performance Tuning Guide :
http://www.microsoft.com/SQL/techinf...tuninguide.htm
Proper SQL Server Configuration Settings :
http://support.microsoft.com/support.../Q166/9/67.ASP
Q175658 - How to gather Information for effective troubleshooting of
Performance :
http://support.microsoft.com/support.../Q175/6/58.ASP
Q224587 INF: Troubleshooting Application Performance with SQL Server
http://support.microsoft.com/support.../q224/5/87.asp
SQL Server Query Performance Troubleshooter :
http://support.microsoft.com/support...7queryperf.asp
Index Tuning Wizard 2000
http://msdn.microsoft.com/library/techart/itwforsql.htm
Troubleshooting the Index Tuning Wizard
http://msdn.microsoft.com/library/ps...tools_48ro.htm
Designing an Index
http://msdn.microsoft.com/library/ps...es_05_2ri0.htm
Thanks,
Vikram
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Performance Tweaks

I have a client that is having some performance issues with sql 2000
database. The application seems to move slow on the server as well as the
workstations so it does seem like a network or workstation issue. It seems
to be a sql issue. The server is a decent server with high P3 processor, 1
gig of memory, RAID 5 scsi drive array running 10k rpm. At this point they
are only "testing" this application, only a hand full of users are using
app. Any suggestions on performance tweaks would be great. They had a
puesdo-administrator that forced sql to use 500 megs of the 1 gig in the
server... other than that its all default.
thanks for the help,
robMOve the log files to somewhere which is mirrored and on a different
physical drive than the data...Also the Data files for your database should
be on a different physical drive than master, model, tempdb.
This sets you up in a position where you can get up to the minute recovery
with no loss of data (using the backup with notruncate option.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Microsoft" <temp@.dstek.com> wrote in message
news:e%23Yb%23b6VEHA.3120@.TK2MSFTNGP12.phx.gbl...
> I have a client that is having some performance issues with sql 2000
> database. The application seems to move slow on the server as well as the
> workstations so it does seem like a network or workstation issue. It
seems
> to be a sql issue. The server is a decent server with high P3 processor,
1
> gig of memory, RAID 5 scsi drive array running 10k rpm. At this point
they
> are only "testing" this application, only a hand full of users are using
> app. Any suggestions on performance tweaks would be great. They had a
> puesdo-administrator that forced sql to use 500 megs of the 1 gig in the
> server... other than that its all default.
> thanks for the help,
> rob
>|||maybe these links could help him in troubleshooting --
SQL Server 7.0 Performance Tuning Guide :
http://www.microsoft.com/SQL/techinfo/perftuninguide.htm
Proper SQL Server Configuration Settings :
http://support.microsoft.com/support/kb/articles/Q166/9/67.ASP
Q175658 - How to gather Information for effective troubleshooting of
Performance :
http://support.microsoft.com/support/kb/articles/Q175/6/58.ASP
Q224587 INF: Troubleshooting Application Performance with SQL Server
http://support.microsoft.com/support/kb/articles/q224/5/87.asp
SQL Server Query Performance Troubleshooter :
http://support.microsoft.com/support/tshoot/sql7queryperf.asp
Index Tuning Wizard 2000
http://msdn.microsoft.com/library/techart/itwforsql.htm
Troubleshooting the Index Tuning Wizard
http://msdn.microsoft.com/library/psdk/sql/tr_servtools_48ro.htm
Designing an Index
http://msdn.microsoft.com/library/psdk/sql/cm_8_des_05_2ri0.htm
Thanks,
Vikram
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.sql

Performance Tweaks

I have a client that is having some performance issues with sql 2000
database. The application seems to move slow on the server as well as the
workstations so it does seem like a network or workstation issue. It seems
to be a sql issue. The server is a decent server with high P3 processor, 1
gig of memory, RAID 5 scsi drive array running 10k rpm. At this point they
are only "testing" this application, only a hand full of users are using
app. Any suggestions on performance tweaks would be great. They had a
puesdo-administrator that forced sql to use 500 megs of the 1 gig in the
server... other than that its all default.
thanks for the help,
rob500 MB memory is not much for SQL Server, however, it all depends on how
big is your database and how many concurrent users, it may be enough, it
may be not. Open perfmon to check if there's any paging going on on the
server. Also, do you have correct index? How did you configure your data
files? Is your application using alot stored procedures? Are those
stored procedures very CPU intensive?
Without any detail, it's hard to determine what's going on.
--
Eric Li
SQL DBA
MCDBA
Microsoft wrote:
> I have a client that is having some performance issues with sql 2000
> database. The application seems to move slow on the server as well as the
> workstations so it does seem like a network or workstation issue. It seems
> to be a sql issue. The server is a decent server with high P3 processor, 1
> gig of memory, RAID 5 scsi drive array running 10k rpm. At this point they
> are only "testing" this application, only a hand full of users are using
> app. Any suggestions on performance tweaks would be great. They had a
> puesdo-administrator that forced sql to use 500 megs of the 1 gig in the
> server... other than that its all default.
> thanks for the help,
> rob
>
>|||RAID 5 is slow for log files.
"Microsoft" <temp@.dstek.com> wrote in message
news:eB9SBc6VEHA.3120@.TK2MSFTNGP12.phx.gbl...
> I have a client that is having some performance issues with sql 2000
> database. The application seems to move slow on the server as well as the
> workstations so it does seem like a network or workstation issue. It
seems
> to be a sql issue. The server is a decent server with high P3 processor,
1
> gig of memory, RAID 5 scsi drive array running 10k rpm. At this point
they
> are only "testing" this application, only a hand full of users are using
> app. Any suggestions on performance tweaks would be great. They had a
> puesdo-administrator that forced sql to use 500 megs of the 1 gig in the
> server... other than that its all default.
> thanks for the help,
> rob
>
>|||so are 10k drives.
you'll need to capture perfmon data and see where your bottlenecks are.
usual suspects.
RAM - 500mb is low and 1GB could be
CPU - Maybe you need additional CPUs
IO - RAID 5 with 10k Drives....that is rather slow. You may be Queueing up
IO requests
Indexes, bad sprocs, excessive blocking, deadlocks, etc
lots of work to do to figure this out
Greg Jackson
PDX, Oregon

Wednesday, March 28, 2012

Performance sqlclient data provider

Well, I hope, after my endless search in the web... maybe, somebody can help me here.

I'm passing a application to a web-based application, therefore I have to use ADO.net to access a sql server DB. But the performance is extremly poor!!!!

Just to compare: with the normal application or with the SQL Query Analyzer, the query needs about 2 seconds. Well, by using ado.net, it's about 200 seconds.....!!!!

Of course, the query is quite long. But the difference is extrem... too extrem for using the same query...

I already tried a lot... actually everything I found on the web. What is really strange is that neither the processor nor the memory is fully used... And the web server use more of the cpu power than the sql server itself (the 2 server are still on my machine where I'm programming). So, could it be that the problem is by filling up the dataset with the sqladapter or something similar?

Thx for your help!!

Two words, "stored procedure". If you query is long you're passing to the DB each time expensive in so many ways. Plus make sure you're re-using the connection by passing in exactly the same details...actually just use a stored procedure.

|||Thx pkr! I already read that point to improve the performance and I tried it. Unfortunately, it's not the solution. May be, it increases a little bit the performance, but it seems that the problem is elsewhere...

What I found out till now:
- it's not the hardware
- it's not the connection
- it's not the amount of data sent between the web server and the db server

actually, other queries are executed quite fast and without this performance leak.

so, quite possible is that the problem is the query itself. Only the fact, that the execution time of the query by the query analyzer is 100x faster than over ADO.net can't be normal. There has to be something ... but where?

Any idea?

also, with other queries, the performance of ADO.net and the query analyzer is almost the same. So, where does this performance leak coming from?
|||thx rtk! It's not the first time I've read about the stored procedure and I tried it. Unfortunately, it's not the solution to my problem. It gives my an improvement, but just a tiny one.

The problem has to be elsewhere...

What I found out until now:
- it's not the hardware
- it's not the connection
- it's not the amount of data sent between the web server and the db server

So, it sounds like it's the query itself. What is so strange is the performance difference between the sql query analyzer and the ado.net. The query analyer executes the query 100x faster than through ado.net. It's just that other queries execute fast through ado.net.

Here the query:

SELECT table1.col1, table2.col1, count(table2.col2) as counter
FROM table3,table1, table2
WHERE table3.Valid = 1
AND table3.SessionId = table2.SessionId
AND table2.col4 *= table1.col2
AND table2.col3 IN ('01','02','04')
AND table3.SessionId IN (
SELECT SessionId
FROM userTable
WHERE UserName = 'userABC' )
GROUP BY table1.col1, table2.col1

comment: I know the "*=" is not the nicest solution (should be a right outer join), but I used it in other queries too and it executes fast.

any idea, what I can improve... or if it's really the query, how to write the query new?|||by the way, here the query:

comment: I know the "*=" is not the nicest solution (should be a rightouter join), but I used it in other queries too and it executes fast.

any idea, what I can improve... or if it's really the query, how to write the query new?|||If you're telling me that the self same query as a stored procedure is 100x slower than in QA then the only thing I can think of is that it's the initial compile time. So in QA in DBCC FreeProcCache and Drop Clean Buffers then run the QA again. This should put the two mechanisms on a level playing field. That wold then suggest that the query is too complicated (although it doesn't look it). I would also move to use "inner join" syntax and remove the sub query.|||I didn't understand exactly what you meant with the FreeProcCache. But isn't it a modification in the QA. It can't be that.

What I also thought of is that the priority of a .net data provider is not that high like when a processing the same query with the QA? Could that be? How to change?

I will try to use the inner join. Actually I did some research on the query and there are 2 parts which ones are slowing down the whole thing.
1. the join... of course
2. the IN ('02', ...)

But the point is, that there has to be something different because of having this huge performance difference between QA and ado.net with the same query. So, how does ad.net handle the query exactly? Where are the diferences between the 2 methods? ... There has to be something and it is really important to find that for me.

Thanks for you help, anyway!!|||

First, create the stored procedure. Then call it (the stored procedure) from both QA and ADO.NET and see what the time difference is.

I'd recommend updating your SQL Syntax as well, but that shouldn't make a difference between the two.

To your question, yes, there are certain differences between the connection settings be default in QA and ADO.NET. One or more of these settings may be the culprit.

|||

Given a level playing field there are only a few minor differences between QA and a sqlClient call. The typical pitful of using QA is to do with the way SQL caches its query, and to some extent its data. I'll exaggerate to make the point. Consider you've developed a query that is complicated for SQL to compile a plan for, remember that a plan is cached for each user. In QA it take 60 secs to compile and cache the plan. So next time you run the query from QA the cache is ready and although the query is complex, it only take 1 second to run. Eureka you shout, and you make the call from your ADO client. However, you've decided to use a different connection string per user, e.g. via NT Authentication. So each time a new user runs the query for the first time you incur the 60 sec overhead.

The "free proc cache" et al, remove all the caches of procedures and data so next time you run the query in QA you can see if any of the above is the problem. They are command you can issue from QA, look up "DBCC" in the help for information on how to run them (they're very simple)...although don't do it on a production server!

sql

Performance SQL Server in remote access

I am having problems with SQL Server performance in remote access.
When I use him through a local net
with an application VB is satisfactory, but when
I try to access through a connection ADSL
of 512KB (speedy) it is slow. Former. to open
a screen is long 5 minutes.
The same application with PostgreSql delays nemos of 15 seconds..
It will be that some exists configuration in SQL Server to optimize a remote
access for certain IPS
I thank allI made some simulations with the cursor on the customer's side,
I increased salary size, but didn't give difference none.
Obs.: the application is already optimized to obtain only
the necessary columns, it always accesses for the key it would excel
or indexes, etc...
The interesting is that for PostgreSQL the time of answer
it is very good in accesses through the internet. Perhaps
be for him to have the native access for IP.
I thank the collaboration
"Tony Rogerson" <tonyrogerson@.sqlserver.eu.com> escreveu na mensagem
news:eVcCdGOYDHA.888@.TK2MSFTNGP10.phx.gbl...
> Make sure you are using a client side cursor - check out my presentation
for
> more information on application cursors and their performance
> comparisons...
> http://www.sqlserverfaq.com?eid=20
> --
> Tony Rogerson
> SQL Server MVP
> http://www.sqlserverfaq.com?mbr=21
> (Create your own groups, Forum, FAQ's and a ton more)
>

Performance slow in Vista. Please help.

I have an application I'm trying to run under Vista using SQL 2005 as the
backend database. Right now the application is running super slow and I dont
know why. If I run SQL 2005 under a Virtual machine running xp, the app runs
fine but if I run the application with SQL 2005 under vista performance is
slow.
Configuration:
Vista all patches according to Windows Update
SQL 2005 - SP2
Doesnt work
Vista - running SQL 2005 in a VM under XP
Works
Can someone point me in the correct direction? Thanks in advance!!
MikeWhen an SQL-Server application is running slow, the first thing to do is to
make sure that the statistics are up to date by using sp_updatestats stored
procedure (or use UPDATE STATISTICS is you want to work on a more detailed
level) and cleaning the caches after that:
DBCC FLUSHPROCINDB
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
(See
http://sqlknowledge.com/index.php?option=com_content&task=view&id=65&Itemid=41 )
.
If this doesn't work, you can try reindexing everything:
http://blog.sqlauthority.com/2007/01/31/sql-server-reindexing-database-tables-and-update-statistics-on-tables/
Finally, things like using the option WITH RECOMPILE or playing with the
parameters sniffing (search this newsgroup with Google for more details) can
help you; as well as other things related to optimizing any sql-server
application.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:0EE6E1F8-2CEF-4B06-AE2B-B19F01D9C954@.microsoft.com...
>I have an application I'm trying to run under Vista using SQL 2005 as the
> backend database. Right now the application is running super slow and I
> dont
> know why. If I run SQL 2005 under a Virtual machine running xp, the app
> runs
> fine but if I run the application with SQL 2005 under vista performance is
> slow.
> Configuration:
> Vista all patches according to Windows Update
> SQL 2005 - SP2
> Doesnt work
>
> Vista - running SQL 2005 in a VM under XP
> Works
> Can someone point me in the correct direction? Thanks in advance!!
> Mike|||I have the same problem and I believe it has nothing to do with SQL Server
maintenance. It look to me that Vista has a bug when running SQL Server 2005.
I am running a .NET 2.0 winform application on a Vista machine. The app runs
about 200 updates on a single table. When updating the Vista SQL Server 2005
instance the time is more then double comparing to the time to complete when
updating the XP SQL Server 2005 instance. Plus the Vista machine is Intel
Core 2 Quad, while the XP machine is Intel Pentium D.
Alex
"Sylvain Lafontaine" wrote:
> When an SQL-Server application is running slow, the first thing to do is to
> make sure that the statistics are up to date by using sp_updatestats stored
> procedure (or use UPDATE STATISTICS is you want to work on a more detailed
> level) and cleaning the caches after that:
> DBCC FLUSHPROCINDB
> DBCC DROPCLEANBUFFERS
> DBCC FREEPROCCACHE
> (See
> http://sqlknowledge.com/index.php?option=com_content&task=view&id=65&Itemid=41 )
> ..
> If this doesn't work, you can try reindexing everything:
> http://blog.sqlauthority.com/2007/01/31/sql-server-reindexing-database-tables-and-update-statistics-on-tables/
> Finally, things like using the option WITH RECOMPILE or playing with the
> parameters sniffing (search this newsgroup with Google for more details) can
> help you; as well as other things related to optimizing any sql-server
> application.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:0EE6E1F8-2CEF-4B06-AE2B-B19F01D9C954@.microsoft.com...
> >I have an application I'm trying to run under Vista using SQL 2005 as the
> > backend database. Right now the application is running super slow and I
> > dont
> > know why. If I run SQL 2005 under a Virtual machine running xp, the app
> > runs
> > fine but if I run the application with SQL 2005 under vista performance is
> > slow.
> >
> > Configuration:
> >
> > Vista all patches according to Windows Update
> > SQL 2005 - SP2
> >
> > Doesnt work
> >
> >
> > Vista - running SQL 2005 in a VM under XP
> >
> > Works
> >
> > Can someone point me in the correct direction? Thanks in advance!!
> >
> > Mike
>
>

Performance sanity check

Hi all,
I hope someone will be kind enough to have a look over this for me and
let me know if I'm correct.
We bought an application to do timetabling a couple of years ago. Last
year we had terrible problems with system performance, during the busy
period in August/September the system became basically unusable. We
tracked that down to disk performance, and after toying with using the
SAN, eventually went for Solid State Disk (RamSan 300) after some
careful analysis of the SAN. We also moved the application up to a new
4x3GHz Xeon machine with 4GB RAM.
We've just run a simulated load test to see if we think the system will
be able to stand up to the load this year and are very concerned about
the results. The load test had 20 users in it.
All IO/Memory/Paging/Compilation counters etc seemed to show the server
wasn't stretched, however the CPU load went to 100% and stayed there for
the duration. The number of Page locks got to 70,000 (last year it was
230,000 + so we've improved something!) and we saw several blocking
chains with the lead blocker in a 'sleeping' state. The wait times for
the blocked processes got to in excess of 500 seconds.
The suppliers say we need a bigger server, my concern, since we're on a
far bigger server is that the app is just highly inefficient and any
size server will get swamped by it...
Following the load test I've been looking at the Profiler tool. The
output seems to show the app uses server side cursors (we've noticed
some very heavy IO on tempdb that seems to support this). I dimly
remember reading something in the past that high CPU can be a symptom of
high lock counts rather than a cause of it - is this right?
Any help or advice will be much appreciated :-)
cheers
daveDave
Try run SQL Server Profiler to identify a long running queries/stored
procedures (look at DURATION ) , so once you have identified them , take a
look at how you casn improve it , may be adding indexes to the table or
somethinmg else
Looking at what you gave provided it seems that the APP is using cursors
(blocking/cursors) which is really bad in terms of performance
Speek to the vendor to improve the app
"Dave Thornley" <cisdht@.yahoo.com> wrote in message
news:uWzgAdxrGHA.2256@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> I hope someone will be kind enough to have a look over this for me and let
> me know if I'm correct.
> We bought an application to do timetabling a couple of years ago. Last
> year we had terrible problems with system performance, during the busy
> period in August/September the system became basically unusable. We
> tracked that down to disk performance, and after toying with using the
> SAN, eventually went for Solid State Disk (RamSan 300) after some careful
> analysis of the SAN. We also moved the application up to a new 4x3GHz Xeon
> machine with 4GB RAM.
> We've just run a simulated load test to see if we think the system will be
> able to stand up to the load this year and are very concerned about the
> results. The load test had 20 users in it.
> All IO/Memory/Paging/Compilation counters etc seemed to show the server
> wasn't stretched, however the CPU load went to 100% and stayed there for
> the duration. The number of Page locks got to 70,000 (last year it was
> 230,000 + so we've improved something!) and we saw several blocking chains
> with the lead blocker in a 'sleeping' state. The wait times for the
> blocked processes got to in excess of 500 seconds.
> The suppliers say we need a bigger server, my concern, since we're on a
> far bigger server is that the app is just highly inefficient and any size
> server will get swamped by it...
> Following the load test I've been looking at the Profiler tool. The output
> seems to show the app uses server side cursors (we've noticed some very
> heavy IO on tempdb that seems to support this). I dimly remember reading
> something in the past that high CPU can be a symptom of high lock counts
> rather than a cause of it - is this right?
> Any help or advice will be much appreciated :-)
> cheers
> dave|||Hi Dave
High CPU can be the result of poor code and you may want to look at the lock
events such as lock escallation in SQL profiler. You can also use the
profiler to find out what the I/O intensive queries are see:
http://www.sql-server-performance.com/sql_server_performance_audit10.asp
You may then want to go back to the vendor and ask them to improve it.
Also check that if there are maintenance routines that will update your
indexes and statistics that they have successfully been run.
For blocking you may want to look at http://support.microsoft.com/kb/271509
HTH
John
"Dave Thornley" wrote:
> Hi all,
> I hope someone will be kind enough to have a look over this for me and
> let me know if I'm correct.
> We bought an application to do timetabling a couple of years ago. Last
> year we had terrible problems with system performance, during the busy
> period in August/September the system became basically unusable. We
> tracked that down to disk performance, and after toying with using the
> SAN, eventually went for Solid State Disk (RamSan 300) after some
> careful analysis of the SAN. We also moved the application up to a new
> 4x3GHz Xeon machine with 4GB RAM.
> We've just run a simulated load test to see if we think the system will
> be able to stand up to the load this year and are very concerned about
> the results. The load test had 20 users in it.
> All IO/Memory/Paging/Compilation counters etc seemed to show the server
> wasn't stretched, however the CPU load went to 100% and stayed there for
> the duration. The number of Page locks got to 70,000 (last year it was
> 230,000 + so we've improved something!) and we saw several blocking
> chains with the lead blocker in a 'sleeping' state. The wait times for
> the blocked processes got to in excess of 500 seconds.
> The suppliers say we need a bigger server, my concern, since we're on a
> far bigger server is that the app is just highly inefficient and any
> size server will get swamped by it...
> Following the load test I've been looking at the Profiler tool. The
> output seems to show the app uses server side cursors (we've noticed
> some very heavy IO on tempdb that seems to support this). I dimly
> remember reading something in the past that high CPU can be a symptom of
> high lock counts rather than a cause of it - is this right?
> Any help or advice will be much appreciated :-)
> cheers
> dave
>|||Hi Dave,
Since the application suffers from heavy locking/blocking you should
considder upgrading to SS2005 and use snapshot isolation.
ALTER DATABASE [S] SET read_committed_snapshot ON
Be sure that tempdb is big enough :-)
The application you are talking about sounds a lot like Axapta :-)
Do you reindex your clustered indexes from time to time - be sure there is a
clustered index on the tables?
You should look at the wait statistics for the different sessions - have you
heard about the YAPP method?
Good luck :-)
"Dave Thornley" wrote:
> Hi all,
> I hope someone will be kind enough to have a look over this for me and
> let me know if I'm correct.
> We bought an application to do timetabling a couple of years ago. Last
> year we had terrible problems with system performance, during the busy
> period in August/September the system became basically unusable. We
> tracked that down to disk performance, and after toying with using the
> SAN, eventually went for Solid State Disk (RamSan 300) after some
> careful analysis of the SAN. We also moved the application up to a new
> 4x3GHz Xeon machine with 4GB RAM.
> We've just run a simulated load test to see if we think the system will
> be able to stand up to the load this year and are very concerned about
> the results. The load test had 20 users in it.
> All IO/Memory/Paging/Compilation counters etc seemed to show the server
> wasn't stretched, however the CPU load went to 100% and stayed there for
> the duration. The number of Page locks got to 70,000 (last year it was
> 230,000 + so we've improved something!) and we saw several blocking
> chains with the lead blocker in a 'sleeping' state. The wait times for
> the blocked processes got to in excess of 500 seconds.
> The suppliers say we need a bigger server, my concern, since we're on a
> far bigger server is that the app is just highly inefficient and any
> size server will get swamped by it...
> Following the load test I've been looking at the Profiler tool. The
> output seems to show the app uses server side cursors (we've noticed
> some very heavy IO on tempdb that seems to support this). I dimly
> remember reading something in the past that high CPU can be a symptom of
> high lock counts rather than a cause of it - is this right?
> Any help or advice will be much appreciated :-)
> cheers
> dave
>|||Thanks for the help and suggestions guys.
davesql

Performance sanity check

Hi all,
I hope someone will be kind enough to have a look over this for me and
let me know if I'm correct.
We bought an application to do timetabling a couple of years ago. Last
year we had terrible problems with system performance, during the busy
period in August/September the system became basically unusable. We
tracked that down to disk performance, and after toying with using the
SAN, eventually went for Solid State Disk (RamSan 300) after some
careful analysis of the SAN. We also moved the application up to a new
4x3GHz Xeon machine with 4GB RAM.
We've just run a simulated load test to see if we think the system will
be able to stand up to the load this year and are very concerned about
the results. The load test had 20 users in it.
All IO/Memory/Paging/Compilation counters etc seemed to show the server
wasn't stretched, however the CPU load went to 100% and stayed there for
the duration. The number of Page locks got to 70,000 (last year it was
230,000 + so we've improved something!) and we saw several blocking
chains with the lead blocker in a 'sleeping' state. The wait times for
the blocked processes got to in excess of 500 seconds.
The suppliers say we need a bigger server, my concern, since we're on a
far bigger server is that the app is just highly inefficient and any
size server will get swamped by it...
Following the load test I've been looking at the Profiler tool. The
output seems to show the app uses server side cursors (we've noticed
some very heavy IO on tempdb that seems to support this). I dimly
remember reading something in the past that high CPU can be a symptom of
high lock counts rather than a cause of it - is this right?
Any help or advice will be much appreciated :-)
cheers
daveDave
Try run SQL Server Profiler to identify a long running queries/stored
procedures (look at DURATION ) , so once you have identified them , take a
look at how you casn improve it , may be adding indexes to the table or
somethinmg else
Looking at what you gave provided it seems that the APP is using cursors
(blocking/cursors) which is really bad in terms of performance
Speek to the vendor to improve the app
"Dave Thornley" <cisdht@.yahoo.com> wrote in message
news:uWzgAdxrGHA.2256@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> I hope someone will be kind enough to have a look over this for me and let
> me know if I'm correct.
> We bought an application to do timetabling a couple of years ago. Last
> year we had terrible problems with system performance, during the busy
> period in August/September the system became basically unusable. We
> tracked that down to disk performance, and after toying with using the
> SAN, eventually went for Solid State Disk (RamSan 300) after some careful
> analysis of the SAN. We also moved the application up to a new 4x3GHz Xeon
> machine with 4GB RAM.
> We've just run a simulated load test to see if we think the system will be
> able to stand up to the load this year and are very concerned about the
> results. The load test had 20 users in it.
> All IO/Memory/Paging/Compilation counters etc seemed to show the server
> wasn't stretched, however the CPU load went to 100% and stayed there for
> the duration. The number of Page locks got to 70,000 (last year it was
> 230,000 + so we've improved something!) and we saw several blocking chains
> with the lead blocker in a 'sleeping' state. The wait times for the
> blocked processes got to in excess of 500 seconds.
> The suppliers say we need a bigger server, my concern, since we're on a
> far bigger server is that the app is just highly inefficient and any size
> server will get swamped by it...
> Following the load test I've been looking at the Profiler tool. The output
> seems to show the app uses server side cursors (we've noticed some very
> heavy IO on tempdb that seems to support this). I dimly remember reading
> something in the past that high CPU can be a symptom of high lock counts
> rather than a cause of it - is this right?
> Any help or advice will be much appreciated :-)
> cheers
> dave|||Hi Dave
High CPU can be the result of poor code and you may want to look at the lock
events such as lock escallation in SQL profiler. You can also use the
profiler to find out what the I/O intensive queries are see:
http://www.sql-server-performance.c...nce_audit10.asp
You may then want to go back to the vendor and ask them to improve it.
Also check that if there are maintenance routines that will update your
indexes and statistics that they have successfully been run.
For blocking you may want to look at http://support.microsoft.com/kb/271509
HTH
John
"Dave Thornley" wrote:

> Hi all,
> I hope someone will be kind enough to have a look over this for me and
> let me know if I'm correct.
> We bought an application to do timetabling a couple of years ago. Last
> year we had terrible problems with system performance, during the busy
> period in August/September the system became basically unusable. We
> tracked that down to disk performance, and after toying with using the
> SAN, eventually went for Solid State Disk (RamSan 300) after some
> careful analysis of the SAN. We also moved the application up to a new
> 4x3GHz Xeon machine with 4GB RAM.
> We've just run a simulated load test to see if we think the system will
> be able to stand up to the load this year and are very concerned about
> the results. The load test had 20 users in it.
> All IO/Memory/Paging/Compilation counters etc seemed to show the server
> wasn't stretched, however the CPU load went to 100% and stayed there for
> the duration. The number of Page locks got to 70,000 (last year it was
> 230,000 + so we've improved something!) and we saw several blocking
> chains with the lead blocker in a 'sleeping' state. The wait times for
> the blocked processes got to in excess of 500 seconds.
> The suppliers say we need a bigger server, my concern, since we're on a
> far bigger server is that the app is just highly inefficient and any
> size server will get swamped by it...
> Following the load test I've been looking at the Profiler tool. The
> output seems to show the app uses server side cursors (we've noticed
> some very heavy IO on tempdb that seems to support this). I dimly
> remember reading something in the past that high CPU can be a symptom of
> high lock counts rather than a cause of it - is this right?
> Any help or advice will be much appreciated :-)
> cheers
> dave
>|||Hi Dave,
Since the application suffers from heavy locking/blocking you should
considder upgrading to SS2005 and use snapshot isolation.
ALTER DATABASE [S] SET read_committed_snapshot ON
Be sure that tempdb is big enough :-)
The application you are talking about sounds a lot like Axapta :-)
Do you reindex your clustered indexes from time to time - be sure there is a
clustered index on the tables?
You should look at the wait statistics for the different sessions - have you
heard about the YAPP method?
Good luck :-)
"Dave Thornley" wrote:

> Hi all,
> I hope someone will be kind enough to have a look over this for me and
> let me know if I'm correct.
> We bought an application to do timetabling a couple of years ago. Last
> year we had terrible problems with system performance, during the busy
> period in August/September the system became basically unusable. We
> tracked that down to disk performance, and after toying with using the
> SAN, eventually went for Solid State Disk (RamSan 300) after some
> careful analysis of the SAN. We also moved the application up to a new
> 4x3GHz Xeon machine with 4GB RAM.
> We've just run a simulated load test to see if we think the system will
> be able to stand up to the load this year and are very concerned about
> the results. The load test had 20 users in it.
> All IO/Memory/Paging/Compilation counters etc seemed to show the server
> wasn't stretched, however the CPU load went to 100% and stayed there for
> the duration. The number of Page locks got to 70,000 (last year it was
> 230,000 + so we've improved something!) and we saw several blocking
> chains with the lead blocker in a 'sleeping' state. The wait times for
> the blocked processes got to in excess of 500 seconds.
> The suppliers say we need a bigger server, my concern, since we're on a
> far bigger server is that the app is just highly inefficient and any
> size server will get swamped by it...
> Following the load test I've been looking at the Profiler tool. The
> output seems to show the app uses server side cursors (we've noticed
> some very heavy IO on tempdb that seems to support this). I dimly
> remember reading something in the past that high CPU can be a symptom of
> high lock counts rather than a cause of it - is this right?
> Any help or advice will be much appreciated :-)
> cheers
> dave
>|||Thanks for the help and suggestions guys.
dave

Performance recommendation

Please give me some advice. In my application I calculate a list of identifiers (Guids) that are primary keys in my table and I have to retrieve those rows from the database. So my first approach is like

Code Snippet

SELECT id, c2 FROM t1 WHERE id IN (@.id1, @.id2, @.id3,....)

where @.idn are the calculated identifies as parameters. This approach does not scale well since there is a limit of parameters that can be used. So one possibility might be to use several SELECT statements, each with the maximum number of parameters. I can't believe that this is a good solution. A temporary table may be a better solution - I don't know. Are there any better ways to retrieve performantly - any recommandations?

Thanks a lot

Hans-Peter

Code Snippet

SELECT id, c2
FROM
t1 a
Join
(Select @.Id1 Index#
union all
Select @.Id2
union all
select @.Id3
...
...
union all
select @.Idn) as b
On
a.Id = b.Index#

|||

First, GUIDs as primary keys is not usually a good idea. See these articles for more information.

GUID -Identity and Primary Keys
http://sqlteam.com/item.asp?ItemID=2599

GUID -Is not Always GOOD
http://bloggingabout.net/blogs/wellink/archive/2004/03/15/598.aspx

GUID -The Cost of GUIDs as Primary Keys
http://www.informit.com/articles/article.asp?p=25862&rl=1

GUID -Uniqueidentifier vs. IDENTITY
http://sqlteam.com/item.asp?ItemID=283

Secondly, using a table variable could be useful.

|||

Arnie is correct that using GUID's for a Primary Key is not a good idea from a performance and maintenance point of view.

It is also a bad idea to use big IN clauses in the WHERE clause of a SELECT statement, since that will give you a large ad-hoc plan with a use count of 1 that will bloat your procedure cache. One work-around to avoid this is to add OPTION (RECOMPILE) to the end of your query, so SQL Server does not cache the plan (which won't be re-used anyway).

You can run this DMV to see if you are experiencing this problem:

Code Snippet

-- Find the ad-hoc queries that are bloating the plan cache

SELECT TOP(1000) *

FROM sys.dm_Exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle)

WHERE cacheobjtype = 'Compiled Plan'

AND objtype = 'Adhoc' AND usecounts = 1

--AND size_in_bytes < 200000

ORDER BY size_in_bytes DESC

I recommend that you build a stored procedure that has say 20 or 50 input parameters, then have a SELECT statement that uses an OR for each parameter. Then call the SP as many times as you need to. You can just use duplicates if you have less than 20 or 50 values for a call.

Code Snippet

SELECT id, c2

FROM t1

WHERE id = @.id1

OR id = @.id2

OR id = @.id3....

|||

Thanks a lot for your recommendations! Let me explain my insight:

There are different ways to avoid the IN with many arguments. One way is that Bushan shows (select and union all parameters internally) and

one way to use ORs. I will try both ways to check performance improvement.

But I still have some questions:

Limits of parameters / command length|||

The best way to compare two alternative ways of doing it is to run the queries back to back in SSMS, with SET STATISTICS IO enabled, and the graphical execution plan turned on. Then you can compare the cost the batches, and you will be able see the percentage cost of each batch, and compare the IO cost also.

If you want to take caching out of it, you can run DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE before each testing run, (but don't do that on a Production system).

Using a very big IN clause will really bloat your Procedure cache with single-use, ad-hoc query plans, which can really eat up some memory.

|||

Thanks for your advice, Glenn!

Hans-Peter

|||

Hi Hans-Peter,

are you building a DW or an OLTP system....

The usual case in DWs is to use generated integer keys all the time....

In OLTP using generated integer keys is often a good idea but it is by no means a hard and fast rule...

if you use integers like this then most queries are where clauses on attributes that link back to the integer...when you want to group things together and shorten where clauses you add attributes to group things.....

Best Regards

Monday, March 26, 2012

performance question - SQL2k sp3

We moved a large database & application from a single server with RAID
drives to a clustered server with a SAN and are not seeing any performance
gains. Statistics were updated, procs recompiled, etc. after the move. The
CPU load is lower (as seen in perfmon), and the SAN disks are also not
working nearly as hard as the RAID was, but the application shows no speed
improvement. Any thoughts on where to look next?
thx.
k
Hi
Generally, moving to a SAN will not improve performance, but might decrease
it due to the higher IO latency.
Disk layout is important, make sure your transaction logs are on a seperate
volume, with RAID-1 or RAID 10. If possible, don't put your data on RAID-5,
but RAID-10.
Run profiler to find your slowest queries and see if you can not optimize
them with indexing.
Regards
Mike
"wasnotwas" wrote:

> We moved a large database & application from a single server with RAID
> drives to a clustered server with a SAN and are not seeing any performance
> gains. Statistics were updated, procs recompiled, etc. after the move. The
> CPU load is lower (as seen in perfmon), and the SAN disks are also not
> working nearly as hard as the RAID was, but the application shows no speed
> improvement. Any thoughts on where to look next?
> thx.
> k
>
>

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.

Friday, March 23, 2012

Performance Question

Hello Everybody,
I do have a question abt performance of one of my report store procedure.
We have a reporing application using Microsoft Reporting Services a Report
Front End and SQL SERVER 2000 as a DB. I have written one report store proc.
That report store proc is taking arround 30 sec to run in Query Analyzer. I
had opened 10 query analyzer windows and ran that report at same time from
each window and it is taking arround 35-40 sec to run. But if 10 people
access that same report from Report Server at same time, it is taking very
long time.. arround 8-10 minutes...
so really wondering...what would be the reason. I have proper indexes
created on all the appropriate columns..
So pls let me know what i can do ?
ThanksGoing from 30 seconds to 10 minutes sounds like blocking.
INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
http://support.microsoft.com/defaul...kb;EN-US;224453
How to monitor SQL Server 2000 blocking
http://support.microsoft.com/defaul...kb;en-us;271509
aba_lockinfo
http://www.sommarskog.se/sqlutil/aba_lockinfo.html
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:F3C00A9E-9D21-4B58-98B5-C2A2A042BEE7@.microsoft.com...
> Hello Everybody,
> I do have a question abt performance of one of my report store procedure.
> We have a reporing application using Microsoft Reporting Services a Report
> Front End and SQL SERVER 2000 as a DB. I have written one report store
> proc.
> That report store proc is taking arround 30 sec to run in Query Analyzer.
> I
> had opened 10 query analyzer windows and ran that report at same time from
> each window and it is taking arround 35-40 sec to run. But if 10 people
> access that same report from Report Server at same time, it is taking very
> long time.. arround 8-10 minutes...
> so really wondering...what would be the reason. I have proper indexes
> created on all the appropriate columns..
> So pls let me know what i can do ?
> Thanks

Performance problems when running trhough Com+ and DTC

Hi everyone,
we have a performance problem when running a relatively heavy INSERT
statement from a COM+ application against SQL Server 2005 (SP1). The query
takes up all CPU resources (4 CPUs) on the database server while processing
(about 15 minutes) and the database server does not respond to other
queries. The general response from the database server computer is also
poor, including its desktop and other user interactions.
When running the same statement from Managerment Studio, it takes about same
time to complete, but it only takes up 1 CPU and other queries can run at
the same time.
This happens only for some queries. A minor change to the SELECT-part of the
query may make the problem go away.
The SQL Server database is a clustered 64 bit installation. The SQL Server
has SP1 installed, but not SP2. Is it likely that this issue is fixed in
SP2.
Thanks in advance.You didn't post the statement so it is hard to say but you can either set
the MAXDOP at the server level or specify a hint inthe query to limit the
number of CPU's a single action uses.
--
Andrew J. Kelly SQL MVP
"Anders Evensen" <anders.evensen@.millionhandshakes.com> wrote in message
news:OIKIRdUbHHA.1400@.TK2MSFTNGP06.phx.gbl...
> Hi everyone,
> we have a performance problem when running a relatively heavy INSERT
> statement from a COM+ application against SQL Server 2005 (SP1). The query
> takes up all CPU resources (4 CPUs) on the database server while
> processing (about 15 minutes) and the database server does not respond to
> other queries. The general response from the database server computer is
> also poor, including its desktop and other user interactions.
> When running the same statement from Managerment Studio, it takes about
> same time to complete, but it only takes up 1 CPU and other queries can
> run at the same time.
> This happens only for some queries. A minor change to the SELECT-part of
> the query may make the problem go away.
> The SQL Server database is a clustered 64 bit installation. The SQL Server
> has SP1 installed, but not SP2. Is it likely that this issue is fixed in
> SP2.
>
> Thanks in advance.
>
>|||You can specify a MAXDOP 1 query hint to prevent a parallel query plan. For
example:
SELECT Col1
FROM MyTable
OPTION (MAXDOP 1)
Depending on the particulars, the query might run a bit longer without
parallelism but will keep more CPU resources available to satisfy
concurrent queries. You might also consider changing the 'max degree of
parallelism' config option to less than the number of total processors:
EXEC sp_configure 'max degree of parallelism', 3
RECONFIGURE
GO
> This happens only for some queries. A minor change to the SELECT-part of
> the query may make the problem go away.
Parallel plans can be an indication that query/index tuning is needed.
Examine the execution plans of parallel queries to see if improvement is
possible.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Anders Evensen" <anders.evensen@.millionhandshakes.com> wrote in message
news:OIKIRdUbHHA.1400@.TK2MSFTNGP06.phx.gbl...
> Hi everyone,
> we have a performance problem when running a relatively heavy INSERT
> statement from a COM+ application against SQL Server 2005 (SP1). The query
> takes up all CPU resources (4 CPUs) on the database server while
> processing (about 15 minutes) and the database server does not respond to
> other queries. The general response from the database server computer is
> also poor, including its desktop and other user interactions.
> When running the same statement from Managerment Studio, it takes about
> same time to complete, but it only takes up 1 CPU and other queries can
> run at the same time.
> This happens only for some queries. A minor change to the SELECT-part of
> the query may make the problem go away.
> The SQL Server database is a clustered 64 bit installation. The SQL Server
> has SP1 installed, but not SP2. Is it likely that this issue is fixed in
> SP2.
>
> Thanks in advance.
>
>|||Thanks. We will try this.
However, I am very interesting in knowing if there is a logical explanation
to why SQL Server processes uses totally different CPU resources when
running the statement from COM+ in a DTC transaction compared to running it
from Management Studio.
-Anders
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23U4xx0UbHHA.4012@.TK2MSFTNGP03.phx.gbl...
> You didn't post the statement so it is hard to say but you can either set
> the MAXDOP at the server level or specify a hint inthe query to limit the
> number of CPU's a single action uses.
> --
> Andrew J. Kelly SQL MVP
> "Anders Evensen" <anders.evensen@.millionhandshakes.com> wrote in message
> news:OIKIRdUbHHA.1400@.TK2MSFTNGP06.phx.gbl...
>> Hi everyone,
>> we have a performance problem when running a relatively heavy INSERT
>> statement from a COM+ application against SQL Server 2005 (SP1). The
>> query takes up all CPU resources (4 CPUs) on the database server while
>> processing (about 15 minutes) and the database server does not respond to
>> other queries. The general response from the database server computer is
>> also poor, including its desktop and other user interactions.
>> When running the same statement from Managerment Studio, it takes about
>> same time to complete, but it only takes up 1 CPU and other queries can
>> run at the same time.
>> This happens only for some queries. A minor change to the SELECT-part of
>> the query may make the problem go away.
>> The SQL Server database is a clustered 64 bit installation. The SQL
>> Server has SP1 installed, but not SP2. Is it likely that this issue is
>> fixed in SP2.
>>
>> Thanks in advance.
>>
>|||why are you suggesting 3? I just came across this thread
For optimal performance of multi-processor installations, we recommend that
the MAXDOP setting remain equal to the number of physical processors that are
being used. For example, if the system is configured for two physical
processors and four logical processors, MAXDOP should be set to 2.
Any thoughts
http://blogs.msdn.com/sqltips/archive/2005/09/14/466387.aspx
"Dan Guzman" wrote:
> You can specify a MAXDOP 1 query hint to prevent a parallel query plan. For
> example:
> SELECT Col1
> FROM MyTable
> OPTION (MAXDOP 1)
> Depending on the particulars, the query might run a bit longer without
> parallelism but will keep more CPU resources available to satisfy
> concurrent queries. You might also consider changing the 'max degree of
> parallelism' config option to less than the number of total processors:
> EXEC sp_configure 'max degree of parallelism', 3
> RECONFIGURE
> GO
> > This happens only for some queries. A minor change to the SELECT-part of
> > the query may make the problem go away.
> Parallel plans can be an indication that query/index tuning is needed.
> Examine the execution plans of parallel queries to see if improvement is
> possible.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Anders Evensen" <anders.evensen@.millionhandshakes.com> wrote in message
> news:OIKIRdUbHHA.1400@.TK2MSFTNGP06.phx.gbl...
> > Hi everyone,
> >
> > we have a performance problem when running a relatively heavy INSERT
> > statement from a COM+ application against SQL Server 2005 (SP1). The query
> > takes up all CPU resources (4 CPUs) on the database server while
> > processing (about 15 minutes) and the database server does not respond to
> > other queries. The general response from the database server computer is
> > also poor, including its desktop and other user interactions.
> >
> > When running the same statement from Managerment Studio, it takes about
> > same time to complete, but it only takes up 1 CPU and other queries can
> > run at the same time.
> >
> > This happens only for some queries. A minor change to the SELECT-part of
> > the query may make the problem go away.
> >
> > The SQL Server database is a clustered 64 bit installation. The SQL Server
> > has SP1 installed, but not SP2. Is it likely that this issue is fixed in
> > SP2.
> >
> >
> > Thanks in advance.
> >
> >
> >
>|||ignore the post above ...sorry wrong thread
"tcs" wrote:
> why are you suggesting 3? I just came across this thread
> For optimal performance of multi-processor installations, we recommend that
> the MAXDOP setting remain equal to the number of physical processors that are
> being used. For example, if the system is configured for two physical
> processors and four logical processors, MAXDOP should be set to 2.
> Any thoughts
>
> http://blogs.msdn.com/sqltips/archive/2005/09/14/466387.aspx
> "Dan Guzman" wrote:
> > You can specify a MAXDOP 1 query hint to prevent a parallel query plan. For
> > example:
> >
> > SELECT Col1
> > FROM MyTable
> > OPTION (MAXDOP 1)
> >
> > Depending on the particulars, the query might run a bit longer without
> > parallelism but will keep more CPU resources available to satisfy
> > concurrent queries. You might also consider changing the 'max degree of
> > parallelism' config option to less than the number of total processors:
> >
> > EXEC sp_configure 'max degree of parallelism', 3
> > RECONFIGURE
> > GO
> >
> > > This happens only for some queries. A minor change to the SELECT-part of
> > > the query may make the problem go away.
> >
> > Parallel plans can be an indication that query/index tuning is needed.
> > Examine the execution plans of parallel queries to see if improvement is
> > possible.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Anders Evensen" <anders.evensen@.millionhandshakes.com> wrote in message
> > news:OIKIRdUbHHA.1400@.TK2MSFTNGP06.phx.gbl...
> > > Hi everyone,
> > >
> > > we have a performance problem when running a relatively heavy INSERT
> > > statement from a COM+ application against SQL Server 2005 (SP1). The query
> > > takes up all CPU resources (4 CPUs) on the database server while
> > > processing (about 15 minutes) and the database server does not respond to
> > > other queries. The general response from the database server computer is
> > > also poor, including its desktop and other user interactions.
> > >
> > > When running the same statement from Managerment Studio, it takes about
> > > same time to complete, but it only takes up 1 CPU and other queries can
> > > run at the same time.
> > >
> > > This happens only for some queries. A minor change to the SELECT-part of
> > > the query may make the problem go away.
> > >
> > > The SQL Server database is a clustered 64 bit installation. The SQL Server
> > > has SP1 installed, but not SP2. Is it likely that this issue is fixed in
> > > SP2.
> > >
> > >
> > > Thanks in advance.
> > >
> > >
> > >
> >
> >|||The number of threads used are always determined at run time based on a
number of factors. So even if one time it uses all the procs it can easily
use just one the next time around. But in this case I feel it is related to
how it is being called and something called parameter sniffing. You can get
two very different plans if they are not called identically and evaluate to
the same datatypes etc. Again it would help to see the real statement.
--
Andrew J. Kelly SQL MVP
"Anders Evensen" <anders.evensen@.millionhandshakes.com> wrote in message
news:efOOB7UbHHA.1508@.TK2MSFTNGP06.phx.gbl...
> Thanks. We will try this.
> However, I am very interesting in knowing if there is a logical
> explanation to why SQL Server processes uses totally different CPU
> resources when running the statement from COM+ in a DTC transaction
> compared to running it from Management Studio.
> -Anders
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23U4xx0UbHHA.4012@.TK2MSFTNGP03.phx.gbl...
>> You didn't post the statement so it is hard to say but you can either set
>> the MAXDOP at the server level or specify a hint inthe query to limit the
>> number of CPU's a single action uses.
>> --
>> Andrew J. Kelly SQL MVP
>> "Anders Evensen" <anders.evensen@.millionhandshakes.com> wrote in message
>> news:OIKIRdUbHHA.1400@.TK2MSFTNGP06.phx.gbl...
>> Hi everyone,
>> we have a performance problem when running a relatively heavy INSERT
>> statement from a COM+ application against SQL Server 2005 (SP1). The
>> query takes up all CPU resources (4 CPUs) on the database server while
>> processing (about 15 minutes) and the database server does not respond
>> to other queries. The general response from the database server computer
>> is also poor, including its desktop and other user interactions.
>> When running the same statement from Managerment Studio, it takes about
>> same time to complete, but it only takes up 1 CPU and other queries can
>> run at the same time.
>> This happens only for some queries. A minor change to the SELECT-part of
>> the query may make the problem go away.
>> The SQL Server database is a clustered 64 bit installation. The SQL
>> Server has SP1 installed, but not SP2. Is it likely that this issue is
>> fixed in SP2.
>>
>> Thanks in advance.
>>
>>
>|||On Fri, 23 Mar 2007 14:25:37 +0100, "Anders Evensen"
<anders.evensen@.millionhandshakes.com> wrote:
>Thanks. We will try this.
>However, I am very interesting in knowing if there is a logical explanation
>to why SQL Server processes uses totally different CPU resources when
>running the statement from COM+ in a DTC transaction compared to running it
>from Management Studio.
I believe COM+ often sets isolation level to repeatable read, which
could explain the situation - management studio doesn't do that.
J.
>-Anders
>"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>news:%23U4xx0UbHHA.4012@.TK2MSFTNGP03.phx.gbl...
>> You didn't post the statement so it is hard to say but you can either set
>> the MAXDOP at the server level or specify a hint inthe query to limit the
>> number of CPU's a single action uses.
>> --
>> Andrew J. Kelly SQL MVP
>> "Anders Evensen" <anders.evensen@.millionhandshakes.com> wrote in message
>> news:OIKIRdUbHHA.1400@.TK2MSFTNGP06.phx.gbl...
>> Hi everyone,
>> we have a performance problem when running a relatively heavy INSERT
>> statement from a COM+ application against SQL Server 2005 (SP1). The
>> query takes up all CPU resources (4 CPUs) on the database server while
>> processing (about 15 minutes) and the database server does not respond to
>> other queries. The general response from the database server computer is
>> also poor, including its desktop and other user interactions.
>> When running the same statement from Managerment Studio, it takes about
>> same time to complete, but it only takes up 1 CPU and other queries can
>> run at the same time.
>> This happens only for some queries. A minor change to the SELECT-part of
>> the query may make the problem go away.
>> The SQL Server database is a clustered 64 bit installation. The SQL
>> Server has SP1 installed, but not SP2. Is it likely that this issue is
>> fixed in SP2.
>>
>> Thanks in advance.
>>
>>
>|||Actually I think it used Serializable but am not 100% sure.
--
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:fuj8039981uc8e6ipe83k51r7f3a9092s3@.4ax.com...
> On Fri, 23 Mar 2007 14:25:37 +0100, "Anders Evensen"
> <anders.evensen@.millionhandshakes.com> wrote:
>>Thanks. We will try this.
>>However, I am very interesting in knowing if there is a logical
>>explanation
>>to why SQL Server processes uses totally different CPU resources when
>>running the statement from COM+ in a DTC transaction compared to running
>>it
>>from Management Studio.
> I believe COM+ often sets isolation level to repeatable read, which
> could explain the situation - management studio doesn't do that.
> J.
>
>>-Anders
>>"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>>news:%23U4xx0UbHHA.4012@.TK2MSFTNGP03.phx.gbl...
>> You didn't post the statement so it is hard to say but you can either
>> set
>> the MAXDOP at the server level or specify a hint inthe query to limit
>> the
>> number of CPU's a single action uses.
>> --
>> Andrew J. Kelly SQL MVP
>> "Anders Evensen" <anders.evensen@.millionhandshakes.com> wrote in message
>> news:OIKIRdUbHHA.1400@.TK2MSFTNGP06.phx.gbl...
>> Hi everyone,
>> we have a performance problem when running a relatively heavy INSERT
>> statement from a COM+ application against SQL Server 2005 (SP1). The
>> query takes up all CPU resources (4 CPUs) on the database server while
>> processing (about 15 minutes) and the database server does not respond
>> to
>> other queries. The general response from the database server computer
>> is
>> also poor, including its desktop and other user interactions.
>> When running the same statement from Managerment Studio, it takes about
>> same time to complete, but it only takes up 1 CPU and other queries can
>> run at the same time.
>> This happens only for some queries. A minor change to the SELECT-part
>> of
>> the query may make the problem go away.
>> The SQL Server database is a clustered 64 bit installation. The SQL
>> Server has SP1 installed, but not SP2. Is it likely that this issue is
>> fixed in SP2.
>>
>> Thanks in advance.
>>
>>
>>
>|||Yep, it is serializable per default.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23$0H1babHHA.1220@.TK2MSFTNGP03.phx.gbl...
> Actually I think it used Serializable but am not 100% sure.
> --
> Andrew J. Kelly SQL MVP
> "JXStern" <JXSternChangeX2R@.gte.net> wrote in message
> news:fuj8039981uc8e6ipe83k51r7f3a9092s3@.4ax.com...
>> On Fri, 23 Mar 2007 14:25:37 +0100, "Anders Evensen"
>> <anders.evensen@.millionhandshakes.com> wrote:
>>Thanks. We will try this.
>>However, I am very interesting in knowing if there is a logical explanation
>>to why SQL Server processes uses totally different CPU resources when
>>running the statement from COM+ in a DTC transaction compared to running it
>>from Management Studio.
>> I believe COM+ often sets isolation level to repeatable read, which
>> could explain the situation - management studio doesn't do that.
>> J.
>>
>>
>>-Anders
>>"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>>news:%23U4xx0UbHHA.4012@.TK2MSFTNGP03.phx.gbl...
>> You didn't post the statement so it is hard to say but you can either set
>> the MAXDOP at the server level or specify a hint inthe query to limit the
>> number of CPU's a single action uses.
>> --
>> Andrew J. Kelly SQL MVP
>> "Anders Evensen" <anders.evensen@.millionhandshakes.com> wrote in message
>> news:OIKIRdUbHHA.1400@.TK2MSFTNGP06.phx.gbl...
>> Hi everyone,
>> we have a performance problem when running a relatively heavy INSERT
>> statement from a COM+ application against SQL Server 2005 (SP1). The
>> query takes up all CPU resources (4 CPUs) on the database server while
>> processing (about 15 minutes) and the database server does not respond to
>> other queries. The general response from the database server computer is
>> also poor, including its desktop and other user interactions.
>> When running the same statement from Managerment Studio, it takes about
>> same time to complete, but it only takes up 1 CPU and other queries can
>> run at the same time.
>> This happens only for some queries. A minor change to the SELECT-part of
>> the query may make the problem go away.
>> The SQL Server database is a clustered 64 bit installation. The SQL
>> Server has SP1 installed, but not SP2. Is it likely that this issue is
>> fixed in SP2.
>>
>> Thanks in advance.
>>
>>
>>
>|||On Sat, 24 Mar 2007 09:40:55 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>Yep, it is serializable per default.
Right, ... the point being he can try to replicate the behavior by
setting the isolation mode in management studio, I meant to point that
out too.
J.|||Thanks. We are actually using read committed as the isolation level from
COM+, and the read commitet snapshot option is turned on for the database.
Management Studio is using read committed as well.
-A
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:321b03pup5h45fns2puit4buo90655cqso@.4ax.com...
> On Sat, 24 Mar 2007 09:40:55 +0100, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>>Yep, it is serializable per default.
> Right, ... the point being he can try to replicate the behavior by
> setting the isolation mode in management studio, I meant to point that
> out too.
> J.
>|||On Sun, 25 Mar 2007 13:34:43 +0200, "Anders Evensen"
<anders.evensen@.millionhandshakes.com> wrote:
>Thanks. We are actually using read committed as the isolation level from
>COM+, and the read commitet snapshot option is turned on for the database.
>Management Studio is using read committed as well.
Then I guess I would ask, are you *sure* that when you run it through
COM+, nothing else is executing? You're running an INSERT statement,
does COM+ get the exact string you use in the MS or does it do a
prepared statement or somesuch? Have you run profiler to be clear on
this?
The COM+ connections might also prep with other random settings that
could be factors. Do they return exactly the same results either way?
You could use profiler to display the plans from executing from either
side, it wouldn't tell you *why* exactly, but it might give more
hints.
J.
>-A
>"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
>news:321b03pup5h45fns2puit4buo90655cqso@.4ax.com...
>> On Sat, 24 Mar 2007 09:40:55 +0100, "Tibor Karaszi"
>> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>>Yep, it is serializable per default.
>> Right, ... the point being he can try to replicate the behavior by
>> setting the isolation mode in management studio, I meant to point that
>> out too.
>> J.
>

Performance problems when running trhough Com+ and DTC

Performance problems when running through Com+ and DTC

We have a performance problem when running a relatively heavy INSERT
statement from a COM+ application against SQL Server 2005 (SP1). The query
takes up all CPU resources (4 CPUs) on the database server while processing
(about 15 minutes) and the database server does not respond to other
queries. The general response from the database server computer is also
poor, including its desktop and other user interactions.

When running the same statement from Managerment Studio, it takes about same
time to complete, but it only takes up 1 CPU and other queries can run at
the same time.

This happens only for some queries. A minor change to the SELECT-part of the
query may make the problem go away.

The SQL Server database is a clustered 64 bit installation. The SQL Server
has SP1 installed, but not SP2. Is it likely that this issue is fixed in
SP2?

More information: we are running in a DTC transaction from COM+. The database are using the read committed snapshot option.

Thanks in advance.

You should probably post your table schema (including indexes), and the query you are trying to run. As I recall, COM+ uses a fairly high transaction isolation level by default, so you might be seeing more locking/blocking because of that.

You might try adding OPTION (MAXDOP1) to the end of the query, and see if the query only uses one CPU.

sql

Wednesday, March 21, 2012

performance problems

hi!
we have developed an application with sql server 2000 as database server. we
still have enormous performance problem and so we started to search for
reasons.
now we made a test were we send a sql query (about 10.000 data records) to
the server and were quite wondering. when we made the test with a lokal pc,
sometimes really old boys, it took some seconds to finish the query. with ou
r
server (dual xenon board, 2,6 mhz, 2 gb mem) it lasts over 3 minutes! when w
e
check the performance monitor of the server he will not have any ressource
troubles. i really dont know which processor the server is using ;-)
how could that happen? are there any troubles with dual boards?
i have also check the processor properties of the server. there are still
two processors listed. the settings should be ok unless i could not change
the settings.
also the disk is ok.
would be great if someone could give me a hint.
greetings,
markusMarkus hi,
Consider that there are many things to check in order to issue a best
performance in SQL 2000. First of all, you need to check the disk I/O. The
partitioning is one other thing.
In the Microsoft SQL 2000 resource kit there is the following article
2061.mspx" target="_blank">http://www.microsoft.com/resources/...r />
2061.mspx
In this article you will find plenty of information regarding performance in
RDBMS
HTH
Andreas
"markus" wrote:

> hi!
> we have developed an application with sql server 2000 as database server.
we
> still have enormous performance problem and so we started to search for
> reasons.
> now we made a test were we send a sql query (about 10.000 data records) to
> the server and were quite wondering. when we made the test with a lokal pc
,
> sometimes really old boys, it took some seconds to finish the query. with
our
> server (dual xenon board, 2,6 mhz, 2 gb mem) it lasts over 3 minutes! when
we
> check the performance monitor of the server he will not have any ressource
> troubles. i really dont know which processor the server is using ;-)
> how could that happen? are there any troubles with dual boards?
> i have also check the processor properties of the server. there are still
> two processors listed. the settings should be ok unless i could not change
> the settings.
> also the disk is ok.
> would be great if someone could give me a hint.
> greetings,
> markus
>
>|||hi andreas,
thanks for the link. i went through it quickly. sure there are a lot of
important things mentioned. but this still can't explain the gap of my
performance test. and i did not notice hardly any change in the performance
monitor during the test. the cpu load is about 7% and it's the same with ram
.
regards,
markus
"Andreas Mavrogenis" wrote:
[vbcol=seagreen]
> Markus hi,
> Consider that there are many things to check in order to issue a best
> performance in SQL 2000. First of all, you need to check the disk I/O. The
> partitioning is one other thing.
> In the Microsoft SQL 2000 resource kit there is the following article
> /c2061.mspx" target="_blank">http://www.microsoft.com/resources/.../>
/c2061.mspx
> In this article you will find plenty of information regarding performance
in
> RDBMS
> HTH
> Andreas
> "markus" wrote:
>|||Hi,
In your test, you run the query in a single processor pc with no-one loged
in. In your server is always a little bit slower. If your server is a HT
server, you will see in the task manager (tab performance), 4 CPU histograms
.
Notice if running the query, captures only one CPU. If this is happening,
check your code if there is a max dop = 1 option. This option is to run the
query only in one processor.
Also, you must tell us if your table has any indexes. If there are, try to
re-index them.
FInally, you must check your disks. Is it possible that you have RAID-5 for
both mdf and ldf files?
What is the configuration ?
HTH
Andreas
"markus" wrote:
[vbcol=seagreen]
> hi andreas,
> thanks for the link. i went through it quickly. sure there are a lot of
> important things mentioned. but this still can't explain the gap of my
> performance test. and i did not notice hardly any change in the performanc
e
> monitor during the test. the cpu load is about 7% and it's the same with r
am.
> regards,
> markus
> "Andreas Mavrogenis" wrote:
>|||hi,
yes, we use a raid 5. the other traffic load on the server is not really
heavy.
at the end of my post i attached the query, nothing sophisticated.
thanks for your help so far,
***********************
if exists (select 'TRUE' from sysobjects where name = 'performance' and
type = 'u')
drop table performance
go
CREATE TABLE performance (
ident int NOT NULL ,
blabla varchar (250) COLLATE Latin1_General_CI_AS NOT NULL ,
datum datetime NOT NULL CONSTRAINT DF__performan__datum__102C51FF DEFAULT
(getdate()),
rowguid uniqueidentifier NOT NULL CONSTRAINT DF__performan__rowgu__11207638
DEFAULT (newid()),
Cash money not null,
CONSTRAINT pk_performance PRIMARY KEY CLUSTERED (ident),
CONSTRAINT ux_performance UNIQUE NONCLUSTERED (blabla)
)
GO
go
set nocount on
go
declare @.i int,
@.count int
set @.count = 10000
select @.i = isnull(max(ident) + 1, 1) from performance
set @.count = @.count + @.i
while @.i < @.count begin
insert into performance (ident, blabla, cash)
values (@.i,
convert(varchar(10), @.i) +
'blablablablablablablablablablablablabla
blabla' + convert(varchar(10), @.i),
rand(@.i)
)
set @.i = @.i + 1
end
go
select * from performance
go
"Andreas Mavrogenis" wrote:
[vbcol=seagreen]
> Hi,
> In your test, you run the query in a single processor pc with no-one loged
> in. In your server is always a little bit slower. If your server is a HT
> server, you will see in the task manager (tab performance), 4 CPU histogra
ms.
> Notice if running the query, captures only one CPU. If this is happening,
> check your code if there is a max dop = 1 option. This option is to run th
e
> query only in one processor.
> Also, you must tell us if your table has any indexes. If there are, try to
> re-index them.
> FInally, you must check your disks. Is it possible that you have RAID-5 fo
r
> both mdf and ldf files?
> What is the configuration ?
> HTH
> Andreas
>
> "markus" wrote:
>|||Hi,
Try to change your while statement with cursor. You will find sample code in
BOL. It works better!. Also, the RAID-5 is mostly for lots of reads and not
for writes. If you can, add two more disks in your array, make them RAID 1
and detach/attach the log (ldf ) file to this new drive.
HTH
Andreas
"markus" wrote:
[vbcol=seagreen]
> hi,
> yes, we use a raid 5. the other traffic load on the server is not really
> heavy.
> at the end of my post i attached the query, nothing sophisticated.
> thanks for your help so far,
> ***********************
> if exists (select 'TRUE' from sysobjects where name = 'performance' and
> type = 'u')
> drop table performance
> go
> CREATE TABLE performance (
> ident int NOT NULL ,
> blabla varchar (250) COLLATE Latin1_General_CI_AS NOT NULL ,
> datum datetime NOT NULL CONSTRAINT DF__performan__datum__102C51FF DEFAULT
> (getdate()),
> rowguid uniqueidentifier NOT NULL CONSTRAINT DF__performan__rowgu__112076
38
> DEFAULT (newid()),
> Cash money not null,
> CONSTRAINT pk_performance PRIMARY KEY CLUSTERED (ident),
> CONSTRAINT ux_performance UNIQUE NONCLUSTERED (blabla)
> )
> GO
> go
> set nocount on
> go
> declare @.i int,
> @.count int
> set @.count = 10000
> select @.i = isnull(max(ident) + 1, 1) from performance
> set @.count = @.count + @.i
> while @.i < @.count begin
> insert into performance (ident, blabla, cash)
> values (@.i,
> convert(varchar(10), @.i) +
> 'blablablablablablablablablablablablabla
blabla' + convert(varchar(10), @.i)
,
> rand(@.i)
> )
> set @.i = @.i + 1
> end
> go
> select * from performance
> go
> "Andreas Mavrogenis" wrote:
>|||hi,
unfortunately i have to use raid 5. i should not mention that there are
running all windows things and no only the database stuff.
but can it be that the raid 5 causes such a gap? with a normal pc it took
appr. 10 sec to carry out the statement. with the server i need more than 3
minutes! and when raid 5 causes that delay i should even notice some tasks i
n
the performance monitor. it's really confusing.
regards,
markus
"Andreas Mavrogenis" wrote:
[vbcol=seagreen]
> Hi,
> Try to change your while statement with cursor. You will find sample code
in
> BOL. It works better!. Also, the RAID-5 is mostly for lots of reads and no
t
> for writes. If you can, add two more disks in your array, make them RAID 1
> and detach/attach the log (ldf ) file to this new drive.
> HTH
> Andreas
> "markus" wrote:
>|||Have you looked at the execution plans for both queries on the server and
client? Is there any difference?
Also check the query Analyzer version on the client and the server.
"markus" <markus@.discussions.microsoft.com> wrote in message
news:5F17AC7D-EA4A-400C-A67C-22F578AE016D@.microsoft.com...
> hi!
> we have developed an application with sql server 2000 as database server.
> we
> still have enormous performance problem and so we started to search for
> reasons.
> now we made a test were we send a sql query (about 10.000 data records) to
> the server and were quite wondering. when we made the test with a lokal
> pc,
> sometimes really old boys, it took some seconds to finish the query. with
> our
> server (dual xenon board, 2,6 mhz, 2 gb mem) it lasts over 3 minutes! when
> we
> check the performance monitor of the server he will not have any ressource
> troubles. i really dont know which processor the server is using ;-)
> how could that happen? are there any troubles with dual boards?
> i have also check the processor properties of the server. there are still
> two processors listed. the settings should be ok unless i could not change
> the settings.
> also the disk is ok.
> would be great if someone could give me a hint.
> greetings,
> markus
>
>|||both tests took place on a sql server 2k, just the hardware is really
different ;-)
the query analyzer version is the same.
"Richard Ding" wrote:

> Have you looked at the execution plans for both queries on the server and
> client? Is there any difference?
> Also check the query Analyzer version on the client and the server.
>
> "markus" <markus@.discussions.microsoft.com> wrote in message
> news:5F17AC7D-EA4A-400C-A67C-22F578AE016D@.microsoft.com...
>
>|||Marcus Hi,
Please check this article, and follow it's steps to optimize your query
http://support.microsoft.com/defaul...kb;en-us;243589
If you don't have any results, try to disable one of your two processors,
stop-start SQL Services and run the query again. Notice the task manager
performance to see it's activity.
If not, try to create a trace file and capture what your query is doing.
Use this article to analyze your performance data
http://support.microsoft.com/defaul...kb;en-us;283886
HTH
Andreas
"markus" wrote:
[vbcol=seagreen]
> both tests took place on a sql server 2k, just the hardware is really
> different ;-)
> the query analyzer version is the same.
>
> "Richard Ding" wrote:
>

Friday, March 30, 2012

Performance Tweaks

Performance Tweaks

Performance Tweaks

Wednesday, March 28, 2012

Performance sqlclient data provider

Performance SQL Server in remote access

Performance slow in Vista. Please help.

Performance sanity check

Performance sanity check

Performance recommendation

Monday, March 26, 2012

performance question - SQL2k sp3

Performance question - Restarting the SQL server resolved my timeout problem

Friday, March 23, 2012

Performance Question

Performance problems when running trhough Com+ and DTC

Performance problems when running trhough Com+ and DTC

Performance problems when running through Com+ and DTC

Wednesday, March 21, 2012

performance problems

performance monitoring

Blog Archive

About Me