SQL Server 2005 Query History
The data in sys.dm_exec_query_stats
is reset every time SQL Server is restarted. So if you're only seeing a day's worth of data, the server must have been reset about that long ago.
Is there a way for viewing the recent expensive queries in Sql Server 2005?
You can view recent expensive queries with this... I use it all the time, it has far more detail than Activity Monitor.
NB: This is for cached plans only.
SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
--ORDER BY qs.total_logical_reads DESC -- logical reads
--ORDER BY qs.total_logical_writes DESC -- logical writes
ORDER BY qs.total_worker_time DESC -- CPU time
Source
TSQL: Get Last Queries Ran
Yes, take a look, this will give you the 50 most recent executed SQL statements
sql 2005 and up only
SELECT TOP 50 * FROM(SELECT COALESCE(OBJECT_NAME(s2.objectid),'Ad-Hoc') AS ProcName,
execution_count,s2.objectid,
(SELECT TOP 1 SUBSTRING(s2.TEXT,statement_start_offset / 2+1 ,
( (CASE WHEN statement_end_offset = -1
THEN (LEN(CONVERT(NVARCHAR(MAX),s2.TEXT)) * 2)
ELSE statement_end_offset END)- statement_start_offset) / 2+1)) AS sql_statement,
last_execution_time
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 ) x
WHERE sql_statement NOT like 'SELECT TOP 50 * FROM(SELECT %'
--and OBJECTPROPERTYEX(x.objectid,'IsProcedure') = 1
ORDER BY last_execution_time DESC
List the queries running on SQL Server
This will show you the longest running SPIDs on a SQL 2000 or SQL 2005 server:
select
P.spid
, right(convert(varchar,
dateadd(ms, datediff(ms, P.last_batch, getdate()), '1900-01-01'),
121), 12) as 'batch_duration'
, P.program_name
, P.hostname
, P.loginame
from master.dbo.sysprocesses P
where P.spid > 50
and P.status not in ('background', 'sleeping')
and P.cmd not in ('AWAITING COMMAND'
,'MIRROR HANDLER'
,'LAZY WRITER'
,'CHECKPOINT SLEEP'
,'RA MANAGER')
order by batch_duration desc
If you need to see the SQL running for a given spid from the results, use something like this:
declare
@spid int
, @stmt_start int
, @stmt_end int
, @sql_handle binary(20)
set @spid = XXX -- Fill this in
select top 1
@sql_handle = sql_handle
, @stmt_start = case stmt_start when 0 then 0 else stmt_start / 2 end
, @stmt_end = case stmt_end when -1 then -1 else stmt_end / 2 end
from sys.sysprocesses
where spid = @spid
order by ecid
SELECT
SUBSTRING( text,
COALESCE(NULLIF(@stmt_start, 0), 1),
CASE @stmt_end
WHEN -1
THEN DATALENGTH(text)
ELSE
(@stmt_end - @stmt_start)
END
)
FROM ::fn_get_sql(@sql_handle)
See when a stored procedure was second last modified/altered?
You could try the Schema Changes History report from SQL Server Management Studio.
Right click on database - Reports - Standard Reports - Schema Changes History
What is the best way to store historical data in SQL Server 2005/2008?
it DEPENDS on the applications usage patterns... If usage patterns indicate that the historical data will be queried more often than the current values, then put them all in one table... But if Historical queries are the exception, (or less than 10% of the queries), and the performance of the more common current value query will suffer from putting all data in one table, then it makes sense to separate that data into it's own table...
Related Topics
Update X Set Y = Null Takes a Long Time
Compare Current Row with Previous Row in SQL Server
Recommended Method to Import a .Csv File into Microsoft SQL Server 2008 R2
Replacing Variable Length String with Some Word
There Is Already an Object Named '##Temp' in The Database
String Equivalent of Sum to Concatenate
I Am Trying to Copy a File, But Getting Error Message
Fillfactor for a Sequential Index That Is Pk
Unique Date Range Fields in SQL Server 2008
Sql Server Stored Procedure and Execute in VB.NET
Executing SQL Query on Multiple Databases
Oracle Text Escaping with Curly Braces and Wildcards
Check That a List Parameter Is Null in a Spring Data JPA Query
Select All Projects That Have Matching Tags