28Jul 2009 by Grant Fritchey 7 Comments

When did this statement start?

SQL Server, T-SQL
UPDATE: This post is incorrect. Adam nailed it in the comments. I explain my mistake here. A question came up over at SQL Server Central where someone was wondering if it was possible to know when a given statement within a batch started. Immediately I thought, oh yeah, that's easy, use the sys.dm_exec_requests dynamic management view (DMV). Done. Wrong. The original poster pointed out that I had assumed that the values present in the DMV represented statement level values, but they show the batch. While the DMV shows a start_time, that time is the start of the current batch, not the statement within the batch. Now the question was, where else might I get this data? I next tried sys.dm_exec_sessions because it has the last_request_start_time value. Sure enough this worked. Don't…
Read More
24Jul 2009 by Grant Fritchey No Comments

Attache/Brief Case Review

Misc
Steve Jones, through a series of comments, emails, blackmail, back-alley deals & tons of whining, agreed to let me review a brief case, the Maxpedition Hard Use Gear Aggressor Attache. The review is up over at SQL Server Central and a video I made of the bag should be up shortly. I love Maxpedition products, even if the names (Agressor, come on) can be a bit off-putting. The products are very well designed and extremely well built. They're worth your time & money.
Read More
21Jul 2009 by Grant Fritchey No Comments

Conversations

PASS
Do you know how to start a conversation or how to join one? I usually wait for a pause and then blurt out whatever point I thought was missed, missing, or insufficiently covered. In other words, don't come to me for advice. The fact of the matter is, while the legend of misanthropic, barely washed, hardly lucid, but frighteningly competent IT personnel is alive and strong, in reality, we need to speak to each other, our peers, and worse yet, the business people. Developing social skills is a must. Further, with the economy being what it is (and looks to be for some time to come) you probably need to put those social skills to work building up a network, people you can help and, hopefully never needed, can help…
Read More
20Jul 2009 by Grant Fritchey No Comments

MS Field Engineer’s on Performance Troubleshooting

SQL Server, T-SQL
Do you want to get a glimpse into how the Microsoft Field Engineers would go about troubleshooting performance issues on your server? Then go and read this blog entry by Denzil Ribeiro. Not only is this an excellent how-to on troubleshooting performance problems, but Mr. Ribeiro provides multiple links that describe the concepts he's dealing with further, making it a great piece of documentation. The MS Field Engineer blog is not terribly active, but what gets posted there is worth reading. If you don't have it on your feed list, you should.
Read More
17Jul 2009 by Grant Fritchey 15 Comments

Execution Plan Compile Termination

SQL Server, T-SQL
Recently I've been seeing a lot of people with bad execution plans, desperately trying to tune them, but they were unable to explain why they had such bad plans. More often than no these were larger queries, with a number of derived tables, CTE's, CROSS APPLY, etc. In most cases the statistics appeared to be fine (this is usually checked by comparing estimated & actual rows within the operations in the execution plan) and the plans themselves didn't look crazy, but the execution plans were flat out, not good. If you're looking at a plan and it doesn't make much sense, one option that most people don't check... SQL Server didn't have enough time to complete optimization. The optimizer is a pretty amazing bit of code. The scary volume of…
Read More
14Jul 2009 by Grant Fritchey 2 Comments

More on BALD-D

Misc
The Battle Against Lawless Database Design continues. And thus were the Berserkers born. Or, for the more film-oriented, Skynet becomes self-aware.   Hold the Line. It's been one of those days. A bit of humor and a nice dose of SF are good for the soul.
Read More
26Jun 2009 by Grant Fritchey No Comments

Re-evaluating Execution Plans (again)

SQL Server, T-SQL
I keep thinking I've got a handle on the way execution plans are dealt with in SQL Server. I had a pretty simplistic view of things, there's the estimated plan which comes out of the optimizer and there's the actual plan which comes out of the data engine. The one shows the best guess for what will happen based on the query and the underlying statistics. The other shows what actually happened based on the query and the real data, load on the server, available memory, you get the idea. This model is easy to explain and understand. Too bad it's not quite the way things work. If you query the dynamic management function sys.dm_exec_query_plan, you can see a query plan. Once you drill down on the XML, or browse…
Read More