SQL Server Temp Table Statistics

The statistics of temp tables may affect the query plan.



SQL Server Statistics

-- general information
select *
from sys.stats s
apply sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
join sys.stats_columns c
  on s.object_id = c.object_id
 and s.stats_id = c.stats_id
where s.object_id = object_id(N'dbo.MyTable')

dbcc show_statistics (N'dbo.MyTable', 'MyStatName')


SQL Server: check snapshot

rtrim(ltrim(str(elapsed_time_seconds/3600)))+' Hours '
+rtrim(ltrim(str((elapsed_time_seconds/60)%60)))+' Minutes'
as 'row versioning since',
sys.dm_tran_active_snapshot_database_transactions trn
inner join sys.dm_exec_sessions ses on ses.session_id=trn.session_id
JOIN sys.dm_exec_connections c
ON ses.[session_id] = c.[session_id]
LEFT OUTER JOIN sys.dm_exec_requests R
ON ses.[session_id] = R.[session_id]
CROSS APPLY sys.dm_exec_sql_text (c.[most_recent_sql_handle]) AS st
OUTER APPLY sys.dm_exec_query_plan (R.[plan_handle]) AS qp
ORDER BY elapsed_time_seconds DESC;


tsql – consolidating date range

-- ref: https://dba.stackexchange.com/questions/100965/combining-separate-ranges-into-largest-possible-contiguous-ranges
with a as
select StartDate
, EndDate
, max(EndDate) over (order by StartDate) EndDate2
from tablex
where ObjectId = '0P000196KE'
and IdType = 1
and DataGroupType = 4
and MessageType = 2
and cast(UpdateTime as date) = '2018-04-18'
, b as
select *
, lag(EndDate2) over (order by StartDate) EndDate3
from a
, c as
select *
, case when EndDate3 + 1 < StartDate then EndDate3 else null end step
from b
, d as
select *
, count(step) over (order by StartDate) as grp
from c
select min(StartDate)
, max(EndDate2)
from d
group by grp
order by 1

Execute the same query on all databases in an instance (Alternative to sp_MSForEachDB)

Rick Bielawski

Although many people know Microsoft includes an undocumented stored procedure (sp_MSForEachDB) that looks like an ideal solution to the problem of executing the same query in every database on an instance there are a long list of drawbacks, not the least of which is that it is undocumented and therefore ill-advised to be used on production systems. That procedure uses a deprecated compatibility view in conjunction with a cursor to iterate over the databases and a temporary table in conjunction with a while loop to perform token replacement making performance pretty bad. Some other issues that sp_MSForEachDB as well as many ‘replacements’ you find on the web also suffer from are:

  • A separate result set returns for each database making sorting or use with the Registered Servers feature of SSMS harder.
  • You can’t apply criteria to exclude databases such as read-only or offline ones.
  • The replacement token is limited to…

View original post 1,050 more words