|
Yes, and when you add more rows the number of levels grows logarithmically with the number of rows, and therefore also the seek time.
You very quickly reach three levels in an index, while more than six almost doesn't exist.
|
|
|
|
|
We have indexes. Lots of indexes. The "lots of" has been an issue we've been attacking and have removed a number that are simply slowing down the writes.
We've made some changes that have resulted in better execution plans but we're still not there. We're now starting to remove some logging calls that, while useful, aren't critical, in the hope that this reduces load enough that we can see the root cause of this particular issue.
The funny thing is that load is actually quite low on our DB. It's just a few sprocs that, in certain circumstances, take a lot of time. Run them in isolation and they are instant. Run them in the debugger and they are instant. Run them on your crappy laptop and they are instant. Run them live during a quiet part of the day when nothing strange is happening and they misbehave. So we look at stats, and recompilation, and plans, and load, and locks and everything is fine. Except it's not always.
If software development is magic then database tuning is voodoo.
cheers
Chris Maunder
|
|
|
|
|
I have a feeling you're not using SQL Server any more, otherwise I'd recommend sp_BlitzIndex®[^] by Brent Ozar. It will list overlapping indexes amongst other things. I also have a nifty little query that will list missing indexes for you.
Anyway, the thing is, this particular timeout isn't happening just sometimes, it happens basically all the time for users with high enough reputation. Which implies that it is very dependent on the number of rows in the query.
Normally this would be a query similar to
SELECT TOP 100 Columns FROM Reputation WHERE mid = 2624750 ORDER BY DateTimeAdded DESC which would easily be handled by a RangeScan on the index(mid,DateTimeAdded DESC). (Preferably clustered in this case)
But you know that already, so there's something else going on here.
Have to admit I'm curious.
|
|
|
|
|
Jörgen Andersson wrote: Preferably clustered in this case
Which would almost certainly kill the insert performance. 
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Well, the index exists anyway.
And if I recall correctly, (I haven't seen the reputation history for a while), there isn't very much more data in it.
But, while writing this I realize a possible reason to why this is slow. And then the index obviously can't be clustered, because this is not a table of it's own.
<edit>that's the bad part of making assumptions on something you don't have any knowledge about. </edit>
|
|
|
|
|
We actually do use SQL and have run the Brent script and have our laundry list.
Happy to see your script too!
Current theory: too many indexes which are slowing down writes (and causing locks) which is killing reads.
cheers
Chris Maunder
|
|
|
|
|
In my experience, the indexes that really slows down writes are Indexed Views, they can be really nasty when having a few joins in them.
Anyway, if you believe it's write locks that is the culprit you can easily test that by adding the hint WITH (READ UNCOMMITED) on the Query.
And if it works you need to make a decision on how important it is, that there are no dirty reads in the Reputation Points list.
I'm not generally a proponent of changing the default behaviours of a database, but I really hate don't like the pessimistic locking of SQL Server.
I learned SQL on Oracle which has optimistic locking, so it only have write locks while commiting (well, not entirely true, you can always SELECT FOR UPDATE), and it doesn't have any read locks at all.
It's really hard to get used to the crappy multi user performance on SQL Server.
Anyway, here's a query to find Missing indexes:
SELECT migs.user_seeks
,migs.user_scans
,migs.unique_compiles
,migs.avg_user_impact [Index Impact %]
,migs.avg_total_user_cost [Avg Query Cost]
,db_name(mid.database_id) AS DatabaseName
,OBJECT_SCHEMA_NAME(mid.OBJECT_ID, mid.database_id) AS SchemaName
,OBJECT_NAME(mid.OBJECT_ID, mid.database_id) AS TableName
,mid.equality_columns
,mid.inequality_columns
,mid.included_columns
,'CREATE INDEX [' + OBJECT_NAME(mid.OBJECT_ID, mid.database_id) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns, ''), ', ', '_'), '[', ''), ']', '')
+ CASE
WHEN mid.equality_columns IS NOT NULL
AND mid.inequality_columns IS NOT NULL
THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(mid.inequality_columns, ''), ', ', '_'), '[', ''), ']', '') + '_IX]'
+ ' ON ' + mid.statement + ' (' + ISNULL(mid.equality_columns, '')
+ CASE
WHEN mid.equality_columns IS NOT NULL
AND mid.inequality_columns IS NOT NULL
THEN ','
ELSE ''
END
+ ISNULL(mid.inequality_columns, '') + ')' + ISNULL(' INCLUDE (' + mid.included_columns + ')', '') + ';' AS DDL
FROM sys.dm_db_missing_index_group_stats AS migs
JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle
WHERE migs.last_user_seek > DATEADD(MM,-1,GETDATE())
ORDER BY migs.user_seeks * migs.avg_total_user_cost * migs.avg_user_impact DESCThis query only shows the last month, anything older is usually of less importance in my experience.
If you have as many compiles as user_seeks you should really consider using parameters.
Don't use the suggested indexes out of the box. There are often several similar indexes suggested that probably can be covered by one slightly less optimized one.
Also the order of the columns is just a suggestion, consider changing them at least within the groups. The optimizer has a tendency to favour indexes with skewed statistics. I'm not very convinced that's always the best.
And the last caveat, this is a list of indexes that the optimizer believes it's missing. The intelligence of the optimizer is on the level of a badger with alzheimers. So it's not a complete list.
And finally, the best tool I've used for examining execution plans is SentryOne Plan Explorer - A Free SQL Server Query Tuning Tool[^]
Yes it is free, it only has a start page plugging their paid products.
|
|
|
|
|
An alternative (or adjunct) to indices:
Split the data into active (a month or less) and the rest.
A query, by default, accesses only the active table. If one is paging through data, historically, then at some point it could cross over - as a total break with the original table or with a UNION.
A weekly or monthly CRON job can keep the active table trim.
| Ravings en masse^ |
|---|
| "The difference between genius and stupidity is that genius has its limits." - Albert Einstein | | "If you are searching for perfection in others, then you seek disappointment. If you seek perfection in yourself, then you will find failure." - Balboos HaGadol Mar 2010 |
|
|
|
|
|
Horizontal partitioning.
Shouldn't actually be necessary, but we'll look at all options.
cheers
Chris Maunder
|
|
|
|
|
Or just using a filtered index.
|
|
|
|
|
I suspect it was when Dij the Cat took a stroll over the keyboard.
Either that or I suddenly have Admin Powers due to a cockup somewhere ... please let me know how I turn it off as it persists over a refresh, and is appearing on all pages now:
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
|
Not the cat then... 
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
|
Currently, they are limited to "making tissues very soggy" and "covering things in a layer of mucus" but I'm hoping that isn't their final form.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
I'm getting it too.  Just scrolled down far enough to see it.
Software rusts. Simon Stephenson, ca 1994. So does this signature. me, 2012
|
|
|
|
|
Same here. Today may be hamsters' open house.
"Five fruits and vegetables a day? What a joke!
Personally, after the third watermelon, I'm full."
|
|
|
|
|
+1
me too
M.D.V. 
If something has a solution... Why do we have to worry about?. If it has no solution... For what reason do we have to worry about?
Help me to understand what I'm saying, and I'll explain it better to you
Rating helpful answers is nice, but saying thanks can be even nicer.
|
|
|
|
|
Seeing it as well.
What do you get when you cross a joke with a rhetorical question?
The metaphorical solid rear-end expulsions have impacted the metaphorical motorized bladed rotating air movement mechanism.
Do questions with multiple question marks annoy you???
|
|
|
|
|
Bah, I'm not trusted. 
|
|
|
|
|
Oops! I set a configuration switch to the wrong value.
My bad.
Chris found it and fixed it.
Thanks for the heads up.
"Time flies like an arrow. Fruit flies like a banana."
|
|
|
|
|
|
The "Treat my content as plain text, not as HTML" checkbox was checked
cheers
Chris Maunder
|
|
|
|
|
D'oh 
|
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
