Exotic wait types: PREEMPTIVE_OS_ENCRYPTMESSAGE

Some time ago, we were discussing wait types with a group of customers. To demonstrate the waits-and-queues methodology we use, I showed the group the sys.dm_os_wait_stats DMV, and the results were sorted by wait_time_ms in descending order. On the top of that list was the PREEMPTIVE_OS_ENCRYPTMESSAGE wait type. At that moment I was not exactly…

0

Too many files causing RAM pressure? (a.k.a. DynCache to the rescue!)

Readers of this blog might recall a previous post which described one impact of having too many similarly named files in one folder in NTFS. It turns out that the 8.3 naming convention is not the only thing you need to worry about when you have very large amounts of (smaller) files in the same…

3

Tracking TEMPDB internal object space usage in SQL 2012

It is a documented fact that using the MAX specifier can result in TEMPDB usage in specific cases. From the TEMPDB whitepaper, some of these can use ‘internal object’ space within TEMPDB: “all of the large object types: text, image, ntext, varchar(max), varbinary(max), and all others.” Now, a FAQ from our customers is how to…

2

Indexed views with remote tables–possible?

Once upon a time, I had this question come up during an onsite visit: someone wanted to create a ‘materialized’ copy of the remote data locally as an indexed view. I had never heard that requirement before, but since the motivation was genuine, I decided to validate it. There is a long list of what…

0

SQL Server and Storage / SAN replication (mirroring) tips

Some customers have adopted block level replication of the storage disks (LUNs) as their DR solution for SQL Server databases. Remote mirroring of storage disks sounds great – transparent to the application, replicates every block as-is; so very little reconfiguration required on the other side, scales fairly linearly regardless of the number of databases and…

5

Getting worker thread IDs using DMVs

In SQL 2000, the kpid column in sysprocesses was a convenient way to find out the actual worker thread ID (OS level thread identifier) for a given task. How does one do this using the DMVs? Here is a quick way: select R.Session_Id, Th.os_thread_id from sys.dm_exec_requests R join sys.dm_exec_sessions S on R.session_id = S.session_id join…

1

Priority boost details – and why it’s not recommended

Some times, we see customer has (accidentally or otherwise) enabled the option ‘boost priority’ for SQL Server worker threads. In general Microsoft does not recommend that you set this option. Why? First a bit of background. When we set the ‘priority boost’ option using sp_configure what is happening is that after restart the SQL engine…

10

Some notes on database snapshots

Overview I recently conducted some tests to double check the exact behavior of database snapshots when: Snapshots are created on volumes which are much smaller than the actual database size Metadata-only operations such as DROP TABLE are executed DDL operations execute on the main database and the snapshot runs out of disk space Here is…

1

T-SQL Anti-pattern of the day: 'all-in-one' queries

Scenario A common requirement for enquiry queries on an OLTP database is to have search criteria which are very specific (‘get me details for for OrderID = NNNN’) and also the occasional reports which ask for all the orders (‘get me all the orders, no questions asked’.) Here is a sample from AdventureWorks which illustrates…

10

Resources from TechEd sessions

Several attendees have asked for the resources section from our presentations at TechEd India. Enjoy! BlogsSQLCAT: http://blogs.msdn.com/sqlcat WINCAT: http://blogs.msdn.com/wincatSlava Oks’ blog: http://blogs.msdn.com/slavao Product Feedbackhttp://lab.msdn.microsoft.com/productfeedback/ External Links:NUMA FAQ: http://lse.sourceforge.net/numa/faq Books:Inside Microsoft SQL Server 2005: Query Processing and Optimization (Volume 3)The Guru’s Guide to SQL Server Architecture and Internals (SQL Server 2000) SQL Server 2005 – Operations http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx…

0