Below are answers to questions asked during the recent webinar. If you have additional questions, please contact Thomas LaRock at thomas.larock@solarwinds.com
Q: Do statistics get refreshed frequently or is it the stats of the last ten execs?
A: It depends on the size of the table, but for tables with more than 500 rows, and that have auto update statistics enabled, the statistics are updated when the colmodcntr of the leading column in the statistics objects has changed by more than 20% plus 500 of the the number of rows in the table. You can read more about statistics at: http://msdn.microsoft.com/en-us/library/ms190397.aspx
Q: Would you test disabling all hints or hints included?
A: Great question! I had not thought about the use of query hints. I'd advise you to test the query with and without hints, and using both the legacy and new cardinality.
Q: What about the statistics DMV?
A: There is a new DMO available in SQL 2008R2 to help you understand the properties of statistics, sys.dm_db_stats_properties. You can find more info at: http://msdn.microsoft.com/en-us/library/jj553546.aspx
Q: So using GUIDs as PKs may not perform as well with the new CE?
A: The issue with GUIDs remains, your concern there is fragmentation. The new CE will not address that issue.
Q: Is the new 2014 model using the data sampling and taking 10% data instead of 100%? How has the performance increased? And, how is SQL 2014 taking fewer rows?
A: SQL Server does not use 100% sampling by default. The sample size is dynamically determined by the database engine at the time statistics are created or updated. I'm not sure I understand your last two questions, but performance of queries are increased if the optimizer builds a plan around the best possible statistical estimates.
Q: Is there any logic involved?
A: I don't fully understand this question, but yes there is logic involved in the cardinality estimator.
Q: There is some confusion on what the querytraceon 9481 does when running SQL 2014 where databases are in compatibility 100…?
A: The 9481 hint will revert to legacy CE behavior. Since your database is set to compatability mode 100, using 9481 will have no effect.
Q: Where did you get AdventureWorks2014 from? I could not find sample databases for SQL 2014.
A: Yes, they are available at: https://msftdbprodsamples.codeplex.com/releases/view/125550
Q: I've upgraded my instance to 2014 but all my DBs are in Compatability 100 (2008). We want to test some queries against the new CE without upgrading the entire DB Compatability... is this possible ... if so, how?
A: You simply need to use the following hint: OPTION (querytraceon 2312), which will allow you to use the new CE for that query.
Q: Link to Joe's whitepaper:
A: http://tinyurl.com/joe-sack-cardinality
Q: Does any of this apply to the ColumnStore indexes?
A: Yes! The new CE will also affect ColumnStore indexes.
Q: Would you say that the new CE would be especially benifical for a a table with 1 million rows as the 20%+500 rule would rarly be triggered?
A: For a large table such as that you will want to look at enabling trace flag 2371. You can read more about that at:http://blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371.aspx
Q: Is SolarWinds Database Performance Analyzer included in the SolarWinds package?
A: No, DPA is not currently a part of the Orion platform.
Q: Is there an ETA for SP1 for 2014?
A: I have no knowledge of Microsoft release cycles, but I'd suspect it could be a while before you see SP1 for SQL 2014. There is CU1 for SQL 2014 that you can find here: http://support.microsoft.com/kb/2931693
