Want to be mentored by me?

Every year Redgate hands out a series of Tribal Awards, voted for by the SQL Server community around the world. In the 2013 awards I won the “Most Authoritative Voice” category (I think just because I’m very tall and have a deep voice :-) and in the 2014 awards, announced a few weeks ago, I won the “Person You’d Most Like to be Mentored By” category.

Cool! I like to mentor people and that’s one of the most enjoyable parts of managing teams, both when I was at Microsoft, and my team here at SQLskills.

Given that you all voted for me, I’m going to give you the opportunity to be mentored by me. I’ll mentor up to three men and three women during March and April to help with career guidance, skills growth, or whatever I can help you with (disclaimers: I do all mentoring over email – asynchronicity rules! I reserve the right to change the mix of mentees if I’m short on applicants.).

How do you qualify? Write a blog post explaining why you should be mentored, and then post a link to your post as a comment on this post (it won’t appear right away as comments are moderated due to the amount of spam comments).

Don’t make the post about me, make it about you. I already know about me :-)

You’ve got until Sunday 15th February at 23:59:59 PST. I’ll pick the winners based on my sole judgment and announce them on Monday 16th February. I don’t care who you are, where you are, who you work for, or how good your English is; you have an equal chance of being picked as anyone else. It’s all down to the blog post.

Note: the contest is over, please do not submit any further entries.

Have fun!

PS So far people are focusing on what they know about SQL Server. I’m not going to teach you what I know about SQL Server in two months – that’s what my blog and training materials are for – but I could help you figure out *how* to learn more, if that’s really the most important thing in your career that you need help with. A blog post about what you do and have done with SQL Server tells me nothing about you or what you want to get out of being mentored….

PPS Remember that mentoring means you working to figure out what you need help with first of all – if you can’t tell me what you need help with (and I’ll provide a whole bunch of suggestions) then there’s nothing I can do…

PPPS Mentoring (as far as I’m concerned) isn’t about firing off a bunch of questions about day-to-day SQL Server problems you’re having – that’s not what I’ll help you with…

Important change to VLF creation algorithm in SQL Server 2014

Since SQL server 2014 was released back in April last year, there have been some rumblings about changes to how many VLFs are created when the log is grown or auto-grown (I’ll just say auto-grown from now on, as that’s the most common scenario). I experimented a bit and thought I’d figured out the algorithm change. Turns out I hadn’t. There was a question on the MVP distribution list last week that rekindled the discussion and we collectively figured out that the algorithm was behaving non-deterministically… in other words we didn’t know what it was doing. So I pinged my friends in CSS who investigated the code (thanks Bob Ward and Suresh Kandoth!) and explained the change.

The change is pretty profound, and is aimed at preventing lots of auto-growth from creating huge numbers of VLFs. This is cool because having too many (it depends on the log size, but many thousands is too many) VLFs can cause all manner of performance problems around backups, restores, log clearing, replication, crash recovery, rollbacks, and even regular DML operations.

Up to 2014, the algorithm for how many VLFs you get when you create, grow, or auto-grow the log is based on the size in question:

  • Less than 1 MB, complicated, ignore this case.
  • Up to 64 MB: 4 new VLFs, each roughly 1/4 the size of the growth
  • 64 MB to 1 GB: 8 new VLFs, each roughly 1/8 the size of the growth
  • More than 1 GB: 16 new VLFs, each roughly 1/16 the size of the growth

So if you created your log at 1 GB and it auto-grew in chunks of 512 MB to 200 GB, you’d have 8 + ((200 – 1) x 2 x 8) = 3192 VLFs. (8 VLFs from the initial creation, then 200 – 1 = 199 GB of growth at 512 MB per auto-grow = 398 auto-growths, each producing 8 VLFs.)

For SQL Server 2014, the algorithm is now:

  • Is the growth size less than 1/8 the size of the current log size?
  • Yes: create 1 new VLF equal to the growth size
  • No: use the formula above

So on SQL Server 2014, if you created your log at 1GB and it auto-grow in chunks of 512 MB to 200 GB, you’d have:

  • 8 VLFs from the initial log creation
  • All growths up to the log being 4.5 GB would use the formula, so growths at 1, 1.5, 2, 2.5, 3, 3.5, 4, 4.5 GB would each add 8 VLFs = 56 VLFs
  • All growths over 4.5 GB will only create 1 VLF per growth = (200 – 4.5) x 2 = 391 VLFs
  • Total = 391 + 56 + 8 = 455 VLFs

455 is a much more reasonable number of VLFs than 3192, and will be far less of a performance problem.

A commenter asked whether compatibility level affects this? No – compatibility level is ignored by the Storage Engine internals.

I think this is an excellent change and I can’t see any drawbacks from it (apart from that it wasn’t publicized when SQL Server 2014 was released). CSS will be doing a comprehensive blog post about this soon, but they were cool with me making people aware of the details of the change ASAP to prevent confusion.

You might think that it could lead to very large VLFs (e.g. you set a 4 GB auto-growth size with a 100 GB log), and it can. But so what? Having very large VLFs is only a problem if they’re created initially and then you try to shrink the log down. At a minimum you can only have two VLFs in the log, so you’d be stuck with two giant VLFs at the start of the log and then smaller ones after you’d grown the log again. That can be a problem that prevents the log being able to wrap around and avoid auto-growth, but that’s not anywhere near as common as having too many VLFs. And that’s NOT a scenario that the new algorithm creates. (As an aside, you can fix that problem by creating a database snapshot and then reverting to it, which deletes the log and creates a 0.5 MB log with two tiny VLFs… it’s a bugfeature that’s been there since 2005, but it breaks your log backup chain when you do it.)

There’s certainly more that can be done in future around VLF management (e.g. fixing the problem I describe immediately above), but this is a giant step in the right direction.

Enjoy!

Updated sys.dm_os_waiting_tasks script

Over the holidays I was playing around with parallelism and updated my sys.dm_os_waiting_tasks script to add in the scheduler distribution of the waiting threads. Here it is for your use.

Enjoy!

(Note that ‘text’ on one line does not have delimiters because that messes up the code formatting plugin):

/*============================================================================
  File:     WaitingTasks.sql

  Summary:  Snapshot of waiting tasks

  SQL Server Versions: 2005 onwards
------------------------------------------------------------------------------
  Written by Paul S. Randal, SQLskills.com

  (c) 2015, SQLskills.com. All rights reserved.

  For more scripts and sample code, check out 
    http://www.SQLskills.com

  You may alter this code for your own *non-commercial* purposes. You may
  republish altered code as long as you include this copyright and give due
  credit, but you must obtain prior permission before blogging this code.
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/
SELECT
    [owt].[session_id],
    [owt].[exec_context_id],
    [ot].[scheduler_id],
    [owt].[wait_duration_ms],
    [owt].[wait_type],
    [owt].[blocking_session_id],
    [owt].[resource_description],
    CASE [owt].[wait_type]
        WHEN N'CXPACKET' THEN
            RIGHT ([owt].[resource_description],
                CHARINDEX (N'=', REVERSE ([owt].[resource_description])) - 1)
        ELSE NULL
    END AS [Node ID],
    --[es].[program_name],
    [est].text,
    [er].[database_id],
    [eqp].[query_plan],
    [er].[cpu_time]
FROM sys.dm_os_waiting_tasks [owt]
INNER JOIN sys.dm_os_tasks [ot] ON
    [owt].[waiting_task_address] = [ot].[task_address]
INNER JOIN sys.dm_exec_sessions [es] ON
    [owt].[session_id] = [es].[session_id]
INNER JOIN sys.dm_exec_requests [er] ON
    [es].[session_id] = [er].[session_id]
OUTER APPLY sys.dm_exec_sql_text ([er].[sql_handle]) [est]
OUTER APPLY sys.dm_exec_query_plan ([er].[plan_handle]) [eqp]
WHERE
    [es].[is_user_process] = 1
ORDER BY
    [owt].[session_id],
    [owt].[exec_context_id];
GO

2014 review: the year by the numbers

The last post of the year! It’s been a really excellent year all round and time for my traditional post counting down some of the numbers that have been my life this year.

  • 10068445 (roughly): the number of page views across my blog according to Google (up around 9% from 2013)
  • 105881: the number of miles I flew on United
  • 31968: my current tweet total (up 2308 from 2013)
  • 12267: the number of emails I sent (up 207 from 2013)
  • 11621: the number of subscribers to our Insider mailing list (up 1331 from 2013)
  • 9395: the number of people who follow my Twitter ramblings (up 1714 from 2013)
  • 1474: the number of books (real ones) that I own (up 144 from 2013)
  • 812: the number of books I own but haven’t read yet (up 105 from 2013)
  • 185: the number of nights away from home (all with Kimberly, so not *too* bad)
  • 122: the total number of hours of online training we have available on Pluralsight
  • 103: the number of feet down on my deepest dive this year (taking both girls down to 100 feet for the first time)
  • 93: the number of dives I did this year in Kona and Indonesia, taking my total to 400
  • 81: the number of minutes of my longest dive this year
  • 80: the number of days in Immersion Events and conferences
  • 56: the number of SQLskills blog posts, including this one
  • 53: the number of books I read (see this post)
  • 50.68: the percentage of time we were away from home (which is why we call it our vacation home!)
  • 45: the number of flights this year
  • 38: the number of Pluralsight courses we have available
  • 20: the number of different places we slept apart from our house and on planes
  • 20: the number of new bird species I saw, taking my total to 484
  • 20: the number of airports I flew through this year
  • 15: the number of monthly magazines I subscribe to
  • 7: the number of years I’ve been married to Kimberly
  • 6: the number of  SQLskills full-time employees, all of whom are fabulous and indispensable (soon to be 7, with Tim Radney joining in January)
  • 5: the number of countries we visited this year
  • 2: the number of new airports I flew through, taking my total to 82
  • 2: the number of times we visited Australia this year, for four weeks in total
  • 2: the number of awesome daughters we have, who both qualified as Rescue Divers this year, one level below Master Diver
  • 2: the number of me and Kimberly who qualified as PADI Master Divers this year – the highest non-professional dive qualification
  • Finally, the one and only best person in my life: Kimberly, without whom I would be lost…

Thank you to everyone who reads our blogs, follows us on Twitter, sends us questions, watches our videos, comes to our classes, and generally makes being deeply involved in the SQL community a joy.

I sincerely wish you all a happy, healthy, and prosperous New Year!

Cheers!

(Giving a chin tickle to an old wallaroo in December at a wildlife sanctuary a few hundred kilometers from Sydney)

10850298_10205310703509099_8622221409002578133_n

 

(On top of the Sydney Harbor Bridge in March)

1956929_10152329459415452_551473595_o

2014: the year in books

Back in 2009 I started posting a summary at the end of the year of what I read during the year (see my posts from 200920102011, 2012, 2013) and people have been enjoying it, so I present the 2014 end-of-year post. I set a moderate goal of 40 books this year and I managed 53. I wanted to get through some of my larger non-fiction books but ended up not doing as many as I thought (reading more, shorter books), and it’s just coincidence that my total works out to about a book per week. Next year I’m setting myself a goal of reading 60 books.

For the record, I read ‘real’ books – i.e. not in electronic form – I don’t like reading off a screen. Yes, I’ve seen electronic readers – we both have iPads – and I’m not interested in ever reading electronically. I also don’t ‘speed read’ – I read quickly and make lots of time for reading.

Why do I track metrics? Because I like doing it, and being able to compare against previous years. Some people don’t understand the logic in that – each to their own :-)

There were several strong candidates for my favorite book this year (including Command and Control by Eric Schlosser and Reamde by Neal Stephenson) but I’m choosing Natchez Burning by Greg Iles. It’s a fantastic and shocking drama that I knew as I read it that it was the best book I’d read this year. You can read my review of it in the top-10 list below.

Now the details. I enjoy putting this together as it will also serve as a record for me many years from now. I hope you get inspired to try some of these books – push yourself with new authors and very often you’ll be surprisingly pleased.

Once again I leave you with a quote that describes a big part of my psychological make-up:

In omnibus requiem quaesivi, et nusquam inveni nisi in angulo cum libro!

Analysis of What I Read

I read 28407 pages, or 77.8 pages a day, and a book every 6.9 days or so. The chart below shows the number of pages (y-axis) in each book I read (x-axis).

lengths

 

genres

The average book length was 536 pages, almost 100 pages longer than last year. Quite interestingly, I dropped travel and history this year in favor of non-fiction books, which was my goal. Fiction and historical fiction were the same as last year, and sci-fi was almost double.

The Top-10

I read a lot of really good books this year, but I didn’t struggle to get to a top-10 as I had in previous years. If you don’t read much, at least consider looking at some of these in 2015. It’s impossible to put them into a priority order so I’ve listed them in the order I read them, along with the short Facebook review I wrote at the time.

book1 #7 Command and Control; Eric Schlosser; 640pp; Non-Fiction; February 16; (Absolutely excellent history of US nuclear weapon control (or lack of it), accidents involving US nuclear weapons, and US nuclear strategy from the 40s to the 80s. Really shocking how close the US came to an accidental nuclear detonation several times. it also covers the Damascus accident in great detail, along with the almost laughable ineptitude of the Air Force and SAC in dealing with it. There are very comprehensive notes and sources, which actually led me to buy another 15 or so books over the last two weeks on related topics. Highly recommended – excellent book.)

book2 #9 Line War; Neal Asher; 566pp; Science Fiction; February 27; (This is an excellent finale to the Agent Cormac series, nicely wrapping up all the plot lines and leaving things set for continuations with some of the characters in other books. Hard to say anything else about the book without giving away major spoilers, but plenty of good battles and AI intrigue. Looking forward to reading The Gabble and Technician by him next. Strongly recommended (reading the Agent Cormac series in order).)

book3 #11 The Martian; Andy Weir; 369pp; Science Fiction; March 7; (Really excellent modern-day Robinson Crusoe story about an astronaut accidentally stranded on Mars after a hurried mission abort during a major dust storm. Very believable and no credibility stretching – a brilliant debut from Weir. I cruised through this in about 8 hours. Highly recommended and I’m really looking forward to Weir’s second book.)

book4 #14 The Dead Hand: The Untold Story of the Cold War Arms Race and Its Dangerous Legacy; David Hoffman; 608pp; Non-Fiction; March 23; (Really excellent book I picked up while reading Command and Control earlier this year. This book details the hidden Soviet bioweapons programs that existed all the way up to the 90s (anthrax, plague, tularemia, and even smallpox, after it had been eradicate by WHO, for goodness sake!) during the Cold War. The Soviets lied to the world, and Gorbachev and Yeltsin continued to lie after the fall of the USSR in ’91. It also explains the ‘dead hand’ semi-automatic mechanism to allow retaliation by the Soviets after a decapitative nuclear strike against them. Most alarming is the telling of how the a Soviet Union fell apart and left thousands of tons of bioweapons, enriched uranium and plutonium, and nuclear weapons spread across the various republics and Russia without adequate storage, security, and safeguards to stop profiteering and proliferation to rogue states like Iran. Interesting to read about how some parts of the US govt stepped in to buy uranium from Kazakhstan to stop it falling into the wrong hands, and built secure storage for Russia. Well worth reading. Not an alarmist, sensational book at all, but an insightful and level-headed description of what went on. Contains a lot about the transfer of power to Gorbachev, his meetings with Reagan, and his fall from power that I hadn’t read about before either. Strongly recommended.)

book5 #17 The Sunne in Splendour: A Novel of Richard III; 944pp; Historical Fiction; April 26; (Sharon Kay Penman’s novels are excellent historical fiction, meticulously based on actual history. In this book she follows the life of Richard III, steeped in betrayal and treason against both himself and his brother, the earlier Edward IV (with Edward V only reigning for a few months until pronounced illegitimate). In more than 900 pages of small-font, the book is huge and a long, engrossing read. Penman puts the death of the infamous ‘princes in the Tower’ at the hands of the Duke of Buckingham, which makes a lot of sense given that neither Richard nor Henry Tudor (later Henry VII) had both motivation *and* opportunity to do the deed. Altogether a splendid book, and I strongly recommend it for all fans of history.)

book6 #19 The Quarry; Iain Banks; 326pp; Fiction; May 10; (This is the last book that my favorite author of all time, Iain Banks, wrote before he died last year. It’s quite excellent. The dying character is irascible, bitter, and filled with justifiable invective against elements of the world and I have to imagine this is semi-autobiographical. It’s a great story and a fitting final novel. I think I’ll have to go back and re-read Banks’ non-sci-fi books (already read the sci-fi ones several times each). Thanks Iain – RIP.)

book7 #21 Duty: Memoirs of a Secretary at War; Robert M. Gates; 617pp; Non-Fiction; May 25; (This memoir describes Gates’ time as the Secretary of Defense under Presidents Bush (43) and Obama. Gates comes across as someone who really cares about the service men and women under his command (as second in the chain under the president).This is reiterated many times through the book when he describes budget battles to get more equipment, better equipment, the right equipment, unwanted/unneeded equipment cancelled, and more against parochial members of Congress and inexperienced White House staffers. He seems to have been a very, very different SecDef than his predecessor Rumsfeld, whose autobiography I now look back on with a much more negative view. Different how? Nicer is probably the easiest way to say it, just a nicer, more caring person. He looks back several times and wishes he’d handled a situation better and done things differently for the benefit of the troops. He takes personal responsibility for everything that happened on his watch, rather than blaming others (a failing of Rumsfeld.) The book also gives a lot of insight into the workings of the Department of Defense, the largest organization on the planet, with 3 million ‘staff’, and a budget of more than $700 billion in 2011. Altogether a very interesting book and well worth reading. I’m looking forward to reading the memoirs of the presidents and other cabinet members from the last 10-15 years too. Highly recommended.)

book8 #34 Reamde: A Novel; Neal Stephenson; 1042pp; Fiction; August 18; (Absolutely excellent, and a monster 1000+ pages. Terrorists, MMORPG, Chinese and Hungarian hackers, MI6, Idahoan survivalists, private jets, Russian mercenaries, lots of guns. Mix well with superlative storytelling and you get Stephenson’s usual top-notch page turner. Best book so far this year. Very, very strongly recommended.)

book9 #40 D-Day: The Battle For Normandy; Antony Beevor; 608pp; History; September 10; (Absolutely excellent narrative of the D-Day landings and the following several months of fighting up to the liberation of Paris. What seems obvious is that there was incompetence on both sides, especially around Field Marshall Montgomery on the Allied side and Hitler’s incessant micro-management of the German side, that led to greater losses or troops, civilian French, and widespread destruction in Normandy. I’m not a big war-history buff (yet!) but I found this a thoroughly engrossing book rather than just a dry battle chronology. I really liked that way Beevor could conjure up little vignettes of action to highlight interesting points in the wider battlefield. Highly recommended!)

book10 #50 Natchez Burning; Greg Iles; 800pp; Fiction; December 21; (This is easily the best book I’ve read this year, and is fantastic. It’s long, really involved, and deals with a murder case on the Mississippi/Louisiana border. Apparently it’s the fourth in a series about the main character – so I just bought the first three to add to my pile of unread books. The book is centered on the extreme racism in that area in the late 1960s and its continuation in some cases today, and is quite shocking in parts. The writing is superb, with rich characters and excellent dialog. I’m very glad I took a chance on this after seeing it reviewed in the W.S.Journal. Strongly recommended, but definitely not for the faint-hearted.)

The Complete List

And the complete list, with links to Amazon so you can explore further. One thing to bear in mind, the dates I finished reading the book don’t mean that I started, for instance, book #2 after finishing book #1. I usually have anywhere from 10-15 books on the go at any one time so I can dip into whatever my mood is for that day. Some books I read start to finish without picking up another one and some books take me over a year. Lots of long airplane flights help too!

  1. Travels in a Thin Country: A Journey Through Chile; Sara Wheeler; 336pp; Travel; January 3
  2. The Hundred Secret Senses; Amy Tan; 358pp; Fiction; January 7
  3. Zima Blue; Alastair Reynolds; 455pp; Science Fiction; January 11
  4. The Paleo Manifesto; John Durant; 300pp; Non-Fiction; January 15
  5. The Astronomer; Lawrence Goldstone; 304pp; Historical Fiction; January 19
  6. Blink: The Power of Thinking Without Thinking; Malcolm Gladwell; 266pp; Non-Fiction; January 27
  7. Command and Control; Eric Schlosser; 640pp; Non-Fiction; February 16
  8. Service: A Navy SEAL at War; Marcus Luttrell360pp; Non-Fiction; February 21
  9. Line War; Neal Asher; 566pp; Science Fiction; February 27
  10. Known and Unknown: A Memoir; Donald Rumsfeld; 832pp; Autobiography; March 3
  11. The Martian; Andy Weir; 369pp; Science Fiction; March 7
  12. Leviathan Wakes; James S. A. Corey; 592pp; Science Fiction; March 17
  13. The Sixth Extinction: An Unnatural History; Elizabeth Kolbert; 319pp; Non-Fiction; March 19
  14. The Dead Hand: The Untold Story of the Cold War Arms Race and Its Dangerous Legacy; David Hoffman; 608pp; Non-Fiction; March 23
  15. Caliban’s War; James S. A. Corey; 624pp; Science Fiction; April 2
  16. 2312; Kim Stanley Robinson; 576pp; Science Fiction; April 11
  17. The Sunne in Splendour: A Novel of Richard III; Sharon Kay Penman; 944pp; Historical Fiction; April 26
  18. A Game of Thrones: A Song of Ice and Fire: Book One; George R. R. Martin; 704pp; Fiction; May 8
  19. The Quarry; Iain Banks; 326pp; Fiction; May 10
  20. The Casual Vacancy; J.K. Rowling; 512pp; Fiction; May 15
  21. Duty: Memoirs of a Secretary at War; Robert M. Gates; 617pp; Non-Fiction; May 25
  22. Asia’s Cauldron: The South China Sea and the End of a Stable Pacific; Robert D. Kaplan; 225pp; Non-Fiction; June 4
  23. 1356: A Novel; Bernard Cornwell; 382pp; Historical Fiction; June 18
  24. Lady of the English; Elizabeth Chadwick; 544pp; Historical Fiction; June 29
  25. The Son; Philipp Meyer; 562pp; Historical Fiction; July 4
  26. The Operators: The Wild and Terrifying Inside Story of America’s War in Afghanistan; Michael Hastings; 432pp; Non-Fiction; July 18
  27. Legacy of Ashes: The History of the CIA; Tim Weiner; 848pp; History; July 21
  28. The Greatest Knight: The Unsung Story of the Queen’s Champion; Elizabeth Chadwick; 560pp; Historical Fiction; July 24
  29. A Clash of Kings: A Song of Ice and Fire: Book Two; George R.R. Martin; 784pp; Fiction; July 28
  30. The Divide: American Injustice in the Age of the Wealth Gap; Matt Taibbi; 448pp; Non-Fiction; August 3
  31. Ghost in the Wires: My Adventures as the World’s Most Wanted Hacker; Kevin Mitnick; 448pp; Non-Fiction; August 7
  32. Dear Leader: Poet, Spy, Escapee – A Look Inside North Korea; Jang Jin-sung; 368pp; Non-Fiction; August 8
  33. The Chemistry of Tears; Peter Carey; 229pp; Fiction; August 9
  34. Reamde: A Novel; Neal Stephenson; 1042pp; Fiction; August 18
  35. Outlaw Platoon: Heroes, Renegades, Infidels, and the Brotherhood of War in Afghanistan; Sean Parnell; 384pp; Non-Fiction; August 20
  36. The Scarlet Lion; Elizabeth Chadwick; 576pp; Historical Fiction; August 24
  37. Niccolò Rising: The First Book of The House of Niccolò; Dorothy Dunnett; 496pp; Historical Fiction; September 1
  38. The Spring of the Ram: The Second Book of The House of Niccolò; Dorothy Dunnett; 496pp; Historical Fiction; September 4
  39. Americana: Despatches from the New Frontier; Hampton Sides; 450pp; Non-Fiction; September 7
  40. D-Day: The Battle For Normandy; Antony Beevor; 608pp; History; September 10
  41. A Storm of Swords: A Song of Ice and Fire: Book Three; George R.R. Martin; 992pp; Fiction; September 14
  42. Abaddon’s Gate; James S. A. Corey; 539pp; Science Fiction; September 22
  43. A King’s Ransom; Sharon Kay Penman; 685pp; Historical Fiction; October 23
  44. The Forest Laird: A Tale of William Wallace; Jack Whyte; 512pp; Historical Fiction; November 22
  45. Wired for War: The Robotics Revolution and Conflict in the 21st Century; P.W. Singer; 512pp; Non-Fiction; November 28
  46. The Universe: 365 Days; Nemoriff & Bonnell; 750pp; Photography; November 30
  47. The Absolute Sandman: Volume 1; Neil Gaiman; 612pp; Comics; November 30
  48. Stealing Light; Gary Gibson; 603pp; Science Fiction; December 2
  49. Nova War; Gary Gibson; 568pp; Science Fiction; December 15
  50. Natchez Burning; Greg Iles; 800pp; Fiction; December 21
  51. Makers: All Kinds of People Making Amazing Things In Garages, Basements, and Backyards; Bob Parks; 184pp; Non-Fiction; December 27
  52. Empire of Light; Gary Gibson; 544pp; Science Fiction; December 29
  53. The Absolute Sandman: Volume 2; Neil Gaiman; 616pp; Comics; December 30

Christmas Eve RunAs Radio show 400

Last week we joined our wonderful friend (he MC’d our wedding in 2007) Richard Campbell for RunAs Radio show #400, where we jet-laggedly discuss all kinds of stuff, even occasionally getting technical!

You can download it here.

Enjoy and Happy Holidays!

PS We also did the 200th show back in 2011 – see here.

SQLskills hires all-round SQL expert Tim Radney

It’s hiring time again, as our consulting volume has reached the point where we need more help on our close-knit, expert team!

Specifically, we’ve asked well-known SQL expert and MVP Tim Radney to join us and we’re extremely pleased that he accepted our offer. He’ll bring our team up to seven people when he starts with us on Monday, January 19th, bringing a wealth of experience and knowledge to the team. You can reach him at tim@SQLskills.com.

IMG_0603

I’ve known Tim for years (a prerequisite for working here) and he’s a huge contributor to the SQL Community: on Twitter (@TRadney), on his blog (here), as a frequent speaker at user groups, SQL Saturdays, and PASS Summits, as the Chapter Leader for the Columbus, GA SQL Server User Group, and as PASS Regional Mentor for the SE USA. Wow! Tim was also named a PASS Outstanding Volunteer in 2012 in recognition of all that he does.

Tim’s been working with SQL Server for 15 years, in a variety of roles including DBA, Lead DBA, and multi-department manager, which have given him expertise in areas like HA/DR, virtualization, SSIS, SSRS, and performance tuning, among everything else SQL Server-related. As a manager, Tim’s gained extensive experience planning and implementing large-scale environment changes and upgrades, which will be invaluable for working with some of our larger Fortune 20-100 clients.

Outside of SQL Server, Tim is married with three kids and also shares my passion for electronics and messing around with a soldering iron. He also farms chickens and tilapias in his spare time!

Tim is an excellent addition to our world-class consulting team and we’re very excited that he’s coming on board – welcome!

Thanks as always,

Paul and Kimberly

PS You can read Tim’s post here.

SQLskills holiday gift to you: all 2013 Insider videos

As we all wind down for the 2014 holiday season, we want to give the SQL Server community a holiday gift to say ‘thank you’ for all your support during 2014, and what better gift than more free content?!

As many of you know, I publish a bi-weekly newsletter to more than 11,500 subscribers that contains an editorial on a SQL Server topic, a demo video, and a book review of my most recently completed book. We’re making all the 2013 demo videos available  so everyone can watch them – 24 videos in all, WMV format only. I did the same thing the last few years for the 2012 videos and 2011 videos.

Here are the details:

  1. January 2013: Recreating missing log files on attach (from Pluralsight) (video | demo code)
  2. January 2013: Using the sys.dm_db_stats_properties DMV (video | demo code)
  3. February 2013: Using Microsoft Data Link (video)
  4. February 2013: Linked servers and statistics (video | demo code)
  5. March 2013: Using the system_health Extended Event session (video | demo code)
  6. March 2013: Moving from SQL Trace to Extended Events (video | demo code)
  7. April 2013: Color coding and other SSMS features (video)
  8. April 2013: DISTINCT aggregate improvements (video | demo code)
  9. May 2013: Using the tsql_stack Extended Event action (from Pluralsight) (video | demo code)
  10. May 2013: Deferred drop behavior (from Pluralsight) (video | demo code)
  11. June 2013: Finding duplicate statistics (video | demo code)
  12. June 2013: Using data compression (video | demo code)
  13. July 2013: Undetectable performance problems (video | demo code)
  14. July 2013: SSMS Object Explorer features (video)
  15. August 2013: Parallel crash recovery (from Pluralsight) (video | demo code)
  16. August 2013: Tracking tempdb space usage (video | demo code)
  17. September 2013: Enabling instant file initialization (video | demo code)
  18. September 2013: Using query hashes (video | demo code)
  19. September 2013: Recovering from data purity corruptions (from Pluralsight) (video | demo code)
  20. October 2013: Implicit conversions (from Pluralsight) (video | demo code)
  21. October 2013: Extended Events templates (video | demo code)
  22. November 2013: Using the missing index DMVs (video | demo code)
  23. November 2013: Using older backups to retrieve data after corruption (from Pluralsight) (video | demo code)
  24. December 2013: Enabling database mail (video)

If you want to see the 2014 videos before next December, get all the newsletter back-issues, and follow along as the newsletters come out, just sign-up at http://www.SQLskills.com/Insider. No strings attached, no marketing or advertising, just free content.

Happy Holidays and enjoy the videos!

Problems from having lots of server memory

A month ago I kicked off a survey asking how much memory is installed on your largest server that’s running SQL Server. Thank you to everyone that responded.

Here are the results:

memory

 

 

 

 

 

 

 

 

 

 

 

The “other” values are:

  • 3 more for the ‘128 GB or more, but less than 256 GB’ count
  • 1 more for the ‘Less than 16 GB’ count
  • One poor soul who only has 512 MB in their server!

This is very interesting:

  • I expected the majority of servers to fall into the middle of the range (around 128GB), and it’s actually only 37% that fit into the 64 GB to 256 GB range.
  • I’m surprised at the percentage of servers (41%) of servers with 256 GB or more.
  • I didn’t know what percentage would have more than 1 TB, so almost 10% is really cool to see.

So what do these results mean? Well, the number of servers out there with lots (more than 128GB) of memory is more than half of all respondents. The more memory you have, the more important it is that you make sure that the memory is being used efficiently and that you’re not wasting space in the buffer pool (see here) and that you’re not churning the buffer pool with poor query plans causing lots of reads (see here).

What other things could be problems with large amounts of memory?

  • Shutting down the instance. This will checkpoint all the databases, which could take quite a long time (minutes to hours) if suddenly all databases have lots of dirty pages that all need to be flushed out to disk. This can eat into your maintenance window, if you’re shutting down to install an SP or a CU.
  • Starting up the instance. If the server’s POST checks memory, the more memory you have, the longer that will take. This can eat into your allowable downtime if a crash occurs.
  • Allocating the buffer pool. We’ve worked with clients with terabyte+ buffer pools where they hit a bug on 2008 R2 (also in 2008 and 2012) around NUMA memory allocations that would cause SQL Server to take many minutes to start up. That bug has been fixed in all affected versions and you can read about in KB 2819662.
  • Warming up the buffer pool. Assuming you don’t hit the memory allocation problem above, how do you warm up such a large buffer pool so that you’re not waiting a long time for your ‘working set’ of data file pages to be memory resident? One solution is to analyze your buffer pool when it’s warm, to figure out which tables and indexes are in memory, and then write some scripts that will read much of that data into memory quickly as part of starting up the instance. For one of the same customers that hit the allocation bug above, doing this produced a big boost in getting to the steady-state workload performance compared to waiting for the buffer pool to warm up naturally.
  • Complacency. With a large amount of memory available, there might be a tendency to slacken off proactively looking for unused and missing index tuning opportunities or plan cache bloat or wasted buffer pool space (I mentioned above), thinking that having all that memory will be more forgiving. Don’t fall into this trap. If one of these things becomes such a problem that it’s noticeable on your server with lots of memory, it’s a *big* problem that may be harder to get under control quickly.
  • Disaster recovery. If you’ve got lots of memory, it probably means your databases are getting larger. You need to start considering the need for multiple filegroups to allow small, targeted restores for fast disaster recovery. This may also mean you need to think about breaking up large tables, using partitioning for instance, or archiving old, unused data so that tables don’t become unwieldy.

Adding more memory is one of the easiest ways to alleviate some performance issues (as a band-aid, or seemingly risk-free temporary fix), but don’t think it’s a simple thing to just max out the server memory and then forget about it. As you can see, more memory leads to more potential problems, and these are just a few things that spring to mind as I’m sitting in the back of class here in Sydney.

Be careful out there!

 

T-SQL Tuesday: Giving back in 2015

TSQL2sDay150x150

 

 

 

 

 

My friend Wayne Sheffield (b|t)is hosting T-SQL Tuesday this month and it’s been a long time since I’ve taken part (wow – since January 2011!). His theme is about how you’re going to give back or continue giving back to the SQL Server community in 2015. I’m going to talk about stuff we at SQLskills do in the community, in the spirit of the T-SQL Tuesday theme, not as any kind of marketing or self-aggrandizement.

We do a lot (I think) for the SQL Server community, both because we’re just nice like that :-), and because we appreciate the people in the community as they provide our livelihood. To be honest, for me it’s mostly because I like helping people with SQL Server problems. It’s like an irresistible urge when I see someone with a problem I know how to help with.

We’re going to continue with these things in 2015:

  • Helping out on the fantastic #sqlhelp alias on Twitter
  • Blogging on all our blogs
  • Publishing our bi-weekly newsletter with a demo video and editorial (see the link on the RHS of your screen)
  • Glenn publishes his monthly DMV queries for all versions of SQL Server from 2005 onward
  • Erin helps run the Cleveland User Group

And we’re also going to be doing a lot more:

  • Remote user groups. I had this mad idea to do remote user group sessions for anyone that asks – so we’ve currently got 49(!) remote user group sessions scheduled in 2015 at user groups in the US, UK, Ireland, Belgium, Netherlands, New Zealand, South Africa, Israel, Australia, Canada, Poland, and PASS virtual chapters. I’m personally doing 20 of them. Check if your user group has signed up, and if you’re in a country not listed above definitely hit us up for a session. See here for details.
  • I’m putting together a comprehensive web encyclopedia of all wait and latch types that exist, across all versions, slated to go live in January/February. Shoot me an email if you’d like to help provide data (involves installing debug symbols and XEvent sessions on your prod server). That’s going to be cool!
  • Kimberly and I have another special project coming up… more details in January/February

As Wayne says in his blog post, I encourage you to give back to the community. We’ve got one of the best technical communities in the world – I know we’re the envy of many others.

Thank you to everyone who blogs, tweets, runs/speaks at/attends user groups/SQL Saturdays/PASS – long may we all continue!

Cheers