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
13
votes
1 answer

syntax error in CROSS APPLY

I'm trying to run a simple query to find the queries with the highest average CPU time. The code is literally copy-pasted from here: SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time], SUBSTRING(st.text,…
vlad
  • 4,748
  • 2
  • 30
  • 36
11
votes
5 answers

Sql Server - how to get last server restart (DMV reset date/time)

I'm using some modifications to Glenn Berry's excellent DMV queries! However, I would like to add to the resultset the 'last server restart', or to be more specific, the date/time the statistics for (all, the specific) DMV was reset. Since it would…
dhartford
  • 1,125
  • 2
  • 12
  • 35
6
votes
2 answers

DMF sys.dm_exec_sql_text not showing DBID

I have got this query from http://technet.microsoft.com/en-us/library/ms181929.aspx SELECT s2.dbid, s1.sql_handle, (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 , ( (CASE WHEN statement_end_offset = -1 …
Amir Keshavarz
  • 3,050
  • 1
  • 19
  • 26
5
votes
2 answers

what is Reads and Writes in Sys.dm_exec_requests DMV of SQL Server

As per explaination given on MSDN at link http://msdn.microsoft.com/en-us/library/ms177648.aspx I am not able to understand the meaning of Reads and Writes fully.whether it is physical or logical or database Reads and Writes. Please help me out in…
Ganeshkumar
  • 61
  • 1
  • 10
5
votes
3 answers

MS SQL DMVs for UDF performance stats - how to find top 10 worst UDFs

I heard in Microsoft SQL Server there are multiple ways to find "worst" stored procedures: by number of executions, by CPU worker time, by queue wait time etc. I am looking for a way to find worst (slowest / most used) UDFs -- is there a DMV query…
Andriy Volkov
  • 18,653
  • 9
  • 68
  • 83
4
votes
2 answers

Get Instance Name from Analysis Services like @@ServerName

Is there any way to get the current servername from an Analysis Services instance? Basically the same as SELECT @@SERVERNAME but for SSAS. I have looked into SELECT * FROM $system.DISCOVER_INSTANCES but that returns an error: The…
Ddono25
  • 41
  • 1
  • 2
3
votes
2 answers

Discovering partition names SSAS

Is there a way I can dynamically discover if a partition with a specific name already exists in my cube using DMV or XMLA? Thanks
Maxui
  • 210
  • 1
  • 3
  • 15
3
votes
0 answers

Getting actual memory usage per user session in SSAS tabular model

I'm trying to build a report which would show actual memory usage per user session when working with a particular SSAS tabular in-mem model. The model itself is relatively big (~100GB in mem) and the test queries are relatively heavy: no filters,…
sloggi
  • 31
  • 1
3
votes
1 answer

Way to identify how many rows got updated from logs

We process CSV files from our upstream systems and load them to our master tables in our SQL Server database. We are currently on boarding a new upstream system and suddenly our UPDATE statement took very long time. It could be due to incoming data…
Venkataraman R
  • 12,181
  • 2
  • 31
  • 58
3
votes
2 answers

sys.dm_db_missing_index_details returns no rows

I have tried to look at sys.dm_db_missing_index_details to check for missing indexes on my SQL Server 2005 database. It is returning no rows. It is possible that it should be empty but highly unlikely as I have not added ANY indices on any table…
Mark
  • 1,516
  • 2
  • 14
  • 24
3
votes
1 answer

How to deal with max string length of 255 in SSAS DMV metadata

I'm trying to import SSAS DMV metadata into SQL Server using a data flow in SSIS. In this example, I'm attempting to import data from the $SYSTEM.TMSCHEMA_PARTITIONS DMV. My select statement (which looks like SQL but is not SQL),…
Jonathan Garvey
  • 115
  • 2
  • 9
3
votes
0 answers

Executing DMV catalog query after a SSAS cube process forces execution of all DB scripts

After processing an 2012 SSAS cube, then executing a DMV query, such as: select * from $system.mdschema_sets where [cube_name]='cube name' i see in sql profiler it executes scripts of ALL cubes in the database, disregarding the restriction in the…
Kobi Averbuch
  • 41
  • 1
2
votes
1 answer

List of all queries of Power BI

Is there a way to get the list of all SQL queries in the first step of all Power Query. Please note, I am NOT interested in the source paths (server/database) which can be accessed through Option/Sources settings.
Przemyslaw Remin
  • 6,276
  • 25
  • 113
  • 191
2
votes
3 answers

Get Long Running Queries For Users Or Applications Queries

I recently read this perfect article: How to Find Longest Running Query With Execution Plan – Interview Question of the Week #098 It returns system calling queries (for internal SQL Server works). Is it possible to filter these queries and return…
Arian
  • 12,793
  • 66
  • 176
  • 300
2
votes
2 answers

Powershell Script to pull DMV results from Tabular Model

Trying to build a PowerShell script to connect to Analysis Services Tabular Model and pull the output of DMV queries(eg : SELECT * FROM $System.DBSchema_Tables) Tried Below, but its fails, it seems there is something wrong with connection string or…
Joseph
  • 530
  • 3
  • 15
  • 37
1
2 3 4 5 6 7