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
0
votes
0 answers

How to get Cube Data Source View Last Schema Update value

SQL Server Analysis Services Is there a way to get the data source view last schema update value as seen on the properties dialog box? If I use the DMV syntax: SELECT * FROM $System.MDSCHEMA_CUBES It will list all the measures and dimensions used in…
Willie P
  • 1
  • 5
0
votes
1 answer

Query sql statistics without SET parameter

I try to capture some statistic parameters for logging purpose. "SET parameters" are no option (i.e. set statistics time on). So I tried to query some DMV: select '3AAAAAAAAAAA'; --no GO-statement here select total_worker_time/execution_count…
Christian4145
  • 513
  • 1
  • 9
  • 31
0
votes
1 answer

Performance issue joining system tables & DMVs on DB with many partitioned tables?

I'm running the below query on each database in my environment, twice a day, for tracking. I have a relatively busy server with a database that has a count of 95k rows for this query (this DB contains many about 20 tables that can have many…
mbourgon
  • 1,286
  • 2
  • 17
  • 35
0
votes
2 answers

SQL Server sp_help to pull out just the columns information

The SP_HELP procedures produces multiple subsets of data and I would only like to have the columns information from that. Is there a way to maybe write a query using sp_help to just pull out that information. I need to do this to build a metadata…
rvphx
  • 2,324
  • 6
  • 40
  • 69
0
votes
0 answers

Cube Measure groups, Measures and Dimensions visualised

The following query (used in Excel VBA) gets the measuregroups and the dimensions from a cube, but not the measures. I would like to tie the dimensions to their measures and not just the measuregroup. Is that possible? SQLstr = "SELECT …
preston
  • 145
  • 2
  • 12
0
votes
1 answer

How to filter SSAS catalog list?

I use DMX query like this SELECT * FROM $SYSTEM.DBSCHEMA_CATALOGS which retrieves list of OLAP DBs. I would like to add a where clause to select catalogs where name contains some sequence of chars, e.g., SELECT * FROM…
Janis S.
  • 2,526
  • 22
  • 32
0
votes
1 answer

How to get SSAS tabular model attributes and dimensions usage details

I need to create a cube which purpose would be to gather detailed information about tabular model dimensions and attributes usage by users (e.g. how many times users access certain dimension). So far, I tried to use Data Management View Query…
nefima
  • 1
  • 2
0
votes
0 answers

Azure SQL DMV procedures slow

I tried to use Azure SQL Performance metrics and for me they are running very slow and consume lots of CPU (up 100%). Example takes 4 minutes to execute, CPU 100% SELECT * FROM sys.resource_stats WHERE database_name = 'db1' AND start_time >…
Maxim
  • 144
  • 1
  • 9
0
votes
1 answer

Query dimension / fact table ID on SSAS

I have SSAS server with a analytical tabular model. It has several dimensions and fact tables. I want to create dynamic XMLA code for processing dimensions / fact tables. Using SSMS, I can script out sample XMLA as follows:
DNac
  • 2,663
  • 8
  • 31
  • 54
0
votes
1 answer

SSAS DMV's NOT LIKE Condition

I have the query that runs OK on SSAS. SELECT DIMENSION_NAME FROM $SYSTEM.MDSCHEMA_DIMENSIONS WHERE LEFT(CUBE_NAME,1) = '$' AND [DIMENSION_UNIQUE_NAME] <> '[Metrics]' AND [DIMENSION_UNIQUE_NAME] <> '[Measures]' ORDER BY DIMENSION_NAME How should…
DNac
  • 2,663
  • 8
  • 31
  • 54
0
votes
1 answer

DMV to identify Database Lock scenarios in SQL Server 2012

I have a batch process that runs every night. Once the process completes, I see that there are some records which has not processed completely. I think I might be encountering a locking scenario while running multiple updates on a single table. What…
Sammy
  • 798
  • 2
  • 8
  • 23
0
votes
1 answer

Check if SQL object is referenced by any other SQL objects

I was just reading this SO thread and had a question for @Mack regarding whether there is a way to check if a SQL object is referenced by any other SQL objects. He (@Mack) used T-SQL and DMVs to accomplish something similar in his answer. Is this…
VixterJ
  • 31
  • 5
0
votes
1 answer

SQL Server SysTable with tstamp of last inserted row of each table

Is there any system table or dmv in SQL Server 2008 R2 that contains information regarding the last DML statement (except select) that was issued against any user table? I see that in sys.tables there is a modify_date column but that's just for any…
Sergiu
  • 432
  • 6
  • 18
0
votes
1 answer

What's so special about the value 976 in sys.dm_exec_query_stats?

When querying the sys.dm_exec_query_stats DMV, I am observing some interesting behaviour on the last_worker_time column. Usually, it reports 0 for the specific stored procedure I am monitoring. However occasionally it will return a non-zero value,…
Sir Crispalot
  • 4,792
  • 1
  • 39
  • 64
0
votes
1 answer

SQL Server 2008 R2 running in compatibilty mode 80. What features i get to use?

We have SQL Server 2008 R2 running in comaptibilty mode 80 (2000) as we lot of discontinued features used. Intially i thought i will get only features 2000 to use, but as pleasent surprise i show INFORMATION_SCHEMA, i thought this is superset case.…
Pritesh
  • 1,938
  • 7
  • 32
  • 46
1 2 3 4 5
6
7