Performance Problem on New Server
GaryBMemphis
why certain processes are slowing down server response to other users and
running unusually slow.
We have about 30 users who use a variety of programs written in C, Visual
Basic and even Access. Most of them are simple data entry with occasional
multirecord pulls for analysis or export.
The server is a 2 processor, 64 bit with 4 GB of memory running SQL 2005.
I have a YB 6 process that uses a multitable join to query 1-60,000 records
and then write that to a utility table on the server.
The process then reads a subset of that table into a working cursor contained
in ADO with read/write connection to the server.
It then loops thru the working table, does calculations and many conditionals
(I assume that's all done in my machines memory) and then writes the results
to the original table using an ADO command.
This process runs much slower than before the new server was installed AND
slows down response for everyone else. The server shows peak usage of 88%
and more while it is running.
I put a DOEvents clause to force disk I/O and it helped a little.
Don't know what it is but it certainly seems like the new setup is not
handling the time slicing and or prioritization of tasks very well. It is
not a high priority task but it sure is eating up the server.
The internal people setting it up pretty much did plug and play.
There is another test I've tried that seems to point to a tuning problem.
I have a fairly intensive read/write process that runs every morning using 60,
000 records in an ADO dataset and then running through a loop that will then
create 12-80 records per record for a Data Warehouse fact table. It ends up
with 2,300,000 records and increases daily.
I then move it to a utility server that the DW uses for a query engine.
If I do Select count(*) on the new server where it does the process it will
take 30-40 seconds which seems to be a LONG time.
If I do Select count(*) on the utility server where it resides for query it
takes 0-3 seconds.
Hmmmmm, what do that mean?
Any ideas?
Gary in Memphis
Erland Sommarskog
> The server is a 2 processor, 64 bit with 4 GB of memory running SQL 2005.
Is SQL Server also 64-bit?
> I have a YB 6 process that uses a multitable join to query 1-60,000
> records and then write that to a utility table on the server.
>
> The process then reads a subset of that table into a working cursor
> contained in ADO with read/write connection to the server.
>
> It then loops thru the working table, does calculations and many
> conditionals (I assume that's all done in my machines memory) and then
> writes the results to the original table using an ADO command.
>
> This process runs much slower than before the new server was installed AND
> slows down response for everyone else. The server shows peak usage of 88%
> and more while it is running.
While it runs a lot slower than before, your desciption of it makes
me feel that there is a big potential for performance by rewriting it.
Is that cursor a client-side or server-side?
And what about writing it all in T-SQL?
> If I do Select count(*) on the new server where it does the process it
> will take 30-40 seconds which seems to be a LONG time.
>
> If I do Select count(*) on the utility server where it resides for query
> it takes 0-3 seconds.
Well, there is a whole lot we don't know. You say you installed a new
server. How was the database migrated to the new server? Which version
of SQL Server were you running on the old server?
As for the SELECT COUNT(*) a start could be to defragment the table.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
GaryBMemphis
>> The server is a 2 processor, 64 bit with 4 GB of memory running SQL 2005.
>
>Is SQL Server also 64-bit?
Not sure.
>
>> I have a YB 6 process that uses a multitable join to query 1-60,000
>> records and then write that to a utility table on the server.
>[quoted text clipped - 9 lines]
>> slows down response for everyone else. The server shows peak usage of 88%
>> and more while it is running.
>
>While it runs a lot slower than before, your desciption of it makes
>me feel that there is a big potential for performance by rewriting it.
>Is that cursor a client-side or server-side?
The cursor is client side since I run it from the development platform.
At the bottom of the loop it writes result to the table on the server.
>
>And what about writing it all in T-SQL?
Could but that's not the problem. It all worked fine and quickly BEFORE the
server was changed. the code didn't change the server and SQL version. I
think it was SQL2000.
>
>> If I do Select count(*) on the new server where it does the process it
>> will take 30-40 seconds which seems to be a LONG time.
>>
>> If I do Select count(*) on the utility server where it resides for query
>> it takes 0-3 seconds.
>
>Well, there is a whole lot we don't know. You say you installed a new
>server. How was the database migrated to the new server? Which version
>of SQL Server were you running on the old server?
>
>As for the SELECT COUNT(*) a start could be to defragment the table.
>
I wonder about that too.. Of course, the table is truncated everyday and then
rebuilt. Seems like the i/o would handle that OK.
The other report server gets the table through the Export function on the
Database Tasks menu
Erland Sommarskog
> Erland Sommarskog wrote:
>>> The server is a 2 processor, 64 bit with 4 GB of memory running SQL
>>> 2005.
>>
>>Is SQL Server also 64-bit?
>
> Not sure.
Use "SELECT @@version" to find out.
>>While it runs a lot slower than before, your desciption of it makes
>>me feel that there is a big potential for performance by rewriting it.
>>Is that cursor a client-side or server-side?
>
> The cursor is client side since I run it from the development platform.
Sorry, I should have made the question clear: do you set the .CursorLocation
property to adUseClient in your ADO code? If you do not, ADO will create a
server-side cursor by default.
Server-side cursors are generally a bad idea, and should be avoided.
>>And what about writing it all in T-SQL?
>
> Could but that's not the problem. It all worked fine and quickly BEFORE
> the server was changed. the code didn't change the server and SQL
> version. I think it was SQL2000.
Unfortunately, life is not that simple that just because something
seems to work fine that the code is good. I have looked at performance
issues, where my question after some investigation hasn't been "why does
this run slow now when it used run quickly?", but rather "how could
this ever run speedily"?
There may be a suite of fortunate circumstances that saves poor code,
but usually at some point the stars do not align any more.
Anwyay, you say that you think the previous server was SQL 2000. In
that case, the problem may be that all statistics were dropped with
the upgrade, and running sp_updatestats on all databases is required.
>>As for the SELECT COUNT(*) a start could be to defragment the table.
>>
> I wonder about that too.. Of course, the table is truncated everyday and
> then rebuilt. Seems like the i/o would handle that OK.
How is that truncation carried out? TRUNCATE TABLE? Or do you use
DELETE?
GaryBMemphis via SQLMonster.com
>>>> The server is a 2 processor, 64 bit with 4 GB of memory running SQL
>>>> 2005.
>>>
>>>Is SQL Server also 64-bit?
>>
>> Not sure.
>
>Use "SELECT @@version" to find out.
Microsoft SQL Server 2005 - 9.00.4035.00 (X64) Nov 24 2008 16:17:31
Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on
Windows NT 5.2 (Build 3790: Service Pack 2)
>
>>>While it runs a lot slower than before, your desciption of it makes
>>>me feel that there is a big potential for performance by rewriting it.
>>>Is that cursor a client-side or server-side?
>>
>> The cursor is client side since I run it from the development platform.
>
>Sorry, I should have made the question clear: do you set the .CursorLocation
>property to adUseClient in your ADO code? If you do not, ADO will create a
>server-side cursor by default.
>
>Server-side cursors are generally a bad idea, and should be avoided.
3 - adUseClient
You scared me! Looks like all of mine are adUseClient.
What do you think of Ado?
>
>>>And what about writing it all in T-SQL?
>>
>> Could but that's not the problem. It all worked fine and quickly BEFORE
>> the server was changed. the code didn't change the server and SQL
>> version. I think it was SQL2000.
>
>Unfortunately, life is not that simple that just because something
>seems to work fine that the code is good. I have looked at performance
>issues, where my question after some investigation hasn't been "why does
>this run slow now when it used run quickly?", but rather "how could
>this ever run speedily"?
When I wrote the original code in 2002, I tried T-SQL but since the debugger
was flawed and I was greener than I am now and so I used VB 6. I really like
the step through ability to debug and code on the go.
are there better debuggers for T-SQL and can you recommend one?
We've run this procedure thousands of times so we have a pretty good feel for
when it runs slow.
We have started truncating the transaction logs more.
However! Since the cursor is clientside, the only weight in my view is the
update query - oops!
There is NO update query .. The code is updating individual fields through
the recordset.fields object depending on IF conditionals.
Seems like the update query would be an ADO generated Update when the row is
changed.
Actually I think I have to use the .UPDATE command or it loses it place.
looks like there is no index on my primary key though. If a cursor is
holding a place in a record read from the table, wouldn't the place be an
address (64 bit no less) and that is what it is going to use.
The newbie DBA (I don't qualify to look at performance anymore) says it is
identifying a query with a where clause as the culprit. Maybe the ADO does
use a where update.
>
>There may be a suite of fortunate circumstances that saves poor code,
>but usually at some point the stars do not align any more.
Interesting point but it makes my point. The fortunate circumstances for me
was an adequate server and setup before the date of change. After changing
servers MY circumstances are now unfortunate!
It's like my race car ran 160 mph before the pit crew changed engines, now it
runs 80 MPH and the reason I lose races is because of my poor driving ability.
The looping procedure we use for this process use to process about 10,000 per
minute, now it takes twice as long.
I'm concerned about prioritization, my process should be low, with the time
slices for other users higher so they get their i/o immediately and no wait
cycles.
Could that be a problem and can it be adjusted?
>
>Anwyay, you say that you think the previous server was SQL 2000. In
>that case, the problem may be that all statistics were dropped with
>the upgrade, and running sp_updatestats on all databases is required.
Wouldn't new stats be done as processes were run. I drop and recreate
utility tables a lot.
They use the same structures and names. Would that mess with statistics?
>
>>>As for the SELECT COUNT(*) a start could be to defragment the table.
>>>
>> I wonder about that too.. Of course, the table is truncated everyday and
>> then rebuilt. Seems like the i/o would handle that OK.
>
>How is that truncation carried out? TRUNCATE TABLE? Or do you use
>DELETE?
>
I was doing deletes but they absolutely failed when the new server was
installed.
I went to truncate table and at least the table can be emptied now.
Something else just absolutely dragged down the system. There was a join in
an old routine that used a concatenated string of two 10 char fields as a
join. Worked fine when getting a few records but when it tried to join 47,
000 records to 82,000 it failed miserably.
Putting them into one string made it work fine. I'm not advocating poor join
factors or inefficient code but in an isolated world, seems like 64 bit
addressing, two processor, 4 GB (maybe more) and unlimited disk space should
be able to do the concatenated join.
Is it possible? And if it is, why did it fail on this machine?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-performance/200908/1
Erland Sommarskog
> Microsoft SQL Server 2005 - 9.00.4035.00 (X64) Nov 24 2008 16:17:31
> Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit)
> on Windows NT 5.2 (Build 3790: Service Pack 2)
So that is 64-bit SQL Server, good.
> 3 - adUseClient
>
> You scared me! Looks like all of mine are adUseClient.
Good!
> When I wrote the original code in 2002, I tried T-SQL but since the
> debugger was flawed and I was greener than I am now and so I used VB 6.
> I really like the step through ability to debug and code on the go.
>
> are there better debuggers for T-SQL and can you recommend one?
With SQL Server Mgmt Studio 2005, there is no built-in debugger; you
have to use Visual Studio. SSMS 2008 has a debugger built in. But it
is not that different from the debugger in Query Analyzer in terms of
capabilities.
Personally, I hardly use the T-SQL debugger. Through the years, I've found
that there are so many things for it to be aligned for it do work.
Throwing in debug PRINT and SELECT usually works fine. Although it depends
a the type of code, and that brings us to the heart of the matter. If
you write T-SQL code as if it was VB, that you still iterate over the
data, then it's nice to have a traditional debugger. But in SQL you
firstmost try to work with data in sets. That is, instead of running
a loop, you compute and update all at once. In the most cases, this is
a lot more efficient. And the need for a traditional debugger is less.
But, granted, not all problems lends themselves to set-based operations,
and since I don't know about your VB calculations, I can't tell. If you
need to loop over the data, it may be more efficient to do this client-
side. At least if you perform the update in an efficient way; I'll come
back to that.
> We have started truncating the transaction logs more.
Truncating the transaction logs? Do you mean that you back them up
more regularly? You should never truncate the log without backing up,
but in very special situations. If you truncate the log, you lose
the option of restoring to a point in time. If you don't need to restore
to a point in time, you should run with simple recovery.
> However! Since the cursor is clientside, the only weight in my view is
> the update query - oops!
>
> There is NO update query .. The code is updating individual fields
> through the recordset.fields object depending on IF conditionals.
>
> Seems like the update query would be an ADO generated Update when the
> row is changed.
> Actually I think I have to use the .UPDATE command or it loses it place.
As far as I know, ADO only updates if you say .Update or .UpdateBatch.
But you should not do that. As I understood, you are updating a bunch
of rows when you are done, or the entire table. The best would be to
build an XML document from the recordset, and pass that XML document
to a stored procedure and shred that into table format. Then you update
the table all at once. That's an efficient update. If you update row
by row, it will not scale very well.
> looks like there is no index on my primary key though. If a cursor is
> holding a place in a record read from the table, wouldn't the place be an
> address (64 bit no less) and that is what it is going to use.
If there is a PRIMARY KEY constraint, there is an index, as that is how
primary keys are implemented in SQL Server. But if no one bother to
create primary-key constraint, things are bad.
> Interesting point but it makes my point. The fortunate circumstances
> for me was an adequate server and setup before the date of change.
> After changing servers MY circumstances are now unfortunate!
Or maybe just what you deserve! Since I have no information about the
code, tables or indexes, I can only speculate why things go slow.
But SQL Server Profiler is a good tool to track down slow queries.
> I'm concerned about prioritization, my process should be low, with the
> time slices for other users higher so they get their i/o immediately and
> no wait cycles.
>
> Could that be a problem and can it be adjusted?
You cannot set priorties in SQL 2005. In SQL Server 2008, there is
something called Resource Governor, but it is only in Enterprise Edition,
and you are on Standard.
>>Anwyay, you say that you think the previous server was SQL 2000. In
>>that case, the problem may be that all statistics were dropped with
>>the upgrade, and running sp_updatestats on all databases is required.
>
> Wouldn't new stats be done as processes were run. I drop and recreate
> utility tables a lot.
> They use the same structures and names. Would that mess with statistics?
Well, I know as much about your system as you tell me, so I can only
give the standard answers. Not updating the statistics is a common error,
but a lot of people have found that their performance degraded after
moving to a new version of SQL Server, even if they updated all statistics.
There are always changes to the optimizer, usually to the better for
most of the population, but sometimes it backfires.
> I was doing deletes but they absolutely failed when the new server was
> installed.
Since DELETE logs every deleted row, deleting many rows takes time.
Furthermore, in SQL 2005, there is a ghost cleanup process which takes
care of deallocation of extents. This can have some unexpected effects.
I've seen SELECT COUNT(*) run for minutes to tell me that there were
zero rows. The table had been inflated to 3 GB, and then all rows
deleted. But they were still allocated to the table.
TRUNCATE TABLE just deallocates the extents, so it's faster. I don't
recall whether the actual allocation is deferred in this case to. If
it is, it could explain why your SELECT COUNT(*) takes such a time.
> Something else just absolutely dragged down the system. There was a
> join in an old routine that used a concatenated string of two 10 char
> fields as a join. Worked fine when getting a few records but when it
> tried to join 47, 000 records to 82,000 it failed miserably.
>
> Putting them into one string made it work fine. I'm not advocating poor
> join factors or inefficient code but in an isolated world, seems like 64
> bit addressing, two processor, 4 GB (maybe more) and unlimited disk
> space should be able to do the concatenated join.
You mean you had:
SELECT ...
FROM a
JOIN b ON a.col1 + col2 = b.col1 + b.col2
That certainly looks like something slow to me. Particularly, it
looks like something where it is not possible to use an index, where
as with:
SELECT ...
FROM a
JOIN b ON a.col1 = b.col1
AND a.col2 = b.col2
the odds are a lot better.
Just because you have 64-bit, 4 GB etc does not mean that things can
run slow. When you work with the amount of data you do in a relational
databases, there are lot of oppurtunities to make things really
slow, particularly if you don't index carefully.
GaryBMemphis via SQLMonster.com
On July 5th, a routine that has been thousands of times, doing a large loop
with a table update command at the bottom of each loop on the current record
might take 8 minutes to run, with no noticeable effect on other users or
applications. In fact, the looping application is used for analysis and can
be run concurrently by several users, however, that is seldom.
On July 6th, a new server was dropped in place with new security constraints.
On July 7th and everyday since then, the same routine takes twice as long and
prevents other users from getting their time slices.
It ain't the code that is causing the problem!
Are you sure that a particular user or database cannot be given a lower
prioritization thread?
Erland Sommarskog
> loop with a table update command at the bottom of each loop on the
> current record might take 8 minutes to run, with no noticeable effect on
> other users or applications. In fact, the looping application is used
> for analysis and can be run concurrently by several users, however, that
> is seldom.
>
> On July 6th, a new server was dropped in place with new security
> constraints.
>
> On July 7th and everyday since then, the same routine takes twice as
> long and prevents other users from getting their time slices.
>
> It ain't the code that is causing the problem!
I'm afraid I've seen enough of bad SQL code actually running fairly
smoothly, until the day it didn't. So I'm not prepared to agree on
that.
In any case, the information you have given is miniscule, and I can only
give general advice like use Profiler to find out what is slow, and
then inspect the query plan for that query. If the old server is
still available, compare with that server.
> Are you sure that a particular user or database cannot be given a lower
> prioritization thread?
In SQL 2005, you can't. In SQL 2008 Enterprise Edition, the Resource
Governor can deal with this situation to some extent.