All of the indexing strategy posts I’ve written in the past have been concerned with predicates combined with ANDs. That’s only one half of the possibilities though. There’s the case of predicates combines with ORs, and the guidelines for indexing that work well with ANDs don’t work with ORs
When… Read more
From Tom LaRock’s latest idea…
For a change I’m not going to mention performance (well, not more than once anyway) and I’m not going to preach backups or recoverability. Nor am I going to talk about technical issues like many others are doing.
Instead I’m going to briefly mention… Read more
Earlier this year I had a look at a query pattern that I often see on forums and in production code, that of the Distinct within an IN subquery. Today I’m going to look at a similar patters, that being the use of TOP 1 within an EXISTS subquery.
Three… Read more
SQL’s auto-updating statistics go a fair way to making SQL Server a self-tuning database engine and in many cases they do a reasonably good job
However there’s one place where the statistics’ auto-update fails often and badly. That’s on the large table with an ascending column where the common queries… Read more
Earlier this week I took part in the 24 Hours of PASS live webcast event. There were far more questions at the end of the presentation than what I could answer online, so the answers to the rest are given here. (I have edited some of the questions for spelling,… Read more
There have been a large number of posts made regarding the interaction between full database backups and the log chain (like this one). However there still seems to be a lot of confusion around regarding how they interact. I doubt I can clear up the confusion with yet… Read more
A while back I wrote up a short introductory overview of Genetic Algorithms. Just for the shear, absolute fun of it, I thought I’d implement a basic genetic algorithm within SQL Server and use it to solve a form of the knapsack problem.
Now first a few comments… Read more
This one comes up a lot on the forums, often as advice given…
“You should use integers for the key rather than strings. The joins will be faster.”
It sounds logical. Surely integers are easier to compare than large complex strings. Question is, is it true?
This is going to… Read more
I was guest presenter on last week’s Quest Pain of the Week presentation. The presentation is available for download from Quest. This is a roundup of the questions from the presentation. (I have edited some of the questions for spelling, grammar and readability)
Q: Why do you have to wait… Read more
I see a lot of advice that talks about the clustered index been the best index for use for range queries, that is queries with inequalities filters, queries that retrieve ranges of rows, as opposed to singleton queries, queries that retrieve single rows (including, unfortunately, a Technet article).
I… Read more
It’s looking like it’s going to be a busy year.
It’s only January and I already have three presentations lined up for the first quarter of the year.
- On the 10th February I’m presenting on database corruption for Quest’s Pain of the Week webcast. I’m not Paul Randal,…
This is going to be a quick one…
I keep seeing forum code (and production code) that includes the DISTINCT in IN or EXISTS subqueries. The rationale is given either as a performance enhancement or as necessary for correct results.
Is it necessary or useful? Only one way to find… Read more
One last post on execution plans and Profiler (at least for now)
When trying to check a query’s execution plan, the display execution plan option of Management Studio is usually adequate, however there are occasions where it’s either not feasible to run the query from Management Studio or the particular… Read more
I heard this one over at SSC a while back. “Avoid IF statements in stored procedures as they result in recompiles”
Ok, it sounds reasonable, if the optimiser optimises for the execution path taken on first execution it’ll have to go back and optimise the other paths when they are… Read more
There are two recompile events available in Profiler in SQL 2008
- SP:Recompile under Stored Procedures
- SQL:StmtRecompile under T-SQL
Which to use when? Read more
Microsoft SQL Server “Denali” (CTP1) – 11.0.1103.9 (X64)
I’ve been playing with the CTP for a few days (with absolutely no documentation) and there are a few fun and interesting things that I found. These may not be earth-shattering changes, but they are interesting, and I don’t doubt… Read more
I thought I’d tackle a trio of table variable myths and partial truths.
Table Variables are memory-only
This is a question that comes up very often on the forums. Something along the lines of:
I have a query with multiple where clause conditions on a table. Should I create one…
Or “Plan cache monitoring – insert and remove”
Previously I took a look at the CacheHit and CacheMiss events to see how they behave and how to identify what’s been searched for in the cache. in this follow up, I want to take a similar look at the… Read more
Or “Monitoring plan cache usage”
For people interested in the details of how SQL is using and reusing execution plans, there are some useful events in profiler for watching this in detail, under the Stored procedure group:
Additionally there’s the SQL:StmtRecompile event… Read more