Memory Consumed By SQLServer

Categories: News, Professional, SSC
Tags: ,
Comments: 1 Comment
Published on: February 28, 2011

Have you wondered how much memory was being consumed by SQL Server?  Have you wondered if there was a way to find out that information from a tSQL command?

On occasion it would be nice to be able to find this information.  A command was provided for this very purpose.  Have you heard of DBCC MemoryStatus?

As stated in the MSDN documentation for this command, “The DBCC MEMORYSTATUS command provides a snapshot of the current memory status of Microsoft SQL Server.” (MSDN).  There is a grundle of information that is provided through this command.  I recommend reading the documentation on it to better understand the outputs – otherwise you may completely misunderstand the results.

Sometimes, you may just want to find high-level usage information.  For a snapshot of that high-level information, you could try a query such as this:

[codesyntax lang=”tsql”]


I got the idea for this query online (slightly adapted) from the following blog.  Go check it out.  Once you delve into the result set of dbcc memorystatus, you can quickly see how many possibilities there would be for various scripts based on the results.

Finding Compressed Tables

Categories: News, Professional, SSC
Tags: ,
Comments: No Comments
Published on: February 25, 2011

Have you been working with compression?  Have you inherited a database that may or may not have some tables compressed?  On occasion you may want to know what the compression type being used on a table is.  There is a really easy way to figure that out.

This is also helpful for those of us who may have known this before, but had difficulty finding where they filed the information.

In SQL Server there is an object catalog view that can help you find just the information you seek.  The name of the view is sys.partitions.  To find the compression information is rather simple as well.  If you read the MSDN info about this view, you can quickly determine some easy queries to document the compression settings in your database.

One such possibility is (for instance to simply find tables that are compressed):

[codesyntax lang=”tsql”]


And a little more informative might look something like this:

[codesyntax lang=”tsql”]


As you can see from that last query, I am limiting the results to User Created Objects.  This is just a quick code snippet to reference in case you need it.  Maybe, at a later date, I will venture into some pros and cons of compression as well as the types of compression available for the data in SQL Server.

February 2010 S3OLV Recap

Categories: News, Professional, SSC
Comments: No Comments
Published on: February 22, 2011

For the month of February, I conducted the S3OLV meeting virtually from the confines of home in Utah.  As you may know, I have moved from Vegas back to Utah.  Despite having moved away from Vegas, I am still helping with the User Group in any way that I can.  As long as we can continue to do these meetings virtually and Live, then all works well with that effort.

In February, on last minute request and notice, Randy Knight (Blog | Twitter) volunteered to present to the group.  Randy has given this presentation a few times at various SQL Sat Events and there seems to be good interest in the topic.  Randy is trying to educate us in some of the nuances and differences in  lingo between two main DBMSs (Oracle and SQL Server).  He did a great job of it too.  We had some in attendance with a preference for Oracle as well as those who preferred SQL Server.  It seemed to go over well – with both groups.

We had a decent showing in light of the lateness of the notifications and invites.  I am pleased with the turnout, understanding that I failed to get the word out sooner.  I am still working on that.

The meeting was recorded and is available for review.  Randy included a slide in his presentation referring attendees back to to rate his performance – please do that if you watch the video.  It is extremely helpful to the presenters.  If you would like to view the presentation recording, you can do so here (  The recording will be available until Feb 10, 2011.

As for upcoming news, we have a speaker on tap for March.  It is another MVP (the third to present to S3OLV, over the past year, that is not a local).  People should get quite a bit out of the presentation that he will be doing.  I will get the invites and news on that event out shortly.

SQLServerPedia Blogger Awards

Comments: No Comments
Published on: February 16, 2011

Several moons ago I learned that I had won one of the categories for the 2010 SQLServerpedia Blogger Awards.  You can read about that here.  Of all of the promises made in that post, I have yet to fulfill two of them (one is being fulfilled now).  I can’t fulfill the other promise because I do not have a cubicle at work – just an open work area.  That said, I am prominently displaying my trophy in my work area.

As you can see in the image, I have displayed my trophy (though the photo does not prove that it is at work).

Again, I am thankful to have won and I will continue to blog and try to put out useful information to the blogosphere.

Thanks to all who voted for my entry (which was really just one part of a series).

P.S.  I have a really good reason for this post coming out so late too.  My award was delivered in the middle of me moving from Vegas back to Utah.

PASS Summit Volunteers Needed

Tags: ,
Comments: 3 Comments
Published on: February 16, 2011

Well, we are coming around into a new year.  With this new year there is once again PASS Summit preparations that are well under way.  Time has come again for people to volunteer to help with PASS Summit.  The call has gone out!!  Allen Kinsel has blogged about it here.

For those that haven’t volunteered or helped in the past, it doesn’t require too much and there are many ways in which you can help.  Assistance is needed from abstract review to speaker ratings to some database work to being an usher during the Summit.  PASS has put together a little survey to help you in volunteering for the Summit 2011.  You can find the survey here.

So what do you get by volunteering to help at PASS?  You get a sense of accomplishment for having helped, in whatever miniscule way, to put on a pretty big Conference for the best Community out there – the SQL Server Community.  I liked helping out last year and have volunteered again.  I hope to be selected and wouldn’t mind doing something different this year.  On the flip-side, I wouldn’t mind doing the same things I did last year.

I know, this wasn’t much of a motivational speech.  Hopefully this works better for you – JUST DO IT.

Feb 2011 S3OLV Meeting

Tags: ,
Comments: No Comments
Published on: February 9, 2011

I am getting this out extremely late.  I seriously have good excuses for that.  Due to my location and Charley feeling ill, we will be doing the S3OLV UG meeting entirely virtual this month.

Here is the note on the invite:

Please join us this month for a Virtual UG meeting.  We will not be meeting at The Learning Center.

Livemeeting Details:

Follow these steps:

  1. Copy this address and paste it into your web browser:
  2. Copy and paste the required information: Meeting ID: 89TDFP

Randy Knight of SQLSolutions Group will be presenting.  He has some insight to help both the Oracle and SQL DBA.

Randy Knight (MCITP) is an IT Professional who has been working with Microsoft technology for over 20 years, focusing on SQL Server for the last 14. He has worked in a variety of settings, including six years as a Database Architect for, one of the largest and most successful dot coms.

We welcome anybody who is able, to attend this virtual meeting.

The meeting will be held February 10, 2011 at 6:30 PST.

I mentioned that Randy will be teaching us some useful info for both SQL and Oracle DBAs.  Here is the abstract for the presentation he will be giving.

In today’s enterprise environments, it is becoming increasingly necessary to integrate data from a variety of sources. As SQL Server continues its march into the enterprise, the days of focusing 100% on one platform are over. At a bare minimum, we need to be able to communicate with DBA’s for other platforms. Terminology as simple as Instance and Database mean very different things in Oracle than they do in SQL Server. In this session, we will compare and contrast the two platforms in terms of features and terminology. We will also discuss some of the best practices and pitfalls when integrating the two.

T-SQL Tuesday #15 DBA Automaton

Comments: 3 Comments
Published on: February 8, 2011

I have been incognito for the last couple of weeks and nearly missed TSQL Tuesday this month.  If it weren’t for somebody pinging me requesting the link to the list of upcoming hosts, I would have missed it entirely.  The topic merges well with the work I have been doing for the past few weeks.  In fact, there are so many things that DBA’s do on a regular basis to automate things – there should be plenty of items to cherry pick for a topic.  Unfortunately, that doesn’t make this topic any easier for me.  My decision process for this is partially based on thinking through what might be unique without having read what others may have written.  (While thinking about reading blogs, it would be nice if there was an automated method to ingest all of the blogs into my head without having to iterate through them one at a time – manually.)

This month the party is hosted by Pat Wright (blogtwitter).  Pat has asked us to describe some of the things we automate – or some of our automation that we have implemented.  Often, we hear about DBA’s automating everything under the sun.  Why?  It simplifies the job and creates time to work on other projects.  With all of the automation, I wonder if DBA’s are related to Hephaestus in any fashion.  If we had our way, it seems that our databases would be…Automatons.

A Lesser Automation

Now that I have rambled for a good bit, I guess it is time to get to the meat of the topic.  First, we need to understand automation.  So, what is automation?

  1. The automatic operation or control of equipment, a process, or a system.
  2. The techniques and equipment used to achieve automatic operation or control.

A closely related word to automation is:

  1. Computerization – the control of processes by computer

In other words, for a DBA, automation is the implementation of a process or control for the computer to operate without the DBA doing the work.  This is typically something that is repetitive or menial or tedious or frequently done.  But that is not always the case.  This can also be something that is fun and/or only done once or twice a year.

I have one of those cases where I may use the automation even less often than once a year, or maybe it could come about more frequently.  It all depends on the needs of designing and testing new databases.

This process is to help in properly sizing the database before the database is finally released to production.  With good project requirements, you may have a good idea of what the fields and sizes of those fields should be.  In some cases, you will be getting data from an external source in some fashion or another.  This data does not always come with storage requirements or data size parameters.  You can make guesses at it by looking at the data – but sometimes, something more is required.

It is when more is required that this script comes in a bit handy for me.  The script is ugly, but it does the deed.  The base idea is to retrieve the data length for a sample of data from each of the tables (after import into a staging database).  This is done so I can run statistical analysis on the data later.  And cringe now because I use a nested cursor to get at what I want.

So Here is the script that will load a sample of data from every table in every database for every column and give you the length of the pertinent columns (I have excluded obvious columns such as numeric types and certain LOB types).

And yeah, I rushed through this to get it finished up quickly.  I hope to have more time to delve into it later.  At least with this script, I can load a table with adequate data to be able to generate histograms on the data length/size distribution and then make appropriate sizing decisions based on the statistics.  Be warned – though automated it is slow and should not be run on a production server.

Immersions Training

Comments: 4 Comments
Published on: February 8, 2011

The last week of January 2011, I wrote a blog post entering a contest for free training at the hands of SQLSkills.  Later that week an announcement was made as to the winner(s) of that contest.  Lo and behold I found out that I was one of the winners.  Totally awesome.

What did I win?  I won a week of training at half price.  The training is in Dallas the week of February 21st.  Brent Ozar even came up with some info to show the ROI on this training.  (Yeah, I’ll be bookmarking that page.)

Even with all of the upside, I will be unable to attend this time.  I have other things at this juncture that take precedence for me.  I evaluated the pros and cons and had people encouraging me to attend the training regardless of the cons.  Some of the conflicts at this point in time are:

  1. Moving my family and trying to get settled in
  2. Transitioning new work

Those are just too big right now to try to interrupt for a week.  Again, many thanks to Paul, Kim, Brent, and crew for the great opportunity.

Anybody who has the opportunity to attend one of these events really should do it.  My wife and I agree that it is something that is worth me doing.  I will be diving in the immersions training some time in the future.

page 1 of 1

February 2011
« Jan   Mar »

Welcome , today is Tuesday, April 7, 2020