Showing posts with label bug. Show all posts
Showing posts with label bug. Show all posts

Monday, March 12, 2012

I think this is a bug

We have a test system on which we installed Sql Server 2005, then Sql Server
2005 Express.
We then deleted SqlServer 2005 leaving just express.
The problem is when we call
DbProviderFactory.CreateDataSourceEnumerator().Get DataSources(), the row
returned has the server name in column [0] and nothing in the other 3 columns.
So our code then assumes that to connect the server is CONUNDRUM (server
name) instead of CONUNDRUM\SQLEXPRESS and that fails. How can we get the
correct name?
ps - I know we can re-install SqlServer 2005 or uninstall and reinstall
Express to fix this. But I want to make sure our code works properly when
someone does this.
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm
Hi David,
From your description, I understand that:
You found that the function
DbProviderFactory.CreateDataSourceEnumerator().Get DataSources() failed to
get all matched values after you uninstalled SQL Server 2005 from your
machine. You found that your application could only get the server name in
the first column but nothing in the other three columns.
If I have misunderstood, please let me know.
I just wrote a simple test application with the following code:
DbProviderFactory factory = SqlClientFactory.Instance;
DataTable sources =
factory.CreateDataSourceEnumerator().GetDataSource s();
this.textBox1.Text = "SQL Instances:" + sources.Rows.Count +
"\r\n";
textBox1.Text += "Attributes:" + sources.Columns.Count + "\r\n";
for (int r = 0; r < sources.Rows.Count; ++r)
{
textBox1.Text += "Instance " + r + ":\r\n";
for (int i = 0; i < sources.Columns.Count; ++i)
{
textBox1.Text += "[" + r + "," + i + "]: " +
sources.Rows[r][i] + "\r\n";
}
textBox1.Text += "====================\r\n";
}
I first ran it under my computer with SQL Server 2005 Ent and Express
Editions, then ran it under a computer with only SQL Server 2005 Express
edition, but it worked fine on the two conditions.
To reproduce your issue, I moved it to a test machine with both SQL Server
2005 Ent and Express Editions, then I manually uninstalled the SQL Server
2005 Ent Edition, and ran it again, however it still worked fine.
I also used Reflector to check the source code, the internal logical should
be no problem. However I found that there was a timeout setting by a
private variable timeoutTime. Per my test, I found sometimes at the first
time I could not retrieve all the SQL Server instances information, but if
I tried a second time, I could get the information. I think that the
half-baked information at my side is related to the timeout setting. Not
sure if you encounter the similar issue and you may try again if you could
not get the correct information at the first time.
I recommend that you create a simple C# project and copy my code into it
and see if this issue still exists.
If the issue only exists in your application, you my extract your code into
a simple .net project and mail it to me (changliw@.microsoft.com) so that I
can reproduce your issue for further research.
Thanks for using Microsoft Online Managed Newsgroup. If you have any other
questions or concerns, please feel free to let me know.
Sincerely yours,
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.
================================================== ====
|||Here is the result of your test program. But the only server on conundrum is
conundrum/sqlexpress.
SQL Instances:7
Attributes:4
Instance 0:
[0,0]: BELLE
[0,1]:
[0,2]: No
[0,3]: 8.00.194
====================
Instance 1:
[1,0]: BELLE
[1,1]: SQLEXPRESS
[1,2]: No
[1,3]: 9.00.1399.06
====================
Instance 2:
[2,0]: CONUNDRUM
[2,1]:
[2,2]:
[2,3]:
====================
Instance 3:
[3,0]: JASMINE
[3,1]:
[3,2]:
[3,3]:
====================
Instance 4:
[4,0]: LUMIERE
[4,1]:
[4,2]:
[4,3]:
====================
Instance 5:
[5,0]: MINNIE
[5,1]:
[5,2]:
[5,3]:
====================
Instance 6:
[6,0]: PICARD
[6,1]:
[6,2]:
[6,3]:
====================
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm
"Charles Wang[MSFT]" wrote:

> Hi David,
> From your description, I understand that:
> You found that the function
> DbProviderFactory.CreateDataSourceEnumerator().Get DataSources() failed to
> get all matched values after you uninstalled SQL Server 2005 from your
> machine. You found that your application could only get the server name in
> the first column but nothing in the other three columns.
> If I have misunderstood, please let me know.
> I just wrote a simple test application with the following code:
> DbProviderFactory factory = SqlClientFactory.Instance;
> DataTable sources =
> factory.CreateDataSourceEnumerator().GetDataSource s();
> this.textBox1.Text = "SQL Instances:" + sources.Rows.Count +
> "\r\n";
> textBox1.Text += "Attributes:" + sources.Columns.Count + "\r\n";
> for (int r = 0; r < sources.Rows.Count; ++r)
> {
> textBox1.Text += "Instance " + r + ":\r\n";
> for (int i = 0; i < sources.Columns.Count; ++i)
> {
> textBox1.Text += "[" + r + "," + i + "]: " +
> sources.Rows[r][i] + "\r\n";
> }
> textBox1.Text += "====================\r\n";
> }
> I first ran it under my computer with SQL Server 2005 Ent and Express
> Editions, then ran it under a computer with only SQL Server 2005 Express
> edition, but it worked fine on the two conditions.
> To reproduce your issue, I moved it to a test machine with both SQL Server
> 2005 Ent and Express Editions, then I manually uninstalled the SQL Server
> 2005 Ent Edition, and ran it again, however it still worked fine.
> I also used Reflector to check the source code, the internal logical should
> be no problem. However I found that there was a timeout setting by a
> private variable timeoutTime. Per my test, I found sometimes at the first
> time I could not retrieve all the SQL Server instances information, but if
> I tried a second time, I could get the information. I think that the
> half-baked information at my side is related to the timeout setting. Not
> sure if you encounter the similar issue and you may try again if you could
> not get the correct information at the first time.
> I recommend that you create a simple C# project and copy my code into it
> and see if this issue still exists.
> If the issue only exists in your application, you my extract your code into
> a simple .net project and mail it to me (changliw@.microsoft.com) so that I
> can reproduce your issue for further research.
> Thanks for using Microsoft Online Managed Newsgroup. If you have any other
> questions or concerns, please feel free to let me know.
> Sincerely yours,
> 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 David,
This is indeed a strange behavior, but unfortunately I could not reproduce
your issue. In my local network area, there are about 60 SQL Server
instances, when I ran my application to detect the instances, I could got
all the information of the 60 instances. I also ran the test program on
other 3 machines and still could not reproduce your issue.
After discussing with our tech lead, I recommend 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.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
|||

Quote:

Originally Posted by Charles Wang[MSFT]View Post

Hi David,
This is indeed a strange behavior, but unfortunately I could not reproduce
your issue. In my local network area, there are about 60 SQL Server
instances, when I ran my application to detect the instances, I could got
all the information of the 60 instances. I also ran the test program on
other 3 machines and still could not reproduce your issue.
After discussing with our tech lead, I recommend 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.

I am experiencing exactly the same behavior.
This code returns the Server and Instance names correctly on my local box (I have three instances installed locally, all with instance names).
However, it returns blank fields for every server on the network (there are a great many servers on the network). I know for a fact that many of these have instance names, but the instance name comes back as null in this code.
So this is definitely something that is not isolated to just the first poster who reported this behavior.
Are there ANY other ideas here? Any suggestions of what might be going on? It sure looks like a bug from my end, but could it be anything in the internal firewalls or any settings in SQL Server? Or the fact that I'm going over VPN? Could there be any VPN or network issues?
Our environment is a mix of SQL Server 2000 and SQL Server 2005. Again, everything is fine on my local box, but the remote servers report no instance names, or versions, or anything else, ever. Just the server name comes back, exactly as the grandparent indicated.|||

Quote:

Originally Posted by Charles Wang[MSFT]View Post

Hi David,
This is indeed a strange behavior, but unfortunately I could not reproduce
your issue. In my local network area, there are about 60 SQL Server
instances, when I ran my application to detect the instances, I could got
all the information of the 60 instances. I also ran the test program on
other 3 machines and still could not reproduce your issue.
After discussing with our tech lead, I recommend 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...S;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
Sincerely yours,
Charles Wang
Microsoft Online Community Support

Is there any more information on this?
I've verified that it's definitely related to networking or VPN.
When I run the code from my VPN'd machine, I don't get back
anything but the server name (no instance name, no version,
just as originally reported by David). But if I take the same
EXE and copy it to a machine inside the actual network and
run it, it sees everything fine.
Can you verify if there are some additional ports that need
opening, or some additional settings that need to be made?
Surely someone ELSE can reproduce this problem, since two
of us experience it so reproducably...
Anyone? Bueller?

I think this is a bug

We have a test system on which we installed Sql Server 2005, then Sql Server
2005 Express.
We then deleted SqlServer 2005 leaving just express.
The problem is when we call
DbProviderFactory.CreateDataSourceEnumerator().GetDataSources(), the row
returned has the server name in column [0] and nothing in the other 3 co
lumns.
So our code then assumes that to connect the server is CONUNDRUM (server
name) instead of CONUNDRUM\SQLEXPRESS and that fails. How can we get the
correct name?
ps - I know we can re-install SqlServer 2005 or uninstall and reinstall
Express to fix this. But I want to make sure our code works properly when
someone does this.
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htmHi David,
From your description, I understand that:
You found that the function
DbProviderFactory.CreateDataSourceEnumerator().GetDataSources() failed to
get all matched values after you uninstalled SQL Server 2005 from your
machine. You found that your application could only get the server name in
the first column but nothing in the other three columns.
If I have misunderstood, please let me know.
I just wrote a simple test application with the following code:
DbProviderFactory factory = SqlClientFactory.Instance;
DataTable sources =
factory.CreateDataSourceEnumerator().GetDataSources();
this.textBox1.Text = "SQL Instances:" + sources.Rows.Count +
"\r\n";
textBox1.Text += "Attributes:" + sources.Columns.Count + "\r\n";
for (int r = 0; r < sources.Rows.Count; ++r)
{
textBox1.Text += "Instance " + r + ":\r\n";
for (int i = 0; i < sources.Columns.Count; ++i)
{
textBox1.Text += "[" + r + "," + i + "]: " +
sources.Rows[r][i] + "\r\n";
}
textBox1.Text += "====================\r\n";
}
I first ran it under my computer with SQL Server 2005 Ent and Express
Editions, then ran it under a computer with only SQL Server 2005 Express
edition, but it worked fine on the two conditions.
To reproduce your issue, I moved it to a test machine with both SQL Server
2005 Ent and Express Editions, then I manually uninstalled the SQL Server
2005 Ent Edition, and ran it again, however it still worked fine.
I also used Reflector to check the source code, the internal logical should
be no problem. However I found that there was a timeout setting by a
private variable timeoutTime. Per my test, I found sometimes at the first
time I could not retrieve all the SQL Server instances information, but if
I tried a second time, I could get the information. I think that the
half-baked information at my side is related to the timeout setting. Not
sure if you encounter the similar issue and you may try again if you could
not get the correct information at the first time.
I recommend that you create a simple C# project and copy my code into it
and see if this issue still exists.
If the issue only exists in your application, you my extract your code into
a simple .net project and mail it to me (changliw@.microsoft.com) so that I
can reproduce your issue for further research.
Thanks for using Microsoft Online Managed Newsgroup. If you have any other
questions or concerns, please feel free to let me know.
Sincerely yours,
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.
========================================
==============|||Here is the result of your test program. But the only server on conundrum is
conundrum/sqlexpress.
SQL Instances:7
Attributes:4
Instance 0:
[0,0]: BELLE
[0,1]:
[0,2]: No
[0,3]: 8.00.194
====================
Instance 1:
[1,0]: BELLE
[1,1]: SQLEXPRESS
[1,2]: No
[1,3]: 9.00.1399.06
====================
Instance 2:
[2,0]: CONUNDRUM
[2,1]:
[2,2]:
[2,3]:
====================
Instance 3:
[3,0]: JASMINE
[3,1]:
[3,2]:
[3,3]:
====================
Instance 4:
[4,0]: LUMIERE
[4,1]:
[4,2]:
[4,3]:
====================
Instance 5:
[5,0]: MINNIE
[5,1]:
[5,2]:
[5,3]:
====================
Instance 6:
[6,0]: PICARD
[6,1]:
[6,2]:
[6,3]:
====================
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm
"Charles Wang[MSFT]" wrote:

> Hi David,
> From your description, I understand that:
> You found that the function
> DbProviderFactory.CreateDataSourceEnumerator().GetDataSources() failed to
> get all matched values after you uninstalled SQL Server 2005 from your
> machine. You found that your application could only get the server name in
> the first column but nothing in the other three columns.
> If I have misunderstood, please let me know.
> I just wrote a simple test application with the following code:
> DbProviderFactory factory = SqlClientFactory.Instance;
> DataTable sources =
> factory.CreateDataSourceEnumerator().GetDataSources();
> this.textBox1.Text = "SQL Instances:" + sources.Rows.Count +
> "\r\n";
> textBox1.Text += "Attributes:" + sources.Columns.Count + "\r\n
";
> for (int r = 0; r < sources.Rows.Count; ++r)
> {
> textBox1.Text += "Instance " + r + ":\r\n";
> for (int i = 0; i < sources.Columns.Count; ++i)
> {
> textBox1.Text += "[" + r + "," + i + "]: " +
> sources.Rows[r][i] + "\r\n";
> }
> textBox1.Text += "====================\r\n";
> }
> I first ran it under my computer with SQL Server 2005 Ent and Express
> Editions, then ran it under a computer with only SQL Server 2005 Express
> edition, but it worked fine on the two conditions.
> To reproduce your issue, I moved it to a test machine with both SQL Server
> 2005 Ent and Express Editions, then I manually uninstalled the SQL Server
> 2005 Ent Edition, and ran it again, however it still worked fine.
> I also used Reflector to check the source code, the internal logical shoul
d
> be no problem. However I found that there was a timeout setting by a
> private variable timeoutTime. Per my test, I found sometimes at the first
> time I could not retrieve all the SQL Server instances information, but if
> I tried a second time, I could get the information. I think that the
> half-baked information at my side is related to the timeout setting. Not
> sure if you encounter the similar issue and you may try again if you could
> not get the correct information at the first time.
> I recommend that you create a simple C# project and copy my code into it
> and see if this issue still exists.
> If the issue only exists in your application, you my extract your code int
o
> a simple .net project and mail it to me (changliw@.microsoft.com) so that I
> can reproduce your issue for further research.
> Thanks for using Microsoft Online Managed Newsgroup. If you have any other
> questions or concerns, please feel free to let me know.
> Sincerely yours,
> 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 David,
This is indeed a strange behavior, but unfortunately I could not reproduce
your issue. In my local network area, there are about 60 SQL Server
instances, when I ran my application to detect the instances, I could got
all the information of the 60 instances. I also ran the test program on
other 3 machines and still could not reproduce your issue.
After discussing with our tech lead, I recommend 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/defaul...US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
Sincerely yours,
Charles Wang
Microsoft Online Community Support

I think I found SQL 2005 bug by doing simple Group BY!

I did:

select city,count(*)from classifieds_addressgroupby city

You would think it would distiguish between"Koh Phangan" and "Koh Phangan ". No!!! It shows them as one row in SQL!!! Is this SQL bug?

Aberdeen 1
bayombong 1
Brooklyn 4
Castle Peak Bay 1
Causeway Bay 2
Chiang Mai 1
Hong Kong 1
Hong Kong Wan Chai 1
Hong Kong Island 10
Hong Kong Sheung Wan 1
Karachi 1
Koh Phangan 21
Koh Samui 37
Koh Tao 9
Kowloon 13
Kowloon, Tsim Sha Tsui 1
Lantau Island 1
Mid-Levels 3
New Territories 2
New York 3
North Point 1
Oligar 1
Tsim Sha Tsui 1
Wan Chai 5
Yau Ma Tei 1
Yau Ma Tei, Kowloon 1

Actually I think it is worse than that! I run several queries and they all return 21 rows!!

select*from classifieds_addresswhere city='Koh Phangan'

select*from classifieds_addresswhere city='Koh Phangan '

select*from classifieds_addresswhere city='Koh Phangan '

select*from classifieds_addresswhere city='Koh Phangan '

Is not that a bug?? I am using SQL 2005 and the latest service pack!

|||

Seems MS is well aware of this problem:http://support.microsoft.com/kb/316626

I wonder why they do not fix this.

How do I select UNIQUE values (if there are spaces it is different than the one without spaces) and count of those records??

Of now is to late to do SET ANSI_PADDING OFF, since the values are already inserted with trailing spaces top the table...How do I distinguish those records??

|||

So for now you can do:

GROUP BY LTRIM(RTRIM(city))

|||

Jack,

That is not what I look for, I just want the oposite what you are proposing. What you are proposing is already done in group by inside of SQL Server...

I want to find the records where there are different. I want to find UNIQUE records in the table.I want to find those records with trailing spaces and mispells... I thought that Group By would do it, but it treats records with trailing spaces the same as the ones without...

GROUP BY LTRIM(RTRIM(city)) will just do the same as group by Rtrim(city)!

|||

Sorry for misunderstood your problem. Try the code below:

SELECT REPLACE(REPLACE(city,' ','_'),'_',' ')AS city,COUNT(*)FROM dbo.classifieds_addressGROUP BY REPLACE(city,' ','_')

I smell a big, fat compiler bug

Last two or three years now I've been spending a lot of time doing
SQLServer performance tuning. The last couple of weeks I've been
doing this at a place using a whole lot of inline table-valued UDFs,
an issue both in itself and because the database does a LOT of
joining, and in this place, I've been finding one technique is working
for me just a little too often.
Time and again, there is some complex query that looks like it should
be OK, but is running 100k's of logical reads and taking ten, twenty,
thirty seconds to return a handfull of records. No doubt someone who
can really, really read execution plans might be able to glance at the
plan and see what's wrong, but I haven't reached quite that point yet.
Time and again, what I find is that if I break out a chunk of code and
run it stand-alone in QA, suddenly it runs "correctly", that is, with
99% fewer reads and 80-99% faster execution. OK, we've all seen that,
we understand how much more the optimizer knows with explicit
constants and such, and how SPs have to live with "parameter sniffing"
and such. But I've gone the next step, wrapped ten or twenty or fifty
lines in a relatively small SP or UDF, and darned if it doesn't still
run fast, with all the same parameters and variables that it had in
the original big SP.
So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
large SP, it is not properly resetting the flags or counters or other
internal doodads it uses to compute optimizations.
Suggestion to speed freaks: break apart large queries inside the SP,
often that works, but when that doesn't work, try moving chunks into
separately compiled SPs, and you may find significantly better
performance falls your way.
Microsoft, take a look, wouldya?
J.
Hi
Without seeing the procedures then it is hard to comment on this. You may
want to read Kalens articles on Reuse of Query Plans and Conditional
Recompilation in SQL Server Magazine also
http://www.sql-server-performance.co...erver_udfs.asp may be useful
reading.
John
"JXStern" wrote:

> Last two or three years now I've been spending a lot of time doing
> SQLServer performance tuning. The last couple of weeks I've been
> doing this at a place using a whole lot of inline table-valued UDFs,
> an issue both in itself and because the database does a LOT of
> joining, and in this place, I've been finding one technique is working
> for me just a little too often.
> Time and again, there is some complex query that looks like it should
> be OK, but is running 100k's of logical reads and taking ten, twenty,
> thirty seconds to return a handfull of records. No doubt someone who
> can really, really read execution plans might be able to glance at the
> plan and see what's wrong, but I haven't reached quite that point yet.
> Time and again, what I find is that if I break out a chunk of code and
> run it stand-alone in QA, suddenly it runs "correctly", that is, with
> 99% fewer reads and 80-99% faster execution. OK, we've all seen that,
> we understand how much more the optimizer knows with explicit
> constants and such, and how SPs have to live with "parameter sniffing"
> and such. But I've gone the next step, wrapped ten or twenty or fifty
> lines in a relatively small SP or UDF, and darned if it doesn't still
> run fast, with all the same parameters and variables that it had in
> the original big SP.
> So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
> large SP, it is not properly resetting the flags or counters or other
> internal doodads it uses to compute optimizations.
> Suggestion to speed freaks: break apart large queries inside the SP,
> often that works, but when that doesn't work, try moving chunks into
> separately compiled SPs, and you may find significantly better
> performance falls your way.
> Microsoft, take a look, wouldya?
> J.
>
|||Hi
Well, can you post some data that we will be able to reprocude the
problem/bug?
Also, at least, can you show an execution plan of the query?
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:aieof11bdp2vfid400cslukgm94ivkqnjh@.4ax.com...
> Last two or three years now I've been spending a lot of time doing
> SQLServer performance tuning. The last couple of weeks I've been
> doing this at a place using a whole lot of inline table-valued UDFs,
> an issue both in itself and because the database does a LOT of
> joining, and in this place, I've been finding one technique is working
> for me just a little too often.
> Time and again, there is some complex query that looks like it should
> be OK, but is running 100k's of logical reads and taking ten, twenty,
> thirty seconds to return a handfull of records. No doubt someone who
> can really, really read execution plans might be able to glance at the
> plan and see what's wrong, but I haven't reached quite that point yet.
> Time and again, what I find is that if I break out a chunk of code and
> run it stand-alone in QA, suddenly it runs "correctly", that is, with
> 99% fewer reads and 80-99% faster execution. OK, we've all seen that,
> we understand how much more the optimizer knows with explicit
> constants and such, and how SPs have to live with "parameter sniffing"
> and such. But I've gone the next step, wrapped ten or twenty or fifty
> lines in a relatively small SP or UDF, and darned if it doesn't still
> run fast, with all the same parameters and variables that it had in
> the original big SP.
> So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
> large SP, it is not properly resetting the flags or counters or other
> internal doodads it uses to compute optimizations.
> Suggestion to speed freaks: break apart large queries inside the SP,
> often that works, but when that doesn't work, try moving chunks into
> separately compiled SPs, and you may find significantly better
> performance falls your way.
> Microsoft, take a look, wouldya?
> J.
>
|||On Thu, 11 Aug 2005 23:31:16 -0700, John Bell
<jbellnewsposts@.hotmail.com> wrote:
>Without seeing the procedures then it is hard to comment on this. You may
>want to read Kalens articles on Reuse of Query Plans and Conditional
>Recompilation in SQL Server Magazine also
>http://www.sql-server-performance.co...erver_udfs.asp may be useful
>reading.
Yes, I'm aware of the threat and menace of scalar UDFs in where
clauses and the like, but that isn't what I'm talking about here.
I'm not going to try to post actual code and plans from the app, cuz
(even if the code were not proprietary) it would take the schema and
stats as well to make any sense out of it. What I'm fishing for here
are similar stories and suspicions, or any official or unofficial
further descriptions of the problem or workarounds.
If I find myself with lots of time, I may try to synthesize an entire
duplicatable scenario, but it's liable to require a bunch of tables
and data to get the thing off the ground.
J.
|||Yes , I also experienced this. I have a sp 1000 lines long and when I run it
took too much time and when I devided it in 4 small sps it run about 150%
faster.
Even execution plan is almost same I can not find what causes the sp to run
faster when it is in small code.
May be locks are not released when they should be ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eZssQiwnFHA.1948@.TK2MSFTNGP12.phx.gbl...
> Hi
> Well, can you post some data that we will be able to reprocude the
> problem/bug?
> Also, at least, can you show an execution plan of the query?
>
>
> "JXStern" <JXSternChangeX2R@.gte.net> wrote in message
> news:aieof11bdp2vfid400cslukgm94ivkqnjh@.4ax.com...
>
|||Hi
You may want to look at
http://www.sql-server-performance.co...recompiles.asp and
subsequent links if you are getting recompiles.
John
"AM" wrote:

>
> Yes , I also experienced this. I have a sp 1000 lines long and when I run it
> took too much time and when I devided it in 4 small sps it run about 150%
> faster.
> Even execution plan is almost same I can not find what causes the sp to run
> faster when it is in small code.
> May be locks are not released when they should be ?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eZssQiwnFHA.1948@.TK2MSFTNGP12.phx.gbl...
>
>
|||I had much the same problem on a project a couple of years ago. It
usually turned out that a less than optimal plan was cached for the
original SP - mostly due to the SP being run the first time with
non-typical params. I used DBCC FREEPROCCACHE and DBCC
DROPCLEANBUFFERS to get a more accurate benchmark.
Good luck.
Payson
|||Thanks for your feedback.
I will try to give you a few pieces of information that may help explain
what you are seeing (which, as others have mentioned, would be more
definitive with the plan + additional information in question) - so, I am
guessing.
User latency for ad hoc queries is generally a function of compilation time
+ execution time. If your query is cached and run multiple times, it is
likely a function of execution time alone.
In either case, there are a number of algorithmic problems that can impact
plan choice and thus user latency, even if the query is run multiple times.
For example, if you join N tables together, there are a large number of ways
to actually execute this query based on the join order picked. Once you
have a sufficiently large number of tables, it actually becomes
algorithmically intractible to search the complete set of possible cases (in
a reasonable time - however, eventually it just becomes impossible). So,
heuristics are used to try to find a good join order relatively quickly.
This may or may not be the exact, optimal plan for a particular machine.
Alternatives are considered from this "spot" in the universe of possible
join orders. In some cases, it may be that the initial heuristic order is
not "close" to the optimal choice and significant searching is required to
get to the right plan. Additionally, it may be that the optimal plan is so
far away from the optimal choice as to make it impossible to consider that
choice in a reasonable amount of time using the heuristics I mentioned.
If your query is one of these cases, manually rewriting the query to remove
some of the joins could possibly make things run faster for your query +
data. It's not impossible to have such a case. I'll suggest that you can
also use query hints, in many cases, to force the join order into one that
may be more optimal for your query.
Root causes for picking an initial, heuristic order that is suboptimal
include missing statistics, statistics with a sample rate that is too low,
physical grouping of values on disk that causes problems in sampled
statistics interpolation, correlations between columns that cause the
optimizer to over- or under-estimate cardinality for a join or filter, or
others. In some cases, the technique you describe (moving part of the query
into a temporary result) can correct errors because part of the query
becomes materialized - the cardinality of that intermediate result thus does
not contain some of the errors and the rest of the query may have enough
information to pick a good join order.
Join order is one way in which this condition can happen - others also
exist, but the basic impact is generally the same.
I hope that this gives you some insight into the complexities involved in
this process.
Sincerely,
Conor Cunningham
SQL Server Query Optimization Development Lead
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:aieof11bdp2vfid400cslukgm94ivkqnjh@.4ax.com...
> Last two or three years now I've been spending a lot of time doing
> SQLServer performance tuning. The last couple of weeks I've been
> doing this at a place using a whole lot of inline table-valued UDFs,
> an issue both in itself and because the database does a LOT of
> joining, and in this place, I've been finding one technique is working
> for me just a little too often.
> Time and again, there is some complex query that looks like it should
> be OK, but is running 100k's of logical reads and taking ten, twenty,
> thirty seconds to return a handfull of records. No doubt someone who
> can really, really read execution plans might be able to glance at the
> plan and see what's wrong, but I haven't reached quite that point yet.
> Time and again, what I find is that if I break out a chunk of code and
> run it stand-alone in QA, suddenly it runs "correctly", that is, with
> 99% fewer reads and 80-99% faster execution. OK, we've all seen that,
> we understand how much more the optimizer knows with explicit
> constants and such, and how SPs have to live with "parameter sniffing"
> and such. But I've gone the next step, wrapped ten or twenty or fifty
> lines in a relatively small SP or UDF, and darned if it doesn't still
> run fast, with all the same parameters and variables that it had in
> the original big SP.
> So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
> large SP, it is not properly resetting the flags or counters or other
> internal doodads it uses to compute optimizations.
> Suggestion to speed freaks: break apart large queries inside the SP,
> often that works, but when that doesn't work, try moving chunks into
> separately compiled SPs, and you may find significantly better
> performance falls your way.
> Microsoft, take a look, wouldya?
> J.
>
|||Connor,
Thank you for your response. I'll comment on a few of your notes
below. However, my accusation remains in place, that I am seeing too
many cases where simply removing a lump of code to a separate SP gets
a totally different (and better) result from the compiler/optimizer,
such that I suspect it is not properly finding cutpoints in the text
of large SPs.
Joshua Stern
On Fri, 12 Aug 2005 13:01:35 -0700, "Conor Cunningham [MS]"
<conorc_removeme@.online.microsoft.com> wrote:

>Thanks for your feedback.
>I will try to give you a few pieces of information that may help explain
>what you are seeing (which, as others have mentioned, would be more
>definitive with the plan + additional information in question) - so, I am
>guessing.
>User latency for ad hoc queries is generally a function of compilation time
>+ execution time. If your query is cached and run multiple times, it is
>likely a function of execution time alone.
I make certain to null that out through reruns, which also generally
ends up caching the data and getting down to zero physical reads.
Most of the SPs I'm talking about here have runtimes of at least a
minute when I begin to work on them, and still take five or ten
seconds after improvements are in place, so the compilation is not
likely a huge factor in any case (well, except where there are a lot
of temp tables involved, but even there it's a fairly minor point,
which I realize I could check further with the profiler and such).

>In either case, there are a number of algorithmic problems that can impact
>plan choice and thus user latency, even if the query is run multiple times.
>For example, if you join N tables together, there are a large number of ways
>to actually execute this query based on the join order picked. Once you
>have a sufficiently large number of tables, it actually becomes
>algorithmically intractible to search the complete set of possible cases (in
>a reasonable time - however, eventually it just becomes impossible). So,
>heuristics are used to try to find a good join order relatively quickly.
>This may or may not be the exact, optimal plan for a particular machine.
>Alternatives are considered from this "spot" in the universe of possible
>join orders. In some cases, it may be that the initial heuristic order is
>not "close" to the optimal choice and significant searching is required to
>get to the right plan. Additionally, it may be that the optimal plan is so
>far away from the optimal choice as to make it impossible to consider that
>choice in a reasonable amount of time using the heuristics I mentioned.
All true in theory and practice, yet I wonder if there isn't some room
for improvement here. Where I have had success breaking a join of six
or sixteen tables into multiple queries, it hasn't seemed all that
difficult for me to guess how to decompose them best, and I do wonder
at how much better the heuristics could be.

>If your query is one of these cases, manually rewriting the query to remove
>some of the joins could possibly make things run faster for your query +
>data. It's not impossible to have such a case. I'll suggest that you can
>also use query hints, in many cases, to force the join order into one that
>may be more optimal for your query.
Never had much luck with hints.
What I'd like are optimizer hints, like "hey, this temp table is going
to only hold ten rows, so please use it as the root of your plan!")

>Root causes for picking an initial, heuristic order that is suboptimal
>include missing statistics, statistics with a sample rate that is too low,
>physical grouping of values on disk that causes problems in sampled
>statistics interpolation, correlations between columns that cause the
>optimizer to over- or under-estimate cardinality for a join or filter, or
>others. In some cases, the technique you describe (moving part of the query
>into a temporary result) can correct errors because part of the query
>becomes materialized - the cardinality of that intermediate result thus does
>not contain some of the errors and the rest of the query may have enough
>information to pick a good join order.
I don't believe I've had much of the cardinality issue, fwiw.

>Join order is one way in which this condition can happen - others also
>exist, but the basic impact is generally the same.
Any hints here, should the major tables be listed *last*? I think
most developers tend to list them first. I recall back in 6.x days
there was some order-dependency in where clauses (?) that I believe
went away as of SQL7.

>I hope that this gives you some insight into the complexities involved in
>this process.
Yes, it does. Until I started to specialize in this area, I had no
proper appreciation for just what it takes to turn a couple of lines
of SQL into an appropriate plan. I guess I would now wish for some
additional "transparency" on just WHY the optimizer chooses what it
does, so that I could use that as a basis for reorganizing code as
necessary. No doubt there are all sorts of additional topics to
explore in the area. But all in all, I *still* think there's a big,
fat bug in the compiler right now that is preventing it from doing
proper optimization of (logically independent) chunks of code in large
SPs.
Joshua Stern

I smell a big, fat compiler bug

Last two or three years now I've been spending a lot of time doing
SQLServer performance tuning. The last couple of ws I've been
doing this at a place using a whole lot of inline table-valued UDFs,
an issue both in itself and because the database does a LOT of
joining, and in this place, I've been finding one technique is working
for me just a little too often.
Time and again, there is some complex query that looks like it should
be OK, but is running 100k's of logical reads and taking ten, twenty,
thirty seconds to return a handfull of records. No doubt someone who
can really, really read execution plans might be able to glance at the
plan and see what's wrong, but I haven't reached quite that point yet.
Time and again, what I find is that if I break out a chunk of code and
run it stand-alone in QA, suddenly it runs "correctly", that is, with
99% fewer reads and 80-99% faster execution. OK, we've all seen that,
we understand how much more the optimizer knows with explicit
constants and such, and how SPs have to live with "parameter sniffing"
and such. But I've gone the next step, wrapped ten or twenty or fifty
lines in a relatively small SP or UDF, and darned if it doesn't still
run fast, with all the same parameters and variables that it had in
the original big SP.
So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
large SP, it is not properly resetting the flags or counters or other
internal doodads it uses to compute optimizations.
Suggestion to speed freaks: break apart large queries inside the SP,
often that works, but when that doesn't work, try moving chunks into
separately compiled SPs, and you may find significantly better
performance falls your way.
Microsoft, take a look, wouldya?
J.Hi
Without seeing the procedures then it is hard to comment on this. You may
want to read Kalens articles on Reuse of Query Plans and Conditional
Recompilation in SQL Server Magazine also
http://www.sql-server-performance.c...server_udfs.asp may be useful
reading.
John
"JXStern" wrote:

> Last two or three years now I've been spending a lot of time doing
> SQLServer performance tuning. The last couple of ws I've been
> doing this at a place using a whole lot of inline table-valued UDFs,
> an issue both in itself and because the database does a LOT of
> joining, and in this place, I've been finding one technique is working
> for me just a little too often.
> Time and again, there is some complex query that looks like it should
> be OK, but is running 100k's of logical reads and taking ten, twenty,
> thirty seconds to return a handfull of records. No doubt someone who
> can really, really read execution plans might be able to glance at the
> plan and see what's wrong, but I haven't reached quite that point yet.
> Time and again, what I find is that if I break out a chunk of code and
> run it stand-alone in QA, suddenly it runs "correctly", that is, with
> 99% fewer reads and 80-99% faster execution. OK, we've all seen that,
> we understand how much more the optimizer knows with explicit
> constants and such, and how SPs have to live with "parameter sniffing"
> and such. But I've gone the next step, wrapped ten or twenty or fifty
> lines in a relatively small SP or UDF, and darned if it doesn't still
> run fast, with all the same parameters and variables that it had in
> the original big SP.
> So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
> large SP, it is not properly resetting the flags or counters or other
> internal doodads it uses to compute optimizations.
> Suggestion to speed freaks: break apart large queries inside the SP,
> often that works, but when that doesn't work, try moving chunks into
> separately compiled SPs, and you may find significantly better
> performance falls your way.
> Microsoft, take a look, wouldya?
> J.
>|||Hi
Well, can you post some data that we will be able to reprocude the
problem/bug?
Also, at least, can you show an execution plan of the query?
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:aieof11bdp2vfid400cslukgm94ivkqnjh@.
4ax.com...
> Last two or three years now I've been spending a lot of time doing
> SQLServer performance tuning. The last couple of ws I've been
> doing this at a place using a whole lot of inline table-valued UDFs,
> an issue both in itself and because the database does a LOT of
> joining, and in this place, I've been finding one technique is working
> for me just a little too often.
> Time and again, there is some complex query that looks like it should
> be OK, but is running 100k's of logical reads and taking ten, twenty,
> thirty seconds to return a handfull of records. No doubt someone who
> can really, really read execution plans might be able to glance at the
> plan and see what's wrong, but I haven't reached quite that point yet.
> Time and again, what I find is that if I break out a chunk of code and
> run it stand-alone in QA, suddenly it runs "correctly", that is, with
> 99% fewer reads and 80-99% faster execution. OK, we've all seen that,
> we understand how much more the optimizer knows with explicit
> constants and such, and how SPs have to live with "parameter sniffing"
> and such. But I've gone the next step, wrapped ten or twenty or fifty
> lines in a relatively small SP or UDF, and darned if it doesn't still
> run fast, with all the same parameters and variables that it had in
> the original big SP.
> So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
> large SP, it is not properly resetting the flags or counters or other
> internal doodads it uses to compute optimizations.
> Suggestion to speed freaks: break apart large queries inside the SP,
> often that works, but when that doesn't work, try moving chunks into
> separately compiled SPs, and you may find significantly better
> performance falls your way.
> Microsoft, take a look, wouldya?
> J.
>|||On Thu, 11 Aug 2005 23:31:16 -0700, John Bell
<jbellnewsposts@.hotmail.com> wrote:
>Without seeing the procedures then it is hard to comment on this. You may
>want to read Kalens articles on Reuse of Query Plans and Conditional
>Recompilation in SQL Server Magazine also
>http://www.sql-server-performance.c...server_udfs.asp may be useful
>reading.
Yes, I'm aware of the threat and menace of scalar UDFs in where
clauses and the like, but that isn't what I'm talking about here.
I'm not going to try to post actual code and plans from the app, cuz
(even if the code were not proprietary) it would take the schema and
stats as well to make any sense out of it. What I'm fishing for here
are similar stories and suspicions, or any official or unofficial
further descriptions of the problem or workarounds.
If I find myself with lots of time, I may try to synthesize an entire
duplicatable scenario, but it's liable to require a bunch of tables
and data to get the thing off the ground.
J.|||
Yes , I also experienced this. I have a sp 1000 lines long and when I run it
took too much time and when I devided it in 4 small sps it run about 150%
faster.
Even execution plan is almost same I can not find what causes the sp to run
faster when it is in small code.
May be locks are not released when they should be ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eZssQiwnFHA.1948@.TK2MSFTNGP12.phx.gbl...
> Hi
> Well, can you post some data that we will be able to reprocude the
> problem/bug?
> Also, at least, can you show an execution plan of the query?
>
>
> "JXStern" <JXSternChangeX2R@.gte.net> wrote in message
> news:aieof11bdp2vfid400cslukgm94ivkqnjh@.
4ax.com...
>|||Hi
You may want to look at
http://www.sql-server-performance.c..._recompiles.asp and
subsequent links if you are getting recompiles.
John
"AM" wrote:

>
> Yes , I also experienced this. I have a sp 1000 lines long and when I run
it
> took too much time and when I devided it in 4 small sps it run about 150%
> faster.
> Even execution plan is almost same I can not find what causes the sp to ru
n
> faster when it is in small code.
> May be locks are not released when they should be ?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eZssQiwnFHA.1948@.TK2MSFTNGP12.phx.gbl...
>
>|||I had much the same problem on a project a couple of years ago. It
usually turned out that a less than optimal plan was cached for the
original SP - mostly due to the SP being run the first time with
non-typical params. I used DBCC FREEPROCCACHE and DBCC
DROPCLEANBUFFERS to get a more accurate benchmark.
Good luck.
Payson|||Thanks for your feedback.
I will try to give you a few pieces of information that may help explain
what you are seeing (which, as others have mentioned, would be more
definitive with the plan + additional information in question) - so, I am
guessing.
User latency for ad hoc queries is generally a function of compilation time
+ execution time. If your query is cached and run multiple times, it is
likely a function of execution time alone.
In either case, there are a number of algorithmic problems that can impact
plan choice and thus user latency, even if the query is run multiple times.
For example, if you join N tables together, there are a large number of ways
to actually execute this query based on the join order picked. Once you
have a sufficiently large number of tables, it actually becomes
algorithmically intractible to search the complete set of possible cases (in
a reasonable time - however, eventually it just becomes impossible). So,
heuristics are used to try to find a good join order relatively quickly.
This may or may not be the exact, optimal plan for a particular machine.
Alternatives are considered from this "spot" in the universe of possible
join orders. In some cases, it may be that the initial heuristic order is
not "close" to the optimal choice and significant searching is required to
get to the right plan. Additionally, it may be that the optimal plan is so
far away from the optimal choice as to make it impossible to consider that
choice in a reasonable amount of time using the heuristics I mentioned.
If your query is one of these cases, manually rewriting the query to remove
some of the joins could possibly make things run faster for your query +
data. It's not impossible to have such a case. I'll suggest that you can
also use query hints, in many cases, to force the join order into one that
may be more optimal for your query.
Root causes for picking an initial, heuristic order that is suboptimal
include missing statistics, statistics with a sample rate that is too low,
physical grouping of values on disk that causes problems in sampled
statistics interpolation, correlations between columns that cause the
optimizer to over- or under-estimate cardinality for a join or filter, or
others. In some cases, the technique you describe (moving part of the query
into a temporary result) can correct errors because part of the query
becomes materialized - the cardinality of that intermediate result thus does
not contain some of the errors and the rest of the query may have enough
information to pick a good join order.
Join order is one way in which this condition can happen - others also
exist, but the basic impact is generally the same.
I hope that this gives you some insight into the complexities involved in
this process.
Sincerely,
Conor Cunningham
SQL Server Query Optimization Development Lead
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:aieof11bdp2vfid400cslukgm94ivkqnjh@.
4ax.com...
> Last two or three years now I've been spending a lot of time doing
> SQLServer performance tuning. The last couple of ws I've been
> doing this at a place using a whole lot of inline table-valued UDFs,
> an issue both in itself and because the database does a LOT of
> joining, and in this place, I've been finding one technique is working
> for me just a little too often.
> Time and again, there is some complex query that looks like it should
> be OK, but is running 100k's of logical reads and taking ten, twenty,
> thirty seconds to return a handfull of records. No doubt someone who
> can really, really read execution plans might be able to glance at the
> plan and see what's wrong, but I haven't reached quite that point yet.
> Time and again, what I find is that if I break out a chunk of code and
> run it stand-alone in QA, suddenly it runs "correctly", that is, with
> 99% fewer reads and 80-99% faster execution. OK, we've all seen that,
> we understand how much more the optimizer knows with explicit
> constants and such, and how SPs have to live with "parameter sniffing"
> and such. But I've gone the next step, wrapped ten or twenty or fifty
> lines in a relatively small SP or UDF, and darned if it doesn't still
> run fast, with all the same parameters and variables that it had in
> the original big SP.
> So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
> large SP, it is not properly resetting the flags or counters or other
> internal doodads it uses to compute optimizations.
> Suggestion to speed freaks: break apart large queries inside the SP,
> often that works, but when that doesn't work, try moving chunks into
> separately compiled SPs, and you may find significantly better
> performance falls your way.
> Microsoft, take a look, wouldya?
> J.
>|||Connor,
Thank you for your response. I'll comment on a few of your notes
below. However, my accusation remains in place, that I am seeing too
many cases where simply removing a lump of code to a separate SP gets
a totally different (and better) result from the compiler/optimizer,
such that I suspect it is not properly finding cutpoints in the text
of large SPs.
Joshua Stern
On Fri, 12 Aug 2005 13:01:35 -0700, "Conor Cunningham [MS]"
<conorc_removeme@.online.microsoft.com> wrote:

>Thanks for your feedback.
>I will try to give you a few pieces of information that may help explain
>what you are seeing (which, as others have mentioned, would be more
>definitive with the plan + additional information in question) - so, I am
>guessing.
>User latency for ad hoc queries is generally a function of compilation time
>+ execution time. If your query is cached and run multiple times, it is
>likely a function of execution time alone.
I make certain to null that out through reruns, which also generally
ends up caching the data and getting down to zero physical reads.
Most of the SPs I'm talking about here have runtimes of at least a
minute when I begin to work on them, and still take five or ten
seconds after improvements are in place, so the compilation is not
likely a huge factor in any case (well, except where there are a lot
of temp tables involved, but even there it's a fairly minor point,
which I realize I could check further with the profiler and such).

>In either case, there are a number of algorithmic problems that can impact
>plan choice and thus user latency, even if the query is run multiple times.
>For example, if you join N tables together, there are a large number of way
s
>to actually execute this query based on the join order picked. Once you
>have a sufficiently large number of tables, it actually becomes
>algorithmically intractible to search the complete set of possible cases (i
n
>a reasonable time - however, eventually it just becomes impossible). So,
>heuristics are used to try to find a good join order relatively quickly.
>This may or may not be the exact, optimal plan for a particular machine.
>Alternatives are considered from this "spot" in the universe of possible
>join orders. In some cases, it may be that the initial heuristic order is
>not "close" to the optimal choice and significant searching is required to
>get to the right plan. Additionally, it may be that the optimal plan is so
>far away from the optimal choice as to make it impossible to consider that
>choice in a reasonable amount of time using the heuristics I mentioned.
All true in theory and practice, yet I wonder if there isn't some room
for improvement here. Where I have had success breaking a join of six
or sixteen tables into multiple queries, it hasn't seemed all that
difficult for me to guess how to decompose them best, and I do wonder
at how much better the heuristics could be.

>If your query is one of these cases, manually rewriting the query to remove
>some of the joins could possibly make things run faster for your query +
>data. It's not impossible to have such a case. I'll suggest that you can
>also use query hints, in many cases, to force the join order into one that
>may be more optimal for your query.
Never had much luck with hints.
What I'd like are optimizer hints, like "hey, this temp table is going
to only hold ten rows, so please use it as the root of your plan!")

>Root causes for picking an initial, heuristic order that is suboptimal
>include missing statistics, statistics with a sample rate that is too low,
>physical grouping of values on disk that causes problems in sampled
>statistics interpolation, correlations between columns that cause the
>optimizer to over- or under-estimate cardinality for a join or filter, or
>others. In some cases, the technique you describe (moving part of the quer
y
>into a temporary result) can correct errors because part of the query
>becomes materialized - the cardinality of that intermediate result thus doe
s
>not contain some of the errors and the rest of the query may have enough
>information to pick a good join order.
I don't believe I've had much of the cardinality issue, fwiw.

>Join order is one way in which this condition can happen - others also
>exist, but the basic impact is generally the same.
Any hints here, should the major tables be listed *last*? I think
most developers tend to list them first. I recall back in 6.x days
there was some order-dependency in where clauses (?) that I believe
went away as of SQL7.

>I hope that this gives you some insight into the complexities involved in
>this process.
Yes, it does. Until I started to specialize in this area, I had no
proper appreciation for just what it takes to turn a couple of lines
of SQL into an appropriate plan. I guess I would now wish for some
additional "transparency" on just WHY the optimizer chooses what it
does, so that I could use that as a basis for reorganizing code as
necessary. No doubt there are all sorts of additional topics to
explore in the area. But all in all, I *still* think there's a big,
fat bug in the compiler right now that is preventing it from doing
proper optimization of (logically independent) chunks of code in large
SPs.
Joshua Stern

I smell a big, fat compiler bug

Last two or three years now I've been spending a lot of time doing
SQLServer performance tuning. The last couple of weeks I've been
doing this at a place using a whole lot of inline table-valued UDFs,
an issue both in itself and because the database does a LOT of
joining, and in this place, I've been finding one technique is working
for me just a little too often.
Time and again, there is some complex query that looks like it should
be OK, but is running 100k's of logical reads and taking ten, twenty,
thirty seconds to return a handfull of records. No doubt someone who
can really, really read execution plans might be able to glance at the
plan and see what's wrong, but I haven't reached quite that point yet.
Time and again, what I find is that if I break out a chunk of code and
run it stand-alone in QA, suddenly it runs "correctly", that is, with
99% fewer reads and 80-99% faster execution. OK, we've all seen that,
we understand how much more the optimizer knows with explicit
constants and such, and how SPs have to live with "parameter sniffing"
and such. But I've gone the next step, wrapped ten or twenty or fifty
lines in a relatively small SP or UDF, and darned if it doesn't still
run fast, with all the same parameters and variables that it had in
the original big SP.
So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
large SP, it is not properly resetting the flags or counters or other
internal doodads it uses to compute optimizations.
Suggestion to speed freaks: break apart large queries inside the SP,
often that works, but when that doesn't work, try moving chunks into
separately compiled SPs, and you may find significantly better
performance falls your way.
Microsoft, take a look, wouldya?
J.Hi
Without seeing the procedures then it is hard to comment on this. You may
want to read Kalens articles on Reuse of Query Plans and Conditional
Recompilation in SQL Server Magazine also
http://www.sql-server-performance.c...server_udfs.asp may be useful
reading.
John
"JXStern" wrote:

> Last two or three years now I've been spending a lot of time doing
> SQLServer performance tuning. The last couple of weeks I've been
> doing this at a place using a whole lot of inline table-valued UDFs,
> an issue both in itself and because the database does a LOT of
> joining, and in this place, I've been finding one technique is working
> for me just a little too often.
> Time and again, there is some complex query that looks like it should
> be OK, but is running 100k's of logical reads and taking ten, twenty,
> thirty seconds to return a handfull of records. No doubt someone who
> can really, really read execution plans might be able to glance at the
> plan and see what's wrong, but I haven't reached quite that point yet.
> Time and again, what I find is that if I break out a chunk of code and
> run it stand-alone in QA, suddenly it runs "correctly", that is, with
> 99% fewer reads and 80-99% faster execution. OK, we've all seen that,
> we understand how much more the optimizer knows with explicit
> constants and such, and how SPs have to live with "parameter sniffing"
> and such. But I've gone the next step, wrapped ten or twenty or fifty
> lines in a relatively small SP or UDF, and darned if it doesn't still
> run fast, with all the same parameters and variables that it had in
> the original big SP.
> So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
> large SP, it is not properly resetting the flags or counters or other
> internal doodads it uses to compute optimizations.
> Suggestion to speed freaks: break apart large queries inside the SP,
> often that works, but when that doesn't work, try moving chunks into
> separately compiled SPs, and you may find significantly better
> performance falls your way.
> Microsoft, take a look, wouldya?
> J.
>|||Hi
Well, can you post some data that we will be able to reprocude the
problem/bug?
Also, at least, can you show an execution plan of the query?
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:aieof11bdp2vfid400cslukgm94ivkqnjh@.
4ax.com...
> Last two or three years now I've been spending a lot of time doing
> SQLServer performance tuning. The last couple of weeks I've been
> doing this at a place using a whole lot of inline table-valued UDFs,
> an issue both in itself and because the database does a LOT of
> joining, and in this place, I've been finding one technique is working
> for me just a little too often.
> Time and again, there is some complex query that looks like it should
> be OK, but is running 100k's of logical reads and taking ten, twenty,
> thirty seconds to return a handfull of records. No doubt someone who
> can really, really read execution plans might be able to glance at the
> plan and see what's wrong, but I haven't reached quite that point yet.
> Time and again, what I find is that if I break out a chunk of code and
> run it stand-alone in QA, suddenly it runs "correctly", that is, with
> 99% fewer reads and 80-99% faster execution. OK, we've all seen that,
> we understand how much more the optimizer knows with explicit
> constants and such, and how SPs have to live with "parameter sniffing"
> and such. But I've gone the next step, wrapped ten or twenty or fifty
> lines in a relatively small SP or UDF, and darned if it doesn't still
> run fast, with all the same parameters and variables that it had in
> the original big SP.
> So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
> large SP, it is not properly resetting the flags or counters or other
> internal doodads it uses to compute optimizations.
> Suggestion to speed freaks: break apart large queries inside the SP,
> often that works, but when that doesn't work, try moving chunks into
> separately compiled SPs, and you may find significantly better
> performance falls your way.
> Microsoft, take a look, wouldya?
> J.
>|||On Thu, 11 Aug 2005 23:31:16 -0700, John Bell
<jbellnewsposts@.hotmail.com> wrote:
>Without seeing the procedures then it is hard to comment on this. You may
>want to read Kalens articles on Reuse of Query Plans and Conditional
>Recompilation in SQL Server Magazine also
>http://www.sql-server-performance.c...server_udfs.asp may be useful
>reading.
Yes, I'm aware of the threat and menace of scalar UDFs in where
clauses and the like, but that isn't what I'm talking about here.
I'm not going to try to post actual code and plans from the app, cuz
(even if the code were not proprietary) it would take the schema and
stats as well to make any sense out of it. What I'm fishing for here
are similar stories and suspicions, or any official or unofficial
further descriptions of the problem or workarounds.
If I find myself with lots of time, I may try to synthesize an entire
duplicatable scenario, but it's liable to require a bunch of tables
and data to get the thing off the ground.
J.|||Yes , I also experienced this. I have a sp 1000 lines long and when I run it
took too much time and when I devided it in 4 small sps it run about 150%
faster.
Even execution plan is almost same I can not find what causes the sp to run
faster when it is in small code.
May be locks are not released when they should be ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eZssQiwnFHA.1948@.TK2MSFTNGP12.phx.gbl...
> Hi
> Well, can you post some data that we will be able to reprocude the
> problem/bug?
> Also, at least, can you show an execution plan of the query?
>
>
> "JXStern" <JXSternChangeX2R@.gte.net> wrote in message
> news:aieof11bdp2vfid400cslukgm94ivkqnjh@.
4ax.com...
>|||Hi
You may want to look at
http://www.sql-server-performance.c..._recompiles.asp and
subsequent links if you are getting recompiles.
John
"AM" wrote:

>
> Yes , I also experienced this. I have a sp 1000 lines long and when I run
it
> took too much time and when I devided it in 4 small sps it run about 150%
> faster.
> Even execution plan is almost same I can not find what causes the sp to ru
n
> faster when it is in small code.
> May be locks are not released when they should be ?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eZssQiwnFHA.1948@.TK2MSFTNGP12.phx.gbl...
>
>|||I had much the same problem on a project a couple of years ago. It
usually turned out that a less than optimal plan was cached for the
original SP - mostly due to the SP being run the first time with
non-typical params. I used DBCC FREEPROCCACHE and DBCC
DROPCLEANBUFFERS to get a more accurate benchmark.
Good luck.
Payson|||Thanks for your feedback.
I will try to give you a few pieces of information that may help explain
what you are seeing (which, as others have mentioned, would be more
definitive with the plan + additional information in question) - so, I am
guessing.
User latency for ad hoc queries is generally a function of compilation time
+ execution time. If your query is cached and run multiple times, it is
likely a function of execution time alone.
In either case, there are a number of algorithmic problems that can impact
plan choice and thus user latency, even if the query is run multiple times.
For example, if you join N tables together, there are a large number of ways
to actually execute this query based on the join order picked. Once you
have a sufficiently large number of tables, it actually becomes
algorithmically intractible to search the complete set of possible cases (in
a reasonable time - however, eventually it just becomes impossible). So,
heuristics are used to try to find a good join order relatively quickly.
This may or may not be the exact, optimal plan for a particular machine.
Alternatives are considered from this "spot" in the universe of possible
join orders. In some cases, it may be that the initial heuristic order is
not "close" to the optimal choice and significant searching is required to
get to the right plan. Additionally, it may be that the optimal plan is so
far away from the optimal choice as to make it impossible to consider that
choice in a reasonable amount of time using the heuristics I mentioned.
If your query is one of these cases, manually rewriting the query to remove
some of the joins could possibly make things run faster for your query +
data. It's not impossible to have such a case. I'll suggest that you can
also use query hints, in many cases, to force the join order into one that
may be more optimal for your query.
Root causes for picking an initial, heuristic order that is suboptimal
include missing statistics, statistics with a sample rate that is too low,
physical grouping of values on disk that causes problems in sampled
statistics interpolation, correlations between columns that cause the
optimizer to over- or under-estimate cardinality for a join or filter, or
others. In some cases, the technique you describe (moving part of the query
into a temporary result) can correct errors because part of the query
becomes materialized - the cardinality of that intermediate result thus does
not contain some of the errors and the rest of the query may have enough
information to pick a good join order.
Join order is one way in which this condition can happen - others also
exist, but the basic impact is generally the same.
I hope that this gives you some insight into the complexities involved in
this process.
Sincerely,
Conor Cunningham
SQL Server Query Optimization Development Lead
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:aieof11bdp2vfid400cslukgm94ivkqnjh@.
4ax.com...
> Last two or three years now I've been spending a lot of time doing
> SQLServer performance tuning. The last couple of weeks I've been
> doing this at a place using a whole lot of inline table-valued UDFs,
> an issue both in itself and because the database does a LOT of
> joining, and in this place, I've been finding one technique is working
> for me just a little too often.
> Time and again, there is some complex query that looks like it should
> be OK, but is running 100k's of logical reads and taking ten, twenty,
> thirty seconds to return a handfull of records. No doubt someone who
> can really, really read execution plans might be able to glance at the
> plan and see what's wrong, but I haven't reached quite that point yet.
> Time and again, what I find is that if I break out a chunk of code and
> run it stand-alone in QA, suddenly it runs "correctly", that is, with
> 99% fewer reads and 80-99% faster execution. OK, we've all seen that,
> we understand how much more the optimizer knows with explicit
> constants and such, and how SPs have to live with "parameter sniffing"
> and such. But I've gone the next step, wrapped ten or twenty or fifty
> lines in a relatively small SP or UDF, and darned if it doesn't still
> run fast, with all the same parameters and variables that it had in
> the original big SP.
> So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
> large SP, it is not properly resetting the flags or counters or other
> internal doodads it uses to compute optimizations.
> Suggestion to speed freaks: break apart large queries inside the SP,
> often that works, but when that doesn't work, try moving chunks into
> separately compiled SPs, and you may find significantly better
> performance falls your way.
> Microsoft, take a look, wouldya?
> J.
>|||Connor,
Thank you for your response. I'll comment on a few of your notes
below. However, my accusation remains in place, that I am seeing too
many cases where simply removing a lump of code to a separate SP gets
a totally different (and better) result from the compiler/optimizer,
such that I suspect it is not properly finding cutpoints in the text
of large SPs.
Joshua Stern
On Fri, 12 Aug 2005 13:01:35 -0700, "Conor Cunningham [MS]"
<conorc_removeme@.online.microsoft.com> wrote:

>Thanks for your feedback.
>I will try to give you a few pieces of information that may help explain
>what you are seeing (which, as others have mentioned, would be more
>definitive with the plan + additional information in question) - so, I am
>guessing.
>User latency for ad hoc queries is generally a function of compilation time
>+ execution time. If your query is cached and run multiple times, it is
>likely a function of execution time alone.
I make certain to null that out through reruns, which also generally
ends up caching the data and getting down to zero physical reads.
Most of the SPs I'm talking about here have runtimes of at least a
minute when I begin to work on them, and still take five or ten
seconds after improvements are in place, so the compilation is not
likely a huge factor in any case (well, except where there are a lot
of temp tables involved, but even there it's a fairly minor point,
which I realize I could check further with the profiler and such).

>In either case, there are a number of algorithmic problems that can impact
>plan choice and thus user latency, even if the query is run multiple times.
>For example, if you join N tables together, there are a large number of way
s
>to actually execute this query based on the join order picked. Once you
>have a sufficiently large number of tables, it actually becomes
>algorithmically intractible to search the complete set of possible cases (i
n
>a reasonable time - however, eventually it just becomes impossible). So,
>heuristics are used to try to find a good join order relatively quickly.
>This may or may not be the exact, optimal plan for a particular machine.
>Alternatives are considered from this "spot" in the universe of possible
>join orders. In some cases, it may be that the initial heuristic order is
>not "close" to the optimal choice and significant searching is required to
>get to the right plan. Additionally, it may be that the optimal plan is so
>far away from the optimal choice as to make it impossible to consider that
>choice in a reasonable amount of time using the heuristics I mentioned.
All true in theory and practice, yet I wonder if there isn't some room
for improvement here. Where I have had success breaking a join of six
or sixteen tables into multiple queries, it hasn't seemed all that
difficult for me to guess how to decompose them best, and I do wonder
at how much better the heuristics could be.

>If your query is one of these cases, manually rewriting the query to remove
>some of the joins could possibly make things run faster for your query +
>data. It's not impossible to have such a case. I'll suggest that you can
>also use query hints, in many cases, to force the join order into one that
>may be more optimal for your query.
Never had much luck with hints.
What I'd like are optimizer hints, like "hey, this temp table is going
to only hold ten rows, so please use it as the root of your plan!")

>Root causes for picking an initial, heuristic order that is suboptimal
>include missing statistics, statistics with a sample rate that is too low,
>physical grouping of values on disk that causes problems in sampled
>statistics interpolation, correlations between columns that cause the
>optimizer to over- or under-estimate cardinality for a join or filter, or
>others. In some cases, the technique you describe (moving part of the quer
y
>into a temporary result) can correct errors because part of the query
>becomes materialized - the cardinality of that intermediate result thus doe
s
>not contain some of the errors and the rest of the query may have enough
>information to pick a good join order.
I don't believe I've had much of the cardinality issue, fwiw.

>Join order is one way in which this condition can happen - others also
>exist, but the basic impact is generally the same.
Any hints here, should the major tables be listed *last*? I think
most developers tend to list them first. I recall back in 6.x days
there was some order-dependency in where clauses (?) that I believe
went away as of SQL7.

>I hope that this gives you some insight into the complexities involved in
>this process.
Yes, it does. Until I started to specialize in this area, I had no
proper appreciation for just what it takes to turn a couple of lines
of SQL into an appropriate plan. I guess I would now wish for some
additional "transparency" on just WHY the optimizer chooses what it
does, so that I could use that as a basis for reorganizing code as
necessary. No doubt there are all sorts of additional topics to
explore in the area. But all in all, I *still* think there's a big,
fat bug in the compiler right now that is preventing it from doing
proper optimization of (logically independent) chunks of code in large
SPs.
Joshua Stern

I smell a big, fat compiler bug

Last two or three years now I've been spending a lot of time doing
SQLServer performance tuning. The last couple of weeks I've been
doing this at a place using a whole lot of inline table-valued UDFs,
an issue both in itself and because the database does a LOT of
joining, and in this place, I've been finding one technique is working
for me just a little too often.
Time and again, there is some complex query that looks like it should
be OK, but is running 100k's of logical reads and taking ten, twenty,
thirty seconds to return a handfull of records. No doubt someone who
can really, really read execution plans might be able to glance at the
plan and see what's wrong, but I haven't reached quite that point yet.
Time and again, what I find is that if I break out a chunk of code and
run it stand-alone in QA, suddenly it runs "correctly", that is, with
99% fewer reads and 80-99% faster execution. OK, we've all seen that,
we understand how much more the optimizer knows with explicit
constants and such, and how SPs have to live with "parameter sniffing"
and such. But I've gone the next step, wrapped ten or twenty or fifty
lines in a relatively small SP or UDF, and darned if it doesn't still
run fast, with all the same parameters and variables that it had in
the original big SP.
So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
large SP, it is not properly resetting the flags or counters or other
internal doodads it uses to compute optimizations.
Suggestion to speed freaks: break apart large queries inside the SP,
often that works, but when that doesn't work, try moving chunks into
separately compiled SPs, and you may find significantly better
performance falls your way.
Microsoft, take a look, wouldya?
J.Hi
Without seeing the procedures then it is hard to comment on this. You may
want to read Kalens articles on Reuse of Query Plans and Conditional
Recompilation in SQL Server Magazine also
http://www.sql-server-performance.com/mw_sql_server_udfs.asp may be useful
reading.
John
"JXStern" wrote:
> Last two or three years now I've been spending a lot of time doing
> SQLServer performance tuning. The last couple of weeks I've been
> doing this at a place using a whole lot of inline table-valued UDFs,
> an issue both in itself and because the database does a LOT of
> joining, and in this place, I've been finding one technique is working
> for me just a little too often.
> Time and again, there is some complex query that looks like it should
> be OK, but is running 100k's of logical reads and taking ten, twenty,
> thirty seconds to return a handfull of records. No doubt someone who
> can really, really read execution plans might be able to glance at the
> plan and see what's wrong, but I haven't reached quite that point yet.
> Time and again, what I find is that if I break out a chunk of code and
> run it stand-alone in QA, suddenly it runs "correctly", that is, with
> 99% fewer reads and 80-99% faster execution. OK, we've all seen that,
> we understand how much more the optimizer knows with explicit
> constants and such, and how SPs have to live with "parameter sniffing"
> and such. But I've gone the next step, wrapped ten or twenty or fifty
> lines in a relatively small SP or UDF, and darned if it doesn't still
> run fast, with all the same parameters and variables that it had in
> the original big SP.
> So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
> large SP, it is not properly resetting the flags or counters or other
> internal doodads it uses to compute optimizations.
> Suggestion to speed freaks: break apart large queries inside the SP,
> often that works, but when that doesn't work, try moving chunks into
> separately compiled SPs, and you may find significantly better
> performance falls your way.
> Microsoft, take a look, wouldya?
> J.
>|||Hi
Well, can you post some data that we will be able to reprocude the
problem/bug?
Also, at least, can you show an execution plan of the query?
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:aieof11bdp2vfid400cslukgm94ivkqnjh@.4ax.com...
> Last two or three years now I've been spending a lot of time doing
> SQLServer performance tuning. The last couple of weeks I've been
> doing this at a place using a whole lot of inline table-valued UDFs,
> an issue both in itself and because the database does a LOT of
> joining, and in this place, I've been finding one technique is working
> for me just a little too often.
> Time and again, there is some complex query that looks like it should
> be OK, but is running 100k's of logical reads and taking ten, twenty,
> thirty seconds to return a handfull of records. No doubt someone who
> can really, really read execution plans might be able to glance at the
> plan and see what's wrong, but I haven't reached quite that point yet.
> Time and again, what I find is that if I break out a chunk of code and
> run it stand-alone in QA, suddenly it runs "correctly", that is, with
> 99% fewer reads and 80-99% faster execution. OK, we've all seen that,
> we understand how much more the optimizer knows with explicit
> constants and such, and how SPs have to live with "parameter sniffing"
> and such. But I've gone the next step, wrapped ten or twenty or fifty
> lines in a relatively small SP or UDF, and darned if it doesn't still
> run fast, with all the same parameters and variables that it had in
> the original big SP.
> So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
> large SP, it is not properly resetting the flags or counters or other
> internal doodads it uses to compute optimizations.
> Suggestion to speed freaks: break apart large queries inside the SP,
> often that works, but when that doesn't work, try moving chunks into
> separately compiled SPs, and you may find significantly better
> performance falls your way.
> Microsoft, take a look, wouldya?
> J.
>|||On Thu, 11 Aug 2005 23:31:16 -0700, John Bell
<jbellnewsposts@.hotmail.com> wrote:
>Without seeing the procedures then it is hard to comment on this. You may
>want to read Kalens articles on Reuse of Query Plans and Conditional
>Recompilation in SQL Server Magazine also
>http://www.sql-server-performance.com/mw_sql_server_udfs.asp may be useful
>reading.
Yes, I'm aware of the threat and menace of scalar UDFs in where
clauses and the like, but that isn't what I'm talking about here.
I'm not going to try to post actual code and plans from the app, cuz
(even if the code were not proprietary) it would take the schema and
stats as well to make any sense out of it. What I'm fishing for here
are similar stories and suspicions, or any official or unofficial
further descriptions of the problem or workarounds.
If I find myself with lots of time, I may try to synthesize an entire
duplicatable scenario, but it's liable to require a bunch of tables
and data to get the thing off the ground.
J.|||Hi
You may want to look at
http://www.sql-server-performance.com/rd_optimizing_sp_recompiles.asp and
subsequent links if you are getting recompiles.
John
"AM" wrote:
>
> Yes , I also experienced this. I have a sp 1000 lines long and when I run it
> took too much time and when I devided it in 4 small sps it run about 150%
> faster.
> Even execution plan is almost same I can not find what causes the sp to run
> faster when it is in small code.
> May be locks are not released when they should be ?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eZssQiwnFHA.1948@.TK2MSFTNGP12.phx.gbl...
> > Hi
> > Well, can you post some data that we will be able to reprocude the
> > problem/bug?
> >
> > Also, at least, can you show an execution plan of the query?
> >
> >
> >
> >
> >
> > "JXStern" <JXSternChangeX2R@.gte.net> wrote in message
> > news:aieof11bdp2vfid400cslukgm94ivkqnjh@.4ax.com...
> > > Last two or three years now I've been spending a lot of time doing
> > > SQLServer performance tuning. The last couple of weeks I've been
> > > doing this at a place using a whole lot of inline table-valued UDFs,
> > > an issue both in itself and because the database does a LOT of
> > > joining, and in this place, I've been finding one technique is working
> > > for me just a little too often.
> > >
> > > Time and again, there is some complex query that looks like it should
> > > be OK, but is running 100k's of logical reads and taking ten, twenty,
> > > thirty seconds to return a handfull of records. No doubt someone who
> > > can really, really read execution plans might be able to glance at the
> > > plan and see what's wrong, but I haven't reached quite that point yet.
> > > Time and again, what I find is that if I break out a chunk of code and
> > > run it stand-alone in QA, suddenly it runs "correctly", that is, with
> > > 99% fewer reads and 80-99% faster execution. OK, we've all seen that,
> > > we understand how much more the optimizer knows with explicit
> > > constants and such, and how SPs have to live with "parameter sniffing"
> > > and such. But I've gone the next step, wrapped ten or twenty or fifty
> > > lines in a relatively small SP or UDF, and darned if it doesn't still
> > > run fast, with all the same parameters and variables that it had in
> > > the original big SP.
> > >
> > > So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
> > > large SP, it is not properly resetting the flags or counters or other
> > > internal doodads it uses to compute optimizations.
> > >
> > > Suggestion to speed freaks: break apart large queries inside the SP,
> > > often that works, but when that doesn't work, try moving chunks into
> > > separately compiled SPs, and you may find significantly better
> > > performance falls your way.
> > >
> > > Microsoft, take a look, wouldya?
> > >
> > > J.
> > >
> >
> >
>
>|||I had much the same problem on a project a couple of years ago. It
usually turned out that a less than optimal plan was cached for the
original SP - mostly due to the SP being run the first time with
non-typical params. I used DBCC FREEPROCCACHE and DBCC
DROPCLEANBUFFERS to get a more accurate benchmark.
Good luck.
Payson|||Thanks for your feedback.
I will try to give you a few pieces of information that may help explain
what you are seeing (which, as others have mentioned, would be more
definitive with the plan + additional information in question) - so, I am
guessing.
User latency for ad hoc queries is generally a function of compilation time
+ execution time. If your query is cached and run multiple times, it is
likely a function of execution time alone.
In either case, there are a number of algorithmic problems that can impact
plan choice and thus user latency, even if the query is run multiple times.
For example, if you join N tables together, there are a large number of ways
to actually execute this query based on the join order picked. Once you
have a sufficiently large number of tables, it actually becomes
algorithmically intractible to search the complete set of possible cases (in
a reasonable time - however, eventually it just becomes impossible). So,
heuristics are used to try to find a good join order relatively quickly.
This may or may not be the exact, optimal plan for a particular machine.
Alternatives are considered from this "spot" in the universe of possible
join orders. In some cases, it may be that the initial heuristic order is
not "close" to the optimal choice and significant searching is required to
get to the right plan. Additionally, it may be that the optimal plan is so
far away from the optimal choice as to make it impossible to consider that
choice in a reasonable amount of time using the heuristics I mentioned.
If your query is one of these cases, manually rewriting the query to remove
some of the joins could possibly make things run faster for your query +
data. It's not impossible to have such a case. I'll suggest that you can
also use query hints, in many cases, to force the join order into one that
may be more optimal for your query.
Root causes for picking an initial, heuristic order that is suboptimal
include missing statistics, statistics with a sample rate that is too low,
physical grouping of values on disk that causes problems in sampled
statistics interpolation, correlations between columns that cause the
optimizer to over- or under-estimate cardinality for a join or filter, or
others. In some cases, the technique you describe (moving part of the query
into a temporary result) can correct errors because part of the query
becomes materialized - the cardinality of that intermediate result thus does
not contain some of the errors and the rest of the query may have enough
information to pick a good join order.
Join order is one way in which this condition can happen - others also
exist, but the basic impact is generally the same.
I hope that this gives you some insight into the complexities involved in
this process.
Sincerely,
Conor Cunningham
SQL Server Query Optimization Development Lead
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:aieof11bdp2vfid400cslukgm94ivkqnjh@.4ax.com...
> Last two or three years now I've been spending a lot of time doing
> SQLServer performance tuning. The last couple of weeks I've been
> doing this at a place using a whole lot of inline table-valued UDFs,
> an issue both in itself and because the database does a LOT of
> joining, and in this place, I've been finding one technique is working
> for me just a little too often.
> Time and again, there is some complex query that looks like it should
> be OK, but is running 100k's of logical reads and taking ten, twenty,
> thirty seconds to return a handfull of records. No doubt someone who
> can really, really read execution plans might be able to glance at the
> plan and see what's wrong, but I haven't reached quite that point yet.
> Time and again, what I find is that if I break out a chunk of code and
> run it stand-alone in QA, suddenly it runs "correctly", that is, with
> 99% fewer reads and 80-99% faster execution. OK, we've all seen that,
> we understand how much more the optimizer knows with explicit
> constants and such, and how SPs have to live with "parameter sniffing"
> and such. But I've gone the next step, wrapped ten or twenty or fifty
> lines in a relatively small SP or UDF, and darned if it doesn't still
> run fast, with all the same parameters and variables that it had in
> the original big SP.
> So, I accuse the compiler/optimizer of SQLServer 2000 sp 3x: given a
> large SP, it is not properly resetting the flags or counters or other
> internal doodads it uses to compute optimizations.
> Suggestion to speed freaks: break apart large queries inside the SP,
> often that works, but when that doesn't work, try moving chunks into
> separately compiled SPs, and you may find significantly better
> performance falls your way.
> Microsoft, take a look, wouldya?
> J.
>|||Connor,
Thank you for your response. I'll comment on a few of your notes
below. However, my accusation remains in place, that I am seeing too
many cases where simply removing a lump of code to a separate SP gets
a totally different (and better) result from the compiler/optimizer,
such that I suspect it is not properly finding cutpoints in the text
of large SPs.
Joshua Stern
On Fri, 12 Aug 2005 13:01:35 -0700, "Conor Cunningham [MS]"
<conorc_removeme@.online.microsoft.com> wrote:
>Thanks for your feedback.
>I will try to give you a few pieces of information that may help explain
>what you are seeing (which, as others have mentioned, would be more
>definitive with the plan + additional information in question) - so, I am
>guessing.
>User latency for ad hoc queries is generally a function of compilation time
>+ execution time. If your query is cached and run multiple times, it is
>likely a function of execution time alone.
I make certain to null that out through reruns, which also generally
ends up caching the data and getting down to zero physical reads.
Most of the SPs I'm talking about here have runtimes of at least a
minute when I begin to work on them, and still take five or ten
seconds after improvements are in place, so the compilation is not
likely a huge factor in any case (well, except where there are a lot
of temp tables involved, but even there it's a fairly minor point,
which I realize I could check further with the profiler and such).
>In either case, there are a number of algorithmic problems that can impact
>plan choice and thus user latency, even if the query is run multiple times.
>For example, if you join N tables together, there are a large number of ways
>to actually execute this query based on the join order picked. Once you
>have a sufficiently large number of tables, it actually becomes
>algorithmically intractible to search the complete set of possible cases (in
>a reasonable time - however, eventually it just becomes impossible). So,
>heuristics are used to try to find a good join order relatively quickly.
>This may or may not be the exact, optimal plan for a particular machine.
>Alternatives are considered from this "spot" in the universe of possible
>join orders. In some cases, it may be that the initial heuristic order is
>not "close" to the optimal choice and significant searching is required to
>get to the right plan. Additionally, it may be that the optimal plan is so
>far away from the optimal choice as to make it impossible to consider that
>choice in a reasonable amount of time using the heuristics I mentioned.
All true in theory and practice, yet I wonder if there isn't some room
for improvement here. Where I have had success breaking a join of six
or sixteen tables into multiple queries, it hasn't seemed all that
difficult for me to guess how to decompose them best, and I do wonder
at how much better the heuristics could be.
>If your query is one of these cases, manually rewriting the query to remove
>some of the joins could possibly make things run faster for your query +
>data. It's not impossible to have such a case. I'll suggest that you can
>also use query hints, in many cases, to force the join order into one that
>may be more optimal for your query.
Never had much luck with hints.
What I'd like are optimizer hints, like "hey, this temp table is going
to only hold ten rows, so please use it as the root of your plan!")
>Root causes for picking an initial, heuristic order that is suboptimal
>include missing statistics, statistics with a sample rate that is too low,
>physical grouping of values on disk that causes problems in sampled
>statistics interpolation, correlations between columns that cause the
>optimizer to over- or under-estimate cardinality for a join or filter, or
>others. In some cases, the technique you describe (moving part of the query
>into a temporary result) can correct errors because part of the query
>becomes materialized - the cardinality of that intermediate result thus does
>not contain some of the errors and the rest of the query may have enough
>information to pick a good join order.
I don't believe I've had much of the cardinality issue, fwiw.
>Join order is one way in which this condition can happen - others also
>exist, but the basic impact is generally the same.
Any hints here, should the major tables be listed *last*? I think
most developers tend to list them first. I recall back in 6.x days
there was some order-dependency in where clauses (?) that I believe
went away as of SQL7.
>I hope that this gives you some insight into the complexities involved in
>this process.
Yes, it does. Until I started to specialize in this area, I had no
proper appreciation for just what it takes to turn a couple of lines
of SQL into an appropriate plan. I guess I would now wish for some
additional "transparency" on just WHY the optimizer chooses what it
does, so that I could use that as a basis for reorganizing code as
necessary. No doubt there are all sorts of additional topics to
explore in the area. But all in all, I *still* think there's a big,
fat bug in the compiler right now that is preventing it from doing
proper optimization of (logically independent) chunks of code in large
SPs.
Joshua Stern