New SQL Server Show
SelectViews: Today on the show we have three interviews – Kevin Kline, Scott Golightly and Anthony D’Angelo. We talked about all sorts of things, from Business Intelligence tricks to upcoming tools.
[Watch the Show]
Featured Article(s)
Simple string encryption without using keys in SQL Server 2008
The following 2 rather simple functions demonstrate the power of encryption and decryption that SQL Server 2008 has. These are very simple examples but yet useful to start learning data encryption basics.
Locking and Blocking Investigations
Native tools with SQL Server can be challenging to use to determine what’s happening when it comes to locking and blocking situations. It’s important to get precise, "as of the moment" information from SQL Server so you can accurately see what’s causing the issues, specifically. Performance Advisor from SQL Sentry will help greatly in seeing what’s happening when you are experiencing the issues. You’ll get a graphical display of all nodes impacted by the deadlocks and you’ll be able to see resources, owners and even those waiting on the resource. Check out SQL Sentry’s Performance Advisor and you’ll see exactly what’s happening on your server and be able to make great decisions about the best ways to correct issues.
SAN Solutions, SQL Server and Many Misconceptions…
From our own Chris Shaw: "I think the comments that you have received to this point are great. There is a huge need there for SAN Administrators to understand SQL Server, or even SQL Server DBA’s to start learning SANS. Either way in the past few years I have seen this as being a huge gap. When I was working with a SAN not too long ago, the SAN Administrator would create a RAID and then many LUN’s on that RAID. These LUNS would be owned by different servers. In some cases this would not have been a big deal if the servers and solid busy times and times when there was no activity. So you could put 2 machines on the same RAID no matter the flavor of RAID.
This company clearly did not understand that if you have 10 spindles on a server and then you upgrade it to a SAN where it may have 12 spindles, but it has to be shared with another server that used to have 10 spindles… Well now what was 20 spindles for 2 servers is now 12 spindles for 2 servers. The end result was performance problems like we had never seen before. We had the SAN Vendor come in give us the configuration recommendation and when they left all they did was move the LUNS around. The Spindles were still shared."
Jeremy: "This is a great topic.
Steven’s comment about using the word spindle has been very effective for me as well. Also helping to educate the SAN admin’s on why I request different raid configurations for different files has been very effective. By explaining that a raid 1+0 configuration is helpful for logs due to their contiguous writes versus having data on raid 5 due to the decreased cost (less spindles needed) and the random read / write nature of data files.
David’s comment is also very interesting. I’ve been part of many SAN implementations over the years and I’ve only once seen the performance degrade when moving to a SAN. This was due to the SAN administrators creating one huge raid group (5) and putting exchange, databases and file shares all together.
At the end of the day, I believe that the responsibility for this to be effective rests on the owner / manager of the database system. Typically that’s the DBA. If a DBA doesn’t understand the topology of storage, be it NAS, SAN, SCSI, FIBER etc… it is their job to get up to speed on all of it. From a hardware performance standpoint it is nearly always the bottleneck, in my experience.
One quick lesson learned (the hard way). Even the best SAN guys don’t always understand the underpinnings of the technology and metrics provided by the SAN vendor. The experience that I’m referring to had to do with the SAN monitoring software showing performance capacity at 30 – 40 percent. Meaning that the SAN was reporting that it was only busy that percentage of time; but from the O/S / RDBMS side, I was noticing queue lengths / waits. It took over a week for me to convince them to give me a login to the monitoring software. Once I had it, I was able to find a statistic that explained everything… it was busy time per disk. On one of the raid groups; the group was reporting 40% busy, yet each disk was reporting > 80% busy. Meaning that the disk was thrashing and killing performance.
There are many other stories out there which are similar and some new ones with NAS. However, at the end of the day, SANS are simply awesome for databases."
Featured White Paper(s)
Managed .NET Connectivity
Database connections are the lifeblood of enterprise applications, administrating the secure and steady flow of information b… (read more)