Second day pre-conference at SQL Pass Summit for me was a session with Brent Ozar, Kendra Little and Jes Schultz Borland called “Make your SQL Server Apps go faster”
Here is some of the things that we learned today.
Key focus in the presentation was to see what can be done to make applications run faster from a DBA point of view. What can I as a DBA do without involving a developer or changing any of the application code.
First we need to know what is our worst performing queries, there are a number of ways to find that out. Activity Monster Monitor is NOT the best tools for this, beacuse it’s not telling the truth all the time.
sp_whoisactive is a free stored procedure that can be downloaded and installed. It can be downloaded from here http://sqlblog.com/files/folders/beta/entry42453.aspx. Brent Ozar also has a quick guide here http://www.brentozar.com/archive/2010/09/sql-server-dba-scripts-how-to-find-slow-sql-server-queries/.
Kendra Little aslo gave a quick guiding to Extended Events and how that can be used to give us information of our top queries, more about that in my next post after I have attended the “Making the Leap from Profiler to Extended Events” tomorrow.
We are also interested in what kind of waits we have in our server, this together with the information we get about the heaviest queries will help us determine what choices we have to tune.
SQL server have some DMV’s that can be queried to find missing indexes, there is also the Database Tuning Advisor that is commonly used by developers to create indexes on tables. Netiher of these are very good tools, just implementing recommendation from theses tools might give you worse performance in the long run. Recommendations should really been seen as just that. It is vital to know how your tables are accessed and queried to be able to build the correct indexes.
Correct indexing can not only solve direct IO problems but can in some cases also help to solve fixed implicit conversions.
Sp_blitzindex is a great tool to figure out your index needs. It available here http://brentozar.com/go/blitzindex. It will also help you find your duplicates.
It’s also important to know that some simple queries are executed as trivial and will not generate a index recommendation. If they are run frequently it might be worth while to look at it anyway. Find them with sp_whoisactive.
Remember that in SQL 2012 index recommendations are flushed when a index rebuild takes place, this was not the case in earlier versions. Index defragmentation does not flush the recommendations, this can make it tricky to maintain indexes in SQL 2012.
Index maintenance should be carried out every 4-6 weeks, this is because usage pattern and data volumes changes a lot over time.
CX_PACKET is a wait stat that is very common in SQL Server and often it is thought of as a problem. A common reason why this can become a problem is the “parallel threshold” value in the SQL Server configuration. Default value for that has been 5 since a very long time. In todays high performance servers it is way to low. A starting value of 50 is now days the recommendation. MAXDOP is also taking part here and that should not be left at default value of 0. Less or equal to number of cores on server is a good starting point. Usually no benefit over 16.
Always try to avoid table variables unless your 100% sure they will ever return more then one row. http://brentozar.com/go/serialudf describes this more in detail.
These where just some takeaways from todays pre-con. It will be interesting to get home and practice this on real data…..