Speaking at Connections

I just got my acceptance to the DevConnections, SQLConnections in September for two sessions:

  • Continuous Integration for Databases
  • Maintaining High Performance When Using Encryption

I enjoy the Connections events, usually finding time to go check out an ASP.NET or developer session of some sort.

Now’s the time to register and save. There will be some great pre-cons as well, including one from a scary individual that I know well.

DevConnections – Fall 2012

This fall is the DevConnections SQL Server Connections conference in Las Vegas again. It’s at the Bellagio, setting for one of my favorite movies, Ocean’s 11. It’s a smaller conference this time, and SQLServerCentral only has 4 slots for the event. Both Grant Fritchey and myself will be speaking on the Monday. At least I think we will. The schedule isn’t out, but that’s what I was told.

I haven’t decided, but I’d like to try for a tweetup/meetup one night there, perhaps in front of the famous fountains. It would be neat to see a few SQL Server people at the spot shown at the end of the movie.


In any case, I’m heading back to Las Vegas. I enjoy visiting, and am looking forward to wandering around a bit.

I’ve also got a lot of work to do. I have two sessions, one on binary data search in SQL Server, using the Integrated Full Text Search, and another on Policy Based Management. Both interesting topics to me, and areas where I’ve done some work, but I have to dig in over the next month or two and build a couple great presentations.

If there is anything you want to learn about these topics, let me know. I haven’t quite decided where I’m going to focus, other than what’s in the abstracts. I have some room to speak in a few areas, but I’ve learned time goes away quickly when you’re presenting, so I need to be sure I’m focused.

Hopefully I’ll meet some of you at the event. SQL Server Connections is a neat conference, one where you have the chance to learn a lot in a bunch of different areas. With 8 other conferences co-located, I always find a few sessions outside the SQL world that I find very interesting. A lot of the private cloud, SVVM, and related topics I’ve seen talked about recently were things I saw at the last Connections event in March. I expect there will be a lot of Windows 2012 stuff in the fall, just in time for you to plan for the 2013 budgets.

Check out the event, and think about joining me in Las Vegas. It’s a good place for an event, easy to get to, and there are lots of activities to join in outside of the conference.

Spring SQL Server Connections / DevConnections

I returned late last night from the Spring DevConnections event, delivering three sessions in the SQL Server Connections conference. I talked about Encryption, Filestream/Filetable, and Contained Databases. Always cool to see your name in print

Photo Mar 28, 9 15 56 AM

It was a nice event, with decent crowds in the SQL Server. I didn’t have a ton of people, but when you’re competing with the SQLskills crew, Brent Ozar, Allen White, Glen Berry and a few others, it’s hard to draw a large crowd.

Photo Mar 27, 11 13 04 AM

Grant and Brad both presented as well, filling up the slots in our SQLServerCentral track, and talking about a wide range of topics. Grant’s talk on continuous integration and deployment was really good, with lots of interesting information. It was good to hear from a few people in the audience that face challenges on this particular topic. We have a whitepaper from Red Gate that talks a little about how we solve this, but Grant gave some other alternatives that you could use to tackle this.

Photo Mar 29, 3 29 29 PM

The show was at the MGM Grand this time, and I have to say I really like that resort. Things seemed a little closer together, more food/dining choices, and close to other places on the strip. A walk to the Bellagio was no big deal. There were also three of my favorite morning stops on the road

Photo Mar 27, 7 51 14 PM

It wasn’t just me that liked the hotel. I left Paul Randal, Kimberliy Tripp, Brent Ozar, and Glenn Berry at Craftsteak Thur night to go to the airport. I say down before my flight to see a tweet that the First Lady of the US was having dinner that night in the restaurant.

The sessions I attended and delivered were interesting. Lots of system administrators and developers mixed in with the DBAs, looking for information on various features. It was a twist to get questions that sometimes were more basic for DBAs, and at the same time, had a different view and some challenges when presented from another perspective. On a panel with Grant Fritchey and Brent Ozar, we had some interesting HA/DR questions from the audience about 2012 as well, things that made us think a bit.

The exhibition hall was a little small, but it’s nicely set up with long breaks between some sessions for people to wander around and see what new third party tools are available. I spent a little time at the Red Gate booth and had some very interesting conversations with people, including a nice long one about .NET development.

Photo Mar 27, 9 23 26 AM

As I do everywhere on the road, I start the mornings out with a run. This time the loop around the MGM complex is a little over a mile and a half, which I enjoyed all three days with Allen White at 6:30 every morning. #sqlrun in Las Vegas!

Photo Mar 29, 6 25 37 AM

This time the Windows and Visual Studio tracks were on another floor, and with three sessions to prepare for, I didn’t get the chance to visit those tracks. I did see a great talk from Allen on Server Core, which I’ve been wanting to experiment with. Look for most of my future server VMs to be Server Core, which should make things run faster on my little Air.

I also saw a couple talks from Mark Minasi on Windows 8 and that looks rather interesting in a few ways. I’m not sure I like the client interface, but some of the architectural changes look like good moves on the part of Microsoft. The server side, especially seems like it will end up being a good server OS for management by IT staffs.

The other great thing about a show in Vegas is the wide variety of shows. I asked in my sessions and a few people saw Ka, one of the Cirque du Soleil shows. Someone saw David Copperfield as well, which I would have liked to see. Oh well, there’s always next time.

My entertainment this time was a wonderful dinner with Brent Ozar and Grant Fritchey at e by Jose Andres in the Cosmopolitan. Brent invited us and I wasn’t sure since it’s a very fancy dinner. However I thoroughly enjoyed it. Not sure I’d go back myself, but I’d take someone else, just for the experience. The food was very interesting (I’m not a foodie), and the presentation amazing. I had a great time. Here was one of our “courses”

Photo Mar 28, 5 44 38 PM

Overall a great show, and a fun time. SQLServerCentral is one of the sponsors, and we have a track there every show.

Photo Mar 29, 12 23 12 PM

We should be back in the fall, this time at the Bellagio, Oct 29-Oct 1. Hopefully you can join us, and we look forward to seeing you then.

The Connections Launch Event

The SQLServerCentral track at SQL Server Connections

The Spring SQL Server Connections conference will be one of the launch events for SQL Server 2012, the next version of the platform that we write about every day. This spring SQLServerCentral is sponsoring a track again, with Brad McGehee, Grant Fritchey, and myself speaking in one room. We’re all focusing on SQL Server 2012, trying to get ready to upgrade and use the new features.

However we’re only a small part of the SQL Server track. SQLskills.com headlines the track, as they have for many years, with Kimberly L. Tripp, Paul Randal, and Jonathan Kehayias presenting some amazing information based on their years of experience (go to one of the SQLskills.com classes if you can). Paul and Kimberly have also invited a number of other great speakers, including Aaron Bertrand, Allen White, Brent Ozar, Glenn Berry, Mike Walsh, and more. These are the people that write many of books, blogs, and articles that you read to learn more about the SQL Server platform.

The DevConnections conferences are a great place to not only learn about SQL Server, but also learn about all parts of the Microsoft technology stack. There are nine conferences in all taking place at the same time, covering Windows, Visual Studio, Sharepoint, Exchange, and more. There’s even a Cloud Connections, dealing with those issues that you’ll find when working with the Microsoft Azure products.

It’s still winter in many parts of the US, so schedule your own “spring break” from work and come to Las Vegas this spring. The weather will be great, and you’ll have the chance to learn about all different type of Microsoft technologies and platforms in a fun environment. You can even take in an amazing show one of your nights in town and let your brain relax from all the tech talk during the day.

Steve Jones

The Voice of the DBA Podcasts

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.

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.

Extended Events at SQL Server Connections with Jonathan Kehayias

I was very interested to hear Jonathan Kehayias’ session on extended events. I know this is one of the areas i need work and it is becoming a more important part of SQL Server with each version. Beginning with a good discussion of what extended events are and the terminology is, Jonathan presented a lot of information. Much like PBM, this subsystem requires you to learn a lot of new names and terms in order to even understand the documentation.

With a dozen or so terms I am sure i wasn’t the only one that was slightly confused, not because of the explanations but just because it’s a lot of information. I know i need to read up on this a bit more before i see another session.

One important note: You Need to learn XML. all of the results come back as xml and some knowledge of xquery is going to be necessary to work with the event results, not my favorite thing, but i have another skill to work on now.

After 20 minutes, it’s demo time. The first questions is do log operations cause waits? We see a demo that shows what is recorded in DMVs, what the events see, and what is reported in the query statistics.

An interesting fact. Once the event data is gathered, you cannot drop the session without losing the data. You can however drop the events and leave the session so you can query the data.

Another item, causality tracking is needed to associate events to a specific process. It adds overhead, but you need this at times to determine which events correspond to which particular sessions doing work.

How often does ghost cleanup run and what does it do? Demo 2 looks at the events that are associated with ghost cleanup. In a busy system, ghost cleanup can become a bottleneck, so it can be handy to know if this is a problem. There is a trace flag to turn this off, but it turns it off for the instance.

The demo looks at what happens when you delete a number of records and the ghost cleanup task begins to fire. By turning off and on ghost cleanup, we can see records being deleted. Lots of events, and we need ghost cleanup hitting lots of records in a short period of time. In fact we saw 12 rows being cleaned up in one ms.

The last demo is a look at checkpoint. What happens with a checkpoint? When does it run during a backup? During the demo we can see the checkpoint being started right after the backup starts and we can see lots of pages being flushed to disk.

Very cool demos. Lots more information than you normally want, but useful stuff for digging into problematic systems.

Brian Kelley talking Windows Internals for SQL Pros at DevConnections

I have known Brian for nearly as long as i have been running SQLServerCentral. He has been the guy that I called for security questions. He was a DBA, then became a security and auditing guy, and is now back to being a DBA. This talk is designed to better explain the host OS for SQL Server pros. Some random notes since i am off to lunch.

We have user mode and kernel mode. Where does SQL Server run? Most services run in user mode. Kernel mode is for privileged mode, base OS services, memory access, device drivers,, HAL, etc. Most crashes come from device drivers or video drivers, so be sure these are up to date.

If SQL Server crashes, and it could, you don’t want the OS to crash. You want a graceful recovery or restart. That is why SQL Server runs in user mode.

Most processes start in user mode. If it makes a call to a system function, the kernel traps the request and switches to kernel mode, the call is carried out, and then the OS switches the process back to user mode. This tries to prevent poorly written applications from harming the OS environment.

BSOD. Microsoft doesn’t like this terminology and i am not surprised. When this happens, you have a hard OS crash. However you get a crash dump and information. The code listed, the parameters, and the dump file can be important to finding the root cause of the issue. Microsoft typically uses the mini-dump these days of the kernel to debug the issue.

SQL Server can be run as a command line application, but it typically runs as a server. The services subsystem in Windows manages this. There are various services host that control the services. Your interactions with services, starting, stopping, etc, are with these hosts, not the actual program. The services hosts must communicate with the actual program to have it perform an action.

Most services are also command line apps because they cannot have on screen interactions. MAPI was an example of this causing problems with SQL Server when it would pop a message at times that required an interactive desktop.

Windows 2008 (and later) and Vista (and later) have service isolation to prevent service hopping of service accounts. These OS’s also have a delayed auto start to allow some services to be delayed at startup for places where there are dependencies. Reporting services is an example here, you might want to delay this so that the database engine has a chance to start.

Sql manages its own threads, which are not linked to connections. So each connection is not a thread. Usually more connections than threads. SQL Server manages the threads and while you can boost the maximum number, you ought to do this only after extensive testing or advice from CSS. Or both.

Threads have priority, determining which threads may run before other threads, there are 32 priority levels. 1-15 are normal, and 16-31 are realtime processes, most things ought not run as 16 or above, SQL Server normally runs at 7, but can be boosted to 13. This is generally not recommended for most systems, but it can be an option. Often if SQL Server is waiting on something, it is usually disk or another bottleneck. Changing thread priority will not necessarily help.

Affinity mask determines which processors is SQL Server allowed to run on. Tis might be something you use if you have multiple instances and they compete for CPU time.

Highest priority thread always runs first. The OS an boost priority to prevent thread starvation, but the highest priority runs. Threads run for the duration of its quantum (duration). It can end early because it finishes, it needs to wait in something, or it is pre-empted.

Why are client tools slow on the server? The quantum duration is lower on servers. The server OS is optimized for background tasks, not interactive tasks. Some tasks get higher boosts. Sound application gets a very high boost, so do not run things like Media Player on a server.