dmv's questions - Chinese 1answer

140 dmv questions.

Msg 15199, Level 16, State 1, Line 29 T he current security context cannot be reverted. Please switch to the original database where Execute As was called and try it again. execute as login=...

Monitoring software has indicated average lock wait times rising to 500ms during the night. Is there any way of confirming which locks these were as we currently do not log any performance data to a ...

I run this Script: SELECT dest.TEXT AS [Query], deqs.execution_count [Count], deqs.last_execution_time AS [Time], (select case when dest.TEXT like '%select%' ...

By querying select * from sys.dm_exec_connections I do not see corresponding connections for internal sessions, about the existance of which I know from sys.dm_exec_sessions DMV. My questions is ...

Is there a quick way to find all columns in SQL Server 2008 R2 that are encrypted/have encrypted data? I need to nullify the data in all encrypted columns in a development server (according to our ...

It is a known fact that the DMVs dont hold accurate information regarding number of pages and count of rows. However, when you have the stats updated, I can't see why they wouldn't. I am working on a ...

I am looking for a DMV query which contain Dimension_Name and Object_ID together. I have System.mdschema_dimensions to get the Dimension Unique name and System.discover_object_activity to get the ...

I'd like to work around instances where a database were changed to simple and then reverted back to full or bulk logged recovery models. This is for my backup maintenance plan, as obviously any ...

We had a site slowdown between 11.57 am and 12:02 pm on 6th Aug 2018, To trace back the queries which were ran our production database (SQL Server Standard Edition 2012) and which might have caused a ...

I have a server where all my sql server backups are currently being saved to the G: drive. Now there is no enough disk space on drive G: to keep backups for the last 5 days. I have asked to add ...

when I run the following script in one of my databases: SELECT * FROM SYS.triggers when I try to find out about sys.triggers Contains a row for each object that is a trigger, with a type of TR ...

I'm analyzing a 2008 Instance that supports a 3rd party app. The app will generate the SQL Code then send it to the Database as an ad-hoc query. I'm using this query (based on a Glenn Berry script): ...

In production, each instance of SQL Server has over 250 databases (what we call 'OrgDb's). The project I'm currently working on, aims to send all the reported missing indexes by SQL Server's DMVs to ...

One way to determine the executing stored procedure is to use "dynamic management" methods, like so: SELECT sqlText.Text, req.* FROM sys.dm_exec_requests req OUTER APPLY sys....

I have a user on SQL Server 2014 Standard Edition (RTM) with SYSADMIN role with Server View State Permissions but when I execute DMV sys.dm_os_performance_counters it returns no record. Any idea ...

I am trying to monitor live performance data and usage, which cannot be tracked with PerformanceMonitor. What are the consequences of reading DMVs in a production live OLTP database? : Eg: sys....

When asking which databases are writable on any given server, I've been using the below snippet to list PRIMARY replica databases and databases that do not participate in an AG. Now that I've ...

In my databases I have some Stored procedure with recompile The way I find them is: SELECT OBJECT_NAME(ID)AS SP_NAME,* FROM SYSCOMMENTS WHERE TEXT LIKE '%WITH RECOMPILE%' when I look at: select * ...

To evaluate the migration of an on-prem SQL Server to a cloud-hosted VM, I want two fundamental pieces of information: The list of clients that access the on-prem DB Server. I'd like to know: client ...

I am using the DMV views to get a list of index recommendations and then I am reviewing each of them to make sure they make sense and don't overlap etc. Some examples are Index ON X ( Y ) and Table ...

If a query is executing on a multi-core server, and the query uses parallel threads, will sys.dm_exec_requests.cpu_timeshow the cpu time consumed by all threads executing the query? I assume this is ...

I have verified through SQL Server Profiler that my request per-second are around 30 request/s as is corroborated by SSMS Activity Monitor but sys.dm_os_performance_counters is reporting hundreds of ...

On Microsoft SQL Server 2008 R2 SP3, the DMV sys.dm_exec_procedure_stats is reporting incorrect statistics for me. It is reporting statistics of a regular INSERT statement against a stored procedure....

I have a job that executes every 5 minutes to gather info from DMVs that has not changed in over 2 years, normally running under a minute. All of a sudden starting last week, with no changes deployed,...

I would like to find out from the code below, what was the name of the database where the last command was run. In this case I am looking for my_other_database. his there any way of finding this? Is ...

How can I check the progress / status when I submit an alter index reorganize / rebuild ?

I'm trying to figure out a way(using only TSQL) to determine if and how much the PAGEFILE is used (I will capitalize this term if I refer to the windows page file from now on - like pagefileusage% ...

The following query shows how to find Current view of Buffer Memory used by each database. How do I find Average/Cumulative Memory buffer usage of each database since the last SQL server restart, not ...

In Question How to get CPU usage by database for particular instance? Solomon states: The query using sys.sysprocesses is only looking at right now. And the query using sys.dm_exec_query_stats is ...

What is the best indicator in finding out whether a database is utilized or accessed? I tried index statistics and based on I/O utilization but none seem to be a good indicator. Problem: I have a ...

The boss has asked for a list of DB names along with size and name of last person to use that DB. I've found resources for when a DB was last accessed but for by whom. How would I solve this? ...

In SQL Server on the database engine, you can run a SQL query like this to get the server's version info like Edition and Version, and Update Level, etc. SELECT @@VERSION What is the MDX equivalent ...

I executed the query on my production server. select s.session_id, s.login_time, s.is_user_process, c.session_id, c.connect_time, case when c.Connect_Time ...

I'm working on a query to tell what objects have locks from a given session and I'm running into some blocking issues. Here is my basic query: SELECT request_session_id AS session_id, ...

During a maintenance job, I'm trying to get a list of fragmented indexes. But the query is extremely slow and takes over 30 minutes to execute. I think this is due to a remote scan on sys....

Please, if this is not place to post questions like these, let me know and I will delete it. Inside the Glenn berry's Diagnostic queries, there is a query to show how much CPU a database is using. ...

I've seen a couple of blog posts where fairly knowledgeable sounding MVPs are still using sys.sysprocesses rather than the recommended DMVs: sys.dm_exec_connections, sys.dm_exec_sessions and sys....

I am trying to select 15% of disk space available in a server. SELECT DISTINCT s.volume_mount_point [Drive], CAST(s.available_bytes / 1048576.0 as decimal(20,2)) [...

No doubt this is syntax or typo...but when I query sys.dm_sql_referencing_entities for a parent table, I get zero results. No error, just no results. USE [source]; GO SELECT * FROM sys....

Looking at a couple of DMVs, and trying to see if I can programmatically figure out the cost of the cached plans in my SQL Server. I'm hoping that column represents the same concept of cost that is ...

I am executing a select statement to retrieve fragmentation percentage of indexes and facing below error. SELECT A.NAME,B.NAME,C.avg_fragmentation_in_percent FROM SYS.TABLES A INNER ...

I'm trying to set up some scheduled jobs which will collect some server stats, using system DMV's in SQL Server Azure. If a none SA users uses 'sys.dm_os_schedulers', they get an error (below), query ...

We recently had a problem on our SQL Server 2014 HADR environment, where one of the servers ran out of worker threads. Got the message: The thread pool for AlwaysOn Availability Groups was unable ...

I was using a table that logs the results of querying dm_exec_procedure_stats every minute to identify activity of stored procedures on one of my servers. Basically it compares the database, object ...

I am executing the following dmv query against SQL Server 2014 to find all activity on a server and any blocking activities: SELECT Distinct ... FROM sys.dm_exec_requests er OUTER APPLY sys....

When looking at sys.dm_os_wait_stats, the following columns are defined as BIGINT waiting_tasks_count wait_time_ms max_wait_time_ms If any of those values go above 9,223,372,036,854,775,807, do ...

I am running DMV queries to get stats for my stored procedure, but I am not finding any rows against this SP under sys.dm_exec_procedure_stats. Please suggest what could be various reason for this ...

Is there any method of looking for Head Blocking history via DMVs. I have been able to find the subsequent wait details via Extended Events but there doesn't seem to be much information about what ...

I have been using this script from Kenneth Fisher to get the server permissions. -- Server Permissions SELECT Grantee.principal_id AS GranteePrincipalId, Grantee.name AS GranteeName, Grantor.name ...

what is meaning of last_worker_time and last_elapsed_time in DMV sys.dm_exec_query_stats and what is differnce between them? when I fire below query SELECT TOP 20 qs.last_worker_time, qs....

Related tags

Hot questions

Language

Popular Tags