What’s the Size of that Index?

Categories: News, Professional, Scripts, SSC
Comments: No Comments
Published on: July 27, 2015

I have previously written about finding what the big space consumers are within the database. I even shared scripts in those articles on how to find those big consumers (read all about it here).

sizematters2That is grand when it comes to quickly finding which objects happen to be consuming the greatest amount of space within the database. It really helps to quickly assess where some extra TLC may need to be spent. But what about more specific details? What if you wished to figure out what the size was for each of the indexes within that object?

Well, the solution is pretty simple. I have another script to help determine which indexes are the big space consumers within the specified table for the given database.

 

First, let’s see what that script is.

As you can see, I am parsing a few different measures on space usage from the dynamic management view sys.dm_db_partition_stats. This will tell me information such as how much space is being reserved for each index as well as how much space is actually being used on each index. Then I use a grouping set to summarize that size information so I can then compare the total index size back to the size I would have seen from the table space script (discussed lightly earlier).

Why might I want to do something like this?

That is a really good question! The first obvious answer to a question such as this is in the off chance that you discover (again, from the use of the previous script), that one of your terabyte tables is actually only 150GB of data and the rest of that space consumption is actually from all of the indexes on that table.

When I see that a table has more space allocated due to indexes than due to data, I might be curious which indexes are contributing to that consumption. In addition, I can look at numbers much faster to see if maybe there are multiple indexes that are the exact same size. If they are, I might want to evaluate and dig a little deeper into the definitions of those indexes. We would only really love to have 850GB of indexes on a table to only find that three or four of those indexes are exact duplicates.

From there, I might even decide to look at the usage stats for the indexes in question. Why go about it in this fashion? Well, on some databases it is much faster to run a few quick queries than to try and run a big query against the entire database to get all of the index usage stats. I am sure you are familiar with those times when trying to query physical and operational stats takes hours to complete.

But But But…

I can hear you thinking right now. Another question has popped into your head. What about sp_spaceused. That old proc does work just fine for this as well. But there is something about being able to get to know what contributes to the space of an index and being able to work through how to get it calculated. Additionally, with a minor tweak, this query can provide that (detailed) information very quickly for all objects in the database.

Tweaked query

 

Extended Events Removed from 2016

Comments: No Comments
Published on: July 22, 2015

SQL Server 2016 is on the horizon. We have a few CTP versions already released for download, allowing people to become more and more familiar with the product.

Recently, I wrote about how SQL Server 2016 has many more Extended Events that have been added for our use. You can read about the new events added – here.

One thing I did not discuss in that previous article was the apparent deprecation of a few events. Yes, events do get removed from time to time. Every now and again, old events are not removed (e.g. databases_data_file_size_changed and databases_log_file_size_changed which you can read more about here). It would be nice to remove those old events but that is for another article!

As of this writing, the following image shows what has been removed and no longer available.

nomore

 

Looking closely at this list of three events, I don’t know that it is terribly concerning. Two of the events are for the query store that did not work in 2014. So there is nothing really lost there. The other event appears to be more of an event used for debugging column store code (the event was in the debug channel).

So overall, the impact of the loss of these events should be very limited. And on the positive side, it is still evidence that Microsoft continues to push forward to make XEvents even bigger and badder. Extended Events are a wonderful tool-set for use by DBAs to be better informed about their environment.

TSQL Recipes – 2014 Edition

Announcing…the book

book_coverAt long last the wait is over. After much blood, sweat and more blood and sweat, the next edition of the SQL Server Recipes book is finished.

This edition brings several changes and quite a bit of re-write. It has been updated for many of the new SQL Server 2014 (well, new, at least until SQL 2016 hits mainstream) features that were added. In addition, we revisited some of the other features that had been omitted from previous editions to try and give it a more encompassing feel.

All of that said, the book is not a comprehensive listing of every command, jot or tittle in SQL Server. There were a finite number of pages and the features in SQL Server just has far too many features and nuances to cover within a single edition.

Despite the limitation of page quantity, we do feel that this is a pretty comprehensive book covering a wide array of features with several examples of how to perform various tasks along with use-cases for when to use the example.

When you crack the covers, you will find examples of how to perform backups to blob storage in Azure, create In-memory OLTP tables, restore from blob storage, and in some cases you will see how to use Extended Events to help with troubleshooting. That is just a small sampling of the contents that fill the almost 900 pages in this book.

Combine this reference book with the previous editions, and you will have an excellent resource for SQL Server.

You can purchase the book on Amazon – here.

Now for the mushy stuff

I am grateful to the folks at Apress for letting us come on board with this book and continue writing about SQL Server and creating a great resource.

I am grateful to the other authors Jonathan Gennick and Wayne Sheffield for helping push this along. The collaboration and support provided by these guys was fantastic. Their patience was also exceptional.

Equally important was the patience and understanding afforded by my family. Writing does take a significant amount of time and they sacrificed so I could continue on this project.

Thanks to all these folks for helping make this a great achievement!

Murder and XE Train in Louisville

Categories: News, Professional, SSC
Comments: No Comments
Published on: July 17, 2015

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.

Amazing this thing is still going on after the sailing train comment that was made around the time of PASS Summit 2013.

lvillecrazytrain

 

Time to sink or sail, so to speak.  SQL Saturday 403 in Louisville 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 Louisville, KY. Did I mention that this is a:

bogo

That’s right! If you get two all day sessions for the price of one! Attend just one or both of the sessions that Wayne and I will be presenting. You can see full details at our eventbrite site here.

If you are a DBA or a database developer, these sessions are for you.  If you are managing a database and are experiencing performance issues, these sessions are a must.  We will chat with attendees about a horde of performance killers and other critical issues we have seen in our years of 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.

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

That is just the Murder session. Here are the details for the XE session.

A Masters Passport to Extended Events

As is commonly the case, all good things come to an end.  And now is as good a time as any for the use of SQL Trace and Profiler to come to an end.  Let’s face it, Trace was a good tool and had some wonderful uses.  Profiler for that matter was a good tool and was useful at times.

It is time to let those old tools retire gracefully and move into the world of XE.  This full day workshop will provide you the means to let Profiler and Trace be retired from your toolset as you discover all that XE has to offer.

This full day session on Extended Events will help prepare you to put this tool to immediate use as you walk back to your daily duties.  This workshop will teach you about Extended Events starting with the basics and moving through how to create XE sessions that will get the right data for you, while doing so with minimal impact.

You will be exposed to advanced troubleshooting techniques as we work through complex issues that are made easier through the use of XE.  Take advantage of this opportunity to dive into the world of Extended Events and learn how you can make best use of this tool in your SQL 2008+ environment.

Course Objectives

  1. Build a knowledge base for Extended Events
  2. Become familiar with the tools for Extended Events
  3. Become familiar with uses for Extended Events
  4. Get acquainted with troubleshooting scenarios for Extended Events
  5. Begin to put Extended Events to practical use
  6. Return to work with enough information to eradicate Profiler from the environment

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 also an MVP for SQL Server.

 

 

 

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 Louisville 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 Louisville for these workshops, I will also be presenting as a part of the SQLSaturday event on August 22, 2015 (the Saturday after the workshops which run Aug. 20-21, 2015).  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.

SQL Server and Defaults

TSQL2sDayWhat is that default setting?

SQL server does a fantastic job of having numerous settings at the server level and at the database level predefined for you. Additionally, the OS has a bunch of settings that are predefined. These are notoriously the default settings. Or as some would say: “Set it and Forget it Settings.” Sadly, the set it part never really happens – except during the install.

Today is the second Tuesday of the the month, and that means it is TSQL Tuesday. As I mentioned in a previous article, this month the topic is hosted by Andy Yun (blog | twitter). Andy has chosen to have everybody talk about default settings in SQL Server. You can read everything Andy has said in his invite for the month.

deadlydefaults

Defaults Defaults Defaults

I could ramble on about database settings that get changed, but I have already addressed the changing of database settings – here. While I did address the changing of the settings, I did not address that it is an awesome thing to use to find out who might be changing those default settings (at the database level) that you have already set and optimized for the instance.

Or I could go on about how the OS has various settings that are far less than optimal for SQL Server. A good example would the that tree hugger setting that I talked about last month. But rather than do that, I will advise that you just read that article – here.

Or we can belabor the point about a fantastic setting that equates to NOLOCK. But, that isn’t a default setting and one would need a pretty good reason to change the default setting to READ UNCOMMITTED. So, just read a bit about the coolness you can see in the execution plans when the NOLOCK directive is used – here.

There are just so many wonderful default settings within SQL Server (or within the Windows OS) that could be discussed or that have already been discussed.

About all of those settings, I will say this. Database Settings are not “One Size Fits ALL.” But that is what a default setting is trying to do. If you find default settings within a database, then you should probably evaluate the setting and make sure it is set appropriately.

What default to discuss then?

Rather than talk about a plethora of settings that could / should be changed, I want to speak about one I doubt most would consider. This is the default setting that can only be adjusted from between the ears!

This is a default behavior I have noticed with many DBAs. I have heard various names for this behavior and most are probably quite accurate. The default setting that I think should be changed is one that will lead to a longer more fulfilling career. Let me see if I can describe the behavior well enough.

Often times, companies will purchase monitoring software to help alert to problems within the environment. This is a good thing. A DBA can’t be available 24x7x365. So the software should be configured and tuned to each database to allow for different tolerance/alert thresholds. This is a good thing too – if it is done. When done properly, the DBA can get a good nights rest as well as feel confident the environment is running properly.

If the software is not properly tuned and configured, the DBA will probably rectify that sooner rather than later. This is not the big behavior to change.

What I do see all too often is a complete reliance on the software that is monitoring – to a fault. I have seen hundreds of DBAs just sit and watch the pretty little dials and gauges on the screen of the software and only react when one of the items turns red. A career surely can’t be built off of this kind of behavior.

Rather than sit there and wait for something to fail, why not proactively check the servers? Why not try to build a script repository that will do everything (and more) that the monitoring software can do? While building that repository, think of the skills that will be gained and the knowledge that can be retained across jobs! In addition, I have been some places where a script repository was able to replace the purchased software and saved 100’s of thousands of dollars per year in software maintenance costs.

One of my favorite statements is that a “Senior DBA should be able to script his own solutions!” Being able to create monitoring scripts to replace that canned app will certainly get you to that next level. It will also get you out of that default behavior of complete reliance on the canned software and imminent career stagnation.

Learn a little and grow your career.

Oh, and there are some great monitoring tools out there. They can provide a great asset to a company – if and when used properly.

saynoribbon

PASS Summit 2015 – Guess What?

Comments: No Comments
Published on: July 13, 2015

Quick run the other way!

PASS Summit will never be the same!

Why?

Well, because for the first time in the history of me, I have been selected to speak at such a prestigious event.

I'm Speaking Graphic_Large

Isn’t that just crazy?

In some ways it seems crazy. When I received the email I was exhilarated and completely astonished. I had hoped I would be selected, but did not entirely think it would happen this year.

I have to confess that I am honored to be one of the many to have been selected. This is really cool. You may be wondering why I am blogging about this opportunity soooo late after the speakers were announced. Well, the answer is rather straight forward. Life has been a bit crazy the past few weeks.

The last night of SQL Cruise I received a phone call from my family letting me know that my grandfather had become fatally ill. I spent the next several days with him in the hospital until he passed away a week later. Then there were the holidays and the funeral. And to cap that off, I flew down to Phoenix to speak to the two user groups last week. SO, it has been a whirlwind!

All of that said, I am announcing it now and really am excited to be a apart of such an AWESOME event.

Now for the surprise.

When I discovered which session had been selected, I was very surprised. I expected one of my Extended Events sessions to be picked. Nope! Instead, one of my other fun sessions was picked. We will be talking about Compression in SQL Server at Summit.

I am hopeful we are able to make this an exciting and enjoyable session for the attendees. I hope the attendees will also be able to take the information home and be able to use it for better (or worse) in their environments.

I do hope to see many of you in attendance. On the agenda for the attendees in this session will be:

  1. Dive into functions available to help learn about compression
  2. Evaluation of compression settings and possible savings
  3. Dive into the Pages
  4. A quick look at the CD and compressed data types
  5. Levity

Of course, I could just decide to change this on a whim – buahaha!

Database Settings Changes – Red Handed

Comments: 1 Comment
Published on: July 8, 2015

One of my pet-peeves (and consequently frequent topic of discussion) is finding database settings (or any setting that has changed) without knowing about it. Worse yet is finding that the change has occurred and nobody claims to have any knowledge of it or having done the deed.

This happened again recently where a database was set to single_user and suddenly performance in the database tanked. Change the database back to multi_user and the performance issues are magically resolved.

Fortunately there is a means to combat this. Well, sort of. The default trace in SQL Server does capture the event that occurs when the database is set to single_user or read_only. Unfortunately, all that is captured is that an Alter Database occurred. There is no direct means of mapping that event to the statement or setting that changed.

This inadequacy got me to thinking. The default trace is looking at a set of specific “events”, why wouldn’t that set of events be available within Extended Events. It only seems logical! So I decided to query the event catalog and lo and behold, I found just the event I was seeking – object_altered. Combine this with a recently used predicate (object_type = ‘DATABASE’) and we are well on our way to having just the trap to catch the source of these database changes red-handed.

Easy enough to create this particular session. The event does not capture the “whodunnit” without a little extra prodding. So, I added in a couple of actions to get that information – sqlserver.nt_username,sqlserver.server_principal_name,sqlserver.client_hostname. Additionally, the event does not explicitly tell me what setting changed – just that some setting changed. For this, I decided to add the sql_text action so I could correlate event to the actual setting being changed. Then to cap it all off, I made sure the predicate specified that we only care about database settings changes as previously mentioned.

Running the session and then testing some settings changes should prove fruitful to capturing good info. Here are a few of the tests that I ran and the results of those tests (by querying the session data).

Now to try and look at the results.

DB Change Data

 

There you have it! I have just been caught red-handed changing my AdventureWorks2014 database to single_user and multi_user.

Bonus

For more ideas on settings and changes and so forth, Andy Yun (blog | twitter) has invited all to participate in TSQL2SDAY on this very topic. He has invited all to talk about their experiences with “default settings” and what you might change them to! You can read about it here. I have another article coming up that will fit just nicely with that. Let’s just call this a preview and maybe it can help you get those tsql2sday juices flowing.

In memory of Grandpa Greenland

Categories: News
Tags: ,
Comments: No Comments
Published on: July 6, 2015

Dan “L” Greenland (1935 ~ 2015)

Dan GreenlandDan “L” Greenland, 80, passed away June 30, 2015.
Born June 3, 1935 in Highland, Utah, to William John and Cressie Althera Loveridge Greenland. He grew up in Highland and attended schools in American Fork. Graduated from
American Fork High School in 1953. Married his high school sweetheart, Eleanor Bunker, October 21, 1953 in the Salt Lake Temple which was officiated by Pres. J. Reuben Clark Jr.
Married 61½ years. Worked for Deseret Chemical and Chevron Oil. Loved the youth, umpired baseball, and refereed basketball for many years. Active in the LDS Church serving in many positions. Received the Duty to God award and the Scouter Key for his work with the Cub Scouts. Served as the priesthood leader at girls camp for 17 years and enjoyed every minute. He loved people and served as bishop three times and two years as a stake missionary. Served an 18 month mission with his wife to the New York Utica Mission where he was called as a counselor in the mission presidency.

Survived by wife, Eleanor; children: Dan “L” Jr. (Marilee), Jeanette (Mike) Brimhall, Dale, JoAnn (Douglas) Braithwaite, Laura (John) Aitken, Debra (David) Ginnett, Marguerite (Michael) Thacker, Tina (Andrew) Rother; 36 grandchildren; 55 great-grandchildren;
brother, Vernon (Golda) Greenland, and many nieces and nephews. Preceded in death by parents; 2 brothers; 2 sisters; son; and a daughter-in-law.

Funeral services will be held Monday, July 6, 2015, 11:00 a.m. at the Kearns Stake Center, 4260 West 5215 South.

That is what the obituary read. This is how I will remember him.

Grandpa was a kind loving man that was well liked and respected by many people. He touched the lives of many whether through baseball, basketball, church or his daily interactions with young and old alike.

I enjoyed the time I was able to spend with my grandfather and the lessons that I was able to learn just by observing him. As a young man I really enjoyed the times that I was able to spend with him whether it was to drive 100 miles or so to watch him umpire a baseball game or just go to an ice cream parlor for fun.

He taught me an appreciation for baseball as well as other sports. It is an appreciation that I have to this day and that I hope my children (how could they not) will be able to gain and keep.

Something that I noticed early on was how hard a worker grandpa was. Grandpa did not shy away from work and made sure he always provided for his family. Despite all of the work, he always seemed to have time for the grand-kids or even to help with church or community. He loved to tease and enjoy the people he was with. He brightened the room.

Grandpa conducted his life with dignity and with my admiration. I hope I could some day be as kind, fair, gentle and hard working as we was throughout his life. I was honored and humbled to be able to have the chance to sit by his side and have a few conversations as he was preparing to pass from this earth to the next.

Grandpa has earned his place in heaven and this chance to rest.

I love you Grandpa! I will miss you and hope to see you again.

New Extended Events for 2016

As we begin to get a grasp of all that is available with SQL Server 2016 and all of the new features, it is a great idea to see what else has been made available to us in order to help support and troubleshoot these new features. With that, it is well worth a look at the new changes to Extended Events to support all the shiny new stuff.

Here is a list of what I could find as of CTP 2 (I have yet to look at CTP 2.1). This list alone has some really cool stuff in it.  Browse through it and you will be able to find new stuff to help support and administer things like Stretch Database as well as JSON. You won’t find these events within Trace or profiler – only XE.

The presence of these new events (even some new ones for Query Store) is quite enough to get me excited for that next release of SQL Server. There is some great stuff coming down the pipe.

I have randomly highlighted some of the new events to support some of these new features. (And since this is only CTP 2.0, one can be sure there will be changes made to this list with the actual release of SQL Server 2016.)

SQLVersion EventName PackageName EventDescription
Microsoft SQL Server 2016 (CTP2.0) availability_replica_database_fault_reporting sqlserver Occurs when a database reports a fault to the availability replica manager which will trigger a replica restart if the database is critical
Microsoft SQL Server 2016 (CTP2.0) backup_restore_progress_trace sqlserver Prints backup/restore progress trace messages with details
Microsoft SQL Server 2016 (CTP2.0) batchmode_sort_spill_file sqlserver Record the spill file read/write information for batch mode sort
Microsoft SQL Server 2016 (CTP2.0) batchmode_sort_status sqlserver Record batch mode sort status
Microsoft SQL Server 2016 (CTP2.0) cl_duration XtpCompile Reports the duration of the C compilation.
Microsoft SQL Server 2016 (CTP2.0) clr_context_dump sqlclr ClrContextDump triggered.
Microsoft SQL Server 2016 (CTP2.0) column_store_expression_filter_apply sqlserver An expression bitmap filter was applied on a rowgroup column batch.
Microsoft SQL Server 2016 (CTP2.0) column_store_expression_filter_bitmap_set sqlserver An expression bitmap filter was set on a rowgroup column at rowgroup compile time.
Microsoft SQL Server 2016 (CTP2.0) columnstore_delete_buffer_closed_rowgroup_with_generationid_found sqlserver Delete buffer can not be flushed due to existence of one or more closed rowgroups with generation ID.
Microsoft SQL Server 2016 (CTP2.0) columnstore_delete_buffer_flush_failed sqlserver Columnstore delete buffer flush failed.
Microsoft SQL Server 2016 (CTP2.0) columnstore_delete_buffer_state_transition sqlserver Occurs when closed delete buffer state changes.
Microsoft SQL Server 2016 (CTP2.0) columnstore_delta_rowgroup_closed sqlserver A delta rowgroup was closed.
Microsoft SQL Server 2016 (CTP2.0) columnstore_no_rowgroup_qualified_for_merge sqlserver A user invoked a REORG command but based on the policy, no rowgroup qualified.
Microsoft SQL Server 2016 (CTP2.0) columnstore_rowgroup_compressed sqlserver A compressed rowgroup was created.
Microsoft SQL Server 2016 (CTP2.0) columnstore_rowgroup_merge_complete sqlserver A MERGE operation completed merging columnstore rowgroups together.
Microsoft SQL Server 2016 (CTP2.0) columnstore_rowgroup_merge_start sqlserver A MERGE operation started merging columnstore rowgroups together.
Microsoft SQL Server 2016 (CTP2.0) columnstore_tuple_mover_begin_delete_buffer_flush sqlserver Columnstore tuple mover started flushing a delete buffer.
Microsoft SQL Server 2016 (CTP2.0) columnstore_tuple_mover_compression_stats sqlserver Statistics about the movement of a deltastore to a compressed rowgroup, including duration, size, etc.
Microsoft SQL Server 2016 (CTP2.0) columnstore_tuple_mover_delete_buffer_flush_requirements_not_met sqlserver Occurs when column store tuple mover was not able to acquire required locks for flushing a delete buffer.
Microsoft SQL Server 2016 (CTP2.0) columnstore_tuple_mover_delete_buffer_truncate_requirements_not_met sqlserver Occurs when column store tuple mover was not able to acquire required locks for truncating a delete buffer.
Microsoft SQL Server 2016 (CTP2.0) columnstore_tuple_mover_delete_buffer_truncated sqlserver Columnstore tuple mover truncated delete buffer.
Microsoft SQL Server 2016 (CTP2.0) columnstore_tuple_mover_delete_buffers_swapped sqlserver Columnstore tuple mover swapped delete buffers.
Microsoft SQL Server 2016 (CTP2.0) columnstore_tuple_mover_end_delete_buffer_flush sqlserver Columnstore tuple mover completed flushing a delete buffer.
Microsoft SQL Server 2016 (CTP2.0) columnstore_tuple_mover_met_requirements_for_delete_buffer_flush sqlserver Occurs when column store tuple mover has acquired required locks and is ready to start flushing a delete buffer.
Microsoft SQL Server 2016 (CTP2.0) columnstore_tuple_mover_met_requirements_for_delete_buffer_truncate sqlserver Occurs when column store tuple mover has acquired required locks and is ready to start truncating a delete buffer.
Microsoft SQL Server 2016 (CTP2.0) columnstore_x_dbfl_acquired sqlserver Occurs when X Delete Buffer Flush Lock is acquired.
Microsoft SQL Server 2016 (CTP2.0) compressed_alter_column_is_md_only sqlserver Occurs during an alter column operation. Indicates whether the alter column is metadata only or not.
Microsoft SQL Server 2016 (CTP2.0) connection_accept sqlserver Occurs when a new connection is accepted by (or duplicated into) the server. This event serves to log all connection attempts.
Microsoft SQL Server 2016 (CTP2.0) connection_duplication_failure sqlserver Occurs when connection duplication fails
Microsoft SQL Server 2016 (CTP2.0) data_purity_checks_for_dbcompat_130 sqlserver Occurs when an operation that may require a data purity check for dbcompat level 130 occurs.
Microsoft SQL Server 2016 (CTP2.0) database_recovery_times sqlserver Database recovery times
Microsoft SQL Server 2016 (CTP2.0) database_tde_encryption_scan_duration sqlserver Database TDE Encryption Scan
Microsoft SQL Server 2016 (CTP2.0) database_transaction_yield sqlserver Occurs when a database transaction yields execution due to TDS buffer being full.
Microsoft SQL Server 2016 (CTP2.0) ex_raise2 sqlos Raised exception
Microsoft SQL Server 2016 (CTP2.0) fulltextlog_written sqlserver Errorlog written
Microsoft SQL Server 2016 (CTP2.0) global_transaction sqlserver Occurs when global transaction is started.
Microsoft SQL Server 2016 (CTP2.0) hadr_db_log_throttle sqlserver Occurs when DB log generation throttle changes.
Microsoft SQL Server 2016 (CTP2.0) hadr_db_log_throttle_configuration_parameters sqlserver Occurs when DB log generation throttle configuration parameters are read.
Microsoft SQL Server 2016 (CTP2.0) hadr_db_log_throttle_input sqlserver Occurs when HADR Fabric log management component updates log throttle.
Microsoft SQL Server 2016 (CTP2.0) hadr_db_marked_for_reseed sqlserver Occurs when a HADR secondary DB falls too far behind primary and is marked for reseed.
Microsoft SQL Server 2016 (CTP2.0) hadr_db_remote_harden_failure sqlserver A harden request as part of a commit or prepare failed due to remote failure.
Microsoft SQL Server 2016 (CTP2.0) hadr_log_block_send_complete sqlserver Occurs after a log block message has been sent. This event is only used for failpoints.
Microsoft SQL Server 2016 (CTP2.0) hadr_partner_log_send_transition sqlserver Log send transition between log writer and log capture.
Microsoft SQL Server 2016 (CTP2.0) hadr_partner_restart_scan sqlserver Restart partner scans for this partner.
Microsoft SQL Server 2016 (CTP2.0) hadr_physical_seeding_backup_state_change sqlserver Physical Seeding Backup Side State Change.
Microsoft SQL Server 2016 (CTP2.0) hadr_physical_seeding_failure sqlserver Physical Seeding Failure Event.
Microsoft SQL Server 2016 (CTP2.0) hadr_physical_seeding_forwarder_state_change sqlserver Physical Seeding Forwarder Side State Change.
Microsoft SQL Server 2016 (CTP2.0) hadr_physical_seeding_forwarder_target_state_change sqlserver Physical Seeding Forwarder Target Side State Change.
Microsoft SQL Server 2016 (CTP2.0) hadr_physical_seeding_progress sqlserver Physical Seeding Progress Event.
Microsoft SQL Server 2016 (CTP2.0) hadr_physical_seeding_restore_state_change sqlserver Physical Seeding Restore Side State Change.
Microsoft SQL Server 2016 (CTP2.0) hadr_physical_seeding_submit_callback sqlserver Physical Seeding Submit Callback Event.
Microsoft SQL Server 2016 (CTP2.0) hadr_send_harden_lsn_message sqlserver Occurs when we’re crafting a message to send containing a new hardened LSN on a secondary. Test only.
Microsoft SQL Server 2016 (CTP2.0) hadr_transport_configuration_state sqlserver Indicates session state changes
Microsoft SQL Server 2016 (CTP2.0) hadr_transport_dump_dropped_message sqlserver Use this event to trace dropped HADR transport messages throughout the system.
Microsoft SQL Server 2016 (CTP2.0) hadr_transport_dump_failure_message sqlserver Use this event to help trace HADR failure messages.
Microsoft SQL Server 2016 (CTP2.0) hadr_transport_dump_preconfig_message sqlserver Use this event to help trace HADR preconfig messages.
Microsoft SQL Server 2016 (CTP2.0) hadr_transport_sync_send_failure sqlserver Synchronous send failure in hadr transport.
Microsoft SQL Server 2016 (CTP2.0) hadr_transport_ucs_registration sqlserver Reports UCS registration state changes
Microsoft SQL Server 2016 (CTP2.0) json_depth_error sqlserver Occurs when depth of json text being parsed is bigger than 128.
Microsoft SQL Server 2016 (CTP2.0) json_parsing_error sqlserver Indicates json parser error. Occurs when json format is not valid.
Microsoft SQL Server 2016 (CTP2.0) json_stackoverflow_error sqlserver Json parser stack overflow.
Microsoft SQL Server 2016 (CTP2.0) json_unescaped_character sqlserver Jsonparser hitted unescaped character in json string.
Microsoft SQL Server 2016 (CTP2.0) log_pool_cache_miss sqlserver Occurs when a log consumer attempts to lookup a block from the log pool but fails to find it.
Microsoft SQL Server 2016 (CTP2.0) log_pool_push_no_free_buffer sqlserver Occurs when log pool push fails to get a free buffer and bails out.
Microsoft SQL Server 2016 (CTP2.0) login_event sqlserver This is an abbreviated version of process_login_finish, containing only the columns required by external monitoring telemetry pipeline.
Microsoft SQL Server 2016 (CTP2.0) notify_on_clr_disabled sqlclr Event_ClrDisabled has been triggered in ClrHost.
Microsoft SQL Server 2016 (CTP2.0) on_app_domain_failure sqlclr AppDomain hit a failure.
Microsoft SQL Server 2016 (CTP2.0) on_app_domain_unloading sqlclr AppDomain is unloading.
Microsoft SQL Server 2016 (CTP2.0) on_host_policy_callback sqlclr IHostPolicyManager received an event.
Microsoft SQL Server 2016 (CTP2.0) on_host_policy_failure sqlclr IHostPolicyManager received an event.
Microsoft SQL Server 2016 (CTP2.0) page_cache_trace sqlserver Modification of the page cache.
Microsoft SQL Server 2016 (CTP2.0) premature_systemthread_wakeup sqlos system thread is woken up prematurely
Microsoft SQL Server 2016 (CTP2.0) private_login_accept sqlserver TDS connection accept event that is logged to private MDS table.
Microsoft SQL Server 2016 (CTP2.0) private_login_finish sqlserver TDS login finish event that is logged to private MDS table.
Microsoft SQL Server 2016 (CTP2.0) process_login_finish sqlserver This event is generated when server is done processing a login (success or failure).
Microsoft SQL Server 2016 (CTP2.0) query_execution_batch_filter sqlserver Occurs when batch processing filters one batch using expression services.
Microsoft SQL Server 2016 (CTP2.0) query_execution_batch_spill_started sqlserver Occurs when batch operator runs out of granted memory and initiates spilling to disk of another partition of in-memory data.
Microsoft SQL Server 2016 (CTP2.0) query_execution_column_store_rowgroup_scan_finished sqlserver Occurs when row bucket processor finishes column store row group scan.
Microsoft SQL Server 2016 (CTP2.0) query_execution_column_store_segment_scan_finished sqlserver Occurs when row bucket processor finishes column store segment scan.
Microsoft SQL Server 2016 (CTP2.0) query_execution_column_store_segment_scan_started sqlserver Occurs when column segment scan starts.
Microsoft SQL Server 2016 (CTP2.0) query_memory_grant_blocking sqlserver Occurs when a query is blocking other queries while waiting for memory grant
Microsoft SQL Server 2016 (CTP2.0) query_memory_grant_usage sqlserver Occurs at the end of query processing for queries with memory grant over 5MB to let users know about memory grant inaccuracies
Microsoft SQL Server 2016 (CTP2.0) query_store_background_task_persist_finished qds Fired if the background task for Query Store data persistence is completed successfully
Microsoft SQL Server 2016 (CTP2.0) query_store_background_task_persist_started qds Fired if the background task for Query Store data persistence started execution
Microsoft SQL Server 2016 (CTP2.0) query_store_capture_policy_abort_capture qds Fired when an UNDECIDED query failed to transition to CAPTURED.
Microsoft SQL Server 2016 (CTP2.0) query_store_capture_policy_evaluate qds Fired when the capture policy is evaluated for a query.
Microsoft SQL Server 2016 (CTP2.0) query_store_capture_policy_start_capture qds Fired when an UNDECIDED query is transitioning to CAPTURED.
Microsoft SQL Server 2016 (CTP2.0) query_store_db_data_structs_not_released qds Fired if Query Store data structures are not released when feature is turned OFF.
Microsoft SQL Server 2016 (CTP2.0) query_store_db_diagnostics qds Periodically fired with Query Store diagnostics on database level.
Microsoft SQL Server 2016 (CTP2.0) query_store_db_settings_changed qds Fired when Query Store settings are changed.
Microsoft SQL Server 2016 (CTP2.0) query_store_db_whitelisting_changed qds Fired when Query Store database whitelisting state is changed.
Microsoft SQL Server 2016 (CTP2.0) query_store_generate_showplan_failure qds Fired when Query Store failed to store a query plan because the showplan generation failed.
Microsoft SQL Server 2016 (CTP2.0) query_store_global_mem_obj_size_kb qds Periodically fired with Query Store global memory object size.
Microsoft SQL Server 2016 (CTP2.0) query_store_load_started qds Fired when query store load is started
Microsoft SQL Server 2016 (CTP2.0) query_store_schema_consistency_check_failure qds Fired when the QDS schema consistency check failed.
Microsoft SQL Server 2016 (CTP2.0) query_store_size_retention_cleanup_finished qds Fired when size retention policy clean-up task is finished.
Microsoft SQL Server 2016 (CTP2.0) query_store_size_retention_cleanup_skipped qds Fired when starting of size retention policy clean-up task is skipped because its minimum repeating period did not pass yet.
Microsoft SQL Server 2016 (CTP2.0) query_store_size_retention_cleanup_started qds Fired when size retention policy clean-up task is started.
Microsoft SQL Server 2016 (CTP2.0) query_store_size_retention_plan_cost qds Fired when eviction cost is calculated for the plan.
Microsoft SQL Server 2016 (CTP2.0) query_store_size_retention_query_cost qds Fired when query eviction cost is calculated for the query.
Microsoft SQL Server 2016 (CTP2.0) query_store_size_retention_query_deleted qds Fired when size based retention policy deletes a query from Query Store.
Microsoft SQL Server 2016 (CTP2.0) query_trace_column_values sqlserver Trace output column values of each row on each query plan operator
Microsoft SQL Server 2016 (CTP2.0) recalculate_mem_target sqlos New Memory Targets which are set after RecalculateTarget is executed
Microsoft SQL Server 2016 (CTP2.0) remote_data_archive_db_ddl sqlserver Occurs when the database T-SQL ddl for stretching data is processed.
Microsoft SQL Server 2016 (CTP2.0) remote_data_archive_provision_operation sqlserver Occurs when a provisioning operation starts or ends.
Microsoft SQL Server 2016 (CTP2.0) remote_data_archive_query_rewrite sqlserver Occurs when RelOp_Get is replaced during query rewrite for Stretch.
Microsoft SQL Server 2016 (CTP2.0) remote_data_archive_table_ddl sqlserver Occurs when the table T-SQL ddl for stretching data is processed.
Microsoft SQL Server 2016 (CTP2.0) remote_data_archive_telemetry sqlserver Occurs whenever an on premise system transmits a telemetry event to Azure DB.
Microsoft SQL Server 2016 (CTP2.0) remote_data_archive_telemetry_rejected sqlserver Occurs whenever an AzureDB Stretch telemetry event is rejected
Microsoft SQL Server 2016 (CTP2.0) report_login_failure sqlserver This event is generated for a TDS login failure.
Microsoft SQL Server 2016 (CTP2.0) rpc_starting_aggregate sqlserver Occurs periodically, aggregating all occasions an rpc call is started.
Microsoft SQL Server 2016 (CTP2.0) rpc_starting_aggregate_xdb sqlserver Occurs periodically, aggregating all occasions an rpc call is started.
Microsoft SQL Server 2016 (CTP2.0) sql_batch_starting_aggregate sqlserver Occurs periodically, aggregating all occasions a sql batch is started.
Microsoft SQL Server 2016 (CTP2.0) sql_batch_starting_aggregate_xdb sqlserver Occurs periodically, aggregating all occasions a sql batch is started.
Microsoft SQL Server 2016 (CTP2.0) startup_dependency_completed sqlserver Occurs on the completion of a startup dependency in the SQL Server startup sequence
Microsoft SQL Server 2016 (CTP2.0) stretch_codegen_errorlog sqlserver Reports the output from the code generator
Microsoft SQL Server 2016 (CTP2.0) stretch_codegen_start sqlserver Reports the start of stretch code generation
Microsoft SQL Server 2016 (CTP2.0) stretch_create_migration_proc_start sqlserver Reports the start of migration procedure creation
Microsoft SQL Server 2016 (CTP2.0) stretch_create_remote_table_start sqlserver Reports the start of remote table creation
Microsoft SQL Server 2016 (CTP2.0) stretch_create_update_trigger_start sqlserver Reports the start of create update trigger for remote data archive table
Microsoft SQL Server 2016 (CTP2.0) stretch_database_disable_completed sqlserver Reports the completion of a ALTER DATABASE SET REMOTE_DATA_ARCHIVE OFF command
Microsoft SQL Server 2016 (CTP2.0) stretch_database_enable_completed sqlserver Reports the completion of a ALTER DATABASE SET REMOTE_DATA_ARCHIVE ON command
Microsoft SQL Server 2016 (CTP2.0) stretch_database_events_submitted sqlserver Reports the completion telemetry transfer
Microsoft SQL Server 2016 (CTP2.0) stretch_migration_debug_trace sqlserver Debug trace of stretch migration actions.
Microsoft SQL Server 2016 (CTP2.0) stretch_migration_queue_migration sqlserver Queue a packet for starting migration of the database and object.
Microsoft SQL Server 2016 (CTP2.0) stretch_migration_sp_stretch_get_batch_id sqlserver Call sp_stretch_get_batch_id
Microsoft SQL Server 2016 (CTP2.0) stretch_migration_start_migration sqlserver Start migration of the database and object.
Microsoft SQL Server 2016 (CTP2.0) stretch_sync_metadata_start sqlserver Reports the start of metadata checks during the migration task.
Microsoft SQL Server 2016 (CTP2.0) stretch_table_codegen_completed sqlserver Reports the completion of code generation for a stretched table
Microsoft SQL Server 2016 (CTP2.0) stretch_table_provisioning_step_duration sqlserver Reports the duration of a stretched table provisioning operation
Microsoft SQL Server 2016 (CTP2.0) stretch_table_remote_creation_completed sqlserver Reports the completion of remote execution for the generated code for a stretched table
Microsoft SQL Server 2016 (CTP2.0) stretch_table_row_migration_event sqlserver Reports the completion of the migration of a batch of rows
Microsoft SQL Server 2016 (CTP2.0) stretch_table_row_migration_results_event sqlserver Reports an error or completion of a successful migration of a number of batches of rows
Microsoft SQL Server 2016 (CTP2.0) stretch_table_unprovision_completed sqlserver Reports the completion removal of local resources for a table that was unstretched
Microsoft SQL Server 2016 (CTP2.0) stretch_table_validation_error sqlserver Reports the completion of validation for a table when the user enables stretch
Microsoft SQL Server 2016 (CTP2.0) stretch_unprovision_table_start sqlserver Reports the start of stretch table un-provisioning
Microsoft SQL Server 2016 (CTP2.0) trace_dump_deleted_object_table_row XtpEngine Dump deleted object table row
Microsoft SQL Server 2016 (CTP2.0) trust_verification_failed sqlserver Occurs when a SQL Server binary fails Authenticode signature verification.
Microsoft SQL Server 2016 (CTP2.0) ucs_negotiation_completion ucs UCS transport connection negotiation completed
Microsoft SQL Server 2016 (CTP2.0) unable_to_verify_trust sqlserver Occurs when SQL Server is unable to perform Authenticode signature verification on binaries.
Microsoft SQL Server 2016 (CTP2.0) xio_blob_properties_obtained sqlserver Windows Azure Storage blob property is obtained from response header.
Microsoft SQL Server 2016 (CTP2.0) xio_failed_request sqlserver Failed to complete a request to Windows Azure Storage.
Microsoft SQL Server 2016 (CTP2.0) xio_header_obtained sqlserver Response header is obtained from request to Windows Azure Storage.
Microsoft SQL Server 2016 (CTP2.0) xio_read_complete sqlserver Read complete from Windows Azure Storage response.
Microsoft SQL Server 2016 (CTP2.0) xio_request_opened sqlserver A request is opened to Windows Azure Storage.
Microsoft SQL Server 2016 (CTP2.0) xio_send_complete sqlserver Request send to Windows Azure Storage is complete.
Microsoft SQL Server 2016 (CTP2.0) xio_write_complete sqlserver Request send to Windows Azure Storage is complete.
Microsoft SQL Server 2016 (CTP2.0) xstore_acquire_lease sqlserver The properties of the lease acquire reques.
Microsoft SQL Server 2016 (CTP2.0) xstore_create_file sqlserver Creating an XStore file has been attempted with the options below.
Microsoft SQL Server 2016 (CTP2.0) xstore_debug_trace sqlserver Telemetry tracing event has occurred.
Microsoft SQL Server 2016 (CTP2.0) xstore_lease_renewal_request sqlserver Attempt to renew blob lease
Microsoft SQL Server 2016 (CTP2.0) xtp_alter_table sqlserver Occurs at start of XTP table altering.
Microsoft SQL Server 2016 (CTP2.0) xtp_ckptctrl_abort_checkpoint XtpEngine Indicates that the checkpoint close thread aborted a checkpoint.
Microsoft SQL Server 2016 (CTP2.0) xtp_ckptctrl_close_checkpoint XtpEngine Indicates that the checkpoint close thread hardened a checkpoint.
Microsoft SQL Server 2016 (CTP2.0) xtp_ckptctrl_close_install_merge XtpEngine Indicates that the checkpoint close thread installed a merge.
Microsoft SQL Server 2016 (CTP2.0) xtp_ckptctrl_new_segment_definition XtpEngine Indicates that the checkpoint controller processed a new segment definition.
Microsoft SQL Server 2016 (CTP2.0) xtp_ckptctrl_storage_array_grow XtpEngine Indicates the XTP storage array has grown in size.
Microsoft SQL Server 2016 (CTP2.0) xtp_drop_table sqlserver Occurs after an XTP table has been dropped.
Microsoft SQL Server 2016 (CTP2.0) xtp_merge_request_start XtpEngine Indicates that an XTP storage merge was requested.
Microsoft SQL Server 2016 (CTP2.0) xtp_merge_request_stop XtpEngine Indicates that an XTP storage merge request ended.
Microsoft SQL Server 2016 (CTP2.0) xtp_merge_start XtpEngine Indicates that an XTP storage merge range is starting.
Microsoft SQL Server 2016 (CTP2.0) xtp_merge_stop XtpEngine Indicates that an XTP storage merge range completed.
Microsoft SQL Server 2016 (CTP2.0) xtp_redo_log_corruption XtpEngine Indicates that the redo process failed due to log corruption.
Microsoft SQL Server 2016 (CTP2.0) xtp_root_file_deserialized XtpEngine Indicates that the load of a checkpoint root file is complete.
Microsoft SQL Server 2016 (CTP2.0) xtp_root_file_serialized XtpEngine Indicates that the write of the checkpoint root file is complete.

There you have it, currently 165 new events available to help monitor and troubleshoot SQL Server.

Database Drops in SQL 2012

In the previous article on this topic (which can be read here), I discussed the problem of having a database get dropped and the need to find out who dropped the database and when they dropped it.  In that article, I demonstrated how the information (at least some of it) could be found through the use of Extended Events.

What I forgot to mention is the method I shared was for SQL Server 2014. While the events do exist for SQL Server 2012, there is a minor modification that needs to be made in order to avoid the dreaded error message that says something like:

Msg 25713, Level 16, State 23, Line 1
The value specified for event attribute or predicate source, “object_type”, event, “object_created”, is invalid.

I am sure many of us would rather not have to deal with such a terrible thing as an error when we want to do something that should just work. Well, here is the fix for that error if you tried to deploy that XE Session to a previous version (such as 2012).

Do you see that change? Instead of using the map_value in 2012, one must use  the map_key. This was a good change in 2014 to allow us to use human friendly terms instead of needing to lookup the map_key for everything like in 2012.

In following the theme from the previous article, here is the rest of the setup for testing this XEvent session to see how it would trap that data and how to parse the data from the session.

Enjoy!

Nice and simple for a test. This should be enough to have trapped some data for our XEvent session.  Let’s parse the data and take a look.

With the XEvent session started, and a database that has been created and dropped, this last query will produce some data to help track who has been adding or removing databases from your SQL Instance.  If you have run the example code, you should see data very similar to what is shown in this next image.

xe_results

In the attached image, I have separated the pair of statements for the DB create from the pair of statements for the DB drop (recall that I mentioned each will fire twice).  I hope this serves you well in your attempts to reign in the control of your environment and discover who may be creating rogue databases or even dropping the databases that are essential to the business.

«page 1 of 46






Calendar
July 2015
M T W T F S S
« Jun    
 12345
6789101112
13141516171819
20212223242526
2728293031  
Content
SQLHelp

SQLHelp


Welcome , today is Wednesday, July 29, 2015