I got the idea for this article when one of our clients complained that their server’s performance was degrading during business hours. They thought it was weird that at the same time, SQL Server would list more than 1200 connections on SQL server Activity Monitor.

The server hosts more than 50 databases that serve an ASP.NET application hosted on some servers in a web farm. These servers issue connections to the databases in a distributed manner to balance the web application load. I tried to discover what these connections were doing and to what databases they were connected. Connections grouped by database:

select  db_name(dbid) , count(*) 'connections count'
from master..sysprocesses
where spid > 50 and spid  @@spid
group by  db_name(dbid)
order by count(*) desc

This showed some databases having more than 300 connections associated with them.

What about logins used?

select  loginame , nt_username, count(*) 'Connections count'
from master..sysprocesses
where spid > 50 and spid  @@spid
group by  loginame , nt_username
order by count(*) desc

This showed a mix of windows domain accounts (those with values in column nt_username, e.g: domain\user) beside SQL authentication accounts (those with column nt_username empty, e.g: “sa”).

In order to reduce the number of times that new connections must be opened, applications may use connection pooling. This was clearly not the case here, and all these connections resulted in what is known as “pool fragmentation”.

So what’s connection pooling? how does it work, what can cause pool fragmentation and how can we avoid/reduce it?

(more…)

Server virtualization is becoming a very hot topic out in the wild, and for good reason. This is a fantastic technology to bring to the x86 platform. When used right, it will solve a lot of problems and potentially reduce Total Cost of Ownership (TCO), while allowing many data centers to become greener by reducing power consumption, cooling requirements and waste (fewer servers = less hardware to dispose of later on). Not every server, however, is a candidate to be “virtualized.” When used in the wrong situation, virtualization can be a support nightmare.

I’m going to write a small series of posts on this topic because its something that will affect a lot of us even if you aren’t a DBA. I’m starting at the very beginning, so if you’ve already created virtual templates for all of your core applications, this post won’t have anything new for you. If you’re wondering what about “virtual” is virtual, this is the post for you to start at. Due to the number of different technologies and platforms and and lack of time on my part, I’m going to stick to Microsoft technologies (Microsoft Virtual Server 2005) and write from the perspective of an MS SQL Server DBA. Let’s get started.

What is server virtualization? (more…)

Welcome to the 117^th edition of Log Buffer, the weekly review of database blogs.

For those of you who don’t know me, My name is Nicklas Westerlund, and I’m a MySQL DBA with The Pythian Group. This is my first time writing Log Buffer, and I hope I’ll do it right.

Let’s start off with SQL Server, where Simon Sabin asks if you know what concurrency is and how to improve it. And on SatisticsIO, Jason Massie focuses on the SQL Server 2008 experience instead, which should provide more inside knowledge of the technology used.

Continuing on with SQL Server 2008, the engineering team is sending loads of engineers to the SQL PASS Conference, as the SQL Server Customer Advisory Team tells us in their post on what, in their opinion, just may be the best PASS Conference yet. And if you’re into meeting engineers, then perhaps you’d also like to know how that patching is done in SQL Server 2008, which PSS SQL informs us about.

The folks over at sqlserver-qa.net also give us an overview of the SQL Server Web Edition.

Let’s move over to Oracle, where there’s still a lot of buzz about Exadata, and let’s start with with the second part of the Exadata FAQ by Kevin Closson. In that post he also mentions his interview on the Exadata with Paul and Christo here at Pythian.

(more…)

A short post to direct people’s attention to and solicit comments on the following from someone who is admittedly a hero of mine, Richard Stallman:

But Richard Stallman, founder of the Free Software Foundation and creator of the computer operating system GNU, said that cloud computing was simply a trap aimed at forcing more people to buy into locked, proprietary systems that would cost them more and more over time.

“It’s stupidity. It’s worse than stupidity: it’s a marketing hype campaign,” he told The Guardian.

“Somebody is saying this is inevitable – and whenever you hear somebody saying that, it’s very likely to be a set of businesses campaigning to make it true.”

The 55-year-old New Yorker said that computer users should be keen to keep their information in their own hands, rather than hand it over to a third party.

His comments echo those made last week by Larry Ellison, the founder of Oracle, who criticized the rash of cloud computing announcements as “fashion-driven” and “complete gibberish”.

“The interesting thing about cloud computing is that we’ve redefined cloud computing to include everything that we already do,” he said. “The computer industry is the only industry that is more fashion-driven than women’s fashion. Maybe I’m an idiot, but I have no idea what anyone is talking about. What is it? It’s complete gibberish. It’s insane. When is this idiocy going to stop?”

That blockquote links to the article at the Guardian where Stallman is interviewed and quoted. Please follow it to read the article in its entirety.

What do you think?

Welcome to the 116^th edition of Log Buffer, the weekly review of database blogs.

This was the week of Oracle Open World (OOW), Oracle’s gigantic annual get-together in San Francisco — always the heaviest week in Oracle blogs, so let’s start there.

For day-by-day coverage of OOW on the ground, I recommend Doug’s Oracle Blog: OOW Day 1, OOW Day 1.5, OOW Day 2, OOW Day 3.

Tom Kyte shared a podcast from OOW 2008, and interview with Oracle Magazine editor Tom Haunert, in which Tom, “ . . . stirs things up in this conversation about Oracle OpenWorld happenings, a new approach to publishing, and the trouble with triggers.”

Oracle teased everyone right at the beginning with word that CEO Larry Ellison’s keynote, carrying the title “Extreme Performance,” would introduce something big and new. And there was much speculation in the blogging world, some of it quite perspicacious. “Big and new” was soon going by the tantalizing nom-de-hype “X”. And before Larry’s keynote was even over (before he mothballed the black mock-turtleneck for another year), X was no longer unknown.

Writes Lucas Jellema on the AMIS Technology blog — The secret is out: Oracle launches “The Database Machine” - becoming a hardware vendor! “The big announcement that had loomed over the conference has been made. Oracle - in joint partnership with HP - introduces the world’s fastest hardware for running databases and especially data warehouses: the Exadata Storage Server.” Click through for Lucas’s précis of what it’s all about.

On blogs.oracle.com, Jack Flynn has some video excerpted from the keynote.

Lucas’s story has a picture of the thing itself, albeit a somewhat blurry one. Here’s a better image of one of the two new machines, the Exadata. Oooh, just look at it! Cor!

(more…)

The morning begins with this page: “a large number of sessions are blocked on one of your managed SQL Server 2005.” So you go and check out the Activity Monitor, and you can tell something unusual is going on:

Blocking chain of 200 procedures, your server is crawling with high CPU and requests are coming out a funnel. Now, don’t worry, the waitresource column provides us with the information to start zooming in on our problem. In this case, we have value “TAB: 7:357576312 [[COMPILE]]”. Disregarding the fact that the resource description says TAB, run the following query with the resource information to get the database and procedure being locked:

select db_name(7)

Use [PerformanceReports] -- the database name we got from the select above
GO

select object_name(357576312)

Knowing the procedure, it’s then a matter of discarding possibilities. The common causes of COMPILE locking are well documented on the Microsoft KB, “Description of SQL Server blocking caused by compile locks”, so if you have a chance, go on and read that. If you don’t, then this is the gist of it:

(more…)

Welcome to the 115^th edition of Log Buffer, the weekly review of database blogs.

I must thank Paul for taking over at last minute for LB#114 last week, when, as he put it, “ . . . a killer combo of painkillers and the pain that the painkillers can’t kill . . . ” reduced to me a less-than Log-Buffer-capable state. Or to be more precise, to a writhing, benighted gargoyle of misery. (Too colorful?)

Anyway, the good news is that I’m better. Not all better, mind you. Between the tooth thing and my spending all my working time on a special project, there was nothing left for poor old Log Buffer. So, I face the choice: throw it open to you, LB’s loyal readers for your contributions; or adopt Paul’s approach¹ from last week, and use the nifty AideRSS.

I’m going to bet on our readers. Let’s hear from you with your picks for best database blogs of the week gone by. I promise you a real, proper Log Buffer next week, from someone. If not me, well, Nick Westerlund still wants his go, and Ward Pond is back looking for a slot.

Until then, wish me luck with my angry tooth.

1. The truth is that I was briefly worried about having my job taken away by software. My concerns were allayed, at least partially, when I saw that the original software-built list of database blogs also included an item from “manscaping.com”, which I’m fairly sure had little or nothing to do with database administration.

This is the 114^th edition of Log Buffer, the weekly review of database blogs.

I am sorry to say that this log buffer was supposed to be edited by Dave Edwards, but he’s suffering from severe and long-lasting tooth pain and until his root canal is done he’s KO’d by a killer combo of painkillers and the pain that the painkillers can’t kill. I’ve been there myself, twice, and here’s a tip Dave. It hurts until the dentist takes out the needle. Then the pain goes away while he digs. The pain comes back that night. The next morning it’s worse than ever, unbelievably, writhingly bad. But later that afternoon, blisssssssssssss. :-) Good luck man.

This Log Buffer has been generated in a completely automated way with the help of the incredibly awesome AideRSS.

To give you an idea of just how awesome it is, I was able to load up Dave’s complete OPML file of all the blogs he monitors for Log Buffer. And AideRSS applied it’s magical PostRank algorithm which scores blog posts based on how many comments, del.icio.us bookmarks, blog links from other blogs, etc. that it received, along with some more secret sauce they don’t publicly tell us about (kind of like Google with their Pagerank equivalent). The number to the left of each headline represents the linked item’s AideRSS PostRank.

It did a great job of automatically selecting the best posts from the last week.

To give you an idea of AideRSS’s helpfulness, here are a couple useful feeds I suggest you subscribe to:

1. PlanetMySQL, but only with posts that rank “Best”
2. OraNA.info, again only the posts that rank “Best”. Note that there is a bug in Eddie’s feed that makes it impossible to use all possible information on the ranking.
3. SQLBlogs.com processed by AideRSS to show only the best posts.

While I have no idea how AideRSS plans to make moolah, I think we can agree that is some kind of awesome if you’re like me and can’t afford to miss a big story, but can’t afford the time you would need to read it all. Many thanks to Andrew Baldwin and although that’s the AideRSS about page there there’s a good pic of Andrew on that page. I first met Andrew at MySQLConf 2008 this spring and he’s a great guy and a great advocate for this service.

With no further ado or free advertising for AideRSS, here’s this week’s fully automated Log Buffer. We do not plan a fully automated Log Buffer to become routine but depending on the feedback we might adopt this approach whenever we have a last minute cancellation due to illness or what have you. So your feedback would definitely be appreciated, thanks.

10.0 - Random selection, with a bias ..

(more…)

All,

I’m writing to help get the word out that Microsoft announced a major security vulnerability in GDI+, a component that is included and vulnerable to remote code execution exploits in every supported release of SQL Server 2005.

You can find our more about the vulnerabilities and affected products (there’s a long list, not just SQL 2005) at the Microsoft announcement here.

There is an update already available, so you probably want to evaluate an accelerated deployment of that. If you are a current Pythian client, we’ll be reviewing this patch for you. If you are not, now would be a good time to sign up, and Michelle will take care of you. :-)

Snippet from the announcement:

Executive Summary

This security update resolves several privately reported vulnerabilities in Microsoft Windows GDI+. These vulnerabilities could allow remote code execution if a user viewed a specially crafted image file using affected software or browsed a Web site that contains specially crafted content. Users whose accounts are configured to have fewer user rights on the system could be less impacted than users who operate with administrative user rights.

This security update is rated Critical for all supported editions of Windows XP, Windows Server 2003, Windows Vista, and Windows Server 2008, Microsoft Internet Explorer 6 Service Pack 1 when installed on Microsoft Windows 2000 Service Pack 4, Microsoft Digital Image Suite 2006, SQL Server 2000 Reporting Services Service Pack 2, all supported editions of SQL Server 2005, Microsoft Report Viewer 2005 Service Pack 1 Redistributable Package, and Microsoft Report Viewer 2008 Redistributable Package.

This security update is rated Important for all supported editions of Microsoft Office XP, Microsoft Office 2003, 2007 Microsoft Office System, Microsoft Visio 2002, Microsoft Office PowerPoint Viewer 2003, Microsoft Works 8, and Microsoft Forefront Client Security 1.0. For more information, see the subsection, Affected and Non-Affected Software, in this section.

The security update addresses the vulnerabilities by modifying the way that GDI+ handles viewing malformed images. For more information about the vulnerabilities, see the Frequently Asked Questions (FAQ) subsection for the specific vulnerability entry under the next section, Vulnerability Information.

Recommendation. Microsoft recommends that customers apply the update immediately.

Known Issues. Microsoft Knowledge Base Article 954593 documents the currently known issues that customers may experience when installing this security update. The article also documents recommended solutions for these issues.

This is the 113^th edition of Log Buffer, the weekly review of database blogs.

Sheeri Cabral gets things going this week with her coverage of this rumour: Monty Widenius Leaves Sun/MySQL. If it turns out to be true, that seems like bad–or at least sad–news for MySQL, but Sheeri’s take is mostly an optimistic one.

MySQL’s Kaj Arno responds to the rumours on Monty resigning: “First, it’s a rumour.  . . .  Second, Monty’s resignation has been a possible outcome already since years before the Sun acquisition.  . . .  I can neither confirm nor deny the rumour.”

On CNET’s The Open Road, Matt Asay says. “Monty has done the right thing with his dissent. He has taken it outside the company, as Arjen Lentz, MySQL’s twenty-fifth employee, did before him. Arjen continues to be both a promoter and critic of MySQL, but is able to do so publicly without the constraints of an employee agreement. I assume Monty will do the same, and rightly so.” That’s not all the response out there, and there will certainly be still more as this develops.

In non-Monty blogs, Brook Johnson of Database Science asks, can a timestamp be slower than a datetime? (more…)