SQL RNNR

1 DBA's Professional Blog

Tags: script

ShowPlan XML

by Jason Brimhall

Categories: News, Professional, SSC

Tags: Execution Plan, script, SQL

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.

Code block

SELECT *
	INTO #testtemp
	FROM sys.databases

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.

Code block

SET showplan_xml ON
go
 
SELECT *
	INTO #testtemp
	FROM sys.databases
 
go
SET showplan_xml OFF
go

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.

Code block

SELECT @@VERSION
go
SET showplan_xml ON
go
 
SELECT *
	INTO #testtemp
	FROM sys.databases
 
go
SET showplan_xml OFF
go

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

by Jason Brimhall

Categories: News, Professional

Tags: script, Scripts

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.

Code block

;
WITH twoperiods AS (SELECT top 2 p1.PeriodID,p1.startdate,p1.enddate
	FROM PayPeriods p1
		INNER JOIN LCComm_PayPeriods p2
			ON p2.PeriodID = p1.PrevID
		WHERE (@CurrentDate BETWEEN p1.Startdate AND p1.Enddate)
	UNION ALL
		SELECT 	p2.PeriodID,p2.startdate,p2.enddate
			FROM PayPeriods p1
				INNER JOIN PayPeriods p2
					ON p2.PeriodID = p1.PrevID
			WHERE (@CurrentDate BETWEEN p1.Startdate AND p1.Enddate)
)

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:

Code block

LEFT OUTER JOIN twoperiods PS
			ON MIS.RecordDate BETWEEN PS.StartDate AND PS.EndDate
			AND @CurrentDate BETWEEN PS.StartDate AND PS.EndDate
		LEFT OUTER JOIN twoperiods Prev
			ON MIS.RecordDate BETWEEN Prev.StartDate AND Prev.EndDate
			AND  IsNull(PS.StartDate,0) = 0

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

Code block

, Case When IsNull(Prev.StartDate,0) = 0 Then 0
			Else 1
		End AS PreviousPayPeriod

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

Code block

OPTION (maxrecursion n)

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.

Conclusion

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

by Jason Brimhall

Categories: News, Professional

Tags: Professional, script, SQL

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.

The payperiods need to be accessible to the query
No Physical Lookup Table
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
)
[/code]

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:

Code block

INNER JOIN clc.dbo.employees e
			ON e.ntloginname = MIS.NTLoginName
			AND e.lc_flag = 1
			AND e.activeflag = 1
			AND e.AdminRights = 0
		LEFT OUTER JOIN PeriodStarts PS
			ON MIS.RecordDate BETWEEN PS.StartDate AND PS.EndDate
			AND @CurrentDate BETWEEN PS.StartDate AND PS.EndDate
		LEFT OUTER JOIN PeriodStarts Prev
			ON MIS.RecordDate BETWEEN Prev.StartDate AND Prev.EndDate
			AND IsNull(PS.StartDate,0) = 0

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:

The payperiods need to be accessible to the query
No Physical Lookup Table
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

by Jason Brimhall

Categories: News, Professional

Tags: challenge, script, TSQL Tuesday

Comments: 1 Comment

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.

Create Function

CREATE FUNCTION [dbo].[CurTime]()
RETURNS Char(22)
AS
BEGIN
DECLARE @DateString AS Char(23)
SELECT @DateString = '[' + CONVERT(Char(10),GETDATE(),101) + ' ' +
CONVERT(Char(8) ,GETDATE(),108) + '] '
RETURN (@DateString)
END

When you call this Function as follows:

SSMS Call

PRINT dbo.CurTime() + 'some status text'

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.

Create Proc

Create procedure sometest
 
as
Begin
declare @curtime varchar(32)
select @curtime = dbo.curtime()
PRINT @curtime + 'some status text'
 
Select * from Quote
PRINT dbo.CurTime() + 'some status text'
 
End

Execute Proc:

Exec Proc

Exec sometest

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

SQL Agent Job

USE [msdb]
GO
 
/****** Object:  Job [sometest]    Script Date: 01/13/2010 18:39:32 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 01/13/2010 18:39:32 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 
END
 
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'sometest',
		@enabled=1,
		@notify_level_eventlog=0,
		@notify_level_email=0,
		@notify_level_netsend=0,
		@notify_level_page=0,
		@delete_level=0,
		@description=N'No description available.',
		@category_name=N'[Uncategorized (Local)]',
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [tst]    Script Date: 01/13/2010 18:39:33 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'tst',
		@step_id=1,
		@cmdexec_success_code=0,
		@on_success_action=1,
		@on_success_step_id=0,
		@on_fail_action=2,
		@on_fail_step_id=0,
		@retry_attempts=0,
		@retry_interval=0,
		@os_run_priority=0, @subsystem=N'TSQL',
		@command=N'Exec dbo.sometest',
		@database_name=N'TestA',
		@output_file_name=N'C:\sometest.txt',
		@flags=12
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
 
GO

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.

Tada

SELECT @DateString = '[' + CONVERT(Char(10),GETDATE(),101) + ' ' +
CONVERT(Char(8) ,GETDATE(),108) + '] '

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:

Revised Function

Create FUNCTION [dbo].[CurTime]()
RETURNS Char(22)
AS
BEGIN
DECLARE @DateString As Char(23)
SELECT @DateString = '(' + CONVERT(Char(10),GETDATE(),101) + ' ' +
CONVERT(Char(8) ,GETDATE(),108) + ') '
RETURN (@DateString)
END

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

May 2012
M	T	W	T	F	S	S
« Apr
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30	31

Content

Now Reading

Planned books:

Throne of Fire, the by Rick Riordan
Seeds of Rebellion (Beyonders) by Brandon Mull

Current books:

ChiRunning: A Revolutionary Approach to Effortless, Injury-Free Running by Danny Dreyer, Katherine Dreyer
Advanced Marathoning – 2nd Edition by Peter Pfitzinger, Scott Douglas
SQL Server MVP Deep Dives by Nielsen Paul, Delaney Kalen, Machanic Adam, Tripp Kimberly, Randal Paul, Low Greg
A World Without Heroes (Beyonders) by Brandon Mull