SQL Query Optimizations, Sponsored by PASS
Host Guide_KenM:
Welcome to today's TechNet Chat. Our topic is "SQL Query Optimizations, Sponsored by PASS". Questions, comments, and suggestions are welcome. The Input Room is where you can enter questions for our hosts today. We will read them and select questions to answer.
The questions and answers will be posted in the Reading Room.
Host Guest_MichaelOConnor_ms:
Michael O'Connor, SQL Server BETA Program Manager. This is the time to give us your questions about Query Optimizations! Anybody have anything particularly difficult they would like this crack team to take on??
Host Guest_nigele_ms:
Hi, I'm Nigel Ellis. I'm the development manager for the SQL Server Query Processor team.
Host Guest_MichaelOConnor_ms:
Your questions don't have to be limited to SP2.
Host Guest_Lubor_MS:
Hi, I'm Lubor Kollar, Lead Program Manager in Query Processor team (QP team consists of Q Optimization and Q Execution).
Host Guest_nigele_ms:
Vera, when you say your statistics are out of date, do you see an auto-update occur on the first query against the table? What happens when you run your next query against the table? Do you see updated statistics or not? Vera - we'll have to investigate this issue offline. On UDFs: Yes, we're looking at extensions to make the UDFs more usable.
Host Guest_prakash_ms:
Hi, I'm Prakash Sundaresan, dev-lead in the Query Execution team.
Q: Hi guys! It would be great if a new Service Pack would ship a new version of Northwind database with proper indexes and statistics to play with.
Host Guest_MichaelOConnor_ms:
A: We are looking at building a much more comprehensive demo database that will cover several aspects of SQL development in future versions of SQL Server. Unfortunately, there are no plans to update the Northwinds database in service packs. This just isn't up there with taking any bug fixes we may have.
steve_fms:
Q: A general question I have is what strategies can I use to speed up full-text searches?
Host Guest_Lubor_MS:
A: The best source are KB articles. Try https://support.microsoft.com/default.aspx?scid=FH;EN-US;KBHOWTO&sd=GN&ln=EN-US.
Arnie:
Q: What's the timeline for the new demo database?
Host Guest_MichaelOConnor_ms:
A: The next major release of SQL Server, date TBD.
Fernando:
Q: What about providing a NULL entry in the Density?
Host Guest_Lubor_MS:
A: We hear you. We will fix it in the next release.
Emil:
Q: I was wondering if any of you would explain the workings of splits.
Host Guest_Lubor_MS:
A: This is a complex subject. I will try to provide a short answer. Splits usually do not affect performance dramatically (as opposed to, for example, Query Plan selection).
Vera:
Q: I noticed that today I cannot use internal XPs like the XML ones in a UDF but I can use regular XPs like sp_OA. Why is that different?
Host Guest_nigele_ms:
A: Which internal XPs are you referring to? There are limitations on calling of "side effecting" functions or stored procedures from UDFs. These take the same form as the getdate() limitation you referred to earlier.
izaltsman:
Q: What could cause an update statement use different execution plans when executed by itself, versus when executed as part of a stored procedure?
Host Guest_Lubor_MS:
A: For example, variables used in the query; also the whole SP is compiled as a single unit.
Host Guest_MichaelOConnor_ms:
Hey, we are keeping up with the questions, keep them coming!
Vera:
Q: Way back in pre-7.0, there was a lot of talk about adding indexing strategies beside the current b-tree, like hash indexes and maybe even bitmap indexes. Is this ever going to happen?
Host Guest_prakash_ms:
A: There are no specific plans to add hash or bitmap indexes at this point. We feel that we get a lot of the benefits of those indexes via our existing hash join and index intersection operations. Having said that, we are actively looking at other index enhancements for future releases.
mh:
Q: Do UDFs have different optimizations than stored procedures? We are using a UDF to select from a table of "constants" so that we can reference values consistently throughout our stored procs without having to hardcode these values.
Host Guest_nigele_ms:
A: It depends. There are two basic forms of UDFS: inline-able and non-inline-able. The form depends on the syntax you use. If you declare the inline form, you are really creating a parameterized view that will be optimized as part of the parent-calling query. If you declare the non-inline form, this will be optimized as if it were a stored procedure.
Host Guest_MichaelOConnor_ms:
For beta participants, don't forget the Beta Postmortem Chat on Thursday, November 29th!
Host Guest_MichaelOConnor_ms:
We are all caught up on questions! Do you have any more for us? We've got a lot of guys taken away from development to answer your questions!
Vera:
Q: sp_xml_preparedocument and sp_xml_removedocument, for example, or sp_executesql, which are all showing up in sysobjects as X but do not reference a DLL, so sp_helptext returns (server internal).
Host Guest_nigele_ms:
A: Yes, there are extended stored procedures implemented as part of the SQL Server kernel process. Extended stored procedures don't have to be in a DLL (if they are Microsoft-shipped).
mh:
Q: We would like the capability to pass parameters from an update, delete, or insert to lower-level triggers. For instance, a key value is in the initial "deleted" set, is not required for a level 1 trigger but is required for a level 2 trigger.
Host Guest_Lubor_MS:
A: I think you can use a temporary table to pass such values.
Vera:
Q: Nigel, why is the behavior different between a "kernel" XP and a regular XP in a DLL? Why can I use one and not the other?
Host Guest_nigele_ms:
A: It depends on whether they are side effecting or not. "sp_executesql" has internal side effects on the server procedure cache. For the sp_xml sprocs, I'm not sure why we don't allow it there. I'll take this as an issue for follow-up.
Emil:
Q: For non-inline, table-valued UDFs used as parts of another query, is there any way of affecting the execution plan strategy at all?
Host Guest_prakash_ms:
A: For non-inline-able TV-UDFs, we execute the UDF into a temp table in the first step and then pass the temp table to the outer query in the second step. In general, it is always better to make a UDF inline-able if possible.
robert:
Q: Are there any plans to add ways to manipulate how a query plan is executed, besides the few join and query hints available? There have been times when I wished I could force a query to execute a certain way that I felt was better and couldn't.
Host Guest_Lubor_MS:
A: It is true that you cannot enforce each possible query plan. But you can enforce almost all "useful" plans. Can you be more specific about what kind of operator/function you would like to, and cannot, enforce?
Vera:
Q: I have 8 different servers connected through linked servers.
Host Guest_prakash_ms:
A: Is there an error-code?
bip:
Q: How can I delete 300000 rows table without using CURSOR and filling up the Transaction log?
Host Guest_Lubor_MS:
A: If you can do TRUNCATE TABLE, that would be fast. SET kind of DELETE is faster than CURSOR, but you will still need the log space.
Marlo:
Q: Was any work done to enhance performance of OR/IN clauses? I find in SQL 7.0 and SQL 2000 SP1 (not tested in SP2 yet) that I have to rewrite many queries that contain OR/IN statements and other equi-joins into UNIONS for performance.
Host Guest_nigele_ms:
A: Our design treats OR vs. IN as semantically equivalent. Executing then using UNION is a strategy we consider. If you are experiencing problems with this in your application, you should follow up with PSS.
paschott:
Q: What about "short-circuit" queries? Those were useful in 7.0 but were removed in SQL 2000. Are there any plans to bring that back?
Host Guest_nigele_ms:
A: What do you mean they were removed? Can you provide a specific example?
mh:
Q: To Lubor_MS, we are using temp tables currently. I was wondering if you foresee an enhancement in the future to support this natively.
Host Guest_Lubor_MS:
A: Not in the near future. Please send e-mail to sqlwish@microsoft.com
explaining the requirement. What is important is to understand is what improvement you expect from the native support compared to the temp table approach.
Server:
Q: Msg 7318, Level 16, State 1, Line 5 OLE DB provider 'SQLOLEDB' returned an invalid column definition.
Host Guest_prakash_ms:
A: Which release (and service pack) are you seeing this on?
Fernando:
Q: In the newsgroups, we always have the same question: Which is the most efficient way to retrieve rows n to m from a given query? Are there any plans to support an extension to the SELECT statement to do this?
Host Guest_nigele_ms:
A: Currently, there are two ways to approach this. Assuming you are running a "paging" style query, where all rows need to be returned, you can use a "fast forward cursor" model with an N-row fetch buffer to fetch N rows at a time. If you need a "stateless fetch", the only way to approach this is using keys of resultset, where you remember the prior key value and run a query such as: select top N ... where key > @lastkey.
bip:
Q: What is the best way to deal with issue on Tran Log being filled up on massive delete?
Host Guest_nigele_ms:
A: An alternative way to process this is to perform "chunk deletes". You loop while @@rowcount > 0 doing: do set rowcount N begin tran delete ... where ... commit while @@rowcount > 0.
ygreis:
Q: I need to transfer about 10000 rows from one DB table to another DB table every day. I use a cursor and call a check_err stored check each row data first, and if the data is okay, then insert to table. It is very slow. Do you have any better way?
Host Guest_Lubor_MS:
A: It is better to do it as a set operation. But then also the test will be done on all rows, and you will have to decide if you want to remove the invalid rows or abort the whole set operation.
paschott:
Q: Using something like "where (@var1 = THIS AND OtherVar = 'Something') OR (@var2 = xxx AND AdditionalStuff = ZZZ)" would only execute the appropriate variable section instead of testing all of them to see if they match up.
Host Guest_nigele_ms:
A: We had a problem related to this scenario that was addressed for 8.0 SP2. Please retry your scenario against this build.
robert:
Q: One example that I remember is that when using OPTION (FORCE ORDER) and three UNIONS, parallelism is used on some queries but not all. I want to be able to tell SQL Server when to use parallelism and when not too.
Host Guest_Lubor_MS:
A: You are correct. You cannot force parallelism on parts of a query. In fact, you cannot force parallelism for a query. Why would you want to do it? Do you believe it will perform better?
paschott:
Q: This stopped some procs from working as they did in 7.0 because SQL no longer did a short-circuit to only test that first @var = xxx part. It now tests all parts of the WHERE clause from what I can tell instead of stopping once one part did.
Host Guest_nigele_ms:
A: There wasn't any change made in this area; the semantics of SQL does allow short-circuit evaluation, and we take advantage of this in our optimization engine. If you are seeing problems with this, please file a case with PSS.
Vera:
Q: What is better: select col1 from t1 or select col1 from dbo.t1? Does prefixing the object owner improve (reduce) the time to parse the query? I would think so, logically, but is it true?
Host Guest_MichaelOConnor_ms:
A: Yes, it does really improve performance to specify the owner. By default, SQL Server first checks your logon for matching tables, then DBO, so you save SQL a step by specifying.
ygreis:
Q: Lubor, what about set operation. Can you give me some details?
Host Guest_Lubor_MS:
A: Anything that is not dealing with CURSOR is a set operation. Ask for more if needed.
mh:
Q: Consider: select... where isnull(col1,'') = isnull(@var1,''). Will this optimize as well as the ANSI solution: where (col1 is null and @var1 is null) or col1 = @var1.
Host Guest_nigele_ms:
A: There are some limited cases where we recognize and optimize for intrinsics, but in general you are much better being explicit in your predicates—i.e., using the long ANSI form is preferable. There are some limited cases where we recognize…
Host Guide_KenM:
We are going to have to wrap up this chat in about 12 minutes. We are working on answers to some last questions though.
Vera:
Q: Will SP2 include a Books Online update? Why are they always behind? It should be easy to update books online on the fly. Isn't that where the Online stands for?
Host Guest_Lubor_MS:
A: No, there is no plan to do that, but there may be separate update of the BOL sometime.
paschott:
Q: Any ideas on when SP2 will be released?
Host Guest_MichaelOConnor_ms:
A: In December of this year. We can not be more specific because it depends on how beta testing turns out.
Fernando:
Q: Nigel, the problem with the solution you mention for the stateless fetch is if your query doesn't have a key as such, or is a multiple key.
Host Guest_nigele_ms:
A: If you don't have a key, you can't use this approach. However, a multi-column key can still use this technique (it just gets more complex). k1 > @k OR (k1=@k AND k2>@k2) OR (k1=@k AND k2=@k2 AND K3>@k3).
Moltas:
Q: What about Yukon? When do we expect to see some early copies of that?
Host Guest_MichaelOConnor_ms:
A: We can't talk about that yet, sorry. :(
TonyLewis:
Q: The join and where clauses are using indexes. What's the best way to approach this, using Tablock optimizer hint? Concurrency is not an issue as the statement can be scheduled in off-peak hours.
Host Guest_prakash_ms:
A: I would try to isolate whether the query is taking too much time because it is waiting on locks or whether the processing itself is taking time. Using the tablock hints will help you eliminate waiting on locks. Please use this to isolate the problem, not necessarily as a final solution.
Vera:
Q: What is the exact evaluation order if no owner is provided? USER? DBO?
Host Guest_MichaelOConnor_ms:
A: If an object is fully qualified, it can be shared across the query cache and is therefore faster. Non-qualified stored proc calls must be evaluated for ownership each time, so you don't get the sharing advantage. Yes, User, DBO is the correct order.
Host Guide_KenM:
We are going to have to wrap up this chat in a few minutes. We are working on some final answers.
Arnie:
Q: If a table has a clustered index, and a view on that table is indexed, where is the data? In the leaf nodes of the table clustered index, or in the leaf nodes of the view clustered index? Or does SQL keep duplicate data in both locations?
Host Guest_Lubor_MS:
A: Duplicate data in both locations. This is similar as with normal index: data is both in the table and in the index.
Vera:
Q: Is there an authors' program for Yukon?
Host Guest_MichaelOConnor_ms:
A: It's too early really to talk about this. Stay tuned and check back with us in a few months. :)
robert:
Q: I don't have specific examples in front of me as I did in-depth analysis on SPs a few months ago. I just remember being frustrated that I couldn't manipulate the query execution more…
Host Guest_Lubor_MS:
A: In general, we are trying not to encourage users to use hints. Query optimizer should get the plan right in the first place, and we are improving it each release and SP, so we hope the need will go down.
Host Guide_KenM:
Thanks for joining us today! You've asked some great questions. Unfortunately, it's time to go.
Host Guest_MichaelOConnor_ms:
Fernando, we are not going to get to that question. Please take it to the newsgroups, and we'll get someone to take a look at it! For product feature suggestions, please send us notes at sqlwish@microsoft.com! Thanks for attending today's chat!
Host Guest_KurtWindisch:
Thanks everyone!! Transcripts for this chat will be available on the PASS Web site and on the TechNet Web site.
We at Microsoft Corporation hope that the information in this work is valuable to you. Your use of the information contained in this work, however, is at your sole risk. All information in this work is provided "as-is", without any warranty, whether express or implied, of its accuracy, completeness, fitness for a particular purpose, title or non-infringement, and none of the third-party products or information mentioned in the work are authored, recommended, supported or guaranteed by Microsoft Corporation. Microsoft Corporation shall not be liable for any damages you may sustain by using this information, whether direct, indirect, special, incidental or consequential, even if it has been advised of the possibility of such damages.