SQL Server with Mr. Denny

March 12, 2009  11:00 AM

Back To Basics: Reading an Execution Plan

Denny Cherry Denny Cherry Profile: Denny Cherry

All to often when helping people look at query performance problems I’ll ask them to look at the execution plan, and see what it says.  Most query performance problems can be resolved simply by looking at the execution plan and seeing where you need to add an index.

All to often I (and others) then get asked, how to I look at the execution plan, and what does it mean?

Continued »

March 11, 2009  9:05 PM

What the hell is wrong with people?

Denny Cherry Denny Cherry Profile: Denny Cherry

I ran across the post A Culture of (Potential) Assholes: Sexual Harassment in IT on twitter today, and all I could think was “What the hell is wrong with people?”

Before I go on, I’d like to go on the record with a few things.  I’m a guy, and have on occasion been called a pig (I try and keep these occasions as few and far between as I can).  So I’m not above treating people like crap, but this is just so far beyond acceptable.

They story which is given on the thread is that a drunk “douche bag” (my words not his) walked up to a woman (which I assume he didn’t know) and gave her his room key telling her that she better he up to his room later.

I just can’t imagine doing this to someone.  Now maybe I’m a more evolved form of caveman, I don’t know.  Now, the original poster didn’t say which conference this was in his post, but I will because I think that it makes it even more pathetic that the creep said what he said to this woman.  This was the MVP Summit in Seattle.  For those that don’t know what the MVP Summit is, this is when Microsoft invites all the members of the MVP program for that year up to Seattle and show them all the shinny new things that they are working on.

Now I know that this is the first year that I’ve been an MVP, so some may think that I’m talking out of turn here, but too bad. I’m pretty sure that someone being an ass at the hotel isn’t covered by the NDA.

Why do I mention that this is the MVP Summit?  Because we were all invited by Microsoft to come meet our peers in the program, and the product groups.  One of the many things that you have to agree to when you become an MVP is a code of conduct.  While this code of conduct is pretty basic, and hard to enforce in the real world, when you are at the Microsoft Summit, its pretty easy for Microsoft to enforce it.  Rule #1 on the code of conduct is “No disrespectful behavior”.  There’s a breakdown which I won’t bother to go into, since that is pretty self explanatory.  (Don’t get me wrong I don’t think this sort of poor behavior would fly at any other event or time either.)

Everyone at the Summit is an MVP or a Microsoft Employee, so one would assume that the level of peer respect would be high.  Don’t get me wrong, there is a health banter between MVPs of different products.  The members of my product group (SQL Server) like to pick on the FoxPro and Access but we certainly have a respect for the MVPs of those products.

Becoming an MVP is hard work, no matter the product you represent.  Some of the MVPs catch a lot of crap for the products that the represent (XBox, Zune and Office probably catch the most crap from people).  Everyone who is an MVP got there for the same reason, they support their respective community and user base.  Without knowing any of the Zune MVPs (as an example) I know that they got to be a Zune MVP by providing steller support to the Zune user base.

I think that its just horrible to speak like this to anyone, especially someone you should be considering a peer.

I think, I’ve gotten off point, and I’m starting to ramble so I’ll wrap up with this.  The person who gave her his room key should be ashamed of himself.  And the guy who was sitting next to her who tried to make a funny comment and ended up WAY over the line should also be ashamed of himself.

I’ll leave you with this though.  If you can’t be bothered to respect people in public, then don’t show up.  If you insist on showing up, then consider this before you make an ass out of your self; someone who saw you may be a customer of yours, or a future customer of yous, or a future boss of yours.  Wouldn’t that suck to loose a big sale, or your that promotion because of some dumb ass thing you did when you were drunk on a company trip.  And no being drunk isn’t an excuse.  If you act like that when you are drunk, then you shouldn’t be drunk in public.


March 9, 2009  11:00 AM

Handy script for use when looking at blocking

Denny Cherry Denny Cherry Profile: Denny Cherry

A while back I was looking at a clients database and we were looking at why the ASPState database was having blocking issues.  So I through this script together to show not only the blocked processes, but also the blocking processes, but also include the name of the stored procedure as well as the statement within the stored procedure which was causing the blocking. Continued »

March 8, 2009  2:20 AM

Developing a SQL Server Database Backup Plan

Denny Cherry Denny Cherry Profile: Denny Cherry

Last week I published a new article on Enterprise IT Planet called “Developing a SQL Server Database Backup Plan” in which I go over some of the techniques which are used to backup databases and why you need to ensure that your databases are backed up.


March 7, 2009  6:20 AM

SoCal Coffee and Code was a success

Denny Cherry Denny Cherry Profile: Denny Cherry

I would have to say that the first SoCal Coffee and Code was a hit.  Only a few people came, but I wasn’t sure if any were going to show, so fora  first event I’d say that a few is a success.

First were a couple of co-workers named Rene and Hattan who stopped by to talk about SQL Server tuning.  We ended up talking about SQL Server tuning, VMware, upgrading their companies SQL Server, crappy VB6 code that they’ve seen among other things.  They are still running good old SQL Server 2000 in their environment.  They have a machine with only a few Gigs of RAM but a 20+ Gig database so we talked about adding more RAM to increase the buffer cache on the machine to decrease the response time.  Hattan had seen me speak at the SoCal Code Camp in January and started reading my blog, which led him to today.

Later Thu came by and we talked about SQL Server 2008, and how to install SQL Server 2000, 2005 and 2008 side by side.  We also dug into using fn_listextendedproperty to problematically retrieve the extended properties of columns on a table.  Apparently a legacy system at her company has some columns which are simply labeled as user defined and it’s been causing a problem not having them correctly documented anywhere.  The most reliable system that they came up with was extended properties.  Makes sense to me.

The last person to come by was Thomas.  He’s a fellow speaker from the SoCal Code Camps and we know easy other fairly well.  We chatted about SQL Server, and a project he’s working on which envolved moving data from mainframe systems to SQL Server.  Thomas was the last person to come over and we decided to grab some dinner together before calling it for the night.


March 5, 2009  11:00 AM

Coffee And Code in Irvine Tomorrow

Denny Cherry Denny Cherry Profile: Denny Cherry

Don’t forget tomorrow (Friday the 6th) is our first Coffee and Code in the SoCal area (at least the first that I know of).

We’ll be at the Starbucks at the Tustin Market place (2959 El Camino Real Tustin, California) from 3pm until 7pm (or there abouts).

See you there.


March 5, 2009  10:59 AM

Steps to Troubleshoot Connections to your SQL Server

Denny Cherry Denny Cherry Profile: Denny Cherry

People ask all the time why they can’t connect to their SQL Server. Here are some thoubleshooting steps that they should take in order to find out what the problem is.

Please keep in mind that without knowledge of your specific problem I can only point you towards the right direction.

Windows XP Running Service Pack 2 and higher (This includes Vista and Windows 7)
Windows XP SP 2 and higher by default turns on the Personal Firewall Component of Windows XP. This will by default block your connection to your SQL Server. You will need to open the firewall to allow this traffic, or turn off the firewall.

There are several steps that I am going to lay out here to attempt to find the problem. We will be starting with the most basic, and working up to the more complex. Some of the more complex will involve people from network security, network engineering and/or your ISP.

1. Can you ping the server?
From a command prompt type:

ping {servername}

If you can not ping the server then you need to make sure that both you and the server are online. (This may not mean that either you or the server are offline. It may simply mean that someone has blocked ICMP packets from passing from you to the server for some reason.)

2. Can you telnet to the SQL Service port on the SQL Server?
From a command prompt type:

telnet {servername} 1433

If you get a black window with a flashing cursor that means that you have connected to the SQL Server. If you get an error message that means that something is blocking your connection to the SQL Server. Check for firewalls running on both machines, or on the network between you and the server. If the server is hosted by an ISP outside of your company’s control, check with them, and with your ISP to ensure that neither of them are blocking port 1433. If they are blocking access to the SQL Server they will have another way for you to access the SQL Server, probably a web based version of Enterprise Manager. There are several on the market, and they all work fairly well.

There are a couple of ways to check what port SQL Server is listening on.
2a (SQL 2000). Log on to the servers console, and open Enterprise Manager. Right click on the server and click properties. Click Network Configuration. Check the properties for TCP/IP. It will tell you what TCP port the SQL Server is listening on.

2b (SQL 2005/2008). Log onto the servers console and open the SQL Server Configuration Manager.  Navigate to the SQL Server 200n Network Configuration and select the “Protocols for MSSQLSERVER (or YourInstanceName if a named instance).  Open the TCP/IP protocol in the right hand pane.  On the IP Addresses tab the port number that SQL Server should be listening on is shown.

2c. Check the Current SQL Server Log (the ERRORLOG if your looking at the actual file). At the beginning of the log there should be one line for each IP on the server. Something like:

SQL server listening on 1433.

That’s the port that it’s listening on.

If these lines aren’t there, and you’ve checked the TCP/IP is turned on, then there is probably an error saying that the port couldn’t be opened. This means that something else was using the port at startup. Odds are what ever was using it no longer is, and a service restart will fix the problem.

3. Can you connect to the file share on the server?
If this is being blocked that could also explain why you can not get connected. By default SQL Server will use TCP/IP port 1433 as it’s default connection method, with named pipes as a backup. If you can’t connect to the Windows file share you won’t be able to connect to the SQL Server over named pipes.

4. Can you connect to the SQL server from another machine on the same network as the workstation that isn’t working? If so then the problem is probably with the nonworking workstation, or some setting on a router or firewall that is preventing this machines access.

5. Are there any firewalls or ACLs on your network preventing access?

At this point odds are you are either connected to the SQL Server or you know why you are not. If you still can’t connect post a question in one of the SQL Server forums and someone will do there best to help you. You will want to post the results of these questions so that they don’t ask you to try them again.

This post is a copy of an FAQ that I posted on tek-tips.com a while back.


March 3, 2009  7:02 PM

Photo’s from last night

Denny Cherry Denny Cherry Profile: Denny Cherry

A couple of people have posted pictures from last night. So far I’ve run across which Andrew Karcher and I were both in. Thanks to Pinal Dave for posting it.

As I run across more photo’s I’ll post links to them.


March 2, 2009  2:43 PM

Standalone SQL Agent Update 3/2/2009

Denny Cherry Denny Cherry Profile: Denny Cherry

I’ve been quiet about the Standalone SQL Agent for a while now.  I haven’t forgotten about the project, in fact I’ve been busily working on it in what little free time I have.  I’ve been working on phase 2 of the project which is adding in the UI to handle job management.  Needless to say, I forgot how much I hate building User Interfaces.  I much prefer building windows services which no one sees.

I’m trying to keep the UI as simple as possible while providing all the functionality that the native job management via SSMS provides; even if not all that functionality is supported by the Standalone SQL Agent at this time.

Even though you can’t download a client installer, or the source (since I haven’t checked and in yet) I figured that I’d post a few screenshots to show where I’m at and get some feedback on it.

Server Job List

The first screenshot here is the job list which comes up after you select the SQL Server instance you want to connect to (yes you can connect to a remote instance so that you can manage SQL Express machines remotely).  It shows the jobs, there current state as queried from the SQL Server, if they are enabled and the Next Run Time.  Currently I’m not getting the Last Run Time back from the SQL Server so that needs to be corrected.

The next screenshot is the Job Info job step info page.  This should look very similar to what you see in SQL Server Management Studio or SQL Server Enterprise Manager.

It has the job name, owner, category, and the date information.

The third screenshot (below) are the job steps for the selected job.Job Steps This screen should also look very similar to what is shown in SQL Server Management Studio and SQL Server Enterprise Manager.

I hope that this looks useful as I’m still plugging away at the UI (I hate making UIs).

I’ve also added in a command line application which will remove the three (so far) stored procedures which are placed within the msdb database.  The service puts these in place when the Agent is started, and I need a way to remove them easily during install, so I put them in a separate executable so that they could be easily removed if needed.


March 2, 2009  9:00 AM

FREE Seminar: Spend a Day with the Experts – Aliso Viejo, CA

Denny Cherry Denny Cherry Profile: Denny Cherry

Quest has put together a one day free seminar at the Quest Software office on Friday March 13, 2009.

Brent Ozar and myself will be presenting several presentations on SQL Server 2008.

For more information and to register for the event visit Quest webpage for this event.

I’ll be speaking on Spotlight on SQL Server Enterprise and Brent is doing a couple of presentations.


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: