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.
======================================================

performance and #of users

If we have an SQL server with 20 concurrent connections, how does it fail as it runs out of RAM and starts swapping?

Does it slow down enough on its ability to service connections that you would appear to need to add more concurrent connections? If you add these connections, will the server as a whole start to slow down as the system starts using more and more dasd to emulate the RAM it needs?

:confused:Originally posted by ricka49
If we have an SQL server with 20 concurrent connections, how does it fail as it runs out of RAM and starts swapping?

Does it slow down enough on its ability to service connections that you would appear to need to add more concurrent connections? If you add these connections, will the server as a whole start to slow down as the system starts using more and more dasd to emulate the RAM it needs?

:confused:

SQL Server 7.0 and 2000 use approximately 50 Kb of server RAM per connection. Note that a user can have multiple connections, even with connection pooling. A good rule of thumb here is one connection per 100 records returned, so 50Kb of server memory per 100 records.

I cannot site an article on this. I got this information from a MS developer when I was looking at licensing options a couple of years back.|||You can check the number of users logged in from Enterprise Manager. You can also run sp_who and/or sp_who2. There is another place to check on user connections (and also connection memory)::

Look at the sysperfinfo table on master:

SELECT * FROM master..sysperfinfo
WHERE
(object_name = 'SQLServer:General Statistics' and counter_name = 'User Connections') OR
(object_name = 'SQLServer:Memory Manager' and counter_name = 'Connection Memory (KB)')

I make it a general habit to snapshot this data to a history table on an hourly basis. It can help me sometimes track usage and performance issues.

Regards,

Hugh Scott

Originally posted by ricka49
If we have an SQL server with 20 concurrent connections, how does it fail as it runs out of RAM and starts swapping?

Does it slow down enough on its ability to service connections that you would appear to need to add more concurrent connections? If you add these connections, will the server as a whole start to slow down as the system starts using more and more dasd to emulate the RAM it needs?

:confused:

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
>
>

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
>
>

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 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
>
>