TechNet Chat - SQL Server 2000 SP1: Query Optimizations

Published: Thursday, May 3, 2001 at 10:00 a.m. PST

Host Guide_KenM:
Welcome to today's TechNet Chat. Our topic is SQL Server 2000 SP1: Query Optimizations. Questions, comments, and suggestions are welcome.

Host Guide_KenM:
We have a full line-up of experts for you today: Nigel Ellis, Development Manager, query processor team; Prakash Sundaresan, Technical Lead, query processor team; Nick Kline, Development Lead, query processor team; Cesar Galindo-Legaria, Development Lead, query processor team; Lubor Kollar, Program Manager, query processor team; James Morris, Program Manager, SQL Server beta programs.

Host Guest_ms_nigel:
Let's get going!

Host Guest_MS_jamMo:
Now would be a good time to ask questions related to Query Optimization! Your questions do not have to be specific to SP1, it can be related to SQL Server.

Suprio:
Q: Hi, I sent two questions yesterday. Did you receive that?

Host Guest_ms_nigel:
"What tools are available to estimate the number for rows to be returned?"

Host Guest_MS_jamMo:
A: I believe we are answering those right now and will post them soon.

Host Guest_ms_nigel:
SQL server query analyser can be used to estimate the number of rows returned from query. You can use graphical showplan or use the options 'SET SHOWPLAN_TEXT' or 'SET SHOWPLAN_ALL', which will return the plan chosen for execution. The showplan output contains information on estimated rows returned. You can also see actual numbers by using "show execution plan" or "set statistics profile on".

Darwin:
Q: We have had problems submitting extremely complicated queries to SQL Server, especially on v.7 with and without SP1, such that the queries return incorrect results (missing rows or missed data that would satisfy joins).

Host Guest_nick_at_ms:
A: We have had some similar problems reported. Many of the reported problems are actually not bugs, but they look like problems, due to the complicated semantics for outer joins and null handling. I recommend upgrading to the latest service pack on 7.0, SP4.

MhimedA:
Q: What is the most definitive document on understanding the showplan output, graphical or text, available from Microsoft or third-party authors?

Host Guest_Lubor:
A: The Books On Line that are installed with the SLQ Server. I haven't seen any better description. You can also look here for documentation, http://msdn.microsoft.com/sqlserver/.

mrMichael:
Q: OK. Could you explain how query cost in Execution Plan relates to the duration in Profiler. In my particular case, I see a query with a cost 0.1%, but it is maximum in duration.

Host Guest_nick_at_ms:
A: If you have multiple queries, then on each query it tells what portion of time that particular query takes of all the queries you optimized together. So if you have a 10-second query and a 30-second query, the first one takes 25%.

Host Guest_nick_at_ms:
Suprio - Oops, I meant to say SP3 on 7.0.

mrMichael:
Q: As a continuation, what is the most reliable number from profiler trace Duration, CPU, Reads, Writes? Why sometime it is 0 cpu, 10 reads and duration sky high.

Host Guest_nick_at_ms:
A: Duration is accurate.

Q: I understand the SQL Server 7/2K supports TRUE row-level locking. I know that you've supported this at the leaf (data) pages. Is it also supported for btree splits/merges?

Host Guest_ms_nigel:
A: Yes, this is also supported for interior splits.

Suprio:
Q: Is SQL Server 7.0 SP4 already available? Where can I download it?

Host Guest_MS_jamMo:
A: I can't be precise...but I would look for it Q1 2002. You will be able to download it through Microsoft Download Center.

bac0n:
Q: I have a general SQL question if you accept those: Is there a Microsoft SQL equivalent to the PLSQL nval command?

Host Guest_Lubor:
A: If you think NVL then the T-SQL equivalent is ISNULL.

dbjA:
Q: On SQL 6.5 a query with 'where columnC = X' would not return the row. If specify any other column in row, it will return row. ColumnC part of index which was recreated. Any idea what could have happened?

Host Guest_MS_jamMo:
A: dbja, could you post more information for us?

Q: Within a transaction running at isolation level READ COMMITTED (default) does SQL Server release...?

Host Guest_ms_nigel:
A: Yes, we release locks as we skip over rows unless there is a reason to hold the locks (say using a fetch after a sort operation).

Brad:
Q: How do I get SQL Server 2000 SP1? I do not see it on MSDN.

Host Guest_MS_jamMo:
A: Brad, SP1 is not generally available yet, but it is through the BETA program. If you are interested in being a part of the BETA program, e-mail betareq@microsoft.com, and they'll get you in!

MrMichael:
Q: This is the theory you are telling me. I have a query that runs the longest in the batch duration-wise. Yet the execution plan somehow reports that a couple of trigger queries take 13% of batch and their duration is 0 in the profiler.

Host Guest_nick_at_ms:
A: You have to separate estimates and actual. Duration is actual time it took. Estimates are not always exactly correct, of course. Those percentages come from optimizing the original queries. You could try running the query with "set statistics profile on", and this will tell you many rows each query processes, and you can see how much work they really do.

Q: Is SQL 2000 faster with more or less memory than 7.0"?

Host Guest_ms_nigel:
A: It really depends on the workload. We continue to improve the product by adding more complex query plans and operators. Assume you need the same memory and then check your baseline perf.

DaveG:
Q: What are the rules in the query engine for optimizing out unnecessary queries, in particular joins and outer joins?

Host Guest_nick_at_ms:
A: In 7.0 we simplify outer joins into joins. If you have an outer join "A LEFT JOIN B", say, in a view, and the query uses the view and applies a null-rejecting filter or join on B--for example B.a > 10--then the outer join is turned into inner join A JOIN B. In 8.0 we remove joins based on foreign key information. Again, say you have a view with a join ORDERS JOIN CUSTOMERS, with foreign key constraint, and the query is using.

Q: What about using GUIDS as PKs?

Host Guest_ms_nigel:
A: They are clearly larger , if using the a clustered PK, note the clustering columns will appear in all non-clustered indexes. This can cause "bloat" and take more space.

Host Guest_nick_at_ms:
Answer continued: Only columns from ORDERS, then the join with customers is discarded. A restriction in 8.0 is that the foreign key is one column (not multi-column). In the case of outer join, say A LEFT JOIN B. It will be reduced to simply A if no columns from B are used later in the query, and either: 1) The outerjoin is on a key of B (need not be foreign key from A), or 2) there is a distinct on some columns from A so that the number of duplicates introduced by the outerjoin are not relevant.

scotts:
Q: On SQL Sever 2000, you can have database with different collations. When you use the "create table" statement to create a temp table, it uses the collation of TempDB. However, if you use a "select into" statement to create the temp table it uses the collation of the source table. Shouldn't these work the same?

GUIDS:
Q: As with other data types, the performance is always a function of the amount of data retrieved and compared. However, there are no specific issues with using them in indices.

Rajesh from BTS:
A: If you create a table, then the table will get default collation as per database level collation. When we do select into, we create the table on the fly based on the source table so the new temp table or table in another database will have collation based on the parent table.

Host Guest_nick_at_ms:
MrMichael, please ask again if you need more help.

dbjA:
Q: Select * from tab1 where columnA='x' works. Select * from tab1 where columnC='x' returns no rows. Both should return the same row. ColumnC is in multiple indexes including clustered. Rebuilt index, copied row to temp table, perform dbcc. No results.

Host Guest_MS_jamMo:
A: Send mail to rajeshpa@microsoft.com, and he will help research this one.

Suprio:
Q: Does SQL Server 2000 support EBCDIC collations especially when working with OLE DB providers?

Host Guest_Lubor:
A: Sorry, we don't have experts on this here right now. I will try to find out...

mrMichael:
Q: Thank you, Nick. So to make sure I understood you right, Duration is a correct number as suppose to Qry. cost in the execution plan.

Host Guest_nick_at_ms:
A: Duration is actually measured. Estimates are estimates. Using statistics, histograms, and schema information, we estimate how long the query will take. Sometimes we are wrong. If it's wrong, look for problems in statistics and card estimates. Look at the actual rows returned when running the query with "set statistics profile on" vs. what is estimated.

Brad:
Q: I have a database table which includes the date as char in yyyymmdd format. Is there a way, perhaps using an indexes view, to end up with an index on this date using the date data type?

Host Guest_Lubor:
A: The best way is to use a computed column, and then to…

Q: "index rebuild takes a long time".

Host Guest_ms_nigel:
A: Why are you rebuilding the indexes every night? This used to be common practice on 6.5 but should no longer be needed on 7.0 and 2000. In SQL 2000, we added 'DBCC INDEXDEFRAG', which will occur online. If you still think you need to rebuild the indexes, make sure you aren't rebuilding the non-clustered indexes first - always rebuild the clustered indices first (only) since this causes rebuild of the n/c indexes anyway

Suprio:
Q: Does SQL Server 2000 support EBCDIC collations especially when working with OLE DB providers?

Host Guest_Lubor:
A: No, the conversion has to happen in the provider. The SQL Server has to see either UNICODE or ASCII.

mrMichael:
Q: How expensive it is to use inserted/deleted pseudo tables? Is it more expensive to store its data into Table variable and use it instead?

Host Guest_nick_at_ms:
A: Triggers are materialized from the transaction log, so if there is a huge amount of changes going on, then it can be expensive to scan the log. In SS2K, we added support for cascading actions, one of the main things people use triggers for. Please ask a more detailed question if this doesn't answer your question. Every release will be better than the last!

Host Guide_KenM:
We are going to have to wrap up this chat in about 10 minutes. We will answer some last questions though.

Brad:
Q: Lubor, In reference to my previous question on date as char, in SQL 6.5 we have an index on such a date. What does SQL actually do if I specify the char index but convert the date to a date data type? Does it scan the entire index?

Host Guest_Lubor:
A: Yes, the conversion does not allow to use index seek (if I understand the question correctly).

Q: ..are view defs retrieved every time ...

Host Guest_ms_nigel:
A: No, the view definitions are loaded once and cached until a schema change occurs. Look at "select * from master.dbo.syscacheobjects.

Riley:
Q: So would I first look at using something like and Index Tuning Wizard? Can you use this on temporary tables?

Host Guest_Lubor:
A: Query can be tuned only if all the object exist before the query is tuned. So if your batch is creating table and has a query referencing it, the batch cannot be tuned as a single entity.

Host Guide_KenM:
We are going to have to wrap up this chat in a few minutes. We are working on the answers to the last few questions.

mrMichael:
Q: Most of the triggers we are trying to rewrite are implementing some kind of business logic, not necessarily replaceable by cascade. So if I SELECT * INTO @MyTable FROM inserted and use @MyTable in all the queries, would it would be better than to use inserted?

Host Guest_nick_at_ms:
A: It depends:-). Let me give more background on triggers. In SS2K, the new "instead-of" triggers don't read from the log. The classic "after triggers" do use the log. So if you are using an instead-of trigger, no need to worry about this. So let's assume you are using an "after trigger" (which is what 7.0 and before has). If you have lots of changes going on, it could be expensive to read the log repeatedly. So yes, it might improve performance. If you don't have much going on (changes), then it won't matter.

Brad:
Q: Will (or is it) it be possible to create an index and have the fields contained in the index converted in some way first? i.e. char to int.

Host Guest_Lubor:
A: You have to introduce a computed column, and then index it.

Host Guide_KenM:
We can follow up offline on any remaining issues.

Host Guide_KenM:
Thanks for joining us today! You've asked some great questions. Unfortunately, it's time to go.

Host Guest_MS_jamMo:
If anyone is interested in joining the beta program for SQL Server, please send mail with your request to betareq@microsoft.com.

Host Guest_MS_jamMo:
Thanks for the great session!