|
Home > Archive > Microsoft SQL Server forum > August 2005 > Why the performace differs?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Why the performace differs?
|
|
|
| All,
I have a problem regarding SQL Server 2000 SP3,
I have SP that calls other SP and it inserts about 30,000 records as a
time,
in the development environment (MS Windows 2003 Enterprise, 256 RAM,
3.0 GHz Intel Processor) takes about 6 seconds to run this SP.
But, with the same Software but, 2.6 GHz Intel and 1 GB Ram, it runs
very slow it takes more than 135 Seconds to run,
I have read a lot of articles about expanding the SQL Memory and give
it a higher process privilege but, with no use,
I don't know where the problem is, do you have any idea about what is
the problem?
Thank you in advance,
MAG
| |
|
|
| Mohamed Gad 2005-08-30, 7:24 am |
| Yes, SQL Server is having the highest piority and consumes about 800MB
from the memory (I made this by configure the SQL Server from the
Enterprise Manager)
but still very slow.
*** Sent via Developersdex http://www.droptable.com ***
| |
| Greg D. Moore \(Strider\) 2005-08-30, 9:25 am |
|
"Mohamed Gad" <mohamed.gad@gmail.com> wrote in message
news:YPVQe.2$XV.213@news.uswest.net...
> Yes, SQL Server is having the highest piority and consumes about 800MB
> from the memory (I made this by configure the SQL Server from the
> Enterprise Manager)
Does one have logging enabled and the other doesn't?
What about the disk subsystem?
That's most likely the chokepoint.
>
> but still very slow.
>
>
> *** Sent via Developersdex http://www.droptable.com ***
| |
| Mohamed Gad 2005-08-30, 9:25 am |
|
>Does one have logging enabled and the other doesn't?
How would i know weather the Logging is enabled or not?
>What about the disk subsystem?
I don't get it.. do you mean the
FAT Type if so? Both are NTFS Partitions
Thank you soo much for your reply, but, I hope i would find a solution.
*** Sent via Developersdex http://www.droptable.com ***
| |
|
| many possibilities. For instance, there could be lock contention with
another connection in production
| |
| Mohamed Gad 2005-08-30, 9:25 am |
| No, this database is accessed by only me no one is using it.
really, this slow performance is really weared,
I have tested it and when i run the Stored Procedure the sql server
process is eating up to 98% from the processor,
and the Stored Procedure inserts something like that
INSERT INTO CardStatus
(SrlNo, StatusID,StatusDate,
PresentStatus,[TimeStamp])
Values
(@SRL, 0,GetDate(),0,GetDat
e())
set @CurrstatID = (select IDENT_CURRENT('CardS
tatus'))
/*Card Loc*/
Insert into CardLoc
(SrlNo, LocID, TransferDate, TransID, [TimeStamp])
Values
(@SRL, 0, GetDate(), @TransID,GetDate())
set @CurrlocID = (select IDENT_CURRENT('CardL
oc'))
/*Card Main*/
INSERT INTO dbo.CardMain
(
SrlNo, CTypeID, [TimeStamp], CardStatusID, CartonNo, BoxNo,
PacketNo,LocID
)
Values
(
@SRL,Cast(Substring(
@SRL,1,1) AS bigint), GETDATE(),@CurrstatI
D,
Cast(substring(@Cart
on,2,3) as varchar), Cast(Substring(@Box,
5,2)
as varchar), Cast(Substring(@Pack
et,5,2) as varchar),@CurrlocID
)
which in a loop of 10,000 time,
if you need more details, I can send them to you,
Thanks
*** Sent via Developersdex http://www.droptable.com ***
| |
| Erland Sommarskog 2005-08-30, 8:24 pm |
| MAG (mohamed.gad@gmail.com) writes:
> I have a problem regarding SQL Server 2000 SP3,
> I have SP that calls other SP and it inserts about 30,000 records as a
> time,
> in the development environment (MS Windows 2003 Enterprise, 256 RAM,
> 3.0 GHz Intel Processor) takes about 6 seconds to run this SP.
>
> But, with the same Software but, 2.6 GHz Intel and 1 GB Ram, it runs
> very slow it takes more than 135 Seconds to run,
Are the tables the same size on both machines?
What does DBCC SHOWCONTIG say for the tables on the two machines? That is,
one could have severe fragmentation.
Are indexes the same?
And - most of all - are there any triggers on the tables in either server?
Also, I noted from your other post that you are using IDENT_CURRENT. Note
that this function is not safe for concurrent use. (Yeah, I noticed that
did not have concurrent access to your database, but nevertheless.)
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
| |
| Mohamed Gad 2005-08-31, 3:24 am |
| Dear Erland,
the database are the same, I made a backup from the developement one and
restored it to the Test one,
so, it should be all the same,
I don't know what's going wrong, but, I'll do some Monitoring for some
counters and I'll share the results with you, maybe we could reach
somewhere.
Hopefully,
Thanks,
*** Sent via Developersdex http://www.droptable.com ***
| |
| Greg D. Moore \(Strider\) 2005-08-31, 7:24 am |
|
"Mohamed Gad" <mohamed.gad@gmail.com> wrote in message
news:7RYQe.1422$yn2.1342@news.uswest.net...
>
>
>
> How would i know weather the Logging is enabled or not?
Go into Enterprise manager and check the properties of the DB.
It should be FULL, Bulk Logged or Simple.
>
>
>
> I don't get it.. do you mean the
> FAT Type if so? Both are NTFS Partitions
No, I mean the layout and type of disks.
RAID vs non-RAID (and type of RAID).
ATA vs SATA vs SCSI etc.
Log files on same physical drive(s) as data files. Location of temp db?
>
> Thank you soo much for your reply, but, I hope i would find a solution.
Well, we're trying to help.
>
>
> *** Sent via Developersdex http://www.droptable.com ***
| |
| Mohamed Gad 2005-08-31, 9:28 am |
| the Recovery Option is set to FULL
and the Hard disk subsystem is ATA NTFS (ONE PARTITION) system and
non-RAID
and the Log files are on the same Partition.
*** Sent via Developersdex http://www.droptable.com ***
| |
| Erland Sommarskog 2005-08-31, 8:23 pm |
| Mohamed Gad (mohamed.gad@gmail.com) writes:
> the database are the same, I made a backup from the developement one and
> restored it to the Test one,
Thanks. That is valuable information. It would have been a good idea
to include that originally, to avoid unnecessary speculation.
> so, it should be all the same, I don't know what's going wrong, but,
> I'll do some Monitoring for some counters and I'll share the results
> with you, maybe we could reach somewhere.
Did you ever post the queries and the query plans?
It's difficult to give good advice with so little information. I would
definitely look into setting up a second instance on the dev box, and
install SP4 on it, to see whether the service pack is part of the equation.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
|
|
|
|
|