Latest Posts

Parameter sniffing

Author: 27 November 2007 4 Comments

This seems to come up again and again on the forums.

At its heart, parameter sniffing is the ability of the SQL Server optimiser to know the values of parameters passed to a stored proc at the point that it compiles the procedure. The idea is that if the parameter values are known, then the appropriate column statistics can be used and the optimiser can estimate the number of rows that the various query operators will have to process for various different possible execution plans.

Since the approximate number of rows is known, the cost of each possible plan can be more accurately calculated and a more accurate execution plan can be selected.

So, why is parameter sniffing so often a problem? Well, mainly, because parameter values do change.

(more…)

Performance, SQL Server

SQL Server 2008 Nov CTP

Author: 20 November 2007 0 Comments

The latest SQL Server 2008 CTP is available on Connect.

Notable features new in this CTP:

  • Intellisense in Management studio. (About bloody time). This was demo’d at PASS back in Sept.
  • Change tracking. What we’ve done all these years with triggers, just without the triggers.
  • The Performance warehouse. This I really want to play with, even if it’s just to see what pieces I can use on 2005 or 2000 servers.
  • Plan freezing. To be able to freeze the plan cache at a point in time, and to move query plans from one server to another. I see this as a fairly advanced feature, probably only really useful in specific cases. (and probably a feature that will be badly abused)
  • Resource Governor. This must be my number one anticipated feature for 2008. The governor allows the creation of limitations and priorities based on properties of the connection, eg login name, application name, host name. It’ll be a very nice way of limiting the damage that ad-hoc queries do to a server’s performance, without stopping them outright.
  • Geospacial data types and functions.
  • The filestream data type

Now I just need to install virtual PC on my laptop and it’s playtime…..

SQL Server

Execution plan operations – scans and seeks

Author: 15 November 2007 2 Comments

Another post in my ongoing series on reading execution plans. I know I’m jumping around a bit. I hope it makes some kind of sense.

I thought I’d quickly go over the seek and scan operations that can be seen in execution plans. There are 6 main ones. There’s a fair bit that I’m glossing over in this. I’ll get into some details at a later date.

Scans

  • Table scan. This operation only appears for a heap (table without a clustered index). The first page in the heap is located based on info in the system tables, and then the pages are read one by one, using the next and, if necessary, previous pointers in the page headers. This is generally an expensive operation and should be avoided where ever possible
  • (more…)

Execution Plans, SQL Server

DateTime Manipulation

Author: 5 November 2007 4 Comments

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

SQL Server, T-SQL

Become a better ….

Author: 31 October 2007 0 Comments

So…

I issued some of my colleagues a challenge this morning.In the vein of the ‘Become a better developer in 6 months’ idea that was going around the net a few months back, I asked them what they were going to do in what’s left of this year to become better DBAs.

Following the idea of ‘make something public and you’ll go through with it’ idea, here are my intentions for the remainder of the year:

  • Finish 2 database books by the end of the year. The two I’m looking at are ‘Guru’s guide to SQL architecture and internals’ and ‘Practical troubleshooting’, The first is written by Ken Henderson, the second is edited by him
  • Write at least 1 blog post a week here. This is the one I’m most likely to have trouble with.
  • Listen to at least 1 SQL related podcast a week. I’m open to suggestions for good SQL-related podcasts.

General

Memory and SQL 2005 SP2

Author: 21 October 2007 4 Comments

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

Admin, SQL Server

Indexes for aggregates

Author: 19 October 2007 0 Comments

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

Indexes, SQL Server

Execution plans from Profiler

Author: 6 October 2007 0 Comments

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.

Execution Plans, SQL Server

Final day

Author: 23 September 2007 0 Comments

By the last day I was feeling like my head was ready to explode. So much information in so little time.

Kevin Kline’s session kicked off the day with a discussion of benchmarking, baselining and monitoring. Some decent ideas, not really any new information for me. That’s probably more because I’d been at just about every monitoring and performance presentation for the entire conference, than any lack on his part.

After lunch the number of attendees seemed to drop substantially. The best of the afternoon sessions that I attended was Kevin Kline’s interview with Ken Henderson. It wasn’t a technical session, more to do with been an author, a respected sql expert.

The flight home is this evening. I’m not really looking forward to that, I only get back to JHB tuesday morning. Not fun at all.

Pass 2007

Copyright 2018 Gail Shaw