Click here to Skip to main content
14,489,971 members

Bugs and Suggestions

   

General discussions, site bug reports and suggestions about the site.

For general questions check out the CodeProject FAQs. To report spam and abuse Head to the Spam and abuse watch. If you wish to report a bug privately, especially those related to security, please email webmaster@codeproject.com


 
GeneralRe: The "admin messages" in the thread below might explain why "Reputation history" almost never shows any more. Pin
Wendelius4-Feb-20 6:50
mveWendelius4-Feb-20 6:50 
GeneralRe: The "admin messages" in the thread below might explain why "Reputation history" almost never shows any more. Pin
Jörgen Andersson4-Feb-20 8:23
professionalJörgen Andersson4-Feb-20 8:23 
GeneralRe: The "admin messages" in the thread below might explain why "Reputation history" almost never shows any more. Pin
Chris Maunder4-Feb-20 6:57
cofounderChris Maunder4-Feb-20 6:57 
GeneralRe: The "admin messages" in the thread below might explain why "Reputation history" almost never shows any more. Pin
Jörgen Andersson4-Feb-20 11:56
professionalJörgen Andersson4-Feb-20 11:56 
GeneralRe: The "admin messages" in the thread below might explain why "Reputation history" almost never shows any more. Pin
Richard Deeming5-Feb-20 0:38
mveRichard Deeming5-Feb-20 0:38 
GeneralRe: The "admin messages" in the thread below might explain why "Reputation history" almost never shows any more. Pin
Jörgen Andersson5-Feb-20 1:11
professionalJörgen Andersson5-Feb-20 1:11 
GeneralRe: The "admin messages" in the thread below might explain why "Reputation history" almost never shows any more. Pin
Chris Maunder11-Feb-20 7:49
cofounderChris Maunder11-Feb-20 7:49 
GeneralRe: The "admin messages" in the thread below might explain why "Reputation history" almost never shows any more. Pin
Jörgen Andersson11-Feb-20 10:19
professionalJörgen Andersson11-Feb-20 10:19 
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 [Index Uses] DESC
--ORDER BY DatabaseID,SchemaName,TableName
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. Smile | :)
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.
Wrong is evil and must be defeated. - Jeff Ello

GeneralRe: The "admin messages" in the thread below might explain why "Reputation history" almost never shows any more. Pin
W Balboos, GHB11-Feb-20 7:09
mveW Balboos, GHB11-Feb-20 7:09 
GeneralRe: The "admin messages" in the thread below might explain why "Reputation history" almost never shows any more. Pin
Chris Maunder11-Feb-20 7:48
cofounderChris Maunder11-Feb-20 7:48 
GeneralRe: The "admin messages" in the thread below might explain why "Reputation history" almost never shows any more. Pin
Jörgen Andersson11-Feb-20 10:21
professionalJörgen Andersson11-Feb-20 10:21 
QuestionNot sure how I got this... Pin
OriginalGriff1-Feb-20 22:30
mveOriginalGriff1-Feb-20 22:30 
AnswerRe: Not sure how I got this... Pin
Wendelius1-Feb-20 23:18
mveWendelius1-Feb-20 23:18 
GeneralRe: Not sure how I got this... Pin
OriginalGriff1-Feb-20 23:37
mveOriginalGriff1-Feb-20 23:37 
GeneralRe: Not sure how I got this... Pin
Wendelius2-Feb-20 0:11
mveWendelius2-Feb-20 0:11 
GeneralRe: Not sure how I got this... Pin
OriginalGriff2-Feb-20 0:47
mveOriginalGriff2-Feb-20 0:47 
AnswerRe: Not sure how I got this... Pin
Peter_in_27802-Feb-20 1:18
professionalPeter_in_27802-Feb-20 1:18 
GeneralRe: Not sure how I got this... Pin
phil.o2-Feb-20 1:26
mvephil.o2-Feb-20 1:26 
AnswerRe: Not sure how I got this... Pin
Nelek2-Feb-20 5:03
protectorNelek2-Feb-20 5:03 
AnswerRe: Not sure how I got this... Pin
Brisingr Aerowing2-Feb-20 14:56
professionalBrisingr Aerowing2-Feb-20 14:56 
AnswerRe: Not sure how I got this... Pin
Jörgen Andersson2-Feb-20 20:53
professionalJörgen Andersson2-Feb-20 20:53 
AnswerRe: Not sure how I got this... Pin
Matthew Dennis3-Feb-20 9:53
sysadminMatthew Dennis3-Feb-20 9:53 
BugPre tags not working in Visual Basic forum Pin
CHill6030-Jan-20 2:31
mveCHill6030-Jan-20 2:31 
GeneralRe: Pre tags not working in Visual Basic forum Pin
Chris Maunder30-Jan-20 7:16
cofounderChris Maunder30-Jan-20 7:16 
GeneralRe: Pre tags not working in Visual Basic forum Pin
CHill602-Feb-20 21:48
mveCHill602-Feb-20 21:48 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Flags: FixedSolved

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.