Vue SQL WorkItemsLatestUsed inexistante avec TFS 2017

Avec TFS 2013.5, j’utilisais la vue SQL WorkItemsLatestUsed pour calculer certains indicateurs.

J’ai migré en TFS 2017, et cette vue n’existe plus. J’ai cependant trouvé mon bonheur en utilisant d’autres vues:

  • vw_denorm_WorkItemCoreLatest: contient les données actuelles des work items, mais des champs sont manquants de l’ancienne vue sont manquants
  • vw_denorm_WorkItemCustomLatest: contient les données actuelles des autres champs (que j’appellerais non standards) des work items, sur le modèle 1 ligne correspond à 1 champ
  • tbl_field: cette table permet de savoir l’id d’un champ non standard

Pour retrouver un équivalent de l’ancienne vue WorkItemsLatestUsed et avoir une table contenant l’ensemble de mes work items, j’utilise donc dorénavant la requête suivante:

SELECT Core.[System.Id] as SystemId
,Core.[System.TeamProject] as SystemTeamProject
,Core.[System.ChangedDate] as SystemChangedDate
,Core.[System.CreatedDate] as SystemCreatedDate
,ClosedDate.DateTimeValue as MicrosoftVSTSCommonClosedDate
,OriginalEstimate.FloatValue as MicrosoftVSTSSchedulingOriginalEstimate
,RemainingWork.FloatValue as MicrosoftVSTSSchedulingRemainingWork
,CompletedWork.FloatValue as MicrosoftVSTSSchedulingCompletedWork
,rtrim(left(Core.[System.AssignedTo], charindex('<',Core.[System.AssignedTo])-1)) as SystemAssignedTo
-- Core.[System.AssignedTo] has now some data in an additional "<...>"
,Core.[System.WorkItemType] as SystemWorkItemType
,Title.TextValue as SystemTitle
,Core.[System.State] as SystemState
FROM [dbo].[vw_denorm_WorkItemCoreLatest] Core
left join [dbo].[vw_denorm_WorkItemCustomLatest] OriginalEstimate on Core.[System.Id] = OriginalEstimate.Id and OriginalEstimate.FieldId = 10021
left join [dbo].[vw_denorm_WorkItemCustomLatest] RemainingWork on Core.[System.Id] = RemainingWork.Id and RemainingWork.FieldId = 10020
left join [dbo].[vw_denorm_WorkItemCustomLatest] CompletedWork on Core.[System.Id] = CompletedWork.Id and CompletedWork.FieldId = 10022
left join [dbo].[vw_denorm_WorkItemCustomLatest] ClosedDate on Core.[System.Id] = ClosedDate.Id and ClosedDate.FieldId = 10009
left join [dbo].[vw_denorm_WorkItemCustomLatest] Title on Core.[System.Id] = Title.Id and Title.FieldId = 1
where Core.[IsDeleted] = 0

Ancien Wiki / SQL Server

Shortcuts

Alt + F1 on a table name : shows table definition

Changer le recovery mode d’une base

Pour passer d’un mode « full » à « simple », la requête à utiliser est la suivante :

ALTER DATABASE <nom_base_de_données> SET RECOVERY SIMPLE
GO

Effectuer une pause dans une requête SQL

Pour faire une pause dans une procédure stockée (équivalent Thread.Sleep) ou une grosse suite de requêtes SQL Server, il y a l’instruction suivante :

waitfor delay ’0:0:5′

Voir toutes les options de SQL Server

exec sp_dboption

Voir la valeur du paramètre « trunc log »

exec sp_dboption 'Database_Name', 'trunc. log on chkpt.'

Change collation

1> use pubs
2> SELECT serverproperty('collation')
3> go

ALTER DATABASE [My_Database]
COLLATE My_Collation

USE Tempdb
GO
ALTER DATABASE Pubs COLLATE French_CI_AS
GO

Supprimer manuellement une réplication

Voir l’article Comment supprimer manuellement une réplication dans SQL Server 2000 ou SQL Server 2005

Remove standalone subscription

exec sp_subscription_cleanup @publisher = 'SERVERNAME', @publisher_db = 'DATABASENAME'

WARNING: to perform in the concerned database

Tuning with nolock

Information récupérée:

For a low transactional system or a system where one user will always just « touch » his « data », the rule of thumb is to always use with (nolock) with the select statements.

I am a SQL Server/DB2 DBA since 2000 and before that I was an Oracle/DB2 DBA for 7 years and we always minimized the locking of the select statements. Lock escalation is quite costly sometimes.

If dirty reads is not an issue always use with (no lock). In doubt, think about the impacts and use it where it is needed.

One other thing we do to improve the overall performance of our code: All our applications are using stored procedures to read/update/delete/insert into our databases. There are pros and there are cons at that, but it is not the point. At the beginning of each stored procedure, we are using set nocount on. The more queries you have, the most benefits you gain in load gain. It is especially important if you call one stored procedure 20,000 times per minute and it has 15 queries in it.

Implementing both with (nolock) and set nocount on reduced our CPU load by 25% for the latest example.

Thank you

Totally agree with the SET NOCOUNT tip. We had (still have) horribly inefficient overnight batch processes which took anything from 1 – 2.5 hours to complete. This is because they use nested cursors (Yeuk!) and lots of temporary tables and intermediate queries. By simply setting NOCOUNT ON we reduced the execution time down to 12 – 15 minutes!

They still lock the crap out of everything, though, which is why we have to run them overnight (you should see the chaos they cause if they’re ever run during the day!!). Sadly, these are NOT candidates for using NOLOCK (need to be transactionally consistent as they’re financial salesledger batches).

Voir Improving SQL Server Performance

Management Studio : saving changes is not permitted

Error:

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created...

Do

To change this option, on the Tools menu, click Options, expand Designers, and then click Table and Database Designers. Select or clear the Prevent saving changes that require the table to be re-created check box.

Divers liens