ARCHITECTING MICROSOFT SQL SERVER ON VMWARE VSPHERE®

https://www.vmware.com/content/dam/digitalmarketing/vmware/en/pdf/solutions/sql-server-on-vmware-best-practices-guide.pdf

Advertisements

SQL Server: check snapshot


SELECT
ses.original_login_name,
trn.session_id,trn.transaction_id,
rtrim(ltrim(str(elapsed_time_seconds/3600)))+' Hours '
+rtrim(ltrim(str((elapsed_time_seconds/60)%60)))+' Minutes'
as 'row versioning since',
[host_name]--,last_request_start_time,last_request_end_time
,st.[text]
FROM
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;

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/95671c2e-488c-4ef2-b488-c29fab891e1c/impact-of-using-snapshot-isolation-and-readcommittedsnapshot-will-tempdb-grow-significantly?forum=sqldatabaseengine

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