I have been working on this deadlock issue for quite a few days now and no matter what I do, it persists in one way or another. First, the general premise: We have Visits with VisitItems in a one to many relationship. VisitItems relevant info: ``` CREATE TABLE [BAR].[VisitItems] ( [Id] INT IDENTITY (1, 1) NOT NULL, [VisitType] INT NOT NULL, [FeeRateType] INT NOT NULL, [Amount] DECIMAL (18, 2) NOT NULL, [GST] DECIMAL (18, 2) NOT NULL, [Quantity] INT NOT NULL, [Total] DECIMAL (18, 2) NOT NULL, [ServiceFeeType] INT NOT NULL, [ServiceText] NVARCHAR (200) NULL, [InvoicingProviderId] INT NULL, [FeeItemId] INT NOT NULL, [VisitId] INT NULL, [IsDefault] BIT NOT NULL DEFAULT 0, [SourceVisitItemId] INT NULL, [OverrideCode] INT NOT NULL DEFAULT 0, [InvoiceToCentre] BIT NOT NULL DEFAULT 0, [IsSurchargeItem] BIT NOT NULL DEFAULT 0, CONSTRAINT [PK_BAR.VisitItems] PRIMARY KEY CLUSTERED ([Id] ASC), CONSTRAINT [FK_BAR.VisitItems_BAR.FeeItems_FeeItem_Id] FOREIGN KEY ([FeeItemId]) REFERENCES [BAR].[FeeItems] ([Id]), CONSTRAINT [FK_BAR.VisitItems_BAR.Visits_Visit_Id] FOREIGN KEY ([VisitId]) REFERENCES [BAR].[Visits] ([Id]), CONSTRAINT [FK_BAR.VisitItems_BAR.VisitTypes] FOREIGN KEY ([VisitType]) REFERENCES [BAR].[VisitTypes]([Id]), CONSTRAINT [FK_BAR.VisitItems_BAR.FeeRateTypes] FOREIGN KEY ([FeeRateType]) REFERENCES [BAR].[FeeRateTypes]([Id]), CONSTRAINT [FK_BAR.VisitItems_CMN.Users_Id] FOREIGN KEY (InvoicingProviderId) REFERENCES [CMN].[Users] ([Id]), CONSTRAINT [FK_BAR.VisitItems_BAR.VisitItems_SourceVisitItem_Id] FOREIGN KEY ([SourceVisitItemId]) REFERENCES [BAR].[VisitItems]([Id]), CONSTRAINT [CK_SourceVisitItemId_Not_Equal_Id] CHECK ([SourceVisitItemId] <> [Id]), CONSTRAINT [FK_BAR.VisitItems_BAR

We have some code that pages through a SQL result. (Currently running on SQL Server 2008) We notice that when paging is done, some rows are not returned. Let me clarify this a bit: nHibernate generates SQL queries. We are doing paging. If we page by 100, the way nHibernate generates the successive SQL queries is: - TOP 100 // gives us first 100 - TOP 200 // gives us 2nd 100 of this block - etc The above, without an ORDER BY / sorting at nHibernate level, has the end result that some rows never surface to nHibernate. We surmise this is due arbitrary sorting by SQL, so that rows are "moving around" within the pages (and thus "hide" from our application code). If we do the nHibnerate query as a single shot (returning all rows), we see all the data. (This query below is generated by nhibernate.) Will adding an order by clause (aka nHibernate sorting) help? ``` SELECT top 33 ... FROM salesOrder this_ left outer join [Item] Item2_ on this_.ItemId=Item2_.ItemId WHERE this_.AccountId = @p0 and this_.ModifiedAt > @p1 and this_.ModifiedAt <= @p2 ```

We are using `nHibernate` which generates queries from `.NET` code. Occasionally, some queries get stuck in the `sp_whoisactive` list and we cannot find out why. I have a feeling that it has to do with a missing or broken parameter. When I run `sp_whoisactive`, however, I only see the parameter name (in example: `@p0`), not the actual value. Is there a way to retrieve this information while the process is still running? Example: ``` SELECT this_.Id as Id6_2_, this_.HRNumber as HRNumber6_2_, this_.FirstName as FirstName6_2_, this_.LastName as LastName6_2_, this_.StatusTypeID as StatusTy5_6_2_, this_.PropertyId as PropertyId6_2_, this_.DepartmentGroupId as Departme7_6_2_, property2_.PropertyID as PropertyID10_0_, property2_.PropertyCode as Property2_10_0_, property2_.LMSCode as LMSCode10_0_, property2_.PropertyName as Property4_10_0_, property2_.Report as Report10_0_, department3_.Id as Id4_1_, department3_.Name as Name4_1_, department3_.DisplayName as DisplayN3_4_1_ FROM [dbo].[EmployeeDepartmentGroupView] this_ LEFT OUTER JOIN [dbo].[Property] property2_ ON this_.PropertyId=property2_.PropertyID LEFT OUTER JOIN [dbo].[DepartmentGroup] department3_ ON this_.DepartmentGroupId=department3_.Id WHERE this_.HRNumber LIKE @p0; ``` Please note, this is automatically generated SQL from `nHibernate`. Specs: SQL Server 2008r2

Inherited control over a database that has this nasty config. It also has large sections of code generated by NHibernate, including the gneeration of GUIDs before they get to the db so no chance of using NEWSEQUENTIALID() either. Obviously changing some of these sounds like a huge piece of work, but changing the Lookups doesn't sound too painful. I've documented the fragmentation and CL width and NC indexes built from these, optimizer choosing incorrect plans, etc.. but tasked with a workaround i'm coming up a bit short. My current plan is to investigate how much is possible to change and in the interim add an identity field to the main heavily indexed tables and make that the clustered index and keep the GUIDs as PK's. Is this the best I can hope for? or not even worth it? any other workarounds that i have omitted ? Thanks

How can I monitor a production database to see if there are table scans happening? And what indexes might fix them... I'm told that we have adequate indexes but I can't see every code path, and we have too many people touching the codebase (hey, lots of developers can sometimes be a good thing) so what's the most comprehensive way to see how if we've missed indexes based on our production load? SQL Server 2008 R2, C#/ASP.NET code, NHibernate are the key factors in use.