Friday, March 04, 2011

New monitoring tool

I've finally upgraded from Red-Gate SQL Response 1.3 (which was barely adequate) to Red-Gate SQL Monitor 2.2. It is a HUGE step up, forward, what not. This thing replaces several queries I have to monitor the health of my servers. Things like buffer cache usage, page splits, disk utilization.

I know the basics behind it are, well, basic. Perfmon stuff and things that can be found in DMVs and DMOs, however the interface is just wonderful.
I'm a huge fan of Red-Gate. My sys admins aren't... but thats because their favorite exchange archive tool was discontinued.

But for the DBA and serious Database Developer on the SQL Server platforms the tools are top notch.


Thursday, March 03, 2011

Same Stored Proc, Different plans

So my last post (yesterday) extolled the virtues of going back to basics.

What triggered the thoughts was that I had the same SP running on my SQL 2008 server, with the exact same parmeters... and yet vastly different plans.

In this event, it is most likely a connection setting difference between the two systems running the stored proc. In my case SSMS and SSRS.

I suggest rather than recompiling every time, or fiddling around with the connection settings (both paths may not work btw), that you take the worst case execution plan, and tune it.

Look at what it is doing and get rid of the 'bad stuff'. Yeah, I said the same thing yesterday, but I wasted a day or two trying to be slick and force the 'good' plan to be used.

Labels: , ,

Wednesday, March 02, 2011

SQL Server -- Query Plans -- Caching -- Performance issues

Ahhh yes, the life of a DBA. I was presented with an interesting issue this past week, and a very interesting solution that wasn't the answer, but allowed me to escalate this issue to be my top priority and it led me down a twisting turning path.
We have a SQL Server Reporting Services report (SSRS) that is the mainstay of our business. It is 25% of all of our report executions or about 4700 executions in the past 60 days. For the most part it runs just fine but I had reports of it being slow. The base of the report is a stored procedure that selects off of a pair of views, and I've long known that there were two issues with these views that caused Clustered Index lookups rather than index seeks, so that is what I tackled first. It helped everything BUT one case, a case that eluded me.
You see in Management Studio (SSMS) the query was returning in about 4 minutes. Not fast, but considering it is doing many different aggregations on 4 million records I let that slide. But in SSRS the report was timing out after an hour.
Naturally the assumption was that the report was simply having issues rendering. There are quite a few charts and graphs and what not. So we removed all of that, no dice. It was, however, discovered that the SP was being called 2x in the same report.... let's just say a less than talented developer who is no longer with us didn't realize that he was calling the same SP with the same parameters to pull a subset of columns down. This lead to a massive improvement, but not massive enough. Now the report ran but still in 30 minutes, not good.
I was baffled. Same SP, Same Parameters, Same login.... why would one be faster than another.... SO I went poking around and snagged the actual execution plan of both the SSMS and SSRS runs using the following code:
er.session_id ,
FROM sys.dm_exec_requests er
CROSS APPLY sys. dm_exec_query_plan ( er.plan_handle ) qp
I got the code from : and it was a great help.

I was able to display the execution plans, and saw that oddly enough they were VERY different. But why? It turns out that SSRS has a set of connection settings it applies to all of its connections, and they happen to have some keys in them for caching execution plans. turns out, in this case, Set ARITHABORT OFF was the key setting. SSMS keeps ARITHABORT ON. AND somebody must have run a very different set of parameters in this SP to get vastly different plans cached between the two.
Many different developers on the web suggested simply adding "With Recompile" to the SP declaration. That, however, turns out to not be a silver bullet. I recompiled the sucker, and then ALL of my execution plans were the 'bad' one. This, however, turned out to be a blessing. Now I had a stable state, a platform to do some old fashioned performance tuning. Prior, the execution plan was good enough. Now it wasn't so I rolled up my sleeves and dug in.
In this case the orig. developer used Table Variables declaring that they were "Always Better" than Temp Tables. There is rarely an "Always" when it comes to SQL Server performance... the answer is always "It Depends" and that's where we were.
On smaller datasets the Table Variables worked very well. But once they crept up over 50,000 rows (and in this case 3.5 Million Rows) they crapped out. I ended up replacing the two biggest variables with temp tables, and if the rowcount was over a threshold I indexed them. All of the sudden performance was much better across the board, and it didn't matter if ARITHABORT was set on or not... AND I didn't have to add With Recompile, which adds all sorts of other issues in a SP that is run with any frequency.
The code is being rolled out today. I'm writing this because of all the hubbub this setting has caused in online forums. Set ARITHABORT isn't the problem. It triggers an issue with cached execution plans. The cached plans tend to work better than getting a new plan each time... otherwise Microsoft wouldn't have done it. Once again, a highly variable and complex issue is boiled down to the basics. Basically, if there is a bad plan cached, chances are it can be cached for other scenarios... and it is best to simply tune the query to avoid that plan.

Labels: , , ,