Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Q & A from 24 Hours of PASS session

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

0 comments, 346 reads

Posted in SQL In The Wild on 18 March 2011

Full backups, the log chain and the COPY_ONLY option.

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

1 comments, 550 reads

Posted in SQL In The Wild on 8 March 2011

And now for a completely inappropriate use of SQL Server

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

3 comments, 716 reads

Posted in SQL In The Wild on 22 February 2011

Are int joins faster than string joins?

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

1 comments, 191 reads

Posted in SQL In The Wild on 15 February 2011

Quest Webcast: Dos and Don’ts of Database corruption

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

0 comments, 185 reads

Posted in SQL In The Wild on 14 February 2011

Is a clustered index best for range queries?

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

0 comments, 347 reads

Posted in SQL In The Wild on 1 February 2011

Upcoming presentations

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.

Read more

0 comments, 165 reads

Posted in SQL In The Wild on 28 January 2011

Distincting an IN subquery

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

7 comments, 1,442 reads

Posted in SQL In The Wild on 18 January 2011

Capturing the Execution Plan

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

3 comments, 568 reads

Posted in SQL In The Wild on 4 January 2011

Do IF statements cause recompiles?

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

11 comments, 636 reads

Posted in SQL In The Wild on 14 December 2010

Recompiles

I’ve looked at cache hit and miss events and at the cache insert and remove events. The only cache-monitoring event (of current interest) left is the recompile event.

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

0 comments, 823 reads

Posted in SQL In The Wild on 18 November 2010

First Impressions

SELECT @@Version

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

1 comments, 503 reads

Posted in SQL In The Wild on 9 November 2010

A Trio of Table Variables

So, it’s the second Tuesday of the month again, and it’s time for T-SQL Tuesday again. This month it’s hosted by Sankar Reddy and the topic is “Misconceptions in SQL Server

I thought I’d tackle a trio of table variable myths and partial truths.

Table Variables are memory-only

Read more

1 comments, 485 reads

Posted in SQL In The Wild on 12 October 2010

One wide index or multiple narrow indexes?

Or “If one index is good, surely many indexes (indexes? indices? indi?) will be better

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…

Read more

0 comments, 556 reads

Posted in SQL In The Wild on 14 September 2010

Come and gone

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

0 comments, 619 reads

Posted in SQL In The Wild on 31 August 2010

Hit and miss

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:

  • SP:CacheMiss
  • SP:CacheInsert
  • SP:CacheHit
  • SP:CacheRemove
  • SP:Recompile
  • SP:StmtRecompile

Additionally there’s the SQL:StmtRecompile event… Read more

1 comments, 295 reads

Posted in SQL In The Wild on 27 July 2010

Does a missing data file send a database suspect?

Short answer: No

I keep seeing this come up on various forums when people ask what makes a database go suspect or what could have caused their database to be marked suspect. I can almost guarantee when that question comes up, one or more people will answer ‘missing files’. That… Read more

1 comments, 936 reads

Posted in SQL In The Wild on 29 June 2010

DBA Hell

On the first day of DBA hell, the server gave to me
A database with damaged system tables and no good backups (1)

On the second day of DBA hell, the server gave to me
Two databases with widespread corruption, no backups (1, 2)

On… Read more

4 comments, 305 reads

Posted in SQL In The Wild on 15 June 2010

In, Exists and join – a roundup

Over the last several months I’ve had a look at IN, Exists, Join and their opposites to see how they perform and whether there’s any truth in the advice that is often seen on forums and blogs advocating replacing one with the other.

Previous parts of this series can be… Read more

1 comments, 497 reads

Posted in SQL In The Wild on 27 April 2010

Left outer join vs NOT EXISTS

And to wrap up the miniseries on IN, EXISTS and JOIN, a look at NOT EXISTS and LEFT OUTER JOIN for finding non-matching rows.

For previous parts, see

I’m looking at NOT EXISTS and LEFT OUTER JOIN, as… Read more

14 comments, 3,879 reads

Posted in SQL In The Wild on 23 March 2010

Newer posts

Older posts