Finding Deprecated Uses in SQL Server



How well do you know your environment? You probably know all of the jobs that are running, the frequency that indexes need to be rebuilt, and even which users have which level of access to each object in the SQL Server instance. Do you know that your applications are accessing deprecated datatypes over 300 million times a week? What if your TSQL constructs are a bit archaic? Do you know that the code needs to be updated? Do you know how to find deprecated uses in SQL Server?

In this article, I will explore how to use Extended Events to track feature use and abuse. To be more precise, I will share how this tool can help you better understand all of the ways that your applications have been abusing your database by continuing to employ the use of deprecated features, syntax, or constructs in general. In case you are a bit behind in your exploration of XEvents, I have the perfect solution for you – my series on the topic that is continually growing. You can explore the full list of articles in the series by visiting the table of contents – here.

Audit Deprecated Uses

redxI would dare say that most data professionals think there is some use of deprecated constructs, datatypes or features within their environment. I would double down on that and say that most do not know just how bad it really may be. To find just how bad it really is, we need to audit for the use and abuse of these deprecation events.

Right here would be a good time to point out that your mileage may vary. Some items that are deprecated are more painful than others. Some may be deprecated and may have been on the list for 10+ years at this point. The point is, know your environment and then use good judgement to determine which items in your results need the most attention to fix and update. Why? Well, things really may break especially if you are looking to upgrade to a new version of SQL Server. Just because an item is still available in your current edition, that does not ensure it will still be available in a future release of SQL Server.

Now for the juicy stuff. As I mentioned, finding when and where a deprecated feature or syntax is employed, there are a couple of neat little events within Extended Events that can help to track each time a deprecated feature is accessed or employed. How do we do that? Use the deprecation_announcement and deprecation_final_support events. To help create sessions to track these events, I have the following script to create an “audit” session to do exactly that.


And just in case you are using 2008 or 2008R2, use this version instead.

Slight differences between these two sessions. First, in the 2008 version of the script, I rotten_orangehave to provide database ids instead of names. That is a shortcoming of 2008 and 2008R2 implementations of Extended Events. Additionally, the file target is different between the two (recall that they renamed the file target). And lastly, there are a few actions that I included in the 2012 version of the script that are not available in 2008 and R2.

With the session in place, I am now going to run through some sample scripts that will generate deprecation events to occur. I am sticking with my 2014 instance for this segment. That is important to note because different events may occur for different versions of SQL Server. Additionally, the parse script I will share will require a slight change for 2008 and r2 (again related to the file target name).

Now to take a peek at the data with this next script.

Now at long last, I can see what kind of data I am generating (they are really wide so I am just posting a snip).






With this, I can see the feature_id along with the description and even the tsql that generated the event. What you don’t see in this is that I also trap the source machine and the user name. If there is an application name included in the connection string, I also trap that. These pieces of data can prove critical to efficiently troubleshooting and finding the source of these events.

From here, one might wish to explore all of the events generated from this session in order to ensure the environment is properly prepared for upgrade. Most tools do not evaluate the code thoroughly to trap all of these events. Instead they do a cursory look through stored procedures or at the data types. As we all should know, not every piece of SQL code is actually stored in the database or even is it cached at the time of analysis. This is the type of thing that requires a long running trace to prove that you are that rockstar DBA.


In the article today, I have shown how it is possible to see the deprecation alerts that may be generated in your environment. This data is what can help set you apart as a rockstar when it comes time for that migration. If you have yet to read my series on Extended Events, I highly recommend it. You can find that series here.

Events By Feature

sql_features_xeWithin the world of SQL Server there are a few things one can be certain of – things will change. This is true of the features in SQL Server. Additionally, Extended Events is constantly evolving which underscores this constant change.

With all of this change occurring within SQL Server, sometimes it is difficult to figure out how to troubleshoot or track issues that relate to new features. Within the need to figure out how to troubleshoot the new features, there is the need to understand what tools are out there to help troubleshoot.

It is no big secret that Profiler cannot help you trace any of the new features. To help perform the task of tracing events related to new features one must take advantage of the power of Extended Events. Even knowing that you can use XEvents, there is still a need to know what is related to the feature.

In this article, I will show how you can discover the events related to specific features. In addition, I will provide enough base information to help you perform other discovery type queries on your own as you continue your exploration of XEvents. In case you are a bit behind in your exploration of XEvents, I have the perfect solution for you – my series on the topic that is continually growing. You can explore the full list of articles in the series by visiting the table of contents – here.

Events and Features

More and more I am being asked how to track which events belong to which features. I have also been seeing more people ask for a way to list the new features supported by XEvents. Thankfully there is adequate information within the XEvent metadata to help retrieve this type of information. The downside is that there is a bit of homework that must be done across a few versions of SQL Server to help produce the desired information. I have done that work and pulled the information into the following query to help make it easier for anybody else wishing to dive in and compare features and events across versions of SQL Server.

In the preceding query, I have provided an aggregation of the events and features across multiple editions of SQL Server. This aggregate of data is necessary to help see how the features are changing and how the events are changing within the features that remain constant from one version to the next.

Once that data is aggregated, I then provide a couple of sample queries that show what can be done with the data. I need to stress here that I have not provided an exhaustive set of queries to explore this data seven ways to Sunday. I have left those types of exercises up to the reader to explore and experiment.

The first example query shows how to pull the new features that can be “Traced” in the current version of SQL Server on which the query is being executed. The second example provides a difference count between versions of SQL Server to help illustrate the evolution of XEvents within SQL Server. The third query is a simple query to list out the number of events for each feature in your version of SQL Server.

From here, one might wish to explore all of the events that are related to a specific feature. This would be easily accomplished by querying out the data from the #presel temp table based on the feature name. Orrrr…one could query the desired feature by following the guidelines in this article.


In the article today, I have shown how it is possible to see the new events and how they relate to the various features within SQL Server. Being able to correlate events that can trap information about new features can and will help you evolve into that rock-star DBA you are trying to become!

Finding the Right Path

xe_path1I have a fairly large backlog of articles that are in progress or that are planned for my series about Extended Events. Despite the backlog and planned articles, every now and then something else comes up that bumps things around. This article is an example of bumping the schedule around. You can see some of the backlog and the full list of articles in the series by visiting the table of contents – here.

I bring that up for a couple of reasons. First and foremost being that the topic was recently raised as a “need” by some colleagues. The second being that I see the need and how it was lacking in coverage by anything I had already written.

What is this gaping hole in the coverage of Extended Events? To be honest, it is not a very complicated topic or very difficult gap to fill. It’s just something that has been overlooked. The gap boils down to this: how does one consistently find the correct path to the Extended Event Log file (XEL file)?

Filling the Gap

The gap I will be working to fill in this article deals with consistently finding the file path for Extended Event (XE) sessions. This gap rises due a few different things such as the ability to define a target in different manners, being able to move the logs directory, or even the fact that a target may not be added to the session (let alone a file target). These causes can all contribute to a bit of frustration and may pose as different symptoms when trying to get the file path.

One additional complication is tied to the running state of a session. For that complication, I talked briefly about it in my article about better practices. The state of the session could have an impact and could cause frustration when trying to retrieve the file path. All of these things are considerations that must be made when trying to retrieve the file path.

To find the file path, let’s start with some basics. The default path for XEL files is in the log directory of the instance. In addition to this default behavior, each session stores metadata about running sessions as well as deployed sessions that is accessible from DMVs and system catalogs.

Log Path

The first basic to tackle is the log path for the instance. I can query for the log path of the error log for the instance and rely on that as the path of my xel files. Should I choose this method, then I could execute the following query.

Executing that query on my SQL 2014 instance produces the following results:


Now the obvious problems with this method come from the fact that relying on this data is relying upon an assumption that you have set all of your XE Sessions to use the default log path. If you have declared your sessions to use a file target and did not specify a path, then the assumption is safe. However, if you are detail oriented, you probably have been somewhat explicit in how you define your event file target. This brings us to the next topic – defining the path.

Defining The Path

While a bit of bird-walk, it is necessary to cover this topic at this juncture. This brief discussion will help to understand some of the other issues with retrieving the path consistently.

When defining the path of the event path, there is a bit of flexibility in how one can define the file to be used. You can either declare the file as just the file name, or you can define the file as the folder path along with the file name. Both methods are completely legitimate. Unfortunately, this flexibility is what causes some of the pain with retrieving the file path consistently.

Let’s take a look at two quick, and acceptable, ways to add an event file to an XE Session. I will be re-using a session from a previous article for these examples.

This is a pretty standard format I use for creating my sessions. The section I want to highlight though is the set of the filename near the end of the script. I will generally define the entire path for my xel files when I create a session – just as I have done in this example. Defining this path helps me to know exactly where I am putting the session for starters. Equally as important is that this path is easier to retrieve from metadata because I have explicitly defined the path.

Take this next example of the same session but with one minor difference.

The minor difference in this example is just in how the filename was defined. It is technically accurate and acceptable to only use the filename instead of the path and filename as I did in the previous example. The problem here comes from the retrieval of the path from metadata.

Getting the Path

In the previous two examples, I showed two methods of defining the filename within a session. In the former example, this means I can more easily find the path. However, if I use the script in the beginning of the article to find my xel file paths, then I have made an inaccurate assumption. This leads to an inconsistency in how the file path is fetched. In the latter example, the assumptions concerning the default log path would be valid and could be applied here.

Since the assumptions do not prove to be consistent or accurate across the board, we need to both evaluate how the data for each would look and we would need to see how to retrieve this path more consistently. First up is looking at how the data for each of these example sessions would be stored in metadata.

In the preceding query, I am looking at the deployed session metadata for two sessions, both of which start with “AuditSelect”. In the sys.server_event_session_fields view, there is an attribute called name that contains the value “filename”. This value will only exist if the session has an event file target defined for the session. Running the query will yield the following results (on my system).


In this result set, I can see there is one of each of the two event file definition methods I described in the previous section. In the green highlight you will see that I have the entire filepath. In the red highlight, you will only see the filename. Nothing special has been done to this data in the return as you can verify from the posted script. These are the actual stored values. So this would indeed seem like we have a bit of a problem, right?

Well, this is where we get to be a little bit imaginative and employ a script such as the following:

Wow, that is considerably longer than the first example in the article. That is true! This script accounts for a few of the conditions that cause inconsistencies (but not yet all of them) in retrieving the file path for a deployed event session. The key here is to understand this will fetch the path whether you enter a complete path or not while creating the session. I also through a check in there to confirm that the session has a file target as well as a check to ensure the session is deployed to the server.

Looking at the output, I get results that are somewhat friendly and easy to understand without any of the guesswork.


What if the session has never been started and did not have the full path declared? Well, that is one of the inconsistencies I am working on still.

There is an alternative to this method as well. For the time being, this method would also be recommended in the event the session being researched happens to be one of the system sessions that is “private”. This next code chunk will show two different methods to parse the file path from running session metadata in the DMVs.


In the article today, I have shown some of the internals to retrieving file paths for Extended Event Sessions. I dove into metadata to pull out the path for the session and discussed some concerns for some of these methods. In the end, you have a few viable options to help retrieve the file path in a more consistent fashion.

Easily Shred Event Data

shred_xeIt has been nearly a year since I started an extensive series about Extended Events. Previous to that start, I had already written a bunch of articles about Extended Events.

Among the batch of articles preceding the 60 day series one can find an article about shredding extended event data. Then as a part of the series (the first article in the 60 day series) one can find how to shred the action payload data attached to an extended event session.

You can read the article on shredding XEvent payload data here. Then you can follow that up by reading how to shred the Action data here. And once you are done with those and you really want to learn more about Extended Events, please read the whole series which can be found here.

All of that reading to catch up should keep you busy for a tad bit.


A year is a really long time to go without updating a script – according to some. Today, I have an update for both of the XML shredding scripts you just read about in the list of articles mentioned moments earlier in this post.

Before I dive into the script, let’s revisit a sample of the XML from an event session payload. Within an event session, you can see that there are nodes for both the data and the actions (highlighted in green and red). With all of this information found within the same XML for the session, it somewhat makes sense to try and parse all of the data at once.


In addition to parsing all of the XML for the data and the actions at the same time, it seems to also make sense to generate the statements that would parse the XML within a single effort. As you would have noted, my previous scripts were just that – scripts. That implies executing a separate script for each the data and the actions. Maybe it would make more sense to execute a single script.

leaving_painIt is that notion of a single script that constitutes this upgraded version of the script.

One Script

In the following script, I have tried to accomplish just that – a single script to create the entire XML parser for me, for you, and for anybody wishing to use it. I don’t want to have to remember the subtle nuances of how to parse each of the events each time I need to parse the session data. I want something that is quick, easy, and repeatable.

With all of that said, here is the script that I now use to parse my session data. You should notice that it has been simplified and is more extensive now.

Not only does this script slice and dice for you…Wait that’s not quite right.

This script doesn’t just generate the XML shredding strings for the payload and action data. This script also will produce the rest of the tsql statements that should precede and conclude the XML parsing statements. Yes it uses dynamic sql. That is to your advantage in this case.

Could there be other enhancements? Absolutely! More will be coming.

Enjoy this script and happy parsing.

Extra Extra – Read All About It!

From the comments, you will see that Brent Ozar (blog | twitter) made an excellent suggestion. To be honest, I considered sharing some examples during the initial write of this article. I had opted against it then, not considering it in the same way that Brent puts it. So, in this addendum I have a quick example using an XEvent Session that I have not yet written about in all of my articles on Extended Events (pseudo spoiler alert).

Let’s use the AuditSelects XEvent Session I have deployed and running (remember a session can be deployed but may be in the “stopped” state). If I want to figure out what data has been captured for this session, I can take the session name and plug it into the script shown in this article.


Ignore the green tsql comments for now (you can read them direct from the script). Highlighted in gold is the variable for the session name. By entering a valid session name for this variable, I can restrict the results to just the metadata for that specific session. In addition, note that I have highlighted, in a pale chartreuse, two variables that will give me unique column names for each of the events and actions within each session. This is important because some events have the same name for different attributes as other events. If events with the same attribute names are used in the same session, you will get a duplication of data. Maybe you want the duplicate data. Maybe you don’t.

The session name variable is used in each of the next three code segments. Each segment in the script will generate sql statements that will need to be copied into a new query window. I will leave the further breakdown of the script as an exercise for you. What I want to do here is show the results from executing this script.

When I execute the script, I will have four result sets returned to me (in grid mode). It will look something like shown in the following image.


Breaking this down into three colors is probably easiest to see how things fit together. Highlighted in red, I have the build out of the pre-processing statements and the build of the “select” statement that will be returning our data to us. Then in blue comes the guts of the query – all of the beautiful XML parsing statements. The segments in blue correlates to the columns in the select list. And at the bottom of the image I have the finishing pieces that includes my predicates, from, and joins. Each of the highlighted segments will then be copied and pasted to a new query window and look something like the following.


And there you have it. Sure there are still some manual steps in it, but you no longer need to memorize all of that glorious XML parsing syntax. You only need to copy and paste with this version of the script.

Enjoy your adventures in parsing XML!

Shredding Extended Event Actions

lovehatedice_v1The other day I wrote about that torrid love/hate relationship DBAs tend to have with working with XML. In that same post, I promised I would have a follow up post about XML in Extended Events.

Well, today I present to you another opportunity for you to renew your love/hate relationship with XML.

In the previous post (which you can read here), I discussed just one facet of shredding the XML related to Extended Events. Today, we have “actions” to discuss. I am not going to get into what an “action” is today. I will save that for a near future post.

For the shredding of the actions, I will use the TreeHuggerCPU Event Session I used in the last article (for the sake of consistency). The session doesn’t necessarily need to be running. I will just pull the actions related to the session from the metadata accessible via the system catalog views.

Should the session be running (and in my case it is for demonstration purposes), I could open the session data and view it as XML and see something like the following:



I have highlighted two different types of nodes available in the XML data. In the previous article, I discussed the “data” nodes and I have highlighted that in red here. Today, we are talking actions, and those are highlighted in green this time around. It is the “action” nodes that we will be shredding via the following script.


With this script, I can either search for all actions tie to an XE Session, for the actions tied to one event within a Single XE Session, for all actions tied to a specific event across multiple sessions, or for all actions that are tied to any event tied to any event session deployed to the server.

Combine this with the previous script and suddenly all of that XML just got several times easier.

Shredding XML in XEvents

lovehateOne of the biggest pains with Extended Events is the thing we love to hate – XML. XML is so foreign to many DBAs. It’s not relational and often brings nightmares of parsing and performance issues.

Despite that, Extended Events takes advantage of XML much like we have seen in so many other areas of SQL Server. You might be familiar with execution plans, SSRS, SSIS, or maybe even the ring buffer. If you look hard enough, you will find XML within the database engine. Whether you love or hate it, you still have to deal with it. Today, I want to dive into a means of dealing with XML, as far as Extended Events is concerned.

Mad Scientist Lab

Let’s head on over to the lab to dive into XML head first. I will be using a session as an example of which I have previously written – here.

If the session is already deployed – great. If not, you may need to create it to execute (successfully) these scripts. Note that I am starting the session and then fetching some data and then stopping the session. To see some data, you may want to wait a few cycles before stopping the event session.

The sole purpose is just so I can take a look at the session data in XML format. Now that I have some data, I would see something that might look like the following:

For today’s lab, I just want to focus on the “data” node while saving the attributes of the event node, and the action node(s) for another discussion.



The “data” node happens to be the data that is directly tied to an Extended Event event. When looking at the event metadata, this would be called the event columns. Knowing that all of these columns follow a fairly standard format can make it a bit easier to figure out how to query this data. One of the daunting things with XML is figuring out how to query the XML data to make it more relational – a format we may be more accustomed to seeing (as DBAs).

Due to the daunting task of figuring out how to query the XML and because it is a pretty decent format for consumption, I decided to simplify the entire process. Why not write some code that will write the XML parsing code for me? And that is what we have here.

This script will take an event session name, an Extended Event event name, or a combination of both (imagine having multiple events tied to a session) to produce the XML strings automagically. This script does only produce some pretty generic column aliases, so that part is left to the user of the script to edit after generating the XML parse statements.

With this script, I can quickly retrieve all of the XML parse statements for all of the data nodes within the session or event that I specify. This can significantly reduce the amount of time taken to produce a usable script to consume the event session data.

This is just the script to parse the event data. If there are actions tied to the session, this will not produce the statements for those actions. The script for that will be provided in a future article. Stay tuned!

ShowPlan XML

Categories: News, Professional, SSC
Comments: No Comments
Published on: March 28, 2011

I just ran into something that I hadn’t noticed in SQL Server.  It struck me as somewhat interesting but is really not too big of a deal.

While demonstrating that the XML plan can be generated when you dump data into a temp table, I happened onto this little ditty.  First, let’s take a look at the simple demo script that I created for the temp table dilemma.

[codesyntax lang=”tsql”]


It was thought, by the questioner, that inserting into a temp table just like I am demonstrating would cause an error to be thrown when trying to show the xml plan.  I can execute this query and it runs just fine.  Now, if I add the showplan_xml to it, like the next script, it will still run fine.

[codesyntax lang=”tsql”]


Pretty straight forward and note that I have batched the showplan separately from the code I am testing.  This is required by the showplan command.  The curious part comes when looking at the results.  Well, not entirely the results – but more accurately the name of the results.

I looked at the column name for this output and thought: “wait, I know this is a SQL 2008 instance.”  I decided to verify the version and results.  Thus, I reran the query below to test.

[codesyntax lang=”tsql”]


This query produces the following results.

This seems pretty conclusive to me that I am running SQL 2008 but the showplan still outputs as 2005 XML Showplan.  I also checked this on 2008 R2 and get the same results.  Microsoft has decided to continue to use the 2005 Showplan schema and did not update that name.  Would it be nice to have an updated label?  Certainly it would.  The flip-side is that the XML Schema does not seem to have changed, so it is merely aesthetic in nature.

If you would like, you can take a look at the schema here.  I only checked the last updated date on the current, SQL 2008, and SQL 2005 sp2 schemas and saw that the date was the same for all of them.

PayPeriod II

Categories: News, Professional
Tags: ,
Comments: No Comments
Published on: February 13, 2010

I recently blogged about a solution I had decided to use in order to solve a problem related to PayPeriod Matching.  The result needed to meet a few requirements.  One of those requirements was to arrive at the results without the use of a Table.  I did not want to create a table that may need to be maintained down the road.  I also wanted to give myself a little more time to make sure the database being used in the warehouse was not involved in one of the ETL processes that actually restores a the database from a different system.  Another requirement was that the current payperiod create an aggregate as well as the prior pay period create an aggregate.  This information was to be consumed by Reporting Services for a report.  At the time, I now realize that, I had insufficient data for this to work as desired.  Thus, I needed to revisit the solution and make a couple of adjustments.

The first adjustment to be made was the creation of a table and the elimination of the CTE.  Though the CTE performed very rapidly in every single test I threw at it, it bogged down during our month-end processing.  That is another process that will be revised shortly and will not be so resource intensive nor will it be so time intensive.  Anyway, that is a topic for another discussion.  Present circumstances required an update to the proc that I created in order to make it perform better and regain, on a more long-term basis, the performance the CTE showed during testing and the first couple of weeks it lived in production prior to month-end processing.  I went ahead and created the table for the payperiods.

Not only did I create the table due to performance reasons, but it also simplified my query later in the proc.  The aggregates for the previous payperiod needed an easier way to be retrieved.  There was also an inaccuracy in my query.  With more data, I was able to spot it.  All previous payperiods were being lumped into the previous payperiod – though I only wanted the immediate previous payperiod and nothing more.  This caused the aggregates to be incorrect.  The solution could have still been achieved through the use of the CTE, however I wanted to simplify it a little and produce a faster result.

My final solution does not eliminate all CTE’s – merely the PayPeriod Table population CTE.  Now I use a CTE to retrieve the current payperiod and then recursively pull in the previous payperiod.  The table was created exactly like the CTE with an ID field, PeriodStart and PeriodEnd.  I decided the simplest method to ensure I would only aggregate on the two payperiods in question was to only pull those two payperiods into the query.  I wanted to be certain that I could only have two periods in play at any time.

[codesyntax lang=”sql”]


With this method, you can see that I peform a top 2 operation in the base query from the PayPeriods table.  Without the recursive definition on this query, the base query will only return 1 record.  With the recursion, it will only return two records.  In addition to that change, I changed the Left Joins later in the query to the following:

[codesyntax lang=”sql”]


And then one final change of note.  I changed the aggregation on the Previous PayPeriod to the following:

[codesyntax lang=”sql”]


This was much simpler than what I was trying to use previously.  I also found a nice side effect of using the top clause in the base query of the CTE.  When using the top in a recursive query, it appears that 

[codesyntax lang=”sql”]


is no longer necessary.  I tested this and retested to verify results.  Just another way of controlling a recursive CTE in SQL server.

I was happy with the first query that I came up to meet this requirement.  I am much more satisfied with this revision.  Query times are <= 1 sec and other performance indicators are positive.  Of course, using the table, I can now use indexes on the date ranges which should help query performance somewhat as well.


Despite meeting the requirements in the last article, and the query being pretty cool in performing what it did – sometimes it really is better to test other methods.  Even with the need to maintain this table (maybe), the consistent performance gains and accuracy outweigh the desire to not create that table.  It is a good idea to test multiple methods in some cases to ensure best path decision is made.  It was a good exercise to come back to this one and redo the query – I learned at least one new trick (really a few).  Learning something new made it worthwhile.

PayPeriod Matching

Categories: News, Professional
Comments: 3 Comments
Published on: January 20, 2010

Recently I was asked to alter a report to pull different more meaningful data.  The particular report was pulling data from the datawarehouse.  It had been recently modified to use a stored procedure (and subsequently improved performance 10 fold).  Due to this change, the report started showing differently.  The report was now paginating on the different matrices.  Due to this minor display change, the creative wheels started turning and new requirements started developing from the real needs of the users of the report.

Through the quick review process, the requirements evolved from first displaying the current week and previous week data to being able to display data grouped by pay period.  If possible, then the goal would be to correlate the data to specific pay periods.  If the process was going to be too complex, or take too long – then sorting by weeks would be adequate.  Correlating the data to current and previous weeks would be rather simple.  The data was already present.  The code was also conducive to making this correlation.  So as a milestone, this task was completed first – as quickly as possible.  Doing this also made it possible to more quickly jump onto the more puzzling requirement – which was more of a desirable, yet unnecessary requirement requested by the business.

Based on this, I came up with some enhanced requirements that would better define this request.

  1. The payperiods need to be accessible to the query
  2. No Physical Lookup Table
  3. Do not hard-code the data in the code.

When reviewing these requirements, I was puzzled as to how to accomplish the task.  I did not want physical structures that required continued maintenance and pay period updates.  I would do that if it was absolutely necessary.  I was hoping to achieve something that required little-to-no maintenance, was fast, accurate, and provided the end-user the desired results.

Thinking about it for a bit, I came across a few ideas but each evaporated when I found a flaw with it.  It soon dawned on me a quick way to do it.  I could use a recursive CTE, one known good pay period start date, and then some date logic.  Date logic by itself did not seem useful enough for me since the pay periods were for specific ranges.

Thus I came up with the following CTE:

[code lang=”sql” smarttabs=”true”]
With periodstarts (StartID, StartDate,EndDate)As (
Select 1 As StartID,@StartDate,dateadd(wk, datediff(wk, 0, @StartDate) + 2, 0) -1
Union All
Select StartID + 1,DATEADD(wk, DATEDIFF(wk, 0, StartDate) + 2, 0) As StartDate,dateadd(wk, datediff(wk, 0, EndDate) + 2, 0)-1 as EndDate
From PeriodStarts
Where DATEADD(wk, DATEDIFF(wk, 0, StartDate) + 2, 0) > StartDate
And Startid < 105

This CTE gives me the ability to create 4 years worth of pay periods on the fly.  There are likely other ways of accomplishing the same task, this one suits me very well.  To build the table, I start with a known valid pay period start date.  From there, I can create the start and end dates of each of the pay periods over the next four years.  To calculate the dates for the start and end for each period beyond the initial seed date, I used a method shown by Lynn Pettis in one of his Blog posts.  In my scenario, each pay period is two weeks.  To calculate the ending day of the pay period, I just subtract 1 day from the Result of adding two weeks to the start date.  Verifying the data, I can see that I have start and end dates that correlate correctly to the pay periods.

The next step was to integrate the above into the query, and thus be able to correctly assign data to either the previous pay period or the current pay period.  I was able to accomplish this through two left joins.  This was the tricky part.  I initially only created the CTE to have Start Dates and no end dates.  This proved to be more difficult than I desired.  The Table Joins started getting a little too complex and convoluted for what I had envisioned.  I decided it would be much simpler to also include the EndDate in the CTE, thus drastically improving readability and ease of design for the query.  That tricky part was now overcome to a degree, and I was able to associate some of the records.  However, I was getting stumped on the Previous PayPeriod records.  After trying a few things, I realized how easy the fix was to retrieve those records.  A simple change to use Isnull in the Second Left join resolved this issue.

So now, my Join code is something like this:

[codesyntax lang=”sql”]


Since the only tying factor between my data is a recorddate and the payperiod range, I needed to be able to compare the recorddate to the startdate and enddate range.  This works better than I had expected.  As was expected, I would incur some cost to create the “PayDay” table on the fly as in the CTE.  I also take a hit for the date comparisons, since I can only compare on a Range and not do an actual equality.  The query is executing across two databases (1 is SQL 2005 and the other is SQL 2ooo) and returns in about 200ms, without any index tuning.

If I tune indexes in one of the tables (93% of total cost to the query comes from this table), I expect to see some improvement.  Since the table only has a clustered Index, I started by creating an Index on NTLoginName, RecordDate, Product, LVCRequested and ProspectID.  I know, I didn’t divulge the entire query, so some of this is coming out of the blue.  However, those fields were in the SQL 2000 database and were required outputs for this query.  By adding a new NC Index, I was able to reduce the Clustered Index Scan to an Index Seek.  For this part of the query, it reduced overall cost from 93% to 33%.  Logical reads on the table reduced from about 5000 to 76 – another substantial savings.  Total execution time is down to about 140ms.

All in all, this is a good solution for the requirements at hand. Revisiting the self-defined requirements:

  1. The payperiods need to be accessible to the query
  2. No Physical Lookup Table
  3. Do not hard-code the data in the code.

The only requirement that may be questionable is #3.  I do need to pass a date into the proc to make this whole thing work.  That date must be a known good payperiod start date.  However, I have also set a default so that the date will populate to one that I know is good.  #2 is a achieved since I did not create a permanent physical lookup table.  The nice takeaway from this exercise has been the  improvement in the query once again.  Though the query is doing something a bit more difficult than previously, performance is better.  It was also a nice exercise in thinking outside the box.

Here is the full execution plan of the revised query.

TSQL Tuesday – But I was Late

Comments: 2 Comments
Published on: January 13, 2010

I was late to the game having discovered the Blog Post the day after entries were allowed.  Despite that, I will trackback to the Adam Machanics Blog Post.  I read the rules and fully understand that it will only count for me having done the exercise and my own personal hoorah.  That said, I had a stumper that came up recently that the TSQL Tuesday challenge made me think of.

The challenging script ended up being very easy to fix, but it took me a bit to find the issue.  The setup comes from a UDF written to print out timestamps.  When called directly from SSMS – it works as expected.  When called from a stored proc it works as expected.  When called from a SQL Agent Job it does not work as expected.

When you call this Function as follows:

You should receive the a printed statement formatted as “[current time] some status text”.  If you create a stored procedure and then call it from the proc you will get the same results.

Execute Proc:

And now to setup a job and continue testing.  As said earlier, this is where the problem is seen.

Now, the database listed in this job probably does not exist for you.  Replace that database with a valid database name.  Run the job and the job will complete successfully.  The results of executing the above job show the following output in the job history (step history):

” some status text”

This is missing the date and time that the function should provide.  I verified correct database names, function was firing, etc., etc., etc.  It had to be something in the function.  And then finally it dawned on me while staring at the code.

The Job was escaping out of the string due to the ‘[.’  By changing the ‘[‘ to a ‘(‘ and ‘]’ to ‘)’ the job ran and the expected output was returned.

Revised function is as follows:

I would have expected the same results between SQL Agent and SSMS.  However, the Agent was more strict in the execution of the SQL statements.  This little adventure was posted in the forums where a User was asking for assistance.  It stumped for a bit, so decided I would create a POST about it.

page 1 of 1

April 2019
« Mar    

Welcome , today is Saturday, April 20, 2019