Category archives for Uncategorized

MEMORYCLERK_XE and Sharepoint

Yesterday I was called at a customer complaining about a performance issue. Sounds familiar? The current activity showed me lots of processes waiting for resource semaphores. The first line (session_id 600) caught my attention. This is a SQL maintenance job to check integrity of the system databases. Job history showed this process took a few seconds […]

What does it cost you to compress your data online?

Where there is often lots of info on compression available everywhere, it’s often much harder to find info what the cost is related to online vs offline page compression. So I did a test. I have a 10GB filegroup containing a 10GB heap on it (no indexes), data and log files have autogrowth set to […]

Check scheduler_id’s for parrallel queries using Extended Events

Trying to be good DBA’s, we followed the rule to set the maxdop = number of schedulers per NUMA node. I now want to check how often parallel queries can use different schedulers within the same NUMA node. Following scripts works fine for me. select DISTINCT session_id , scheduler_id from sys.dm_os_tasks WHERE parent_task_address IS NOT […]

Transactionlog lost

So what if you lose the disk that holds your transaction logs – and all database backup files. Of course, there is no DR plan available (if you put transaction log and backup files on the same disk, DR is clearly no priority). There are 3 options here Plan A: If your database was stopped […]

Database is in transition

When trying to put some old SQL2000 databases offline using my SQL2012 SSMS, there was one that generated all kinds of unexpected errors. However the state of the DB stated running, no one could connect. Any connection using USE <DBName> generated  an error …Database is in transition… Turns out I was causing the problem myself. […]

Query cost not reliable when using FullText search

I activated fulltext search on a database and wanted to test following 3 queries: SELECT c1 as [key] FROM [dbo].[TableFullTextSearch] WHERE [DocContent] LIKE ‘%uncurbed%’; SELECT c1 as [key] FROM [dbo].[TableFullTextSearch] WHERE CONTAINS([DocContent], ‘uncurbed’); SELECT [key] FROM CONTAINSTABLE ([TableFullTextSearch], [DocContent], ‘uncurbed’); A Clustered index was put on [c1], a fulltext index was put on [DocContent]. Results […]

CHECKPOINT generates timeout

On my SQL2008R2 (RTM) instance I get following timeout error after 5 minutes when running a manual CHECKPOINT in TEMPDB: Msg 845, Level 17, State 1, Line 2 Time-out occurred while waiting for buffer latch type 3 for page (1:763), database ID 2. After inspecting waittimes, it appeared it was waiting for a PAGELATCH_UP on […]

What happens if the Version Stored gets filled completely

What happens if your version store in TEMPDB fills up? Opposite to transactionlog behavior (Where the process that fills the tranlog is rolled back), the versionstore doesn’t penalize the process responsible for generating the versions, but rather the poor select statement that needs to read from the version store. How unfair… The script hereunder demonstrates […]

Federations in SQL Azure

Playing around with SQL Azure for some days now. I was hoping to find a solution for the never ending story of migrating old databases where nobody remembers what these databases are used for let alone how the client application behind it works. SQL Azure by definition would solve this issue together with lots of […]

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.  

Data Corruption on CDC enabled DB

We were recently blessed with data corruption on a system table within a user database. The corruption was 5 days old when we noticed it (don’t ask why) and each process that triggered a checksum failed. Result: no backups more recent that 1 week; page level restore was no option. An additional level of complexity […]

SQL 2012 slower than SQL 2008 on large boxes

We just got our new 32-core server to get SQL 2012 installed on it. Coming from a 16-core SQL 2008 server, our expectations were high. BUT, turned out that results of our first set of performance tests were not what we expected. After some investigations at system level we found that the server had 4 NUMA nodes […]

Truncate table process blocked by select with(nolock)

Where you would expect that writers don’t get blocked by readers using dirty reads, this is not the case when the writer executes a TRUNCATE TABLE command. This is because a truncate is regarded as a DDL operation (not DML). More technically, a select statement holds a sch-s(schema stability) lock (even using isolation level read uncommitted) […]

Memory Grant Mystery

“Add more memory” was in the early days of SQL server often the solution to all problems, and for some people, it still is. But even if you think there is a memory bottleneck, adding more RAM isn’t always solving the issue. When inspecting the sys.dm_exec_query_memory_grants view, we noticed one session was queued because SQL […]

TEMPDB and buffer cache

Suppose you notice write latency on TEMPDB is well above acceptable values (in our case 150ms). What can you do? First you need to know TEMPDB is not like any other user database (because it’s a system DB). There is no automated checkpoint process writing your dirty pages to disk. Only if your transactionlog fills […]

Read the Transaction Log for Page Splits

select AllocUnitName, count([AllocUnitName]) [Splits] from ::fn_dblog(null, null) where Operation = N’LOP_DELETE_SPLIT’ and parsename(AllocUnitName,3) <> ‘sys’ group by AllocUnitName




Sometimes, you click on an xml link representing a queryplan in the resultset of SSMS but the graphical representation doesn’t show. Save the XML file as .SQLPLAN file and open it with SSMS.

Full Tranlog on a CDC enabled DB

I was finally able to simulate my problem with the full tranlog on a CDC enabled DB. I created one table and enabled it for CDC. –          BEGIN TRAN –          INSERT records into this table filling up the tranlog to 95%. (not 100% because this will rollback the transaction and space in the tranlog can […]

Unable to truncate transactionlog with CDC

The other day I ran into following problem; A DB which was CDC enabled, started to fill up the transaction log to an extent where a translog backup could not run due to lack of space. So I performed a remote tranlog backup (using UNC path as destination for my backup file), in order for […]

Missing join predicate

A simple typo can have serious consequences. SELECT * FROM t1 INNER JOIN t2 ON t1.c1=t1.c1 Generates a ‘missing join predicate’ warning and a Cartesian product. In our case filling up 500GB TEMPDB space and a never ending query. Some good advice, check your query plan for ‘missing join predicate’ warnings.