Percentage of total: seconds

Showing posts with label seconds. Show all posts

Monday, March 26, 2012

Performance degrades severely after executing REORGANIZE

Hi,
We have a SQLServer 2005 database that is undergoing a serious problem. It
is causing some searches to take 10 minutes rather than seconds. Our users
are almost unable to use the application since this has been ongoing for 3
days, so any help you can provide is much appreciated!
We have a job that runs weekly. The job does these steps:
1. Reorganize indexes
2. Update statistics
3. Cleanup history
In the Event Viewer of the server, we can see that this job fails every 2
weeks. So it runs and successfully one week, then fails the next, then
succeeds, then fails. We do not know why it fails, there is no error message
in the event viewer other than "step failed".
However, when it runs SUCCESSFULLY, we see a sever performance degredation
the next morning. When it fails, we see no performance changes the next day.
In the past, when this has occurred, we have run update stats and the
problem goes away. That happened about 3 times. However, this week, running
update stats is not fixing the issue. We have rebooted the server also, with
no affect.
Has anyone ever seen anything like this before? Please advise!!
thanks
-RobbHi Robb
Do you do anything after this such as shrinking the database or do you have
autoshrink enabled?
John
"Robb Gilmore" wrote:
> Hi,
> We have a SQLServer 2005 database that is undergoing a serious problem. It
> is causing some searches to take 10 minutes rather than seconds. Our users
> are almost unable to use the application since this has been ongoing for 3
> days, so any help you can provide is much appreciated!
> We have a job that runs weekly. The job does these steps:
> 1. Reorganize indexes
> 2. Update statistics
> 3. Cleanup history
> In the Event Viewer of the server, we can see that this job fails every 2
> weeks. So it runs and successfully one week, then fails the next, then
> succeeds, then fails. We do not know why it fails, there is no error message
> in the event viewer other than "step failed".
> However, when it runs SUCCESSFULLY, we see a sever performance degredation
> the next morning. When it fails, we see no performance changes the next day.
> In the past, when this has occurred, we have run update stats and the
> problem goes away. That happened about 3 times. However, this week, running
> update stats is not fixing the issue. We have rebooted the server also, with
> no affect.
> Has anyone ever seen anything like this before? Please advise!!
> thanks
> -Robb|||Hi John,
No, nothing after that. And autoshrink is not enabled.
We did find out that the job failed due to lack of dick space for the
logs...that explains why it sometimes failed. But still, when it succeeds,
the performance goes right down the tubes. It has been bad now since Monday.
Queries taking 10 minutes.
Any advice on what else to look at or try?
thanks
Robb
"John Bell" wrote:
> Hi Robb
> Do you do anything after this such as shrinking the database or do you have
> autoshrink enabled?
> John
> "Robb Gilmore" wrote:
> > Hi,
> > We have a SQLServer 2005 database that is undergoing a serious problem. It
> > is causing some searches to take 10 minutes rather than seconds. Our users
> > are almost unable to use the application since this has been ongoing for 3
> > days, so any help you can provide is much appreciated!
> >
> > We have a job that runs weekly. The job does these steps:
> > 1. Reorganize indexes
> > 2. Update statistics
> > 3. Cleanup history
> >
> > In the Event Viewer of the server, we can see that this job fails every 2
> > weeks. So it runs and successfully one week, then fails the next, then
> > succeeds, then fails. We do not know why it fails, there is no error message
> > in the event viewer other than "step failed".
> >
> > However, when it runs SUCCESSFULLY, we see a sever performance degredation
> > the next morning. When it fails, we see no performance changes the next day.
> >
> > In the past, when this has occurred, we have run update stats and the
> > problem goes away. That happened about 3 times. However, this week, running
> > update stats is not fixing the issue. We have rebooted the server also, with
> > no affect.
> >
> > Has anyone ever seen anything like this before? Please advise!!
> >
> > thanks
> > -Robb|||Hi Robb,
From your description, I understand that:
After executing REORGANIZE to index, you found that the performance
degrades dramatically. You have used UPDATA STATISTICS, but it still
couldn't get back to normal. Queries took about 10 minutes.
If I have misunderstood, please let me know.
It is most likely caused by missing indexes and changes in statistics.
If you can locate which statistic caused the performance degrading, you can
try drop it and then recreate it by using:
CREATE STATISTICS ¡ WITH FULLSCAN, NORECOMPUTE
and a batch job that does
UPDATE STATISTICS ¡ WITH FULLSCAN, NORECOMPUTE.
Also, I recommend that you refer to this article for troubleshooting this
issue:
Troubleshooting Performance Problems in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx#EYBAG
Statistics Used by the Query Optimizer in Microsoft SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx#EFFAE
NOTE: Our managed newsgroup is focused on break/fix issues that are neither
urgent nor complex. If the issue is urgent to your business, it is
recommended that you contact Microsoft Customer Support Services (CSS) via
telephone so that a dedicated Support Professional can assist you in a more
efficient manner. Please be advised that contacting phone support will be a
charged call.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi Robb
That is very odd, if you replaced this with a script that used DBCC
DBREINDEX do you still get the same problem?
John
"Robb Gilmore" wrote:
> Hi John,
> No, nothing after that. And autoshrink is not enabled.
> We did find out that the job failed due to lack of dick space for the
> logs...that explains why it sometimes failed. But still, when it succeeds,
> the performance goes right down the tubes. It has been bad now since Monday.
> Queries taking 10 minutes.
> Any advice on what else to look at or try?
> thanks
> Robb
> "John Bell" wrote:
> > Hi Robb
> >
> > Do you do anything after this such as shrinking the database or do you have
> > autoshrink enabled?
> >
> > John
> >
> > "Robb Gilmore" wrote:
> >
> > > Hi,
> > > We have a SQLServer 2005 database that is undergoing a serious problem. It
> > > is causing some searches to take 10 minutes rather than seconds. Our users
> > > are almost unable to use the application since this has been ongoing for 3
> > > days, so any help you can provide is much appreciated!
> > >
> > > We have a job that runs weekly. The job does these steps:
> > > 1. Reorganize indexes
> > > 2. Update statistics
> > > 3. Cleanup history
> > >
> > > In the Event Viewer of the server, we can see that this job fails every 2
> > > weeks. So it runs and successfully one week, then fails the next, then
> > > succeeds, then fails. We do not know why it fails, there is no error message
> > > in the event viewer other than "step failed".
> > >
> > > However, when it runs SUCCESSFULLY, we see a sever performance degredation
> > > the next morning. When it fails, we see no performance changes the next day.
> > >
> > > In the past, when this has occurred, we have run update stats and the
> > > problem goes away. That happened about 3 times. However, this week, running
> > > update stats is not fixing the issue. We have rebooted the server also, with
> > > no affect.
> > >
> > > Has anyone ever seen anything like this before? Please advise!!
> > >
> > > thanks
> > > -Robb|||Hi Robb
This is very odd, if you replaces this task with a script that used DBCC
DBREINDEX do you get the same problem? You could also add in a script that
out the information from sys.dm_db_index_physical_stats() before and after
this task!
Have you checked for any other jobs that are run against the database?
John
"Robb Gilmore" wrote:
> Hi John,
> No, nothing after that. And autoshrink is not enabled.
> We did find out that the job failed due to lack of dick space for the
> logs...that explains why it sometimes failed. But still, when it succeeds,
> the performance goes right down the tubes. It has been bad now since Monday.
> Queries taking 10 minutes.
> Any advice on what else to look at or try?
> thanks
> Robb
> "John Bell" wrote:
> > Hi Robb
> >
> > Do you do anything after this such as shrinking the database or do you have
> > autoshrink enabled?
> >
> > John
> >
> > "Robb Gilmore" wrote:
> >
> > > Hi,
> > > We have a SQLServer 2005 database that is undergoing a serious problem. It
> > > is causing some searches to take 10 minutes rather than seconds. Our users
> > > are almost unable to use the application since this has been ongoing for 3
> > > days, so any help you can provide is much appreciated!
> > >
> > > We have a job that runs weekly. The job does these steps:
> > > 1. Reorganize indexes
> > > 2. Update statistics
> > > 3. Cleanup history
> > >
> > > In the Event Viewer of the server, we can see that this job fails every 2
> > > weeks. So it runs and successfully one week, then fails the next, then
> > > succeeds, then fails. We do not know why it fails, there is no error message
> > > in the event viewer other than "step failed".
> > >
> > > However, when it runs SUCCESSFULLY, we see a sever performance degredation
> > > the next morning. When it fails, we see no performance changes the next day.
> > >
> > > In the past, when this has occurred, we have run update stats and the
> > > problem goes away. That happened about 3 times. However, this week, running
> > > update stats is not fixing the issue. We have rebooted the server also, with
> > > no affect.
> > >
> > > Has anyone ever seen anything like this before? Please advise!!
> > >
> > > thanks
> > > -Robb|||Thank you Charles and John,
We will open a call with CSS.
-Robb
******************************************
"Charles Wang[MSFT]" wrote:
> Hi Robb,
> From your description, I understand that:
> After executing REORGANIZE to index, you found that the performance
> degrades dramatically. You have used UPDATA STATISTICS, but it still
> couldn't get back to normal. Queries took about 10 minutes.
> If I have misunderstood, please let me know.
> It is most likely caused by missing indexes and changes in statistics.
> If you can locate which statistic caused the performance degrading, you can
> try drop it and then recreate it by using:
> CREATE STATISTICS Â¡Â WITH FULLSCAN, NORECOMPUTE
> and a batch job that does
> UPDATE STATISTICS Â¡Â WITH FULLSCAN, NORECOMPUTE.
> Also, I recommend that you refer to this article for troubleshooting this
> issue:
> Troubleshooting Performance Problems in SQL Server 2005
> http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx#EYBAG
> Statistics Used by the Query Optimizer in Microsoft SQL Server 2005
> http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx#EFFAE
> NOTE: Our managed newsgroup is focused on break/fix issues that are neither
> urgent nor complex. If the issue is urgent to your business, it is
> recommended that you contact Microsoft Customer Support Services (CSS) via
> telephone so that a dedicated Support Professional can assist you in a more
> efficient manner. Please be advised that contacting phone support will be a
> charged call.
> To obtain the phone numbers for specific technology request please take a
> look at the web site listed below.
> http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
> If you are outside the US please see http://support.microsoft.com for
> regional support phone numbers.
> Charles Wang
> Microsoft Online Community Support
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================>
>|||John and Charles,
Our issue is resolved! Thank you both for your help.
It's hard to believe, but the only thing we needed to do was use the "FULL
SCAN" option on the update statistics. Charles mentioned it in his post, and
my DBA tried it and it worked.
So we definitely learned the difference between using the FULL SCAN option
and not using it!
Thank you both for your input
-Robb|||Hi,
Appreciate your update and response. I am glad to hear that the problem has
been fixed. If you have any other questions or concerns, please do not
hesitate to contact us. It is always our pleasure to be of assistance.
Have a nice day!
Charles Wang
Microsoft Online Community Support

Performance degrades severely after executing REORGANIZE

Wednesday, March 21, 2012

Performance clues

I have a web application that calls stored procedures that sometimes timeout
at 30 seconds. We cannot do it at will, so it makes troubleshooting difficult.
I happened to have profiler running during one of these timeout episodes. The
duration was in the 30 second range for the stored procedures, but the reads
were very low (I did not trace for CPU or Writes). Performance Monitor on the
CPU was within an acceptable range, as were the Buffer Cache Hit ratio (about
96 %) and the Disk Queue length.
When you have high duration and low reads, does that indicate an area to
pursue.
Using SQL Server 2000, SP4, on Win 2003.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1> When you have high duration and low reads, does that indicate an area to
> pursue.
Looks like a blocking situation to me...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:68fe0ef849e6f@.uwe...
>I have a web application that calls stored procedures that sometimes timeout
> at 30 seconds. We cannot do it at will, so it makes troubleshooting difficult.
> I happened to have profiler running during one of these timeout episodes. The
> duration was in the 30 second range for the stored procedures, but the reads
> were very low (I did not trace for CPU or Writes). Performance Monitor on the
> CPU was within an acceptable range, as were the Buffer Cache Hit ratio (about
> 96 %) and the Disk Queue length.
> When you have high duration and low reads, does that indicate an area to
> pursue.
> Using SQL Server 2000, SP4, on Win 2003.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1
>|||cbrichards via SQLMonster.com wrote:
> I have a web application that calls stored procedures that sometimes timeout
> at 30 seconds. We cannot do it at will, so it makes troubleshooting difficult.
> I happened to have profiler running during one of these timeout episodes. The
> duration was in the 30 second range for the stored procedures, but the reads
> were very low (I did not trace for CPU or Writes). Performance Monitor on the
> CPU was within an acceptable range, as were the Buffer Cache Hit ratio (about
> 96 %) and the Disk Queue length.
> When you have high duration and low reads, does that indicate an area to
> pursue.
> Using SQL Server 2000, SP4, on Win 2003.
>
Some other process is blocking the one in question...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Blocking. Is anyone using Enterprise Manager to work with the data?
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:68fe0ef849e6f@.uwe...
>I have a web application that calls stored procedures that sometimes
>timeout
> at 30 seconds. We cannot do it at will, so it makes troubleshooting
> difficult.
> I happened to have profiler running during one of these timeout episodes.
> The
> duration was in the 30 second range for the stored procedures, but the
> reads
> were very low (I did not trace for CPU or Writes). Performance Monitor on
> the
> CPU was within an acceptable range, as were the Buffer Cache Hit ratio
> (about
> 96 %) and the Disk Queue length.
> When you have high duration and low reads, does that indicate an area to
> pursue.
> Using SQL Server 2000, SP4, on Win 2003.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1
>|||As others have already said, blocking is one very common cause of this
behaviour but database file growth during proc execution is another (monitor
for this with the profiler's file growth events).
96% Buffer Cache Hit Ratio isn't actually very high - you could very easily
be experiencing memory problems with BCHR at 96%. It's worth having a look
at the BufferManager's Page Life Expectancy counter as well - it measures
how long buffered pages are surviving in cache before being forced out by
pressure from other memory consumers. If this number falls during long
execution of your proc, it could be that other factors are indirectly
influencing this problem
HTH
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:68fe0ef849e6f@.uwe...
>I have a web application that calls stored procedures that sometimes
>timeout
> at 30 seconds. We cannot do it at will, so it makes troubleshooting
> difficult.
> I happened to have profiler running during one of these timeout episodes.
> The
> duration was in the 30 second range for the stored procedures, but the
> reads
> were very low (I did not trace for CPU or Writes). Performance Monitor on
> the
> CPU was within an acceptable range, as were the Buffer Cache Hit ratio
> (about
> 96 %) and the Disk Queue length.
> When you have high duration and low reads, does that indicate an area to
> pursue.
> Using SQL Server 2000, SP4, on Win 2003.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1
>

Performance clues

I have a web application that calls stored procedures that sometimes timeout
at 30 seconds. We cannot do it at will, so it makes troubleshooting difficul
t.
I happened to have profiler running during one of these timeout episodes. Th
e
duration was in the 30 second range for the stored procedures, but the reads
were very low (I did not trace for CPU or Writes). Performance Monitor on th
e
CPU was within an acceptable range, as were the Buffer Cache Hit ratio (abou
t
96 %) and the Disk Queue length.
When you have high duration and low reads, does that indicate an area to
pursue.
Using SQL Server 2000, SP4, on Win 2003.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200611/1> When you have high duration and low reads, does that indicate an area to
> pursue.
Looks like a blocking situation to me...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:68fe0ef849e6f@.uwe...[vbcol
=seagreen]
>I have a web application that calls stored procedures that sometimes timeou
t
> at 30 seconds. We cannot do it at will, so it makes troubleshooting diffic
ult.
> I happened to have profiler running during one of these timeout episodes.
The
> duration was in the 30 second range for the stored procedures, but the rea
ds
> were very low (I did not trace for CPU or Writes). Performance Monitor on
the
> CPU was within an acceptable range, as were the Buffer Cache Hit ratio (ab
out
> 96 %) and the Disk Queue length.
> When you have high duration and low reads, does that indicate an area to
> pursue.
> Using SQL Server 2000, SP4, on Win 2003.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200611/1
>[/vbcol]|||cbrichards via droptable.com wrote:
> I have a web application that calls stored procedures that sometimes timeo
ut
> at 30 seconds. We cannot do it at will, so it makes troubleshooting diffic
ult.
> I happened to have profiler running during one of these timeout episodes.
The
> duration was in the 30 second range for the stored procedures, but the rea
ds
> were very low (I did not trace for CPU or Writes). Performance Monitor on
the
> CPU was within an acceptable range, as were the Buffer Cache Hit ratio (ab
out
> 96 %) and the Disk Queue length.
> When you have high duration and low reads, does that indicate an area to
> pursue.
> Using SQL Server 2000, SP4, on Win 2003.
>
Some other process is blocking the one in question...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Blocking. Is anyone using Enterprise Manager to work with the data?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:68fe0ef849e6f@.uwe...
>I have a web application that calls stored procedures that sometimes
>timeout
> at 30 seconds. We cannot do it at will, so it makes troubleshooting
> difficult.
> I happened to have profiler running during one of these timeout episodes.
> The
> duration was in the 30 second range for the stored procedures, but the
> reads
> were very low (I did not trace for CPU or Writes). Performance Monitor on
> the
> CPU was within an acceptable range, as were the Buffer Cache Hit ratio
> (about
> 96 %) and the Disk Queue length.
> When you have high duration and low reads, does that indicate an area to
> pursue.
> Using SQL Server 2000, SP4, on Win 2003.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200611/1
>|||As others have already said, blocking is one very common cause of this
behaviour but database file growth during proc execution is another (monitor
for this with the profiler's file growth events).
96% Buffer Cache Hit Ratio isn't actually very high - you could very easily
be experiencing memory problems with BCHR at 96%. It's worth having a look
at the BufferManager's Page Life Expectancy counter as well - it measures
how long buffered pages are surviving in cache before being forced out by
pressure from other memory consumers. If this number falls during long
execution of your proc, it could be that other factors are indirectly
influencing this problem
HTH
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:68fe0ef849e6f@.uwe...
>I have a web application that calls stored procedures that sometimes
>timeout
> at 30 seconds. We cannot do it at will, so it makes troubleshooting
> difficult.
> I happened to have profiler running during one of these timeout episodes.
> The
> duration was in the 30 second range for the stored procedures, but the
> reads
> were very low (I did not trace for CPU or Writes). Performance Monitor on
> the
> CPU was within an acceptable range, as were the Buffer Cache Hit ratio
> (about
> 96 %) and the Disk Queue length.
> When you have high duration and low reads, does that indicate an area to
> pursue.
> Using SQL Server 2000, SP4, on Win 2003.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200611/1
>

Performance Change In 1 Day, How Can I See, What??

Hi,
We have an application runs on SQL Server 2000 online (win 2000). Yesterday,
I saw that an T-SQL which runs in 3 seconds, is running now in 30 seconds
(After 1 day). Then I saw that windows update installed patches and restart
windows at night.
Now there is not any problems in sql server log, I tried theese:
DBCC CHECKDB
DBCC DBREINDEX
No problem seen in this checks. But performance is bad. How can realize what
is the problem?
My t-sql uses UDFs. I tested sql with UDFs. Than I replace UDF call with
T-SQL which is called in UDF. Results:
With UDF call: 4843 ms
With T-SQL call: 77 ms
I think this difference is abnormal.
Finally anything was changed my system performance. But what'
Thank you in advance.
______________________________
Senol AkbulakHello Senol,
Thank you for using Microsoft MSDN newsgroup!
I understand that after installing some Windows update, the performance of
your SQL Server becomes slow. You identified the issue is caused by UDFs.
If I'm off-base, please let me know.
To know the issue better, I'd like to know the following information:
1. What UDFs do you use? Does it only occur for a specific UDF? Is it
scalar function? Did you check the Execution plan of the query? Also,
please check the actual time of query by using the folowing option in QA.
SET STATISTICS PROFILE ON
SET STATISTICS TIME ON
SET STATISTICS IO ON
2. Does the issue still occur if you use UDF on a different table?
3. Check if the issue occurs after clean cache and update statistics
dbcc freeproccache
dbcc dropcleanbuffers
sp_updatestats
Please note this may have performance impact for some time if it is a
production server.
You may set following option in the session to see if it makes any
difference.
Set ansi_null_dflt_on ON
Set ansi_padding ON
Set ansi_warnings ON
Also, you may want to use profiler to trace the execution with Execution
plan class selected so that you could see the execution plan from the
trace.
More related information
HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
http://support.microsoft.com/?id=243589
More information can be found in the following BOL topics:
Query Tuning
http://msdn.microsoft.com/library/e..._tun_1_536v.asp
Query Tuning Recommendations
http://msdn.microsoft.com/library/e..._tun_1_33lf.asp
Performance issues can be difficult to troubleshoot and resolve in a
newsgroup setting due to the number of variables and the amount of time
required to narrow down possible causes and observe the effects. We will
assist as best as we can, but you may wish to consider contacting CSS for a
more timely resolution for these type issues.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/defaul...US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com
for regional support phone numbers.
Thank you for your patience and understanding.
Please let me know if you have any update and I look forward to your reply.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Senol,
Just want to check in if you have further questions on the issue. Please
feel free to post back if need any help.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hello Senol,
How things are going there? Please feel free to post back if you need
further assistance.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
========================================
=============
PLEASE NOTE: The partner managed newsgroups are provided to assist with
break/fix
issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader: microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============sql

Performance Change In 1 Day, How Can I See, What??

Hi,
We have an application runs on SQL Server 2000 online (win 2000). Yesterday,
I saw that an T-SQL which runs in 3 seconds, is running now in 30 seconds
(After 1 day). Then I saw that windows update installed patches and restart
windows at night.
Now there is not any problems in sql server log, I tried theese:
DBCC CHECKDB
DBCC DBREINDEX
No problem seen in this checks. But performance is bad. How can realize what
is the problem?
My t-sql uses UDFs. I tested sql with UDFs. Than I replace UDF call with
T-SQL which is called in UDF. Results:
With UDF call: 4843 ms
With T-SQL call: 77 ms
I think this difference is abnormal.
Finally anything was changed my system performance. But what'
Thank you in advance.
______________________________
Senol AkbulakHello Senol,
Thank you for using Microsoft MSDN newsgroup!
I understand that after installing some Windows update, the performance of
your SQL Server becomes slow. You identified the issue is caused by UDFs.
If I'm off-base, please let me know.
To know the issue better, I'd like to know the following information:
1. What UDFs do you use? Does it only occur for a specific UDF? Is it
scalar function? Did you check the Execution plan of the query? Also,
please check the actual time of query by using the folowing option in QA.
SET STATISTICS PROFILE ON
SET STATISTICS TIME ON
SET STATISTICS IO ON
2. Does the issue still occur if you use UDF on a different table?
3. Check if the issue occurs after clean cache and update statistics
dbcc freeproccache
dbcc dropcleanbuffers
sp_updatestats
Please note this may have performance impact for some time if it is a
production server.
You may set following option in the session to see if it makes any
difference.
Set ansi_null_dflt_on ON
Set ansi_padding ON
Set ansi_warnings ON
Also, you may want to use profiler to trace the execution with Execution
plan class selected so that you could see the execution plan from the
trace.
More related information
HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
http://support.microsoft.com/?id=243589
More information can be found in the following BOL topics:
Query Tuning
http://msdn.microsoft.com/library/en-us/optimsql/odp_tun_1_536v.asp
Query Tuning Recommendations
http://msdn.microsoft.com/library/en-us/optimsql/odp_tun_1_33lf.asp
Performance issues can be difficult to troubleshoot and resolve in a
newsgroup setting due to the number of variables and the amount of time
required to narrow down possible causes and observe the effects. We will
assist as best as we can, but you may wish to consider contacting CSS for a
more timely resolution for these type issues.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com
for regional support phone numbers.
Thank you for your patience and understanding.
Please let me know if you have any update and I look forward to your reply.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Senol,
Just want to check in if you have further questions on the issue. Please
feel free to post back if need any help.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hello Senol,
How things are going there? Please feel free to post back if you need
further assistance.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
PLEASE NOTE: The partner managed newsgroups are provided to assist with
break/fix
issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader: microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

Monday, March 12, 2012

Performance and Large Data

Hi *.*
I've a table with 14 columns and 640.000 rows, a simple select (* from
table ) is very slow (about 16 seconds on my P4 2800, 750 MB of Ram),
Is it normal? How can I reduce this time? With some reports that use
this table, I've use Index Tuning Wizard and performance is now a
little better but I don't know if this is a limitation of SQL Server or
limitation of my knowledge? (SQL Server 2000-SP4 on Windows XP Pro)
Would you plz kindly help me with your tips and advices?
Thanks in Advance,
HamedHamed,
pls can you post up your query, the table schema, tell us what indexes you
have at present and the showplan output from running the query.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Are you saying that you return 640,000 rows? If so:
Why? Who can make heads or tails out of 640,000 rows? Without doing somethin
g reasonable, like
filtering, aggregating to cut down number of rows to be returned, there's no
t much to be done. One
thing that can be interesting is the Query Analyzer is much quicker to print
the result in grid
compared to text mode.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hamed" <hoveisi@.gmail.com> wrote in message
news:1155289810.440647.110830@.i3g2000cwc.googlegroups.com...
> Hi *.*
> I've a table with 14 columns and 640.000 rows, a simple select (* from
> table ) is very slow (about 16 seconds on my P4 2800, 750 MB of Ram),
> Is it normal? How can I reduce this time? With some reports that use
> this table, I've use Index Tuning Wizard and performance is now a
> little better but I don't know if this is a limitation of SQL Server or
> limitation of my knowledge? (SQL Server 2000-SP4 on Windows XP Pro)
> Would you plz kindly help me with your tips and advices?
> Thanks in Advance,
> Hamed
>|||On 11 Aug 2006 02:50:10 -0700, "Hamed" <hoveisi@.gmail.com> wrote:
> I've a table with 14 columns and 640.000 rows, a simple select (* from
>table ) is very slow (about 16 seconds on my P4 2800, 750 MB of Ram),
>Is it normal? How can I reduce this time? With some reports that use
>this table, I've use Index Tuning Wizard and performance is now a
>little better but I don't know if this is a limitation of SQL Server or
>limitation of my knowledge? (SQL Server 2000-SP4 on Windows XP Pro)
If it has to read and compare the rows to some kind of where clause,
or if you have no where clause and it has to return all 640k rows,
which is probably at least 60mb, it just takes that long to move the
data around.
If your logic is such that the SQLServer optimizer thinks it needs to
scan the table and not use any available indexes, then it is simply
going to take a while. OTOH, if you have 750mb of RAM, it may be that
the first time you run the select, it runs slowly because it is doing
physical disk I/O, but if you rerun the same query immediately it will
run much, much faster because ALL of the data is cached in RAM.
Hope that helps.
J.

Performance and Large Data

Hi *.*
I've a table with 14 columns and 640.000 rows, a simple select (* from
table ) is very slow (about 16 seconds on my P4 2800, 750 MB of Ram),
Is it normal? How can I reduce this time? With some reports that use
this table, I've use Index Tuning Wizard and performance is now a
little better but I don't know if this is a limitation of SQL Server or
limitation of my knowledge? (SQL Server 2000-SP4 on WIndows XP Pro)
Would you plz kindly help me with your tips and advices?
Thanks in Advance,
HamedHamed,
pls can you post up your query, the table schema, tell us what indexes you
have at present and the showplan output from running the query.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Are you saying that you return 640,000 rows? If so:
Why? Who can make heads or tails out of 640,000 rows? Without doing something reasonable, like
filtering, aggregating to cut down number of rows to be returned, there's not much to be done. One
thing that can be interesting is the Query Analyzer is much quicker to print the result in grid
compared to text mode.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hamed" <hoveisi@.gmail.com> wrote in message
news:1155289810.440647.110830@.i3g2000cwc.googlegroups.com...
> Hi *.*
> I've a table with 14 columns and 640.000 rows, a simple select (* from
> table ) is very slow (about 16 seconds on my P4 2800, 750 MB of Ram),
> Is it normal? How can I reduce this time? With some reports that use
> this table, I've use Index Tuning Wizard and performance is now a
> little better but I don't know if this is a limitation of SQL Server or
> limitation of my knowledge? (SQL Server 2000-SP4 on WIndows XP Pro)
> Would you plz kindly help me with your tips and advices?
> Thanks in Advance,
> Hamed
>|||On 11 Aug 2006 02:50:10 -0700, "Hamed" <hoveisi@.gmail.com> wrote:
> I've a table with 14 columns and 640.000 rows, a simple select (* from
>table ) is very slow (about 16 seconds on my P4 2800, 750 MB of Ram),
>Is it normal? How can I reduce this time? With some reports that use
>this table, I've use Index Tuning Wizard and performance is now a
>little better but I don't know if this is a limitation of SQL Server or
>limitation of my knowledge? (SQL Server 2000-SP4 on WIndows XP Pro)
If it has to read and compare the rows to some kind of where clause,
or if you have no where clause and it has to return all 640k rows,
which is probably at least 60mb, it just takes that long to move the
data around.
If your logic is such that the SQLServer optimizer thinks it needs to
scan the table and not use any available indexes, then it is simply
going to take a while. OTOH, if you have 750mb of RAM, it may be that
the first time you run the select, it runs slowly because it is doing
physical disk I/O, but if you rerun the same query immediately it will
run much, much faster because ALL of the data is cached in RAM.
Hope that helps.
J.

Friday, March 9, 2012

Performance - Slow load times for SSIS Packages

Is there any information around what the SSIS packages are doing in the first 5-10 seconds of execution, and ways to speed this process up?Validation then Pre-Execute are the first things to happen as I recall. You can see all the events being raised prior to the Execute event itself in the BIDS Progress or Output windows. You can also capture lots if information including this through logging. DTEXEC can also dump all this information to the console, so easily captured when not running interactively, just not through BIDS, thereby cutting out the debugging overhead.|||It would be nice to know how to get rid of the delay when loading packages, so that tasks would fire instantaneously. Every package, no matter how simple, seems to have a 5-10 second delay.|||

Make sure the SSIS service is running - it caches information about packages that have run, making subsequent package invocations go faster.

Cheers/Kristian

Performance -- Plan Cost and Scan

I am looking some query stats and have a query.
Query 1 Plan Cost: 5.312 -- Execution 20.497 seconds -- total time 23.848
seconds --Physical Reads 1,404, Logical Reads, 927,701,Scans 621, Read Ahead
Reads - 3,976
Query 2 Plan Cost 9.469 -- Exection 00.143 seconds -- total time 02.016
seconds -- physical reads 0, logical reads 7146, sans 622 , read ahead reads
100
Query 2 obviously runs around 95% faster and it would seem difference
between plan cost is very little.
What is the downside of having the plan cost go up? Under heavier load
would this start to perform poorly because of that? At what point does the
plan cost become more important than the other items? Or are the logical
reads a better indiciator of which way to go.
Sorry for so many questions -- really starting to use the tools to tweak
queries and want to make sure I am going down right paths.
Thanks!!!Execution plan costs are worth looking at, but must be taken with a
bit of skepticism. The cost in the execution plan is just an
estimate. The actual results can be quite different. You can even
get completely different execution plans sometimes just by updating
statistics.
Roy Harvey
Beacon Falls, CT
On Tue, 28 Feb 2006 10:46:07 -0500, "Brian" <brian@.nospam.com> wrote:

>I am looking some query stats and have a query.
>Query 1 Plan Cost: 5.312 -- Execution 20.497 seconds -- total time 23.848
>seconds --Physical Reads 1,404, Logical Reads, 927,701,Scans 621, Read Ahea
d
>Reads - 3,976
>Query 2 Plan Cost 9.469 -- Exection 00.143 seconds -- total time 02.016
>seconds -- physical reads 0, logical reads 7146, sans 622 , read ahead read
s
>100
>
>Query 2 obviously runs around 95% faster and it would seem difference
>between plan cost is very little.
>What is the downside of having the plan cost go up? Under heavier load
>would this start to perform poorly because of that? At what point does the
>plan cost become more important than the other items? Or are the logical
>reads a better indiciator of which way to go.
>Sorry for so many questions -- really starting to use the tools to tweak
>queries and want to make sure I am going down right paths.
>Thanks!!!
>

performance

Hi all,
I have strange problem.
I have query (where I have to use cursors, functions, subqueries, variables
of type table) and it runs aprox 7 seconds on new built and filled database.
New built and filled database means :
- create tables
- load data
- create constrains, indexes, views, procedures, functions
After restart of SQL server query runs longer (aprox 11 seconds) and it
stays on 11 seconds all the time.
Also it is very strange that on server with more memory, SCSI disks it runs
slower than on my local workstation.
Any ideas ?
Regards,
jan
Hello Jan,
It is possible that your pages have been flushed from the buffer pool on the
server, if there are lots of other queries going on. A way to test the actual
performance is to flush the cache on both machines before running the query.
CAUTION: This will cause a performance degradation to users on both machines
as SQL Server will need to get all flushed pages from disk.
Use:
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
Then run your query.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Jan Hruz" wrote:

> Hi all,
> I have strange problem.
> I have query (where I have to use cursors, functions, subqueries, variables
> of type table) and it runs aprox 7 seconds on new built and filled database.
> New built and filled database means :
> - create tables
> - load data
> - create constrains, indexes, views, procedures, functions
> After restart of SQL server query runs longer (aprox 11 seconds) and it
> stays on 11 seconds all the time.
> Also it is very strange that on server with more memory, SCSI disks it runs
> slower than on my local workstation.
> Any ideas ?
> Regards,
> jan
>
>

Wednesday, March 7, 2012

performance

Hi all,
I have strange problem.
I have query (where I have to use cursors, functions, subqueries, variables
of type table) and it runs aprox 7 seconds on new built and filled database.
New built and filled database means :
- create tables
- load data
- create constrains, indexes, views, procedures, functions
After restart of SQL server query runs longer (aprox 11 seconds) and it
stays on 11 seconds all the time.
Also it is very strange that on server with more memory, SCSI disks it runs
slower than on my local workstation.
Any ideas ?
Regards,
janHello Jan,
It is possible that your pages have been flushed from the buffer pool on the
server, if there are lots of other queries going on. A way to test the actua
l
performance is to flush the cache on both machines before running the query.
CAUTION: This will cause a performance degradation to users on both machines
as SQL Server will need to get all flushed pages from disk.
Use:
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
Then run your query.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Jan Hruz" wrote:

> Hi all,
> I have strange problem.
> I have query (where I have to use cursors, functions, subqueries, variable
s
> of type table) and it runs aprox 7 seconds on new built and filled databas
e.
> New built and filled database means :
> - create tables
> - load data
> - create constrains, indexes, views, procedures, functions
> After restart of SQL server query runs longer (aprox 11 seconds) and it
> stays on 11 seconds all the time.
> Also it is very strange that on server with more memory, SCSI disks it run
s
> slower than on my local workstation.
> Any ideas ?
> Regards,
> jan
>
>

Monday, March 26, 2012

Performance degrades severely after executing REORGANIZE

Performance degrades severely after executing REORGANIZE

Performance degrades severely after executing REORGANIZE

Wednesday, March 21, 2012

Performance clues

Performance clues

Performance clues

Performance Change In 1 Day, How Can I See, What??

Performance Change In 1 Day, How Can I See, What??

Monday, March 12, 2012

Performance and Large Data

Performance and Large Data

Friday, March 9, 2012

Performance - Slow load times for SSIS Packages

Performance -- Plan Cost and Scan

performance

Wednesday, March 7, 2012

performance

Percentage of total

Blog Archive

About Me