Auditing and Event SubClasses

Categories: News, Professional, Scripts, SSC
Comments: 2 Comments
Published on: May 28, 2014

A recent discussion got me to thinking about Auditing.  To be honest, it got started with a complaint about some documentation that seemed overly light about the various fields related to auditing as it stands in SQL Server.

In talking to the person who raised the valid concern about the lack of good documentation, I was curious why he suddenly had so many questions about auditing and its functionality within SQL Server.  Reflecting on the answers, it seems that it made good sense and it all kind of fell into place with the whole Audit Life Cycle.  I hadn’t previously considered the Life Cycle, but it makes sense to map it out.  Here is a simple rendition of what an audit Life Cycle might entail.

 

 

AuditCycle_trans

 

 

In order to audit anything, it is necessary to know what you really want to audit, why you want to audit it and how to achieve those goals within the tools given to you.  In that vein, it makes sense that one would need to study up on the topic to figure out what different things meant within the tool.

Of course, once you start collecting that data, then you also need to figure out how to measure it and then to determine if adjustments to the auditing plan need to be made.  In the end, it boils down to what is the data to be collected, what are you doing with that data and what does that data represent.

In our simple discussion, the data trying to be understood was related to the Event Subclass field in this View (sys.trace_subclass_values) and in this Microsoft document (one of several).  The beauty of this field is that it is not just tied to Auditing, but you will also find it in Profiler, server side traces, and Extended Events.

With so little information to help understand what the field values represent, maybe it is better to just turn to the data to help understand what the values might represent or how to interpret them.  To do this, we can query a few catalog views as in the following query.

[codesyntax lang=”tsql”]

[/codesyntax]

With the above query, I can filter down to just the Event Types that have Audit in the name.  Or I could add a different filter so I can try and better understand the different subclasses in a more focused effort.

I hope this helps in your efforts to provide a better auditing or “profiling” type of experience in your environment.

 

Can you partition a temporary table?

Reading that title, you might sit and wonder why you would ever want to partition a temporary table.  I too would wonder the same thing.  That withstanding, it is an interesting question that I wanted to investigate.

The investigation started with a fairly innocuous venture into showing some features that do apply to temp tables which are commonly mistaken as limitations (i.e. don’t work with temp tables).  To show this I set off to create a script with reproducible results to demonstrate these features.  I have included all of those in the same script I will provide that demonstrates the answer to the partitioning question.

In fact lets just jump to that script now.

In the beginning (after dropping objects if they exist), I start by creating a temp table that has a couple of mythical limitations.  These mythical creatures are that temp tables can’t have indexes or that they can’t have constraints.

In this script, I show that a temp table (#hubbabubba) can indeed have indexes created on it (clustered and nonclustered).  I also demonstrate the creation of two different kinds of constraints on the #hubbabubba table.  The two constraints are a primary key and a default constraint.  That stuff was easy!!

To figure out whether or not one could partition a temporary table, I needed to do more than simply create a “test” temp table.  I had to create a partitioning function and a partitioning scheme and then tie that partition scheme to a clustered index that I created after table creation.  Really, this is all the same steps as if creating partitioning on a standard (non-temporary) table.

With that partitioning scheme, function and the table created it was time to populate with enough random data to seem like a fair distribution.  You see, I created a partition function for each month of the year 2014.  To see partitioning in action, I wanted to see data in each of the partitions.

That brings us to the final piece of the whole script.  Kendra Little produced a script for viewing distribution of data across the partitions so I used her script to demonstrate our data distribution.  If you run the entire script including the data distribution segment at the end, you will see that there are 13 partitions with each of the monthly partitions containing data.

The distribution of data into the different partitions demonstrates soundly that partitioning can not only be created on a temporary table, but that it can be used.  As for the secondary question today “Why would you do that?”, I still do not know.  The only reason that pops into my mind is that you would do it purely for demonstration purposes.  I can’t think of a production scenario where partitioning temporary data would be a benefit.  If you know of a use case, please let me know.

Supported Compatibility Levels in SQL Server

Categories: News, Professional, Scripts, SSC
Comments: 1 Comment
Published on: May 21, 2014

It has been well documented and is well known that SQL Server supports certain older versions of SQL Server in a compatibility mode.  This setting is something that can be configured on the database properties level.  You can quickly change to an older compatibility level or revert the change to a newer compatibility level.

Changing the compatibility level is sometimes necessary.  Knowing what compatibility modes are available for each database is also somewhat necessary.  The common rule of thumb has been the current version and two prior versions.  But even with that, sometimes it is warm and fuzzy to be able to see the supported versions in some sort of format other than through the GUI for database properties.

Sure, one could go and check Books Online.  You can find that information there.  Or you could fire up this script and run with the output (as a guideline).

 

This script will return results such as the following.

Picture0002

And if we wanted to see the results for a SQL Server 2014 installation, we would see the following.

Picture0003

The output is displayed in the same format you might see it if you were to use the Database Properties GUI.  That said, if you are using the GUI in SQL Server 2014, you might run into the following.

Picture0006

Notice the additional compatibility level for SQL 2005?  If you check the documentation, you will probably find that compatibility level 90 is not supported in SQL 2014.  In fact it says that if a database is in 90 compatibility, it will be upgraded to 100 automatically (SQL 2008).  You can find all of that and more here.

If you tried to select compatibility 90, you might end up with an error.  If you are on 2014 CTP2, you will probably be able to change the compat level without error.

Anyway, this is the message you might see when trying to change to compatibility 90.

Picture0005

They sometimes say that “seeing is believing.”  Well in this case, you may be seeing a compatibility level in the 2014 GUI that just isn’t valid.  Keep that in mind when using the GUI or trying to change compatibility modes.

Missing Indexes Script v2

Comments: 11 Comments
Published on: May 15, 2014

missing_idx

Frequently we hear the analogy that <insert item here> is like opinions, everybody has one and not all of them are good (some may stink).

Well, this may just be another one of those <items>.  Whether it stinks or not may depend on your mileage.

I had shared a similar script back in January 2012 and wanted to share something a little more current.  As is the case for many DB professionals, I am always tweaking (not twerking) and refining the script to try and make it more robust and a little more accurate.

This version does a couple of things differently than the previous version.  For one, this is a single database at a time (the prior version looped through all of the databases with a less refined query).  Another significant difference is that this query is designed to try and pull information from multiple places about the missing indexes and execution statistics.  I felt this could prove more advantageous and useful than to just pull the information from one place.

Here is the current working script.

The following script gets altered on display.  n.VALUE is displayed but in the code it is actually n.value.  The code display is wrong but it is correct in the code as presented in the editor.  If copying from this page, please change the U-cased “VALUE” in the XML segment to “value” so it will work.  A download of the script has been added at the end.

[codesyntax lang=”tsql”]

[/codesyntax]

As has been the case in the past, this is not the end query.  I have a backlog of updates I want to make to the query and try to create something even more useful from it.

As a bit of fair caution, just because an index shows up as a recommended implementation from this script, it does not mean you should blanket implement it.  Also, like the previous script, the higher the impact value, the more probable the index will have a positive impact on your system.

In addition to the impact, I also look at the “Similar Queries” and “Similar Plans” numbers from the result set.  These values can sometimes lead to an understanding that their are other queries or other plans that could also benefit from the creation of the index.  Your mileage may vary.

Download script Missing_Index.

T-SQL Tuesday #54 – Interviews and Hiring

Comments: 1 Comment
Published on: May 13, 2014

TSQL2sDay150x150

This month’s T-SQL Tuesday is hosted by Boris Hristov (blog|twitter) and his chosen topic is “Interviews and Hiring” – specifically interviewing and hiring of SQL Server Professionals.

 

This is a pretty interesting topic from a few different angles.  Boris proposed a few topics such as the following list.

 

  1. The story of how did you get hired on your latest position?
  2. The most interesting interview you ever had?
  3. How do you think an interview should be handled? What should it include?
  4. Any “algorithms” of how to find the perfect candidate?
  5. If you are the one that leads the technical interview – what do you focus on?
  6. What are the most important questions to ask for the various SQL Server positions out there?

Any one of these ideas would be good fodder for a blog article.  A combination of these topics might prove more interesting.  I think I will try something a little different.  I want to broach the topic of the use and abuse of interviews.

infinte

There are two interviews that come to mind that might be good examples.  The first is the infinite interview.

In the infinite interview, the candidate comes in for a full day of interviews (a surprise to the candidate).  If you were lucky you might have been informed in advance that the interview would be an all-day ordeal.

You arrive on-site and are shuttled from one interviewer to the next and so on throughout the day.  Most of these people will have absolutely nothing to do with your work queue or your job duties.  Most won’t be able to spell SQL other than maybe having a book that somebody might have given them.

In one such case, I had the opportunity to be grilled all day long.  The peak of the interview(s) occurred when their dev team sat down in an office, gave me chalk and eraser and required me to redesign their database that they took 6+ months to design and were still in the process of fixing bugs.  Lots of memorization based questions centered around developer (not database) terminology.

In short this pretty much felt like a free consultation session for them.  Once finished, I got to show my own way out the door.  Not by choice but by them being too busy for it.  And in the end not a word from the company.

The second kind of interview comes in the form of stump the chump.

stumpThis is another fun type of interview.  It can come in many forms.  Sometimes it can be in the form of free consultation.  Sometimes, the interviewer just gets his rocks off trying to prove he is smarter or that you are not as qualified as you say you are.

In the type where it comes as free consultation, the interviewer has usually been trying to resolve a production issue for quite some time and just can’t figure it out.  They will present a partial scenario to you and see if you can figure it out on limited info.  If you can’t, they might come back with “We already tried that” or they may provide more info.  Again, this is all in an effort to try and resolve a problem that they couldn’t.  Sometimes  it is often to try and save face with the boss showing that even an expert couldn’t do it.

The alternate style, the interviewer knows from the start that you may be overqualified but really wants to just prove they are as smart or smarter.  Often times it just proves that they have some really erroneous understandings about SQL Server.  One such interview, the person seemed to have an explicit Oracle background and wanted to get into the internals of SQL Server.  He wanted to get into index trees and tried to go down the path of some io statistics for queries based on a bunch of unknowns.

There is really only one thing to do in these types of interviews.  Once you recognize what is going on (be it stump the chump or the never-ending interview), politely excuse yourself and look for a position somewhere else.

Omaha BI/SQL User Group – What?

Comments: 1 Comment
Published on: May 7, 2014

I recently received an email from this guy named John Morehouse (blog | twitter) about the Omaha SQL/BI User Group Meeting in May.

Imagine my surprise when reading the email and discovering that I was presenting.

Here are the guts of that email.

Can you believe that it’s almost May?!  Time sure does fly!
Our next meeting will be on Wednesday May 7th, 2014 @ 6PM.  Food will be available approximately 20-30 minutes prior to the meeting.  We will be at the Farm Credit Services of America facility and our sponsor for the evening is QCI.
Speaker: Jason Brimhall
Jason Brimhall has 10+ yrs experience and has worked with SQL Server from 6.5 through SQL 2014. He has experience in performance tuning, high transaction environments, as well as large environments. He is currently a Principal Consultant and MCM. He is he VP of the Las Vegas User Group (SSSOLV).
Topic: SQL 2012 Extended Events
Extended Events were introduced in SQL Server 2008. With SQL 2012, we have seen a significant upgrade to this feature. Join me for a little adventure into what extended events are. We will discuss how to use extended events to aid in performance tuning and in day to day administration. We will also explore some background and the architecture of extended events.
Sponsor: QCI
QCI has a long history of attracting and retaining the best talent. Continuous investment in our consultants results in an immediate, positive impact on your projects.  Contact Information: Omaha@qci.com 402-981-4264
For further information and to RSVP please go here: http://omahamtg.com/Events.aspx?ID=242
Please make sure to RSVP so that we have an accurate head count for food plus a chance to win some awesome SWAG!!
Thanks!
John Morehouse

Ok, so it really was not a surprise.  John asked and I agreed to present.  If you are in the Omaha area – come join the group for a pleasant evening and to learn something (hopefully) about SQL Server.

And yes – the meeting is tonight May 7, 2014.

 

page 1 of 1








Calendar
May 2014
M T W T F S S
« Apr   Jul »
 1234
567891011
12131415161718
19202122232425
262728293031  
Content
SQLHelp

SQLHelp


Welcome , today is Saturday, May 27, 2017