intermittent performance problems while insertig records

I have a SP that I use to insert records in a table. This SP is called
hundreds of times per minute.

Most inserts complete very fast. And the profiler data is as follows:

CPU: 0
Reads: 10
Writes: 1
Duration: varies from 1 to 30

But once in a while the insert SP seems to stall and takes a very long
time. Here's the info returned by profiles in this case:

CPU: 0
Reads: 10
Writes: 1
Duration: can vary from 6000 to 60000

Note that the CPU, reads, writes remain the same. But the duration of
the SP increases. What could be the reason for this?? The SP
eventually completes in all cases - its just that they seem to take a
very long time sometimes??

What areas should I investigate??

Thanks in advance,

DKDK (dk@.realmagnet.com) writes:
> Most inserts complete very fast. And the profiler data is as follows:
> CPU: 0
> Reads: 10
> Writes: 1
> Duration: varies from 1 to 30
> But once in a while the insert SP seems to stall and takes a very long
> time. Here's the info returned by profiles in this case:
> CPU: 0
> Reads: 10
> Writes: 1
> Duration: can vary from 6000 to 60000
> Note that the CPU, reads, writes remain the same. But the duration of
> the SP increases. What could be the reason for this?? The SP
> eventually completes in all cases - its just that they seem to take a
> very long time sometimes??

The most likely cause is blocking. That is, another process accesses
data from the table, which prevents the INSERT operation to continue.
It could be that this access operation is poorly written, and does not
make use of indexes.

Another possible cause is autogrow. This is more likely to be the cause
if the database is small. Say that you started with 10 MB database. The
default is to autogrow with 10%. You will get frequent autogrows. On
the other hand, if the database is 10 GB in size, the autogrows will
not appear equally often. The remedy here is to pre-grow to a determined
size.

Rather than the data file autogrowing, it could be the transaction
log that autogrows.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||you should look at some options like auto shrink and auto growth wich may
use a lot of I/O; you should shrink manually during offline hours and give
auto growth a sufficient value for a week/month of insert activity.

Maj

"DK" <dk@.realmagnet.com> wrote in message
news:14f9b5f4.0309091151.2c332581@.posting.google.c om...
> I have a SP that I use to insert records in a table. This SP is called
> hundreds of times per minute.
> Most inserts complete very fast. And the profiler data is as follows:
> CPU: 0
> Reads: 10
> Writes: 1
> Duration: varies from 1 to 30
> But once in a while the insert SP seems to stall and takes a very long
> time. Here's the info returned by profiles in this case:
> CPU: 0
> Reads: 10
> Writes: 1
> Duration: can vary from 6000 to 60000
> Note that the CPU, reads, writes remain the same. But the duration of
> the SP increases. What could be the reason for this?? The SP
> eventually completes in all cases - its just that they seem to take a
> very long time sometimes??
> What areas should I investigate??
> Thanks in advance,
> DK|||Thanks for your replies. I am sure auto-grow is not causing this -
because the datafile size is almost 10Gb and the growth is set to 25%.
And I have noticed this issue quite frequently - sometimes 4-5 times in
a day.

Blocking could be an issue - how can I find if "blocking" is indeed the
reason - does profiler have a counter that indicates "blocking"??

Thanks

DK

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||DK (netedk1@.yahoo.com) writes:
> Blocking could be an issue - how can I find if "blocking" is indeed the
> reason - does profiler have a counter that indicates "blocking"??

Hm, don't remember off hand if you can track blocking in Profiler.
Look in Books Online under Administrating SQL Server/Monitoriing Server
Performance. There is a very good description of what events and what
data you can catch with Profiler.

The simplest way to see blocking is to run sp_who, and look for non-zero
values in the Blk column. But your blocking scenarios appear to fairly
short, a couples of seconds, so you would have to run it frequently to
see any.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Blocking will manifest itself as long duration for the 'Lock: Acquired'
event. You can filter these events based on your target threshold (e.g.
Duration >= 5000). It may be helpful to include the ObjectID column in
the trace.

The sp_who (or sp_who2) technique mentioned by Erland is handy to
monitor and analyze blocking while it is occurring. You can also use
sp_lock to help identify the contended resource.

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"DK" <netedk1@.yahoo.com> wrote in message
news:3f5e968e$0$62084$75868355@.news.frii.net...
> Thanks for your replies. I am sure auto-grow is not causing this -
> because the datafile size is almost 10Gb and the growth is set to 25%.
> And I have noticed this issue quite frequently - sometimes 4-5 times
in
> a day.
> Blocking could be an issue - how can I find if "blocking" is indeed
the
> reason - does profiler have a counter that indicates "blocking"??
> Thanks
> DK
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Thanks for all your suggestions - I have been trying out your
recommendations - but looks like blocking is not the issue here. I tried
running sp_who/ sp_who2 while the inserts seemed to have stuck (and was
taking a long time) - but there was no blocking.

I am wondering if it could be something to so with the network
connection or the database connection that my app. server makes with the
db server. Here's some more info on what exactly is happening:

In my app. I have 3 threads that could be inserting records in this same
table.

Thread 1: loop through 10000 times and insert records in TableA

Thread 2: loop through 5000 times and insert records in TableA

Thread 3: loop through 20000 times and insert records in TableA

All these 3 threads may be running simultaneously. And it often happens
that one of these threads get stuck while the other keeps writing. So
say for example Thread 1 is has written 1003 records; the 1004th record
may take almost 10-60 seconds. And thread2 keeps writing. Thread1
eventually starts again; but again gets stuck at some other number.

While this is happening, I have observed that once a particular thread
gets stuck - its always that thread that keeps having issues. While the
other threads keep going on. This leads me to suspect that it could be
the database connection. But am not sure how I can confirm this? Or if
this could be the case at all? Any ideas how I can go about
investigating this??

Thanks for all your help once again...

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Do you have a separate database connection for each thread? How many
CPUs on the database and app servers?

You might examine master..sysprocesses info while a thread is stalled to
see if that indicates why a thread is waiting.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"DK" <netedk1@.yahoo.com> wrote in message
news:3f608771$0$62077$75868355@.news.frii.net...
> Thanks for all your suggestions - I have been trying out your
> recommendations - but looks like blocking is not the issue here. I
tried
> running sp_who/ sp_who2 while the inserts seemed to have stuck (and
was
> taking a long time) - but there was no blocking.
> I am wondering if it could be something to so with the network
> connection or the database connection that my app. server makes with
the
> db server. Here's some more info on what exactly is happening:
> In my app. I have 3 threads that could be inserting records in this
same
> table.
> Thread 1: loop through 10000 times and insert records in TableA
> Thread 2: loop through 5000 times and insert records in TableA
> Thread 3: loop through 20000 times and insert records in TableA
> All these 3 threads may be running simultaneously. And it often
happens
> that one of these threads get stuck while the other keeps writing. So
> say for example Thread 1 is has written 1003 records; the 1004th
record
> may take almost 10-60 seconds. And thread2 keeps writing. Thread1
> eventually starts again; but again gets stuck at some other number.
> While this is happening, I have observed that once a particular thread
> gets stuck - its always that thread that keeps having issues. While
the
> other threads keep going on. This leads me to suspect that it could be
> the database connection. But am not sure how I can confirm this? Or if
> this could be the case at all? Any ideas how I can go about
> investigating this??
> Thanks for all your help once again...
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||DK (netedk1@.yahoo.com) writes:
> I am wondering if it could be something to so with the network
> connection or the database connection that my app. server makes with the
> db server. Here's some more info on what exactly is happening:
> In my app. I have 3 threads that could be inserting records in this same
> table.
> Thread 1: loop through 10000 times and insert records in TableA
> Thread 2: loop through 5000 times and insert records in TableA
> Thread 3: loop through 20000 times and insert records in TableA
> All these 3 threads may be running simultaneously. And it often happens
> that one of these threads get stuck while the other keeps writing. So
> say for example Thread 1 is has written 1003 records; the 1004th record
> may take almost 10-60 seconds. And thread2 keeps writing. Thread1
> eventually starts again; but again gets stuck at some other number.

I have to admit that at this point I am completely stumped. If it is
not blocking, nor autogrow, then I can't think of anything obvious.
But here are some ideas how to improve your application, and thus
remove the problem.

1) Issue SET NOCOUNT ON when you connect.
2) Use the bulk-copy interface instead.
3) Form an XML document of all rows to insert, and then send down
all the data to a stored procedure that unpacks the XML into a
result set with OPENXML. This can give a tremendous performance
boost.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Intermittent Performance Issue

I am having a severe performance issue with a procedure, but it only happens
intermittently so it's difficult to track down. The procedure is too
complex to include in this post because it uses nested views with lots of
tables etc. Basically it does this:
1. create temp table 1 (fast)
2. insert query result into table 1 (100 lines or so)
3. create temp table 2
4. query temp table 1 and insert results into table 2
5. query temp table 1 again (different summary) and insert results into
table 2
6. query temp table 1 again (different summary) and insert results into
table 2
7. query temp table 1 again (different summary) and insert results into
table 2
8. update temp table 2
9. select * from temp table 2 as output from procedure
Normally it runs very fast (a couple of seconds). When it's slow, it takes
30 - 150 seconds even though it's only dealing with a hundred lines or so.
Most of the time is consumed by lines 4 and 8 (about half and half). The
real mystery is line 4. It's only dealing with 100 lines and it sometimes
takes a minute! It is always very fast or very slow.
Yesterday when I investigated it I found that the C drive, which contained
SQL Server, my main database and the temp database, was nearly full. So I
moved the databases to the D and E drives with lots of space and freed up a
bunch of space on C. But the problem continues today.
When testing, I can run the procedure over and over again, but it always
finishes in a couple of seconds. When I try to display an estimated
execution plan, it gives me "Invalid object name" errors on two of my temp
tables. How can I debug it?
Is this type of performance problem (and inability to debug) caused by the
use of temp tables? If so, I could rewrite it not to use them, but it will
require several UNIONS of similar queries, which seemed to me would take
longer.
I tried setting the transaction isolation level to "READ UNCOMMITTED" in
case the problem was somehow caused by blocking, but that didn't make any
difference.
Rick.It sounds like you may have disk or cpu bottlenecks but that's not a lot to
go on. Try running perfmon to see if you can spot what the disk and
processor queues are like when it's happening. You might also try using a
table variable instead of a temp table and see if it makes a difference.
--
Andrew J. Kelly
SQL Server MVP
"Rick Harrrison" <rick@.knowware.com> wrote in message
news:eJOY1Ze4DHA.1636@.TK2MSFTNGP12.phx.gbl...
> I am having a severe performance issue with a procedure, but it only
happens
> intermittently so it's difficult to track down. The procedure is too
> complex to include in this post because it uses nested views with lots of
> tables etc. Basically it does this:
> 1. create temp table 1 (fast)
> 2. insert query result into table 1 (100 lines or so)
> 3. create temp table 2
> 4. query temp table 1 and insert results into table 2
> 5. query temp table 1 again (different summary) and insert results
into
> table 2
> 6. query temp table 1 again (different summary) and insert results
into
> table 2
> 7. query temp table 1 again (different summary) and insert results
into
> table 2
> 8. update temp table 2
> 9. select * from temp table 2 as output from procedure
> Normally it runs very fast (a couple of seconds). When it's slow, it
takes
> 30 - 150 seconds even though it's only dealing with a hundred lines or so.
> Most of the time is consumed by lines 4 and 8 (about half and half). The
> real mystery is line 4. It's only dealing with 100 lines and it sometimes
> takes a minute! It is always very fast or very slow.
> Yesterday when I investigated it I found that the C drive, which contained
> SQL Server, my main database and the temp database, was nearly full. So I
> moved the databases to the D and E drives with lots of space and freed up
a
> bunch of space on C. But the problem continues today.
> When testing, I can run the procedure over and over again, but it always
> finishes in a couple of seconds. When I try to display an estimated
> execution plan, it gives me "Invalid object name" errors on two of my temp
> tables. How can I debug it?
> Is this type of performance problem (and inability to debug) caused by the
> use of temp tables? If so, I could rewrite it not to use them, but it
will
> require several UNIONS of similar queries, which seemed to me would take
> longer.
> I tried setting the transaction isolation level to "READ UNCOMMITTED" in
> case the problem was somehow caused by blocking, but that didn't make any
> difference.
> Rick.
>|||to see the estimated execution plan
first create the temp tables, then use display est. ..
(from same session)
or use show execution plan, which runs the query and shows
the execution plan
if you don't like the above, do as andrew suggested and
switch to table variables.
also, on the test system, run profiler, capturing
SP:Recompile, see how times your sp recompiles during
execution,
recompile set points in the advent of inserts to temp
tables are 6 rows, 500 rows, and every 20%.
table variables do not cause recompiles
you can also use the hint OPTION (KEEP PLAN) to inhibit 6
row recompile and (KEEP FIXED PLAN) to inhibit 500+ row
recompiles
>--Original Message--
>I am having a severe performance issue with a procedure,
but it only happens
>intermittently so it's difficult to track down. The
procedure is too
>complex to include in this post because it uses nested
views with lots of
>tables etc. Basically it does this:
> 1. create temp table 1 (fast)
> 2. insert query result into table 1 (100 lines or so)
> 3. create temp table 2
> 4. query temp table 1 and insert results into table 2
> 5. query temp table 1 again (different summary) and
insert results into
>table 2
> 6. query temp table 1 again (different summary) and
insert results into
>table 2
> 7. query temp table 1 again (different summary) and
insert results into
>table 2
> 8. update temp table 2
> 9. select * from temp table 2 as output from procedure
>Normally it runs very fast (a couple of seconds). When
it's slow, it takes
>30 - 150 seconds even though it's only dealing with a
hundred lines or so.
>Most of the time is consumed by lines 4 and 8 (about half
and half). The
>real mystery is line 4. It's only dealing with 100 lines
and it sometimes
>takes a minute! It is always very fast or very slow.
>Yesterday when I investigated it I found that the C
drive, which contained
>SQL Server, my main database and the temp database, was
nearly full. So I
>moved the databases to the D and E drives with lots of
space and freed up a
>bunch of space on C. But the problem continues today.
>When testing, I can run the procedure over and over
again, but it always
>finishes in a couple of seconds. When I try to display
an estimated
>execution plan, it gives me "Invalid object name" errors
on two of my temp
>tables. How can I debug it?
>Is this type of performance problem (and inability to
debug) caused by the
>use of temp tables? If so, I could rewrite it not to use
them, but it will
>require several UNIONS of similar queries, which seemed
to me would take
>longer.
>I tried setting the transaction isolation level to "READ
UNCOMMITTED" in
>case the problem was somehow caused by blocking, but that
didn't make any
>difference.
> Rick.
>
>.
>|||Hi Rick,
Thank you for using the Newsgroup and I am reviewing your post and want to
know if the community member's suggestions are helpful or if you still have
questions about it. Here I just want to provide you some more information
of troubleshooting the problem:
1) HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
http://support.microsoft.com/?id=243589
2) HOW TO: Troubleshoot Application Performance Issues
http://support.microsoft.com/?id=298475
3)INF: Troubleshooting Stored Procedure Recompilation
http://support.microsoft.com/?id=243586
Hope this helps! If you still have any questios about it, please feel free
to post new message here and I am glad to help! Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Intermittent Performance Issue

I am having a severe performance issue with a procedure, but it only happens
intermittently so it's difficult to track down. The procedure is too
complex to include in this post because it uses nested views with lots of
tables etc. Basically it does this:
1. create temp table 1 (fast)
2. insert query result into table 1 (100 lines or so)
3. create temp table 2
4. query temp table 1 and insert results into table 2
5. query temp table 1 again (different summary) and insert results into
table 2
6. query temp table 1 again (different summary) and insert results into
table 2
7. query temp table 1 again (different summary) and insert results into
table 2
8. update temp table 2
9. select * from temp table 2 as output from procedure
Normally it runs very fast (a couple of seconds). When it's slow, it takes
30 - 150 seconds even though it's only dealing with a hundred lines or so.
Most of the time is consumed by lines 4 and 8 (about half and half). The
real mystery is line 4. It's only dealing with 100 lines and it sometimes
takes a minute! It is always very fast or very slow.
Yesterday when I investigated it I found that the C drive, which contained
SQL Server, my main database and the temp database, was nearly full. So I
moved the databases to the D and E drives with lots of space and freed up a
bunch of space on C. But the problem continues today.
When testing, I can run the procedure over and over again, but it always
finishes in a couple of seconds. When I try to display an estimated
execution plan, it gives me "Invalid object name" errors on two of my temp
tables. How can I debug it?
Is this type of performance problem (and inability to debug) caused by the
use of temp tables? If so, I could rewrite it not to use them, but it will
require several UNIONS of similar queries, which seemed to me would take
longer.
I tried setting the transaction isolation level to "READ UNCOMMITTED" in
case the problem was somehow caused by blocking, but that didn't make any
difference.
Rick.It sounds like you may have disk or cpu bottlenecks but that's not a lot to
go on. Try running perfmon to see if you can spot what the disk and
processor queues are like when it's happening. You might also try using a
table variable instead of a temp table and see if it makes a difference.
Andrew J. Kelly
SQL Server MVP
"Rick Harrrison" <rick@.knowware.com> wrote in message
news:eJOY1Ze4DHA.1636@.TK2MSFTNGP12.phx.gbl...

quote:

---

> I am having a severe performance issue with a procedure, but it only

---

happens

quote:

---

> intermittently so it's difficult to track down. The procedure is too
> complex to include in this post because it uses nested views with lots of
> tables etc. Basically it does this:
> 1. create temp table 1 (fast)
> 2. insert query result into table 1 (100 lines or so)
> 3. create temp table 2
> 4. query temp table 1 and insert results into table 2
> 5. query temp table 1 again (different summary) and insert results

---

into

quote:

---

> table 2
> 6. query temp table 1 again (different summary) and insert results

---

into

quote:

---

> table 2
> 7. query temp table 1 again (different summary) and insert results

---

into

quote:

---

> table 2
> 8. update temp table 2
> 9. select * from temp table 2 as output from procedure
> Normally it runs very fast (a couple of seconds). When it's slow, it

---

takes

quote:

---

> 30 - 150 seconds even though it's only dealing with a hundred lines or so.
> Most of the time is consumed by lines 4 and 8 (about half and half). The
> real mystery is line 4. It's only dealing with 100 lines and it sometimes
> takes a minute! It is always very fast or very slow.
> Yesterday when I investigated it I found that the C drive, which contained
> SQL Server, my main database and the temp database, was nearly full. So I
> moved the databases to the D and E drives with lots of space and freed up

---

a

quote:

---

> bunch of space on C. But the problem continues today.
> When testing, I can run the procedure over and over again, but it always
> finishes in a couple of seconds. When I try to display an estimated
> execution plan, it gives me "Invalid object name" errors on two of my temp
> tables. How can I debug it?
> Is this type of performance problem (and inability to debug) caused by the
> use of temp tables? If so, I could rewrite it not to use them, but it

---

will

quote:

---

> require several UNIONS of similar queries, which seemed to me would take
> longer.
> I tried setting the transaction isolation level to "READ UNCOMMITTED" in
> case the problem was somehow caused by blocking, but that didn't make any
> difference.
> Rick.
>

---

|||Hi Rick,
Thank you for using the Newsgroup and I am reviewing your post and want to
know if the community member's suggestions are helpful or if you still have
questions about it. Here I just want to provide you some more information
of troubleshooting the problem:
1) HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
http://support.microsoft.com/?id=243589
2) HOW TO: Troubleshoot Application Performance Issues
http://support.microsoft.com/?id=298475
3)INF: Troubleshooting Stored Procedure Recompilation
http://support.microsoft.com/?id=243586
Hope this helps! If you still have any questios about it, please feel free
to post new message here and I am glad to help! Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.sql

Interesting SQL...

Anyone else experience this?

A developer just finished complaining about the performance of one of our databases. Well, he sent me the query and I couldn't understand why it was such a dog. Anyways I rewrote it. The execution plan is totally different between the two. I had no idea specifying the join made such a difference. First sql executed in 7 minutes that 2nd took 1 second.

SELECT
dbo.contract_co.producer_num_id, contract_co_status
FROM
dbo.contract_co,
dbo.v_contract_co_status
WHERE ( dbo.v_contract_co_status.contract_co_id = dbo.contract_co.contract_co_id )
AND contract_co_status = 'Pending'
OR ( contract_co_status = 'Active' and effective_date > '1/1/2004' )

SELECT
dbo.contract_co.producer_num_id, contract_co_status
FROM dbo.contract_co
INNER JOIN dbo.v_contract_co_status
ON dbo.contract_co.contract_co_id = dbo.v_contract_co_status.contract_co_id
WHERE contract_co_status = 'Pending'
OR ( contract_co_status = 'Active' and effective_date > '1/1/2004' )The two queries look like they will give different results, too. The first one appears to include a cartesian join. The OR in the where clause makes all the difference.|||Don't want to sound like a snob, but it's all due to the order of processing by QP:

1. JOIN
2. GROUP
3. WHERE
4. HAVING

By rewriting the old query you filtered out what the first query had to deal with while still trying to JOIN.|||actually, i believe it's

1. JOIN
2. WHERE
3. GROUP
4. HAVING

Intel Hyper Threading

Hi
Has anyone had any SQL performance issues when Hyper Threading is enabled on
BIOS? Do you have any comments regarding to HT?
I know this is a bit of a grey area for Citrix - but i have not seen any
comments about this for SQL
Regards
James
On our SQL boxes, we keep HT enabled and tweak the SQL Server max degree of
parallelism config option to specify no more than the number of processor
cores.
Every application is different so, if performance is important to you,
consider taking the time to run benchmarks with your application under
various configurations.
Hope this helps.
Dan Guzman
SQL Server MVP
"James" <hushdontspamme@.hotmail.com> wrote in message
news:eU68yba9FHA.1148@.tk2msftngp13.phx.gbl...
> Hi
> Has anyone had any SQL performance issues when Hyper Threading is enabled
> on BIOS? Do you have any comments regarding to HT?
> I know this is a bit of a grey area for Citrix - but i have not seen any
> comments about this for SQL
> Regards
> James
>
|||Have a look here:
http://blogs.msdn.com/slavao/archive...12/492119.aspx
Markus
|||<Excerpt>
So does it mean you have to disable HT when using SQL Server? The answer is
it really depends on the load and hardware you are using.
You have to test your application with HT on and off under heavy loads to
understand HT's implications.
</Excerpt>
There is no substitute for thorough performance testing when you have a
demanding application and/or expect to tax the hardware. I ran a benchmark
with our app on a 4-way (8 logical) proc server and we got 15-20% percent
improvement with HT enabled at the bios level. It would have been a pity to
throw this away.
Hope this helps.
Dan Guzman
SQL Server MVP
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1133359063.147112.317730@.z14g2000cwz.googlegr oups.com...
> Have a look here:
> http://blogs.msdn.com/slavao/archive...12/492119.aspx
> Markus
>
|||Hi
Hi I wonder which tools (benchmarks) are you using to get the
performace infomation.
Thanks
Yoel
Dan Guzman wrote:
> <Excerpt>
> So does it mean you have to disable HT when using SQL Server? The answer is
> it really depends on the load and hardware you are using.
> You have to test your application with HT on and off under heavy loads to
> understand HT's implications.
> </Excerpt>
> There is no substitute for thorough performance testing when you have a
> demanding application and/or expect to tax the hardware. I ran a benchmark
> with our app on a 4-way (8 logical) proc server and we got 15-20% percent
> improvement with HT enabled at the bios level. It would have been a pity to
> throw this away.
>
|||I performed a controlled test using our production application code and
data. The performance I reported was based on the actual elapsed time
difference. IMHO, this is the most meaningful type of test since the
objective is to optimize the server for production application processing.
Importantly, the application I tested was typical OLTP and highly optimized
with very few scans. Results could be different with an OLAP/reporting
application profile, a different OLTP application or different application
mix. I mentioned this when I posted to Slava's blog.
It can take considerable work to develop and run application benchmarks like
this. Such effort can be justified when you have demanding mission critical
applications that will fully tax your hardware but perhaps not justified
when hardware resources are less utilized. In my case, performance testing
and tuning was required anyway so that we could perform an application
migration in the shortest possible time.
Hope this helps.
Dan Guzman
SQL Server MVP
"Yoel Zumbado" <yzumbado@.hotmail.com> wrote in message
news:OK8Cfe0AGHA.3984@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Hi
> Hi I wonder which tools (benchmarks) are you using to get the performace
> infomation.
> Thanks
> Yoel
> Dan Guzman wrote:

Intel Hyper Threading

Hi
Has anyone had any SQL performance issues when Hyper Threading is enabled on
BIOS? Do you have any comments regarding to HT?
I know this is a bit of a grey area for Citrix - but i have not seen any
comments about this for SQL
Regards
JamesOn our SQL boxes, we keep HT enabled and tweak the SQL Server max degree of
parallelism config option to specify no more than the number of processor
cores.
Every application is different so, if performance is important to you,
consider taking the time to run benchmarks with your application under
various configurations.
Hope this helps.
Dan Guzman
SQL Server MVP
"James" <hushdontspamme@.hotmail.com> wrote in message
news:eU68yba9FHA.1148@.tk2msftngp13.phx.gbl...
> Hi
> Has anyone had any SQL performance issues when Hyper Threading is enabled
> on BIOS? Do you have any comments regarding to HT?
> I know this is a bit of a grey area for Citrix - but i have not seen any
> comments about this for SQL
> Regards
> James
>|||Have a look here:
http://blogs.msdn.com/slavao/archiv.../12/492119.aspx
Markus|||<Excerpt>
So does it mean you have to disable HT when using SQL Server? The answer is
it really depends on the load and hardware you are using.
You have to test your application with HT on and off under heavy loads to
understand HT's implications.
</Excerpt>
There is no substitute for thorough performance testing when you have a
demanding application and/or expect to tax the hardware. I ran a benchmark
with our app on a 4-way (8 logical) proc server and we got 15-20% percent
improvement with HT enabled at the bios level. It would have been a pity to
throw this away.
Hope this helps.
Dan Guzman
SQL Server MVP
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1133359063.147112.317730@.z14g2000cwz.googlegroups.com...
> Have a look here:
> http://blogs.msdn.com/slavao/archiv.../12/492119.aspx
> Markus
>|||Hi
Hi I wonder which tools (benchmarks) are you using to get the
performace infomation.
Thanks
Yoel
Dan Guzman wrote:
> <Excerpt>
> So does it mean you have to disable HT when using SQL Server? The answer i
s
> it really depends on the load and hardware you are using.
> You have to test your application with HT on and off under heavy loads to
> understand HT's implications.
> </Excerpt>
> There is no substitute for thorough performance testing when you have a
> demanding application and/or expect to tax the hardware. I ran a benchmar
k
> with our app on a 4-way (8 logical) proc server and we got 15-20% percent
> improvement with HT enabled at the bios level. It would have been a pity
to
> throw this away.
>|||I performed a controlled test using our production application code and
data. The performance I reported was based on the actual elapsed time
difference. IMHO, this is the most meaningful type of test since the
objective is to optimize the server for production application processing.
Importantly, the application I tested was typical OLTP and highly optimized
with very few scans. Results could be different with an OLAP/reporting
application profile, a different OLTP application or different application
mix. I mentioned this when I posted to Slava's blog.
It can take considerable work to develop and run application benchmarks like
this. Such effort can be justified when you have demanding mission critical
applications that will fully tax your hardware but perhaps not justified
when hardware resources are less utilized. In my case, performance testing
and tuning was required anyway so that we could perform an application
migration in the shortest possible time.
Hope this helps.
Dan Guzman
SQL Server MVP
"Yoel Zumbado" <yzumbado@.hotmail.com> wrote in message
news:OK8Cfe0AGHA.3984@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Hi
> Hi I wonder which tools (benchmarks) are you using to get the performace
> infomation.
> Thanks
> Yoel
> Dan Guzman wrote:

Intel Hyper Threading

Hi
Has anyone had any SQL performance issues when Hyper Threading is enabled on
BIOS? Do you have any comments regarding to HT?
I know this is a bit of a grey area for Citrix - but i have not seen any
comments about this for SQL
Regards
JamesOn our SQL boxes, we keep HT enabled and tweak the SQL Server max degree of
parallelism config option to specify no more than the number of processor
cores.
Every application is different so, if performance is important to you,
consider taking the time to run benchmarks with your application under
various configurations.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"James" <hushdontspamme@.hotmail.com> wrote in message
news:eU68yba9FHA.1148@.tk2msftngp13.phx.gbl...
> Hi
> Has anyone had any SQL performance issues when Hyper Threading is enabled
> on BIOS? Do you have any comments regarding to HT?
> I know this is a bit of a grey area for Citrix - but i have not seen any
> comments about this for SQL
> Regards
> James
>|||Have a look here:
http://blogs.msdn.com/slavao/archive/2005/11/12/492119.aspx
Markus|||<Excerpt>
So does it mean you have to disable HT when using SQL Server? The answer is
it really depends on the load and hardware you are using.
You have to test your application with HT on and off under heavy loads to
understand HT's implications.
</Excerpt>
There is no substitute for thorough performance testing when you have a
demanding application and/or expect to tax the hardware. I ran a benchmark
with our app on a 4-way (8 logical) proc server and we got 15-20% percent
improvement with HT enabled at the bios level. It would have been a pity to
throw this away.
Hope this helps.
Dan Guzman
SQL Server MVP
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1133359063.147112.317730@.z14g2000cwz.googlegroups.com...
> Have a look here:
> http://blogs.msdn.com/slavao/archive/2005/11/12/492119.aspx
> Markus
>|||Hi
Hi I wonder which tools (benchmarks) are you using to get the
performace infomation.
Thanks
Yoel
Dan Guzman wrote:
> <Excerpt>
> So does it mean you have to disable HT when using SQL Server? The answer is
> it really depends on the load and hardware you are using.
> You have to test your application with HT on and off under heavy loads to
> understand HT's implications.
> </Excerpt>
> There is no substitute for thorough performance testing when you have a
> demanding application and/or expect to tax the hardware. I ran a benchmark
> with our app on a 4-way (8 logical) proc server and we got 15-20% percent
> improvement with HT enabled at the bios level. It would have been a pity to
> throw this away.
>|||I performed a controlled test using our production application code and
data. The performance I reported was based on the actual elapsed time
difference. IMHO, this is the most meaningful type of test since the
objective is to optimize the server for production application processing.
Importantly, the application I tested was typical OLTP and highly optimized
with very few scans. Results could be different with an OLAP/reporting
application profile, a different OLTP application or different application
mix. I mentioned this when I posted to Slava's blog.
It can take considerable work to develop and run application benchmarks like
this. Such effort can be justified when you have demanding mission critical
applications that will fully tax your hardware but perhaps not justified
when hardware resources are less utilized. In my case, performance testing
and tuning was required anyway so that we could perform an application
migration in the shortest possible time.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Yoel Zumbado" <yzumbado@.hotmail.com> wrote in message
news:OK8Cfe0AGHA.3984@.TK2MSFTNGP14.phx.gbl...
> Hi
> Hi I wonder which tools (benchmarks) are you using to get the performace
> infomation.
> Thanks
> Yoel
> Dan Guzman wrote:
>> <Excerpt>
>> So does it mean you have to disable HT when using SQL Server? The answer
>> is it really depends on the load and hardware you are using.
>> You have to test your application with HT on and off under heavy loads to
>> understand HT's implications.
>> </Excerpt>
>> There is no substitute for thorough performance testing when you have a
>> demanding application and/or expect to tax the hardware. I ran a
>> benchmark with our app on a 4-way (8 logical) proc server and we got
>> 15-20% percent improvement with HT enabled at the bios level. It would
>> have been a pity to throw this away.

Integration Services extraction/loading throughput/performance

I'm new to integration services.
I want to create a centralized reporting system for our customers. Some customers have up to 1,000 sites and some are expected to grow past 5,000 sites. The sites are running POS applications and I want to extract the POS sales data from these sites. Is it practical to expect that SSIS can handle the extraction of data from this many sites and load the data into a central
SQL database? The POS sales data at the sites is stored in SqlExpress databases but the data is also available in XML format.
If it's practical for Integration Services to do this, what frequency is it possible to pull this data?
I realize that the amout of data is relative but just wondering if anyone is attempting to do this with integration services.
If not with integration services, then what method(s) are available and used to extract data from this many remote sites?

SSIS is a very good fit for this type of problem. Loading data from flat files/XML files/databases and inserting to a RDBMS is an every day task for SSIS and something it does very efficiently.

SSIS does not have any restriction on how often you pull the data. This decison depends on many many factors such as:

latency of data|||Thanks for the reply!

Do you know where I may find a case study or know of anyone else extracting data from this many sites? I really do expect some customers to grow to 5,000 sites. So before investing into this strategy I want to see if it's being done.

Thanks!
|||

I don't know of any case studies, no. Fundamentally, SSIS will be able to do this. Whether it will work in your situation is down to the factors that I mentioned, not the lack of functionality.

-Jamie

Integration Services Designer (Visual Studio) very very slow

Hi all

I have some performance issues when developing Integration Service Package ...

It is very very slow for example when I try to add new connection in
Connection Manages, it takes about 2 minutes just to open the property window.

On another pc in the same environment it works ok.
In the past I have doing a lot of SSIS Packages ... Is there any cache to empty ?

Thanks for any comments

Best regards
Frank Uray

Have you tried the 'work offline' option in SSIS menu. That option will prevent SSIS to validate connections in the package at design time. I am not sure if that is the source of your problem; bt you can try it.|||

Thank you for your answer,
but this does not help, it is still very slow ... :-(

Best regards
Frank Uray

|||Um, well... What are your machine specifications? CPU? RAM? etc...

What's the version number of SSIS?|||

Hi Phil

Here are the specifications:
- Dual Core AMD Opteron 2.20 GHz.
- 3.50 GB RAM
- 160 GB Disk
- HP xw9300 Workstation
- Windows XP SP2
- Visual Studio 8.0.50727.42 (RTM.050727-4200)

Hope you can find out something ...

Thanks and best regards
Frank Uray

|||

That's weird. I have half of that hardware and same VS version and run with no problem. Do you get the same behavior even when you create a new project/package? Check the task manager activity as well.

|||

Hi Rafael

Yes, I can create a new project and when I try to
add a new connection it takes 2 minutes to open
the first dialogbox ...
TaskManager is very quiet ...

Also when I run a package on my pc, it takes about
this 2 minutes to begin running the package.
When I run it on the server, the package finishes in 30 seconds.

Thanks and best regards
Frank Uray

|||This could also just be caused by network configurations when trying to do the connection discovery.|||

1.For integration service designer You should also install SQL 2005 SP1 and

the SQL2005 hotfix 2153.

2.another tip I found in the newsgroups was :

In internet explorer go to Tools-> options-> advanced

Uncheck the tickbox "check for publisher's certificate revocation"

(tnx to Nico Verheire)

3.When the designer is extremely slow in complex data flows, you have to break up the package in severaldataflows and use raw files to transfer data from one data flow to the other. See article <http://www.sqljunkies.com/WebLog/reckless/archive/2006/05/01/ssis_largedataflows.aspx>

I hope this can help.

Met vriendelijke groeten - Best Regards - Cordialement - Mit Freundlichen

Gruessen

Jan D'Hondt

Jade bvba

www.jadesoft.be

Integration Services Designer (Visual Studio) is very very slow

Hi all

I have some performance issues when developing Integration Service Package ...

It is very very slow for example when I try to add new connection in
Connection Manages, it takes about 2 minutes just to open the property window.

On another pc in the same environment (Domain etc.) it works ok.
In the past I have doing a lot of SSIS Packages ... Is there any cache to empty ?

Thanks for any comments

Best regards
Frank Uray

Do you have the SSIS service running? That can sometimes speed things up because it caches information about tasks and components.

Other than that - its more likely this can be attributed to hardware or network issues. I suppose it could be a million and one things.

-Jamie