Database Ghosts

phantasmripRecently you may have seen my first article on this topic over at SQL Solutions Group.  If not, here is the link so you can read that article first before proceeding here.  This article is intended as a first of two part follow-up to that article.

Now that you have read that other article, we can recap it a little bit.

You will recall that in that article, I discussed the presence of Ghosts in your database.  I also discussed that those ghosts are a good thing.  They have some benefits to them, of which is a bit of a performance boost for some operations like the rollback of a delete.

In that article I discussed one method with which you could see these ghost records.  In this article, I would like to share an alternate method to be able to see these ghosts.

In order to explore this alternate means, let’s go ahead and follow the first few steps from the other article to get the setup complete so our second ghost hunting foray can begin.

Now with the setup complete, we should once again confirm that we have appropriate data available for the hunt.  Once again a simple query can suffice to show the data.

Upon execution of the check script, we should see something similar to the following data-set.

datasample

 

Great, the data is confirmed and we should have a wonderful chance to find some ghosts once again.  In the previous attempt, we needed an additional plasma blaster in the form of a trace flag.  In this attempt we will hunt these ghosts without that tool and see what we can find.  Why attempt it in this way?  Well, it is simply because I’d rather not use a trace flag if it is not necessary.  If you recall, that trace flag had a couple of noted effects.  One of the effects was that it turned off the ghost cleanup process.  If I can avoid it, I’d rather leave the containment unit in tact.

Now, due to that clerical error of putting Halloween on the wrong date, we need to follow the prescribed policy to delete records prior to creating the proper records.

Notice that we issued the delete in a transaction this time around.  We are going to leave that transaction open for a bit while we hunt those ghosts.

The first step is to verify that some ghosts might be present.  To do that we should run a query such as the following.

From that query we should see something like the following.

idxstats_ghosts

Once again we are on the right track.  We can further confirm the existence of these ghosts through a little more investigation and monitoring.  Let’s try the trick with the dblog function again.

With that query, we should see something like the following result set.

dblog_ghosts

 

Very cool.  We once again can see that these phantasms are in the database.  We have enough information that we can proceed on to the next stage.  We can pass the PageID into DBCC PAGE in order to investigate the ghosts on the page.  If we use the PageID that is circled with the green in the preceding result set, we can get a better feel for these specters.

And we may see results such as the following.

page_ghosts2

Recall that the log says this page has ghost records on it.  When we check the page with DBCC PAGE we can definitely see that there are ghosts on the page.  This is very cool.  Now, had we tried to check for ghost records on the PFS page we would not be able to see the ghost count like we were able to see by enabling the TF.

Once again we have been able to display the existence of ghosts in the database.  In order to get these ghosts to move on from the database to their afterlife, we merely need to commit the transaction or roll back the transaction.

Stay tuned for the next article in this mini-series about ghosts.  Who knows, we may even have a bonus Halloween article since this is Halloween month.

Part III of the series can now be found here.

Murder In Utah

I am about to set sail on a new venture with my next official whistle stop.  This year has been plenty full of whistle stops and I plan on continuing.  You can read (in full) about previous whistle stops and why they are called whistle stops here.

Suffice it to say at this point that it all started with a comment about a sailing train a few months back.

goldspike

 

Time to sink or sail, so to speak.  SQL Saturday 349 in Utah will mark the next attempt at what I hope to be a repeat performance – many times.  I will be tag-teaming with Wayne Sheffield in this all day workshop event.  The session is one of two all day sessions for the event in Lehi, UT (just south of Salt Lake City).

If you are a DBA or a database developer, this session is for you.  If you are managing a database and are experiencing performance issues, this session is a must.  We will chat with attendees about a horde of performance killers and other critical issues we have seen in our years working with SQL Server.  In short, some of these issues are pure murder on your database, DBA, developer and team in general.  We will work through many of these things and show some methods to achieve a higher state of database Zen.

Description

Join Microsoft Certified Masters, Wayne Sheffield and Jason Brimhall, as they examine numerous crazy implementations they have seen over the years, and how these implementations can be murder on SQL Server.  No topic is off limits as they cover the effects of these crazy implementations from performance to security, and how the “Default Blame Acceptors” (DBAs) can use alternatives to keep the developers, DBAs, bosses and even the end-users happy.

Presented by:

wayneWayne Sheffield, a Microsoft Certified Master in SQL Server, started working with xBase databases in the late 80’s. With over 20 years in IT, he has worked with SQL Server (since 6.5 in the late 90’s) in various dev/admin roles, with an emphasis in performance tuning. He is the author of several articles at www.sqlservercentral.com, a co-author of SQL Server 2012 T-SQL Recipes, and enjoys sharing his knowledge by presenting at SQL PASS events and blogging at http://blog.waynesheffield.com/wayne

 

 

 

JasonBrimhall

Jason Brimhall has 10+ yrs experience and has worked with SQL Server from 6.5 through SQL 2012. He has experience in performance tuning, high transaction environments, as well as large environments.  Jason also has 18 years experience in IT working with the hardware, OS, network and even the plunger (ask him sometime about that). He is currently a Consultant and a Microsoft Certified Master(MCM). Jason is the VP of the Las Vegas User Group (SSSOLV).

 

 

 

 

Course Objectives

  1. Recognize practices that are performance pitfalls
  2. Learn how to Remedy the performance pitfalls
  3. Recognize practices that are security pitfalls
  4. Learn how to Remedy the security pitfalls
  5. Demos Demos Demos – scripts to demonstrate pitfalls and their remedies will be provided
  6. Have fun and discuss
  7. We might blow up a database

kaboom

 

There will be a nice mix of real world examples and some painfully contrived examples. All will have a good and useful point.

If you will be in the area, and you are looking for high quality content with a good mix of enjoyment, come and join us.  You can find registration information and event details at the Salt Lake City SQL Saturday site – here.  There are only 75 seats available for this murder mystery theater.  Reserve yours now.

The cost for the class is $150 (plus fees) up through the day of the event.  When you register, be sure to tell your coworkers and friends.

Wait, there’s more…

Not only will I be in Utah for this workshop, I will also be presenting as a part of the SQLSaturday event on October 25, 2014 (the day after the workshop which is Oct. 24, 2014).  You can view the available sessions here.

Shameless plug time

I present regularly at SQL Saturdays.  Wayne also presents regularly at SQL Saturdays.  If you are organizing an event and would like to fill some workshop sessions, please contact either Wayne, myself or both of us for this session.

October 2014 Las Vegas UG Meeting

Comments: No Comments
Published on: October 8, 2014

The Las Vegas User Group is happy to announce our monthly meeting.  The meeting is available for in person and webinar style.  The start time is 6:30 PM Pacific and the details are listed in this post.  We hope to see you there!

invite_oct2014

This month we have a special treat brought to us by fellow MVP Argenis Fernandez (blog | twitter).  If you had a devilish little trick to upgrade SQL Server without an outage, would you do it?  Argenis will show you a good set of tricks to put your upgrade nightmares to rest.

You can read all about what Argenis is planning to present and read all about Argenis on our meetup page.

We hope to see you either in person or virtually for our monthly UG installment.

Microsoft, Me and the MVP

Comments: 2 Comments
Published on: October 2, 2014

Microsoft_MVP_logo_thumbOctober 1st is one of the quarterly dates that Microsoft announces all of the new people into the MVP program.  It is also a day in which Microsoft recognizes many already in the program and renews their status for the upcoming year.

This time around, I had the distinct honor and pleasure of joining the ranks of so many people who have been a part of this program for some time or who might have also been recently named to the program.

The MVP program is a program designed to recognize people for their efforts in teaching about various Microsoft technologies.  In my case, that happens to be SQL Server.  It is a humbling feeling to have been nominated and to be welcomed into the program.

To top it all off, I found it really cool that my welcome letter came in French rather than the standard English.  This is perfectly acceptable to me since it is a language I speak fluently and do try to keep up to date with it.  It was also interesting to see the MVP Lead call me within minutes of receipt of one of NDA email to make sure it was ok.  He was thrown by the NDA being in French and was concerned whether I could understand it or not.  At which point we had a good chuckle because I had no qualms with the language in use.

Attached are a couple of screen grabs with the welcome letter and so forth.  I geek out every time I look at them and it is totally cool.

In addition, I have also attached a link to my updated MVP profile.  You can find that here.

notice

CongratsSo what does it mean now that I am an MVP?

Well, to be truthful, it means status quo.  Having this means that a recognition was given.  It also means that, in my opinion, I am on the right track in doing good things for the community.  Because of that, I will continue to do what I can where and when I can to help teach others about SQL Server.  I am indeed grateful to be in pretty good company with people who also try to teach about SQL Server.

To all who helped this happen, I am grateful for your support.

I am also grateful to SQL Solutions Group who first broke the news.  You can read about that here.

 

September 2014 Las Vegas UG Meeting

Comments: 1 Comment
Published on: September 10, 2014

Who is up for a little free learning this week? Besides the opulence and feast that was the 24 Hours of PASS (Summit Preview), we have more training in store for you from the people in Las Vegas. Let’s call this a preview for next week which happens to be DevConnections (which also happens to be in Vegas)!!

The Las Vegas User Group is happy to announce our monthly meeting.  The meeting is available for in person and webinar style.  The start time is 6:30 PM Pacific and the details are listed in this post.  We hope to see you there!

Capture

Abstract: PowerShell: The Way of the DBA Dragon

In this introduction to PowerShell, attendees will learn how to start from scratch with PowerShell 3.0 or newer, use the pipeline, run T-SQL against multiple instances, use transcripts, and be shown martial arts usage of one of the SQLPSX cmdlets.  Scripts will be provided.

BIO

Lars Rasmussen was born in Illinois, but considers Utah home.  He does not play video games, is learning to camp and hike, and is happy to have shared the summit of Mt. Timpanogos with two of his sons.  Lars’ wife and four children help him smile and laugh, and the family dog is teaching him patience.  Playing board games is one his favorite pastimes.  He considers SQL Server, PowerShell, and CMD.EXE some of his dearest frenemies.  Lars enjoys the company of SQL Server professionals and sysadmins – he used to be one of the latter, and is employed as a database administrator for HealthEquity.

LiveMeeting Info

Attendee URLhttps://www.livemeeting.com/cc/UserGroups/join?id=MR7C92&role=attend

Meeting ID: MR7C92

«page 2 of 83»






Calendar
November 2014
M T W T F S S
« Oct    
 12
3456789
10111213141516
17181920212223
24252627282930
Content
SQLHelp

SQLHelp


Welcome , today is Wednesday, November 26, 2014