Voice of the DBA

Tag: SQL Connections

  • 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.

  • DevConnections Pictures

    20110329-115359.jpg

    20110329-115415.jpg

    20110329-115428.jpg

  • Brent Ozar Blitz at SQL Server Connections

    The first session of the day for me was Brent Ozars Blitz talk on how to quickly get information on a new server. I have seen Brent’s video on this and read some of his blog posts, but i wanted to see him go over the script in a session.

    Brent is funny and has a way of interacting with the audience that makes his talks enjoyable. In this one, he walks you through the script, taking you through the list of items that he finds important to check when he first takes over a server. You can get the script from BrentOzar.com, or search Brent and Blitz in your favorite engine.

    It’s important to go over certain things in a new server, and i would agree with what Brent checks. He looks for backups, DBCC execution, jobs, privileged accounts and more. He has scripts that query the system databases to find out this information. It’s a good practice to use these types of scripts to ensure that you have self-documenting information from your instance. It prevents your information from being out of date.

    A few things I learned in here. One was to check for backup history in msdb since that can slow down your backup process over time. The msdb table for backup tracking isn’t well indexed and can fill over time 30-60 days should be enough history. Another is to check for encryption of your databases. I typically don’t work with Enterprise Edition so I don’t run into TDE. However its good to know this and prep your keys for a DR situation. Nothing worse than trying to restore and not having those keys. Also, once you encrypt a database, tempdb is always encrypted. Even if you remove the individual database encryption. That’s good to know. It might not e a big deal, but it is extra overhead.

    I also found a nice check for objects in master or model, which isn’t a recommended practice, and you might not think to look and be aware of anything that is stored here. One thing i was not aware of is a query to check for Enterprise Edition features being used in a database since you cannot restore these databases in other editions. actually you can, but at the end the restore process will throw an error and then fail.

    I would definitely recommend this session if you find Brent speaking at an event near you.

  • Locking and Blocking from Joe Webb – DevConnections

    Locking should be simple, right? You need to access a row or table and you get a lock. Oh yeah, blocking is bad.

    It’s not that simple, and I find this is an area that many people do not understand that well. Joe does a nice job of presenting this from a beginning level and going into more details on locking using lots of demos.

    Blocking is the result of contention, but that is good. It helps enforce the consistency between resources. Joe shows how SQL Server tries to balance the concurrency and consistency requirements, showing escalation and how to query the sys.dm_tran_locks table and understand what is being locked and why.

    When does SQL Server escalate? The default is 5000 locks but it is not that simple. There are other factors that come into play.

    This was an area I studied a lot for the MCM and I learned a lot about the details of locking behavior. Joe does a nice job of simplifying locking for the DBA or developer looking to better understand why locks and blocks occur on your server.

    Has anyone modified the parallelism lock threshold? No one in the audience and I would be interested in hearing if anyone reading this has.

    Controlling locking is a bit of an art. There are hints you can use, like NOLOCK or READPAST. These can help, but this should not be a standard in all your queues. It’s like duct tape. It can “appear” to fix a problem but it’s really just alleviating some pain. A tool but not necessarily the first one you want to use.

    Keep in mind that your hints are “requests”, not mandates. SQL Server many not honor them.

    By default, SQL Server will wait indefinitely for a lock to clear. Deadlocks are an exception, where one process will have it’s locks clears when it is killed. You can set a lock timeout but if you do this, the application need to handle the 1222 error and then retry.

    This is a good session for you to attend if you are new to locking and want to learn more about how this works. As with many things in SQL Served, there are no hard and fast rules to apply but you will learn some tricks to try and where to look to get more information.

    There are also isolation level settings you can change. These are a little more drastic and you really need to understand the impact of them, especially snapshot isolation.