Summit 2014 – Next Impressions

Comments: 4 Comments
Published on: November 7, 2014

As Summit 2014 begins to wind down, it is time for some more impressions from the week.  The week has been good so far.  It has been very busy and also can be quite a drain mentally and physically from everything that has transpired.

If you are interested, I have written about some of my other impressions from the week, here.

Several years ago, I blogged about an incident with plagiarism with both an original post and a follow-up.  I bring that up, not to rehash the negative, but instead to discuss an impression from this week.  If you read the follow-up, you will see that I had a chat, at that time, with Steinar (twitter) about the problem and how to resolve it.  I met Steinar for the first time this week.  And to be honest, I had forgotten about the conflict and had removed the RSS feed since the original domain had gone down.

Anyway, Steinar and I had the chance to chat for the first time face to face this week.  Steinar, in my opinion, is a pretty cool guy that made a simple RSS mistake.  The impression is that he remembered me for how I treated him several years ago and was very appreciative of that.  How cool is that?  I really appreciate the opportunity to chat with him and that something I did left a positive impact on him.

Another opportunity is to be able to perform random acts of kindness or service while at Summit.  Much like helping Paul White learn how to use a smart phone, I had the even more rare opportunity to help Kalen Delaney (blog | twitter) out of a sticky situation.  It was a minor but frustrating thing that all of us run into from time to time.  The zipper on her Surface case had become stuck, so I helped her with that.  It’s a little thing but it is the type of thing that, if you are watching, you will see happening all over the place during the week of PASS Summit.

So, the next time you are at Summit, and while back in your local communities after the week has ended for Summit, keep an eye out for those little acts of kindness.  But at the same time, keep an eye out for those that might be watching you.  What kind of impression are you leaving for them or for the SQL Community?

Summit 2014 – Early Impressions

Categories: News, Professional, SSC
Comments: 3 Comments
Published on: November 5, 2014

Summit 2014 is upon us.  Unless you are still under a rock, you probably know that.  And if you are under that rock, I am curious how you are reading this.

While it is early on in the week for the PASS Summit, things have really been going since Sunday for many.  A lot has happened.  A lot has already been learned.  And yes, some new people have already been met.  So far so good.

Now is a good time for me to just jot down some of my early impressions from the week.

It has been nice to receive a couple of compliments this week from a few people on various things.  It is waaaaay cool to hear things such as the following from community members.  Here are some samples.

“You taught me something.” Paul Randal (blog | twitter) in reference to a recent blog post that you can read here.  It is great to hear somebody learned something.  That is a primary driver for putting up content on the web and trying to help in the community where possible.

“Your index script has saved my bacon several times!” Stuart Ainsworth (blog | twitter) talking about my missing index script here.  Again, totally cool.  I am happy to hear about successes from code that I have put out there.

Those are two big impressions that would be great takeaways for the week.

But then we have to start throwing in the learning that is part of the week is.  I have attended a couple of sessions and found myself inspired by some of the content as well as hopeful by some of the other content.

By attending a session about Extended Events, I learned about an API that can expose some XE data via powershell to extend the possibilities and uses of XE.  Since the referenced blog does not contain any of the proposed material (slides or demos) it is hard to do much more with it just yet.  I will continue to check the referenced site as well as the session information on the Summit website.

By attending another session, I learned about a new feature called the Query Store.  It is basically a “Hammer that can make a lot of things look like nails!”  That session was presented by Conor Cunningham.  And while the Query Store has some extended events that are exposed in 2014, the XEs are useless and do nothing until SQL v.Next.  It would be totally awesome to have it back ported but that has no chance of happening.

And to top all of it off, it was great to sit down and get a couple of client issues fixed during the lunch break.

This is what Summit is, a huge chance to recharge, learn and to get excited about the technology and what is coming.  Oh and every now and again, one might get the chance to teach Paul White how to use a smart phone.

Ghosts – an eXtrasensory Experience

ghostrip_fireThis is the last article in a mini-series diving into the existence of ghosts and how to find them within your database.

So far this has been a fun and rewarding dive into Elysium to see and chat with these entities.  We have unearthed some means to be able to see these things manifesting themselves in the previous articles.  You can take a look at the previous articles here.

For this article, I had planned to discuss another undocumented method to look into the ghost records and their existence based on what was said on an msdn blog.  But after a lot of research, testing and finally reaching out to Paul Randal, I determined that won’t work.  So that idea was flushed all the way to Tartarus.

Let it be made very clear that DBTABLE does not offer a means to see the ghosts.  Paul and I agree that the other article that mentioned DBTABLE really should have been referring to DBCC Page instead.

Despite flushing the idea to Tartarus, it was not a fruitless dive.  It just was meaningless for the purpose of showing ghosts via that DBCC command.  I still gained value from the dive!!

All of that said, the remainder of the plan still applies and it should be fun.

Really, at this point what is there that hasn’t been done about the ghosts?  Well, if you are well tuned to these apparitions, you may have received the urge to explore them with Extended Events – sometimes called XE for short.

As has been done in the past, before we board Charon’s boat to cross the River Styx to Hades to find these ghosts in Elysium, one really needs to run the setup outlined here.

With the framework in place, you are now ready to explore with XE.

Look at that! There are several possible events that could help us track these ghosts.  Or at the least we could get to know how these ghosts are handled deep down in the confines of Hades, err I mean the database engine.

Ghost_XE

 

From these possible events, I opted to work with ghost_cleanup and ghost_cleanup_task_process_pages_for_db_packet.  The sessions I defined to trap our ghost tracks are as follows.

You can see there are two sessions defined for this trip down the Styx.  Each session aptly named for our journey.  The first (GhostHunt) is defined to trap ghost_cleanup and sends that information to a histogram target.  The second (SoulSearch) is defined to use the other event, and is configured to send to the ring_buffer.  Since the second event has a “count” field defined as a part of the event, it will work fine to just send it to the ring buffer for later evaluation.

Once I have the traps, I mean event sessions defined, I can now resume the test harness from the delete step as was previously done in previous articles.  The following Delete is what I will use.

Prior to running that delete though, I checked the Event Session data to confirm a starting baseline.  Prior to the delete, I had the following in my histogram target.

 

predelete_count

 

After running the delete, and checking my histogram again, I see the following results.

post_count

 

You can see from this that in addition to the 25 pre-existing ghosts, we had another 672 ghosts (666 of which were from the delete).

This is how I was able to investigate the GhostHunt Extended Event Histogram.

But what about looking at the other event session?

Let’s look at how we can go and investigate that session first and then look at some of the output data.

ghostclean

 

Cool!  Querying the SoulSearch session has produced some information for various ghosts in the database.  Unlike the histogram session that shows how many ghosts have been cleaned, this session shows us some page ids that could contain some ghosts – in the present.  I can take page 1030111 for instance and examine the page with DBCC PAGE as follows.

 

 

pagealtLook at that page and result!! We have found yet another poltergeist.

RIP

Once again we have been able to journey to the depths of the Database engine and explore the ghosts that might be there.  This just happens to illustrate a possible means to investigate those ghosts.  That said, I would not necessarily run these types of event sessions on a persistent basis.  I would only run these sessions if there seems to be an issue with the Ghost cleanup or if you have a strong penchant to learn (on a sandbox server).

Some good information can be learned.  It can also give a little insight into how much data is being deleted on a routine basis from your database.  As a stretch, you could even possibly use something like this to get a handle on knowing the data you support.  Just be cautious with the configuration of the XE and understand that there could be a negative impact on a very busy server.  And certainly proceed at your own risk.

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.

«page 2 of 83»






Calendar
December 2014
M T W T F S S
« Nov    
1234567
891011121314
15161718192021
22232425262728
293031  
Content
SQLHelp

SQLHelp


Welcome , today is Monday, December 22, 2014