SQL Sentry - Recent Posts

Topic "Scheduled Maintenance Window - Doesn't reset when set to Inherit" a message from admin

Fri, 25 May 2012 11:47:50 GMT

No problem Tom,

It does make less sense when you are using a one time schedule, but a lot of other scenarios will make less sense in that case as well, and it is better to maintain consistent behavior in general.

Topic "Scheduled Maintenance Window - Doesn't reset when set to Inherit" a message from Tom

Fri, 25 May 2012 11:44:50 GMT

Fair enough. I just want to make sure it is intended behavior. Thanks!

Topic "Scheduled Maintenance Window - Doesn't reset when set to Inherit" a message from admin

Fri, 25 May 2012 11:29:52 GMT

Hi Tom,

When inherit is set to true, the settings from the ancestor override the settings for the descendant.

The settings for the descendant are not actually replaced, so allowing the schedule to be deleted would break those settings, even though they are currently being overridden.

The idea is that you will easily be able to go back and pick up those settings again later, just by setting inherit to false.

Topic "Scheduled Maintenance Window - Doesn't reset when set to Inherit" a message from Tom

Fri, 25 May 2012 11:06:32 GMT

I had created a one-time schedule due to maintenance on two servers, which worked fine.

After the window was over, I set the Inherit back to True, which should inherit the None (default) window. For alerting, it worked as expected.

However, I couldn't delete the old window, so I went back to one of the hosts, and set Inherit to False. Sure enough, it picked up the old schedule. I set to the window to None then set Inherit to True again. After repeating those steps on the other host, I was able to delete the scheduled window.

Shouldn't setting Inherit to True automatically change the window so the old window can be deleted?

Topic "Alerting on Specific Wait Type" a message from swright

Mon, 21 May 2012 15:00:06 GMT

While you can't currently alert on specific wait types, this particular wait type correlates with the Memory Grants Pending performance counter. When Resource_Semaphore waits are greater than zero, so too will be the Memory Grants Pending counter. This topic is actually discussed in the Performance Metrics topic in the User Guide at:

http://sqlsentry.net/help/ug/webframe.html?Performance_Metrics.html

Note that Page Life Expectancy will likely be at or near zero at the same time. Thresholds on either of these metrics can trigger alerts as outlined in this KB article:

http://forum.sqlsentry.net/topic1856-setting-up-automated-performance-alerting-in-sql-sentry-event-manager.aspx

Topic "Alerting on Specific Wait Type" a message from ntxdba

Mon, 21 May 2012 11:59:25 GMT

On the performance dashboard the memory waittype reports a resource_semaphore when this happens our servers slows dramatically and we starting getting calls on the issue. I s there a way to genreate an alert when the the waittype resource_semaphore reaches a certain level?

Topic "Disk write latency measurements" a message from GregGonzalez

Thu, 17 May 2012 17:08:21 GMT

It's also important to note that the dashboard shows raw latency and doesn't factor in volume. So you could have low IO volume, but high latency due to a very slow drive or other IO path bottleneck.

The Disk Activity tab *does* factor in volume, and will actually mask scenarios where latency is high but volume is low. More details can be found in the patent app here, specifically item [0083]:
http://appft1.uspto.gov/netacgi/nph-Parser?Sect1=PTO1&Sect2=HITOFF&d=PG01&p=1&u=%2Fnetahtml%2FPTO%2Fsrchnum.html&r=1&f=G&l=50&s1=%2220100214298%22.PGNR.&OS=DN/20100214298&RS=DN/20100214298

This is why, as Ryan indicated, for a more definitive view of disk performance the Disk Activity tab should be used.

Topic "Disk write latency measurements" a message from Ryan Brickey

Thu, 17 May 2012 15:17:36 GMT

Aaron,

SQL Sentry does not write data to disk to measure I/O latency. The Disk IO performance data is collected from the Windows performance counters provided in the Physical Disk category. It is also important to note that the data collection process is a continuous process as long as the server is being monitored. Here is a link to the Performance Metrics section of our user guide that discusses the Disk IO counters, along with the other dashboard performance counters, in detail: http://www.sqlsentry.net/help/ug/webframe.html?Performance_Metrics.html.

I also wanted to mention that the dashboard is intended as a high-level view of the server’s performance. I would recommend that you review the Disk Activity tab to further analyze where the latency is occurring as the Disk Activity tab provides in-depth details for individual database files. Here is a link to the Disk Activity section of the user guide for additional reference: http://www.sqlsentry.net/help/ug/webframe.html?PA_Disk_Activity.html.

Please let us know if you have any questions about this information.

Topic "Performance Data Collection Failed: Error reading performance category MSOLAP$VELB:Cache" a message from swright

Thu, 17 May 2012 14:00:30 GMT

Hi,

Can you follow the steps outlined in this KB article:

http://forum.sqlsentry.net/topic2017-missing-performance-counter-categories.aspx

and let us know the results? Note you will need to check for these counters on the node where the issues occur.

Thanks

Topic "Disk write latency measurements" a message from aarondba

Thu, 17 May 2012 13:42:55 GMT

We have a mostly empty drive letter X which currently contains only a few SQL scripts and output files. SQL Sentry is reporting 20ms+ latency on this drive letter right after business hours, and I have been asked to see if Sentry might be sending data to the disk to measure I/O latency?

Thanks,
Aaron

Topic "Current Waits" a message from swright

Thu, 17 May 2012 11:43:08 GMT

Tom,
Performance Advisor provides multiple ways to view wait stats for your server. From the dashboard view all current waits are shown in live mode in graphical format, grouped into friendly classes. Irrelevant wait types are filtered for efficient analysis. If you mouse over any of the charts, a pop up provides the native wait types for that class and the length of that particular wait type. At the query level, the current wait type for running Top SQL is shown under the Top SQL tab.

There are several methods to review historical wait stats as well. Just as in live mode, the Performance Advisor dashboard provides complete historical visibility into these metrics. In this view they are further distilled into categories, but again you can mouse over the chart for a drill down to the specific native wait types. Additionally our SQL Server Wait Stats Analysis report, found under the Reports > Performance Advisor > Performance menu in the SQL Sentry Client, provides a breakdown of your wait stats over any specified timeframe at the Category, Class, and native Wait Type levels. This includes a further distinction between resource and signal (CPU) waits just as in Sample mode on the dashboard.

That said, whenever discussing wait stats I always feel compelled to add that, while wait stats are a good resource to begin investigating any performance bottlenecks, they seldom provide the complete picture. At times they can even lead you down the wrong path. That's why wait stats are just part of the comprehensive view SQL Sentry provides into your server's performance. It's always important to distinguish between "What are you waiting on?" vs. "Why are you waiting?" I've written a blog post regarding this topic at http://steve.blogs.sqlsentry.net/2011/04/wait-stats-and-rest-of-story.html.

Let us know if you have any other questions.

Topic "Current Waits" a message from Tom

Thu, 17 May 2012 09:36:50 GMT

Today, I was troubleshooting some tempdb contention, and it occurred to me that there doesn't seem to be a way to get a listing of the current waits, similar to Adam Machanic's sp_whoisactive, or a listing of the waits over time.

Am I missing something?

Topic "Performance Data Collection Failed: Error reading performance category MSOLAP$VELB:Cache" a message from lzhu

Wed, 16 May 2012 18:47:03 GMT

I have no problem setting up and watch PA for a SSAS instance on a cluster node via adding the virtual name of the server. There is no custom defined performance counters. Everything is the default. The dashboard displays data as expected. However, after I failover the SSAS instance to another node, the Dashboard shows the following error message:

Performance Data Collection Failed: Error reading performance category MSOLAP$VELB:Cache.
at Intercerve.SqlSentry.Server.PerformanceAnalysisDataCollector.CollectPerformanceData(DeviceAccessor deviceAccessor, PerformanceAnalysisSampleIntervalAccessors performanceAnalysisSampleIntervalAccessors, Int32 timestamp)
at Intercerve.SqlSentry.Server.DeviceDataCollector.CollectData(PerformanceAnalysisSampleIntervalAccessors performanceAnalysisSampleIntervalAccessors, PerformanceAnalysisSubsystemRefreshRateAccessors allSubsystemRefreshRates, PerformanceAnalysisSubsystemRefreshRateAccessors refreshedSubsytemRefreshRates, Boolean forceSchemaRefresh, Int32 timestamp)
at Intercerve.SqlSentry.Server.PerformanceMonitor.PerformPerformanceAnalysis()

It did not make any difference if I close and reopen the PA Dashboard, or stop then restart watching the connection. I have also verified that the SSAS instance was successfully moved to the other node and restarted. After I fail back the SSAS instance to its original node, the error message on Dashboard disappeared. The clustered server is Windows 2003 R2, with the SSAS instance SQL Server 2008. Is this a known issue with clusters?

Topic "Scheduled Maintenance Window - Error with One-Time" a message from Ryan Brickey

Wed, 16 May 2012 15:44:10 GMT

Tom,

Thank you for bringing this to our attention. I was able to reproduce this behavior in our labs and have logged it as defect #3388 to be resolved in a future build.

Thank you again for bringing this to our attention as well as your patience while it is being resolved.

Topic "Scheduled Maintenance Window - Error with One-Time" a message from Tom

Wed, 16 May 2012 14:21:19 GMT

I received the following error while creating a one-time maintenance window.

I was able to work around it by changing to recurring, selecting a day, and then selecting one-time again.

This is v7.0.41.0.

Topic "Filter specific SQL from max runtime" a message from Ryan Brickey

Tue, 15 May 2012 17:44:22 GMT

Tom,

When there is a statement that you would not like to be alerted on, you can right-click on the statement in the Performance Advisor Top SQL tab and select the Hide option. This will prevent the statement from being shown in both the Top SQL Running Queries and the Complete Queries. It will also prevent the statement from triggering any the Top SQL conditions, including Runtime Maximum Threshold, as well as being seen on the Event Manager Calendar.

Please let me know if this will meet your needs.

Topic "Replacing watched server" a message from Ryan Brickey

Tue, 15 May 2012 17:32:23 GMT

Hello Tom,

When you migrate a currently monitored server to a new server that will have the same server name, SQL Sentry should automatically identify the new server and continue to monitor it. This should also allow you to retain all of your previous data. Please note that you will want to stop the monitoring the connection with SQL Sentry during the migration. There are, however, some additional points that you should be aware of.

With regard to event chains; because this is a new SQL Server installation, the job GUIDs are going to be different on the new server. As a result any event chains that involve jobs from this server are going to need to have the existing jobs replaced with the jobs from the new server.

Additionally, depending on the new server’s specifications you may see that your event runtime statistics change from what they were on the pervious server, and this could cause an increase in the amount of runtime minimum or maximum notifications sent by SQL Sentry. If this happens, you can configure SQL Sentry to recalculate the runtime statistics from the day of the migration for the offending events. Here is a link to the user guide that discusses this topic in detail: http://www.sqlsentry.net/help/ug/webframe.html?Event_Object_Information_Window.html.

If you have any additional questions or concerns about the migration process, please feel free to contact support directly by emailing support at sqlsentry.net or calling 855-775-7733.

Topic "Filter specific SQL from max runtime" a message from Tom

Tue, 15 May 2012 16:21:11 GMT

I have an app that uses a WAITFOR statement while connecting to Service Broker. Unfortunately, I'm constantly alerted that these exceed the max runtime. For example, it says the SQL statement ran for 156 days.

Is there any way to filter by the following:

1. User

AND

2. Database Name

AND

3. SQL Text

Topic "Replacing watched server" a message from Tom

Tue, 15 May 2012 10:15:07 GMT

We are planning on migrating an existing watched server to new hardware. The server name will not change, but the IP address will. In essence, we have both servers online with different names. We will be changing the names of the systems and migrating the databases over during the outage.

Is there anything in SQL Sentry I should do before or after this change? I would prefer not to lose any history of the server's performance.

Topic "Migrating SQL 2008 SQL Sentry DB to 2012" a message from Ryan Brickey

Mon, 14 May 2012 14:51:27 GMT

Hi Tim,

Being that SQL Server 2012 was not supported prior to SQL Sentry version 7.0, would you please upgrade to v7.0 and let me know if you continue to see this error. You can download v7 by logging into the customer portal and following the ‘Download SQL Sentry Software’ link. Please also note that you will be prompted to upgrade your license version and you will need to apply the upgraded license after v7 has been installed.

If this does not resolve the error, please open a support ticket for additional analysis by emailing support at sqlsentry.net or calling 855-775-7733.

Topic "Migrating SQL 2008 SQL Sentry DB to 2012" a message from Tim Ford

Mon, 14 May 2012 14:34:54 GMT

Good Afternoon, I just restored a copy of the existing 2008 DB for SQL Sentry and in attempting to point the service to the new DB after restoration and upgrade to 2012 (and setting the compat mode to 110) I receive the following error message:

SQL Sentry 6.1 requires Microsoft SQL Server 2005 or higher.

Do you have any suggestions on troubleshooting. I've confirmed rights are not an issue.

Thanks!

Topic "Monitoring non-MDX statements" a message from GregGonzalez

Thu, 10 May 2012 16:28:43 GMT

Hi,
Actually nothing has changed with regard to how the SSAS trace works in v7. SQL Sentry still collects XMLA and failures, but any command collected must be above the Minimum Duration specified for the Top Commands Source. I would double-check the effective setting at the SSAS instance level. If things aren't adding up for some reason, please go ahead and log an incident to support at sqlsentry.net and we'll have a look.

Thanks,
-greg

Topic "Monitoring non-MDX statements" a message from lzhu

Thu, 10 May 2012 14:54:18 GMT

Several years ago, when PA for SSAS was first released, I thought we were able to see cube processing failures on event/calendar. Now it seems only MDX queries are watched. Is there a way in SQL Sentry for us to watch cube processing failures, database synchronization failures, etc.?

Topic "Detecting SSAS configuration changes" a message from jhall

Thu, 10 May 2012 02:46:03 GMT

Hi Izhu,

Thanks for posting your request to the forums.

SQL Sentry does not track changes like this for SSAS out of the box today, but we would be happy to add your request for this type of auditing to be added to a future release of SQL Sentry.

Topic "Detecting SSAS configuration changes" a message from lzhu

Wed, 09 May 2012 16:14:47 GMT

Many times we (DBA) would like to know if someone has changed the server configurations, i.e. added/removed IDs/groups to/from the server role security, changed certain configuration values from the default, etc. Is there a way in SQL Sentry to setup an alert for this type of changes?

Topic "Read and write faults" a message from aarondba

Fri, 04 May 2012 12:00:34 GMT

Thanks. Should have known to look at the docs first.

Topic "Trusting the SQL Sentry hyperlink in Outlook" a message from Esmith

Thu, 03 May 2012 16:43:30 GMT

Trusting the SQL Sentry hyperlink in Outlook

Many of the alert emails sent from SQL Sentry now contain a link which opens the Client directly to the problem you are being alerted about. This feature can save you a lot of time, but as you may have noticed, Outlook does not natively trust hyperlinks which use the SQL Sentry protocol.

When clicking on the SQL Sentry link in your email, you will see a security notice like the one below.

While there are ways to disable all Outlook hyperlink warning messages, we recommend against them.

Instead we recommend that you write a specific set of registry keys, which will allow Outlook to trust just the SQL Sentry protocol handler. Using this method will allow you to quickly open the SQL Sentry Client, while still leaving you protected against unknown hyperlink locations. This attached .txt file contains those registry keys.

Note: The registry path in the file is for Outlook 2010. If you are using Outlook 2007, you will need to modify the path as follows.

From
[HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\14.0]

To
[HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\12.0]

Once you save the attached text file to your computer, you will need to remove the .txt extension. Double click on the file to apply the registry changes. After applying these changes, you should no longer see any security notices when opening the SQL Sentry Client from an alert email.

Please don’t hesitate to contact us if you have any problems.

--
Eric Smith
SQL Sentry

Topic "Index Manager - Overflow Exception" a message from Tom

Thu, 03 May 2012 14:41:02 GMT

It's on its way.
Thanks!

Topic "Index Manager - Overflow Exception" a message from jhall

Thu, 03 May 2012 14:36:41 GMT

Hi Tom,

Thank you for reporting this error.

Could you please open a support incident by emailing support at sqlsentry.net so that we can work with you directly to determine the issue?

Topic "Index Manager - Overflow Exception" a message from Tom

Thu, 03 May 2012 14:05:35 GMT

I just enabled the Frag Mgr in v7.0.32.0 on a few boxes and this comes back for two of them, both SQL 2005 on Win2003.

Not a huge issue, since we just started this, but thought I'd bring it to your attention.

[Server]:
[Message]: An error has occurred:
Message: One or more errors occurred.

System.AggregateException: One or more errors occurred. ---> System.OverflowException: Value was either too large or too small for an Int32.
at System.Convert.ToInt32(Int64 value)
at Intercerve.SqlSentry.TableAndIndexCollectionWorker.CollectSqlTablesAndIndexes(Boolean isFullyLicensed, PerformanceAnalysisSqlDatabaseAccessor databaseItem, SqlServerDatabaseSourceSettingsInterfaceAccessor databaseSettings, String sql, Dictionary`2 unmatchedTables, Dictionary`2 unmatchedIndexes, Dictionary`2 unmatchedPartitions, Dictionary`2 unmatchedPartitionFiles, Dictionary`2 uniquePartitionIdToPerformanceAnalysisSqlIndexPartitionAccessor, Int32[] histogramCounts, SpaceAggregate spaceAggregate)
at Intercerve.SqlSentry.TableAndIndexCollectionWorker.DoWork(CancellationToken cancellationToken)
--- End of inner exception stack trace ---
---> (Inner Exception #0) System.OverflowException: Value was either too large or too small for an Int32.
at System.Convert.ToInt32(Int64 value)
at Intercerve.SqlSentry.TableAndIndexCollectionWorker.CollectSqlTablesAndIndexes(Boolean isFullyLicensed, PerformanceAnalysisSqlDatabaseAccessor databaseItem, SqlServerDatabaseSourceSettingsInterfaceAccessor databaseSettings, String sql, Dictionary`2 unmatchedTables, Dictionary`2 unmatchedIndexes, Dictionary`2 unmatchedPartitions, Dictionary`2 unmatchedPartitionFiles, Dictionary`2 uniquePartitionIdToPerformanceAnalysisSqlIndexPartitionAccessor, Int32[] histogramCounts, SpaceAggregate spaceAggregate)
at Intercerve.SqlSentry.TableAndIndexCollectionWorker.DoWork(CancellationToken cancellationToken)<---