March 28, 2012 at 9:55 am
SQL instance names are SQLSERVER1\foundation and SVBerry. Both are 2008 R2 10.50.1600. The query grabs to 10 queries for each db and stores it into another table in a DB DBA_Perform. It run find on SVBerry but error on Sqlserver1\foundation with the following error:
Msg 102, Level 15, State 1, Line 19
Incorrect syntax near '.'.
Would the server\instancename be issue? I am lost on why it would run fine on one but not the other.
/* Retain an entire week of data only */
Delete
from DBA_Perform.dbo.Top_10_Queries
where Date_Collected < Getdate() -7;
exec master..sp_MSforeachdb
'if ''?'' in (''master'',''model'',''msdb'',''tempdb'',''Adventureworks'') return
use [?]
/* Store the results into the Top_10_Queries Table */
Insert into DBA_Perform.dbo.Top_10_Queries
SELECT TOP 10
qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,
qs.total_elapsed_time / 1000000.0 AS total_seconds,
qs.execution_count,
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) AS individual_query,
o.name AS object_name,
DB_NAME(qt.dbid) AS database_name,
Getdate() as Date_Collected
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
where qt.dbid = DB_ID();'
March 28, 2012 at 10:20 am
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
if the database you are executing the query from is not compatibility 9 or above, i think that will fail.
Lowell
March 28, 2012 at 11:35 am
Yep ran query and have 10db in 100 3 in 90 and 1 in 80 mode. I will exclude that DB for now until I find out if it is ok to move up. Thanks...
March 28, 2012 at 11:52 am
ok two questiosn for you:
you are getting TOP 10 from each database with no ORDER BY...were you trying to get slow running queries? ie the ones with a long elapsed time? no ORDER BY = Random queries...probably not what you wanted.
you can change the query to get all the data in a single pass, by running it in master and using the second parameter for object_name(id, Database_id):
SELECT * from (
select ROW_NUMBER() over (PARTITION BY qt.dbid order by qs.total_elapsed_time DESC) AS RW,
qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,
qs.total_elapsed_time / 1000000.0 AS total_seconds,
qs.execution_count,
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) AS individual_query,
object_name(qt.objectid,qt.dbid) as name,
DB_NAME(qt.dbid) AS database_name,
Getdate() as Date_Collected
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
)myAlias
where RW <=10
order by database_name,rw
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply