The Rambling DBA: Jonathan Kehayias

Times they are a changing…

2011-02-25T18:23:04Z

If you follow me on twitter ( @SQLSarg ), you already know that this has been a week of big announcements for me. Wednesday afternoon Paul Randal ( Blog | Twitter ) announced that I joined SQLskills.com as a full time employee, and Thursday afternoon, Joe Sack ( Blog | Twitter ) announced that I passed the Microsoft Certified Masters for SQL Server 2008 . As a part of my transition to working for SQLskills.com full time, I will be changing blogs over to the SQLskills.com site. You can read about...(read more)

TSQL Tuesday #15 – Maintaining Your Sanity While Managing Large Environments

2011-02-08T04:19:00Z

This month’s TSQL Tuesday event is being hosted by Pat Wright (Blog | Twitter) and the topic this month is Automation! “ I figured that since many of you out there set a goal this year to blog more and to learn Powershell then this Topic should help in both of those goals. So the topic I have chosen for this month is Automation! It can be Automation with T-SQL or with Powershell or a mix of both. Give us your best tips/tricks and ideas for making our lives easier through Automation.” Automation is...(read more)

Making Use of Plan Explorer in my own Environment

2011-02-03T03:50:47Z

Back in October 2010, I briefly blogged about the SQL Sentry Plan Explorer in my blog post wrap up for SQL Bits 7 and how impressed I was with what I saw from a Alpha demo standpoint from Greg Gonzalez ( Blog | Twitter ) while I was at SQLBits 7 in York. To be 100% honest and transparent, Greg gave me early access to this tool after discussing it at SQLBits 7, and I had the opportunity to test a number of pre-Beta releases where I was able to offer significant feedback and submit bugs in the...(read more)

Parsing the sqlserver.sql_text Action in Extended Events by Offsets

2011-01-31T02:05:43Z

A couple of weeks back I received an email from a member of the community who was reading the XEvent a Day blog series and had a couple of interesting questions about Extended Events. This person had created an Event Session that captured the sqlserver.sql_statement_completed and sqlserver.sql_statement_starting Events and wanted to know how to do a correlation between the related Events so that the offset information from the starting Event could be used to find the statement of the completed...(read more)

Using Substring() in XML FLOWR Queries

2011-01-25T03:41:24Z

Tonight I was monitoring the #sqlhelp hashtag on Twitter for a response to a question I asked when Randy Knight ( Twitter ) asked a question about using SUBSTRING in FLOWR statements with XML. #sqlhelp Is there a way to do a SQL Type "LIKE" or "SUBSTRING" in the where clause of FLWOR statement? Need to evaluate just first n chars. By the time I posted a response, Randy had figured out how to use the contains() function to solve his problem, but I am going to blog this because...(read more)

Did the Community Lose It’s Focus, or Did I?

2011-01-10T23:05:00Z

Late Thursday night, ok it was actually very early Friday morning, I wrote a blog post that stirred a bit of a controversy in the community. While the outcome of the discussion that was sparked by that post in the community has been good, it is definitely a case where the end isn’t justified by the means. Hindsight is always 20/20, and while I stand by the point I was trying to make with that post, there are a number of ways I could have gone about making that point without risking...(read more)

Has the SQL Community Lost its Focus?

2011-01-07T06:30:00Z

Yesterday, Thomas LaRock’s blog post, WMI Code Creator , was brought to my attention by a member of the SQL Community. I subscribe to Tom’s blog in my blog reader so eventually I’d like to think that his post would have come to my attention, but to be perfectly honest, I have been to busy with other obligations lately that have made reading blog posts almost impossible. When I looked at Tom’s post, I was kind of put off when I did a copy paste of the Code from it and got the following: DECLARE @WmiServiceLocator...(read more)

Using Extended Events in SQL Server Denali CTP1 to Map out the TransactionLog SQL Trace Event EventSubClass Values

2011-01-03T06:39:00Z

John Sansom ( Blog | Twitter ) asked on the MSDN Forums about the meaning/description for the numeric values returned by the EventSubClass column of the TransactionLog SQL Trace Event. John pointed out that this information is not available for this Event like it is for the other events in the Books Online Topic ( TransactionLog Event Class ), or in the sys.trace_subclass_values DMV. John wanted to know if there was a way to determine this information. I did some looking and found an old SQL Server...(read more)

An XEvent a Day (31 of 31) – Event Session DDL Events

2010-12-31T19:00:00Z

To close out this month’s series on Extended Events we’ll look at the DDL Events for the Event Session DDL operations, and how those can be used to track changes to Event Sessions and determine all of the possible outputs that could exist from an Extended Event Session. One of my least favorite quirks about Extended Events is that there is no way to determine the Events and Actions that may exist inside a Target, except to parse all of the the captured data. Information about the Event...(read more)

An XEvent a Day (30 of 31) – Tracking Session and Statement Level Waits

2010-12-31T02:00:00Z

While attending PASS Summit this year, I got the opportunity to hang out with Brent Ozar ( Blog | Twitter ) one afternoon while he did some work for Yanni Robel ( Blog | Twitter ). After looking at the wait stats information, Brent pointed out some potential problem points, and based on that information I pulled up my code for my PASS session the next day on Wait Statistics and Extended Events and made some changes to one of the demo’s so that the Event Session only focused on those potentially...(read more)

An XEvent a Day (29 of 31) – The Future – Looking at Database Startup in Denali

2010-12-30T02:30:00Z

As I have said previously in this series, one of my favorite aspects of Extended Events is that it allows you to look at what is going on under the covers in SQL Server, at a level that has never previously been possible. SQL Server Denali CTP1 includes a number of new Events that expand on the information that we can learn about how SQL Server operates and in today’s blog post we’ll look at how we can use those Events to look at what happens when a database starts up inside of SQL Server. First...(read more)

Learn Who Started that Trace with the Default Trace

2010-12-29T19:00:00Z

This is not Extended Event related but it came from a question on Twitter about how to tell who and from what machine a server side trace was created, and there is no way to explain this in 140 characters so here’s a blog post. This information is tracked in the Default Trace and can be found by querying for EventClass 175 which is the Audit Server Alter Trace Event trace_event_id from sys.trace_events. select trace_event_id , name from sys . trace_events where name like '%trace%' To query...(read more)

An XEvent a Day (28 of 31) – Tracking Page Compression Operations

2010-12-29T02:00:00Z

The Database Compression feature in SQL Server 2008 Enterprise Edition can provide some significant reductions in storage requirements for SQL Server databases, and in the right implementations and scenarios performance improvements as well. There isn’t really a whole lot of information about the operations of database compression that is documented as being available in the DMV’s or SQL Trace. Paul Randal pointed out on Twitter today that sys.dm_db_index_operational_stats() provides...(read more)

An XEvent a Day (27 of 31) – The Future - Tracking Page Splits in SQL Server Denali CTP1

2010-12-28T02:00:00Z

Nearly two years ago Kalen Delaney blogged about Splitting a page into multiple pages , showing how page splits occur inside of SQL Server. Following her blog post, Michael Zilberstein wrote a post, Monitoring Page Splits with Extended Events , that showed how to see the sqlserver.page_split Events using Extended Events. Eladio Rincón also blogged about Using XEvents (Extended Events) in SQL Server 2008 to detect which queries are causing Page Splits , but not in relation to Kalen’s blog...(read more)

An XEvent a Day (26 of 31) – Configuring Session Options

2010-12-27T02:00:00Z

There are 7 Session level options that can be configured in Extended Events that affect the way an Event Session operates. These options can impact performance and should be considered when configuring an Event Session. I have made use of a few of these periodically throughout this months blog posts, and in today’s blog post I’ll cover each of the options separately, and provide further information about their usage. Mike Wachal from the Extended Events team at Microsoft, talked...(read more)