Chances are if you’re experiencing one of these two lock wait types you have a very high transaction application. In our case it’s true to the extent that we have greater then 3x the amount of connection resets on our connection pool then any other application using SQL Server in the firm (on just one instance about 178k connections per minute).

In our few extremely high transaction tables we wind up running into an issue with disposing of stale data. To solve this we partition the tables by day of year (and in some cases hour of day). Every day (or hour) we swap out the partition to a blank table and truncate that swap table for a sub second data cleanup. And because of this we’ve both partitioned and sorted our cluster index by this field.

A PAGELATCH_EX or PAGELATCH_SH alert is actually not related to IO yet you’ll run into it with the scenario I explained above. What winds up happening is that while we have a pretty beefy data server, we have contention on the last page in the table only allowing us to process a few inserts at a time. This is because our clustered index is sorting on day of year ascending which creates a hot spot at the end of the table.

In order to solve this, all we had to do is force our inserts to hit multiple parts of the table at once to prevent a single page lock from slowing down the entire system. In this part of our application we were collecting metrics data from all workstations in the firm and we had a column MachineName for every row in the table. We decided to add MachineName to the clustered index which then forced each insert to each hit a unique page in our table.

From then on, you’re sitting on the speed of your disk IO. Provided you have a decent storage setup you’ll be leaning on your log volume. The rest is pretty well documented in Google, I just didn’t find anything that easy for PAGELATCH issues so I figured I would share.

I was asked this question a few months ago and liked it due to its simplicity, and how it can be done on paper which is how we’ve been administering our interview tests (this is currently not on my test).

Imagine you have a packaging system and there is a table that defines what packages a user can access. There are two columns uid (user id) and pid (package id). Every row in this table provides a specific user with access to a package, for example there may be rows [1,1] or [3,5] where as user 1 has access to package 1 or user 3 has access to package 5. A user can have access to multiple packages which would result in rows like [1,1], [1,2], [1,3], etc… However, a user will be considered a “Super User” if they have a pid of -1 assigned to their user id. Often times a user is given access to multiple packages only to later be given “Super User” access. Write a query for this table to display all user access however if a user is a “Super User”, only display the one row for them demonstrating that they are a “Super User”.

Now, more often then not people will come up with some hackish query involving “UNION” or “IN”. When they do, tell them both are horrible for performance if not for any reason other then there are multiple executions within one query. Ask them to try and rewrite it using joins and not “UNION” or “IN”. This brain teaser is simple and should demonstrate their confidence with joins and ability to think beyond “Step 1, Step2″ answers. There is no one right answer, I’ve included mine below:

Lets jump into a practical example of openrowset in action. A common issue among dataservers is disk space so it only makes sense to monitor for thresholds so that you can fix an issue before it becomes an emergency. Thresholds are great, and you probably have an alerting tool like HP Service Center to take care of that for you. However, what about trending and proactive issue resolution?

When you must maintain hundreds of machines, space trending is practically a requirement. You will likely want to monitor (and trend) disk space on the database level as well, and we will get to that in a separate post.

We will work off of my openrowset disk space collector found on my scripts page HERE. This procedure is meant to be executed from your central data collection point and does not need to exist everywhere.

First, my approach toward the openrowset:

Here, we figure we will try and keep everything as dynamic as possible. The only real things we need to specify are the driver and that this will be a trusted connection. We will construct the remote query dynamically in the code before openrowset and the remote instance will be passed into the instance upon execution (exec p_get_drive_details ‘HOSTNAME\INSTANCENAME’).

To setup, first create a table drive_details with columns for: Date, InstanceName, DriveLetter, TotalMB, FreeMB

After that, create the procedure and run. In our crawler, which we will discuss later, we can loop through all of our available instances (we will get into error checking later too).

Bonus: Create a view on top of your table called drive_details_recent and have it just show the last run. This way we can have a history table for trending yet quickly look up the most recent info for either displaying on a front end or to just hide old data from view.

So, I’m bored and I’m tired of looking at an empty blog, time to share some of the stuff I’ve learned (and built) over the past few years.  A lot of it will be DBA based but I’ve always been a developer at heart so they are tools to help you do the job quicker.  I’m doing a lot of cooler database development work now so hopefully once I work though some backlog I’ll get to writing about more difficult things.

First, lets talk about a crawler briefly as most of the cool stuff I’ve done is based around data collection and storage.  I imagined a single data server being a central point for collecting data from all of the other data servers.  This works for most things in the database world, especially when you don’t have the luxury of pushing a service (application) to thousands of machines.  If you are into the service side, stay tuned, the team I just joined pushes data collection to a new level so hopefully I’ll get to shed some light on the concepts I’m learning.

Now, the central point without an application sitting on every machine can be achieved with openrowset because you can assume credentials between different SQL Servers on the same network (domain can of course be different).  This requires you to enable ad-hoc queries.  The way it is setup in SQL Server is beautiful because here you only have to enable openrowset on your crawler (central point of data collection) and you can leave the settings on the other thousand data servers alone.

There, now we’ve opened the door. The next step is to get the key (which you likely already have) to all the data servers.

Typically if you are managing even a couple hundred instances you will need a DBA functional group. This is a windows domain group where you can stuff all the DBA accounts so this way, when you build out a new server, you just add that group and everyone has access. We can the run the collection instance under one of those accounts and we have access everywhere (while obeying the domain account double-hop rule); OR we can create a common SQL authentication account everywhere and use that as the method to login everywhere. Additionally we can do some tricks via BAT files, but we will talk about that another time.

There you have it, now you’re primed and ready to go. Next post I’ll talk about the queries we can run to start collecting data.