SQL Server

DateTime Manipulation

The date time data type and the date time functions within SQL are things that I see coming up time and time again in news groups and forums. Questions on how to get rid of the time, how to get the first day of the week, the last day of the month and so on. With the new Date and Time data types coming in SQL 2008, things will get easier, nut until then we have to do things the hard way.

In systems I’ve worked on I’ve seen several implementations of functions to find the first and last day of a week, a month or a quarter. Some have worked well, some have worked and others, well, haven’t

(more…)

Memory and SQL 2005 SP2

Or “Why are all my processes waiting on memory. There’s tonnes of memory

It’s probably not new news that there was a fairly nasty memory-related bug in SQL 2005 RTM and SP1 that was related to the relaxing of limits on cache size. Specifically the TokenAndPermUserStore cache.

On systems with large amounts of memory (20GB+) and frequent ad-hoc queries or significant usage of dynamic SQL, the cache can grow quite large, and by quite large I’m talking upwards of 2GB. I think I saw the cache at close on 8GB at one time on one of my servers.

The problem with this is that is takes quite a bit of time to search through several GB of cache to find the required tokens. Making matters worse, access to that cache is synchronised, so only a single thread may have access at a time.

The main symptom of that problem is lots of CMEMTHREAD waits without an apparent wait resource and a higher than normal CPU usage.

But that problem was fixed in SP2 with a change to the caching behaviour. Right?

(more…)

Indexes for aggregates

It’s well known that indexes on columns used in where clause and for joins is a good thing in SQL, but what about other places. How about on aggregates?

Consider a simple table with an amount and a customerID. It’s a common requirement to calculate the total amount that each customer has paid. No conditions are enforced, so this would seem like a place where an index won’t help. Well, let’s see. (sample code at end)

The clustered index (and hence the physical order of the rows) is on the identity column.Take the following query.
SELECT CustomerID, SUM(Amount) FROM Payments group by customerID

(more…)

Execution plans from Profiler

There is another way to get hold of query execution plans than through query analyser/management studio. In SQL 2005, Profiler has a large number of plan-related events. It had some in SQL 2000, but they were quite hard to work with.

The interesting ones in SQL 2005 are as follows, all found under the performance collection in profiler.

  • Showplan XML
  • Showplan XML for query compile
  • Showplan XML Statistics Profile

As soon as one of these is selected, a third tab appears on the trace setup screen, asking for the file to save the xml plans. You can choose to save the plans within the profiler trace file, however the file tends to get very, very large when that is done.

TheĀ  Showplan XML for query compile event fires every time a query is compiled, and it produces an estimated execution plan.

The showplan XML event fires every time a query runs, and it produces an estimated execution plan. When I tested it, it looked as though it was the actual plan, however all the run-time information (actual rows affected, etc) were 0

The Showplan XML statistics profile event fires every time a query runs and produces an actual query plan, with the run-time information in it.

Be careful when running profiler with these events on a busy system as they are quite large, and you can end up with very large output files very quickly. Also, on a busy server they can be very frequent events and capturing them with the profiler front end could result in performance degradation.

Shrinking databases

Or “Order the pages, shuffle the pages.

Do you ever shrink your data files? I’ve personally never been fond of it, especially for production databases. After all, they’ll simply have to grow again and, especially if the data files are on independent drives, there’s little difference between space free on the drive or space free in the data file. There is also a more insidious reason for not shrinking a database.

Let’s take a very simple database (The creation code is at the end of the post). I have two tables, both with a tens of thousands of rows. Both tables have a clustered index on a uniqueidentifier and are heavily fragmented (>99%).

DBCC SHOWCONTIG(LargeTable1) -- 99.30%
DBCC SHOWCONTIG(LargeTable2) -- 99.21%

To fix the fragmentation, rebuild both indexes. That fixes the fragmentation, but now the data file is using almost twice the space necessary.

DBCC ShowFileStats -- 3363 extents total, 1697 used (215 MB total, 106 MB free)

So, shrink the database to release the wasted space back to the OS

DBCC SHRINKDATABASE (TestingShrink, 10) -- Shrink to 10% free

That’s fixed the space issue. But now, have another look at those two indexes that were just rebuilt.

(more…)

Execution plans, estimated vs actual

This is the second post on execution plans. I’m going to briefly discuss estimated execution plans and actual execution plans, the differences between them and when you would want to use which.

First however, a bit on query execution, just so that I know everyone’s on the same page.

When a query is submitted to SQL Server (and for simplicity I’m going to assume it’s a straight select statement not a procedure) the query is parsed, then bound to the underlying objects(tables, views, functions, etc). Once the binding is complete, the query passes to the query optimiser. The optimiser produces one or more suitable execution plans for the query (more on that in a later post). The query is then passed into the query execution engine, which does the memory grants, picks a parallelism option, if necessary and executes the various query operations.

Estimated execution plans

When an estimated execution plan is requested for a query, the query goes through the parsing, binding and optimisation phases, but does not get executed.

(more…)

Structure of an execution plan

So, this is the first part of the series on how to read an execution plan. In this post, I’m going to give a high-level overview of how the execution plan looks and how, in general, to read it.

For the purposes of this and other posts in this series, all screenshots will be from SQL Management Studio (the 2005 GUI) and all comments on features will refer to that tool. Query Analyser, from SQL 2000, is very similar. For other querying tools, your mileage may vary.

Here’s a very simple execution plan. The query that produced this plan is just a join of three tables in the AdventureWorks database on SQL 2005.

Execution plan

(more…)

Reading Execution plans

One of the more common questions I get from colleagues is on how to read the execution plan that SQL produces. At first I found it quite a hard question to answer as no one really taught me how to read them. When I started with performance tuning I got a bit thrown in the deep end.

This is going to be the first in a series, maybe short, maybe long, on reading SQL’s execution plans. I’m going to go into the overall structure of a plan; the differences between the actual and the estimated plans, and how to obtain both; and some of the more common query operators and how they affect query performance.

If you have any specific questions that you’d like answering regarding execution plans, please post a comment here. Otherwise I’m going to ramble on a bit touching on things that I feel are important and relevant and maybe missing things that you would like to know.

Current articles in this series, in order of writing, are

  1. Structure of an execution plan
  2. Execution plans, estimated vs actual
  3. Execution plans from Profiler
  4. A basic execution plan
  5. Execution plan operations – scans and seeks
  6. Execution plan operations – joins
  7. Execution plan operations – aggregate
  8. Execution plan operations – misc
  9. Execution plans – important properties
  10. Execution plan – more properties
  11. An example execution plan

There is more to come.

DateTime Conversions

Or ‘That’s not the date I wanted?

Date time conversions are among the trickiest of conversions in SQL, and the most likely to cause unexpected errors. Mostly, this is because of the variety of formats that dates, when expressed as strings, can be written with. Let’s start with a simple example.

SELECT CAST('20/05/2007' as DATETIME)

Should work?

Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

(more…)