Tag: syndicated

  • A 22 Hour Layover

    I joked about having a 22 hour layover in Denver yesterday and a few people were wondering what airline screwed me this time. That made me laugh, because they didn’t get the joke.

    I got home yesterday at 3:30pm from SQL Server Connections in Orlando. I’ve been there all week, and after some travel delays, I got back at in time to pick up my son at 3:45 from his school bus.

    Today at 1:15 or so I need to leave the house, heading back to the airport for my trip to Dallas and SQL Saturday #63 tonight. Essentially I got a 22 hour layover at home before my next trip.

    I hate that, and as much as I’m looking forward to seeing people in Dallas, the travel wears on me. Fortunately I’ll be shutting things down until June when I got to Pensacola and SQL Saturday #77. That won’t be a bad trip as my daughter will come and we’ll spend a few days at the beach and make it a nice Daddy/daughter vacation.

  • Hanging with an Inspiration

    A long, long time ago, in a state far away, I started writing about technology. At the time, in the late 90s, I was looking to share some of my knowledge, and hopefully advance my career along.

    It was intimidating to think that I could produce articles anywhere near the quality of the books I was reading to learn more about SQL, and I needed some advice. I looked at the covers of a few of the titles on my bookshelf, back when I used to collect physical books, and reached out to one of the very well known authors.

    I asked a few questions about being a writer, asked for tips, and asked about how feasible it was to make a living at writing. Needless to say I was surprised when the author of about 10 books at the time responded with some candid advice and information about the publishing side of technology. I got some advice, some realistic warnings about what to expect, and most importantly, some encouragement.

    The author was Mark Minasi, a fellow Virginia Beach resident. Well, fellow at the time. He’s still there, but I’ve moved to Denver

    Over the years I have seen Mark a couple of times at conferences and shaken his hand. Paul Randal is a mutual friend and has introduced us, though it’s always been a “nice to meet you, big fan” from me since one of us is usually walking away to some other commitment.

    However last night I had the chance to sit and talk with Mark over a few drinks. It was the first time I could properly thank him for his advice and encouragement over a decade ago. We shared a few drinks, some laughs, and I got to hang with someone I admired. I got a few complements back about what I had achieved and it was one of the highlights of my trip.

  • Dude, where’s my memory?

    A session I have wanted to see. Marciej Pilecki has done this before with high ratings and I have missed until today

    He starts with a description of how memory is allocated in the 32 and 64 bit systems. One interesting thin he noted is that the lock Pages in Memory setting on 64 bit still uses the AWE API though the awe setting doesn’t make sense and isn’t used.

    SQL server 2005+ is NUMA aware. Worth noting if you upgrade hardware for those old SQL 2000 instances

    One lazy writer thread per NUMA node.

    TCP NUMA affinity. Hadn’t heard of that but it is something to look up and check on. Marciej calls this “poor man’s Resource Governor”

    The buffer pool is not just for data? Also does single page allocations for other caches. Interesting

    An interesting demo to show the allocation of memory as the server starts and you run queries. dbcc freeproccache
    Might clear buffers but the memory remains allocated.

    Changing the Max memory live shown in Perfmon. Both target and total server memory adjust in real time.

    Memory pressure can be virtual or physical and either internal or external. Inside SQL server, your physical memory pressure could be global across the instance or local to some smaller cache.

    External pressure is a notification from the OS. Clock hands in SQL will sweep cache stores (need to read about this) and asks lazy writer to sweep buffer pool. This external pressure can lead to internal pressure.

    Every memory cache has two clock hands: internal and external. Externals move together. Internal caches move separately from each other

    Demo showing this by querying sys.dm_os_memory_cache_clock_hands

    LRU algorithm used to clear caches

    Procedure cache aging is based on cost of compilation, not cost of execution. This cache can also steal memory from the buffer pool.

    Check on your caches with dbcc Memorystatus. Never have used that before but it shows nicely the “stealing” of buffer pool by the procedure cache

    Good session and very interesting to get a little more insight into the memory of a server.

  • Brad McGehee talking about tempdb issues

    Brad presented a session that tries to help you identify and fix tempdb problems. Performance monitor is your friend. the local disks, avg read and write counters should help you determine if I/O is an issue. Watch for systems where you have >20ms for the averages, this is a gross number, and not an exact measure, but something to be aware of.

    Wait states can help you to identify contention for tempdb on allocation structures. Using sys.dm_OS_waiting_tasks, you can look for pagelatches on the pfs, gam, and sgam pages. If you query and find that you have lots of waits for these allocations, you might want to add more tempdb files to help alleviate contention.

    There is no reason a DBA should allow a database to run out of space. A quote from Brad and for the most part i agree. You ought to have alerting setup, monitor space, and grow files as needed. There are cases where a runaway event might cause issues, but for the most part, you should be managing space actively.

    Optimizing: a variety of suggestions, with the idea that you ought to assume tempdb will be an issue over time. So pre-plan for performance.
    – minimize usage. Dont return more rows than you need. Don’t sort data you don’t need to, don’t use order by or distinct if not needed, keep transactions short, index well, avoid temp tables.
    – Avoid cursors, especially static or keyset driven cursors.
    – avoid LOB columns if you can, consider vertical parittioning.
    – avoid table variables
    – avoid triggers where you can
    – avoid aggregating large sets of data
    – avoid snapshot isolation or read committed isolation levels
    – if you use sort in tempdb for index rebuilds, schedule it during off hours

    You can use these features if you need them, but understand they impact tempdb. Be smart and minimize the usage where you can.

    Isolating tempd on separate physical disks can help speed it up, adding RAM might help if sql server can avoid spilling to tempdb, but that depends if you have memory pressure. If you do not have any memory pressure, then you may not get any benefit from adding ram.

    SSDs are used for tempdb, but the problem is that tempdb has lots of read.write and can “wear out” an SSd drive.

    “The DBA life is about compromise”. I would agree with that. We can’t always do what we want and dont have the money so we need to make tradeoffs.

    Preallocate tempdb space, monitor, and then resize as needed. Use IFI for data growth if needed. Note this does not apply to log growth.

    Multiple files can help prevent tempdb contention, recommendation is 1/4 to 1/2 the number of cores, up to 8. Note that you want to make all your files the same size.

    If you enable TDE, which is a good feature, be aware that tempdb is encrypted. Also be aware that if you remove TDE from all databases, tempdb remains encrypted. Be careful of “testing” TDE on servers.