Okay so I am a TOTAL novice at doing this. Well, even worse than that. I know even less than a novice that has only just arrived at the first class to discover that he’s actually in a lecture about quantum physics.
But, I never give up with trying to learn stuff, and this is very interesting!
First of all there is a MOUNTAIN of stuff on the internet, some of it useful, some of it lacks context, some of it is repetitive (and I’m adding to that last category here I suppose). My experience though is worth sharing, because it’s from the novices perspective.
I have a bit of SQL that runs slowly. It seems all a-okay when there is only a handful of records in the table, but when ths size of the table grows, the performance falls through the floor.
This is the query :
COUNT(*) as VaultCount,
SUM(ArchivedItems) as ArchivedItems,
SUM(CAST(ArchivedItemsSize as decimal(20, 0))) as ArchivedItemsSize
When this runs on a Vault table with millions of rows, then it takes a LONG time to run. What I did was take the query, and run it a number of times — 5 times in fact — then work out the average run time. If you run the query with the option to show the execution plan, you can see which bits of the query take the longest time… I didn’t really understand the output though to be fair, so I won’t go in to the details. The bit I did understand was the the index being used wasn’t efficient it seems.
The SQL 2005 SP 2 tuning wizard suggested the following index to add :
CREATE NONCLUSTERED INDEX
[_dta_index_Vault_6_165575628__K4_5] ON [dbo].[Vault]
INCLUDE ( [ArchivedItemsSize]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY =
OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
After adding that index, I run the same query a number of times — 5 times in fact — this time the average run time is 20 seconds.