SQL 2008 and DMVStats

Sep 4, 2008 at 3:18 AM

I tried to install DMVStats on a X64 machine w/ SQL Server 2008 on it.
The install fails as the the [master].[sys].[dm_os_wait_stats] contains 2 apparently identical entries w/ the ‘wait_type’ column == ‘MISCELLANEOUS’ and this causes a primary key constraint violation for PK_CF_wait_stats_cats on the DMVConfig.wait_stats_categories.

Is this a known issue? Are there any workarounds to it?

Any related references/pointers are appreciated.

Thank you,
    Adrian C.
Apr 29, 2009 at 10:42 PM
I was able to correct the Insert-select by trimming spaces from wait_type.  However, the sampling job fails because of PK violations and out of wack transcounts.  I gave up on this for SQL2008 and will probably used MDW assuming it works on my x64 servers.  I've had all sorts of trouble with x64 behavior versus 32-bit but I don't forsee an issue here since it's using standard SQL mechanisms like data collectors, jobs, ssis, etc.

Executed as user: AMI\Tphillippe. Sample DMV collection: time=Apr 29 2009  4:18:41:147PM [SQLSTATE 01000] (Message 50000)  Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0. [SQLSTATE 25000] (Error 266)  procid=1234103437 procname=sp_SampleRequests_Waiters @rows=1 @duration=3 milliseconds [SQLSTATE 01000] (Error 50000)  procid=1330103779 procname=sp_SampleBlockInfo @rows=0 @duration=6 milliseconds [SQLSTATE 01000] (Error 50000)  catch procid=1266103551, procname=(null), trancount=1 [SQLSTATE 01000] (Error 50000)  sp_InsertErrorInfo: procid=1266103551 name=sp_SampleWaitStats RetCode=0 errno=2627 errsev=14 errstate=1 errproc=sp_SampleWaitStats errline=26 errmsg=Error %d, Level %d, State %d, Procedure %s, Line %d, Message: Violation of PRIMARY KEY constraint 'PK__wait_sta__D0CDAB226B24EA82'. Cannot insert duplicate key in object 'DMVsample.wait_stats'. [SQLSTATE 01000] (Error 50000).  The step failed.
Aug 11, 2010 at 10:52 PM


I got it running and data matches the MDW result, I had to modify at two location



2. cONFIGdmvsTATSdb.SQL installation script to Exclude "Wait Type" Miscellaneous from the query to sys.sm_os_wait_stats

It works fine for our installation now.