SQL Pass Summit 2014 – Day 2

Last nights networking dinner got move from one place to another and we decided to skip it, there was just to many in line and we were to hungry, so we skipped it and went for Hard Rock Cafe instead.

Second day precon is “Better performance through parallelism” with Adam Machanic.

Adam starting of with a history lesson on CPU development and Moores law.

Talks about linear and extra linear operations. In theory only table scan is linear, all other operations have overhead in form of algorithm complexity.

example:

Table Scan: O(N)

Sort: O(N * log(N))

Index Seek: O(logN)), O(M*log(n))

Deep dive into Threads,Preemptive scheduling, Quantum, clock interrupt frequency and affinity.  Don’t run Adobe flash on a server since it will change quantum units for the whole system to get smooth streaming of video. Can be checked with clockres.exe by sysinternals. http://technet.microsoft.com/en-us/sysinternals/bb897568.aspx

Always use Hyperthreading on all newer servers. Makes it possible to get second path into CPU. A 100% utilized CPU may only be using parts of the built in functionality. Hyperthreading makes it possible for other threads to use the available resources.

Numa testing by Linchi Shea shows that there is basically no performance impact if you have to use remote NUMA nodes on newer CPU’s.

http://sqlblog.com/blogs/linchi_shea/archive/2012/01/30/performance-impact-the-cost-of-numa-remote-memory-access.aspx

Performance saving mode on a server is good unless CPU load is above 70-80%, then the switching of C-state modes will incur too much overhead.

In SQL 2014 minimal logged SELECT INTO is finally available. Could make ETL jobs using SELECT INTO much faster. No code changes needed, just works out of the box.

Server level MAXDOP setting can be overridden by OPTION (MAXDOP). Resource Governor can be used to prevent increasing MAXDOP with OPTION feature in SQL code.

Max Server memory setting affects the barrier for parallel plan generation. Buy more RAM !

Affinity mask could be interesting when you run SQL and SSIS and/or SSAS or any other application on the same server. Traceflag 8002 should be used in that case to prevent hard affinity lock within affinity groups.

Parallel cost threshold of 5 is way to low, start with 40-50 and work from there.

Max Degree of parallelism default value of 0 site worst default value in SQL Server. On OLTP system 2 is a recommendation to start with. For OLAP number of physical number of cores * 1,5, or with hyper threading logical number of cores * 0.5

Never touch Max Worker Threads without consulting Microsoft support. Or if your server has stopped responding.

Functions inhibits parallel operations, functions can then be replaced with CLR’s to get around that.

Traceflag 8649 will always give a parallel plan if one is available even if the cost is higher. Great for troubleshooting query plans.

Use the sp_whoisactive with @get_task_info=2 to get better wait info. sp_whoisactive is available at http://sqlblog.com/files/folders/beta/entry42453.aspx

very interesting session with Adam, deep dive in parallelism, what affects it and what can be done to improve the use of parallel plans. Difficult to get into details about query optimization in writing, just lets say I have learned some new ways to approach tuning.

Now it’s time for the Welcome reception, will be nice. Still Jetlagged so it will be an early night today too.

Advertisements
This entry was posted in SQLServer. Bookmark the permalink.

One Response to SQL Pass Summit 2014 – Day 2

  1. Pingback: News About PASS For Week Ending November 8, 2014 | PASSWatch

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s