Archives for January, 2013

Speed up compilation time by using OPTION (RECOMPILE)

Sounds like a contradiction but it works. A client of mine uses lots of views that each hosts a terrible query. Plan cache used up to 2GB (many plans were around 50MB), which seems rather a lot to me (in total 14GB was reserved for buffer pool). More, plans were reused rarely. When running a […]

DBCC OPENTRAN translated using DMV’s

I finally started looking for a decent alternative for the old ‘Sybase’ command to check for open transaction (DBCC OPENTRAN). Here it is: SELECT st.session_id AS [SPID] , DB_NAME(dt.database_id) AS [DB name] , dt.database_transaction_begin_time AS [Started at] , AS [Transaction type] , st.transaction_id AS [Transaction id] FROM sys.dm_tran_database_transactions dt INNER JOIN sys.dm_tran_session_transactions st on […]

Rollback impact on fragmentation

Following script demonstrates the impact of a rollback on fragmentation. Even when you might expect a rollback brings back a database to it’s original state, it does so only at the transactional level and not at the physical level. Although pagesplits are stored in the transactionlog, there are not undone in case of a rollback. […]

Nice tuning example from the field

1.¬†General observations during the first test using loadrunner: – end-user response times very bad – High pressure on TEMPDB – High values for waitstats SOS_SCHEDULER_YIELD and SLEEP_TASK and high values for runnable queues indicating pressure on CPU’s 2. After some investigations we found that – creating/rebuilding statistics,¬†reviewing indexes had no – positive – impact – […]


Suppose you run an SQL trace and see every 20 sec an entry taking up some 40.000 reads with text = #am_get_querystats. This is caused by someone having the Activity Monitor open . So kindly advice him to close his session or hit him.