Recently I gave a presentation on SQL Server transaction performance diagnostics and improvement on Microsoft organized TechDay events in Lithuania and Latvia.
The time for the talk was very limited and topic – really big and complex. So I have decided to briefly cover some aspects like blocking & deadlocks, query plans and finally some tools for diagnostics, including my new favorite - extended events (xEvents).
For those who want to know more about the topics I’ve talked, you will have to dig deeper on your own, but hopefully you have some idea.
Here are couple of good points where to start:
- Information on xEvents on MSDN: http://msdn.microsoft.com/en-us/library/bb630282.aspx (didn’t I mentioned that xEvents is my new favorite feature)
- Blogs of Paul and Kimberly Randal from SQLSkills: http://www.sqlskills.com/BLOGS/Paul/ and http://www.sqlskills.com/BLOGS/KIMBERLY/ – there are so many goodies. You should really keep these on your watch list. In particular you might be interested about xEvents http://www.sqlskills.com/blogs/paul/category/Extended-Events.aspx and index usage http://www.sqlskills.com/BLOGS/KIMBERLY/category/The-Tipping-Point.aspx
- SQL Server central is one of the places, where you can find a lot of various goodies on SQL Server like this one: http://www.sqlservercentral.com/articles/deadlock/65658/
- Itzik Ben-Gan is of course famous figure in the SQL Server world, so you shouldn’t miss articles like this one http://www.sqlmag.com/article/tsql/understanding-query-plans-141850
- Great query samples from Pinal Dave like this one http://blog.sqlauthority.com/2010/05/14/sql-server-find-most-expensive-queries-using-dmv/
- Craig Freedman’s blog contains a lot of interesting content
There are many more sources, but definitely you can start with those links and Google Bing further absorbing all the good information along the way.
Of course, I have posted slides and scripts of my presentation here. And if you will go through these links, or at least take a look to extended events and system_health session – then my goal was achieved.
And make sure you know your data and pass this knowledge to your SQL Server in form of good physical design, indexes, statistics and appropriate queries.