SQL RNNR

1 DBA's Professional Blog

Archives: January 2010

Presentations – Lessons Learned

by Jason Brimhall

Categories: News, Professional

Tags: Lessons Learned, PASS, Presentations, User Group

Comments: No Comments

Published on: January 14, 2010

This will be a very short entry. I just wanted to share a few things I learned very quickly while prepping for my most recent presentation for SSSOLV (Las Vegas PASS).

First, while doing the slides and testing the SQL, it dawned on me that I could probably fix one of the flaws in my first presentation very easily. I did some checking and voila – it was indeed a very easy fix. The problem that needed fixing was readability of SQL code on screen during presentations. I tried using the magnifier, but that was horrific. So for that presentation, I dealt with it. It was very aggravating.

So, how did I fix it for this time around? I changed the font size. From SSMS, click tools and then options. Under the environment option, select Fonts and Colors. There is a Size dropdown menu on the right. I changed that setting from the default of 10 to 20. The code was plenty big to read now.

The same can also be done for the results. Just change to “Grid Results” in the “Show Settings For” dropdown. The default is 8, I just double this to 16.

Changing the “Grid Result” setting does require a Restart of the “Grid Result” which turned out to be a Management Studio restart for me.

Second, was something that ticked me off while prepping the slide deck. I didn’t have an issue with this during the last presentation, and it was a monstrous issue this time around. Sometimes it is nice to add custom animations to the slides to help hide and reveal items as you progress through the discussion. The animations on images were not working. The images were just laying on top of each other, doing nothing and looked like crud. This too, is a simple fix – though tedious. To get an image to perform the custom animation, it must be an attached image. A pasted image will not work. Once the image is saved as a “jpg” for example and then attached to the slide – the animation will work as expected and the slide will look more professional.

Third, to help the presentation flow, is to make sure all SQL connections are open and running. I thought I was ahead of the game by having all of my scripts open in SSMS and ready to go in the order that I need them. Well, my system went to standby while waiting for my turn to present. When the system went to standby, all of my connections closed. I didn’t think of this prior to starting my presentation. This too is easy to fix – it just requires a mental note. Prior to starting the presentation, and even prior to plugging in the connector for the projector – verify the connection state of any open scripts in SSMS.

Fourth, to save any embarrassment, run through your slides one more time close to presentation time. Make sure they make sense. Make sure any thoughts you want to bring up are noted on the slides or can be keyed from something on the slides. And verify the look, flow, feel, and function of the slides. The slide deck could be a useful tool along with any scripts to distribute to the group. You will want to know that they are flawless. Consider this as your final opportunity to edit and revise.

Lastly, build in some question and answer segments. Invoke the group to participate. Get them to help carry the presentation. This will not only help save your voice, but it will also help the group to stay in tune with the presentation. This is less of a lesson learned, and more of a reminder. I try to do this in any setting where I may be presenting (school, church, job). This can easily be done with a slide insert labeled “Discuss” or “Why” or “Questions.” Anything that will remind you and notify the group that interaction is forthcoming.

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.

Primary Key Discovery II

by Jason Brimhall

Categories: News, Professional

Tags: clustered index, inventory, Primary Key, Scripts

Comments: 2 Comments

Published on: January 12, 2010

In Part I, I discussed a couple of scripts that could be used to help discover some vital information about all of the PKs in the database very quickly. I also delved into a performance analysis of the two queries.

The scripts presented showed a very basic discovery of Primary Key information. I decided that I could use more information at my fingertips than the data set provided in that article. So, in this article, I want to demonstrate a few more scripts to be able to achieve my goals. These scripts will cover some clustered index information.

In SQL Server, when you create a Primary Key, the default behavior is to create a Clustered Index on the Primary Key. Though this is the default, it can be overridden. Thus, one should know what tables have Clustered Indexes created on them. It is also desirable to know upon which columns Clustered Indexes have been created.

To get to the Clustered Index Information, the Query is not much different than the one I used to retrieve the Primary Key information. One could make the assertion that this is due to the default behavior of creating a Primary Key and Clustered Index in one fell swoop. They are certainly closely knit together.

See the difference?

And…

Simple little change to get slightly more detailed information and something a little more intuitive about the database. What does this tell me? Well, I can now map these results to my PK results and see what PKs are also Clustered Indexes. This is simply achieved by uncommenting the is_primary_key line in the script. This small change will change the results to only show the Primary Keys and their Clustered Indexes. Even better, the script could be altered as follows to just pull back the Clustered Indexes that are not on Primary Keys. Simple modification, uncomment the is_primary_key line and change it from an equality to an inequality evaluation.

So, now we know more information about the Primary Keys and Clustered Indexes. That is, we know that information for any table that has a Clustered Index. It isn’t unheard of to have tables present in the database that do not have a Clustered Index at all. Knowing that information is essential as well. Again, this is all information that can be garnered by tedious investigation into each table. Or, we could quickly run a little script and get a quick result gaining quicker insight into the data we manage.

If any results are returned, then it would also be useful to know record counts for each of the tables in the result set. Knowing this might give some information as to usage of the table and impact of queries against the table that is a Heap. I was able to achieve this with the following:

Armed with this information, it is a little easier to go into a new environment and speak to the databases and data. The learning curve is shortened and one will be able to spend more time doing other tasks pertinent to maintaining the database environment and learning the database environment.

Download scripts: PKDiscover2_scripts

Professional Communications

by Jason Brimhall

Categories: News, Professional

Tags: communications, courtesy, vendors

Comments: 1 Comment

Published on: January 10, 2010

This past week I had the opportunity to have lunch with a vendor at his clients’ site. As a part of the lunch there is the usual dog and pony show. Vendors are trying to get you to buy their product after-all.

After this rendez-vous, I have had the opportunity to reflect on the lunch and the product demonstration as well as the entire meeting at the client site. Part of this reflection was to determine the roles that each party plays in such a meeting (client, potential client and vendor). Also part of this reflection was the discussions I have held with co-workers both present and not at this meeting.

It is not uncommon to speak of local area events or other things not business related to help bridge the gaps when the discussion lulls a little or you are at a wait point. In my area there were two major conventions coming up and thus for some circles that would make sense. One of the conventions was CES and the other was the Adult Entertainment one. In a business meeting that revolves around technology, I would be stunned if CES did not come up in the conversation at some point. During our meeting it did – only as a flighting thought though. That, in retrospect is somewhat astonishing.

Without the decision makers present in the meeting, the demo took a back seat and I was trying to find a way out there tactfully and professionally. I probably should have been less tactful and more forceful – but I was the guest. I found myself being somewhat ignored, surrounded in sailor language and hearing my hosts go on and on about the Adult Entertainment convention just down the street. Talk about a business meeting gone awkward and weird.

Thus I came to the realization of some things that really should be happening during business meetings.

Know your audience. In a business meeting, you are there for more mature conversation that should be centrally focused on the agenda at hand. If you get off subject, it should be a safe subject. An appropriate vernacular should be used. It is a professional meeting and dropping constant “F” bombs does not impress anybody. It may take a little restraint for some, but think if the language choice would be offensive to the opposite sex, bystanders or would you say it around a child?
It is an Interview. You may be at somebody else’s site and not even thinking of changing jobs. That is ok, this does not mean a job interview. However, treat it as such. You will gain valuable insight into your vendor as well as the local businesses. Should you need to change jobs in the future, you will have this information at hand to help you decide to pursue them or not. Also, you will take back information on how best to portray your company if ever in a similar opportunity.
Be Professional. You should speak and act no differently when management is present than when they are not. You are a representative of your employer. Your behavior reflects not only on you but on your employer as well. This combines quite nicely with #2 above. When attending these kinds of meetings, you are also interviewing the professional attitude of the staff. What if they find themselves displaced, the stars align and you are hiring. You now have excellent insight into their behaviors – did they speak or act in such a manner that would prevent you from hiring them?
Check your Title at the door. This is one that I picked up from a college course. Leave predispositions and arrogance behind. You may have a certain title somewhere, but in a meeting everybody should be treated equally. Opinions and input from all should be welcome without ostracization.

Language and behavior that I experienced as an outsider to this company only begs the question – Where is the filter? Would they behave the same around other staff and staff of a different gender. What about the corporate harassment policies? Let’s extend that to business ethics and morality – how does it mesh with the corporate image? Like it or not, people will judge you and your corporation by your actions and words.

When I recounted the behaviors I encountered to my co-workers, they were stunned. When I recounted it to my wife, she was abhored. I was a little too lenient when they started off slowly. I figured, I was a visitor and a slip of the tongue here or there might be acceptable in their business. Don’t hesitate to request a change in vernacular or behavior if you find it insulting.

The Last Olympian

by Jason Brimhall

Categories: Book Reviews

Tags: Books

Comments: No Comments

Published on: January 8, 2010

The Last Olympian by Rick Riordan.

Pages: 381

This is the fifth book in the Percy Jackson & the Olympians series. The book is a mythological adventure written for the young adult and young at heart adult. I don’t want to give away much of the story so will be somewhat generic.

This is not a difficult read and is quite entertaining. The plot twisted here and turned there and kept me pinned to turning its pages. I view this series as a whole to be on par with the Harry Potter series – and easily much better than the twilight series (no competition really). Some of the plot turns were easy to spot, and some were a surprise.

What I did not like about the book had nothing to do with the story. The editing seemed clumsy. Frequent misuse of words and conjugation. An example would be “spelled” in lieu of “spelling.” There were other grammatical flaws as well that were outside the context of dialog. Grammar errors inside dialog are acceptable – gives a reality check to the character.

Pseudo spoiler: The Oracle of Delphi pronounces a new prophecy involving seven demi-gods near the end of the novel.

Grade: B+ This was a good read and kept me interested for all five books. I may read them again and will likely watch the movies. I would also let me children read the book.

Goals For 2010

by Jason Brimhall

Categories: News, Professional

Tags: Goals

Comments: 2 Comments

Published on: January 7, 2010

Like a lemming in the DBA community, I too am going to post my 2010 goals. I am looking to create goals that are measurable.

1. Start a BLOG (that was a goal at the start of the year, and I jumped right on it).

I wanted to realistically try to do something to Build my own brand, and help give back a little bit to the community. It is a new challenge worthy of doing. It’s like a journal published for the entire world to see. Doing this will also help me figure out how to accomplish Goal 6.

2. Implement a BI solution.

This is one that I have started and have shelved as things have become more busy. I really want a full blown solution that involves SSRS, SSIS and SSAS. I have done SSIS. I have started learning a little SSRS and have supported it a little. SSAS will be something entirely new to me. However, it will be useful as a part of projects to reduce cost and better utilize resources that are currently allocated to Cognos.

3. 1 Technical Blog Post / week.

This may seem extremely light as far as a blogging goal goes. I intend on having more blog posts than that. However, the total will be from multiple categories and not just from a technical aspect. By allowing myself 1 post per week, I give myself time to research and organize the post into something that will be of value down the road to me, and to some of those that may read this blog.

4. Attend PASS 2010 Summit

I have attended PASS in the past, and really enjoyed it. I felt like I really missed out on something this last year. I would prefer not to miss out on another one. There are plenty of people in the community I would like to meet. I would also like to attend the seminars and learn something.

5. Present once a quarter (or 4 times this year).

These presentations will most likely all be at my local PASS chapter. I will be presenting this month, after having presented for the first time last month. Giving a presentation is a good experience, and I learn quite a bit from doing that. The difficult thing in presenting is coming up with something of value.

6. Write 2 Articles

This will be the most difficult Goal as I see them at this point. Writing a good article takes a lot of creativity. Not only does it take creativity, a time commitment is required. Then there is the review process – that is a little daunting. As stated above, I think the BLOG will help with all of that. Personally, presenting is easier than writing an article.

7. Run 2 Marathons

Being a marathoner used to running 4 or 5 a year, this is a low-end goal – if not for my recent injuries and surgery. I will get back into Marathon shape and shed the weight gained from not running as much. This is really the base goal for a much grander goal of qualifying for Boston some day.

I have other goals which I feel are not well suited to measuring in the same manner. I would like to maintain community activity. And then there are the other relationship type goals that just can’t be measured. For all of those goals, we will keep them out of this list.

SSC Syndication

by Jason Brimhall

Categories: News, Professional

Tags: Blogging, syndication

Comments: No Comments

Published on: January 7, 2010

Steve Jones at SqlServerCentral.com has created a Blog for me over there at SqlServerCentral.com. I will be syndicated there and hopefully increase the Blog exposure a little bit. This being a new adventure (the whole Blog idea), this is a good move for me at this time. Through Blogging, I hope to push myself a little more and learn more about SQL server.

Pages and Posts

by Jason Brimhall

Categories: Blogging

Tags: Blogging, page, post

Comments: No Comments

Published on: January 6, 2010

For the past couple of days I have been stumped on a very trivial thing with my blog site. I really want to be able to be able to add posts to different pages in my menu bar. I know I could add those posts to categories. I can even use categories in my menu bar. However, that option does not cut the mustard for me.

To achieve this, I tried several plugins. None of them worked as advertised. That was frustrating and annoying and the plugins will be removed. I continued my google search and finally came up with something that seems to work. I went so far as to try several themes to see if any of them could accomplish this very feat. All I accomplished with that was a short test drive of several themes. On the positive though, I did find some that I like very much and may use them at some future time. This fix is more of a band-aid/ workaround – but at least it works. Many thanks to the other wordpress blog that discovered this little trick.

Here is the original blog on how to get this working.

http://agileom.com/index.php/blog/word-press-posting-on-different-pages-finally-i-did-it/

Again, many thanks. Now I have in essence a combination of categories and pages on my menu bar – but they appear as pages.

I am a Runner

by Jason Brimhall

Categories: Running

Tags: Running

Comments: No Comments

Published on: January 6, 2010

I am a runner that has been riddled with injury for the past couple of years. The injuries are knee related. I had surgery on my left knee middle of 2009 and am still trying to rehab and get back the sensation and lose the pain.

Primary Key Discovery

by Jason Brimhall

Categories: News, Professional, Scripts

Tags: Primary Key, Scripts

Comments: 4 Comments

Published on: January 5, 2010

As a part of my DB documentation process for a large database, I needed a method to quickly show me all of the Primary Keys in the database. Not only did I want the PKs, but I wanted to know the DataType for each of the Primary Keys.

I began by fiddling with some queries first and then by searching on Google for something that might be fitting. I found a couple of queries (one which was very similar to what I already had). The queries used different views and thus sparked a question for me – which method was better?

So let’s start with the base query.

From this query, I can get the Column Name, Constraint Name (PK) and the Table to which it belongs.

Execution Plan for that Query:

With the Following IO Stats:

And here is the alternative base query:

The Execution Plan is much larger, and thus I have added the XML Plan for download via - Alt BaseQuery ExecutionPlan.

And the IO stats:

From both queries, we can see that they are returning the same data, same number of rows, and for this set particular run – both run in under 1 second (109 ms for Query 1 and 156 ms for Query 2). For this base query both can be deemed acceptable. However, I want to emphasize that Query 2 took nearly 50% longer to process in this test.

Now that we have a baseline, I need to be able to retrieve the data type for each of the columns in each primary key. This was a part of the initial self-prescribed requirements. Adding in the additional components for each query is a little different. Once added in, I will be doing the final comparison in a single batch to illustrate a little more poignantly the differences in processing between these queries.

Query 1 needs the following additions (lines 3, and 9-13)

Query 2 needs the following changes (Lines 18, and 22-26)

When I run these in tandem, the query plan for Query 1 changes somewhat, and the Plan for Query 2 a little more. Comparing the two, I see that Query 2 has a cost of 72% for the batch, and Query 1 has the remaining 28% for the batch.

Here is the XML Plan for both for download: Comparison_Plans.

Now the IO Stats:

Notice the one Table that is substantially and surprisingly different between the two sets. In Query 1, sysiscols has 2119 scans and 4243 logical reads. Both are substantially higher than Query 2 at 229 Scans and 459 logical reads. Despite this information, we can find that Query 1 executes (during these tests) better than Query2. The result of the testing could change depending on information used in testing. If I were to just consider the IO stats, I may well opt for Query 2. However, when I consider all factors (used in these tests), my results tell me to use Query1.

Download Scripts For this Article here.

«page 2 of 3»

January 2010
M	T	W	T	F	S	S
« Dec		Feb »
	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

Recent books:

The Red Pyramid (The Kane Chronicles, Book 1) by Rick Riordan
Fablehaven, Book 5:Keys to the Demon Prison by Brandon Mull
Catching Fire (The Second Book of the Hunger Games) by Suzanne Collins
Pirate Latitudes by Michael Crichton
The Battle of the Labyrinth (Percy Jackson and the Olympians, Book 4) by Rick Riordan

View full Library

SQL RNNR

Presentations – Lessons Learned

TSQL Tuesday – But I was Late

Primary Key Discovery II

Professional Communications

The Last Olympian

Goals For 2010

1. Start a BLOG (that was a goal at the start of the year, and I jumped right on it).

2. Implement a BI solution.

3. 1 Technical Blog Post / week.

4. Attend PASS 2010 Summit

5. Present once a quarter (or 4 times this year).

6. Write 2 Articles

7. Run 2 Marathons

SSC Syndication

Pages and Posts

I am a Runner

Primary Key Discovery

Categories

Popular Posts

Blogroll

Now Reading

Planned books:

Current books:

Recent books:

SQLHelp