Stack Overflow
Stack Overflow

Questions tagged [dmv]

DMVs are Dynamic Management Views in SQL Server. DMVs store metadata and important statistics related to SQL Server performance.This information gets updated at runtime so that user can get the most recent values in order to analyse server performance and usage. DMVs greatly assist in analysing system performance issues and diagnosing problems in SQL Server at any time.

96 questions
1
vote
1 answer

Determine hardware specs and OS version of SSAS box using XMLA or a DMV?

At my current project, SSAS is running on a standalone server and I'd like to know the hardware specs (CPU, Memory, etc) and OS version. The catch is I don't have access to the OS (or even remote access to perfmon or eventvwr) and the DBA's have…
Bill Anton
  • 2,920
  • 17
  • 23
0
votes
1 answer

When do the missing index DMVs get cleared?

I have been using Bart Duncan's classic article to find out what indexes I am missing (http://blogs.msdn.com/b/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx) Every now when I execute select * from…
Mark
  • 1,516
  • 2
  • 14
  • 24
0
votes
0 answers

Delete from SQL Server 2017 QueryStore dmv issue

We are using SentryOne, and one of the biggest consumers of IO & CPU resources is the query delete sys.plan_persist_wait_stats where Plan_id in ( select plan_id from sys.plan_persist_plan where query_id = @query_id ) I tried to Google…
Geri Reshef
  • 397
  • 1
  • 6
  • 17
0
votes
1 answer

VERY strange behavior of sys.dm_hadr_database_replica_cluster_states in a SQL Job

How to call DMV sys.dm_hadr_database_replica_cluster_states inside SQL job? Code is very simple in step: CREATE TABLE #D (DB sysname); INSERT #D select database_name from sys.dm_hadr_database_replica_cluster_states; But when job starts, it throws…
Max Isaev
  • 1
  • 1
0
votes
0 answers

Granting server-level roles to Azure AD Logins

I have an Azure Managed Identity service, I wish to grant it permissions to use the sys.dm_exec_input_buffer function in Azure SQL Database. According to this documentation, I must grant VIEW SERVER PERFORMANCE STATE permissions. According to this…
mark
  • 47
  • 1
  • 2
  • 9
0
votes
1 answer

What is the difference in TMSCHEMA vs MDSCHEMA in DMV of Power BI models

For example, what is the difference between in those 2 when querying DMV? select * from $SYSTEM.MDSCHEMA_MEASURES select * from $SYSTEM.TMSCHEMA_MEASURES When those queries are going to return different results?
Przemyslaw Remin
  • 6,276
  • 25
  • 113
  • 191
0
votes
0 answers

Get lineage id of a measure from thin pbix file

Is there a way in PowerQuery to extract measure lineage id from a thin pbix report connected to dataset through live connection mode? I access the contents of thin pbix by unzipping it and reading the section Report/Layout. Lineage id can be found…
Przemyslaw Remin
  • 6,276
  • 25
  • 113
  • 191
0
votes
1 answer

Extract measure alias assigned in a visual

Is there an external tool for Power BI which allows automatically to extract column or measure alias name which as been assigned in a visual. Is it stored anywhere in DMV?
Przemyslaw Remin
  • 6,276
  • 25
  • 113
  • 191
0
votes
0 answers

sys.dm_db_index_usage_stats from SMO with powershell script

I am trying to get the sys.dm_db_index_usage_stats from SMO with powershell script. Currently script looks like this: [system.reflection.assembly]::LoadWithPartialName("Microsoft.SQLServer.Smo") | out-null $SQLServer = "name" $srv = new-object…
evil-cat
  • 111
  • 1
  • 1
  • 9
0
votes
0 answers

Check SQL DW Consumption using Azure Data Factory using DMVs

We are working on 2 solutions: Auto-cancel long running queries on SQL DW that are executed by certain set of Users (that belongs to a workload group). Auto-scale Service tier for SQL DW. These 2 solutions would work based on the current OR…
Saurabh Mehta
  • 91
  • 1
  • 2
  • 9
0
votes
0 answers

Find resource_name used for stalled sp_getapplock call

I am the DBA for an application that uses the sp_getapplock stored procedure call to perform some specific funcitonality. Occasionally, for some unknown reason, perhaps application bug, gremlins, or other unknown error, the lock is not released. I…
Robert Sievers
  • 1,277
  • 10
  • 15
0
votes
1 answer

Is there a way to build TMSL scripts programatically on the same way that XMLA tools create it (like Tabular editor) from an existent tabular db?

I am wondering how is possible to create a TMSL script from an tabular database (power bi service or SSAS) using some programing language. These kind of scripts are available in several tools, like SSMS or Tabular Editor: Example of the menu to…
A Echague
  • 3
  • 1
0
votes
2 answers

Issue where Last_sent_time and Last_received_time columns in DMV dm_hadr_database_replica_states does not get updated

I have an issue on 2016 where Last_sent_time and Last_received_time columns in the DMV dm_hadr_database_replica_states does not get updated. Coincidently we are seeing issues related to timeouts when the times are finally updated which makes me…
Lee
  • 13
  • 1
  • 4
0
votes
1 answer

DMV request for "Description" of the table for Power BI dataset

What I am trying to achieve is to add tables and columns descriptions programmatically to a Power BI dataset. For this reason, I use Server Analysis Services to get access to the metadata. I run a simple request: select * from…
Oleg Kazanskyi
  • 196
  • 2
  • 13
0
votes
0 answers

How to return columns based on selection or add that as parameter in stored procedure

I am trying to capture sql server waits from dmv sys-dm-exec-requests in a stored procedure to run over time. Later i display that in UI as a view. Currently it returns over 30 columns from the proc created which is simply selecting the data from…
Newbie-DBA
  • 107
  • 5
1 2 3
4
5 6 7