Cate Enterprises

File this under the list of “I didn’t know that function existed”. So today I was looking to add the day of the week next to the date of previous and upcoming appointments to a report for an Oncology practice, and I thought I’d look around if there was a cleaner way of doing that and found that I could do this:

select LEFT(DATENAME(WEEKDAY,GETDATE()),3)

I’m confident some years ago I wrote something more along the lines of

select case datepart(weekday,getdate())
when 1 then 'Sun'
when 2 then 'Mon'
when 3 then 'Tue'
when 4 then 'Wed'
when 5 then 'Thu'
when 6 then 'Fri'
when 7 then 'Sat'
else 'Uhh ... we got problems?'
end

Granted I was probably working with SQL Server 2000 at the time, so who knows, DATENAME may not have existed at the time, so maybe it wasn’t as terrible as I think.

I enjoyed this article, How DBAs Can Adopt the Craftsman Mindset. This quote jumped out at me:

Recently I finished the book So Good They Can’t Ignore You, by Georgetown computer science professor Cal Newport. While the book is certainly not specifically targeted at database administrators, I found that many of the points were very applicable to the paths I’ve seen several highly successful ones take. For example, Newport makes the point that the “follow your passion” advice is largely flawed; rather, by honing our skills and getting good at what we do, we find that very passion.

I had a period where I was just doing what they needed me to do at work, and it was kind of miserable. I do find I enjoy my work more when I’m learning, trying to make a more pronounced effort to do constant learning.

So one of the things I have to do a lot in my current job is build web pages in SQL Server. SQL is not normally what you use to build web pages, but it is what is available to us. So recently on a blog I saw reference to the CONCAT string function in SQL, which has the added bonus of essentially skipping NULL values (treats it as an empty string). So many times I’ve run into the “WHERE DID MY WEB PAGE GO?!?” because when I did a building up my web page, varchar by varchar, something I didn’t expect to ever have a NULL value, did.

So my code can go from (imagine there is HTML in the ” below, it wants to hide code, I’ll have to figure it out at some point):

select @html = @html + ''+coalesce(val1,'') + '' + coalesce(val2,'') + '' 
from mytable ...

to

select @html = CONCAT(@html, '', val1, '', val2, '')
from mytable ...

and never get hit by the NULL again when I forgot to put in a coalesce.

Unfortunately, it looks to be only available in SQL Server 2012 and later, and for our main server at work we’re still using SQL 2008 R2. I may need to push for an upgrade …

So indulging some. My family growing up enjoyed following some Space news, starting with my dad, and now into his grand kids. It has become a family tradition to go to the Space Access conference in Phoenix http://space-access.org/. This year we’ve got my dad (Henry Cate Jr), my brother (Henry Cate III), two of his daughters Ellen and Madelyn, and a nephew Alex McCown and his fiancee Lucy, and then me.

It is interesting looking at some of this with the eye of comparing to software development.

Henry Vanderbilt:
Mention of Blue Origin https://www.youtube.com/watch?v=ShPhWbrwFmk publishing some of what they’ve been working on. Looks like they are in more of a position to compete against SpaceX.
Cost per pound is going down and we’re able to do more with fewer pounds than before.

Henry Spencer:
Safety – “Good safety that you can implement is better than outstanding safety that you can’t.” – Randall Clague
Safety has to be designed in, not added on
“If failure is not an option, success can get expensive.” – Peter Stibrany, MOST project manager
Don’t blame the operator – don’t punish mistakes, learn from them, have people report them
Don’t work alone
Checklists – EMERGENCY SHUTDOWN CHECKLIST – closing things down quickly orderly

(this all in the context of rocketry, but seems to really apply in software development as well)

Dr Peter Swan of International Space Elevator Consortium
He’s talking about there needing to be capacity for 60k metric tons of lift per year by 2040(?), in part 40k metric tons for space solar power, and 10k metric tons for disposal of nuclear waste. Interesting.
But all of this relies on there being a way to build it, in particular a material.

Will Pomeranz / Virgin Galactic
Reaction after the accident has been good, pressing forward. People knew there would be a “first accident”, but a shock, glad it hasn’t killed the market. Building a small launcher to be airlaunched, hope to fly end 2016.

Dennis Stone – NASA JSC Commercial Space Capabilities Office
Collaborations for Commercial Space Capabilities, working with commercial companies, including companies like SpaceX. Doing some free consultations to share knowledge NASA has gained, not hoarding it.

Ken Biba AEROPAC, 2 stage 100k feet recoverable CANSAT launcher
Open design, open source fly for about cost of propellant, $2000, are able to keep using same rocket.

Pam Underwood – FAA AST
Transition to more civilian (commercial) launches, in 2010 there were 4 launches, in 2014 there were 18.
Outer Space Treaty – each government responsible for the launches of its citizens

Roert Watzlavick
Building and testing a 250lb thrust rocket motor in back yard, ecavator dug a pit for the testing.
www.watzlavick.com/robert/rocket

I was reading

https://www.simple-talk.com/sql/t-sql-programming/checking-the-plan-cache-warnings-for-a-sql-server-database/?utm_source=ssc&utm_medium=publink&utm_content=checkingplancache

And since I didn’t want to run his function for the ~150 databases I deal with on the production server, I wrote a variation to check the plan cache for warnings on all:


-- =============================================
-- Author: Derek Cate
-- Create date: 01/23/2015
-- Description: return the query plans in cache for all databases
-- based on work of Dennes Torres https://www.simple-talk.com/sql/t-sql-programming/checking-the-plan-cache-warnings-for-a-sql-server-database/?utm_source=ssc&utm_medium=publink&utm_content=checkingplancache
-- =============================================
alter FUNCTION [dbo].[planCachefromAllDatabase]
(
)
RETURNS TABLE
AS
RETURN
(
with xmlnamespaces
(default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select DB_NAME(qt.dbid) database_name,
qp.query_plan,qt.text,
statement_start_offset, statement_end_offset,
creation_time, last_execution_time,
execution_count, total_worker_time,
last_worker_time, min_worker_time,
max_worker_time, total_physical_reads,
last_physical_reads, min_physical_reads,
max_physical_reads, total_logical_writes,
last_logical_writes, min_logical_writes,
max_logical_writes, total_logical_reads,
last_logical_reads, min_logical_reads,
max_logical_reads, total_elapsed_time,
last_elapsed_time, min_elapsed_time,
max_elapsed_time
from sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
)
GO
-- =============================================
-- Author: Derek Cate
-- Create date: 01/23/2015
-- Description: Return the warnings in the query plans in cachefor all databases
-- based on work of Dennes Torres https://www.simple-talk.com/sql/t-sql-programming/checking-the-plan-cache-warnings-for-a-sql-server-database/?utm_source=ssc&utm_medium=publink&utm_content=checkingplancache
-- =============================================
alter FUNCTION [dbo].[FindWarningsAllDatabases]
(
)
RETURNS TABLE
AS
RETURN
(
with xmlnamespaces
(default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
qry as
(select database_name,[text],
cast(nos.query('local-name(.)') as varchar) warning, total_Worker_time
from dbo.planCacheFromAllDatabase()
CROSS APPLY query_plan.nodes('//Warnings/*') (nos)
)
select database_name,[text],warning,count(*) qtd,max(total_worker_time) total_worker_time
from qry
group by database_name,[text],warning
)

*Let me start with a disclaimer, this is not the right solution for most people.*

So I ran into a situation where most times a OLTP database was using only 20MB of log space at a time.  I’ll have to dig up how I found that number and put it in another post.  However, the log file was like 1GB on 1GB of data.  In these days of Terabyte drives it would be tempting to say “who cares”, but it was on a hosted solution where they were trying to keep costs down, and there were over a hundred databases in a similar situation. It turned out it was when a database was updated as part of an application update. Essentially, you start with 100 databases for customers on application version 1 and slowly you upgrade them a couple a night over several months until you have all 100 on version 2 (using 2 application servers on different versions). I came up with something like the following script which I put in a SQL Agent job to run nightly.  So now when someone runs an upgrade and the log file for a database balloons, it gets shrunk down to 40MB that night.

DECLARE @logShrinkQuery AS NVARCHAR(2048)
DECLARE LOG_SHRINK_CURSOR CURSOR FOR 

	select 'use ' + d.name + ' ;
		dbcc shrinkfile('''+f.name+''',40);'
	from sys.master_files f 
	join sys.databases d on d.database_id = f.database_id
	where f.type_desc = 'LOG'
	and f.size*8.0/1024 > 40

OPEN LOG_SHRINK_CURSOR
FETCH NEXT FROM LOG_SHRINK_CURSOR INTO @logShrinkQuery
	WHILE (@@FETCH_STATUS <> -1)
	BEGIN
		EXEC SP_EXECUTESQL @logShrinkQuery
		FETCH NEXT FROM LOG_SHRINK_CURSOR INTO @logShrinkQuery
	END
CLOSE LOG_SHRINK_CURSOR
DEALLOCATE LOG_SHRINK_CURSOR

I went to the SQL Saturday in Richmond, had a fabulous time, my first time at a SQL Saturday.  PASS (Professional Association of SQL Server http://www.sqlpass.org/) and the local user SQL Server user groups help organize the SQL Saturday events, vendors help with the funding, the only expense to me was the gas money and $10 for lunch.

Some of the things I enjoyed

• 40+ presentations offered (see https://www.sqlsaturday.com/381/schedule.aspx)
• The presenters are there on their own dime, a lot of people enthusiastic about the technology and wanting you to succeed
• Meeting lots of people in similar situations as I, a camaraderie
• Networking – and this is coming from a natural introvert.  On this, I was one of about 5 who completed the mixer activity, a “Professional Scavenger Hunt”, but alas my 20% odds of winning didn’t get me the tablet.  *sigh*  😉

The whole thing made me wish there was cloning and I could have attended multiple classes at the same time.  I’ll be attending more and would encourage others to as well. There were several who traveled a long way to attend, not sure that I’ll do that but it seems like Raleigh, Charlotte, Baltimore, and Washington DC are doable.  Look at https://www.sqlsaturday.com/default.aspx for a full schedule.

One of the first tools that was a lifesaver in my role as an Accidental DBA was sp_whoisactive from Adam Machanic.  The situation was SQL server was unresponsive and we were trying to sort out what was happening.  The Activity Monitor in SQL Server Management Studio was timing out before it would return any data.  Fortunately, sp_whoisactive is quick and light enough that we were able to get the results we needed.  A bug in a vendor’s product had opened a transaction without bothering to commit.  As the locks built up, SQL Server got slower and slower, other databases on the server started to lock up … there were a lot of unhappy people.  It was easy to see the offending process, to kill it (not ideal, but it wasn’t committing anyway) and the server cleared up quickly.

Yes, I could have gotten the information from sp_who2, but sp_whoisactive presents more information and presents it more clearly.

Check out sp_whoisactive at http://sqlblog.com/files/folders/beta/entry42453.aspx

I heard the term “Accidental DBA” and knew there were kindred souls out there.  For me has worked out as, “You’re not afraid of the database? Congratulations, you’re the new expert.”  This always made me feel like something was wrong, after all I had graduated in Computer Science, I had learned software development, had I failed?  But now I find I really like databases, so I’m starting to embrace it more instead of hiding from it.  So the blog is in part to share things I’ve learned and in part to remember them so I can find it again later.