And though it may surprise many, I generally do not find the problem to be very often with the SQL they are running. More often it's about configuration issues. For instance, it may be that they have not been keeping their indexes or statistics updated, or it may be that they need indexes that they don't have, or have too many that they don't really need. Or it may be that they have not done any tuning at all and the default settings for their database server are not be well suited to them.
And though such problems are common to any database server (MySQL, Oracle, PostgreSQL, and so on), I find that an awful lot of my clients (for whatever reason) do run on Windows and do use SQL Server (and that doesn't make them stupid or lazy. Different strokes for different folks).
So in this post I want to focus on some SQL Server resources, related to such matters, which I find that I refer folks to often. Some are blog posts, some are code you can run, and some are tools (mostly free).
People using other DBs would do well to read along and could find corresponding resources/tools for the same problems in their respective DB servers.
I offer the list of topic titles (and links to them) below, and have even created a YouTube playlist with them, but note that the links below go to pages that the author had created where he offered a helpful introduction to each video (more than appears in the Youtube description for each).
I had also wanted to offer here this brief overview of them, to set the context and encourage you to view them, so as not to dismiss them if other tech video series haven't impressed you. Whether you're new to IIS 7 or 8, or an old hat user, there really is something for everyone, including wonderful little tips he shares in passing during most videos (which average about 10-20 minutes each).
You may want to consider enabling it, but I would add some caveats and observations that I share below. Note that it's really quite easy to enable, and DOES NOT require a restart of CF (only of your web server, or technically in IIS, the application pool/s) to take effect.
If you've not yet read their blog entry, go check it out and then come back here for several observations I have to share, some of which I think you'll agree could be very important. (BTW, if you don't follow that Adobe CF blog regularly, you really should. Often great content, and very little "noise".)
While the conference name has changed (from cf.Objective()), there are still plenty of CF-oriented topics, and of course as nearly everyone would point out, it's good for everyone to expand their reach and focus.
To that end, I'll be doing a topic a bit different than my normal focus of CF server troubleshooting. Instead, recognizing that there will be folks there who either use other servers, or develop web apps or mobile apps, I'll do a bit of a "soft" topic on how to troubleshoot performance problems more generically, in:
Hey, my web app's slow. Where's the problem?
- ColdFusion 11 Update 5 prerelease build now available
- ColdFusion 10 Update 16 prerelease build available
This is on top of the release earlier this week of the final version of CF11 Update 4 (which had been in prerelease for a couple of weeks):
Great to see the CF team cranking on the bug fixes. I count just under 120 in the technotes for CF11 update 5, and just under 40 in CF10 update 16. And there were just under 200 fixes in Update 3 of CF11, which again was released just in recent weeks.
Yeah, but what about all those bugs?
Yes, I know some could twist things and say "yeah, but the problem is that there are so many bugs to be fixed". Sure.
Still, for others who may have longed to see their bugs fixed,
Wouldn't it be nice to know, at any moment (such as when things are going badly), just what queries (or stored procedures or commands) were running in the database at that point in time?
Well here's good news: if you're running SQL Server, the following SQL query will show you just that: the currently running SQL statement(s) and some additional details about each query including their duration, their database name, the program executing the SQL, the session id, and much more.
(If you're running MYSQL, you may know that you can get pretty much the same info with SHOW PROCESSLIST. Or if you want to do it as SQL, you can use SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND != 'Sleep'. Sadly, it's just not that simple in SQL Server, it seems, thus the need for this entry.)
The code for SQL Server
Following is the code, and then some discussion of it:
The book is a quick read, and really well done, including a discussion of what statistics are, why they're important, how to diagnose trouble with them, and how to use the SSMS interface and/or SQL statements/commands to better understand them.
There are plenty of screenshots and it's written in a tutorial manner. And while it's from 2010 and covers only up to SQL Server 2008, most of the concepts apply just as well to SS 2012 and beyond, and of course many organizations are still running on SS 2008 or even earlier!
Most important, Schmeling explains why it's important that you take responsibility to both create and maintain statistics (rather than leave it as something that "the database" should handle. And if you are leaving it as something "the db should handle", you can use this to make sure that person is doing their job with respect to statistics.)
That said, there are some concerns I have, with respect to how I fear some may read and take action based on it (especially the notion of deleting the cfclasses files, as a possible solution to the problem).
I'm not contradicting Rahul here, just elaborating on some points, as someone who (like some on the CF team) helps people with CF server troubleshooting every day.
I started to write these thoughts as a comment there, and (as often happens) it grew long so I thought it better to be a blog entry rather than a long comment, and point people here. Once I did that I decided to go further still, hoping to really help those interested to consider the issue more carefully. (It also gives me a chance to highlight again the Adobe CF team blog, something I recommend EVERYONE reading this should follow!)
One quick point (and update) for the TL;DR cloud: My recommendation is that you move the cfclasses folder out of that location, as a temporary test, to see if it makes CF startup happen faster. If it does, I explain why and what the implications are in the choices of renaming, deleting, moving, or disabling the related "save class files" feature. Also, I add an update in E.1 below (since posting this) which you may really want to read: consider turning off your anti-virus software's real-time protection against the cfclasses folder to see if that alone helps with startup.
It's a tragic position to be in. (I was tempted to title this, "Help. My JVM has fallen and CF can't get up", but I fear that dated and veiled 80's reference would go over the heads of many of my readers.)
The thing is, there are several reasons why your attempt to update CF's JVM can fail (all this applies to Lucee, Railo, and BlueDragon as well). And fortunately I can offer several things you can look at to hopefully quickly recover from or undo things. If you're interested, read on.
After complaining about how poorly he felt CF had responded to his seeking help, he threw in additionally that "the existing private consultants prices are a total joke - they are taking us for a ride!".
Well, I just couldn't let go his comment go unremarked.
I started to write my reply there, but it grew long (as is my wont). So rather than post there (where most comments are brief), I decided to post it here instead and point to it there. Perhaps some of my readers here may appreciate it as well, as I also talk about some thoughts on CF troubleshooting in general.
One of the most important features is the stack tracing feature, used to understand what's holding up a long-running request.
But as is often the case in a lot of the CF server troubleshooting consulting I do, I find the causes to be far less often what most people seem to suspect. So what would I look for when someone reported high CPU in ColdFusion (or Railo)? Read on.
And your operation fails. You're then prompted to "Click here to login", but even if you back up or client another link, you'll be prompted with the CF Admin login.
What gives? Why is it happening? And how can you fix things? Is CF broken? No, not in the sense that you need to reinstall or anything. The good news is that there is a quite simple solution. Well, there are several, depending on your goals.
The simple solution: delete the duplicate cfid/cftoken or jsessionid cookies that you will find your browser is sending to CF. But there is much more to this, as well as other solutions, which would be worth most readers taking a few minutes to read on here.
BTW, the same root problem can be the cause of your own application's users finding that they can't stay logged in. More on that in a moment.
Of course, in CF10 it's easier now because of the built-in "server updates" feature of the CF Admin. But in earlier releases, it was all on you to both keep up on the updates and to apply them manually. And a lot of people either never bothered, or may have tried and failed, or did it but got it wrong.
What you need to know
So in this blog entry, I some key info that will help you, if you may be in need of applying one or more of those updates to CF9 and earlier. Indeed, I'll point to some past entries I've done where I shared a lot more detail that I find is vital and rarely mentioned when some people try to share just the bare minimum of info (often leaving people hanging).
For instance, I'll help you answer such questions as what hotfixes do you already have applied? How do you find out? And you need to know exactly what version of CF you have, whether 9.0/9.0.1/9.0.2, 8.0/8.0.1, 7.0/7.0.1/7.0.2, and so on. I'll explain how to tell and why that's important, and especially when it comes to finding and applying hotfixes. And if you have applied hotfixes, are you sure you have done it right? It's easy to get things wrong and botch things. I'll help you avoid several very common mistakes.
(That's why it's so great that CF10 finally handles things for us. But this entry, focused on 9 and earlier, is not the place to discuss concerns with the CF10 hotfix mechanism. If you have questions or concerns about that, see the substantial CF10 Hotfix Installation Guide from Adobe, a 50-question FAQ on all things related to that feature.)
I'll also point you to where to find hotfixes and installers for CF9 and earlier (not as easy as it may seem), and still more.
If any of that's of interest, and I hope it is if you're on CF9 or earlier, then read on.
CF911: Why/when you MUST update the web server connector for ColdFusion 10/11 and may have missed it
In brief, a VERY common problem is that while they MAY WELL have applied the provided "updates" for CF, folks often do NOT notice that they may have to (and generally must) "update" the web server "connector" (if they are using an external web server, like IIS or Apache) as a separate manual step, after applying the update.
I explain here what that means, how do to it, and why you may miss that you need to.
(Or if you'd rather just have me help you quickly help you analyze and rectify your situation, whether with regard to the connectors or any other CF server troubleshooting, I can do that in a brief consulting session, likely less than an hour, remotely and securely. I provide all the detail here for those who prefer to "go it on their own". For more on my consulting services, including rates, approach, satisfaction guarantee, and more, see the consulting page at carehart.org.)
Anyway, here is the answer I wanted to offer to that question...