Latest Entries »

CACTUSS May 2011

This month Tim Costello (blog | twitter) gave a presentation on Rapid Fire Business Intelligence using Tableau.  Tim works for InterWorks, where he specializes in ETL and Data Analytics.  When you visit his profile and look at his picture, he wants you to keep in mind he was participating in No-Shave November at the time.

Tableau

Tableau is not SQL Server Reporting Services, it is an Analytics Tool.  The strength of SSRS lies in tabular reports, using string data.  You use it to answer a question that you already know.  Contrast that with Tableau, whose strength is in helping you identify a question you did not know about, but should be asking.

The Big Picture

Most things are done at the aggregate level to enable you to see the big picture visually.  Machines are able to recognize patterns in text very easily, but for us mere humans, it is easier to do this visually.  This is what Tableau focuses on doing.  Furthermore, Tableau hired a professional color designer, who designed some custom color palettes for users who are colorblind.  You can see a nice example of this that uses good ole AdventureWorks for the data source.

The internals of how you setup Tableau are similar to SQL Server Analysis Services.  You will see all the familiar dimension and fact tables.  However, the setup is more visual and user friendly.  It is intended for users, well…power users, instead of designers and engineers.  Tableau is cross platform and you can point it to SQL Server, SSAS, Oracle, Firebird, MySQL, or many other data sources.  Similar to Adobe Reader, Tableau is able to use disconnected data so that you may view and analyze it offline.

After you have connected to your data source, you are presented with a nice, easy to use WYSIWYG designer to setup your Data Analysis.  At this point, it is helpful to run through the Categorical Analysis.  This is a basic statistical breakdown of your data; how many rows, how are your values spread out, etc.  When setting up your design, consider one of the Principles of Data Visualization, “don’t overwhelm your customers with data.”  You can embed SQL queries directly into the connections, however the best practice to create views on the database server so that you can reuse code.

As you begin to analyze your data, it pays to be cognizant of how standard deviation plays into your data.  Seventy-five percent of your population should be contained within one standard deviation, and ninety-five percent of your population should be contained within two standard deviations.  So you want to keep this in mind while looking for outliers in your data set.

While checking out his example, we did see one such outlier; Null Island.  Null Island is conveniently located at the intersection of the Prime Meridian and the Equator.  For my next vacation, I think I may go visit.

Visual Resume

There is a public version of Tableau available.  When using this version, you are limited to 500,000 rows and your data must be uploaded to their servers.  Obviously, this is not a viable option for most production environments.  However, this could serve well for a proof of concept or test environment.  Tim has a demo of this where he has visual version of his resume posted.  By leveraging the visual tools in Tableau, you are able to see a graphic breakdown of Tim’s skill set and experience.  Not that he’s looking…  😉

In conclusion, please keep in mind that analytics is not reporting, and reporting is not analytics

User Group Business

We are trying to rearrange the San Antonio SQL Server Users Group to meet on Mondays.  Doing so, would bring us closer to the goal of having each major metropolitan area hold their respective meeting on a different day of the week.  Having the days aligned in this manner will facilitate having out of state or “big name” speakers make the Texas Tour.

Austin is looking to experiment with adding a second SQL Server Users Group that is located in the downtown area.  This will compliment the existing group which meets in North Austin.

And finally, the news that you have all been waiting for…

We are getting closer to hosting a SQL Saturday in Austin.  The tentative date is September 10th, so mark your calendars.  Let’s all think happy thoughts to help make this a reality.

Week One Fun

Performance and Internals Curriculum

Recently, I participated in the SQLskills Immersion Event for Internals and Performance hosted by Kimberly Tripp (b | t) and Paul Randal (b | t).  My classmates consisted of Senior DBAs from a variety of countries and industries.  We had people from coast to coast and a couple from Europe, as well.

I say participated because this truly is an interactive experience.  Consider a typical class where you sit back and passively listen to an instructor who clicks through the installer wizard and PowerPoint slides.  In this class, you have to actively participate in order to get the most from it.

College Daze Revisited

There is so much information thrown at you, if you do not actively try to assimilate it and ask deep, focused questions, then you do do not stand much of a chance of retaining any of it.  Think about it like like drinking water from a fire hose.  You will get wet, but your thirst will not have been quenched.  By the end of the week, I had taken seventy-seven pages of notes!

The Class

While the class followed a formal curriculum, you are encouraged to ask questions that deviate from the planned outline.  Paul and Kimberly each have the requisite knowledge to answer any question to any level of depth.  Internals and architecture are their respective sweet spots.

They really work hard to foster an open environment where the participants feel comfortable with one another and are willing to open up and share their experience and knowledge.  There will be times when the answers and learning come from the other participants.  When this happens, Paul and Kimberly transcend the role of instructors and become facilitators; guiding us all on a journey of SQL Immersion.

During the breaks, we were able to ask follow-up questions or to dive deeper into an impromptu architecture sessions.  On multiple occasions, I was able get advice specific to my environment from both Kimberly and Paul.

The Food

Ask any teacher and they will tell you the importance of a good breakfast for learning.  If you have ever been to training in a strange city, you know how difficult it can be trying to get a good breakfast.  Between getting from the hotel to the training center and finding a place to eat; either you end up late to class, or you skip breakfast altogether.  In the end, your learning suffers.  Then comes lunch; you have to go find a place to eat, rush through, and still come back late.  Lather, rinse, repeat.

By providing a quality breakfast and lunch on site, all of those problems are removed.  Plus you get extra time to bond with your classmates and talk some shop.  These truly became valuable times where we were able to discuss stuff from class, trade stories, or share some techniques with one another other.

The Camaraderie

Goose Island

After each day’s formal class ended, there was an informal session where the learning continued.  The first night, we had an ice breaker session.  We all met at the hotel bar to have a drink, loosen up, and really get to know each other.  This had the effect of making everyone comfortable with one another.  As the week progressed, we deepened our trust with one another, and were able to ask riskier questions and even take a stab at answering a few ourselves.

After our socializing, one of the locals organized a Portillo’s run.  Portillo’s is a local chain which serves Chicago style hotdogs and Italian Beef’s.  I gotta say, these were some of the best dogs I’ve ever had; I am a convert.

The second night, the Chicago SQL Server User Group meeting was hosted on site.  First, Kimberly ran a question and answer session from the audience.  The coolest part for me, was the realization that I could answer many of the questions myself after being in her class for only two days.  Afterwards, we were treated to a demo session of SQL Sentry’s Plan Explorer.  This is a free tool which gives you a much more useful view of Query Execution Plans than SSMS.  It renders the execution plan in a much better fashion, and allows you to sort the various pieces by relative cost, IOs, etc.  This allows you to drill down to the problem area very quickly.

On the third night, they took a group to the local Microsoft office to see a presentation by Andy Leonard on SSIS.  I must admit I opted out of this excursion.  By midweek, by batteries needed a little recharging, plus I had seen his presentation during SQL PASS.

Thursday Free4All

For the forth night, Kimberly and Paul opened up the floor for the students to have a go at presenting some mini-sessions.  We got to learn about survival skills for being a DBA, gained some insight from the perspective of a software vendor, and saw how to use SSDs in an office with a limited budget.  I think is a wonderful opportunity to get some experience with presentations and to get some feedback from the masters.

Friday, we had some goodbye socializing.  It was a sad day, saying goodbye to the friends you have made during the week.  I think it speaks volumes when you consider how bummed we all were to have to leave and return to the real world.

The Location

Chicago

The Performance and Internals Immersion Event was held at the Oakbrook Terrace Hilton Suites just outside of Chicago from May 2nd through the 5th.  The hotel and rooms were were very nice, and we received a great room rate courtesy of SQLskills; which included free Wi-Fi access.  The hotel bar had some excellent local brew on tap, Goose Island’s Honker’s Ale.  There was also a nice workout room, pool, and hot tub which allowed you to work off any stress and relax.  The hotel staff was very nice and were ready to help with any requests.  If you wanted to explore the surrounding area, a shuttle was available that could take you to some local shopping or the commuter train station in Elmhurst.  From there, you could ride into downtown Chicago; $4 one way, $7 round trip.

Rick Bayless' Xoco

Chicago is a wonderful city with many attractions and friendly people.  Some fun excursions include the Navy Pier, Canal Boat Architecture Tour, Lakeshore Drive, and Steve’s Segway Tours.  There are tons of great restaurants, as well.  One of my highlights was dining at Rick Bayless’ Xoco.  I’m a huge fan of his Mexico – One Plate at a Time series on PBS.  Thank you to Brent Ozar (b | t) for recommending the restaurant to me.

Depending on the time of year, be sure to bring a sweater or jacket as Chicago can get a little cool. Especially if you’re coming from Texas.  Most days were very pleasant with clear skies.

The Cost

The quality of education you receive from SQLskills is far and away superior to any you will receive from the standard Microsoft classes.  What is interesting, is that they both cost the same.  A one week Microsoft class will set you back three large, assuming you don’t have any vouchers; SQLskills charges the same price.  On top of that, they feed you a very high quality breakfast and lunch.  You get extra night-class sessions if you so desire.  You get to network with a variety of Senior DBAs from across the industry and planet.  And, you can even get some one-on-one advice from Kimberly Tripp and Paul Randal.

The Executive Summary

This is not a class for the beginner Database Administrator.  However, if you are mid to senior level, then this is where you want to be.  You will be in a class taught by the best, surrounded by the best.

T-SQL Tuesday is a rotating blog series where a topic is chosen by the host, and all the participants write an article on that topic.  This month, T-SQL Tuesday is being hosted by Bob Pusateri (blog | twitter) and the topic is Common Table Expressions.

Common Table Expressions

I must admit that I am a fairly recent convert to using CTEs, or Common Table Expressions.  I learned about CTEs while preparing for Exam 70-433, SQL Server 2008 Database Development.

In fact, when I first studied the topic, it went right over my head.  Looking back on it, I think they were so sublime that I didn’t get it at first.  The way I look at it is, a CTE is basically a mini-view that you can embed directly in your queries.

Once I grokked what CTEs were, I have been applying them in numerous situations.  After I worked out a few simple examples to learn the ins and outs of CTEs, I dove right in and developed one that I use on an almost daily basis.

Buffer Pool

As a production DBA, I am constantly keeping tabs on the internals of my SQL Servers.  One area that I am particularly interested in is the Buffer Pool.  How big is it?  How much is each database using?  How many Dirty Pages do I have?  You get the idea.

I have seen various scripts from numerous sources that broke down the Buffer Pool by Database, Clean Pages, and Dirty Pages; but always as separate queries.  It bugged me to have to run multiple scripts to get my answers, so I set about to see if I could solve my problem using CTEs.  I figured if I could write a useful script using CTEs, then I should be able to answer any questions on the exam regarding this topic.

I must admit, the inspiration for the base SELECT statements for this script came from my readings of Professional SQL Server 2008 Internals and Troubleshooting.

So, without further ado, here is my script…


-- T-SQL Tuesday #18
-- CTEs - Common Table Expressions

-- define the data cache CTE
with dc as (
	select case database_id
		when 32767 then 'ResourceDB'
			else db_name(database_id)
		end as 'Database',
		count(*) * 8 / 1024 as 'Cached Size (MB)'
	from sys.dm_os_buffer_descriptors
	group by db_name(database_id), database_id
),

-- define the dirty pages CTE
dp as (
	select db_name(database_id) as 'Database',
		count(page_id) * 8 /1024 as 'Dirty Pages (MB)'
	from sys.dm_os_buffer_descriptors
	where is_modified = 1
	group by DB_NAME(database_id)
)

-- link the data cache and dirty pages CTEs together
select dc.[Database],
	dc.[Cached Size (MB)],
	coalesce(dp.[Dirty Pages (MB)], 0) as 'Dirty Pages (MB)'
from dc left join dp
	on dc.[Database] = dp.[Database]
order by dc.[Cached Size (MB)] desc;
go

Nothing too fancy here, just a basic everyday script that lets me know how much buffer pool each database is taking up, and how many pages are dirty.  While I realize it would be much cooler if I were calculating the coefficient of thermal expansion with my CTE, but being as I wrote this script on Valentine’s Day, I saved the thermal expansion for later in the day.  🙂

Plan Cache

After my initial success, I went on to write several more CTE queries for various administrative tasks.  Additionally, I started using CTEs as a way to stitch together queries in a horizontal fashion, instead of vertically by using UNIONs.

Along with the Buffer Pool, another area that I am constantly examining is the Plan Cache.  One particular slice that I like to analyze is how much memory is being used wasted on Single-Use Adhoc Query Plans.

One way I have done this is to run several queries and UNION them together.  This way I can glue my intermediate results together into one results set.  From here, I often import the results into Excel for Executive Formatting.


-- use UNIONs to query one server
-- vertical formatting

-- all plans
select 'All Plans' as 'Data Point',
	COUNT(*) as 'Count',
	sum(cast(size_in_bytes as bigint)) / 1024 / 1024 as 'MB'
from sys.dm_exec_cached_plans

union

-- adhoc plans
select 'Adhoc Plans' as 'Data Point',
	COUNT(*) as 'Count',
	sum(cast(size_in_bytes as bigint)) / 1024 / 1024 as 'MB'
from sys.dm_exec_cached_plans
where objtype = 'Adhoc'

union

-- single-use adhoc plans
select 'Single-Use Adhoc Plans' as 'Data Point',
	COUNT(*) as 'Count',
	sum(cast(size_in_bytes as bigint)) / 1024 / 1024 as 'MB'
from sys.dm_exec_cached_plans
where usecounts = 1
and objtype = 'Adhoc';
go

What I did next, was to rewrite the queries using CTEs, which allows me to format the data Horizonally instead of Vertically.  Also, it made it easier for me to query across multiple servers and have the results formatted much closer to my final Excel format.

You may see my CTE version of this code by viewing yesterday’s blog post.

Conclusion

So there you have it.  Using Common Table Expressions to stitch and glue T-SQL together.  Now head on down to Hobby Lobby and ask them where the CTEs are.

My original plan was to publish this article on April 18th in celebration of Tax Filing Day.  However, life has a way of happening, so I filed IRSQL Form 1433 and applied for an extension.

Say Brother, can you spare me some Cache?

Have you used up all of your Plan Cache?  Would you like a refund?  Are ad-hoc queries bloating your Plan Cache?  How can you tell?  What are ad-hoc queries?  What’s a Plan Cache?

Ad-hoc Queries

Ad-hoc queries are TSQL queries that are issued directly against the database.  Typically, the query text is created directly in the application, then sent directly to the database for the data.  This is very common in web-based applications such as ASP or PHP.  However, you also see this with traditional, desktop application written in C/C++ or Java.

Contrast this with Stored Procedures.  Similar to procedures in other programming languages, Stored Procedures can be thought of as functions, methods, or procedures that are internal to SQL Server.  They are written in TSQL but stored within SQL Server.  The first time a Stored Procedure is executed, SQL Server parses and compiles it, and stores the resultant Execution Plan in the Plan Cache.  The Execution Plan tells SQL Server how it is going to get the data to satisfy the request.

The reason you want to cache Query Plans is that if you run a query once, chances are you will run it again.  Some queries may run thousands of times during the course of a day.  It all depends upon the application.

Plan Cache

The Plan Cache is the area of memory where SQL Server stores the Execution Plans for any queries or stored procedures.  Compiling and caching the execution plan does take a few extra CPU cycles away from the server, so why would you do it?  The next time that query or stored procedure is run, SQL Server already knows how to get the data in an efficient manner.  As your application scales, and your query or stored procedure is being run hundreds or thousands of time, this helps the execution to be a fast as possible.

Single-Use Plans

The goal with caching Query Plans is for the execution count be as high as possible.  This means we are getting the most bang for our buck.  However, if you have lots of single-use plans, then you are wasting CPU cycles calculating the plans, and you are wasting memory by storing them.

Every CPU cycle that is wasted compiling and caching a single-use Query Plan is a CPU cycle that could have been doing some work for your application.  Every byte of memory that is wasted storing a single-use Query Plan is memory that could have been used somewhere else.

If you have a high number of single-use plans in your cache, this could be contributing to high CPU utilization, excessive IO, or low memory on your server.

How to Find Ad-hoc Queries and Single-Use Plans

One way to find out if you have ad-hoc queries hitting your database is to ask the development teams if they have any ad-hoc queries in their code.  There are a couple of problems with this approach.

For one, they may not be aware if they are using ad-hoc queries.  If you have a large application, each developer may only familiar with a small part of it.  Also, if you have inherited an existing application, it may be difficult or impossible to wrap your head around the entire code base.

Another scenario is that the developers may be aware of some of their ad-hoc queries, but may underestimate the number of times they are being called, or they may not realize the impact they have upon the system.

As better approach is to ask SQL Server to tell you what sort of plans it has cached.

sys.dm_exec_cached_plans

SQL Server provides us with a Dynamic Management View, sys.dm_exec_cached_plans which lists out every Execution Plan that is currently stored in the Query Cache.  At the most basic level, you can query this DMV to see the details on every Query Plan in your system.


-- basic query
select * from sys.dm_exec_cached_plans

By itself, this is not very useful. Of course, like all SQL queries you can add various WHERE clauses or aggregates in order to make it more meaningful for you.

So, how much are you wasting?

The way I approached this problem was to calculate how much memory the entire Plan Cache was using, then compare that to how much the Single-Use Adhoc Plans were using.  Once I had those numbers, I did some quick math to figure out what percentage of my Plan Cache was going to Single-Use Adhoc Plans.


-- check percentage of the plan cache that is wasted on single-use adhoc plans

-- use a CTE to check across multiple servers
-- horizontal formatting
-- all plans
;with all_plans as (
	select @@SERVERNAME as 'ServerName',
		COUNT(*) as 'All Plans Count',
		sum(cast(size_in_bytes as bigint)) / 1024 / 1024 as 'Total MB',
		avg(cast(size_in_bytes as bigint)) / 1024 as 'Average KB'
	from sys.dm_exec_cached_plans
),

-- adhoc plans
adhoc_plans as (
	select @@SERVERNAME as 'ServerName',
		COUNT(*) as 'Adhoc Plans Count',
		sum(cast(size_in_bytes as bigint)) / 1024 / 1024 as 'Total MB',
		avg(cast(size_in_bytes as bigint)) / 1024 as 'Average Bytes'
	from sys.dm_exec_cached_plans
	where objtype = 'Adhoc'
),

-- single-use adhoc plans
	single_adhoc as (
	select @@SERVERNAME as 'ServerName',
		COUNT(*) as 'Single-Use Adhoc Plans Count',
		sum(cast(size_in_bytes as bigint)) / 1024 / 1024 as 'Total MB',
		avg(cast(size_in_bytes as bigint)) / 1024 as 'Average Bytes'
	from sys.dm_exec_cached_plans
	where usecounts = 1
		and objtype = 'Adhoc'
)

-- query the CTEs

-- geek version, show all the data
select ap.[All Plans Count], ap.[Total MB], ap.[Average KB],
	ah.[Adhoc Plans Count], ah.[Total MB], ah.[Average Bytes],
	sa.[Single-Use Adhoc Plans Count], sa.[Total MB], sa.[Average Bytes],
	cast(cast(sa.[Total MB] as numeric) / cast(ap.[Total MB] as numeric) * 100 as numeric(5,2)) as 'Percent Wasted on Single-Use Adhoc Plans'
from all_plans ap
join adhoc_plans ah
on ap.ServerName = ah.ServerName
join single_adhoc sa
on ah.ServerName = sa.ServerName

I also have an Executive Version, which shows the Percentage Wasted without adding in all those confusing details.  Replace the Geek Version with this one when you query the CTEs.


-- executive summary, show how much memory is being wasted on single-use adhoc plans
select ap.[Total MB] as 'All Plans MB',
	sa.[Total MB] as 'Single-Use Adhoc MB',
	cast(cast(sa.[Total MB] as numeric) / cast(ap.[Total MB] as numeric) * 100 as numeric(5,2)) as 'Percent Wasted on Single-Use Adhoc Plans'
from all_plans ap
join adhoc_plans ah
on ap.ServerName = ah.ServerName
join single_adhoc sa
on ah.ServerName = sa.ServerName

Getting Buy In

If you encounter resistance to changing this setting, run this query against multiple servers and save the results in Excel.  Bosses love Excel; it helps them think.  🙂

Even better, write up a simple Word Document or PowerPoint Presentation and tie it back to the business or the budget.  That will make an impression.

How much did you spend on memory when you built the server?  Do you see evidence of memory pressure on your server?  Are some of your key business processes running slow because they are not able to get enough memory.

One technique I have used is to put your conclusion first.  Then, lead them along the path that led you to your conclusion.  Put in a few key graphs and screenshots, and voila!  Many managers will not read beyond the first slide or paragraph so you need to work on getting your core message conveyed in a very brief space.

How to fix it

The fix is quite simple: optimize for adhoc workloads.


-- show advanced options - enables all options to be seen / set
use [master]
go
exec sp_configure 'show advanced options', 1
go
reconfigure
go

-- check to see if it is turned on
sp_configure 'optimize for ad hoc workloads'
go

-- optimize for ad hoc workloads
sp_configure 'optimize for ad hoc workloads', 1
go
reconfigure
go

-- verify that it has been set correctly
sp_configure 'optimize for ad hoc workloads'
go

This is an Instance Level setting that tells SQL Server not to compile and cache the Query Plan the first time it encounters an Adhoc Query.  Instead, it compiles a Plan Stub.

The Plan Stub is a small identifier that allows SQL Server to recognize that a particular plan has been looked at before.  This way, if it does end up getting multiple uses down the road, SQL Server is able to compile and cache the plan as normal.

For many of my servers, the Plan Stub was an Order of Magnitude smaller than the size of the full Query Plan; 320 bytes versus 25,000k-35,000 bytes for the average Single-Use Adhoc Query Plan.  Of course, your mileage may vary.

Make it so, Number One!

Something to keep in mind, is that you will not be able to predict or control what SQL Server will decide to use its newfound memory on.  Kimberly Tripp (blog | twitter) has an excellent article which discusses some of the behaviors you may observe.

Conclusion

Taking a look inside your Plan Cache can be an illuminating experience.  While you may not get any money back, it is worth looking into.

Sometimes, you want to pull the Query Execution Plans for any currently running processes on SQL Server.  I use a fairly common method that you may have seen before.

Query Execution Plan

Basic Query

Let’s begin begin with a basic query against sys.dm_exec_requests:


-- basic query

select * from sys.dm_exec_requests    -- one row for each request on the server
go

This query returns one row for every request that is currently running on SQL Server.  Technically, everything you need is already here.  But for us mere humans, it helps to dress up the query a little bit.

Filter System Processes and Observer Effect

The first thing I like to do is filter out the systems processes.  The vast majority of the time, I am focused on the user queries, so I do not need to look at them.  In SQL Server, the system processes will have and ID from 1 through 50.  By adding a WHERE clause for Session ID greater than 50, we can easily filter out any system processes that are running on the server.

The second thing I like to do is filter out my own process.  Having your own requests mixed in with the user requests can muddy up your results.  This is known as the Observer Effect.

Here is the updated query:


-- filter out system processes and the observer effect

select * from sys.dm_exec_requests
where session_id > 50                -- filter out system processes
and session_id <> @@SPID;            -- filter out myself
go

Design Patterns

In the software development world, the idea of Design Patterns is a powerful programming technique.  The basic idea is that idea that most programming problems have already been solved before and you can reuse the solutions in order to create better software.

One of the most common SQL Design Patterns you will see is what I call the CROSS APPLY Design Pattern.  This is used to link a DMV to a DMF.

If you review the results of the previous query, notice two columns; sql_handle and plan_handle.  By using these two columns, we can link in the actual SQL command that goes with the request, and the corresponding Query Execution Plan.

Link in the SQL Text

First, let’s link in the SQL Text:


-- link in SQL text

select er.*, st.text                                -- SQL text
from sys.dm_exec_requests er                        -- base DMV
cross apply sys.dm_exec_sql_text(er.sql_handle) st  -- link to the DMF
where session_id > 50
and session_id <> @@SPID;
go

This query uses the sql_handle column to link the sys.dm_exec_requests DMV to the sys.dm_exec_sql_text DMF.  Once we have done this, we can now return the SQL command that was issues as part of the request.

Link in the Execution Plan

By applying the same technique that we used to obtain the SQL Text, we can pull in the Execution Plan:


-- link in XML query execution plan

select er.*, qp.query_plan                               -- XML query execution plan
from sys.dm_exec_requests er                             -- base DMV
cross apply sys.dm_exec_query_plan(er.plan_handle) qp    -- link to the DMF
where session_id > 50
and session_id <> @@SPID;
go

Link in Both

Now, let’s put both of these together and grab the SQL text and the Query Plan:


-- link in both

select er.*, st.text, qp.query_plan
from sys.dm_exec_requests er
cross apply sys.dm_exec_sql_text(er.sql_handle) st       -- SQL text
cross apply sys.dm_exec_query_plan(er.plan_handle) qp    -- query plan
where session_id > 50
and session_id <> @@SPID;
go

Simmer Down

The base DMVs often give you quite a lot of columns to parse through.  One technique I like to use is to pare down the resulting columns so that I only see the ones that are most useful to me.  Here is an example of one version of this query that I run almost daily.


-- simmer down

select er.session_id, es.host_name, db_name(er.database_id) as 'database', er.status,
    er.command, er.blocking_session_id as 'blocker',
    er.wait_type, er.wait_time, er.wait_resource, er.percent_complete,
    er.cpu_time, er.total_elapsed_time, er.total_elapsed_time - er.cpu_time as 'signal time',
    er.reads, er.writes, er.logical_reads, er.granted_query_memory * 8 as 'query mem kb',
    st.text, qp.query_plan
from sys.dm_exec_requests er
join sys.dm_exec_sessions es
on er.session_id = es.session_id
cross apply sys.dm_exec_sql_text(er.sql_handle) st
cross apply sys.dm_exec_query_plan(er.plan_handle) qp
where er.session_id > 50
and er.session_id <> @@SPID
order by er.session_id;
go

A good exercise is to look through the documentation for the various columns and then add or remove the ones that are important to you.  The columns and information that are useful to you will most likely differ from what I have listed here.  Over time, your own experience will allow you to modify this code and tailor it to your needs.

Mathemagical

You may have noticed I did a little math on one of the columns.  granted_query_memory returns the number of pages that have been allocated to the query.  Remember that in SQL Server, pages are 8k.  So, I multiplied granted_query_memory by eight and renamed the result to something more meaningful to me.  Your preference might be to think in pages, or perhaps you prefer base-5, or some other strange numerical system.  Myself, I like kb.

Conclusion

Looking at the currently running requests and pulling their Execution Plans is an essential skill for a DBA.  By digging in and looking at what is going on inside the server, you can get a better idea as to what the root of your performance problems may be.

Furthermore, the CROSS APPLY Design Pattern is one of the more powerful TSQL programming tricks available to you.  Try to understand how it works and leverage it on new DMVs and DMFs.  I still remember the day the light suddenly came on for me.  Since then, I am constantly finding new ways to apply this pattern to new areas.

Got Power?

Have you ever experienced a power outage at work?  One that lasted several days?  One that affected the entire state?

Hey man, can you give me a jump?

Once you have, I imagine you will think of disaster recovery in a different way.  Most of the time, SQL Server performs perfectly, without any intervention from you.  However, to get the most out of it, you will need to take a few proactive steps.

I have seen power outages that lasted only a few minutes or even several days.  This could be caused by something as simple as someone tripping over a power cable, or a frozen pipe in a power plant a state away.

If they last long enough, your UPS could run out of power.  Or, if you are using a back-up generator, it may require refueling.

No matter how it happens, when SQL Server loses power, you have the possibility of corruption occurring.  You want to have a basic strategy in place to detect and deal with this.

Data Center

If you work in a large office, you may not normally have Data Center access.  However, when all hands are on deck for disaster recovery, you may spend significant time in there.  They tend to be cold, so bring a jacket or sweater.  Keep an extra one handy in your office so that when you need it, you will have it on-hand.

Did your servers come up before the network or switches?  If so, you may have to use a crash cart and the console to log-in to the servers.  This involves plugging a mouse and keyboard directly into the server racks and logging in.  Hopefully, the servers are clearly labeled so you will know which one is which.  It is also handy to have a rolling cart, a.k.a. Crash Cart, to hold the mouse and keyboard.  A rolling chair is nice, too.  Spending hours on end crouched down or bent over at weird angles can be hard on your body.

SAN Considerations

Did you servers come back online before the SAN did?  If so, you may need to start SQL Services manually.  Once the SAN is back online, you can use SQL Server Configuration Manager to start SQL Server.  Right-click on your instance and select Start.  Depending on your situation, you may need to start some of the other services, as well.  For example, SQL Server Agent (for your backup jobs) and SQL Server Browser (for your named instances).


SQL Server Configuration Manager

It is possible for write caching to come back to haunt you.  While it does provide a performance boost, when you are facing a disaster recovery scenario, it may not seems like a good idea anymore.

If you do not have a battery backup for your write cache, you may have data corruption and inconsistency in the event of a power failure.  If you do have battery backup, how long does it last, how often are you checking and changing the batteries?  These are not questions you want to consider when your manager is asking you about recovery time.

If you would like to know more about the storage systems underneath SQL Server, I highly recommend reading some of the stuff Wesley Brown (blog | twitter) has put out.

Starting SQL Server in Single-User Mode

Sometimes you need to start SQL Server in single-user mode to do some advanced troubleshooting.  You can do this by modifying the Startup Parameters in SQL Server Configuration Manager.

Right-click on your instance and select Properties.  Go to the Advanced tab, and look at the entry for Startup Parameters.  Place the following code at the beginning of the line: -m;

Be sure to include the semicolon to separate it from the rest of the commands.

Properties, Advanced Tab

Dedicated Administrator Connection

The Dedicated Administrator Connection is a system backdoor that allows you to connect to SQL Server, even when it becomes unresponsive to client connections.  This is a good one to file away in case you ever need it.

To connect, you must be logged onto the server that is hosting SQL Server.  You are not allowed to make a Dedicated Administrator Connection across the network.  When you open SQL Server Management Studio, open a new Query Window and specify ADMIN: in front of the server name.

Dedicated Administrator Connection

Note: if there is already a DAC in use, your connection will fail.

Preventative Maintenance

Backups

Be sure to use WITH CHECKSUM in your backup commands.  You are taking backups, aren’t you?  As a general rule, I like to take FULL backups once per week, DIFFerential backups daily, and LOG backups every five minutes.  You will need to consider your business requirements and devise a backup strategy that satisfies your needs.

BACKUP WITH CHECKSUM

When writing out a backup, the WITH CHECKSUM option makes SQL Server verify each page before it is written to the backup file.  If an error is detected, the backup will cease.  If you want the BACKUP to continue after an error is found, use the CONTINUE_AFTER_ERROR option.

PAGE_VERIFY CHECKSUM

When the PAGE_VERIFY_OPTION option is turned on, when each page is written out to disk a checksum is calculated and stored with the page header.  Then, when the page is subsequently read from disk, the checksum is calculated and compared to the value stored with the page header.  If the values are not the same, an error is logged to both the SQL Server error log and the Windows Application Event Log.  Look for Error Message 824.  This indicates a problem with your IO system.

One common misconception about this option, is that is automatically checks all of your pages.  This is incorrect.  The check is only made when a page is written to disk and then read back off of it.  Once you turn this on, it may take some time until all of your pages go through this cycle.

Schedule DBCC CHECKDB Regularly

Either use SQL Agent Jobs or the Maintenance Plan Wizard to run DBCC CHECKDB at least once per week on all of your databases.

If you have large databases, or 24/7 activity, it may not be feasible to run this on your Production systems.  If this sounds like your situation, there is a way out.  Do you have DEV and QA systems?  Do they get refreshed from Production regularly?

Run DBCC CHECKDB on your DEV and QA systems each time your refresh them from Production.  If you detect an error, raise the red flag immediately, and prepare to run DBCC CHECKDB on your production system.

Some DBA Commands To Know

DBCC CHECKDB

DBCC CHECKDB performs a variety of physical and logical checks on every object in your database.

Full version, this can take a long time depending on the size of your database.


dbcc checkdb(YOURDBNAME)
with all_errormsgs, no_infomsgs;
go

Some quicker versions, which don’t take as long:


dbcc checkdb(YOURDBNAME, noindex);
go


dbcc checkdb(YOURDBNAME)
with physical_only;
go

Repairing Problems

If DBCC CHECKDB detects some errors, it will make some recommendations at the end of its messages.

However, Microsoft recommends that you first try to restore from a good backup.  This way, you can try to compare the data from the backup and the corrupted database and decide which copy of the data to keep.  Most likely, this will be a long and painful process, but it is preferable to blindly losing production data.

If this is not an option, then you can try using the repair options listed by DBCC CHECKDB.  Note: This should be considered the last resort.

Before running a repair command, be sure to make a FULL backup.


backup database YOURDATABASE
to disk = 'c:\YOURDATABASE.bak'
with format, checksum, stats=1;
go

You must set the database into single-user mode.


alter database YOURDATABASE
set single_user;
go

Depending on the options you choose, you may need to set the database into emergency mode, as well.


alter database YOURDATABASE
set emergency;
go

Then, you can try some of the repair options.  Try this one first, it can fix some problems with your indexes.  This is not able to fix issues with FILESTREAM data.


dbcc checkdb(YOURDBNAME, repair_rebuild);
go

If that does not work, then you can try this one next.  You may experience some data loss with this one.  This should be your last resort.


dbcc checkdb(YOURDBNAME, repair_allow_data_loss);
go

Warning: this option will result in data loss.  Try this only after all other options have been exhausted.

Afterwards, set your database back to multi-user or normal, and take another FULL backup.


alter database YOURDATABASE
set multi_user;
go

alter database YOURDATABASE
set online;
go

backup database YOURDATABASE
to disk = 'c:\YOURDATABASE.bak'
with format, checksum, stats=1;
go

Be sure to take a FULL backup.

For more information about the DBCC CHECKDB command, please refer to the maser, Paul Randal (blog | twitter).

Summary

A power failure can be a stressful thing to deal with.  Everyone wants the systems back online as soon as possible.  However, it is important to remain calm and be sure to run some basic consistency checks on the databases before allowing users back online.

Help yourself sleep better at night.  Be sure to take regular BACKUPs, use CHECKSUM, and run DBCC CHECKDB weekly.

I attended my first SQL Saturday this past weekend in Houston. SQL Saturday is a free event sponsored by PASS, the local SQL Server user group (HASSUG), and some vendors. Well, it was almost free. I did have to pay ten dollars for lunch. It was a delicious selection of eats from Hinze’s BBQ.

Schwag:The real reason we come…
In some ways, I found SQL Saturday to be more accessible than a full SQLPASS conference. SQLPASS can be a bit overwhelming; after the first couple of days, your brain starts to get mushy from all the content that has been thrown at you.

Since this was only one day, I was able to absorb all of the sessions I attended. You come away with tons of great ideas, tips, and tricks that you are ready to apply in your shop once you get back home. By keeping the total number of sessions small, I feel you increase the chances of actually following-up with the knowledge you have gained.

Sessions

There were five tracks with a total of forty-two different technical sessions from which to choose. The tracks were divided among Application Development, Business Intelligence, Database Administration, General, and PowerShell.

Each session I attended was full of great information that I can take back and use at work. The speakers were all enlightening and entertaining. I was able to ask questions, trade emails, and get copies of their presentations.

Presenters

The speakers were very engaging, and in some cases, downright entertaining. Several of the presenters were names I recognized from SQLPASS 2010 in Seattle. Either as presenters there, or as people I met during the various social events. So I knew I was in for a treat.

The day began with with Dean Richards (website) teaching us about VMWare and SQL Server. With more and more systems becoming virtualized every day, this is a good foundation for the future.

I followed-up with hometown hero Wesley Brown (blog | twitter) who got so down on storage systems, I started seeing things in terms of sectors, cylinders, and tracks. I loved his joke about RAID0 not being RAID at all, but rather just ‘AID’… In other words, your resume may need some aid if you use this in production.

Thomas LeBlanc (blog | twitter) was especially hysterical, and I took in a double dose of his stuff, with no fluff. He covered Execution Plans and Business Intelligence. Many times during his presentations, he had the whole room in laughter. I had to ask if he ever did stand-up.

Another stand-out was Jason Wong (website). He took a normally serious topic, performance tuning, and turned it into a rollicking good time with his dry wit and dead-pan delivery.

One of my favorite sessions was presented by Trevor Barkhouse (blog | twitter) on Deadlock Detection. His was a session I recognized from SQLPASS 2010.

Some of the presenters would give mini-quizzes during their presentations and awards prizes to people who got them right. Others had books to give away from some of the sponsors.

Vendors and Employers

Just like any good technical conference, there were plenty of vendors on hand to show you samples of their products, or offer you trial licenses. In some cases, you had the possibility of winning a full license for their software.

Again, since the scale was more manageable, it is more likely that you will actually go back and download the trial software and try them out.

An added bonus was the presence of a few area employers who are in a hiring phase: sparkhound and intellinet. They each had recruiters and technical staff on hand to answer questions about the company, their projects, and what sort of people they are looking for.

Location

This SQL Saturday was held at the Bammel Church of Christ, who was kind enough to provide the facilities for the conference. I have to say, this was the coolest, most modern church I have been in. Amongst the various rooms was the ever-popular Xbox room, complete with a flat screen TV and fluffy couches.

Networking

I got to meet an interesting cross section of SQL professionals from a wide variety of industries. I was surprised at the number of people I met who came from out of state. After the conference, there was an informal dinner held at Outback Steakhouse. This was a great opportunity to socialize and mix with your fellow SQL peeps.

One thing I wish I had done, was attend the Speaker Mixer dinner the night before. Since I was new, and didn’t know anyone, I decided not to attend. However, after meeting everyone on Saturday, and seeing how friendly the group was, I will definitely do this the next time.

At the end of the conference, there was a general meeting for the local user group, HASSUG, where some user group business was discussed, awards given for the volunteers, and a raffle for prizes from the sponsors. Too bad, I didn’t win the Xbox or the iPad. There’s always next time…

Conclusion

When I consider the quality of the education, and more importantly the food, this event was beyond free. It was more like they gave me money. I received quality technical training, networked with some great professionals, and ate some great BBQ all in one day.

My only disappointment was not being able to snag one of those cool shirts that all the speakers and volunteers were sporting. At the end of the conference, they had a few extras to give away, but alas, I was not able to beat the crowds. You would have thought Metallica was giving away free tickets to their next show.

Another opportunity is to volunteer to help with the next SQL Saturday. This entails arriving early, perhaps the day before, and helping to setup the facility, answering questions, or perhaps a more long-term commitment to help organize the whole affair.

If you decide to go to a SQL Saturday, I recommend traveling there the night before. They start at 8:30am and this way you will be fresh and rested; ready to absorb every last drop of SQL awesomeness. Buy using Hotels.com, you can find a wide range of hotels to fit your price range.

Executive Summary

It was like a mini-PASS conference for a fraction of the price; free!

When I first saw the announcement for the SQLskills Immersion Training I was immediately excited.  I have attended some of the stock MSXXXX training courses, and have found them to be OK for a basic introduction to SQL. However, if you are looking for something deeper, or adaptable to your specific questions, then you will likely be disappointed.

I am a SQL Server DBA for a Fortune 500 company where I manage over one hundred Servers and Instances.  I am constantly trying to learn more about SQL Server and become a better DBA.  Often times, it is overwhelming as there is so much to learn; it is difficult to know where to begin.

Too often, the trenches force one to focus in on a specific area or advanced topic just to get through the day.  Being able to go back and focus on the fundamentals of SQL Server and truly understand the internals of SQL Server will give me the knowledge and confidence from which to map out my progression through the rest of the SQL Server Universe.


Every time I learn about a new topic and feel that maybe, just maybe, I have climbed the 1-10 ladder of competency a notch, I realize that there are twice as many things to know than I knew about the day before.

I would love to attend the 5-day Internals and Performance class in Dallas, February 21-25.  Doing so would be an awesome springboard to deepen my knowledge of SQL Server and help to advance my career to the next level.

SQLskills is offering a great contest where you can win a free seat to this very class.  All you have to do is write a letter to Santa about why you will make the best use of the knowledge from the class and you will get your Christmas in February.

Top Ten List

With apologies to David Letterman, here is my Top Ten list of reasons why I want to learn from SQLskills:

10) Breakfast at Cafe Brasil.  Lunch at Cafe Brasil.  Dinner at Cafe Brasil.  Fourth Meal at Cafe Brasil.
9) I’ll have a chance to wear my Cowboy hat and DBA hat at the same time.
8) I love staying up until 4:07am looking at error logs.
7) Dallas in February is way more fun than the Caribbean in March, or Alaska in May.
6) My credit card needs a vacation.
5) Being away for a week, means I can legitimately say “Honey, I would love to help you cut the poodles’ hair, but I have to go learn about SQL Server…”
4) I can sing the lyrics to R.E.M’s Jackson-Dallas bootleg while coming in for a landing.  ‘You should see Dallas from a DC-99.  Dallas is a jewel, you know Dallas is a beautiful sight…’
3) It would be like watching the MCM videos in 3-D, but without the glasses.
2) Who better to learn SQL Server from, than someone who used to be on the development team?
1) I can finally say I paid Brent Ozar with Salmon Meals and Amy’s Fish.  Or was that the other way around?

SQL Saturday #57 Houston

The other day, I registered for SQL Saturday #57 in Houston.  I know what you’re thinking; SQL on a Saturday?  Don’t I have something better to do?  Well, yes, I have plenty of things I would rather be doing.  But I felt this was a fairly unique opportunity that I wanted to take advantage of.

The first time I heard about SQL Saturday was at SQL PASS 2010 in Seattle last year.  I kept seeing people with T-shirts that mentioned SQL Saturday.  I asked some folks about it, and I found out it is an event that rotates locations enabling SQL Server Professionals to get together and learn more about SQL Server.

While at SQL PASS, I signed up for Twitter in order to keep track of all the updates and to be able to see what all the attendees were chatting about during the conference.  Since then, I have been exploring what is out there.  Recently, I saw a posting about the upcoming SQL Saturday in Houston.  Well, since Texas is a relatively small state I decided a road trip was in my future.

After looking at the website and schedule of sessions, I was very impressed.  SQL Saturday offers some very good training on a variety of topics for no cost.  I noticed that several of the speakers were people that I met at SQL PASS; bloggers, presenters, or just some folks I exchanged business cards with.

I am excited about attending my first SQL Saturday.  I think it will be a good way to meet some other people in the SQL community and to learn about more about SQL Server.  Perhaps exploring a few new topics, or getting a deep dive on something familiar.

Either way, it should be a good experience.  If you are interested in learning more about SQL Server, take a look at the SQL Saturday website and see if there is a session near you.