Welcome to Aussie SQL Server Bloggers Sign in | Join | Help

SQL Server - Technical (RSS)

SQL Server - Technical
Sunday, 27 April 2008 9:05 PM

Which query is scanning that index?

SQL 2005's db_dm_index_usage_stats DMV allows DBAs to analyse how often indexes are being used (or not used), including individual counts for seeks, scans & lookups on each index in your database. I was thrilled when I first saw this DMV as there
posted by Greg_Linwood | 4 Comments
Filed Under: ,
Friday, 18 April 2008 1:04 AM

update to usp_helpindexusagestats

Here's an update to the utility SP [usp_helpindexusagestats] I posted a few weeks back, in response to a comment from Mitch Wheat that he was receiving empty result sets, which could have occurred if the original version was run against a system with
posted by Greg_Linwood | 0 Comments
Filed Under: ,
Friday, 30 March 2007 11:31 PM

Design better indexes rather than re-write your queries

I seem to be having the same conversation over & over again recently with developers who believe that re-writing queries is the best way to improve performance. Maybe I'm just obsessing about this topic a little at the moment, but I think the
posted by Greg_Linwood | 0 Comments
Filed Under:
Tuesday, 19 December 2006 11:55 PM

Why the Relational Model is Insufficient (part 1)

Paul Nielsen has pointed out a couple of interesting deficiencies in the relational model on his blog: http://sqlblog.com/blogs/paul_nielsen/archive/2006/12/18/430.aspx
posted by Greg_Linwood | 0 Comments
Filed Under:
Saturday, 16 December 2006 10:46 AM

List of KBs on SQL Server DB engine IO by Bob Dorr

Here's a useful list of IO KBs, put together by Microsoft PSS Escalation Engineer Bob Dorr http://blogs.msdn.com/psssql/archive/2006/11/27/what-do-i-need-to-know-about-sql-server-database-engine-i-o.aspx
posted by Greg_Linwood | 0 Comments
Filed Under:
Monday, 11 December 2006 11:20 PM

Using Reads, CPU, Duration or Writes for query tuning with SQL Trace

Whilst high level tools such as Windows Perfmon can give you an overview of server performance and Waitstats can provide you with an insight into where bottlenecks are occuring within the database engine, seeking out the source of most SQL Server performance
posted by Greg_Linwood | 2 Comments
Filed Under:
Friday, 1 December 2006 6:13 AM

More on TPC-C & Clustered Indexes

Prime & Oracle published another TPC-C benchmark this morning, achieving 1.2M transactions per minute @ $3.94 tpmC. This pushes HP & SQL Server's top result down to 4th & leaves SQL Server with only one result in the top 10 TPC-C benchmarks.
posted by Greg_Linwood | 0 Comments
Filed Under:
Wednesday, 29 November 2006 11:56 PM

Why defragment tables & indexes at the same time?

Virtually every DBA I talk to about index maintenance seems to defrag tables & indexes together. Here are a few thoughts about why this isn't a good idea.. (a) If overall database performance improves radically after rebuilding your tables, you've
posted by Greg_Linwood | 0 Comments
Filed Under:
Thursday, 23 November 2006 11:40 PM

You won't solve SQL performance problems by reading Execution Plans alone

One of the first things that DBAs tend to do when investigating a query performance tuning problem is to check out the query's execution plan, preferably via the graphical showplan in QA or SSMS. Sure, this tells you how the query is currently
posted by Greg_Linwood | 2 Comments
Filed Under:
Monday, 25 September 2006 10:54 PM

Debunking myths about clustered indexes - part 4 (CIXs, TPC-C & Oracle clusters)

This is the fourth blog post in a series I am currently writing in an attempt to de-bunk the myth that "clustered indexes (CIXs) should be created on every table". I'm going to take a moment here to clarify that I'm NOT saying CIXs
posted by Greg_Linwood | 7 Comments
Filed Under:
Wednesday, 20 September 2006 12:01 AM

A good article on trouble-shooting deadlocks

Mitch Wheat pointed out a good article on his blog recently on how to troubleshoot deadlocks, written by Microsoft PSS escalation engineer Bart Duncan. This article is definitely worth a read, but two observations I'd make about this article are:
posted by Greg_Linwood | 1 Comments
Filed Under:
Saturday, 16 September 2006 11:50 AM

Debunking myths about clustered indexes - part 3 (example script)

So far in this series of posts, I have discussed how clustered indexes (CIXs) can be less efficient for both bookmark lookups & range scans than non-clustered indexes (NCIXs). In this post, I'm providing a repro script that demonstrates these two
posted by Greg_Linwood | 2 Comments
Filed Under:
Monday, 11 September 2006 8:34 PM

Debunking myths about clustered indexes - part 2 (CIXs are NOT better for range scans)

In my previous post on this topic, I discussed the ineffiency associated with performing bookmark lookups between Non-Clustered Indexes (NCIXs) and Clustered Indexes (CIXs), as opposed to when those same lookups are made from NCIXs to HEAP storage. Although
posted by Greg_Linwood | 2 Comments
Filed Under:
Sunday, 10 September 2006 12:25 AM

Debunking myths about clustered indexes - part 1 (CIXs worsen bookmark lookups)

One of the most widely published indexing "best practises" is that you should create a clustered index (CIX) on every table in your database. For many years, I accepted this as one of the "golden indexing rules" but over the past few years I have
posted by Greg_Linwood | 3 Comments
Filed Under:
Friday, 25 August 2006 8:14 PM

SELECT * & query / index matching

I spent a few minutes in Microsoft's public discussion forums today & noticed a post which highlighted a very common misconception amongst developers about indexes. The poster presented a question along these lines: I have a table with 4 columns:
posted by Greg_Linwood | 2 Comments
Filed Under:
More Posts Next page »