The postings on this site are my own and do not represent my Employer's positions, advice or strategies.

LifeAsBob - Blog

 

Home

No Ads ever, except search!
Saturday, December 7, 2024 Login
Public

Blog posts for the month of February,2021.
Monitoring read-only PAAS SQL node2/7/2021 7:39:34 PM

If you connect to your Business Critical Azure SQL instance with ADS or SMSS and in the Additional Connection Parameters you specify ApplicationIntent=ReadOnly, you are then connected via your admin account to that read only replica and can do some performance monitoring via database scoped DMVs:


Monitoring and troubleshooting read-only replicas

When connected to a read-only replica, Dynamic Management Views (DMVs) reflect the state of the replica, and can be queried for monitoring and troubleshooting purposes. The database engine provides multiple views to expose a wide variety of monitoring data.

Commonly used views are:

MONITORING AND TROUBLESHOOTING READ-ONLY REPLICAS

Name

Purpose

sys.dm_db_resource_stats

Provides resource utilization metrics for the last hour, including CPU, data IO, and log write utilization relative to service objective limits.

sys.dm_os_wait_stats

Provides aggregate wait statistics for the database engine instance.

sys.dm_database_replica_states

Provides replica health state and synchronization statistics. Redo queue size and redo rate serve as indicators of data latency on the read-only replica.

sys.dm_os_performance_counters

Provides database engine performance counters.

sys.dm_exec_query_stats

Provides per-query execution statistics such as number of executions, CPU time used, etc.

sys.dm_exec_query_plan()

Provides cached query plans.

sys.dm_exec_sql_text()

Provides query text for a cached query plan.

sys.dm_exec_query_profiles

Provides real time query progress while queries are in execution.

sys.dm_exec_query_plan_stats()

Provides the last known actual execution plan including runtime statistics for a query.

sys.dm_io_virtual_file_stats()

Provides storage IOPS, throughput, and latency statistics for all database files.

  

You can also set up xevent sessions : Read queries on replicas - Azure SQL Database | Microsoft Docs

 

SSRS Missing URL parameter IterationId2/5/2021 1:00:35 PM
SQL Server Reporting Services SSRS Error, Missing URL parameter:  IterationId.

Missing URL parameter: IterationId


I began receiving this error when i imported and tried to modify a report from SQL Server performance Dashboard that i was trying to make work with Azure PAAS SQL. I found this was not an uncommon issue. 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b44a0452-d71a-4e69-b7e0-af74b9f6bde7/missing-url-parameter-iterationid

 I deleted the Line from the report and problem was fixed. 

I was getting this error message myself, and it was due to the Line control. For whatever reason, the Line control tries to include a standard blank image right after it, but the URL is missing that parameter in FireFox and Chrome (but not IE, as far as I can tell). When I remove all the Line controls, the error goes away. Make sure you don't have any Lines in your footer (or anywhere else on your report, for that matter.)

Blog Home