Control Z
This post is inspired by the #famouslastwords thread on twitter today. If you are doing a major, minor or any change on a production system, have a rollback plan. This will probably go beyond restoring from backup especially if the data is large. From someone who has put in 24 hour+ shifts, plan and test as much as possible.
I am not NULL!
Adapted from OfficeOFFline.
You might be a geek if…
The stole this from the #youmightbeageek conversation going around twitter yesterday. Funny stuff. The original post is here.
The T’aint
If you don’t get it, bless your heart. If you do, don’t cry foul because you do not learn this phrase in Sunday School. Besides, it is ALWAYS the network!
Ninja Fog
The cloud has been all the buzz this week. Paul makes a prediction, Denis talks about the challenges, Steve chimes in and the register reports that Microsoft plans to release a full featured SQL Server to cloud. I have talked about this before but I got nothing but ninja fog today.
This comic was adapted from OfficeOFFline.
6 degrees of Virtualization
You might think this is funny(or not) but this will be happening sooner or later.
The Senior DBA
I am astounded by how often I see a problem that I know nothing about but fix it after finding the answer in a search. I get more high fives for being a good search engine user than a good DBA. On the flipside, I learned this by asking dumb questions and getting sent back a google search link.
The comic was adapted from OfficeOffline.
The Interview Question
Yes, this really happened. He didn’t say “Duh” but he was so confident that he might as well have.
I got a nice little surprise in my inbox today: The first reader submitted Captain Varchar comic from Rod Colledge of SQLCrunch.
I have written about this twice. I pushed it here and I toned it down after the facts came in from PDC. TJay Belt also had some commentary on it here today.
The comic does raise another off topic but interesting point. Apple’s desktop market share has been gaining ground. What happens when SaaS makes the browser the OS? Yes, another stretch but that is where some “experts” think we are heading.
Guess who the victim of this deadlock is?
On the technical tip, the "end all be all" of deadlock troubleshooting can be found here.
This post was inspired by the SQL Quiz going around that that Chris started, while humorous, can help us learn from each others mistakes.
Over the years, I have gone from a mental project plan to notepad to excel to MS project. My success % has increased and fire fights have decreased for major changes. I recommend it.
A Mental(ly Disturbed) Note
The comic template was adapted from OfficeOFFline.
Once again, I was struggling for a Varchar(MAX) topic and twitter came through. This comic is based on these two tweets.
Update: Anarchy has erupted on twitter. Brent blogged it here.
BrentO If yo momma was a table, she'd be a heap. #SQLputdownsFri, Oct 31 12:31:24 from mobile web
jeffrush @BrentO If she was a datatype, she'd be a BLOBFri, Oct 31 12:37:41 from OutTwit
Yo Mamma is a SQL Server
This template is based on Office OFFline.
This post was inspired by a twitter conversation between @SQLCraftman and @Joewebb.
joewebb @SQLCraftsman TVF's don't kill servers, developers with TVFs kill servers. :) Fri, Oct 17 13:42:42 from twhirl
SQLCraftsman Still recoding bad T-SQL. Table-Valued Functions are evil.Fri, Oct 17 13:16:43 from web
For further information, I suggest reading my favorite whitepaper especially the best practices section.
I am out of town. Look for it Monday or before. Until then I give you:
But hey if it works, then Darwinism is kinky… Maybe this is how cowboy hats evolved.
Was struggling for some comic fodder today. Like for at least 7 minutes. Then I remembered a blog post that BrentO wrote yesterday and blam. Humor(or lack of) aside, test your fracking backups.
This template was adapted from Office OFFline.
SQLOS uses a cooperative scheduler. They actually wrote it from the ground up because it performs better than the Windows preemptive scheduler. KenH(R.I.P.) describes it way better than I can here. It was written for SQL 2000 but is still pretty applicable.
Less Signal Wait. More CPU lovin’
Maybe I will come up with something funny next week. Happy Friday!
This comic was adapted from Office OFFline.
How many times have you heard “Cursors are evil”? Well, +1.
A cursor vs. set based solution
Happy Friday.
I have be running into this problem more often. The exact error message is "A significant part of sql server process memory has been paged out. This may result in performance degradation". It is usually easy to work around if you are on Enterprise Edition but on standard edition, you have to actually fix the problem. :) I have actually had to to do a couple of edition upgrades because the customer could not fix the root cause.
Random Access Memories
Template courtesy of Office OFFline.
This is my humble follow up to the, now classic, xkcd strip on little Bobby Tables.
This post was inspired by this MSDN forum question and with nudge from BrentO.
WordPad FTW!
To go a long with my previous post, I give you a Friday funny.
Intelligent Design?
Time for TRY\CATCH in politics?
Phantom Records
I posted the first Captain VarChar(MAX) comic last Friday. It takes a swing at profiler. A lot of you are like “Duh!”. I have just done too many dba interviews where the candidate’s default answer is profiler. The interview usually gets tough or really short at that point. Just say no to SQL profiler.
I really have to stretch my imagination to come up with a situation where using SQL Profiler sounds like a good idea. For example, you quickly want to capture “ordered” events when repro’ing a problem in your *test* environment AND you filter it by your SPID. Any other time, you are wasting lots of time, cause performances overhead and probably missing what you are looking for while digging for the needle in the haystack. Also contrary to popular belief, running the GUI on another machine DOES NOT help much. For the rest of the post when I refer to a trace, I am referring to one captured with a server side trace unless I say “profiler” or “GUI”
I will admit that I have brought a production app to its knees with profiler. Picture this. You get a call while you are running profiler that the app is having issues. At first you think, “Hey, I have a trace running so at least I will be able to pinpoint the problem.” Then you see TRACE_WRITE wait types. Your stomach knots up because you realize that you just caused a production outage. Don’t be that guy(or gal).
SQL 2005+ just gives you too much info through the DMV’s. 90%+ of the time you can use the DMV’s instead of Profiler. The other 10% of the time, use a server side trace not the Profiler GUI. I have never seen a server side trace bring down an app. Even unfiltered traces on highly transactional systems where a gigabyte+ is collected every minute. I can see it causing an issue but the server would have to be pretty max’ed out to begin with.
Lastly, a trace only reports CPU, Reads, Duration etc when the query completes. Query completion for a misbehaving query could take 10x to 100x longer than normal. This makes traces unusable in a lot of cases when troubleshooting.
Let’s look at some scenario’s:
Troubleshooting a performance issue that is currently happening
A SQL trace in this situation is almost never needed on SQL 2005+. I troubleshoot like this:
1) See if\what hardware subsystem is pegged out with about 5 or 6 perfmon counters.
a) Verify with DMV data or wait stats if needed.
2) Identify the problematic queries with the sys.dm_exec_% DMV's
3) Fix.
Here is the query I use. In a nutshell, it grabs a snapshot, waits a second and does a diff so I can see what is currently slamming the server. Otherwise, the cumulative CPU and IO columns might throw me off. I also grab the query plan which is easy here but very expensive in a trace and hard to correlate with the SQL completed event.
select r.cpu_time
, r.logical_reads
, r.session_id
into #temp
from sys.dm_exec_sessions as s
inner join sys.dm_exec_requests as r
on s.session_id =r.session_id --and s.last_request_start_time=r.start_time
where is_user_process = 1
waitfor delay '00:00:01'
select
substring(h.text, (r.statement_start_offset/2)+1 , ((case r.statement_end_offset when -1 then datalength(h.text)
substring(h.text, (r.statement_start_offset/2)+1 ,
((case r.statement_end_offset when -1 then datalength(h.text)
else r.statement_end_offset end - r.statement_start_offset)/2) + 1) as text
, r.cpu_time-t.cpu_time as CPUDiff
, r.logical_reads-t.logical_reads as ReadDiff
, p.query_plan
, r.wait_type
, r.wait_time
, r.last_wait_type
, r.wait_resource
, r.command
, r.database_id
, r.blocking_session_id
, r.granted_query_memory
, r.reads
, r.writes
, r.row_count
, s.[host_name]
, s.program_name
, s.login_name
on s.session_id =r.session_id and s.last_request_start_time=r.start_time
full outer join #temp as t on t.session_id=s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) h
cross apply sys.dm_exec_query_plan(r.plan_handle) p
order by 3 desc
drop table #temp
Proactive performance tuning
Now this starts to get in to fuzzy territory. Technically, a SQL trace is more thorough than doing your analysis off of the procedure cache. However, I would say benefits outweigh the losses in most but not all cases.
Procedure Cache Pro’s
· No need to capture a trace· No need to load a trace· Aggregation already exists so the data can be smaller assuming that the application has good plan reuse.
· No need to capture a trace
· No need to load a trace
· Aggregation already exists so the data can be smaller assuming that the application has good plan reuse.
· Easy access to XML Showplan
Procedure Cache Con’s
· Queries can be flushed until next execution for many reasons· Some queries are not cached. These are usually queries that you don’t care about.
· Queries can be flushed until next execution for many reasons
· Some queries are not cached. These are usually queries that you don’t care about.
To replace SQL trace analysis with a DMV query, use something like this:
select total_worker_time/execution_count as AvgCPU , total_elapsed_time/execution_count as AvgDuration , (total_logical_reads+total_physical_reads)/execution_count as AvgReads , execution_count , substring(st.text, (qs.statement_start_offset/2)+1 ,
((case qs.statement_end_offset when -1 then datalength(st.text)
else qs.statement_end_offset end - qs.statement_start_offset)/2) + 1) as txt , query_planfrom sys.dm_exec_query_stats as qscross apply sys.dm_exec_sql_text(qs.sql_handle) as stcross apply sys.dm_exec_query_plan (qs.plan_handle) as qp --where – filter as needed. --group by – Consider striping literals and grouping by the SQL text.
--See Inside SQL Server 2005 for more info. --Order by – Sort as needed.
Uncommon cases
There are some less common uses of SQL traces like the blocked process report, deadlock report, recompile tracing and many others. Make sure you review all of your options and don’t forget about the WMI Provider for Server Events. Use your best judgment and avoid the GUI like the plague.
Conclusion
To summarize very briefly: Never use the SQL Profiler GUI, know your tools, and pick the best one for the job.
I have been wanting to do a SQL comic for quite some time. The problem is I really suck at art. My 4 year old laughs at my stick figures. However, @DavidSalaguinto does a comic on MSDN and has shared the Visio template. Thanks David!
I have a post half written on the perils of SQL Profiler so I thought I would drop a prelude to it. If you are asking yourself what is wrong with profiler, keep coming back.
The Technical Interview