[Looking for Charlie's main web site?]

Tending the SQL Server garden: tuning and maintenance resources (updated and useful)

Often in my role as an independent consultant providing ColdFusion server troubleshooting services, I find myself helping folks determine whether or that a problem is in their database.

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.

[....Continue Reading....]

Free, simple code to find out what SQL statements are running slow in SQL Server right now

Often when people are trying to troubleshoot seeming problems in ColdFusion (or whatever app server you use), they may wonder if (or have tools which suggest that) their CF requests are being held up waiting for some long-running query to run in the database.

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:

[....Continue Reading....]

Understanding SQL Server Statistics: A great, free, relatively brief ebook

If you want to understand the important concept of SQL Server statistics (a mechanism within SQL Server which tracks metadata about your data, and which can significantly affect query performance if not managed well), I think you can do no better than than to spend an hour or so reading the free 40-page e-book, SQL Server Statistics, written by Holger Schmeling and offered by Red-gate. book cover

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.)

[....Continue Reading....]

Got problems with SQL Server tempdb? Here are some resources

I was working with a client the other day where it turned out they had a HUGE SQL Server tempdb. I shared with them some pretty substantial and informative MS resources. In case this may ever happen to my readers, I wanted to pass them along:

You may want to check out several other equally useful TechNet articles on SQL Server (like the first one above, but available on a wide range of issues).

Hope that's helpful to someone.

Having problems with SQL Server/Oracle/DB2/Sybase? Check out Confio Ignite

Hey folks, if you're having problems with your CF apps and you determine that (or wonder if) the cause may be due to problems in the database, check out Confio Ignite, a commercial tool that may be well worth the price for you.

Sure, there are many DB monitoring tools out there, but Ignite focuses on tracking, analyzing, reporting, and explaining wait events within the database--and you'd be amazed how often waits caused by your code, that of others, or from other operations in the DB are the explanation for poor performance. It can help target exactly what SQL statement or other operation is a cause of significant waits.

The tool presents the data aggregated over time, so you can view it per hour, day, week, etc. Great for both drilling down to find hot spots, and for viewing how coding/config improvements (resulting from your responding to the analyses) have led to performance improvements over time.

The tool runs with low overhead: it reads data that the DB provides, storing it in a database and providing a web-based interface to view that data. The process to read the data and create the repository (and present the web-based interface) can (and should) be done on a server separate from the server being monitored.

Here's a nice 2-minute demo. There's also a free trial, of course, and it's pretty quick and easy to install and benefit from.

As I noted in the title, it works with SQL Server, Oracle, DB2, or Sybase (sorry, not MySQL. Don't know why). And while it's a commercial product, it's not a ridiculously high price (as for some tools). I just learned of it in the past few weeks, and one customer of mine who tried it has been just thrilled with the results. I hope to write more about it later, but wanted to at least get this info out for folks to consider.

Several SQL Server Performance Tuning how to's

Need to solve problems with SQL Server performance? Here are several resources that can help:

Some of these offer knowledge and understanding, others offer specific steps to follow. Most offer links to still other resources (including often specific entries in Books Online).

Note that some may be written more to those still running SQL Server 7 (less likely) or SQL Server 2000 (not so unlikely), though many do cover SQL Server 2005 as well. Just keep this in mind while reading, both if some step doesn't seem to follow, and also in case it may be that some setting that suits one release may not suit another. In fact, some of the resources specifically discuss how things have changed in later releases, and how in fact some settings or techniques for older releases may be very different for later ones. All this just calls for discernment while you read.

These are all in addition to a couple of entries I wrote back in April (starting here) on some other advanced tools and techniques for diagnosing SQL Server problems.

Sometimes CF gets blamed for problems when in fact the problem is in the DBMS--and it's not always a problem due to the SQL being sent from CF. Sometimes the same code can run very differently one time than another. In that case, you really need to understand why this is happening. I hope the resources above may help you. If you ever want direct assistance, this is one of the things I help people with in my available consulting.

Helpful info on SQL Server Diagnostics: Part II

Last week I pointed out a useful blog entry on SQL Server troubleshooting. Now there's a Part II, which addresses such things as:

  • Are there SQL batches that actually run for a long time?
  • Is there blocking in the database?
  • Are there any problems in the configuration options of SQL Server?
  • Is there a disk IO problem?
  • Are there any indications of memory problems?

Good stuff that even we CFers can benefit understanding. Again, while it's from a Microsoft support engineer who focuses on .NET debugging in her blog, there's really nothing about this blog entry or the last that's .NET specific.

Helpful info on SQL Server Diagnostics

Here's a useful blog entry, "What if I suspect that my performance problem is in SQL server?". It's easy to blame CF for performance problems, but sometimes the problem is in the database--and it could be configuration, or database setup, all in addition to your own (or someone else's) SQL coding.

The entry focuses on using the tools known variously as PSSDIAG and SQLDIAG, either built-into SQL Server 2005 or available for free download for older editions, and it does a nice job of walking through it in a friendly way, with screenshots and more.

The entry is on the blog of Tess, a Microsoft support engineer, called If broken it is, fix it you should. While the majority of her entries are on .NET, this was a guest blog entry with a SQL Server support engineer. Despite her blog being mostly about .NET, there are occasional gems like this which are of equal value to CFers.

Resources for getting a much greater understanding of the SQL Server query plan/procedure cache

Ever wanted to understand the SQL Server query plan/procedure cache better? It can be vital to good performance of SQL statements, especially with respect to the concept of "bind variables" or "parameterized queries" (using CFQUERYPARAM in a CFML context, or Parameters.Add() in a C# context, among other ways).

Update: If you may wonder why any of this is important, see some subsequent talks I gave on the topic, that give far more context on the problem. While you rarely hear about it from others, it is a source of potentially significant problems so is worth understanding more completely. See the "My Own Talks" section at the bottom of this entry.

Yet we so often just kind of assume the database will do what's best. Or we blindly use (or recommend use of) bind variables without fully understanding why, nor understanding some implications when it may not always be such a good idea.

And did you know that if you can't (or don't) change your code to cause parameterization, did you know that the database can do it for you, either automatically in some simple cases, or by way of an enforced parameterization, either for an entire table of for queries that meet certain "plan guides"? There are many implications to understand in all this.

And how do you track the plan cache (using DMVs, the profiler, perfmon)? And how does it work (allocation of memory, flushing the cache automatically or manually)?

Fortunately, I've found many great resources to help you understand, and I've not found them listed (all together) anywhere else yet.

The info applies to 2005, 2000, and 7, though some aspects may differ, as the resources indicate. (There are even substantive changes in 2005 SP2 that are important to note over the RTM and SP1 releases.)

Huge Plan Caching Article Series by MS Engineers

First, I'll point out that some MS engineers have put together a long and very resourceful series of article-length blog posts on the topic, with explanations, code, demos, troubleshooting techniques, and lots more.

They do kindly offer a "table of contents" page listing all the topics covered, with bullet points about topics within each entry.

Sadly, that page doesn't offer URLs to the articles, nor have any that I found there, nor do the articles link to each other. You can dig around and find the URLs, even finding a category page that lists them in rather random order. But I've looked around the web and can't find any page that lists them all with their links, in order, so here you go:

  1. Structure of the Plan Cache and Types of Cached Objects
  2. Sql_Handle and Plan_Handle Explained
  3. How Cache Lookups Work
  4. Query Parameterization
  5. Retrieving Query Plans from Plan Cache DMV's
  6. Best Programming Practices
  7. Costing Cache Entries
  8. Factors that affect Batch Cache-ability
  9. Memory Pressure Limits
  10. Plan Cache Flush
  11. Temporary Tables, Table Variables and Recompiles
  12. Plan Cache Trace Events and Performance

They also offer a series of extensions to that, on troubleshooting:

Chapter in "Practical SQL Server 2005 Troubleshooting" book

Next, I'd point out that one of the contributors to that, Bart Duncan, is a Microsoft Support engineer who I saw speak on the topic at the SQL Server PASS 2006 conference, where I first learned a lot of this info.

He identified then that a lot of the info was in a chapter of a new book, SQL Server 2005 Practical Troubleshooting: The Database Engine, which I've since gotten and was indeed very useful.

If you're a member of the O'Reilly Safari service (or join for their trial), you can find the chapter online.

Of course, both the talk and the book came out before the article series above, and certainly before SP2, so I would recommend you consider both. (I've not yet done a comparison to determine what may be in the chapter that's not in the articles.)

SQL Server BooksOnline

Of course, it always pays to read the docs, and there are indeed some discussions of this concept and related features in the SQL Server Books Online, but I honestly found the info above either easier to find or just more complete.

My Own Talks

I've made my own attempts to communicate some of this info myself in a couple of talks I've given to both CFML and SQL Server audiences:

But really, there is just too much to communicate in any one hour. If this topic seems of interest, take a few hours to digest all the above. I think you'll be AMAZED at what you learn.

New (free) Performance Dashboard for SQL Server 2005 SP2

Those using SQL Server 2005 may want to take note of a new "Dashboard Report" option for SP2, to help monitor and resolve performance problems, including capturing diagnostic info when a problem is detected.

Common performance problems that the dashboard reports may help to resolve include:

  • CPU bottlenecks (and what queries are consuming the most CPU)
  • IO bottlenecks (and what queries are performing the most IO).
  • Index recommendations generated by the query optimizer (missing indexes)
  • Blocking
  • Latch contention

The report is an extension of the Custom Reports feature introduced in the SQL Server 2005 SP2 release of SQL Server Management Studio. Note that Reporting Services does not need to be installed.

The reports retrieve info from dynamic management views. They don't poll performance counters or require tracing be enabled. They also do not store a history of performance over time. So it's a lightweight (yet powerful) monitoring option.

You can get the extension itself at:

Performance Dashboard Reports

There's also a complete article about how to install it from Black Belt Administration: Performance Dashboard for Microsoft SQL Server, Part I , at Database Journal (and from which I obtained the image above).

Have you sought a keyboard shortcut to "open table" in SQL Server Management Studio?

I'm a huge fan of keyboard shortcuts, so imagine my dismay when I noticed that the new SQL Server 2005 "open table" option, available in Management Studio when you're viewing the tables in a database, had no keyboard shortcut (or Admin menu equivalent). The feature opens an editable grid of data in the table, which is a great when you need to do a quick fix of the data. But you have to right-click to see the option--I wonder how many never even notice it?

So I asked around and got an answer to my keyboard dilemma which actually is a generic windows solution. Did you know that you can get the equivalent of the right-click by using Shift-f10? Whatever you have the keyboard focus on, it will open its corresponding context menu. Very nice.

So in SQL Mgt Studio, open the database, then its tables, then select the table (all of which can be done with the keyboard), and then use shift-F10. You'll suddenly see that each context menu option shows the standard underline under the key to hit to execute that command (it's the "o" for open table).

Hope that may help others.

Need to migrate an Access DB into SQL Server? Here's a solution you may have missed?

If you have need to migrate an Access DB to SQL Server 2005, Microsoft has a free tool to help, which it seems many may miss.

Check out: http://www.microsoft.com/sql/solutions/migration/default.mspx#EYC

Thanks to Teddy Payne for pointing it out in his blog entry, which also offers an additional document he's put together on the migration steps.

Solving error connecting to SQL Server from ColdFusion on Localhost

Are you getting the error, "Connection refused" or "Error establishing socket to host and port", trying to connect to a SQL Server database from ColdFusion?

The short answer is:

Open the "SQL Server Configuration Manager" in SQL Server, then "SQL Server Network Configuration", and its "Protocols For [yourserver]" option. Open it and ensure that TCP/IP is enabled as a protocol. If not, enable it, and restart SQL Server.

And if it is enabled, right-click on that TCP/IP option, choose the "IP Addresses" tab, and among the listed features, check if "ipall" (among the last in the list) has the port set to 1433 (or whatever is your SQL Server port), then restart SQL Server. More detail below. If you don't want to enable the "ipall" option, check the other entries to find the IP you're using, and ensure it's "enabled" and the "tcp port" is set to 1433.

And if it's "still not working" after you make these changes, do make sure that the problem is not that you're now getting a different error, like "Cannot open database "yourdbname" requested by the login. The login failed." :-) That just means you have a new and different problem to solve, now that this one is resolved.

The rest of this entry explains additional details, such as how to find and make that change, what specific errors you get, and how I found the information, in case any of it helps others.

[....Continue Reading....]

BlogCFC was created by Raymond Camden. This blog is running version 5.005. (Want to validate the html in this page?)

Managed Hosting Services provided by
Managed Dedicated Hosting