I know this question has been asked several times and I know the solution, but I am trying to understand the underlying cause of the problem: I have the following code to perform database backups. DECLARE @Filename VARCHAR(256) DECLARE @FileDate VARCHAR(15) DECLARE @Path VARCHAR(50) DECLARE @Name VARCHAR(50) -- specify database backup directory SET @Path = '\MyPath' -- specify filename date SELECT @FileDate = CONVERT(VARCHAR(20), GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR(20), GETDATE(), 108),':','') DECLARE db_cursor CURSOR FOR SELECT [name] FROM master.sys.databases WHERE [name] NOT IN ('master', 'msdb', 'model', 'tempdb') AND [state_desc] = 'ONLINE' OPEN db_cursor FETCH NEXT FROM db_cursor INTO @Name WHILE @@FETCH_STATUS = 0 BEGIN SET @Filename = @Path + @Name + '_Full_Backup_' + @FileDate + '.bak' BACKUP DATABASE @Name TO DISK = @Filename WITH CHECKSUM, COMPRESSION FETCH NEXT FROM db_cursor INTO @Name END CLOSE db_cursor DEALLOCATE db_cursor Sometimes, only some databases are backed up, suggesting that the cursor is not iterating over all of the rows returned or that the query itself is not returning the names of all the databases it should be. I am trying to understand *why* this happens. I know that the fix is to use a STATIC cursor, suggesting the issue is with the results in the underlying query SELECT [name] FROM master.sys.databases WHERE [name] NOT IN ('master', 'msdb', 'model', 'tempdb') AND [state_desc] = 'ONLINE' are changing, but I can't see what would be changing (no database names would change and the Error log doesn't suggest that the database state has changed)

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.dm_exec_requests. Given that sysprocesses is deprecated, I'm curious why anyone would continue to use it, particularly MVPs. Is it just that using sysprocesses is less hassle than joining three DMVs or is there a better reason for using it? The two blog posts I mentioned were: Adam Machanic: Smashing a DMV Myth Tim Chapman: Find blocking processes using recursion in SQL Server 2005

I have a transaction in a KILLED/ROLLBACK state and I want to make sure that sql is not dealing with it, meaning, there is no point in waiting for it to sort itself out. the way I currently find out if the query is stuck is by using a query from our dear Pinal Dave, not sure of the exact link, but I got this query from him.

print @@servername
						SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
						SET NOCOUNT OFF


SELECT spid
,kpid
,login_time
,last_batch
,status
,hostname
,nt_username
,loginame
,hostprocess
,cpu
,memusage
,physical_io
FROM sys.sysprocesses
WHERE cmd = 'KILLED/ROLLBACK'

this works and through it I could conclude my session id is stuck. nothing moves: Also when I run this query here , I get a better picture: It is clear to me that I will have to restart the service. I also checked tempdb

SELECT 
            g.database_id, 
            COUNT(vlf_sequence_number) AS VLF_Count
        FROM sys.dm_db_log_info(NULL) g 
        WHERE g.database_id = DB_ID()
        GROUP BY g.database_id

tempdb transaction log is growing (basically need to deal with it - restart the service) Now it turns out that I would like to stop using sys.processes .

dbcc opentran

kill 61 with statusonly

question (finally) How can I find if a spid is active without using sys.processes? I did not include here on this question my main cause of concern. that would be how long it would take for the sql server service come back online, in this case I am nearly convinced that it would be a quick one, as it was not a long transaction, however, there is an availability group (manual failover, asynchronous commit) in the mix. none of these are part of this question. just mentioning for context.

I am trying to ease the monitoring of availability groups across multiple SQL servers. Came across few dmvs which got me the data. But cannot figure out some important columns which i am seeing in SSMS AG Dashboard report. How can i get this data via SQL query. We cannot use PS unfortunately as its blocked to get data remotely Columns in SSMS dashboard which i cannot find in dmv are below. Do we know from where these metrics are getting populated? > Estimate Data Loss (seconds) > > Estimated Recovery Time (seconds) > > Synchronization Performance (seconds) > > Issues: > > Connection state > > Last connection error no:

This lovely query of Glenn Berry's lets you see what is in your buffer pool.

SELECT fg.name AS [Filegroup Name], SCHEMA_NAME(o.Schema_ID) AS [Schema Name],
OBJECT_NAME(p.[object_id]) AS [Object Name], p.index_id, 
CAST(COUNT(*)/128.0 AS DECIMAL(10, 2)) AS [Buffer size(MB)],  
COUNT(*) AS [BufferCount], p.[Rows] AS [Row Count],
p.data_compression_desc AS [Compression Type]
FROM sys.allocation_units AS a WITH (NOLOCK)
INNER JOIN sys.dm_os_buffer_descriptors AS b WITH (NOLOCK)
ON a.allocation_unit_id = b.allocation_unit_id
INNER JOIN sys.partitions AS p WITH (NOLOCK)
ON a.container_id = p.hobt_id
INNER JOIN sys.objects AS o WITH (NOLOCK)
ON p.object_id = o.object_id
INNER JOIN sys.database_files AS f WITH (NOLOCK)
ON b.file_id = f.file_id
INNER JOIN sys.filegroups AS fg WITH (NOLOCK)
ON f.data_space_id = fg.data_space_id
WHERE b.database_id = CONVERT(int, DB_ID())
AND p.[object_id] > 100
AND OBJECT_NAME(p.[object_id]) NOT LIKE N'plan_%'
AND OBJECT_NAME(p.[object_id]) NOT LIKE N'sys%'
AND OBJECT_NAME(p.[object_id]) NOT LIKE N'xml_index_nodes%'
GROUP BY fg.name, o.Schema_ID, p.[object_id], p.index_id, 
         p.data_compression_desc, p.[Rows]
ORDER BY [BufferCount] DESC OPTION (RECOMPILE);

[Erik Darling offers a similar query](https://github.com/erikdarlingdata/DarlingData/blob/main/Helper%20Views/WhatsUpMemory.sql) . I've ran both against all of my databases **and summed the results**. They both agree that, in total, I'm using about 40 GB of memory for my buffer pool across all of my databases. Yet,

SELECT TOP(10) mc.[type] AS [Memory Clerk Type], 
       CAST((SUM(mc.pages_kb)/1024.0) AS DECIMAL (15,2)) AS [Memory Usage (MB)] 
FROM sys.dm_os_memory_clerks AS mc WITH (NOLOCK)
GROUP BY mc.[type]  
ORDER BY SUM(mc.pages_kb) DESC OPTION (RECOMPILE);

reports that my buffer pool (MEMORYCLERK_SQLBUFFERPOOL) is using 200 GB of memory. These can't both be true at the same time; My buffer pool cannot be both 40 GB in size and 200 GB in size. So what do these two numbers actually mean? Is MEMORYCLERK_SQLBUFFERPOOL how much RAM I'm actually using or is it how much I could be using?

I have a script to show me the replication articles of a publication:

declare @publication_name sysname = 'my_pub'
declare @database_name sysname = 'my_db'
 use [my_db]


select  ServerName = @@servername
       ,publication_name=p.name
       ,p.repl_freq
	   ,p.status
	   ,p.sync_method
	   --,snapshot_job_name=j.name
	   ,p.independent_agent
	   ,p.immediate_sync
	   ,p.allow_anonymous
	   ,p.replicate_ddl
	   ,article_name=OBJECT_SCHEMA_NAME(a.objid) + '.' + OBJECT_NAME(a.objid)
	   ,a.pre_creation_cmd 
	   ,pre_creation_cmd_desc  = CASE a.pre_creation_cmd 
	     WHEN 0 THEN 'none	    - Doesn''t use a command.'
         WHEN 1 THEN 'drop	    - Drops the destination table.'
         WHEN 2 THEN 'delete	- Deletes the destination table.'
         WHEN 3 THEN 'truncate	- Truncates the destination table.'
		 ELSE 'Not known - Radhe Radhe'
		END


			FROM dbo.syspublications P 
			INNER JOIN dbo.sysarticles A 
					ON P.pubid = A.pubid

           left outer  JOIN msdb.dbo.sysjobs j
			        ON j.job_id = p.snapshot_jobid

 where p.name = @publication_name

However, in this particular publication I am working on at the moment, I have also functions and these objects are not shown in my query above? How can I see these, as I know they are there?

I frequently find the output of sp_spaceused misleading. What appears to be the size of all data in a database is actually [the combined size of both the data file and the log file](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-spaceused-transact-sql?view=sql-server-ver16#result-set) . I have recently discovered sys.database_files and I have found it superior in every way. It is so much better than sp_spacedused that I am planning to drink until I forget about that procedure. This give me my question: **When checking the size of a database, is there any feature or return value offered by sp_spacedused that cannot be obtained from sys.database_files?** I've checked over the documentation and I'm pretty sure that there isn't.

Is there a way to keep the connection history to a certain table in SQL-server? Not just the database because that's easy using the DMV's but just one particular table.

I am trying to monitor cubes and expressions of Analysis Services using DMV queries that are stored on Server's RAM. I found many useful information about connections on connection's table of SSMS's DMVS rowser using the following mdx statement. SELECT * FROM $SYSTEM.DISCOVER_CONNECTIONS The problem is that the [CONNECTION_HOST_APPLICATION] field of connection's table is not recorded and contains null values for my client application. > CONNECTION_HOST_APPLICATION specifies "The name of the machine that initiated the connection". As a result i can not filter the rows that are generated from my client tool to analyse only them. So how can i modify my client tool in order to register this field?

I need to fetch the max and min values of an arbitrary column on a table. The table has a clustered columnstore-index, billions of rows, and hundreds of columns. I'd like to leverage that fact that every columnstore segment already has its min and max value recorded in the sys.column_store_segments table's min_data_id and max_data_id columns. It shouldn't be necessary to access the actual table, as the columnstore segment metadata has sufficient information. But it appears that the min_data_id and max_data_id columns cannot be trusted to contain the max and min values because they may also contain references into a dictionary instead. Is there any way that I can determine whether min_data_id and max_data_id are real values vs dictionary references? If they are references, is it possible for me to access the underlying dictionary? When I try using MIN and MAX, the queries can take minutes to complete, making me think that metadata table is not being used. References: https://learn.microsoft.com/en-us/archive/technet-wiki/5651.understanding-segment-elimination https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-column-store-segments-transact-sql?view=sql-server-ver16

I am attempting to prove whether the plan cache is caching statements individually within multiple stored procedures with identical statements. I thought I would be able to use parent_plan_handle to see this relationship however it is always returning null. I've checked this on both SQL 2019 and 2022. When I consult the literature , the column is strangely absent. The question: What is the purpose of parent_plan_handle in sys.dm_exec_cached_plans?

I have a stored procedure called sp_radhe that I put on my servers and it has been helping me to "see" what is happening internally. here is the code of this stored procedure: USE [master] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --DROP PROCEDURE dbo.sp_radhe GO CREATE PROCEDURE dbo.sp_radhe AS /* ======================================================================= Script : SP_RADHE Author : Marcelo Miorelli Date : 04 MAR 2013 Wednesday Desc : shows the current processes Usage : sp_radhe -- same as sp_who2 ======================================================================= History Date Action User Desc ----------------------------------------------------------------------- 27-oct-2014 changed Marcelo Miorelli commented out the line --and es.status = 'running' so the procedure returns any es.status ======================================================================= */ --====================================== -- describe primary blocks of processing --====================================== ------------------------------------------------ -- describe action of logical groups of commands ------------------------------------------------ -- describe individual actions within a command set BEGIN SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT es.session_id AS session_id ,COALESCE(es.original_login_name, 'No Info') AS login_name ,COALESCE(es.host_name,'No Info') AS hostname ,COALESCE(es.last_request_end_time,es.last_request_start_time) AS last_batch ,es.status ,COALESCE(er.blocking_session_id,0) AS blocked_by ,COALESCE(er.wait_type,'MISCELLANEOUS') AS waittype ,COALESCE(er.wait_time,0) AS waittime ,COALESCE(er.last_wait_type,'MISCELLANEOUS') AS lastwaittype ,COALESCE(er.wait_resource,'') AS waitresource ,coalesce(db_name(er.database_id),'No Info') as dbid ,COALESCE(er.command,'AWAITING COMMAND') AS cmd ,sql_text=st.text ,transaction_isolation = CASE es.transaction_isolation_level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'Read Uncommitted' WHEN 2 THEN 'Read Committed' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' END ,COALESCE(es.cpu_time,0) + COALESCE(er.cpu_time,0) AS cpu ,COALESCE(es.reads,0) + COALESCE(es.writes,0) + COALESCE(er.reads,0) + COALESCE(er.writes,0) AS physical_io ,COALESCE(er.open_transaction_count,-1) AS open_tran ,COALESCE(es.program_name,'') AS program_name ,es.login_time FROM sys.dm_exec_sessions es LEFT OUTER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id LEFT OUTER JOIN sys.dm_exec_requests er ON es.session_id = er.session_id LEFT OUTER JOIN sys.server_principals sp ON es.security_id = sp.sid LEFT OUTER JOIN sys.dm_os_tasks ota ON es.session_id = ota.session_id LEFT OUTER JOIN sys.dm_os_threads oth ON ota.worker_address = oth.worker_address CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st where es.is_user_process = 1 and es.session_id @@spid --and es.status = 'running' GO exec sys.sp_MS_marksystemobject 'sp_radhe' GO However, when a process is being blocked by some other process that is not active, I am struggling to figure it out the T-SQL of the blocking code. For example: the session 92 you can see on the above picture is a select and the session 75 is an update that I left the transaction open. **session 92** SET TRANSACTION ISOLATION LEVEL SERIALIZABLE SELECT TOP 1000 [accountID] ,[accountCreateKeyID] ,[totalAccountCreditValueLocal] ,[accountCreateDate] ,[createdDate] ,[createdBy] ,[modifiedDate] ,[modifiedBy] FROM [TableBackups].[dbo].[_AO20150806_crm_build_account_DoNotDelete] WITH (HOLDLOCK) **session 75** BEGIN TRANSACTION T1 SELECT @@TRANCOUNT update [TableBackups].[dbo].[_AO20150806_crm_build_account_DoNotDelete] set [totalAccountCreditValueLocal] = 1000 where accountID = 1 How can I find the code of the blocking session, in this case, the session 75 when the session's status is 'sleeping'? **New version** this new version shows also the blocking session, however, I could not find out how to get the database name and other data from a sleeping session. SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT es.session_id AS session_id ,COALESCE(es.original_login_name, 'No Info') AS login_name ,COALESCE(es.host_name,'No Info') AS hostname ,COALESCE(es.last_request_end_time,es.last_request_start_time) AS last_batch ,es.status ,COALESCE(er.blocking_session_id,0) AS blocked_by ,COALESCE(er.wait_type,'MISCELLANEOUS') AS waittype ,COALESCE(er.wait_time,0) AS waittime ,COALESCE(er.last_wait_type,'MISCELLANEOUS') AS lastwaittype ,COALESCE(er.wait_resource,'') AS waitresource ,coalesce(db_name(er.database_id),'No Info') as dbid ,COALESCE(er.command,'AWAITING COMMAND') AS cmd ,sql_text=st.text ,transaction_isolation = CASE es.transaction_isolation_level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'Read Uncommitted' WHEN 2 THEN 'Read Committed' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' END ,COALESCE(es.cpu_time,0) + COALESCE(er.cpu_time,0) AS cpu ,COALESCE(es.reads,0) + COALESCE(es.writes,0) + COALESCE(er.reads,0) + COALESCE(er.writes,0) AS physical_io ,COALESCE(er.open_transaction_count,-1) AS open_tran ,COALESCE(es.program_name,'') AS program_name ,es.login_time FROM sys.dm_exec_sessions es LEFT OUTER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id LEFT OUTER JOIN sys.dm_exec_requests er ON es.session_id = er.session_id LEFT OUTER JOIN sys.server_principals sp ON es.security_id = sp.sid LEFT OUTER JOIN sys.dm_os_tasks ota ON es.session_id = ota.session_id LEFT OUTER JOIN sys.dm_os_threads oth ON ota.worker_address = oth.worker_address CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st where es.is_user_process = 1 and es.session_id @@spid UNION SELECT es.session_id AS session_id ,COALESCE(es.original_login_name, 'No Info') AS login_name ,COALESCE(es.host_name,'No Info') AS hostname ,COALESCE(es.last_request_end_time,es.last_request_start_time) AS last_batch ,es.status ,COALESCE(er.blocking_session_id,0) AS blocked_by ,COALESCE(er.wait_type,'MISCELLANEOUS') AS waittype ,COALESCE(er.wait_time,0) AS waittime ,COALESCE(er.last_wait_type,'MISCELLANEOUS') AS lastwaittype ,COALESCE(er.wait_resource,'') AS waitresource ,coalesce(db_name(er.database_id),'No Info') as dbid ,COALESCE(er.command,'AWAITING COMMAND') AS cmd ,sql_text=st.text ,transaction_isolation = CASE es.transaction_isolation_level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'Read Uncommitted' WHEN 2 THEN 'Read Committed' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' END ,COALESCE(es.cpu_time,0) + COALESCE(er.cpu_time,0) AS cpu ,COALESCE(es.reads,0) + COALESCE(es.writes,0) + COALESCE(er.reads,0) + COALESCE(er.writes,0) AS physical_io ,COALESCE(er.open_transaction_count,-1) AS open_tran ,COALESCE(es.program_name,'') AS program_name ,es.login_time FROM sys.dm_exec_sessions es INNER JOIN sys.dm_exec_requests ec2 ON es.session_id = ec2.blocking_session_id LEFT OUTER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id LEFT OUTER JOIN sys.dm_exec_requests er ON es.session_id = er.session_id LEFT OUTER JOIN sys.server_principals sp ON es.security_id = sp.sid LEFT OUTER JOIN sys.dm_os_tasks ota ON es.session_id = ota.session_id LEFT OUTER JOIN sys.dm_os_threads oth ON ota.worker_address = oth.worker_address CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) AS st where es.is_user_process = 1 and es.session_id @@spid

Regarding the query below which I run on a physical server with SQL Server 2019: SELECT memory_node_id, SUM(pages_kb) / 1024.0/1024 AS TotalMemoryGB FROM sys.dm_os_memory_clerks GROUP BY memory_node_id ORDER BY memory_node_id; for memory_node_id = 0 it shows more memory than physically available on the NUMA node. How can that be?

I had a look using the replication monitor and it is showing me commands that are awaiting to be replicated: My question is - how do I find out what these commands are? I Have tried this query by Ben Anderson select @@Servername,a.publication + ':' + s.srvname as [Agent], v.undelivcmdsindistdb, v.delivcmdsindistdb, a.id from distribution.dbo.msdistribution_status v join distribution.dbo.msdistribution_agents a on v.agent_id = a.id join master.dbo.sysservers s on a.subscriber_id = s.srvid where v.undelivcmdsindistdb > 0 But the number does not exactly match, although it is definitely a good start, is there a way I can find more info about these records?

I am doing some DMV queries on SQL Server Analysis Service, like SELECT * FROM $system.DISCOVER_SESSIONS. This query will return data for all databases on the server, so it's not really database-specific. When I connect to the server through AdomdConnection , I don't specify the database/catalog in the connection string, and the query still works. However I noticed that a database is always associated with this connection, and it's usually the first database on the list of available ones on the server. My question is - do these kind of queries actually burden the database in question, i.e., if for whatever reason we would have many DMV queries that check the server sessions, do they all query the automatically-chosen first database on the list, and therefore impact (performance-wise) any users who might actually do "real" queries on this database? - Or is this kind of DMV query just impacting the server itself, not in any way the database and therefore the performance impact is the same no matter which database is chosen? **Possibly a shorter way of phrasing it would be:** are performance impacts of a DMV query server-related or database-related?

how can I find the list of databases I actually can connect to? I am using sys.master_files to check the state of my database files however, on the secondary, the database is synchronizing and it is showing as ONLINE I cannot do this: use my_database and I cannot do this: select * from my_database.sys.master_files > Msg 976, Level 14, State 1, Line 364 The target database, 'my_database', > is participating in an availability group and is currently not > accessible for queries. Either data movement is suspended or the > availability replica is not enabled for read access. To allow > read-only access to this and other databases in the availability > group, enable read access to one or more secondary availability > replicas in the group. For more information, see the ALTER > AVAILABILITY GROUP statement in SQL Server Books Online.

I have been under the misconception that SELECT * FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'Stolen Server Memory%'; shows how much memory the OS is stealing from SQL Server. The truth, according to [the documentation](https://learn.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-memory-manager-object?view=sql-server-ver16#memory-manager-counters) , is > Specifies the amount of memory the server is using for purposes other than database pages. which I take to mean "the amount of memory used by SQL Server (i.e. not the OS) for anything other than the buffer pool". With this in mind, is there any point in checking sys.dm_os_performance_counters if I am already checking SELECT * FROM sys.dm_os_memory_clerks ORDER BY pages_kb DESC? Given that the buffer pool is always at the top of that list (your server is doomed if it isn't), determining how much memory isn't being used by it should be a simple matter of arithmetic. I suspect that I am wrong about this because these two numbers do not match on my server. However, I do not know how to find out why.

-- Total stolen memory.
SELECT top (1) cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE 'Stolen Server Memory%'
ORDER BY cntr_value desc;

-- Total memory not in buffer pool.
-- Should be the same as the above but is always less on my machine.
SELECT SUM(pages_kb)
FROM sys.dm_os_memory_clerks
WHERE [Type]  'MEMORYCLERK_SQLBUFFERPOOL';

Simple query on sys.dm_db_partition_stats on user database is running for close to 30 mins whereas it used to complete in less than 5 seconds earlier. Query is just to fetch the right partition for loading the data and is not reading any data from user databases. Same query ran and completed in less than 5 seconds on any other large user database. Looking at the execution plan for this db and query output messages, we observed for one Table 'sysrowsets' . Scan count 1, logical reads is hundreds of thousands whereas on other databases, it is less than a thousand. Here is the output for reference. > Table 'sysrowsets'. Scan count 888, logical reads 3227880, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0. We are unable to query this sysrowsets directly as it is an underlying table. Able to query when connected via DAC but couldn't find anything abnormal from this. Still wondering what the reason for this many reads is and why is it taking such long time. SQL Server Version: 2019, Compatibility Level: 150 SELECT Max(pstats.partition_number) AS PartitionNumber FROM sys.dm_db_partition_stats AS pstats(NOLOCK) INNER JOIN sys.destination_data_spaces AS dds(NOLOCK) ON pstats.partition_number = dds.destination_id INNER JOIN sys.partition_schemes AS ps(NOLOCK) ON dds.partition_scheme_id = ps.data_space_id INNER JOIN sys.partition_functions AS pf(NOLOCK) ON ps.function_id = pf.function_id LEFT JOIN sys.partition_range_values AS prv(NOLOCK) ON pf.function_id = prv.function_id AND pstats.partition_number = ( CASE pf.boundary_value_on_right WHEN 0 THEN prv.boundary_id ELSE (prv.boundary_id + 1) END ) WHERE OBJECT_Name(pstats.object_id) = 'OBJECTNAME' AND OBJECT_SCHEMA_NAME(pstats.object_id) = 'DBNAME' Partitions are equally created on all the user databases. Not too many tables were created on the database recently. Adding to that, mentioned query is to fetch data from only system DMVs and no table from user databases is being read. Hence, I assume there is no scope to add indexes for performance improvements. Also, this behavior is being observed all of a sudden since last 4 days. We restored last week full backup and tested with same query and got results in less than 5 seconds. No significant changes or upgrades are done to system or database.

I have a query that shows me how much memory sql server is using, the settings like max memory and min memory, cpu, etc. here is the query:

--============================================================
--checking current memory settings and usage
--marcelo miorelli
--16-feb-2014
--============================================================
			   
SELECT R.[instance],
       R.[Logical CPU Count],
	   R. [Hyperthread Ratio],
	   R.[Physical CPU Count],
	   R.[Physical Memory (GB)],
	   k.[Max server memory (GB)],
	 FOO.Memory_usedby_Sqlserver_MB,
	 FOO.Locked_pages_used_Sqlserver_MB,
	   k.[Min server memory (GB)],
	   k.[optimize for ad hoc workloads],
	   R.affinity_type_desc,
	FOO.process_physical_memory_low,
	FOO.process_virtual_memory_low,
	   R.virtual_machine_type_desc,
	   R.sqlserver_start_time
FROM (

				SELECT [instance]=@@servername,
					   cpu_count AS [Logical CPU Count], 
					   hyperthread_ratio AS [Hyperthread Ratio],
				cpu_count/hyperthread_ratio AS [Physical CPU Count], 
				CONVERT(decimal(12,2),physical_memory_kb/1024.00/1024.00) AS [Physical Memory (GB)], 
				affinity_type_desc, 
				virtual_machine_type_desc, 
				sqlserver_start_time
				FROM sys.dm_os_sys_info WITH (NOLOCK) 
				

) R 
INNER JOIN (


				SELECT [instance] = @@servername,
				       [Max server memory (GB)]   = CONVERT(decimal(12,2),CAST(p.[max server memory (MB)] AS DECIMAL(12,2))/1024.00),
					   [Min server memory (GB)]   = CONVERT(decimal(12,2),CAST(P.[min server memory (MB)] AS DECIMAL(12,2))/1024.00),
					   [min memory per query (MB)]= CONVERT(decimal(12,2),CAST(P.[min memory per query (KB)] AS DECIMAL(12,2))/1024.00),
					   p.[optimize for ad hoc workloads]

				FROM ( SELECT name, [value_in_use] 
				         FROM sys.configurations) t 
						PIVOT (MAX([value_in_use]) 
							   FOR name IN (
											 [min server memory (MB)], 
											 [min memory per query (KB)], 
											 [max server memory (MB)], 
											 [optimize for ad hoc workloads]
											)) p


			) K
ON R.instance = K.instance

INNER JOIN (

							SELECT [instance] = @@servername,
							CONVERT(decimal(12,2),CAST(physical_memory_in_use_kb AS DECIMAL(12,2))/1024.00) AS Memory_usedby_Sqlserver_MB,
							CONVERT(decimal(12,2),CAST(locked_page_allocations_kb AS DECIMAL(12,2))/1024.00) AS Locked_pages_used_Sqlserver_MB,
							CONVERT(decimal(18,2),CAST(total_virtual_address_space_kb AS DECIMAL(18,2))/1024.00/1024.00) AS Total_VAS_in_GB,
							process_physical_memory_low,
							process_virtual_memory_low
							FROM sys.dm_os_process_memory t
		

           ) FOO
ON R.instance = foo.instance
OPTION (RECOMPILE);

this query gives me this - currently on a not busy dev server: My question is related to physical_memory_in_use_kb and locked_page_allocations_kb from the dmv sys.dm_os_process_memory. How can I find what objects or whatever sql server is holding in that memory? I can see that in my case there is a minimum memory set to 2 Gb but the memory in use is over 5Gb. It might as well be that I actually do not need to know as there is probably no memory pressure at the moment, but still, is there a way to find out what sql server is holding in there memory?

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.dm_exec_sql_text(req.sql_handle) AS sqltext However, this only displays the text of the stored procedure's create statement. e.g.: CREATE PROCEDURE IMaProcedure @id int AS SELECT * FROM AllTheThings Where id = @id Ideally I'd like to see what the parameters were for the running procedure that are causing it to run so long for the particular set of offending parameters. Is there a way to do that? (In this question Aaron Bertrand mentions DBCC InputBuffer , but I don't think that's appropriate for this problem.)