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:
DECLARE @MemTable TABLE
( row_id INT IDENTITY PRIMARY KEY, name VARCHAR(100), VALUE BIGINT );
INSERT INTO @MemTable EXEC ( 'DBCC MEMORYSTATUS' );
SELECT TOP 1 Name,CONVERT(DECIMAL(16,2),VALUE) / 1024/1024 'MBUsed'
WHERE RTRIM(LTRIM(name)) = 'Working Set';
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.
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):
SELECT * FROM sys.partitions
WHERE data_compression <> 0
And a little more informative might look something like this:
SELECT OBJECT_NAME(OBJECT_ID) AS ObjName
,data_compression_desc AS CompressionType
WHERE OBJECTPROPERTY(OBJECT_ID,'ismsshipped') = 0
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.
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 speakerrate.com 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.
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.
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:
- Moving my family and trying to get settled in
- 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.