|
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 DESC This 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
|
|
|
|
|
|
I did not The guy is also from Typemock it seems so he is probably the original author.
Thanks,
Sean Ewington
CodeProject
|
|
|
|
|
Throw it on the bug pile then!
I figured he was, but there's nothing there or in his profile that confirms it - so it does need a reference to the original or someone is going to just assume it's stolen.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
Member 14727408 somehow managed to create an account without an account name.
Result: in the who's who page, there is no username for this entry, thus it is impossible to link to his account page. It can be found between Member 14727407 and 14727409, though.
"Five fruits and vegetables a day? What a joke!
Personally, after the third watermelon, I'm full."
modified 27-Jan-20 6:42am.
|
|
|
|
|
|
Problem only arises on who's who pages:
Who's Who at CodeProject[^]
"Five fruits and vegetables a day? What a joke!
Personally, after the third watermelon, I'm full."
|
|
|
|
|
Some of these CBD posts are lines with a URL 1500 characters long, which means we have to scroll massively to the right just to see the "Accept / Reject" buttons.
And that increases the chances of a "Oops moment", I guess.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|