April 4, 2010 at 11:12 pm
Does the 'sp_msforeachdb' just return results run against each database?
April 5, 2010 at 7:09 am
Not totally sure about the question but sp_MSforeachdb allows to execute a T-SQL statement against all databases in the SQL Server instance.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 5, 2010 at 7:20 am
jsb12 (4/4/2010)
Does the 'sp_msforeachdb' just return results run against each database?
The Stored Procedure (SP), "sp_MSforeachtable," allows you to easily process some code against every table in a single database. and the SP, "sp_MSforeachdb," will execute a T-SQL statement against every database associated with the current SQL Server instance.Go through each of these Stored Procedures in a little more detail. click here😉
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 5, 2010 at 9:04 am
thank you Paul and Bhuvnesh.
The results for this are same when executed from any database. So sp_msforeachdb may not be the choice for me
Actually this was what I was trying.
The results of this script are top 20 per instance. I needed top 20 per database.
SELECT TOP 20
[Average IO] = (total_logical_reads + total_logical_writes) / qs.execution_count
,[Total IO] = (total_logical_reads + total_logical_writes)
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average IO] DESC;
When I removed top 20 from select I got a big result set. How do I get the top 20 results per database from the resultset?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply