Below are answers to questions asked during the recent webinar. If you have additional questions, please contact Thomas LaRock at thomaslarock@confio.com or Tim Chapman at timchap@microsoft.com
Q: I have a lot of vendor databases that are using query hinting, making it somewhat difficult for the query optimizer to do its job. Not too much I can do about that, I believe.
A: Sorry to hear that. You are correct in that if you can't touch code, your options for improving performance are limited. We see this often in vendor code. We wish we didn't.
Q: Can I use BETWEEN instead of >= <?
A: Logically they are not the same. Here is the SDN article on BETWEEN: http://technet.microsoft.com/en-us/library/ms187922.aspx.
Q: Will the following also for a scan? WHERE CONVERT(DATE, MODIFIEDDATE) = CONVERT(DATE,
A: If you're on 2008 then there is a good chance that SQL will use the index because of the relationship between DATE and DATETIME.
Q: Are there any Configurations for Performance Optimization in MSSQL 2008R2?
A: It depends upon your workload, we can't tell you what to do without knowing more. I'd encourage you to try the free trial of DPA. That way we can have a look at your workload and make suggestions.
Q: 2nd example use >= and <. How did it work because modifiedate field is datetime that we are comparing it to date only(no time).
A: When time is not involved, SQL Server assumes midnight on the date.
Q: Why not mention the difference between Profiler for events like SQL:BatchCompleted and RPC:Completed gives more accurate answers than SET STATISTICS IO ON and the query plan cost estimates?
A: The only real difference between the two is when you are examining values returned from user-defined functions, as we showed in the video.
Q: When I am using a same query block as a sub query a number of times in a single query will the operation to determine the results of subquery would happen 3 times or 1 only
A: It would happen three times.
Q: Would changing the SELECT 1 to SELECT TOP(1) 1 make any difference if there were many records with ProductId = 870?
A: SELECT 1 returns a numeric value for every record returned, but TOP(1) must insert a sort operation to return a single record
Q: Do we have multithreading for SQL Procedures?
A: Yes, a stored procedure can be executed in parallel across CPUs.
Q: Will Extended events work also?
A: Yes, you can use them instead of SQL Trace.
Q: The higher logical read count is better?
A: No, you want to reduce the number of logical reads.
Q: How significant is the Scan Count and Logical Reads in WorkTables shown in STATISTICS IO ON
A: Same as regular tables; you want as few logical IO as possible.
Q: Are there any Configurations for Performance Optimization in MSSQL 2008R2?
A: It depends upon your workload, we can't tell you what to do without knowing more. I'd encourage you to try the free trial of Database Performance Analyzer (DPA). That way we can have a look at your workload and make suggestions.
Q: How do I reduce the table lock in SQL while performing SQL Queries?
A: There are a handful of options, but you need to know more about why the locks are happening. An easy way to do this is to download the free trial version of DPA.
Q: What would be the impact of using NOLOCK there in that update statement?
A: NOLOCK would be able to read uncommitted data, but it would not be able to update it since it would be locked.
Q: Generally which is better ... Table 'Worktable'. Scan count 740, logical reads 31150 or Table 'Worktable'. Scan count 80, logical reads 1131150?
A: The fewer the logical reads, the better.
Q: What is the performance difference between CONVERT and CAST? What is the difference between the two? Which is best?
A: The different is minimal. The only time it matters to use one or the other has to do with formatting the output.
Q: What situation Index Scan is faster than index seek?
A: When bookmark lookups are involved, then a scan can be many, many times faster than a seek.
Q: If a stored proc has different logic separated by IFs...How does SQL handle the exec plan ?
A: It will be stored in one execution plan. The IF statement is not a batch seperator by default.
Q: Is there any way to see the execution plan if you do not have access to the 'normal' Execution Plan functionality, i.e., as a Contractor
A: Plans can be seen either by mining the plan cache or by displaying the execution plan within SSMS when running the query. You can also highlight the query and look at the estimated plan.
Q: Have you done any research regarding SQL statement bad code detection and transform to better SQL statement and run the better SQL statement if possible. thanks manian csb
A: You can use sys.dm_exec_query_stats to see if you have made performance improvements.
Q: In your examples on Table lock - where you improved the code by only updating 500 records in a loop. At what point will it not work? I.e. SQL Server will upgrade to a Table Lock. Is there a calculation to work this out to avoid Table locks even in a while loop update?
A: SQL will esclalate to a table lock when the lock threshold has beeen reached (5000 locks), and that threshold is configurable.
Q: Can you talk about performance when it comes to CTEs?
A: Sometimes they perform better, but you need to test.
Q: Are there any Configurations for Performance Optimization in MSSQL 2008R2?
A: It depends upon your workload, we can't tell you what to do without knowing more. I'd encourage you to try the free trial of DPA. That way we can have a look at your workload and make suggestions.