SQL DMVStats

New Post: DMVStats report very slow

kdhakal — Tue, 17 Jun 2008 16:30:11 GMT

Hi! I installed DMVstats on my Dev enviornment and collected data for couple of weeks and created 2 baseline (each baseline has only 1 day worth of data). Now, when I try to query top IO query, it just takes long time (3-5 minutes) to give the result. And the result set also has thousands of duplicate records. When I ask for give me top IO query, I expect to see distinct sql statements and their average execution times and number of execution. I don't want to see 1000s lines of same sql statements even if they were actually executed that many times.
Has anyone else run into this problem?

And by the way this is a great free tool and love to put it on production, if the problem mentioned above can be fixed.

thanks,
kush

NEW POST: Question on DMVstats

shivakonduru — Tue, 20 May 2008 15:18:34 GMT

Hi,

I have the following questions on DMVStats:

1. If I configure DMVstats to collect data only for a specific database, will it still continue to collect data for all the databases?

Reason: When I look for the data related to different waits(Like I/O etc), it is showing the data even for the DMVStats database which is not added to the list of configured databases.

2. When I look at the resource waits graph, I see that there was lot of waiting for service broker. There is nothing on sql server which makes use of service broker.

Please advice. Your help is highly appreciated.

-Shiva

CREATED ISSUE: DeployReports Errors

Scoop — Wed, 14 May 2008 13:54:08 GMT

We are using SSMS 2005 SSAS, SSRS, SSIS, ProCLarity and SharePoint 3.0 and we deploy all reports to SHarePoint 3.0 for integration.

We are using the DeployReports.cmd and trying to use the following URL to deploy reports to SharePoint due to we are using SharePoint 3.0 Integration not reporting services and we receive the error below at botton of this description and we really need help not personel but help just with this issue. Thank you Scoop.

http://owa2k3dwdev02:16216/sites/PPS/Report

We deploy reports through BIDS using the following URL's

http://owa2k3dwdev02:16216/sites/PPS/Sources
http://owa2k3dwdev02:16216/sites/PPS/Report
http://owa2k3dwdev02:16216/sites/PPS

********************************************************************
This script will deploy DMVstats reports to the
SQL Server Reporting Services instance at
http://owa2k3dwdev02:16216/sites/PPS/Report

Press any key to continue or CTRL-C to abort ....
********************************************************************
Beginning report deployment...
System.Exception: Could not connect to server: http://owa2k3dwdev02:16216/sites/
PPS/Report/ReportService2005.asmx ---> System.InvalidOperationException: Client
found response content type of 'text/html; charset=utf-8', but expected 'text/xm
l'.
The request failed with the error message:
--
<HTML dir="ltr">
<HEAD><meta name="GENERATOR" content="Microsoft SharePoint" /><meta name="progid
" content="SharePoint.WebPartPage.Document" /><meta HTTP-EQUIV="Content-Type" co
ntent="text/html; charset=utf-8" /><meta HTTP-EQUIV="Expires" content="0" /><met
a name="ROBOTS" content="NOHTMLINDEX" /><title>

COMMENTED ISSUE: System.Exception: Could not connect to server

Scoop — Tue, 13 May 2008 18:09:17 GMT

Hi!

I am getting an error when I try to install the reports. Im not that familar with the sql reporting services so it could be a very basic error.
Or could it be that I am on a 64-bit machine?

Anyway, what I get is pasted below when I try to run DeployReports.cmd.

I have tried to change the loclahost to our servername but still same error. IIS is up and running. Any hints on troubleshooting?

********************************************************************
This script will deploy DMVstats reports to the
SQL Server Reporting Services instance at
http://localhost/reportserver

Press any key to continue or CTRL-C to abort ....
********************************************************************
Beginning report deployment...
System.Exception: Could not connect to server: http://localhost/reportserver/ReportService2005.asmx ---> System.Net.WebE
xception: The request failed with HTTP status 404: Not Found.
at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response,
Stream responseStream, Boolean asyncCall)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at Microsoft.SqlServer.ReportingServices2005.ReportingService2005.ListSecureMethods()
at Microsoft.ReportingServices.ScriptHost.Management2005Endpoint.PingService(String url, String userName, String pass
word, String domain, Int32 timeout)
at Microsoft.ReportingServices.ScriptHost.ScriptHost.DetermineServerUrlSecurity()
--- End of inner exception stack trace ---
at Microsoft.ReportingServices.ScriptHost.ScriptHost.DetermineServerUrlSecurity()
at Microsoft.ReportingServices.ScriptHost.ScriptHost.InstanceMain()
at Microsoft.ReportingServices.BaseCmdLine.CommandLineMain(String[] args, BaseCmdLine instance)
== An error occurred. See P:\Microsoft\SQLServer\dmvstats 1.01\DeployReports.log for details
Press any key to continue . . .

The DeployReports.log is empty.

Thanks
Ola
Comments: ** Comment from web user: Scoop **

What was the resolution for this issue you were having if you do not mind sharing??

We are trying to Deploy the deployreports.cmd also but to sharepoint 3.0 report server URL and are receiving the same error.

Help anyone??/

CREATED ISSUE: Installation issue

spenumatsa — Thu, 28 Feb 2008 19:16:54 GMT

I would like to know if i can install this application in a Clustered environment.

NEW POST: are the management views actually reliable?

ijeb — Wed, 02 Jan 2008 16:54:52 GMT

I use the DMV sys.dmdbindexusagestats and as far as I see all used indexes are recorded. The DMV's are cleared when you restart the server. So you have to make a snanpshot of sys.dmdbindexusagestats just before you shut down the server.

You also have to keep in mind that maybe not every process runs in 71 days. In this case a longer period would be needed to see all used indexes.

Project License Changed

Fri, 30 Nov 2007 19:26:25 GMT

Microsoft Public License (Ms-PL) This license governs use of the accompanying software. If you use the software, you accept this license. If you do not accept the license, do not use the software. 1. Definitions The terms "reproduce," "reproduction," "derivative works," and "distribution" have the same meaning here as under U.S. copyright law. A "contribution" is the original software, or any additions or changes to the software. A "contributor" is any person that distributes its contribution under this license. "Licensed patents" are a contributor's patent claims that read directly on its contribution. 2. Grant of Rights (A) Copyright Grant- Subject to the terms of this license, including the license conditions and limitations in section 3, each contributor grants you a non-exclusive, worldwide, royalty-free copyright license to reproduce its contribution, prepare derivative works of its contribution, and distribute its contribution or any derivative works that you create. (B) Patent Grant- Subject to the terms of this license, including the license conditions and limitations in section 3, each contributor grants you a non-exclusive, worldwide, royalty-free license under its licensed patents to make, have made, use, sell, offer for sale, import, and/or otherwise dispose of its contribution in the software or derivative works of the contribution in the software. 3. Conditions and Limitations (A) No Trademark License- This license does not grant you rights to use any contributors' name, logo, or trademarks. (B) If you bring a patent claim against any contributor over patents that you claim are infringed by the software, your patent license from such contributor to the software ends automatically. (C) If you distribute any portion of the software, you must retain all copyright, patent, trademark, and attribution notices that are present in the software. (D) If you distribute any portion of the software in source code form, you may do so only under this license by including a complete copy of this license with your distribution. If you distribute any portion of the software in compiled or object code form, you may only do so under a license that complies with this license. (E) The software is licensed "as-is." You bear the risk of using it. The contributors give no express warranties, guarantees or conditions. You may have additional consumer rights under your local laws which this license cannot change. To the extent permitted under your local laws, the contributors exclude the implied warranties of merchantability, fitness for a particular purpose and non-infringement.

NEW POST: What SQL Server versions are supported/required?

alasdaircs — Wed, 21 Nov 2007 14:40:32 GMT

I've got this installed on an RTM (9.0.1399) standard edition server and the snapshot job keeps hanging with 100% of 1 CPU utilised. I note that the SQL Server 2005 Performance Dashboard stipulates the SP2 is required because of both new features and bug fixes.

From the PerfDash.chm:
"The report themselves also utilize new server side functionality added in Service Pack 2, notably sys.dmexectextqueryplan and an extension to the OBJECT_NAME() function to accept an optional dbid parameter, and also rely on several bug fixes related to the data reported in the dynamic management views (DMVs). Consequently, the SQL Server instance being monitored via the dashboard reports must also be running Service Pack 2 or higher."

How about DMVstats?

NEW POST: are the management views actually reliable?

grumpyolddba — Thu, 15 Nov 2007 10:40:39 GMT

I have a question concerning the data held within the views - how reliable is it and what are the criteria for what is displayed - for example sys.dmdbindexusagestats - I've collected data from this view at regualr intervals - my database has around 7000 indexes on tables which have data in the table - over a 71 day period I only have data for just under 1,500 indexes from the view - what about the other 5,500 indexes ? I'm a bit pushed to really know where to direct this type of question - dmvstats and the performance dashboard make extensive use of the dmv's - but are the dmv's actually representative of my database usuage/performance?

CREATED ISSUE: System.Exception: Could not connect to server

OlaCarlander — Mon, 12 Nov 2007 17:51:47 GMT

COMMENTED ISSUE: DMVstats: Insert DMV Samples (Collect Sample DMVs)

sulaak — Fri, 05 Oct 2007 17:04:18 GMT

I pick up an Error when I run the DMVstats: Insert DMV Samples job the Error is listed below on further investigate I notice it's linked to the update counter =1
Error Message:

Step ID 1
Server
Job Name DMVstats: Insert DMV Samples
Step Name Collect Sample DMVs
Duration 00:00:00
Sql Severity 16
Sql Message ID 2560
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: QBEEO\TestSQLServerAgent. ... time=Oct 5 2007 3:00:00:223PM [SQLSTATE 01000] (Message 50000) procid=1522104463 procname=sp_SampleRequests_Waiters @rows=1 @duration=0 milliseconds [SQLSTATE 01000] (Message 50000) procid=1618104805 procname=sp_SampleBlockInfo @rows=0 @duration=173 milliseconds [SQLSTATE 01000] (Message 50000) procid=1554104577 procname=sp_SampleWaitStats @rows=201 @duration=13 milliseconds [SQLSTATE 01000] (Message 50000) procid=1538104520 procname=sp_SampleSchedulers @rows=8 @duration=0 milliseconds [SQLSTATE 01000] (Message 50000) procid=1506104406 procname=sp_SamplePerfCounters @rows=0 @duration=0 milliseconds [SQLSTATE 01000] (Message 50000) procid=1570104634 procname=sp_CheckPerfCounterRules @rows=0 @duration=0 milliseconds [SQLSTATE 01000] (Message 50000) procid=1490104349 procname=sp_SampleDMVs @rows=0 @duration=186 milliseconds [SQLSTATE 01000] (Message 50000) Parameter 4 is incorrect for this DBCC statement. [SQLSTATE 42000] (Error 2560). The step failed.
Comments: ** Comment from web user: sulaak **

I have resolve the problem

CREATED ISSUE: DMVstats: Insert DMV Samples (Collect Sample DMVs)

sulaak — Fri, 05 Oct 2007 14:28:27 GMT

CREATED ISSUE: Invalid objects & columns

v_b_bochkov — Mon, 27 Aug 2007 18:29:42 GMT

Msg 207, Level 16, State 1, Line 34
Invalid column name 'top_category'.

stored procedures:
DMVsample.sp_GetTopElapsedStatement
DMVsample.sp_GetTopFrequencyStatements
DMVsample.sp_GetTopIOStatements
DMVsample.sp_GetTopWorkerStatements

still absent table DMVsample.query_stats in 1.01 release

CREATED ISSUE: Virtual File Stats

wdetmer — Tue, 31 Jul 2007 00:17:16 GMT

How do I go about creating the table(s) and perform the setup for this report?

wdetmer

CREATED ISSUE: Rules report

wdetmer — Tue, 31 Jul 2007 00:16:02 GMT

How is the rules report used and/or configured?

Wdetmer

CREATED ISSUE: Index Operaational Stats

wdetmer — Tue, 31 Jul 2007 00:15:18 GMT

This report does not seem to create the comparison tables for comparing baseline with later snapshot.

How do I select the variables for input into the report?

wdetmer

COMMENTED ISSUE: Install error AgentJobs log

brian_sutherland — Thu, 19 Jul 2007 02:11:22 GMT

I just tried to run the InstallDMVStatsDBProcs.cmd and got an error in CreateDMVstatsSQLAgentJobs.log.
Has anyone else experienced this?

Changed database context to 'msdb'.
Msg 515, Level 16, State 2, Server MYSERVER, Procedure sp_add_job, Line 137
Cannot insert the value NULL into column 'owner_sid', table 'msdb.dbo.sysjobs'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Brian
Comments: ** Comment from web user: brian_sutherland **

Thanks, worked a treat.

COMMENTED ISSUE: Install error AgentJobs log

ltevere — Tue, 17 Jul 2007 19:33:20 GMT

Brian -
I ran into the same problem. I was able to resolve it by modifying the CreateDMVstatsSQLAgentJobs.sql script. I changed the @owner_login_name parameter of the sp_add_job procedure call (all 5 instances) to the service account name that I use to run the the SQLAgent.

HTH
lt

CREATED ISSUE: Install error AgentJobs log

brian_sutherland — Tue, 17 Jul 2007 12:13:45 GMT

UPDATED RELEASE: DMVstats - A SQL 2005 Performance Data Warehouse (Jul 05, 2007)

Mon, 09 Jul 2007 12:56:58 GMT

!!! DMVstats Release 1.01 includes small bug fixes:
* database name with embedded blanks e.g. my db
* purge DMV.... batch jobs - work item #79
* index definition report - work item #78