Showing posts with label t-sql. Show all posts
Showing posts with label t-sql. Show all posts

Wednesday, March 21, 2012

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

Friday, March 9, 2012

Performance

I've come accross this post http://blogs.msdn.com/benjguin/archive/2005/05/10/416110.aspx where it compares the performance of a T-SQL cursor approach with a VB.NET CLR Stored procedure, and finds the latter faster.

Using the same example, I wrote a client application that performs the same operation using a DataReader, and compared it with invoking the SQLCLR stored procedure. Using the client-side cursor that scans 1 million rows is around 5 times _faster_ than the stored procedure, even when running the client in a different box.

What am I missing?

The code is below.


Sub Main()

Using cn As New SqlConnection("server=192.168.103.64\ctp;database=test;integrated security=yes")

cn.Open()

Dim stopWatch As New Stopwatch()

stopWatch.Start()

Dim cmd As New SqlCommand("select cast(a.ID * b.ID as decimal) from tableA a, tableA b", cn)

Dim total As SqlTypes.SqlDecimal

Using reader As SqlDataReader = cmd.ExecuteReader()

While reader.Read()

total = total + reader.GetDecimal(0)

End While

End Using

stopWatch.Stop()

Console.WriteLine(stopWatch.ElapsedMilliseconds)

stopWatch.Reset()

stopWatch.Start()

cmd = New SqlCommand("VBCursor", cn)

cmd.CommandType = CommandType.StoredProcedure

cmd.ExecuteNonQuery()

stopWatch.Stop()

Console.WriteLine(stopWatch.ElapsedMilliseconds)

End Using

End Sub


This is the typical scenario where using SQLCLR to do the job of T-SQL will result in poor performance and code bloat.

If you need to aggregate things, then either a built-in or a custom aggregate is the way to go, and it's very unlikely that you'll find a faster way of doing that.

As to why you're seeing this performance difference, here are some thoughts:

When you execute a top-level SQL statement that returns rows to the client, once the server started to produce rows those rows are packaged in TDS "packets" and sent to the client, a bunch of rows at a time, so processing is very efficient.

On the other hand, when you're using the context connection, we need to transition between native and managed for every row (at least in this version).

That means that while we do save the cost of the network hop in the context connection, the networking layer is very efficient in packaging and sending the data to the client; also, for context connections there is still per-row cost that we have to pay. For fast networks, the difference can almost dissapear for these extreme cases.

Our own testing indicates that in many cases of this kind performance should be more or less on par. Also, we continue to do tunning of the inproc code paths.

All that said, 5 times is clearly something I wouldn't expect. Did you try running the code inside the server but with a regular connection instead of a context connection?

Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.